SCCM SQL AlwaysOn

With the release of SCCM Current branch 1602, ConfigMgr can be configured with high aviablity features based on SQL alwaysOn.

Before creating AlwaysOn Availability Group for SCCM make sure;

- Install the windows fialover cluster feature

- Install same version of SQL server database engine

- Enable Always on feature on all the participant servers

- Perform a full backup of the site database

The scenario is;

We have ConfigMgr (1602 or above) installed, on a standalone SQL server (SQLAOAG1).

Our requirement is to move this standalone ConfigMgr site to SQL AlwaysOn availability group.

For SQL AOAG purpose, we have an other standalone SQL Server (SQLAOAG2) installed and configured same as SQLAOAG1. On both the servers we have same version of SQL version, SQL file and folder stracture is created along with Windows Failover cluster.  SQLAOAG1 and SQLAOAG2 are part of the Failover group.

Prepare the site server for SQL AlwaysOn Availability Group: 

This step will be performed on the ConfigMgr site server.

First stop the site before configuring Availability Group. This is a requirement.

To stop the site launch a command prompt as administrator then run

  C:\Program files\Microsoft Configurationmanger\bin\x64\00000409\preinst.exe /stopsite

Will take few minutes before the site is stopped, When all the services are stopped you will see a message on the command line saying All site services are stopped, wait for the confirmation before proceeding further;

Creating SQL AlwaysOn Availability Group: 

This step will be performed on the site database server where the ConfigMgr site created the initial database.

Launch SQL Management studio on the first SQL server  and connect to one of the database engine;

On AlwaysOn High Available node, right click then select New Availability Group Wizard, which will open New Availability group creation wizard;

Click next on the introduction page;

On Specify Availability group options, Under availability group name, enter a desired name, for cluster type click on Windows Server Failover Clustering (if not already selected) then click next;

On select database window, you should see ConfigMgr database. 

If you have more then one database created on the SQL server you will see all of them. 

select only ConfigMgr database then click next; 

On Specify Replicas page, under Replicas the primary replica will be already added to the replica list. 

Select Automatic Failover and synchronous commit options

Click on Add replica to add second SQL replica, when prompted second SQL server name then click connect. The seconds SQL server will be added to the replicas list. 

 Make sure Automatic Failover and synchronous commit options are selected.

On the Listener tab, select Create an Availability group listener then enter a desired name for Listener DNS name, then enter the port number which is 1433 then select static IP for network mode then click next;

On select Initial Data Synchronization, select Full database and log backup then provide a location to store the backup files then click next;

A validation tests will run. If you see any issues, they must be resolved before proceed further. if no issues found on the validation then click next;

Review the selection summary then click finish when done;

The configuration will start, once the configuration is done you will see the results page The wizard completed successfully.

Now refresh the AlwaysOn High availability node in SQL Management Studio.
We should be able to see the newly created AlwaysOn High availability Group. Right click on AlwaysOn High availability Group then select show dashboard;

The dashboard will show the details of replicas and status of them;

In the SQL management studio click on new query then type
ALTER DATABASE CM_XXX SET TRUSTWORTHY ON
XXX is the site code.

Also run below query;
go
sp_Configure 'show advanced options', 1;
go
sp_configure 'clr enabled', 1;
go
reconfigure;
go
You will see command completed successfully message in the messages panel;

To perform Failover to seconds node; click on start Failover wizard on the top right hand corner in the dashboard, then click on the introduction page;

Select the listed server instance under select the new primary replica for this availability group then click next;

Enter username and password then click Connect on Connect to replica page then click next;

 Review the summary of the Failover then click Finish;

Progress will be displayed and you should see success in the the result;

For a very short time, the server instances in AOAG will show as not synchronizing during the Failover.

Very soon, this will turn healthy;

Perform below steps on node 2 (SQLAOAG2)
In the SQL management studio on the seconds node click on new query then type
ALTER DATABASE CM_XXX SET TRUSTWORTHY ON
XXX is the site code.

Also run below query;
go
sp_Configure 'show advanced options', 1;
go
sp_configure 'clr enabled', 1;
go
reconfigure;
go
You will see command completed successfully message in the messages panel;

At this stage, AlwaysOn availability groups in SQL for SCCM been configured and ready to use.

Before we run the site maintenance make sure that the Availability replicas Failover mode is set to Manual

To set the Availability replicas Failover mode to Manual, Right click on Availability group then select Properties;

Then check the Failover mode;

On the Primary site server, go to ConfigMgr install directory then bin\x64 then launch setup.exe as administrator;

Click next on before you begin page;

Under available setup options select Perform site maintenance or reset this site then click next;

On site maintenance page select Modify SQL server configuration then click next;

On database information page, under SQL server name enter the FQDN of the listener then click next;

You will receive a prompt "Before moving the site database into a SQL availability group, Please ensure you have reviewed the relevant documentation."
Click on Begin install

The site reset will start and will take 10-20 to complete the task;

Review the status by opening the log from view log tab;

Upon completion of the maintenance, the log file should show Completed Site Maintenance.

Now open the SCCM console and it should work without any errors. In SCCM console go to servers and site system roles node, you can see the listener server there;

Once the maintenance is completed successfully, change the availability group Failover mode from Manual to Automatic.
If there is an issue with the one node, it will automatically Failover to the next node without any manual process.
Note: Automatic Failover is not supported when patching the servers and restart.

Also refer to the complete guide for SCCM SQL Alwayson Availability Group guide.