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.
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
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.