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.


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
    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


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.”


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


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


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


Click the checkbox to accept the agreement and click Next.


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.


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.


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.


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.


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.


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.




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.


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


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


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.



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.


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


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.


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”


Once the installation starts, click Install.


The installation will proceed on its own.


Click Restart.


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



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

Microsoft.SqlServer.SmoExtended, Version=, 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:


Now click Environment Variables.


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


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!