SQL Server management for Microsoft Endpoint Configuration Manager (SCCM/MECM)

Microsoft Endpoint Configuration Manager needs to storage a lot of data, and we need to be able to access to those in the shortest possible time.

For that, we need to make several decisions like:

• Where the database will be allocated? remote or local to SCCM?
• How many clients will be supported?
• Do I need Hardware Requirements?

Recommended Hardware for Configuration Manager

SQL Partitions

• In this LAB scenario I’ll create partitions for less than 25000 clients on a remote SCCM/MECM SQL Server.
  • This server will have 1vCPU, 4096MB RAM and 200GB SSD aprox.
  • File NO_SMS_ON_DRIVE.SMS on every partition as best practice, the AD group of all our SCCM/MECM Sites will be a local admin in this server.
  • In every drive except for C:, we create a folder called “database” where we’ll allocate all the distinct databases.

Install SQL Server 2019 Database

We download and install SQL Server Standalone, and the latest update we might download as well.

• Considerations for SCCM:
  • Change Collation to SQL_Latin1_General_CP1_CI_AS
  • Set SCCM_SQL AD user to admin the SQL Services, it can start them automatically, this user must be unable to log on locally.
  • Set database path TempDB to G:\Database (SCCM_SQL_TempDB)

If we forget setting the collation we can remediate later doing this, with default SQL Instance name (MSSQLSERVER) and /QUIET parameter is optional.

Setup.exe [/QUIET] /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

Download Microsoft SQL Server 2019

Latest updates for Microsoft SQL Server 2019

Install SQL Server Management Studio (SSMS)

We install or update the application.

SQL Server Management Studio Download

https://aka.ms/ssmsfullsetup

Creating a database for SCCM

From Databases, we choose “New Database”

Here we modify the database name, the name for a SCCM installation must be like CM_XYZ, where XYZ is the site code.

Prepare to install Configuration Manager Sites > site codes

• Database name proposed for Taipei location will be CM_TPE
• SCCM_SQL_MDF database will size 1GB and 128MB of autogrowth.
• SCCM_SQL_LDF database will size 512MB and 256MB of autogrowth.

In options, we set the recovery model for this LAB to simple (+info).

Finally we add the database to the system.

Creating security logins

The logins will permit us to grant or deny access to the database engine, under Security we add a new Login:

• Login Name: Domain\SCCMServer$
• Server Role: +sysadmin
• Enabling Login and grant access to the database engine.

Install SqlServer powershell module

We start opening a powershell console, and list all the SQL modules available.

Get-Module -ListAvailable | Where-Object { $_.Name -like "*SQL*" }

We install the SQL Server module and allowing to substitute some commands with this new module if exist (-AllowClobber). I’ve decided to install this version.

https://www.powershellgallery.com/packages/Sqlserver

Install-Module -Name SqlServer -RequiredVersion 21.1.18245 -AllowClobber

We may update the documentation for SQL Server module.

Update-Help -Module SqlServer -Force

Later, we can list all the cmdlets available in our new module.

Get-Command -Module SqlServer -Type Cmdlet | Sort Name | More

And finally, we need to enter to the SQLSERVER drive in order to be able to execute commands.

Get-PsDrive
cd SQLSERVER:\sql\localhost\default
Get-SqlDatabase
Get-SqlLogin
...

Links:

https://docs.microsoft.com/en-us/mem/configmgr/core/understand/site-size-performance-faq

https://docs.microsoft.com/en-us/mem/configmgr/core/plan-design/configs/support-for-sql-server-versions