Publisher: WIN2k (sp2) SQL 2000 Standard (sp2) 4 processor 4GB RAM
Publisher Database: 5GB, largest table = 6 million rows
180 Subscribers: WIN2k (sp2) laptops MSDE 2000 (sp2)
28 Merge Publications: 120 Tables, 8 tables with static filters, 20
with join filters, max joins per filter = 3
Network: About 500 separate VPN connections distributed throughout the
state, Mostly T1, some 512mb/s and 256mb/s
Average Sync times: 20 min to 3 hours depending time between synchs.
Average Merged Changes/synch: 20,000 to 250,000
Max time between synchs: 14 days
Synch Method: Custom VB app using the MSMerge object
The SQL Server is part of a Web site with a Web Server, an Application
Server, and the transactional SQL Server (OLTP). There is a data
warehouse that has transactional replication (one way) running with
the OLTP SQL Server as the publisher.
In production 13 days
We need to distribute the load on the OLTP database, primarily with
the Laptop Merge Replication. We synchronize mostly at night, but
with network downtime, batch and backup jobs, snapshot jobs etc, we
are running out of time to work with. If more then 3 laptops
synchronize during business hours, the Web site becomes very
vulnerable to long lock wait times and deadlocks.
My basic strategy would be to deploy a new SQL Server solely for
Laptop Merge Replication. I would have all the laptops synchronize to
this server. This server would have the 28 filtered publications.
The OLTP server would then hold a single unfiltered Merger publication
with the new SQL Server as the sole subscriber. I can't use two-way
transactional replication between the servers due to conflicts.
My question is, how much resources on the OLTP server will this single
subscription consume? How often should I synchronize between the two
servers (after they synch the laptop, they go to the web site to print
checks)? Is there a better way of distributing the merge replication
load then this?