Introduction

Configuration Manager 1602 introduced the support of SQL Server AlwaysOn Availability Groups. Now we can host the Central/Primary Site databases into Availability Groups for high availability and disaster recovery purposes.

Note - This feature is supported for existing Site DBs and cannot be used during the initial ConfigMgr setup.

Environment

Here's a snapshot of the environment I am using to demonstrate this feature.

Domain Controller

DDC1

Windows Server 2012 R2

SQL Server

SQL1

Windows Server 2012 R2

SQL Server

SQL2

Windows Server 2012 R2

Primary Site

PRIMARY1

Windows Server 2012 R2

SQL Configuration

Both the SQL Servers are running identical configuration.

Edition

SQL Server 2014 Enterprise Sp1

Features

Database Engine

Management Tools

Instance

MSSQLSERVER (Default)

Port

1433 (Default)

Service Account

Domain Account

Mode

Windows Authentication

If you have a scenario where you can't use the same version, follow the steps in this TechNet link under section Known Issues.

SCCM Configuration

Here's a snapshot of the ConfigMgr environment.

Type

Standalone Primary

Version

1511

Upgrade

1602

Roles

MP & DP Local on Site Server

SQL

SQL1 (Remote)

Site Code

PS1

Mode

HTTP

Prerequisite for SQL AlwaysOn

Assuming you have a healthy ConfigMgr environment, we will now proceed with the introduction of SQL AlwaysOn.

Install Feature - Failover Clustering

On all the SQL servers participating in AlwaysOn group, install the Failover Clustering feature.

Failover Cluster Configuration

Open Failover Cluster Manager and click Create Cluster

Add all the SQL Nodes participating in SQL AlwaysOn availability group

Click Next

Specify a Cluster Name. (This name will not be used by ConfigMgr)

Specify an IP Address and click Next

Uncheck the box Add all eligible storage to the cluster and click Next

Validate a successful creation on cluster and click Finish

Based on the number of nodes, configure the Cluster Witness as appropriate.

Enable SQL Server AlwaysOn Availability Groups Feature

On all the SQL servers participating in AlwaysOn group, perform the following steps.

Open SQL Server Configuration Manager.

Double-click the SQLServer (MSSQLSERVER) service to open the Properties dialog box.

In the Properties dialog box, select the AlwaysOn High Availability tab.

Check the Enable AlwaysOn Availability Groups check box.

Click OK and this will prompt you to restart the SQL Server service.

Manually restart the SQL Server Service

Prepare ConfigMgr for SQL AlwaysOn

Add the computer account of the Primary Site server to the Administrators group on each SQL server participating in SQL AlwaysOn availability group.

Add the Installation account to the Administrators group on each SQL server participating in SQL AlwaysOn availability group.

Add the Installation account to sysadmin role on each SQL server participating in SQL AlwaysOn availability group.

Open a command prompt window and navigate to the following directory - E:\Program Files\Microsoft Configuration Manager\bin\X64\00000409

Run the following command - Preinst.exe /stopsite

Once all the components are stopped proceed to the next steps.

Change Backup Model to Full

On the existing SQL Server -

Open SQL Server Mgmt Studio and go to the Properties of the Site Database and click Options tab.

Click on Listener Tab and select option Create an availability group listener. We need a name that ConfigMgr will use as connection string to connect to any of the SQL server participating in SQL AlwaysOn availability group.

Add Listener DNS Name, Port 1433 and a Static IP

Click Next to Select Data Synchronization. Keep Full and specify a shared path accessible by all replicas.

This share will be used to save the backup of the ConfigMgr database and for restoring to the replicas.

Click Next to complete the validation.

Confirm selections and click Finish on the wizard

The automated process is now going to Create Logins, Configure Endpoints, Backup and Restore Primary Site database on each SQL server participating in SQL AlwaysOn availability group.

Click Close once the operation completes.

Validate the newly created Availability group via the AlwaysOn Dashboard.

In SQL Server Mgmt Studio, expand AlwaysOn High Availability node, expand Availability Groups, the group we created ConfigMgr. Right click Show Dashboard.

Note the Primary instance is SQL1 and Secondary is SQL2 which is the Replica.

Configure SQL for ConfigMgr DB

Run the attached script to validate a healthy ConfigMgr DB configuration. Here's a resultant snapshot of the expected output when you execute the query.

When you perform a backup and restore operation on a new SQL server, it may lose some of the configurations highlighted below -

You need this DB configuration on each SQL AlwaysOn replica. Before you execute the remediation SQL script, you need to failover the DB to that particular node. Follow the steps below to perform the failover.

Failover

Now we need to manually failover the Primary to a Secondary replica.

In SQL Server Mgmt Studio, expand AlwaysOn High Availability node, expand Availability Groups, the group we created ConfigMgr. Right click Failover.

In the Fail Over Availability Group Wizard select a Secondary Replica Server and click Next

Hi Arnab,
Thanks for this post. There are a number of unforeseen steps that we encountered in our migration to a SQL cluster. Including, the creating a certificate for the Listener’s FQDN, installing that certificate on each member of the SQL cluster, giving permission to the service account used to launch SQL Server to that certificate and setting the SQL Server’s Network Protocols to Force Encryption.

Although we completed all these steps, and Setup.exe ran successfully, we are still seeing intermittent errors in the SQL Logs “Setting database option ENABLE_BROKER to ON for database ‘CM_PR1’ as well as login errors for Login failed for user ‘FQDN_MP$’. Reason: Failed to open the explicitly specified database ‘CM_PR1’. [CLIENT: IP]. On the smsdbmon.log side, we see *** [HY000][0][Microsoft][SQL Server Native Client 11.0]Unspecified error occurred on SQL Server. Connection may have been terminated by the server. Random database connection drops follow and come back shortly.

I didn’t use the SSL Certificates for Listener’s as its not a requirement. Regarding the SQL Login errors i suspect the DB was copied manually to each node instead of using the wizard and file share which automatically copies the DB along with logins and the permissions. For record, i don’t see any login error.

For the SQL Native Client errors in SMSDBMon.log i noticed those every hour for 10 hours post installation and nothing post that. I have created Apps, Packages and Test deployments successfully.

Hi Sam,
Thank you for this excellent post!
Is it possible to install the SUS DB and Reporting DB also on the listener address?
Or how do I make sure that in case of a failover Software Updates and Reporting are still working?
Thank you!

Hi there,
Thank you for your post. I have installed a 1610 environment in pre-prod and connected it to a SQL Cluster. All is working as expected. Now The SQL DB’s would now like ot use AlwaysON. We have followed the steps above (The SQL DBA doing the SQL part) and I’ve simply performed the stop SCCM Site, and run setup.exe , modify the SQL configuration and pointed it to the new listener name. The GUI setup process fails saying “The setup-command line option is not valid on this computer.”