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
    2017-04-24_14-40-43.png
  4. Enter product key details and click next.
    2017-04-24_21-19-39
  5. Accept license terms and click next
  6. Check use microsoft update and click next
    2017-04-24_21-21-47.png
  7. Check all updates and click next
    2017-04-24_21-22-50.png
  8. Review pre-req check and click next
    2017-04-24_21-27-42.png
  9. Check database engine services and reporting services  and change the feature installation directory to the SQL directory, mines ‘E:\’
    2017-04-24_21-32-11.png
  10. Specify an instance, I’m using the default.
    2017-04-24_21-34-19.png
  11. Set all services to start with the service account created for SQL earlier ‘SA_SCCM_SQL’ and automatic except the SQL Server Browser
    2017-04-24_22-32-03.png
  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.
    2017-04-24_22-37-59.png
  13. Add SCCM Server Admins to administrators list
    2017-04-25_9-19-57.png
  14. Under Data Directories tab change the locations to the below directories.
    2017-04-25_9-23-17.png
  15. Under the TempDB tab change the data directory for the TempDB to your tempDB volume and the log.
    2017-04-25_9-25-17.png
  16. On Reporting Services select install only
    2017-04-25_9-27-29.png
  17. Click Install
  18. Confirm all components installed successfully
    2017-04-25_9-35-08
  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

    2017-04-25_9-40-58.png

  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
    2017-04-25_15-36-35.png
  22. Click Close
    2017-04-25_15-46-43.png
  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
    2017-04-25_17-51-13.png
  26. Select memory and change the minimum to 8192 and the maximum to 12288 (should be 80% of the servers memory)
    2017-04-25_18-41-19.png
  27. Open SQL Server Configuration Manager
  28. Browse SQL Server Network Configuration>Protocols for instance and right click TCP/IP>Properties
    2017-04-25_18-44-34.png
  29. Configure protocol as per the below
    2017-04-25_18-46-55.png
  30. Select IP Addresses tab
  31. Under IP1 set to the below settings
    2017-04-25_18-51-20
  32. All other IP entries and IP All should be configured as per the below
    2017-04-25_18-52-10.png
  33. Dynamic ports should be configured as per the below
    2017-04-25_18-53-27
  34. Restart the SQL Server Service
    2017-04-25_18-54-26.png
  35. Ready for ConfigMgr!
Advertisements

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:

 Script

Data Type:

 Integer

Discovery script:

$PSVersionTable.PSVersion.Major

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:

Value

The value returned by the specified script:

Greater than or equal to

The following values:

 3

Report noncompliance if this setting instance is not found

Yes

Noncompliance severity for reports

 Critical

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?