SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Locking problem while initializing the replication


Locking problem while initializing the replication

Author
Message
Mischa E.J. Hoogendoorn
Mischa E.J. Hoogendoorn
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 104
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
Sailorking
Sailorking
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 126
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.
Neeraj Dwivedi
Neeraj Dwivedi
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1095 Visits: 1334
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.
Mischa E.J. Hoogendoorn
Mischa E.J. Hoogendoorn
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 104
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search