SQL
Structured Query Language (SQL) lets you access and manipulate databases. It became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987.
After installing and configuring the required prerequisites for Microsoft Endpoint Configuration Manager, it's time to install and configure the SQL Server 2019, so log into to the CM server, and we'll get started.
SQL Server 2019 has some known issues, for more information about that or supported SQL versions click here
Step 1. Mount the "SQL Server 2019" media to the CM server. Execute the "Setup.exe" from the media and click "New SQL Server stand-alone installation or add features to an existing installation"
Configuration Manager - SQL
Step 2. Enter a product key or select the "Evaluation" edition which will expire after 180 days. Click "Next"
Configuration Manager - SQL
Step 3. Check the "I accept the license terms and Privacy Statement" box and click "Next"
Configuration Manager - SQL
Step 4. Check the "Use Microsoft Update to check for updates (recommended)" box and click "Next"
Configuration Manager - SQL
Step 5. The below "Windows Firewall" warning is OK! But if you are getting any errors or warnings at this step, I would always advise you to read the details before proceeding with the installation.
Configuration Manager - SQL
Step 6. Select "Database Engine Services" and change the directory drive letter to "E:\" which is the ConfigMgr drive.
Configuration Manager - SQL
Step 7. Leave everything default and click "Next"
Configuration Manager - SQL
Step 8. Set the "SQL Server Agent" and "SQL Server Database Engine" services to run with the SQL service account that we created earlier and set the startup type to "Automatic"
Configuration Manager - SQL
Step 9. Click on the "Collation" tab. Set "Database Engine:" to use "SQL_Latin1_General_CP1_CI_AS" and click "Next"
Configuration Manager - SQL
Step 10. Select "Windows authentication mode". Add the "Current User" and "CM Administrators" group as SQL Server administrators.
Configuration Manager - SQL
Step 11. Click on the "Data Directories" tab and change the path for "User database" and "User database log" directory. Also make sure that the "Data root" and "Backup" directory is set to use the drive letter "E:\" which is the ConfigMgr drive.
Configuration Manager - SQL
Step 12. Click on the "TempDB" tab and change the path for "Data" and "Log" directory. During this step I made an mistake with the database sizing and I will show you how to correct it in the step "TempDB Correction" further down in this post.
Here's the correct approach during the setup wizard.
For estimating the database size, I am using a Excel file created by Kent Agerlund which can be downloaded here
I will create an database for 2000 Clients, 1 Processor, 8 Cores and 32 GB of Memory.
Configuration Manager - SQL
According to the Excel calculations, the TempDB values should be as shown in the table below. So, as already mentioned, change the path for "Data" and "Log" directory, add the below values and click "Next"
TempDB Data files | Value |
Number of files: | 8 |
Initial size (MB): | 567 |
Autogrowth (MB): | 256 |
TempDB Log file | Value |
Initial size (MB): | 2268 |
Autogrowth (MB): | 512 |
Configuration Manager - SQL
Step 13. Verify that the SQL Server 2019 features to be installed is correct and click "Install"
Configuration Manager - SQL
Step 15. Everything should be completed successfully. Click "Close"
Configuration Manager - SQL
Step 1. Download and execute "SSMS-Setup-ENU.exe"
Configuration Manager - SQL
Step 2. Change the drive letter of the location to "E:\" which is the ConfigMgr drive. Click "Install"
Configuration Manager - SQL
Step 3. Click "Restart"
Configuration Manager - SQL
Step 1. Log into to the CM server. Download and execute "SQLServerReportingServices.exe"
Configuration Manager - SQL
Step 2. Click "Install Reporting Services"
Configuration Manager - SQL
Step 3. Enter a product key or select the "Evaluation" edition which will expire after 180 days. Click "Next"
Configuration Manager - SQL
Step 4. Check the "I accept the license terms" box and click "Next"
Configuration Manager - SQL
Step 5. Click "Next"
Configuration Manager - SQL
Step 6. Change the drive letter of the location to "E:\" which is the ConfigMgr drive. Click "Install"
Configuration Manager - SQL
Step 7. Click "Close"
Configuration Manager - SQL
Step 1. Download and execute "SQLServer2019-KBXXXXXXX-x64.exe"
Configuration Manager - SQL
Step 2. Check the "I accept the license terms and Privacy Statement" box and click "Next"
Configuration Manager - SQL
Step 3. Leave everything default and click "Next"
Configuration Manager - SQL
Step 4. Click "Next"
Configuration Manager - SQL
Step 5. Click "Update"
Configuration Manager - SQL
Step 6. Click "Close"
Configuration Manager - SQL
When configuring the SQL server to use a domain account instead of the local system account, you must manually register the Service Principal Name (SPN) for the domain account.
Step 1. Log into the CM server with an Domain Administrator account. Type "CMD" in the search line next to the start button, and click "Command Prompt"
Configuration Manager - SQL
Step 2. Run the below commands to create the SPN.
# Change the HOST, DOMAIN and ACCOUNT name for each command
# Run the following commands to create the SPN
setspn -A MSSQLSVc/HOST:1433 DOMAIN\ACCOUNT
setspn -A MSSQLSVc/HOST.DOMAIN.LOCAL:1433 DOMAIN\ACCOUNT
# Run this command to verify the SPN creation
setspn –L DOMAIN\ACCOUNT
Configuration Manager - SQL
Now that we are done with the SQL server installation, it's time to configure it
😎
Step 1. Type "Microsoft SQL Server" in the search line next to the start button, and click "Microsoft SQL Server Management Studio 18"
Configuration Manager - SQL
Step 2. Right-click on the "CM (SQL Server...)" node and select "Properties"
Configuration Manager - SQL
Step 3. Select the "Memory" page and set "Minimum server memory" to 8192 MB and the "Maximum server memory" to 25600 MB which is 80% of the total system memory.
Configuration Manager - SQL
It´s not required, but I recommend creating the CM database before installing CM. As mentioned during the SQL server installation, I'm using a Excel file to estimate the database size.
For estimating the database size, I am using a Excel file created by Kent Agerlund which can be downloaded here
I will create an database for 2000 Clients, 1 Processor, 8 Cores and 32 GB of Memory.
Configuration Manager - SQL
According to the Excel calculations, the ConfigMgr DB values should be as shown in the table below.
ConfigMgr DB | Value |
Number of files | 8 |
Size per file | 1890 |
Autogrowth | 624 |
ConfigMgr Log | Value |
Size per file | 4990 |
Autogrowth | 512 |
I'm using "PS1" as my site code, but if you are going to use another site code or path for the database and log file, then please remember to change it in the SQL query below before executing it.
I've found my inspiration to this SQL query on a blog post created by SystemCenterDudes but please keep in mind that I'm no SQL query guru and it ain't perfect but it'll do
🤓
USE master
GO
CREATE DATABASE CM_PS1 ON ( NAME = CM_PS1_1, FILENAME = 'F:\Database\CM_PS1_1.mdf', SIZE = 1890, MAXSIZE = Unlimited, FILEGROWTH = 624)
LOG ON ( NAME = PS1_log, FILENAME = 'G:\Logs\CM_PS1.ldf', SIZE = 4990, MAXSIZE = 25600, FILEGROWTH = 512)
GO
ALTER DATABASE CM_PS1 ADD FILE ( NAME = CM_PS1_2, FILENAME = 'F:\Database\CM_PS1_2.mdf', SIZE = 1890, MAXSIZE = Unlimited, FILEGROWTH = 624)
ALTER DATABASE CM_PS1 ADD FILE ( NAME = CM_PS1_3, FILENAME = 'F:\Database\CM_PS1_3.mdf', SIZE = 1890, MAXSIZE = Unlimited, FILEGROWTH = 624)
ALTER DATABASE CM_PS1 ADD FILE ( NAME = CM_PS1_4, FILENAME = 'F:\Database\CM_PS1_4.mdf', SIZE = 1890, MAXSIZE = Unlimited, FILEGROWTH = 624)
ALTER DATABASE CM_PS1 ADD FILE ( NAME = CM_PS1_5, FILENAME = 'F:\Database\CM_PS1_5.mdf', SIZE = 1890, MAXSIZE = Unlimited, FILEGROWTH = 624)
ALTER DATABASE CM_PS1 ADD FILE ( NAME = CM_PS1_6, FILENAME = 'F:\Database\CM_PS1_6.mdf', SIZE = 1890, MAXSIZE = Unlimited, FILEGROWTH = 624)
ALTER DATABASE CM_PS1 ADD FILE ( NAME = CM_PS1_7, FILENAME = 'F:\Database\CM_PS1_7.mdf', SIZE = 1890, MAXSIZE = Unlimited, FILEGROWTH = 624)
ALTER DATABASE CM_PS1 ADD FILE ( NAME = CM_PS1_8, FILENAME = 'F:\Database\CM_PS1_8.mdf', SIZE = 1890, MAXSIZE = Unlimited, FILEGROWTH = 624)
Step 1. Open "Microsoft SQL Server Management Studio 18" and click on "New Query" from the toolbar. Copy / Paste my SQL query and make your changes before executing it.
Configuration Manager - SQL
Step 2. Verify that the database and log file has been created.
Configuration Manager - SQL
Configuration Manager - SQL
Step 3. From "Microsoft SQL Server Management Studio 18" right-click on the database "CM_PS1" and select "Properties". Verify that the collation is set to "SQL_Latin1_General_CP1_CI_AS"
Configuration Manager - SQL
Step 4. Click on the "Files" page and change the owner to "SA". Verify that the Size, Autogrowth / Maxsize and Path is correct.
Configuration Manager - SQL
Step 5. Click on the "Options" page, change "Recovery model" to use "Simple" and click "OK"
Configuration Manager - SQL
Okay, as I mentioned during the SQL server installation, I made a mistake with the TempDB creation, so here's my way to correct this afterwards.
Configuration Manager - SQL
According to the Excel calculations, the TempDB values should be as shown in the table below. Take these values an add them to the below SQL query.
TempDB DB | Value |
Size per file | 567 |
Autogrowth | 256 |
TempDB Log | Value |
Size per file | 2268 |
Autogrowth | 512 |
USE master
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'H:\Database\tempdb.mdf', SIZE = 567, MAXSIZE = Unlimited, FILEGROWTH = 256)
ALTER DATABASE tempdb MODIFY FILE (NAME = temp2, FILENAME = 'H:\Database\tempdb_mssql_2.ndf', SIZE = 567, MAXSIZE = Unlimited, FILEGROWTH = 256)
ALTER DATABASE tempdb MODIFY FILE (NAME = temp3, FILENAME = 'H:\Database\tempdb_mssql_3.ndf', SIZE = 567, MAXSIZE = Unlimited, FILEGROWTH = 256)
ALTER DATABASE tempdb MODIFY FILE (NAME = temp4, FILENAME = 'H:\Database\tempdb_mssql_4.ndf', SIZE = 567, MAXSIZE = Unlimited, FILEGROWTH = 256)
ALTER DATABASE tempdb MODIFY FILE (NAME = temp5, FILENAME = 'H:\Database\tempdb_mssql_5.ndf', SIZE = 567, MAXSIZE = Unlimited, FILEGROWTH = 256)
ALTER DATABASE tempdb MODIFY FILE (NAME = temp6, FILENAME = 'H:\Database\tempdb_mssql_6.ndf', SIZE = 567, MAXSIZE = Unlimited, FILEGROWTH = 256)
ALTER DATABASE tempdb MODIFY FILE (NAME = temp7, FILENAME = 'H:\Database\tempdb_mssql_7.ndf', SIZE = 567, MAXSIZE = Unlimited, FILEGROWTH = 256)
ALTER DATABASE tempdb MODIFY FILE (NAME = temp8, FILENAME = 'H:\Database\tempdb_mssql_8.ndf', SIZE = 567, MAXSIZE = Unlimited, FILEGROWTH = 256)
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'G:\Logs\templog.ldf', SIZE = 2268, MAXSIZE = Unlimited, FILEGROWTH = 512)
Step 1. Open "Microsoft SQL Server Management Studio 18" and click on "New Query" from the toolbar. Copy / Paste my SQL query and make your changes before executing it.
Configuration Manager - SQL
Step 2. Right-click on the database "tempdb" and select "Properties". Click on the "Files" page and make sure that the owner is set to "SA". Verify that the Size, Autogrowth / Maxsize and Path is correct.
Configuration Manager - SQL
Step 3. Verify that the database and log file has been changed.
Configuration Manager - SQL
Configuration Manager - SQL
We can make a few changes to the SQL Server Network Configuration to ensure a proper SQL communication.
Step 1. Type "SQL Server 2019" in the search line next to the start button, and click "SQL Server 2019 Configuration Manager"
Configuration Manager - SQL
Step 2. Expand the "SQL Server Network Configuration", right-click on "Protocols for MSSQLSERVER" and select "Properties". Ensure that "Enabled" is set to "Yes" and "Listen All" is set to "No"
Configuration Manager - SQL
Step 3. Click on the "IP Addresses" tab and find the IP setting containing the IP address of your CM server and make sure "Enabled" is set to "Yes". Set "Enabled" to "No" for the remaining IP settings.
Configuration Manager - SQL
This blog post won't be covering the Software Update Point (SUP) installation, but I'll prepare the WSUS server role and database for an upcoming blog post covering this part.
Step 1. Open the "Server Manager" and click "Add Roles and Features"
Configuration Manager - WSUS
Step 2. Click "Next"
Configuration Manager - WSUS
Step 3. Leave everything default and click "Next"
Configuration Manager - WSUS
Step 4. Leave everything default and click "Next"
Configuration Manager - WSUS
Step 5. Select "Windows Server Update Services". Leave everything default and click "Add Features"
Configuration Manager - WSUS
Step 6. Leave everything default and click "Next"
Configuration Manager - WSUS
Step 7. Click "Next"
Configuration Manager - WSUS
Step 8. Select "WSUS Services" and "SQL Server Connectivity" on the role services list. Click "Next"
Configuration Manager - WSUS
Step 9. Navigate to the ConfigMgr drive (E:\) and create a WSUS folder in the root.
Return to the "Add Role and Features Wizard"
Configuration Manager - WSUS
Step 10. Add the WSUS folder path and click "Next"
Configuration Manager - WSUS
Step 11. Enter the fully qualified domain name (FQDN) of your SQL server and click "Next"
Configuration Manager - WSUS
Step 12. Check the "Restart the destination server automatically if required" box and click "Install"
Configuration Manager - WSUS
Step 13. Click "Close"
Configuration Manager - WSUS
Step 14. When the WSUS server role installation is done, we should see a yellow triangle with an exclamation mark in the "Server Manager", click on it and then click "Launch Post-Installation tasks"
Configuration Manager - WSUS
The WSUS server role are now installed and we'll make a few changes to the WSUS database.
Step 1. Type "Microsoft SQL Server" in the search line next to the start button, and click "Microsoft SQL Server Management Studio 18"
Configuration Manager - WSUS
Step 2. Right-click on the database "SUSDB" and select "Properties". Click on the "Files" page and change the owner to "SA". Change the "SUSDB" autogrowth to 512 MB and limit the "SUSDB_log" to 10240 MB.
Click "OK" when done.
Configuration Manager - WSUS
As the final step, we'll make a few important changes to the WSUS Application Pool in the Internet information Services (IIS) Manager.
The computer that hosts a software update point requires the following configurations for IIS application pools.
- Increase the WsusPool Queue Length to 2000.
- Increase the WsusPool Private Memory limit by four times, or set it to 0 (unlimited).
Step 1. Type "IIS" in the search line next to the start button, and click "Internet information Services (IIS) Manager"
Configuration Manager - WSUS
Step 2. Navigate to the "Application Pools". Right-click on "WsusPool" and select "Advanced Settings..."
Configuration Manager - WSUS
Step 3. Change the "Queue Length" to 2000.
Configuration Manager - WSUS
Step 4. Change the "Private Memory Limit (KB)" to 0 and click "OK"
Configuration Manager - WSUS
This concludes the SQL installation and configuration. Go back to the main section by clicking on the link below or click "Part 4 - Setting up Microsoft Endpoint Configuration Manager" in the menu to the left.
Last modified 3yr ago