Let’s check the step-by-step guide to SQL Server 2019 Upgrade for SCCM Secondary site server database. I have been part of many in-place upgrades for SQL servers in SCCM infrastructure with the CAS, child primary, and secondary servers.
It’s important to understand the sequence (or order) that you should upgrade SQL servers in your SCCM environment. You should always start the SQL server upgrade with the CAS server. You have an SCCM hierarchy with CAS, Child Primary, and Secondary servers.
It would help if you upgraded the SQL server of the parent primary sites to the last. These SCCM primary sites include both child primary sites that report to a CAS and stand-alone primary sites that are the top-level site of a hierarchy.
- Upgrade SQL Server at the central administration site (CAS) first.
- Upgrade SQL Server at Secondary sites before you upgrade a secondary site’s parent primary site.
- Upgrade SQL Server at Parent Primary Server (includes child-primary and standalone primary) as the last option.
The new installation of the SQL server is explained in detail by Rajus OS. This guide is also helpful for the fresh or new SCCM Primary server-related SQL server 2019 installation. Rajul OS also explains the SQL server migration from one server to another.
I will share the ConfigMgr parent primary server SQL Server 2019 upgrade scenario in the later posts. So stay tuned!
- SQL Server 2019 Upgrade For SCCM Secondary Server (Part 1)
- Upgrade SSRS SQL Server Reporting Services to 2019 (Part 2)
- Upgrade SCCM Primary Server SQL Database to 2019 (Part 3)
- Install New SSRS Reporting Services Point in SCCM
SQL Server Edition for SCCM Secondary Server
Normally, all the secondary servers should be installed with SQL server Express edition. You don’t have to install SQL Server Standard or Enterprise edition for the secondary servers.
NOTE! – The SCCM DB size limit for SQL Express edition is 10 GB. Hence if you think your SQL DB on a secondary site will be above 10 GB, then it should be better to install SQL standard edition.
Download SQL Server 2019 and Cumulative Update
Let’s download SQL Server 2019 and Cumulative Update before upgrading and copy it to the relevant SQL server (SCCM secondary server).
You can download the SQL Server 2019 Enterprise, Standard, or Express edition from VLSC (volume licensing).
You will need to have the minimum CU5 for SQL Server 2019 to fix the known issues (scalar UDF inlining) with SCCM’s current branch.
Download – CU Cumulative Update Package 15 for SQL Server 2019 – KB5008996 https://www.microsoft.com/en-us/download/details.aspx?id=100809
Disable SCCM Services
You will need to disable SCCM Services before upgrading SQL Server to 2019. You can open the services.msc and stop + disable the services.
SQL Server 2019 Express Edition Source Files
This is only applicable for SQL Server 2019 Express edition source files. You need to extract SQL Server 2019 Express source files using the following method. The extracted files should be used for the SQL server 2019 express upgrade.
- C:\Sources\SQL\en_sql_server_2019_express_x64_e2d9f596\
Upgrade to SQL Server 2019
Let’s start the upgrade to SQL Server 2019 by clicking on Setup.exe and selecting the Installation option from the SQL Server Installation Center wizard.
- Click on Setup.exe.
- Click on Installation tab from SQL Server Installation Center.
- Click on Upgrade from a previous version of SQL Server.
This wizard helps launch a wizard to upgrade a previous version of SQL Server to SQL Server 2019. Don’t forget to take a snapshot of the VM before the upgrade to avoid additional risks and SQL DB issues after the upgrade.
Global Rules and Product Updates pages are completed and automatically skipped. The install setup files page was also completed pretty quickly. The tasks are completed or skipped quickly for SQL server 2019 Enterprise, Standard, or Express edition upgrade for the SCCM secondary server.
- Scan for product updates – Completed
- Download Setup Files – Skipped
- Extract Setup Files – Skipped
- Install Setup Files – Not started (Completed so quickly hence couldn’t take screenshot?)
The upgrade to SQL server 2019 completed the Upgrade Rules page automatically. The SQL Server Enterprise key will automatically populate the upgrade scenario if you have the license.
NOTE! – I don’t think you have to install the SQL server enterprise version for SCCM secondary server. The SQL Server 2019 Express or Standard version is more than enough for the secondary server.
Accept the license agreement for SQL Server 2019 and click on the Next button to continue.
You need to specify the instance of SQL Server to upgrade to SQL Server 2019. Select “Upgrade Shared Features” and click the Next button to upgrade only shared features.
NOTE! – Normally, you don’t have any shared features for SCCM secondary server. However, in this scenario, Client Tools Connectivity is Shared Features. You have only one instance for this scenario.
On this page, you can select the features that you want to upgrade to SQL Server 2019. However, it’s impossible to change the SQL Server features to be upgraded in this release.
- MSSQLServer – Database Engine Services.
- Shared Features – Client Tools Connectivity.
Instance Configuration – Specify the name and instance ID for the instance of SQL Server. Instance ID becomes part of the installation path. My lab environment has only one name and instance ID for the secondary server.
NOTE! – Do not install SQL on C:\ drive ever. This is my lab environment, and I couldn’t afford another drive for the SCCM secondary server; hence this is installed on C:\ drive.
Feature Rules setup is running rules to determine if the SQL Server 2019 upgrade process will be blocked or not. It’s recommended that you run Upgrade Advisor to analyze your initial installation of SQL completely.
NOTE! – The previous attempt to upgrade to SQL Server 2019 is failed at this stage because of SQL server edition mismatch. I tried to install SQL Server 2019 Express to upgrade from SQL Server 2014 Enterprise, and it failed on this Feature Rules check.
You can check the full list of SQL Server 2019 System Configuration Check Report – C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\Log\20220307_161249\SystemConfigurationCheck_Report.htm.
You can get the summary of SQL Server 2019 features upgraded from this page. Again, don’t install SQL server on C Drive (C:\Program Files) as explained above. Click on the Upgrade button to start the SQL Server 2019 upgrade process for SCCM secondary server.
Upgrade to SQL Server 2019 progress monitoring page helps you get the status of the upgrade of the SQL Server. There were log files explained below to get more details of SQL server upgrade activities in the background.
NOTE! – SQL Server 2019 Upgrade Log files – C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\Log\20220307_161249\Summary_MEMCMSecondary_20220307_161249.txt where 15 is the version number of SQL Sever 2019.
You can have a look at the SQL Server 2019 Upgrade Configuration file: C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\Log\20220307_161249\ConfigurationFile.ini. You can get the Detailed results from the below screenshot.
- Feature: Database Engine Services – Status: Passed.
- Feature: SQL Browser – Status: Passed.
- Feature: SQL Writer – Status: Passed.
- Feature: Client Tools Connectivity – Status: Passed.
The SQL Server 2019 Enterprise upgrade is completed successfully for SCCM (aka ConfigMgr) secondary server.
Install SQL Server 2019 CU15
You need to install SQL server 2019 cumulative update 15. SQL Server 2019 CU5 is the minimum requirement for SQL Server 2019 as it resolves an issue with scalar UDF inlining.
Download – CU Cumulative Update Package 15 for SQL Server 2019 – KB5008996 https://www.microsoft.com/en-us/download/details.aspx?id=100809
You don’t have to select the SQL Server 2019 CU15 upgrade scenario features. You need to click on the Next button to continue. The current patch version of SQL Server 2019 is 15.0.2080.9.
NOTE! – After the installation of CU15, the patch version will get changed to 15.0.4198.2.
- MSSQLServer – Database Engine Services.
- Shared Features – Client Tools Connectivity & SQL Client Connectivity SDK.
The Check Files in use page of CU15 update of SQL Server 2019 will help find out files used by some other services or applications. The following services and applications use the files that Setup needs to complete the installation.
NOTE! – The update wizard does not control the WmiPrvSE.exe process. You have to stop this process to avoid a computer restart manually.
Ready to Update – This SQL Server 2019 upgrade page will help verify the list of features to be updated. Click on the Update button to continue the update of CU15.
Monitor the SQL Server 2019 CU15 update progress from this page. You can also check the log file from the following location.
In this scenario, you need to restart the SQL Server (SCCM secondary server) if you get a message like the following: One or more affected files have operations pending. You must restart your computer to complete this process.
Click on the OK and Finish buttons to close the wizard. Proceed with the restart of the SQL Server.
SQL Server 2019 Upgrade and CU Update Log Files
You can check the SQL Server 2019 upgrade and CU update related log files from the following folder. Depending on the version, the folder structure will change.
- C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\Log
Within the LOG folder, you will have different folders based on the date and time of the SQL Server upgrade or CU update.
Check out the Detail.txt file to get the end-to-end details of the SQL Server upgrade or CU install. The sample entries of the Detail.txt are given below.
(01) 2022-03-08 07:46:37 Slp: Running Action: PendingRebootAction
(01) 2022-03-08 07:46:37 Slp: Reboot has been required
(01) 2022-03-08 07:46:37 Slp: Completed Action: PendingRebootAction, returned True
(01) 2022-03-08 07:46:37 Slp: ———————————————————————-
(01) 2022-03-08 07:46:37 Slp: Skipping Action: NotifyProgressComplete due to user cancel request
(01) 2022-03-08 07:46:37 Slp: ———————————————————————-
(01) 2022-03-08 07:46:37 Slp: Skipping Action: DetermineProductFeatureSummary
(01) 2022-03-08 07:46:37 Slp: Action is being skipped due to the following restrictions:
(01) 2022-03-08 07:46:37 Slp: Condition “Is the datastore flag /Scenario/Patching/IsRunningInnerSetup set” did not pass as it returned false and true was expected.
(01) 2022-03-08 07:46:37 Slp: ———————————————————————-
(01) 2022-03-08 07:46:37 Slp: Running Action: FinalizeProgressStatus
(01) 2022-03-08 07:46:37 Slp: Completed Action: FinalizeProgressStatus, returned True
(01) 2022-03-08 07:46:37 Slp: ———————————————————————-
(01) 2022-03-08 07:46:37 Slp: Running Action: RebootMessageAction
(01) 2022-03-08 07:46:37 Slp: One or more affected files have operations pending. You must restart your computer to complete this process.
Post Upgrade Secondary Server Services
Change the SCCM services to Automatic and Start those services, assuming that you have already restarted the secondary server to complete the SQL Server 2019 upgrade process.
NOTE! – At the start of the SQL server 2019 upgrade activity, we had disabled these SCCM related services. Here we are enabling those services back.
- SMS Agent Host
- SMS_EXECUTIVE
- SMS_NOTIFICATION_SERVER
- SMS_SITE_COMPONENT_MANAGER
Validation on SCCM Primary Site Side
You can navigate to \Monitoring\Overview\Site Hierarchy in the SCCM admin console to confirm whether the SQL based replication is working fine.
SQL Server 2019 Upgrade Verification
Let’s check and confirm SQL Server 2019 Upgrade status from application shortcuts.
You can check the SQL Server versions from ADD Remove program (appwiz.CPL from command prompt).
Let’s check the SQL Server version using the following SQL query from SSMS. It’s Microsoft SQL Server 2019 (RTM-CU15) (KB5008996) – 15.0.4198.2 (X64).
SELECT @@VERSION AS 'SQL Server Version'
Additional Details on SQL Server 2019 Upgrade Checklist
Let’s additional Details on SQL Server 2019 Upgrade Configuration Checklist. I have uploaded the SQL Server Upgrade checklist and CU install checklist to GitHub to give more visibility on the rules that are checked as part of the SQL upgrade scenario.
I hope this would be helpful for SQL Server 2019 upgrade troubleshooting and solving issues with the upgrade.
Author
Anoop C Nair is Microsoft MVP! He is a Device Management Admin with more than 20 years of experience (calculation done in 2021) in IT. He is Blogger, Speaker, and Local User Group HTMD Community leader. His main focus is on Device Management technologies like SCCM 2012, Current Branch, and Intune. He writes about ConfigMgr, Windows 11, Windows 10, Azure AD, Microsoft Intune, Windows 365, AVD, etc.