How-to: Switching and Updating to SQL Server 2017 and 2019

Step 1: Disconnecting the database on an old server

Remember to disconnect the backup on the old server:

1. Open Microsoft SQL Server Management Studio.
2. Click on the plus behind databases.
3. Right-click the database and select Tasks > Detach

Step 2: Install the new Microsoft SQL Server

We recommend you install Microsoft SQL Server version 2019 and above. Check the system requirements for this and choose a different version if necessary. For more information, see Installatie SQL Server 2017/2019 Express - Nieuwe installatie / nieuwe instance (NL).

In the Instance Configuration section, choose Named Instance and fill in the Name (for example, SQLEXPRESS2019). Remember this name as it will be used later.

Step 3: Create a backup

In this step, you create a backup of your administration that is on the old version of SQL server. For this, you must first find the name of the old server.

It can be found in one of the following locations:

• C:\Program Files\Microsoft SQL server\
• C:\Program Files (x86)\Microsoft SQL server\

Then, look for a folder that looks like one of these (you may only have one of these):

In this example, the part in red is then the name of the server (the MSSQL11 section may vary by server version). You should look for a folder that begins with MSSQL11 or lower than 11. Also, remember the name of this server (we will refer to it as “the old server”).

Create a backup of the administration through the Microsoft SQL Management Studio. Before connecting, you must first connect to the old server. You do this by typing .\ or .\”old server name” in the Server name field in the pop up.

After you connect to the old server, you can back up your administration. For more information, see Een back-up maken/teruglezen via SQL Server Management Studio (Express) (NL). In this step, you can simply save the backup in the default path that is provided. Make a note of this path so that you know where the backup is located.

Step 4: Transfer the administration backup file to the new server

After creating a backup, navigate to the folder you just noted and copy the .bak file (this is called “your administration number”. bak). If it is not there, check again if the path is correct or create the backup again.

Next, paste the .bak file you just copied into the following folder: C:\Program Files (x86)\Microsoft SQL server\MSSQL15.SQLEXPRESS2019\MSSQL\Backup\

Step 5: Restore the backup

After this has been done, open Microsoft SQL Management Studio again. This time, connect to the new SQL server. You do this by filling in one of the following at Server name:

• .\
• .\SQLEXPRESS2019 (or whichever name you chose in step 1)

Then, continue to restore the administration. For more information, see Een back-up maken/teruglezen via SQL Server Management Studio (Express) (NL).

After you have successfully restored the administration, it will be available in the new SQL server.

Step 6: Open your administration

After you succesfully completed all previous steps, you can open the administration in your Exact software.

1. Open your Exact software.
2. On the start page, click Open.
3. Click Advanced at the bottom right

Then fill in the fields:

• User: This is your Microsoft Windows user and cannot be changed.
• Server: This is the new server name that has just been entered in the SQL Server Management Studio (.\”New server name”) in Step 1.
• Company: The company number of your administration (click the drop down arrow to find this).

The other fields are optional.

Then click Open to start your restored administration.

Updating the SQL server

1. Log in as an administrator on the server (of the workstation for a standalone installation).
2. Insert the SQL server installation DVD into the DVD drive. If the autorun does not execute, open the DVD drive in Windows Explorer and start the installation by clicking Setup.ext. The following screen will be displayed:



3. 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:



4. 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:



5. Select the I accept the license terms check box, and then click Next. The following screen will be displayed:



6. 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 2017 and 2019, 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(s). 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:



7. The Microsoft Update screen allows you to check for new update files online. If you select the Use Microsoft Update to check for updates (recommended) check box, the Product Updates screen will be displayed whereby you can download the files. Otherwise, the Install Setup Files screen will be displayed.



8. 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:



9. 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(s). Once the issue is solved, click Re-run to perform the checks again.

Note: Depending on the situation, it is not necessary to close the installation wizard and restart.

10. Click Next and the following screen will be displayed:



11. At Select Instance, 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 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, and version numbers that start with 13 apply to SQL Server 2016. The <Shared Components> line applies to the extra components of the SQL server that are installed, such as SQL Server Management Studio. Click Next and the following screen will be displayed:



12. The Reporting Services Migration screen is displayed only if the reporting services component is installed for the previous SQL server version.

Note: If Reporting Services is installed, this will be removed during the upgrade. Before you continue, back up your report server database, and your encryption key. Furthermore, Reporting Services for SQL Server 2017 and 2019 is done via a separate installation, and you have to migrate the reports. For more information, see How-to: Installing SQL Server Reporting Services (SSRS).

Once you have made a backup of the report server database, and the encryption key, you can select the Uninstall Reporting Service check box, and click Next to continue.



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



14. 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:



15. 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 2017 or 2019 services. You do not have to change anything. Click Next and the following screen will be displayed:



16. The Full-text Upgrade screen is displayed only if the Full-Text and semantic extractions for Search component of SQL server is installed. This screen allows you to select an upgrade option for this component. This component is applicable if you use the SQL full text search to search for feeds in the timeline in Exact Synergy Enterprise. For more information, see Defining social collaboration settings. For this Exact Synergy Enterprise functionality, it is not important which option is selected. If you are using the SQL full text search component for other purposes, check with your system administrator to determine which option to select. Click Next to continue.



17. 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(s). 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.



18. In the Ready to Upgrade screen, the list of upgrade and installation criteria will be displayed. Click Upgrade to start the upgrade and installation.



19. The Upgrade Progress screen displays the progress of the installation.
20. Once the installation has completed successfully, the installed components will be displayed with a green icon. Click Close to exit.
21. 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 2017 or 2019 is installed) SQL Server Configuration Manager. In Windows, go to Start, select All Programs, followed by Microsoft SQL Server 2017. Click Configuration Tools, and then SQL Server 2017 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 2017 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.

22. 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:
1. Start (on the server where MS SQL Server 2017 or 2019 is installed) SQL Server Configuration Manager. In Windows, go to Start, select All Programs, followed by SQL Server 2017. Click Configuration Tools, and then SQL 2017 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 2017 Configuration Manager on the left side of the screen.
2. Click SQL Server Services.
3. 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.
4. Right-click SQL Server Browser and select Properties.
5. In the SQL Server Browser Properties screen, click the Service tab.
6. At Start Mode, select Automatic, as displayed in the following screen:



7. Click Apply to apply the changes. The SQL Server Browser service will now start automatically every time the system is started.
8. Next, start SQL Server Browser.
9. In the SQL Server Browser Properties screen, click the Log On tab.
10. 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 2017 or 2019. The default port for SQL Server 2017 and 2019 is 1433. The SQL browser used UDP port 1434.