April 21, 2006 at 7:42 am
I don't know a lot about replication. When you start a snapshot can it cause blocking on the table it is currently copying?
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
April 21, 2006 at 8:00 am
Yes, it certianly can.......but there are ways around it.....look at the options for creating the snapshot.....
April 21, 2006 at 8:09 am
Thanks!
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
April 25, 2006 at 3:30 pm
There is an option in the snapshot generation that 'Do not lock table'. Not sure if it work.
Does anyone use his option? and does it work? Checking this option, SQLServer forces a complete re-initializing of all subscribers (It ask for comfirmation first). So , execrise caution when usingt his option.
Another question, while snapshot is generating will it lock one table at a time or will it lock all tables until it finishes?
Thanks !!
April 26, 2006 at 12:03 pm
One at a time, as the snapshots for each table are generated...
May 1, 2006 at 5:42 am
Well what i would recommend you is to look into the option of concurrent snapshot generation for your problem in reference to blocking.
This would really help u
May 3, 2006 at 4:53 am
The option to do the non locking snapshots as has been suggested helps as it means that while the main bit of the copy is working the table is available for reading and updating at the publisher. However, you need to be aware that when the initial scripts for creating the tables are generated it will still attempt a schema lock and if you have a heavy transaction environment with proc recompiling, etc you can end up with blocking and deadlocks at schema level - in my experience it's normally the transactions that get rolled back. I still have to do this work at the quietest possible times even with the snapshot non locking option because of this.
Another problem with the shared snap shots happens if the tables are very large. Any updates that occur while the snapshot is in progress are applied to the snapshot after it has been copied at the subscriber using the generated repl procs. Unfortunately it does this before running the scripts to create the indexes so results in tables scans for each update. On very large tables this may cause your snapshot to time out when being applied at the subscriber. Getting round this can be tricky.
May 4, 2006 at 6:33 am
If you're using transactional replication on SQL Server 2005, there is an alternative option that'll avoid the problems you face - you can initialize a subscriber using a backup of the production database. This is not the same as a nosync initialization in SQL Server 2000 - the transaction_timestamp value of the publisher is reused. in other words, you don't need to prevent access to the publisher during the restore of the backup and setting up of the subscriber as concurrent transactions will be applied when you use the distribution agent.
Cheers,
Paul Ibison SQL Server MVP, http://www.replicationanswers.com
Paul Ibison
Paul.Ibison@replicationanswers.com
May 4, 2006 at 10:54 am
Paul - Thanks for that info. Sounds like the answer to the probs I've been having to work around for the last few years. Adds a bit more weight for a 2005 migration!
Cheers
Graham
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply