Home / server / Questions / 520846
Accepted
David Heggie
Asked: 2013-07-05 04:37:13 +0800 CST

Switching SQL Server Replication Publication

  • 772

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.

Thanks

sql-server

2 Answers

  1. Best Answer

    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

    • 2

  2. 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.

    • 1