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
