Part 4: Installing SQL 2016

In the previous posts we’ve setup the lab and done the prep work for the SCCM Primary site. In my lab I’m installing SQL on the same server as the Primary Site server (SCCM-P01). There’s a fair bit of healthy debate as to whether it’s better to co-host or have a dedicated standalone SQL server. I’ve done both and can say that in my experience any performance improvement is negligible for the size environments I’ve seen it in.

So let’s get to it, jump on the server you’re going to install SQL on.

  1. Download SQL Server Standard, I’m using 2016. You can use any of the versions listed here.
  2. Run Setup.exe
  3. Click New Installation
  4. Enter product key details and click next.
  5. Accept license terms and click next
  6. Check use microsoft update and click next
  7. Check all updates and click next
  8. Review pre-req check and click next
  9. Check database engine services and reporting services  and change the feature installation directory to the SQL directory, mines ‘E:\’
  10. Specify an instance, I’m using the default.
  11. Set all services to start with the service account created for SQL earlier ‘SA_SCCM_SQL’ and automatic except the SQL Server Browser
  12. Select the collation tab and set it to ‘SQL_Latin1_General_CP1_CI_AS’. This is critical and if it’s configured incorrectly it can lead to a failed installation, unsupported by Microsoft and may prevent updates installing for SCCM.
  13. Add SCCM Server Admins to administrators list
  14. Under Data Directories tab change the locations to the below directories.
  15. Under the TempDB tab change the data directory for the TempDB to your tempDB volume and the log.
  16. On Reporting Services select install only
  17. Click Install
  18. Confirm all components installed successfully
  19. Set SPN by running the following commands:
    setspn -A MSSQLSvc/SCCM-P01:1433 LAB\SA_SCCM_SQL
    setspn -A MSSQLSvc/SCCM-P01.lab.local:1433 LAB\SA_SCCM_SQL


  20. SQL 2016 doesn’t install management studio as part of the install so you need to download and install manually. You can download it here.
  21. Click Install
  22. Click Close
  23. Configure Memory allocation
  24. Open SQL Server Management Studio (with an account that has admin rights to your SQL instance)
  25. Right click the server in object explorer and select properties
  26. Select memory and change the minimum to 8192 and the maximum to 12288 (should be 80% of the servers memory)
  27. Open SQL Server Configuration Manager
  28. Browse SQL Server Network Configuration>Protocols for instance and right click TCP/IP>Properties
  29. Configure protocol as per the below
  30. Select IP Addresses tab
  31. Under IP1 set to the below settings
  32. All other IP entries and IP All should be configured as per the below
  33. Dynamic ports should be configured as per the below
  34. Restart the SQL Server Service
  35. Ready for ConfigMgr!

SCCM Compliance – PowerShell version

One of the first things I always like to setup in compliance is a CI which checks for a minimum version of PowerShell. This is especially helpful when you’re writing advanced compliance scripts in PowerShell and they’re not running as expected across some of your environment. In my experience this is usually because some of the devices have an old version of PowerShell.

Compliance Settings:

Setting Type:


Data Type:


Discovery script:


Remediation script:

I’m not using a Remediation script, better to just deploy Windows Management Framework across you’re environment and use compliance as validation.

Make sure that Run scripts by using the logged on user credentials is ticked.

Compliance Rules:

Rule Type:


The value returned by the specified script:

Greater than or equal to

The following values:


Report noncompliance if this setting instance is not found


Noncompliance severity for reports


SCCM Compliance: Where to start

Compliance in SCCM is one of the most powerful and overlooked features, ultimately your imagination is the only limit to what it can do. There’s a whole range of ways you can use compliance but the most powerful is PowerShell. There’s a few things you want to do before you start building any Configuration item’s or baselines though.

  1. Change your PowerShell execution to Bypass in SCCM client settings
  2. Deploy Windows Management Framework 4.0 – Don’t reinvent the wheel just deploy it as an application using scripts ‘ wusa.exe Windows6.1-KB2819745-x64-MultiPkg.msu /quiet /norestart’
  3. Check PowerShell version across the board using compliance

To get started some great things to use compliance for include:

  1. SOE related settings
  2. Are core apps installed and healthy?
  3. Is AV running?
  4. Are your certificates installed on all systems?