SQL Server Subscriber Migration
- by SuperCoolMoss
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.
Switch replication back on.
Are there any flaws in this approach?
