Moving replicated database to new server
-
Good Morning everyone,
I hope someone can give me some guidance on how to move a database involved in replication from one server to another. At the end of the month I will be migrating a production database to another server. The gotcha is that the database is a publisher for both transactional replication and snapshot replication. Another issue is that in the new environment the database is also using log shipping. I have already moved the database one time to the new environment and established both the replication and log shipping. Now at the end of the month I will need to refresh that database with a current backup. What I think I need to do is
1. stop the application and make sure there are no user connections on the current production database.
2. Stop the logreader and distribution agent on the distributor.
3. Backup the database.
4. Move the backup to the new server.
5. Remove or disable log shipping on the database to be restored.
6. Stop the logreader and distribution agent on the distributor in the new environment.
7. Restore the database from the backup.
8. Restart the logreader and distribution agent and reinitialize the snapshot for both the transactional and snapshot publication.
9. Reenable the log shipping.
Does that sound correct? Also, prior to the move I want to test the failover to the secondary server in the log shipping. From the documentation I found it indicated after I failover and restore with keep replication that I will need to rename the secondary server to what the primary server is? Is that correct? Has anyone done the log shipping failover before with a replicated database and can give me the steps to follow? I would appreciate any advice, since I have never attempted this before.
Thanks,
Michelle
-
I have just started tinkering with replication myself. I'm not sure when you backup and restore the target database that it remembers your source database at all, or even that it was a replication target. You might have to re-establish the replication from scratch. But lucky you! There is a "generate SQL script" option if you right-click on the publication name in Enterprise Manager. This SQL script can turn off the replication and turn it back on (look in servername\replication\publications in Enterprise Manager).
I have found little harm in turning replication on and off of the source database. It doesn't seem to cause any interruption in service, though of course you have to be careful in your production database.
Maybe not much help but I thought I would chime in.
-
Thanks for your response. Actually, since I posted the thread I have decided that I am going to remove both log shipping and replication (after I generate the sql script of course), before restoring the database on the new server and then recreate the publications, resynch and reapply log shipping.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply