Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Locking problem while initializing the replication Expand / Collapse
Author
Message
Posted Wednesday, April 3, 2013 5:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 3:33 AM
Points: 21, Visits: 89
Hello,

I have a question for the replication experts here:
This morning replication has stopped because of a reboot of the Subscription server.
* I did remove the publication and subscriptions.
* I made a new publication
* I made a new subscription, and let it initialize directly.
* All seems fine, replication started, and all data was transferred correctly.

After a while, we did have some production problems, some SQL-Server time-outs from client applications exactly in the period that the replication was initializing / copying data from our production (the publication server). It seems that there where some locking problems.

Can someone please inform me, how I can make sure that when I start replication, we will not have locking issues? Is this possible? Or will I always have locks on the publication server while bulk copying data / initializing the replication?

I thank you for your reply!

Grt. Mischa
Post #1438274
Posted Wednesday, April 3, 2013 4:51 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:02 PM
Points: 15, Visits: 124
Why did you rebuild the publication and subscriptions after a simple subscriber reboot? The transactions would of just built up on the publication DB and then synched up once the subscriber came back online. regardless to answer your question...

Syncing up the subscriber database uses Snapshots which will create locking on the publication DB as you noticed. The best thing to do is not set to Immediate initialization and either do it manually during off hours or simply set a schedule to do it during non-peak hours.
Post #1438613
Posted Friday, April 12, 2013 2:07 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:34 AM
Points: 820, Visits: 1,177
You can initialize from backup. That is the best way to do replication for bigger databases because it eliminates all bcp generation hence locking/ blocking.
Post #1441918
Posted Monday, April 15, 2013 5:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 3:33 AM
Points: 21, Visits: 89
Thanks for your replies.

I've seen that the locking issue while starting a replication is normal, so we have to do this outside production hours.

Thanks again for your help!

Greetings,
Mischa
Post #1442255
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse