Migrate a SQL Server environment with complex replication without reinitializing or rebuilding replication

Posted in: Microsoft SQL Server, Technical Track

When you have a SQL Server environment where a very complex replication setup is in place, and you need to migrate/move (without upgrading), some or all the servers involved in the replication topology to new servers/Virtual Machines or to a new Data Center/Cloud, this Blog post is for you!

Let’s assume you also have Transactional and/or Merge publications and subscriptions in place, and you need to move the publisher(s) and/or distributor(s) to a new environment. You also have one or more of the following restrictions:

  • You are not sure if the schema at the subscribers is identical to the publisher (i.e.: different indexes, different columns, etc).
  • You cannot afford downtime to reinitialize the subscriber(s)
  • There are too many subscribers to reinitialize and you cannot afford the downtime if anything goes wrong.

Here are the general steps for this migration:
Prior the migration date:

  • New instance has to have same SQL Server version and edition plus patch level as old instance. Windows version and edition can be different but you need to ensure the version of Windows supports the version of SQL Server.
  • The directory structure for the SQL Server files should be identical in the new server as old server and same permissions:
    • Same path for SQL Server binaries
    • Same path and database files names in both servers for system databases
    • Same directories where user database files and T-logs reside
    • Same path for the replication directories (when applies)
  • Copy over any instance-level objects (Logins, Linked Servers and jobs) to new instance; leave jobs disabled if applies or stop SQL Server Agent on new server

On migration date:

  • Disable any jobs, backups and maintenance that should run during the migration window on old server
  • Stop all database activity on old instance or disable logins
  • Restart old instance and verify there is no activity
  • Synchronize all replication agents that are related to the server being migrated
  • Stop and disable replication agents related to the server being migrated
  • Stop both instances
  • Copy over all system database files from old to new server
  • Copy over all user database files from old server to new one
    • Alternatively, backup all user databases on old server before stopping service and copy the files to new server
  • Shutdown old server
  • Rename new server to the name of old server and change the IP of new server to old server’s IP
  • Start the new server
  • Verify that the name of the new instance is like the old server and it’s local
  • If you backed up the user databases previously, you need to restore them to same location and file names as in old server with RECOVERY and KEEP_REPLICATION
  • Verify that all user databases are online and publications + subscribers are there
  • Start all replication agents related to the migrated server and verify replication is working properly
  • Verify that applications are able to connect to the new instance (no need to modify instance name as it is the same as before and same IP)

At any case, it is strongly recommended to test the migration prior to the real cutover, even if the test environment is not identical to Production, just to get a feel for it. Ensure that you are including most replication scenarios you have in Production during your test phase.

The more scripts you have handy for the cutover date, the less downtime you may have.

It is extremely important to also have a good and tested rollback plan.

In future Blog posts I will discuss more complex replication scenarios to be migrated and rollback plans.

If you would like to make suggestions for future blogs, please feel free to add a comment and I will try to include your request in future posts.

email

Interested in working with Michelle? Schedule a tech call.

About the Author

Microsoft Data Olatform Architect
Michelle has 30 years in IT, and has been working with SQL Server for the past 20 years. She has designed methodologies that consist of documentation, utilities, and scripts to automate architecture, design, and performance tuning initiatives for her clients. Michelle is able to see the wider vision of her clients’ business. She is passionate about solving problems quickly and providing value to her clients. She speaks English, Hebrew, Spanish, and a bit of French.

5 Comments. Leave new

Tested this process to migrate the server of one side (the side we manage) of a bi-directional replication setup. The other side is on various clients’ domains and therefore this seemed like the best solution to have as little downtime as possible whilst avoiding mass redeployment.

However, I am running into issues with log reader on publisher – after completing last step of process you’ve outlined, log reader is throwing “error authenticating proxy system error: The user name or password is incorrect” on step 2.

If I try and validate/amend the credentials which the proxy references, it doesn’t let me and I know for certain, password has not changed (after rolling back to original server, replication resumes absolutely fine).

Cannot find any documentation around this online. Any thoughts?

Reply

Just after writing previous post, I found my answer.

Backup service master key and restore it to new server. This allows you to amend or drop and recreate credentials linked to proxies thus solving the issue.

Reply

Thanks for posting the solution! :-)

Reply

Thanks Machelle for interesting blog.
Could you also blog on other complex replication scenarios please…
“using more complex replication scenarios to be migrated and rollback plans.”

Reply

Hi Michelle Gutzait..

Thanks for article and its really helpful..
Similar requirement came to me now but only change is we need to upgrade from 2012 SQL to 2017 SQL and we have separate publisher, distributor and Subscriber..

Its working fine when I upgrade Distributor and failing when Upgraded Publisher..
“Publication does not exit”

Looking forward your replay if you have any..

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *