I have a production database hosted on SQL Server 2008R2 Standard that is replicated to a second server running the same version of SQL server via a subscription to the production database. The subcriber database is used for running SQL reports on the data. Deletes aren't replicated, so the reporting database is pretty large compared with the production published database, which is cleaned up weekly.

I'm planning on replacing the server that hosts the published database and was wondering what the best course of action was to ensure that the subscriber database doesn't loose any data. Is this a sensible outline of the steps to follow:

  1. Restore a copy of the latest backup from the old production database to the new server
  2. Publish this new database using the same settings as the old database
  3. Unsubscribe (or whatever the proper term for breaking the replication is) the reporting subscribed database from the old published database
  4. Subscribe the reporting database to the new publication

Is it as simple as that, or is there something I've missed that could turn around and bite me? The main thing I want to ensure is that the database used for reporting (the subscribed database) doesn't loose any data, and continues to receive new replicated data from the new database.



Your plan is correct except you will need to specify @sync_type of replication support only for sp_addsubscription which assumes the subscriber already has the schema and the initial data for the published tables and will not initialize thereby skipping the drop.

If you are using the New Subscription Wizard then the same can be achieved by selecting Do not initialize on the Initialize Subscriptions page.

enter image description here


The trick will be in how you synchronize. In the sp_addarticle stored procedure, the default for the @pre_creation_cmd parameter is to drop the table at the subscriber. That's going to be a problem for you. Here's how I might do it:

  1. Do whatever you're going to do to move the published database to the new server. This will likely include breaking replication (deliberately).
  2. At the subscriber, rename all of your tables (or put them into another schema). This will protect them from any woe that could befall them as part of the replication reinitialization. Alternatively, you could rename the database with the intent that you'll create a new database to serve as the subscriber.
  3. Rebuild the publication and subscription
  4. Let the publication synchronize
  5. For each table that you have this archiving condition set up on, insert the data from wherever you moved the tables to in step 2 that doesn't exist in the "live" tables at the subscriber. Essentially, a left join between the saved off table and the live table in each case

I'd also suggest that you take this opportunity to implement a safer place for your archive data. If ever replication breaks on its own (it can and does), you're in a tricky spot as far as reinitialization. If I had to do it, I'd create a custom procedure that's called by replication (you can specify it with the @del_cmd parameter to sp_addarticle) that inserts the record into your archive table and then does the delete from your live table. But there are myriad ways to accomplish the same thing. Good luck.

  • Thanks for that - I've been playing with it this afternoon on a small test setup that I've got and I'm coming to the same kind of conclusions as you've suggested - I tried what I'd outlined above, but the initialisation of the subscription deleted all of my archived data. Which I should really have realised would happen. Jul 4 '13 at 15:33
  • This can all be avoided by specifying a @sync_type of "replication support only" for sp_addsubscription when you create the new subscription. Jul 4 '13 at 17:53
  • @Brandon: That setting is for when you're sure that there's no data movement at the publisher. I never assume that.
    – Ben Thul
    Jul 4 '13 at 23:58
  • If the OP is switching Publication servers via backup and restore then surely there will be planned downtime during the switch, which in that case, using @sync_type of replication support only would work perfectly and would alleviate the need of sending down a new snapshot, saving time. Jul 5 '13 at 0:11
  • Even if there is data movement a diff can be done using tablediff utility or SQL Data Compare and the data can be manually synced. It is worth the time saved. Jul 5 '13 at 0:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.