February 17, 2012 at 1:49 pm
Hi All,
I am trying to set up Merge Replication on SQL Server 2008 Standard Edition. We have all the tables in 1 database and the size of the database is approximately 110 GB. I have set up Distributor on the Primary Database Server. Depending on the use of the table, I have divided the tables into 4 different Publications as shown below.
Publisher1:
Number of Articles: 3
Time taken to create Initial Snapshot: 2 mins
Publisher2:
Number of Articles: 46
Time taken to create Initial Snapshot: 15 mins
Publisher3:
Number of Articles: 45
Time taken to create Initial Snapshot: 50 mins
Publisher4:
Number of Articles: 33
Time taken to create Initial Snapshot: 50 mins
When I start creating Publication1, the database gets locked and no transactions can be done. Once the snapshot has been created, everything comes back up and starts working fine. The Snapshot is applied to the Subscriber database. Then I set up the job for Synchronizing the Data every 1 min.
When I start creating Publication2, the database gets locked and no transactions can be done. Even the job created for Publication1 starts getting Locks and the data cannot be synchronized. At this stage I disable the job for Publication1 and Start creating the Snapshot again for Publication2. Once the snapshot has been created, everything comes back up and starts working fine.
I start the job to synchronize the data for Publication1 and it takes almost 30 mins to synchronize the data for the first time.
Now The Snapshot for Publication2 is applied to the Subscriber database. Then I set up the job for Synchronizing the Data for Publication2 as well for every 1 min.
Now to start the Publication3, I need to Stop the jobs for Publication1 and Publication2, so I can create the Snapshot for Publication3. It takes almost 50 mins to create the snapshot for this one. During this time, a lot of data is collected for Publication1 and Publication2 that needs to be synchronized. At this Stage when I start the jobs for Publication1 and Publication2, I start getting plenty of Locks and Deadlocks and I am never able to finish the Synchronize here.
So, having multiple Publications on the same database can have performance issues. What would be best recommendation in such a scenario. Also Does creating the Snapshot Locks the database?
Thanks,
Praveen
February 22, 2012 at 1:19 am
Why are you putting them in different publications?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply