zagandesign - Fotolia

Manage Learn to apply best practices and optimize your operations.

SQL Server Installation Center steps for SQL Server 2016 upgrades

Consultant Michael Otey provides a step-by-step guide to upgrading from SQL Server 2014 to the 2016 release using the SQL Server Installation Center and Microsoft's upgrade wizard.

With the recent release of SQL Server 2016, current SQL Server users can start looking at possible upgrades. You...

can perform an in-place upgrade from any of the supported versions of the database to the new one using the SQL Server Installation Center. In this article, you'll see how to upgrade from SQL Server 2014 to SQL Server 2016.

First, mount the SQL Server 2016 installation media or ISO file on your SQL Server 2014 system and execute the Autorun program. Alternatively, launch the setup.exe program found in the installation media's root directory. Both steps will start the SQL Server Installation Center.

Before beginning the SQL Server 2016 upgrade process, run the Upgrade Advisor -- it will check your existing SQL Server system and database for compatibility with the new release and alert you to any issues that need to be addressed before or after upgrading. To run the advisor tool, click the Download Upgrade Advisor option on the Installation Center's Planning page (see Figure 1). After you use it to determine whether there are any compatibility issues, you'll be ready to upgrade your databases

Launching SQL Server 2016
Figure 1: Launching the SQL Server 2016 Upgrade Advisor.

To begin the upgrade process, click the Installation link in the navigation pane on the left side of the SQL Server Installation Center screen, then click the "Upgrade from a previous version of SQL Server" option on the Installation page (see Figure 2). This will launch the Upgrade to SQL Server 2016 wizard.

upgrade process
Figure 2: Starting the upgrade process in the SQL Server Installation Center.

The first thing the wizard does is display the Product Key dialog box, allowing you to either choose to install the free Evaluation edition or to enter your SQL Server 2016 product key. After entering the product key, click the Next button to display the License Terms screen. To proceed with the upgrade, click the box labeled "I accept the license terms." This triggers the Global Rules screen to display and automatically runs the rules test. Any issues that would prevent the upgrade from completing are listed in the Global Rules dialog box with a red or yellow icon. Before you can continue the upgrade, you need to correct any items with a status of Failed.

If all of the setup rules are passed, the upgrade wizard will automatically continue on to check for applicable product updates and then proceed through the installation of the SQL Server 2016 setup files and a check of the upgrade rules. As before, if there are issues that could interfere with or prevent the upgrade, the rules list will display a yellow icon or Failed status. If no issues are found with the upgrade rules, the Select Instance screen will display next, letting you choose which SQL Server instance to upgrade.

Select Instance screen
Figure 3: The Select Instance screen in the SQL Server upgrade wizard.

It's possible to have multiple instances installed on the same system -- SQL Server supports up to 50 -- and each named instance is essentially a different installation that can be upgraded separately. You can select different instances to upgrade by using the "Instance to upgrade" dropdown at the top of the Select Instance page. In Figure 3, you can see that the default instance named MSSQLSERVER has been selected.

Clicking the Next button then displays the Select Features screen, which shows you the installed features that will be upgraded (see Figure 4). When performing an in-place upgrade, you have to update all of the installed features in the selected instance at the same time, so the default setting in the dialog box can't be changed in this case.

SQL Server upgrade wizard
Figure 4: The SQL Server upgrade wizard's Select Features screen.

From there, you next go to the Instance Configuration screen, where you can enter the name of the database instance you're upgrading along with an instance ID (see Figure 5). This dialog also displays the components that will be upgraded and the installation directories for each of the subsystems.

Instance Configuration
Figure 5: The Instance Configuration screen as part of a SQL Server 2016 upgrade.

The next step in the process is the Server Configuration screen. It enables you to change the default startup type and authentication information for the database services that are part of the SQL Server environment. In the example shown in Figure 6, that includes SQL Server Integration Services, the Distributed Replay Controller, the Distributed Replay Client and the SQL Full-text Filter Daemon Launcher. The default values are retrieved from the existing configuration, and Microsoft recommends that you use a separate domain account for each service.

Server Configuration screen
Figure 6: The SQL Server upgrade wizard's Server Configuration screen.

If you installed the Full-text search feature on the SQL Server instance that you're upgrading, clicking Next will display the Full-text Upgrade screen, which allows you to specify how you want the upgrade process to deal with your full-text search catalogs. You can choose to import the existing catalogs without any changes or to rebuild or reset all of them.

Importing is the default option, but if the catalogs are left unchanged, they may not be able to take advantage of new full-text search functionality. Nonetheless, it's usually the best choice. Rebuilding the catalogs will add new functionality, but can considerably lengthen the upgrade process. The Reset option clears your existing full-text catalog data and will require you to manually rebuild the catalogs after the installation. Even if you do select Import, you can still elect to rebuild your catalogs following the upgrade.

The upgrade process continues with the Feature Rules dialog, which performs a final check for any conditions that might cause the upgrade process to fail. If error conditions are found, they will be shown in the Rule listing preceded by a red X. If all of the upgrade rules are passed, the Upgrade to SQL Server 2016 wizard will automatically display the Ready to Upgrade screen, which shows you a summary of the upgrade actions that will be performed (see Figure 7).

Ready to upgrade screen
Figure 7: The Ready to Upgrade screen, for a final check before upgrading.

If you need to change anything at this point, you can use the Back button to page through the previous upgrade dialogs. If you're all set, click the Upgrade button to launch the SQL Server 2016 in-place upgrade. The Upgrade Progress screen is displayed while the upgrade is in process. An upgrade usually takes several minutes to complete -- after it's finished, the Complete screen will be displayed (see Figure 8).

Complete screen
Figure 8: The Complete screen shows info after an upgrade has finished.

You can verify that the upgrade has been successfully completed by opening the Query Editor tool in SQL Server Management Services and running the following T-SQL query:


That will return the new SQL Server 2016 build number, production level and edition name. In addition, following the upgrade, you can create new database backups, add new features and rebuild the full-text search catalogs if needed to fully prepare your SQL Server 2016 database for production use. And if you have more databases to upgrade, you can go back to the SQL Server Installation Center and repeat the process outlined above as necessary.

Next Steps

Test your knowledge of SQL Server 2016 before you upgrade

SQL Server 2016 in-memory an improvement over 2014 release

PolyBase gives SQL Server 2016 easy access to Azure and Hadoop data

Dig Deeper on Microsoft SQL Server Installation