ConfigMgr and SQL AlwaysOn

Intro

 

How important is it to you and your company to have a highly available ConfigMgr solution? The answer to that is: it depends. It certainly does. I’ll leave that decisions to you.

So, what do we need to get ConfigMgr and SQL AlwaysOn to work?

  • SQL Server needs to be on a separate machine. Since we are talking high availability, ALL of the SQL Servers that ConfigMgr will use need to be on separate machines.
  • You need an Enterprise version of SQL. In fact, you will need at least two.
    • NOTE: SQL Server Standard does support one Availability Group, but it doesn’t support read access to the replica database and that is a requirement for ConfigMgr
  • During ConfigMgr setup, the availability group must be set to manual failover.
  • Any other availability group must be set to read the secondary AND manual failover. ConfigMgr setup will fail if this isn’t the case.
    • NOTE: Since WSUS and Reporting Services do not support availability groups, there really shouldn’t be any other Availability Groups on the SQL Server that hosts the CM database.

https://docs.microsoft.com/en-us/sccm/core/servers/deploy/configure/sql-server-alwayson-for-a-highly-available-site-database

 

According to the following article, SQL Server can be a default or named instance. DO NOT use a named instance. Just because something works doesn’t mean you should do it. Named instances (along with non-default ports) will make your life difficult. The article also states that ConfigMgr will support multiple instances of SQL server. I would advise against that as well. I have tried both and they seem to work. However, when I would install a ConfigMgr hotfix or new branch, ConfigMgr stopped working. Why? I’m not really sure. But, I don’t have any issues with a SQL default instance on regular SQL ports without any other instances.

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

Stuff to think about

 

Most likely you will be spinning up new SQL servers for your ConfigMgr high availability solution. Thus, now would be a good time to think about using Managed Service Accounts and Kerberos.

Managed Service Accounts

 

This article explains it all. https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions

Here are some examples.

NOTE: If this is the first time an AD managed service account is created, you will need to create the KDS root key.

Add-KDSRootKey –EffectiveImmediately

In this case the domain is EmptyGarden.btls. The account name is gmsaSQLCMCB and the AD Group that is allowed to use it is SQL-Server-CMCB

# Group Managed Service Account

New-ADServiceAccount
–Name
gmsaSQLCMTP
-DNSHostName
gmsaSQLCMTP.EmptyGarden.btls
-PrincipalsAllowedToRetrieveManagedPassword
SQL-Server-CMTP
-Enabled
$true

 

In this case the domain is EmptyGarden.btls. The account name is msaSQLCMCB and the computer that is allowed to use it is TEE-SQL- CMCB-1

# Managed Service Account

New-ADServiceAccount
-Name
msaSQLCMTP
-DNSHostName
msaSQLCMTP.EmptyGarden.btls
-PrincipalsAllowedToRetrieveManagedPassword
TEE-SQLCMTP-1$
-Enabled
$true

 

You will need to install the Active Directory module for Windows Powershell on the SQL server that will be using the account. Once that is done you can run this script to install the account

Install-ADServiceAccount
-Identity
gmsaSQLCMTP

 

You can now change the accounts that SQL Server is using to the managed service account you just created.

 

Kerberos

 

The word Kerberos also rhymes with gross. Keep that in mind!

For SQL to use Kerberos the account that is running SQL has to have a SPN. To list the SPN’s for an account type the following in PowerShell or a CMD prompt. (NOTE: you will want to replace the domain\username with your credentials. I really shouldn’t have to say this. If this confuses you, now would be a good time to go to a different website!!)

setspn -L EMPTYGARDEN\gmsaSQLCMTP$

To set the SPN’s type the following. Notice that there are two SPN’s created for each machine the account is used on. The NETBIOS and the FQDN along with the SQL Port. Also notice that the SQL AlwaysOn Availability Group is set as well.

setspn -A MSSQLSvc/TEE-SQL-CMCB-1.EmptyGarden.btls:1433 EMPTYGARDEN\gmsaSQLCMCB$

setspn -A MSSQLSvc/TEE-SQL-CMCB-1:1433 EMPTYGARDEN\gmsaSQLCMCB$

 

setspn -A MSSQLSvc/TEE-SQL-CMCB-2.EmptyGarden.btls:1433 EMPTYGARDEN\gmsaSQLCMCB$

setspn -A MSSQLSvc/TEE-SQL-CMCB-2:1433 EMPTYGARDEN\gmsaSQLCMCB$

 

setspn -A MSSQLSvc/TEE-SQL-CMCB-AG.EmptyGarden.btls:1433 EMPTYGARDEN\gmsaSQLCMCB$

setspn -A MSSQLSvc/TEE-SQL-CMCB-AG:1433 EMPTYGARDEN\gmsaSQLCMCB$

 

To verify the communcication to SQL is using Kerberos run the following query. NOTE: If you are on the same SQL server that you are running the query against the result will always be NTLM. You must connect to the SQL server from a different machine for Kerberos to work.

 

SELECT
@@SERVERNAME
AS instance,net_transport,auth_scheme

FROM sys.dm_exec_connections

WHERE session_id =
@@spid

 

Kerberos references:

https://technet.microsoft.com/en-us/library/cc280745(v=sql.105).aspx

 

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/register-a-service-principal-name-for-kerberos-connections

 

SQL Server AlwaysOn

 

Lucky for me, someone else already has a step by step article on this. Phew! Big thanks to Robert Marshall MVP for this writeup

http://wmug.co.uk/wmug/b/r0b/archive/2017/06/14/sqlalwayson-and-sccm

One thing to note: When applying a ConfigMgr hotfix or service update you MUST put the availability group in manual failover mode. Once the updates are applied you can put it back to automatic failover mode.