I will be working on a project shortly that would involve moving the replicated databases off of old to the new SQL Server instance. I haven't done this in the past so I don't have much knowledge either on how to move ahead except to do some research.

As of this date our current config sits on a Windows 2008 R2 server with SQL Server 2008 R2 and publisher along with the distributor is the same SQL Server instance e.g sqla and the subscriber is sqlb and also now sqlb is acting as a publisher and distributor for sqlc... these are all pull subscribers with one way transactional and snapshot replication.

Now, back to my original question. Is there a way to migrate the databases off of the old to the new with least downtime so I don't have to script out and reinitialize subscriptions as it would take a lot of time (that's what most of the people mentioned on google). Do we have any hacks or tricks to make this work without causing problems or if there is any step by step to work it out even if we use script method.

I am going to that with my same type migration this past February, I had to do all systems in one day. I had all my servers prepped before had with the exact same folder structure and included the folder name MSSQLSERVER10. I shut down all services and disabled them. I then copied all the system databases to the exact same folder location on the new system. I started SQL Services only to ensure systems came up. Once that was confirmed I brought over 1 of the replicated systems to ensure they come up. Once that was successful, I brought over everything else, turned the systems on and waited a bit to start the agent as I wanted to check and ensure every db came back online. I then turned on agent service on Primary first and then started all of the secondaries. Forcing replication, or rebuilding it was not an option for me. There are other ways to do it but this was the one I decided to use for the environment we have. I was luckily enough to have an outage window to complete this.

I had to do a similar scenario for a major CDN, thus we couldn't have downtime more than a couple of minutes to upgrade to 2014 from 2008 and have a rollback strategy.

Short version is we used mirroring to get data up to 2014 and replication for a failback. We tested and created all the scripts using the custom option of 'no snapshot or restore init' which basically you tell the system 'don't worry i know it's synced how I like'.

To implement script everything and ensure writes are stopped while failing over. Configure replication back down to 2008 from the new Publisher and you'll be good to go. Note to have reseed scripts handy for failback.

I have some scripts I can remove sensitive info that does most of this plus:

-Migrate an FCI

-Configure AGs

-Break Mirroring, Setup Replication

-Test

-Other items & rollback scripts.

I'll need a few days to review them for any missed sensitive info but I can post them if needed afterwards.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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