May 2, 2003 at 8:50 am
Is there a way to bring a transactional replicated server on-line during production hours (new transactions being made), WITHOUT using a snapshot? The databases are too large (30GB) to use the snapshot method with concurency turned on. The only way that I can bring the new servers on-line is using the backup/restore method when no activity is happening (off hours), then specifying that the data is already at the subscriber. The problem is that any transactions that happen between the end of the backup, and the creation of the pull subscription are lost.
I have considered the following scenario (not sure if it will work):
1) Create empty subscription database (with empty tables) at subscriber
2) Create Pull subsription at subscriber
3) Stop sync on the pull subscription (transactions will now start queueing)
4) Backup publisher DB
5) Load publisher DB into subscriber (Will the pull subscription be lost?)
6)Start the existing pull subscription back up, queued transactions will sync.
Will this work? Will the pull subscription be lost after restoring the DB? If not, does anybody have any ideas? Thanks.
-Dan
-Dan
May 2, 2003 at 10:32 am
You won't be able to restore the production db in the publisher db if the db is alredy part of a publication.
You will have to drop the publication ,then restore the db and then re create the publication.
Won't the restore process last more than applying the snapshot?
You could also, pause the production server, bcp out all the data of the published articles, bcp in into the subscriber db, create the publication, and then start the production server.
May 2, 2003 at 11:06 am
That was what I was afraid of...
BTW, the snapshot takes about 8-9 hours, but a Baskup/Restore takes 2-3 hours, and that's after optimizing the snapshot using -UseInprocLoader and multiple BCP threads.
I've also considered doing a detach-->copy-->attach, this would only take about 2 hours. Also the snapshot is logged when it is applied, so you need a huge transaction log at the Subscriber to finish the snapshot.
All this said, I guess the only way to do it is during off-peak hours.
-Dan
-Dan
May 2, 2003 at 11:41 am
Im surprised there is such a big disparity between backup vs bcp, maybe I just never looked. One idea is to break it up into separate publications, maybe separate pub for each large table, one pub for all small tables. That way the snapshot will go faster and the log won't grow as much. Lot to be said for this approach anyway, you don't want to have to resnapshot everything if just one article out of sync.
Andy
May 2, 2003 at 12:04 pm
Yes, it is too much time. Are you compressing the snapshot on generation? This will make SQL to last more in generating the snapshot.
With @sync_method are you using?. If you are using concurrent instead of native, SQL will last more to generate the snapshot, because it must also add all the transactions made during the snapshot generation. But with native type the snapshot will lock resources.
Maybe you should manually check the .bcp files that the snapshot generates to check that everytinhg is right. Are you adding the replication stored procedures too? And what about indexes?
May 5, 2003 at 3:20 pm
Just to answer the questions posed...
I'm not compressing the snapshot. I'm using concurrent snapshot generation because I can't lock the production DB during business hours. The BCP files are okay, because I've been able to complete the snapshot successfully one time in the past (painfully long though). Indexes...good point, I didn't make any special changes related to indexes, so I'm not sure if replication builds the indexes before or after the BCP-IN stage. I guess if it did it before, it would then have to use the slow version of BCP...hmmm
-Dan
-Dan
May 5, 2003 at 3:48 pm
Just had a brainstorm...what about this?
1) Create subscription database at subscriber
2) Backup publisher DB
3) Load publisher DB into subscriber
4) Create Pull subsription at subscriber and start syncing
5) Perform differential backup at publisher
6) Restore differential backup on subscriber while pull subscription is in place
Will all transactions be at the subscriber?
-Dan
-Dan
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply