Install SQL Server 2019 for ConfigMgr – Bonus Guide
This post is a step-by-step guide to install SQL Server 2019 for ConfigMgr. The SQL Server 2019 is a supported SQL server version for ConfigMgr 1910 and above.
When I build my ConfigMgr lab, I always prefer to install the latest supported SQL server version. With every new release of SQL server you get performance benefits, new features and a faster experience. SQL 2019 is the latest version as of today and it is supported by ConfigMgr 1910 and above.
Each Microsoft Endpoint Configuration Manager site requires a supported SQL Server version and configuration to host the site database.
Now it’s true that you install SQL server only once for ConfigMgr. You then perform in-place upgrade of SQL Server. Until Microsoft tests and confirms the supported SQL Server version for ConfigMgr, do not install it or upgrade your SQL in production.
I would also like to mention that SQL Server 2017 and SQL Server 2019 installation is similar. However SQL Server 2019 setup includes the options to define or configure memory limit configurations for SCCM. While with SQL 2017, you have to configure the memory limits manually.
Cumulative Update CU5 is the minimum requirement for SQL Server 2019 as it resolves an issue with scalar UDF inlining. Always install the latest available cumulative update. You can install SQL Server 2019 for the following SCCM sites :-
- Central administration site
- Primary site
- Secondary site
Table of Contents
Download SQL Server 2019 for SCCM
SQL Server 2019 comes in 5 different editions and you can download the one that you require.
- Developer Edition
- Express Edition
- Enterprise Edition
- Standard Edition
- Web Edition
For a SCCM standalone primary site, you can either download SQL server 2019 Enterprise or Standard edition and install it. You can always download a trial version of SQL Server 2019 from SQL Server downloads page. You can also download the licensed version of SQL from MSDN or VLSC site.
What are the Supported SQL Server Versions for ConfigMgr
This is a very important question because before you download and install SQL Server 2019 for ConfigMgr, you must know what SQL Server version is supported for ConfigMgr.
Microsoft actually lists the supported SQL server version for all the current branch versions of Configuration Manager. I have also published a guide on supported SQL versions for ConfigMgr.
I recommend installing SQL and SCCM on one server. This is because SCCM talks to SQL database very often and installing both on one server improves the performance. You can give a thought about it and decide if you want the SQL database to reside on SCCM server or on a remote server.
Install SQL Server 2019 for ConfigMgr
Let’s install SQL Server 2019 for ConfigMgr. First of all mount the SQL Server 2019 media (ISO file). Run the SQL setup.exe as administrator. On the SQL Sever Installation center, click Installation tab.
Click New SQL Server stand-alone installation or add features to an existing installation.
SQL 2019 Install Rules
Before you install SQL Server 2019 for ConfigMgr, the install rules must be completed successfully. This section is where setup rules run and identify potential issues that might occur while running the SQL installation. If you notice any failure here, you must correct them.
Altogether 4 rules are tested here.
- Consistency validation for SQL Server registry keys
- Computer domain controller
- Windows Firewall
- SQL 2019 minimum CTP for Upgrade and Side by side support
Next to each rule you see a status indication. If you see passed as status you are good to proceed to next step. If you see any warnings or failure, you need to correct each of them.
Click Next.
SQL Server Feature Selection
On the Feature Selection window, you must select the features to install. There are lot of instance features available when you install SQL Server 2019 for ConfigMgr. However only the Database Engine Services feature is required for each site server.
Although you may select or add more features but that’s simply an overhead because ConfigMgr doesn’t use them.
Configuration Manager database replication doesn’t require the SQL Server replication feature. However, this SQL Server configuration is required when you use database replicas for management points.
After you select the instance features, at the bottom you get some options.
- Specify the instance root directory
- Shared feature directory
- Shared feature directory (x86)
By default the files are installed on the C: drive. If you wish to change the location, you may do that. I will be leaving it with default settings. Click Next.
SQL Server Instance Configuration
You must use a dedicated instance of SQL Server for each site. By default, SQL Server installs in the default instance unless you specify an instance name. Now the SQL server instance can be either a named instance or the default instance.
What is difference between SQL Server default instance and named Instance ?. A default instance is a type of instance that is used when installing a single instance of SQL server. Whereas a named instance is a type of instance where the user specifies an instance name when installing the instance.
Since I am installing SQL Server 2019 for ConfigMgr lab, I will go with Default Instance. So the instance ID will be MSSQLSERVER. Click Next.
SQL Server 2019 Server Configuration
The server configuration requires you to specify the service accounts and collation configuration.
When you install SQL Server 2019, there are 3 services listed under the service accounts.
- SQL Server Agent – Automatic
- SQL Server Database Engine – Automatic
- SQL Server Browser – Disabled
Optional – You can specify the service account name by clicking inside the account name box and clicking Browse. That should allow you to find and choose the AD service account name.
SQL Server 2019 Collation for ConfigMgr
This is a very import step when you install SQL Server 2019 for SCCM. Usually we configure service accounts and forget checking the SQL Server collation info. If you don’t specify the right collation, you may encounter the following error while installing Configuration Manager.
Configuration Manager requires that you configure your SQL Server instance and Configuration Manager site database (if already present) to use the SQL_Latin1_General_CP1_CI_AS collation, unless you are using a Chinese operating system and require GB18030 support.
You can refer the following guide to fix the SCCM Prerequisite Required SQL Server Collation issue.
The Configuration Manager requires site database to use the following collation: SQL_Latin1_General_CP1_CI_AS. Under the Collation tab, if you see SQL_Latin1_General_CP1_CI_AS, click Next. Otherwise click Browse and then select collation SQL_Latin1_General_CP1_CI_AS. Click Next.
Database Engine Configuration
On the database engine configuration page, there are many options that you can configure.
Server Configuration – You can specify the authentication mode and administrators for Database Engine. You get two options under Authentication mode.
- Windows Authentication mode
- Mixed more (both SQL Server Authentication and Windows Authentication)
I usually select Windows Authentication mode and add the current user to specify it as SQL Server administrator.
In the data directories tab you can specify the locations :-
- Data root directory
- System database directory
- User database directory
- User database log directory
- Backup directory
By default these location are on the C: drive. However you may specify other locations based on your requirements. For example, you can specify the user database directory and user database log directory on a different drive such as D: or E: on the same server.
You can specify the Temp DB data directories and log directory on TempDB tab.
Configure SQL Server 2019 Memory Limits for SCCM
This is a new option that we see in SQL Server 2019 and I am happy to see this because when you install SQL server 2017 for SCCM, you always had to configure memory limits after installing SCCM. But with SQL server 2019, you can configure the memory limits for SCCM from Memory tab.
In the Memory tab, you get two options – Recommended and Default. Specify the Min server memory and maximum server memory. If you choose the default option, it may list a high server memory.
I am going to specify Minimum SQL Server memory to 8GB and Maximum server memory to 16GB. When you install SQL server 2019 on your production server, you must consider the recommended hardware for Configuration Manager by Microsoft.
You must accept the recommended memory configurations for SQL Server database engine. If you specify the lower values than recommened ones, you may encounter SQL Server memory warning during Configuration Manager prerequisite check.
On the ready to install window, click Install.
We have successfully installed SQL server 2019 for ConfigMgr. Click Close.
Install SQL Server 2019 Cumulative Update KB5000642
As mentioned earlier, when you install SQL Server 2019 for ConfigMgr, you must also install the cumulative update. Starting with Configuration Manager version 1910, you can install SQL Server 2019 with cumulative update 5 (CU5) or later.
I always recommend installing the latest cumulative update available for SQL server 2019. At the time of writing this post, cumulative update Package 9 for SQL Server 2019 – KB5000642 is the SQL server 2019 latest cumulative update.
Download the cumulative update Package 9 for SQL Server 2019 and follow the below steps to install it.
Run the SQLServer2019-KB5000642-x64.exe as administrator. Accept the license terms on install SQL Server 2019 update window. Click Next.
You don’t need to select any addition features here because we already did that while installing SQL 2019. So click Next.
I have published a post on how to fix WmiPrvSE.exe process blocking SQL Server update. You can refer the post and try the solution. Click Next.
Finally on the ready to update page, click Update. This will now install SQL Server 2019 CU9.
We have successfully installed SQL Server 2019 Cumulative update 9. You must restart your server after installing CU9 update.
Find Installed SQL Server Version
It is a good practice to document the Configuration Manager version, SQL Server version and all the steps that you perform during SCCM setup. It will help you when you decide to upgrade your ConfigMgr infrastructure. I have published a guide on how to find the SQL Server version and also there is a YouTube tutorial for it.
Install SQL Server 2019 SSMS
This is an optional step because you may choose to install SSMS (SQL Server Management Studio) either on a SQL Server or on another server. Download the latest version of SSMS from Microsoft site.
Beginning with SQL Server Management Studio (SSMS) 18.7, Azure Data Studio is automatically installed alongside SSMS.
Run the SSMS-Setup-ENU executable as administrator.
The SQL Server 2019 SSMS installation is complete. Close the install wizard. In some cases the SSMS may require a server restart. If asked for a restart, do so.
You can now launch the SQL Server Management Studio and connect to your SQL Server.
ConfigMgr SQL Server 2019 FAQ’s
When you install SQL Server 2019 for ConfigMgr, you may have few questions and i will try to answer them here.
Is SQL Server 2019 Supported by Configuration Manager?
Yes, Configuration Manager 1910 and above supports SQL Server 2019. You must also install CU5 and above.
Does SCCM Includes SQL Server license?
SCCM includes SQL Server technology. Microsoft’s licensing terms allows your use of SQL Server technology only to support Configuration Manager components. SQL Server client access licenses are not required for that use.
Does SCCM require SQL Server?
Yes, Configuration Manager requires a 64-bit version of SQL Server to host the site database.
What Should be the default SQL Server Collation for SCCM?
While installing SQL server for sccm, the site database must use the following Database collation SQL_Latin1_General_CP1_CI_AS.
What SQL Server Features does SCCM require?
Only the Database Engine Services feature is required for each site server.
What are the SQL Ports required for SCCM?
The Intersite communications use the SQL Server Service Broker, which uses port TCP 4022 by default. And the Intrasite communications between the database engine and various ConfigMgr site system roles use port TCP 1433 by default.
SQL Server Memory Limit Configuration?
Limit the memory for SQL Server to 50 to 80 percent of the available addressable system memory.
Thanks for such a nice article, but while i was installing the sql server 2019 it gave me two more options, distributed replay controller and distributed replay controller. if you define them as well it will be great.
What is problematic at the moment is trying to do a MECM install using the latest versions of the underlying products. That is a SQL Server 2019 fail-over 2 nodes using iSCSI cluster running on Windows Server 2022 and the Site Server also being on Windows Server 2022. You have to start with the last baseline ISO which is 2103 and you just can’t get past the SQL Errors during the setup.
One being an “unsupported site server operating system version for setup” against the SQL Server cluster, with the additional text of “Configuration Manager site systems can only be installed on computers running supported operating systems. ”
And the second one being “Primary FQDN” again against the SQL Server with additional text “Configuration Manager only supports the use of primary FQDN’s, where the DNS hostname (first label in the FQDN) matches the NetBIOS hostname of the computer. The FQDN provided for this site system does not meet this requirement and cannot be used. Correct the FQDN and try again.”
I realise there is a new CM coming later this year but the current product is supposed to work on the 2022 releases (as site servers)
Hello,
Thank you for all the info here. I installed/used SCCM back in the 2012R2 version.
In my scenario, I will have a separate primary site server and database server.
I know for the installation of MECM/SCCM, the account I will be logged in doing the installation of MECM (not the DB install, the actual product) with has to have SQL Server administrators access.
After that installation is done, can that service account be removed from the SQL Server administrators group and given less permissions to run the day-to-day operations in MECM? I am trying to find guidance on this but I am unable to locate that permissions set that would be necessary (the minimum permissions needed on the SQL side to still allow MECM to work.)
Do you know if that is possible or does the account need to remain as an administrator to the database (SA access)?
Thank you again for all the great posts and your help.
With regards to the CMADMIN account – on a production server what Active Directory Group should it be in – in a test environment we used Domain Admin but now we’re installing on a production server what security group in AD should it be in?