Tuesday, March 27, 2012

Do I really need a snapshot (to initialize transactional replication, in SQL2000)?

I have a pretty big (350 gb) OLTP database that I want to replicate in its entirety. I'm concerned about the impact of taking a snapshot of it (it is processing at some level pretty much 24x7). I know on SQL2005 there is the option to initialize from backup, but unfortunately we won't be on 2005 in time.

I'm thinking of doing something like this:

Set up the distributor, publication, and subscription Turn off distribution agent Set the publisher to "sync with backup" Backup the publisher, full then log Truncate tables MSrepl_transactions and MSrepl_commands in the distribution db (I don't have any other replication going on) Turn off "sync with backup" Restore the full and tran log backups to new subscriber db Create subscriber stored procs in subscriber Start up distribution agent

I'm looking for opinions on whether it's worth going this route to avoid taking the snapshot. Data integrity is the number one priority -- if I have to do a snapshot to ensure that, I will do it.

Thanks in advance!

Mike

OK, I just did a search and came across this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;320499

However this method still requires a brief time in single user mode (ie killing all connections), whereas my method doesn't. I just don't like that my method involves deleting the MSrepl_ tables...

No comments:

Post a Comment