July 9, 2009 at 10:06 am
We are evaluating transactional replication as a possible high availability solution, and I have been experimenting with the restore from backup feature and have it working in a basic fashion, but I have a few questions.
1) If we use transactional replication for high availability we plan on only replicating a few high volume tables (our database has over 300). This means we'll need to be able to sync from a fresh backup every day in order to pick up miscellaneous changes outside of our set of replicated articles. What would be the best way to accomplish this daily resync using backup/restore?
2) When we create a subscription it needs to be done from script. In the script we reference the .bak file that was used for the restore. How does the replication engine use the backup file? Can it be used to resynchronize the database? Here is a sample script that I used:
exec sp_addsubscription
@publication ='AcctTransactions', --your pub name here
@subscriber='TCE-Backup', --subscriber server name
@destination_db='PCP',
@sync_type = 'initialize with backup',
@backupdevicetype = 'disk',
@backupdevicename = 'D:\ReplicationSnapShots\PCPReplication.bak'
3) If we're initializeing from backup, then how does the SnapShot agent and SnapShot folder affect replication? Do I still need to make sure the
folder permissions are properly declared? Can I use the SnapShot agent to resynchronize the database?
Thanks for your help. I've scanned this forum and the internet but I haven't found anything yet that helps me understand how resynchronization is done when transactional replication is initialized from backup.
Kathy
July 13, 2009 at 7:59 am
KathyGibson96 (7/9/2009)
...we plan on only replicating a few high volume tables (our database has over 300). This means we'll need to be able to sync from a fresh backup every day in order to pick up miscellaneous changes outside of our set of replicated articles. What would be the best way to accomplish this daily resync using backup/restore?
I don't understand what you mean by this. Are you saying that you want to sync your Subscriber database with the Publication database every day for both replicated and non-replicated data? That is different from initializing the database from a backup.
To answer point 2: as long as the subscriber has access to the backup file location the database engine processes it as it would for a 'normal' restore operation. Note though that the 'allow_initialize_from_backup' value must be 1 on the publication or distribution database. You can check this by running sp_helppublication.
For point 3: you don't need to worry about the snapshot location if you are initializing from a backup - you specify the location in the sp_addsubscription stored proc as per your example.
Depending on what you mean by point 1 I'm not sure that replication is going to meet you HA needs; you might want to consider database mirroring instead.
Regards
Lempster
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply