Upgrade SQL Server And Database To Higher Version

In this article, I will go over the steps to upgrade SQL Server 2016 and it’s databases to a higher version (SQL Server 2017). First you have to download the higher version of SQL Server and upgrade it. Then you have to upgrade the compatibility level of the per-existing database to the higher version.

Tools & Technologies Used

• OS: Windows 10 Pro
• Installed DB Server: SQL Server 2016 Developer Edition
• New DB Server: SQL Server 2017 Developer Edition

Download SQL Server 2017 Developer Edition

1. You can download SQL Server 2017 developer edition from several places.
   • The first option is to go to https://www.microsoft.com/en-in/sql-server/sql-server-downloads and download the installer (5.2 mb exe file) and navigate through the installer. The downside of this option is, you have to do the download on each a every computer where you want to install SQL Server.
   • The second and the best option to download the iso file from the Visual Studio Dev Essentials, so as you can reuse the setup file on any number of computers. Here I’ve used this option.
2. Login to Visual Studio Dev Essentials Program and go to Downloads. Select SQL Server 2017 and download the iso of either the developer edition or the express edition.
   
3. The file name of the downloaded iso file fo developer edition will be like en_sql_server_2017_developer_x64_dvd_11296168.iso.

Upgrade SQL Server

1. Mount the iso file by right clicking on it and selecting the Mount option.
   
2. Once mounted, go to the mounted drive and double-click setup.exe. This will open the SQL Server Installation Center.
3. Go to Installation from the left panel.
4. Press Upgrade from a previous version of SQL Server.
   
5. This will open the Upgrade to SQL Server 2017 screen. Select the preferred options and navigate through the upgrade process.
   
6. In the Select Instance screen, make sure the correct instance to be upgraded is selected and proceed further.
   
7. The upgrade process may take around 5 minutes depending on the speed of the computer. You will see the below screen once the upgrade is complete.
   
8. Now launch the Management Studio, connect to the upgraded instance and verify the version.

Upgrade Database

The above steps will only upgrade SQL Server, but not the database in it. The existing databases on the SQL Server, will not upgraded by itself. i.e. the compatibility level of the existing databases will be still on the older version. These databases will not use the new features available in the new version of SQL Server. So, you have to upgrade the compatibility level of the database to the higher version.

BEWARE: It is possible that some of the features from older version of SQL Server may not work as expected in the newer version. So, before increasing the compatibility level of the DB, go through the release notes and the changes between the two SQL server versions. After changing the compatibility level, do proper testing and make sure all the stored procedures, functions, views, triggers, indexes, and queries are working properly.

Follow the below steps to upgrade the compatibility level of the database.

1. Launch the Management Studio and connect the SQL Server instance. (NOTE: Make sure the login you are using has database ALTER permission. If not, the compatibility level field will be disabled and grayed out.)
2. Right-click the database and select Properties from the context menu.
3. In the Database Properties screen, select Options from the left panel.
4. From the right panel against the Compatibility level drop down field, select the upgraded version and press OK button.
   

The database is now upgraded. You have to test it and make sure everything is working fine. It’s also good to upgrade the SQL Server Management Studio. You can download and upgrade to latest version of Management Studio from https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms.