Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We're currently have one way transaction replication from a SQL Server 2005 OLTP publisher/distrbituor to two subscribers (one SQL 2005 and the other SQL2008 R2). Replication security is via the SQL Agents' domain service account (the same account is used on all boxes).

The SQL2008R2 subscriber is used for BI purposes and hosts a database that has a subset of the Production publisher database tables, with different security and indexes.

We need to migrate this BI subscriber to a newer box with more performant hardware.

The plan is as follows:

  • Stop replicating to the BI box (continue replicating to the other subscriber).

    Backup all databases on the BI box (including system databases).

    Restore all databases (including master in single user mode) to the new BI box (this has SQL Server 2008R2 already installed).

    Take the old BI box off the network and shut it down.

    Rename and Re-IP the new BI box to be the same as the old box.

    Rename SQL (sp_dropserver and sp_addserver).

    Reboot box.

    Switch replication back on.

Are there any flaws in this approach?

share|improve this question
This worked successfully apart from I forgot to include the "keep replication" option on the restore of the replicated user databases. – SuperCoolMoss Dec 13 '11 at 8:53

I prefere to install SQL on the new box AFTER the rename, because sp_dropserver and sp_addserver doesn't rename the local groups created by SQL. In any case: make sure the new box has the same SQL build as the orginal one.

share|improve this answer
Thanks Hugo, had to build environment upfront to allow for some testing. The builds are the same level. – SuperCoolMoss Nov 16 '11 at 9:38

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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