SQL Server recommendations for Microsoft Endpoint Configuration Manager environment – White Paper

Updated 25/05/2020 :

GITHUB is now the new repository that hosts the new release of the document (2.6)
Kudos to Ryad Ben Salah / Benjamin Reynolds and Stephane Serero

Introduction 

As a Premier field engineer at Microsoft, we field many questions about SQL configuration for Configuration Manager environments, so we’re writing this to address some of them here.

Questions are often along the lines of “How should I properly configure SQL database settings in a Configuration Manager environment for the best performance?”, “What maintenance should I be running on SQL”, or “How often should I be running this maintenance?”

So, let’s look at these recommendations from a SQL Server perspective, applied specifically to a Microsoft Endpoint Configuration Manager (MECM) environment. The SQL recommendations for MECM – White Paper is available on GitHub and includes general best practices for SQL Server configuration and management, but also a recommendation applied in Microsoft Endpoint Configuration Manager context. 

This document is not intended to replace the official guidance produced by the Product Group but provides additional information not already in this document. It’s was also written based on field experience and customer feedback.

IMPORTANT!! Customers should test and adjust these settings according to their environment.

How are we going to configure SQL?

The SQL configuration is outlined in the SQL recommendations for MECM – White Paper to provide guidance to Microsoft Endpoint Configuration Manager (MECM) administrators and IT professionals on how to configure and administrate the SQL Server instances related to Microsoft Endpoint Configuration Manager.

The whitepaper has been written in collaboration by Microsoft SQL and MECM/ConfigMgr Premier Field Engineers (PFE)

This new release of the document in version 2.6 includes the following updates:

Chapters covered in the whitepaper:

The below chapters are covered in the whitepaper which deal with instance settings, database settings, global settings and maintenance plan considerations. Finally, some tips and guidance for monitoring and performance considerations. 

1.       SQL Instance settings   
         1.1        Maximum Degree of Parallelism (MaxDOP)     

         1.2        Memory            
         1.3        Database Collation       
         1.4        Additional instance settings             
                    1.4.1        SQL instance settings  
                    1.4.2        Trace Flag considerations    
                    1.4.3        Local Security Policies  
2.       Database Settings         
         2.1        TempDB and autogrowth settings
         2.2        Database compatibility level            
         2.3        Read committed snapshot isolation level   
3.       Global Settings 
         3.1        Optimize for ad hoc workloads         
         3.2        Forced Parameterization
         3.3        SQL Maintenance plan 
         3.4        Ola.Hallengren SQL Server Maintenance    
         3.5        Windows operating system configuration: power saving settings            
         3.6        Antivirus exclusion        
         3.7        Virtualization   
         3.8        Virtual Machines sizing
         3.9        Configuration Manager (current branch) site sizing guidance            
         3.10      SQL Server Reporting Services
         3.11      SCOM  
         3.12      MECM Custom Reports
                   3.12.1      Builtin Reports in Configuration Manager   
                   3.12.2      Custom Reports in Configuration Manager   
                   3.12.3      POP – System Center Configuration Manager Advanced Dashboards

                  3.12.4      POP - PowerBI Dashboard Integration for System Center Configuration Manager and Intune

                   3.12.5     SQL Custom Report  
         3.13      Management Datawarehouse             
                   3.13.1      Disk Usage        
                   3.13.2      Server Activity & Query Statistics  
                   3.13.3      Installation       
                   3.13.4      Configuration   
         3.14      Custom queries
         3.15      Supported Versions of SQL Server  
         3.16      WSUS  
                   3.16.1      MECM WSUS cleanup  

                   3.16.2      Rebuilding WSUS  
                   3.16.3      Shared Database for Configuration Manager Software Update Points  
         3.17      Always ON Availability Groups             

Special thanks and credit go to Stephane Serero (Lead), Ryad Ben Salah (contributor), Justin Manning (contributor), Benjamin Reynolds (reviewer), Keith Thornley (reviewer) and Umair Khan(reviewer) for their contribution to the whitepaper.