Getting Replication Distribution Back After a MASSIVE Failure

Published On: 2014-06-18By:
Distribution

Image from Shutterstock

Recently I had to upgrade a SQL 2008 R2 server to SQL Server 2014 on the fly, because the server in question was the SQL Server replication distributor and a couple of databases which were being replicated were being moved to a SQL Server 2014 instance. The Distributor’s upgrade didn’t go well.

After I got SQL up and running and happy with SQL 2014 CU1 it was time to get replication running again. The first thing I wanted to deal with was getting the SQL Agent jobs back up and running. SQL Server had decided that it was going to put the database files into E:MSSQLMSSQL12.MSSQLSERVERMSSQLDATA. The first thing that I did was backup the folder as I was going to be playing in here, and I needed a backout plan in case things went horribly wrong.

Backing up the folder is easy. You stop the SQL Server services (SQL Server and the Agent) and copy the folder. I then grabbed the msdb database files from the SQL 2008 R2 folder and dropped them into the SQL 2014 database folder and restarted SQL. To my surprise this actually worked. All the jobs were listed as expected.

Next step, getting the distribution database up and running and getting SQL Server to think that replication has been running perfectly for years. The first step of this was to make a backup of the replication database files. If something went wrong, I needed a copy of these to restore from as the only backups that I had were missing transactions as those transactions weren’t backed up as the entire upgrade process was supposed to be smooth.

Attaching it was pretty straight forward. I just needed to run through the distribution setup wizard and get the T-SQL for it, then change the database name to match the old I’d been using.

Some permissions needed to be granted, and most importantly the linked servers needed to be added in the correct order. Once you’ve got them in there you can’t take them back out easily. I ended up with them in the wrong order so I had to switch the ID numbers around by putting the instance into single user mode from the command prompt and editing the sys.servers table manually via T-SQL.

Once that was all straightened out, the replication agents all came online and everything pretty much started working.

Denny


Contact the Author | Contact DCAC

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner    Microsoft MVP    Microsoft Certified Master    VMWare Partner    VMWare vExpert
   Best Full-Service Cloud Technology Consulting Company    Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link