zagandesign - Fotolia
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.
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.
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.
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.
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.
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.
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).
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).
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.
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