We recently got an opportunity to update our MS SQL server as the current version was going out of support. So we noted down all the steps and issues faced during this activity.
We will be updating our database from SP1 to SP2 latest version. You can check the latest version for MS SQL 2016 here.
Download the SP2 version 13.0.5026.0 and then the latest cumulative update available at that time.
Pre-requisite steps
Stop your SAP system which is connected to this database using the SAP Management console and take a complete backup.
First, we will update the database to SP2. So, right-click on the “SQLServer2016SP2-KB4052908-architecture-language.exe” file and select ” Run as Administrator”
MS SQL server installation screens
Click on accept license terms and Privacy Statement and click “Next”
Select all the instances
In step “Check Files in Use”, it may ask to stop the “WmiPrvSE.exe” process manually. That we can do using “Task Manager”. Then click on “Refresh check” to check the files again. But sometimes, this will keep appearing. So you can ignore it and click “Next”.
On the Next screen, the Summary is displayed. Just verify and continue to the next screen.
The update will start and we can see the progress here.
Once done, you should a completion screen as below.
Close this window and restart the complete windows server. Otherwise, it will not allow starting the installation of the cumulative update.
Post restart, follow the same steps for the cumulative file of the latest version available.
Issues faced while updating MS SQL server
You may face the below error where the Database Engine Services (MSSQLSERVER) fails because the registry keys are not defined correctly
Error installing SQL Server Database Engine Services Instance Features
The User Data directory in the registry is not valid. Verify DefaultData key under the instance hive points to a valid directory.
Open the “Regedit” application as an administrator. So that we can check and correct the below registry keys required for this setup
DefaultData –> Pointing to <MS SQL directory>\Data
DefaultLog –> <MS SQL directory>\Log
SQLDataRoot
DefaultData and DefaultLog both reside under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MIcrosoft SQL Server\MSSQL.<version no.>.MSSQLSERVER\MSSQLServer
In my case, the value for DefaultLog was incorrect. Instead of pointing to Log, it was mentioned as “Data”. So we had to correct this value and save the key.
SQLDataRoot is present under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MIcrosoft SQL Server\MSSQL.<version no.>.MSSQLSERVER\MSSQLServer\Setup
Now re-run the setup file. Once Succeeded, Restart the complete server.
Verification
We can verify the MSSQL version using MS SQL Management Studio. Now, you may restart the SAP application again.