One of the nice new features in SCCM 1602 build is the support of SQL Server AlwaysOn availability groups. You can leverage this to host your site database (Primary site or CAS) for a HA (high availability) solution. Info on whats new for version 1602 can be found here – https://technet.microsoft.com/en-us/library/mt622084.aspx#bkmk_1602
I wanted to test this out. I had a Hyper-V lab environment running on Windows 10, and 4 x Windows Server 2012 R2 VM’s. I have one Primary Site running Configuration Manager build 1511 using a SQL database hosted on “03 – SQL 2012SP1 – Primary Replica” VM. “04 – SQL 2012SP1 – Secondary Replica” Has SQL 2012SP1 installed.
Upgrading to Configuration Manager 1602:
In order to use SQL AlwaysOn, Configuration Manager must be updated to version 1602. This can be done by opening the Configuration Manager console and navigating to Administration\Overview\Cloud Services\Updates and Servicing and installing the Configuration Manager 1602 Update Pack.
The “State” will change to Installed once complete.
Configuring SQL AlwaysOn
Before configuring SQL AlwaysOn Availaiblity Groups, the Configuration Manager site must be stopped. This can be done by using the “Preinst.exe /stopsite” command. Preinst.exe is located in \\<SiteServerName>\SMS_<SiteCode\bin\X64\00000409
SQL Availability groups only support Full for the backup model. To change this, right click on the Configuration Manager database in SQL Management Studio, select properties, select Options, then changed the Recovery model to Full.
On both of my SQL VM’s, I installed the Failover Clustering Feature. On my Primary Database (03 – SQL 2012SP1 – Primary Replica) I opened the Failover Clustering management tool and added both nodes to my Failover Cluster CL-SQL.
Once the nodes were members of a Failover Cluster, the AlwaysOn Availability Groups needed to be enabled from the SQL Server Configuration Manager. I right clicked on the SQL Server and clicked on Properties, then selected the Alwayson High Availability tab
The SQL Server service needed to be restarted once AlwaysOn Availability Groups was enabled. The Availability Group was configured by using the New Availability Group Wizard in SQL Management Studio.
Readable Secondary was set to Yes, and Automatic Failure disabled. During set up, only Manual Failover can be used. Under the Listener tab, I created an endpoint for Configuration Manager to connect to the SQL availability group. .
Full data synchronization must be done. The wizard will back up the primary database and transaction log, and restore it to the secondary replica SQL server.
On the Primary Database the following must be done:
Enabling TRUSTWORTHY Database Property
Enabling CLR Integration
On the secondary database, performed a manual failover from the SQL Management Studio by right clicking the Availability Group and selecting Failover:
Once the database has failed over, run Enabling TRUSTWORTHY Database Property and Enabling CLR Integration steps above again
Pointing Configuration Manager to use AlwaysOn Availability Group:
From the Configuration Manager installation directory (eg C:\Program Files\Microsoft Configuration Manager\bin\X64) I launched setup.exe and selected Perform Site Maintenance or Site Reset
Select Modify SQL Server configuration
Type in the SQL Availability Group Listener created before and click Next
Once complete, Configuration Manager will now be set to use the SQL AlwaysOn Availability Groups. In the SCCM console, “\Administration\Overview\Site Configuration\Servers and Site System Roles” there is now the SQL Availability Group Listener listed as Site Database Server roles.
The Failover method can be switched back from Manual to Automatic. However, when updates are applied to Configuration Manager, it must be set back to Manual.
The Failover method has been changed to Automatic in SQL Management Studio. I have powered off “SQL1” to replicate an un-planned failure while still using the Configuration Manager console. In the Failover Cluster Manager, it shows SQL1 as down, and now SQL2 is the Owner Node of the SQL Availability Group.
If the Failover was unsuccessful, the SCCM console should freeze and be unusable as it can’t connect to the SQL database (SQL1). If successful, the SCCM console should continue to function as normal.
This is how I set it up in my lab environment without any issues to test the feature. More information can be found on https://technet.microsoft.com/en-us/library/mt651651.aspx