0

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?

1
  • This worked successfully apart from I forgot to include the "keep replication" option on the restore of the replicated user databases. Dec 13, 2011 at 8:53

1 Answer 1

0

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.

1
  • Thanks Hugo, had to build environment upfront to allow for some testing. The builds are the same level. Nov 16, 2011 at 9:38

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .