Exact Synergy Enterprise

How-to: Upgrading SQL Server 2016

Introduction

Before installing the SQL Server, check the system requirements for the specific SQL Server edition at https://msdn.microsoft.com/en-us/library/ms143506(v=sql.130).aspx.

Ensure the existing SQL Server supports updates. For a list of supported upgrade scenarios, see https://msdn.microsoft.com/en-us/library/ms143393(v=sql.130).aspx.

Note: Back up all your databases.

Upgrading the SQL Server

Log in as an administrator on the server (of the workstation for a standalone installation).
Insert the SQL Server installation DVD into the DVD drive. If the autorun does not run, open the DVD drive in Windows Explorer and start the installation by clicking Setup.exe. The following screen will be displayed:
Click Installation on the left panel, and then click Upgrade from a previous version of SQL Server on the right panel. The following screen will be displayed:
Select the Enter the product key: option and type the product key if the product key is not filled automatically. Click Next to continue. The following screen will be displayed:
Select the I accept the license terms check box, and then click Next. The following screen will be displayed:
Checks will be performed to identify the possible problems when installing the SQL Server Setup support files. The SQL Server Setup support files are needed for the installation of SQL Server 2016 and are automatically installed via this installation wizard. Click Show details >> to view the details of the checks. If all the checks are passed, the results will be displayed in the Global Rules screen. However, if a check has failed, a red icon will be displayed. You can click the hyperlink in the Status column to view the details of the check and error. Once the error is fixed, click Re-run to perform the checks again.
Note: Some warnings about the firewall might be displayed. Despite the warnings, you can still continue with the installation. Depending on the situation, it is not necessary to close the installation wizard and restart. If all the checks are passed, the Global Rules screen will close automatically and the following screen will be displayed:
The Product Updates screen allows you to check for new update files online. Click Skip Scan to skip this step. Click Next to continue. The following screen will be displayed:
The installation wizard will check for updates and install the available update files. Once the files are updated, the screen will close automatically and the next screen will be displayed:
In the Upgrade Rules screen, checks will be performed to identify the problems that might occur during the installation. Click Show details >> to view the details of the checks. If a check has failed, a red icon will be displayed. You can click the hyperlink in the Status column for more information on the check and error. Once the issue is solved, click Re-run to perform the checks again. Click Next and the following screen will be displayed:
Note: Depending on the situation, it is not necessary to close the installation wizard and restart.
At Instance to upgrade, select the instance of the SQL Server that you want to upgrade. The instance(s) will be displayed under the Installed instances section. The Instance Name column displays the name of the instance. The Version column displays the version of the instance. Version numbers that start with 9 apply to SQL Server 2005, version numbers that start with 10 apply to SQL Server 2008 (R2), version numbers that start with 11 apply to SQL Server 2012, and version numbers that start with 12 apply to SQL Server 2014. The <Shared Components> line applies to the extra components of the SQL Server that are installed, such as SQL Server Management Studio. However, the situation will depend whether the line will be displayed. Click Next and the following screen will be displayed:
The Select Features screen displays the components that will be upgraded. By default, the components that will be upgraded will be selected. You cannot change anything in the screen. This screen will close automatically.
In the Instance Configuration screen, you can define the name and instance ID of the SQL Server instance. Since you are upgrading the SQL Server, you will not be able to change the name of the SQL instance. The SQL instance will use the same name. You do not have to change the instance ID and you can accept the value displayed in the screen. Click Next and the following screen will be displayed:
Under the Service Accounts tab in the Server Configuration screen, you can define the account that you want to use to start the SQL Server 2016 services. You do not have to change anything. Click Next and the following screen will be displayed:
In the Feature Rules screen, checks will be performed to identify the problems that might occur during the installation. Click Show details >> to view the details of the checks. When a check has failed, a red icon will be displayed. You can click the hyperlink in the Status column for more information on the check and error. After you have fixed the issue, click Re-run to perform the checks again. The screen will automatically close after all the checks are successful.
Note: Depending on the situation, it is not necessary to close the installation wizard and restart.
In the Ready to Upgrade screen, the list of upgrade and installation criteria will be displayed. Click Upgrade to start the upgrade and installation.
The Upgrade Progress screen displays the progress of the installation.
Once the installation has completed successfully, the installed components will be displayed with a green icon. Click Close to exit.
To be able to access the SQL Server via a network, you have to activate some protocols. When working with a network environment, you have to check if the protocols are activated. If required, activate the protocols.
1. Start (on the server where MS SQL Server 2016 is installed) SQL Server Configuration Manager. In Windows, go to Start, select All Programs, followed by Microsoft SQL Server 2016. Click Configuration Tools, and then SQL Server 2016 Configuration Manager.
  In Windows 8, press the Windows key and the Q key on your keyboard. On the right at Search at the Apps field, type “SQL Server Configuration Manager”. Next, click SQL Server 2016 Configuration Manager.
2. Click SQL Server Network Configuration.
3. Select Protocols for xxxx. (“xxxx” has to be replaced by the name you have defined for the SQL Server during installation).
4. Right-click the protocols Named Pipes and TCP/IP and click Enable to enable these protocols.
You might also need to start the SQL Server Browser service. This service is used for identifying the ports that the installation of SQL Server listens to. When this service is not started, you can use the SQL Server only locally. To check if this service is started, follow these steps:

Start (on the server where MS SQL Server 2016 is installed) SQL Server Configuration Manager. In Windows, go to Start, select All Programs, followed by SQL Server 2016. Click Configuration Tools, and then SQL 2016 Server Configuration Manager.
In Windows 8, press the Windows key and the Q key on your keyboard. On the right at Search at the Apps field, type “SQL Server Configuration Manager”. Next, click SQL Server 2016 Configuration Manager on the left side of the screen.
Click SQL Server Services.
On the right side, the SQL Server Browser service is displayed.
Note: When it is not started, the icon is red. When it is started, the icon is green. Ensure that the service starts automatically so that every time the system is restarted, the service will also start.
Right-click SQL Server Browser and select Properties.
In the SQL Server Browser Properties screen, click the Service tab.
At Start Mode, select Automatic, as displayed in the following screen:
Click Apply to apply the changes. The SQL Server Browser service will now start automatically every time the system is started.
Next, start SQL Server Browser.
In the SQL Server Browser Properties screen, click the Log On tab.
Click Start to start the service. When the Start button is inactive, the SQL Server Browser service is already started and you do not have to start it.
Note: When you have a firewall, you have to configure the firewall to allow connections with SQL Server 2016. The default port for SQL Server 2014 is 1433. The SQL browser used UDP port 1434.

Installing SQL Server Management Studio

The next step is to install SQL Server Management Studio. SQL Server Management Studio is an application for managing the SQL Server. With SQL Server Management Studio, you can create and restore backups, change database settings, and give users SQL Server rights.

Log in as an administrator on the system where you have installed SQL Server 2016.
The setup file for SQL Server Management Studio can be downloaded from the Microsoft website (https://msdn.microsoft.com/library/mt238290.aspx). Place this file on the system where MS SQL Server 2016 is installed.
Start the installation by double-clicking the downloaded file.

The following screen will be displayed:

Click Install to start the installation.

During the installation, you can follow the progress of the installation. The following screens will be displayed. The installation can take some time.

When the installation is completed, the following screen will be displayed. You have to restart the system to complete the setup.

Preparing your database to use SQL Server 2016

Change the compatibility level of your upgraded databases

Go to Start ? All Programs ? Microsoft SQL Server 2016 ? Microsoft SQL Server Management Studio.
Connect to the SQL Server.
Expand the tree view until you see the databases.
Right-click a company database and select Properties.
In the Database Properties screen, select Options on the left panel and select SQL Server 2016 (130) at Compatibility level on the right panel, and then click OK.

Note: You have to change the Compatibility level for every company database.

Updating indexes and statistics

For optimal performance, it is important to update the indexes and statistics. To create these optimization jobs, you can use the ESI tool. For more information, see Exact System Information (ESI) help file.