I am a fan of good documentation and love it when you can find a full how to regarding the thing you are looking to do.  When it comes to SQL 2016, however, there aren’t a lot of how tos out there yet since SQL Server 2016 only came out in July. I am writing this because there are a couple of warnings and gothchas.  I hope this post will save someone time when trying to do their own upgrade.  One thing I would like to note is this was accomplished on a single server with no high-availability groups, no special setup, no special anything.  Also, the pictures will appear quite large, but I wanted to make sure the details in the graphics are viewable.

Pre-Install

Before you begin the fun process of upgrading the install, there are a few things you will want to do.  Below is my checklist for making sure things didn’t blow up in my face as this was a production server (no servers were hurt in the making of this blog post):

  1. Make sure you have good backups!  This is the number one rule for anything DBA-wise, but for upgrades, make sure you have one, two, ten, versions of the backups.  If you can, test them as well.
  2. Take screen-shots of all your settings.  A normal upgrade of SQL, whether you are going from 2005, 2008, or 2012, is a uninstall and re-install of the application. When you have linked servers or mirrored replicas or simply made changes to the memory settings to limit SQL’s control of the OS, take those screenshots.
  3. If you have Read-Only databases, you will have to change them to Read-Write before you start.  The command is simple:
    1. USE [master]
    2. GO
    3. ALTER DATABASE SET [{name of database}] TO READ_WRITE WITH NO_WAIT
    4. GO
  4. Check to make sure the compatibility mode for a database is not set to low.  The upgrade process can handle 2005 databases, but gets a bit weird with them.  If I remember correctly, you have to upgrade a 2000 database to at least 2005 before you can even upgrade it.
  5. Run the Database Migration Assistant.  It is invaluable in prepping your server for the upgrade.  You can find it here: https://www.microsoft.com/en-us/download/details.aspx?id=48119

Install

When you start the upgrade, make sure your user has administrative rights to the server.  Also, make sure that if you have registered servers, that you know you will need to re-register them after the upgrade. First, open the CD or ISO if you have one and double-click “setup.exe.”

image1

Next, click “Upgrade From a Previous Version of SQL Server”

image2

If you have a product key, enter it here.  Otherwise, specify “free edition” and click Next.

image3

You will have to accept the terms of the Microsoft Agreement.

image4

Click the checkbox to accept the agreement and click Next.

image5

The installation will begin looking for pre-requisites and install them if you do not have them.  On this installation, all the pre-requisites were met.  As of this writing, there were no product updates that could pulled either. Click Next.

image6

Make sure that you select the instance you want upgraded.  On this installation, there was only the default instance. When you are satisfied, click Next.

image7

You are now presented with a list of features to install.  As this is an upgrade, it should all be greyed out.  If everything looks normal, click Next.

image8

Now we configure the instance.  For the default, you shouldn’t have to do anything. However, if you have changes, you would make them here. Click Next when done.

image9

If you are on an Active Directory domain, the SQL Server and SQL Agent should run as domain users with administrative rights to the system. We are not really using SSIS off this server or DTS, so this can be left alone.  However, in your configuration, enter domain users if you are using these services.  Click Next.

image10

If you have Full-Text services running and are actively using them, you should probably leave the default, “Import” selected.  However, if you want to reset the build, select one of the other options.  Honestly, I have never used Full-Text services, so you might want to double-check the documentation on this one. Click Next when ready.

image11

image12

image13

The installation will look over its feature list and give you any warnings or errors before you continue.  On this installation, there was a database at a 2005 compatibility level. Since it was only a test database, I ignored the warning. Moreover, I was curious as to how the upgrade would affect it in the other compatibility level.  Click Next when it finishes.

image14

The installation now produces a summary of your choices.  You can go back or click Upgrade when you are ready.

image15

The upgrade will now proceed.  It ran about fifteen minutes on an my older Intel i5 server with 48GB of RAM.

image16

Once the Upgrade finishes, it will require a restart. Click Ok. This will not restart the machine, only warn you it needs to be done.

image17

image18

The final window will present you with everything the Upgrade accomplished, including any warnings or errors.  If you have errors, you will have to go back, fix the errors, and do a Repair on the installation. Click Close and reboot your machine.

Post-Install

After the reboot, verify that all the services have started:

imager1

Close this window if all the services above are running and any that you might have installed that are different than this installation.  We now need to get Powershell running.  To this, we can re-run the setup application.

imagep1

This time, click “Install SQL Server Management Tools.”  It will open a link to this web page: https://msdn.microsoft.com/en-us/library/mt238290.aspx

That same link will be needed on your workstation to gain all the benefits of the 2016. Download the file and click “Run”

imagessms1

Once the installation starts, click Install.

imagessms2

The installation will proceed on its own.

imagessms3

Click Restart.

Powershell

After you do the upgrade and install SSMS, you encounter the following error:

Restore-SqlDatabase : Cannot bind parameter ‘RelocateFile’. Cannot convert the “Microsoft.SqlServer.Management.Smo.RelocateFile” value of type

“Microsoft.SqlServer.Management.Smo.RelocateFile” to type “Microsoft.SqlServer.Management.Smo.RelocateFile”.

Run the following command to verify that the assemblies are pointing to the old version:

PS SQLSERVER:\> [AppDomain]::CurrentDomain.GetAssemblies().GetTypes() | ? FullName -eq Microsoft.SqlServer.Management.Smo.RelocateFile | select Assembly

Assembly

——–

Microsoft.SqlServer.SmoExtended, Version=13.0.0.0, Culture=neutral, PublicKeyToken=123456789123456

Microsoft.SqlServer.SmoExtended, Version=11.0.0.0, Culture=neutral, PublicKeyToken=123456789123456

To correct the issue, you will have to ignore most of what you find on the Internet.  Most of the solutions I found meant either re-installing the entire server or attempting a workaround for every powershell script you have.

The easiest way is to change the system environment variables to remove the unneeded SMO.  As you see above, both version 13 and version 11 are installed.  2016 made a large amount of enhancements to the powershell module (sqlps) and you will want to use them.  Moreover, you probably want to use commands like Restore-Database without having to the do the messy v1 workaround.  Bring up the advanced properties of system properties on the server:

imagesp1

Now click Environment Variables.

imagesp2

On the screenshot above, notice that we highlighted PSModulePath. We will want to edit it.

imagesp3

Remove the following line in the “Edit System Variable” Path:
C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\

Only the below SQL server value should exist:
C:\Program Files (x86)\Microsoft SQLServer\130\Tools\PowerShell\Modules\

The entire line looks like this:
%SystemRoot%\system32\WindowsPowerShell\v1.0\Modules\;C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\
Feel free to change the above to a higher level of powershell.

Once you make the change, reboot, and you should be good to go!