SCCM 1602 – SQL AlwaysOn

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

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.

alwayson2

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.

alwayson3

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.

alwayson4

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

alwayson5

alwayson6

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.

alwayson7

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

alwayson8

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.

alwayson9

alwayson10

On the Primary Database the following must be done:

Enabling TRUSTWORTHY Database Property

https://msdn.microsoft.com/library/ms187861(v=sql.120).aspx

Enabling CLR Integration

https://msdn.microsoft.com/library/ms131048(v=sql.120).aspx

On the secondary database, performed a manual failover from the SQL Management Studio by right clicking the Availability Group and selecting Failover:

alwayson11

alwayson12

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

alwayson13

Select Modify SQL Server configuration

alwayson14

Type in the SQL Availability Group Listener created before and click Next

alwayson15

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.

Failover Testing:

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.

alwayson16

alwayson18

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

 

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s