So I was going along upgrading a SQL Server 2008 R2 RTM server to SQL Server 2014. I had to install SQL 2008 R2 SP2 or newer before I could upgrade. That’s fine, I can live with that.
Then I get to the SQL 2014 installer. I get all the way to the end, when it fails. All because the folder E:MSSQLMSSQL10_50.MSSQLSERVERMSSQLJobs isn’t there (SQL is installed on the E drive). All the other folders were there, just this one wasn’t. Now honestly I have no idea why this folder even exists. To the best of my knowledge and remembering there’s never been anything inside of it in the 15 years that I’ve been working with SQL Server. God forbid the installer just create this folder and move on, or I don’t know check to see if it exists during one of the three rules checks that it does during the install process.
So fine, I created the folder and reran the installer. Only to be created with this annoying screen.
According to this annoying little screen I’ve got two components to upgrade, but it says that there’s no components available to upgrade. So that’s awesome. Way to be consistent Microsoft.
Next step way to try and force the upgrade via the command line. Pretty straight forward upgrading via the command line. Only takes a few switches.
Once that was done the install was totally hosed. SQL wouldn’t start. It would just give me a warning about a corrupt installation. Attempting to start it from the command line wouldn’t help either. Hell the command line upgrade process installed the bits and removed the 2008 R2 bits, but didn’t bother to change the service to point to the new files, which apparently is fine because the rest of the SQL install is hosed now.
Repairing the installation did no good, because the installer couldn’t find a valid installer on the server.
The upgrade process couldn’t find anything to work with either.
The next thing that I tried was to add the features that I needed (the SQL Database Engine) through the normal install wizard so add features to an existing installation. At this point the SQL installer didn’t even see an instance on the server, so there was up add features option. I was simply prompted to do a normal install of SQL Server 2014. When I went to install it stopped me from using my Instance ID which was MSSQLSERVER because it was apparently already in use by the SQL Server instance “MSSQLSERVER.INACTIVE”, what ever the hell that is.
Fixing this required using this blog post which talks about how to get rid of this instance manually. If you read the instructors here and think “damn that’s crazy”, yep you’re right it is.
So once that was done I went through the installer yet again. That got me through the installer. I was even able to give the installer the data folder of the old SQL 2008 R2 folder so that it would be able to pick up all the databases when it came online.
The database installed successfully. However it didn’t pick up the databases and attach them. The database that I was really hoping that it would pickup was the distribution database for replication so that I wouldn’t need to recreate all the replication by hand. How I deal with that is for another blog post.
The moral to this post is that apparently you need to make sure that every single folder is where it should be before you try and upgrade SQL Server from one version to another. Otherwise a 45 minute process could easily take you hours to get through.
Denny
5 Responses
I feel your frustration with the SQL Server install not creating the folder. I blogged about a similar frustration when setting up an AlwaysOn FCI with a local TempDB and how the installer will do it on the first node, but won’t even warn on subsequent nodes much less do it for you.
Good one Denny, I’m on this path at my work place , fortunately all 2008 instances are already on sp2 so I hope I will never face this hell 🙂 , did you considered doing a vm snapshot before attempting any such upgrades, that would give us a good rollback plan in case of such surprises (although only if your environment is virtualized, in my case it is 99% ).Thanks for the post.
This machine is virtualized. Sadly I didn’t snapshot the VM before I did the upgrade. This machine wasn’t supposed to be upgraded to begin with. It needed to be upgraded because this machine was a replication distributor, and a publisher was upgraded to SQL 2014, so the distributor needed to be upgraded as well. Frankly upgrading a SQL instance shouldn’t be a mess like this one was. Microsoft is still going through the logs to figure out what happened.
So would it have been better to install a new instance of SQL 2014 and restore the old databases over to the new instance?
Normally this wouldn’t be needed. Someone specific went very wrong on this machine. I’ve done several upgrades since then and they’ve gone just fine. In the case of this machine a side by side migration wasn’t an option as it is a distributor for SQL Server replication.