Step-By-Step: Configuration Manager Site Database hosted on SQL Server AlwaysOn Availability Group


 

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.

clip_image002

Failover Cluster Configuration

  • Open Failover Cluster Manager and click Create Cluster
  • Add all the SQL Nodes participating in SQL AlwaysOn availability group
  • Click Next

clip_image004

  • Specify a Cluster Name. (This name will not be used by ConfigMgr)
  • Specify an IP Address and click Next

clip_image006

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

clip_image008

  • Validate a successful creation on cluster and click Finish

clip_image010

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

clip_image012

  • 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

clip_image014

 

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\X640000409
  • Run the following command – Preinst.exe /stopsite

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

Change Backup Model to Full

  • Open SQL Server Mgmt Studio and go to the Properties of the Site Database and click Options tab.
  • Change Recovery model = Full

clip_image016

 

Create and Configure SQL Server AlwaysOn Availability Groups

  • Open SQL Server Management Studio. Connect to the SQL Server instance
  • Click Always On High Availability and right click to select New Availability Group Wizard.

clip_image018

  • Click Next in the Availability Group Wizard
  • Specify an Availability Group Name

clip_image020

  • Click Next to Select Database. Ensure status is Meets prerequisites. It checks the recovery model = Full

clip_image022

  • Click Next to Specify Replicas. Click Add Replica to add each SQL server participating in SQL AlwaysOn availability group.
  • Check the box for Synchronous Commit
  • Set Readable Secondary = Yes

These are requirements for ConfigMgr specified in this link https://technet.microsoft.com/en-US/library/mt651651.aspx

clip_image024

  • Click Endpoints tab to verify each SQL server participating in SQL AlwaysOn availability group.

clip_image026

  • 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

clip_image028

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

clip_image030

  • Click Next to complete the validation.

clip_image032

  • Confirm selections and click Finish on the wizard

clip_image034

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

clip_image036

 

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.

clip_image038

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

 

DB Reconfiguration

We need to Set Trustworthy and enable CLR Integration. Run this against Primary Replica (You can confirm from the AlwaysOn dashboard)

Run the following SQL Statements –

–Set Trustworthy

ALTER DATABASE CM_PS1 SET TRUSTWORTHY ON

–CLR Integration

use CM_PS1
go
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘clr enabled’, 1;
GO
RECONFIGURE;
GO

 

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

clip_image040

  • Click Connect on the Instance and click Next

clip_image042

  • Verify the settings and click Finish

clip_image044

Verify the successful completion.

clip_image046

Open the Availability Dashboard to confirm a successful failover

clip_image048

SQL2 is now the Primary Instance and SQL1 is Secondary.

  • Follow the DB Reconfiguration Steps to Set Trustworthy and enable CLR Integration. (The 2 SQL statements)
  • Perform the above step against each SQL server participating in SQL AlwaysOn availability group only after Failover.

 

Direct ConfigMgr to use SQL AlwaysOn

  • Run Setup.exe from \Program Files\Microsoft Configuration Manager\bin\X64
  • Select the option to Perform Site Maintenance or reset this site and click Next

clip_image050

  • Choose the option Modify SQL Server configuration and click Next

clip_image052

  • Change the SQL Server name to Listener DNS name you specified in the AlwaysOn wizard and click Next

clip_image054

  • Validate a successful completion of wizard. Click Close

clip_image056

  • Launch ConfigMgr Console
  • Verify the Site Properties with the new SQL server name.

clip_image058

You will note additional Site System Roles for each Replica and the Listener DNS name

clip_image060

 

Thanks,

Arnab Mitra

Comments (12)

  1. Sam says:

    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.

    Have you experienced any of these symptoms?

    1. Hi Sam,

      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.

      Thanks,
      Arnab

  2. Chris says:

    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!

    1. Hi Chris,

      Hosting WSUS DB in a availability group isn’t supported yet, however you can use a full blown SQL Cluster for higher availability.
      The reporting DB’s can be moved to an availability group. For more info. refer the following TechNet article – https://msdn.microsoft.com/en-us/library/hh882437(v=sql.120).aspx

      Thanks,
      Arnab

  3. Rob says:

    Chris, we followed your steps and like Sam we are seeing certificate errors. How did you create it with out certificates?
    INFO: Registered type PWSQLCONFIG01.##.#######.COM MASTER for pwsqlconfig01.##.#######.com master $$
    INFO: Registered type PWSQLCONFIG01.##.#######.COM CM_001 for pwsqlconfig01.##.#######.com CM_001 $$
    *** [08001][-2146893022][Microsoft][SQL Server Native Client 11.0]SSL Provider: The target principal name is incorrect.~~ $$
    *** [08001][-2146893022][Microsoft][SQL Server Native Client 11.0]Client unable to establish connection $$

  4. Adam says:

    Sam, did you ever find a resolution to the SQL errors that you mentioned? We are seeing the exact same errors. Please let me know if you found a solution.

    Thanks!

  5. Shesha says:

    Hi I have followed the setup as directed above.. But after mentioning the listener name it is not getting through. When i checked configmgrsetup.log it says

    The ssl Error says : the target principal name is incorrect

  6. FinnySamuel [MSFT| says:

    Apply the below as a work around
    https://support.microsoft.com/en-us/kb/3189594

    That should get rid of the false positives. Please note that you will still get the messages after applying the above for about 5-10 minutes, but it should move past that successfully.

    1. FinnySamuel [MSFT| says:

      https://support.microsoft.com/en-us/kb/3189594 should help address the below errors

      …[SQL Server Native Client 11.0]SSL Provider: The target principal name is incorrect.
      .. Microsoft][SQL Server Native Client 11.0]Client unable to establish connection
      *** Failed to connect to the SQL Server, connection type: SMS ACCESS.
      INFO: SQL Connection failed. Connection: SMS ACCESS, Type: Secure
      ERROR: SQL Connection failed. Connection: SMS ACCESS, Type: Secure
      ERROR: Failed to get SQL Server connection.
      INFO: SQL Server Native Client: SQLNCLI11 version:
      ERROR: Failed to find folder of SQL Server assembly setup msi.

  7. Luke Torrens says:

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

    The log file shows (obviously server names redacted)

    INFO: SQL Connection succeeded. Connection: XXXXXX.FQDN CM_PRE, Type: Unsecure Configuration Manager Setup 2/23/2017 1:35:34 PM 3408 (0x0D50)
    INFO: There is SQL Always On enabled. Configuration Manager Setup 2/23/2017 1:35:34 PM 3408 (0x0D50)
    INFO: SQL Always On Primary Node is DC1ZO-CLSQL2-01. Configuration Manager Setup 2/23/2017 1:35:34 PM 3408 (0x0D50)
    INFO: SQL Always On Nodes : XXXXXXXXX-01 detected. Configuration Manager Setup 2/23/2017 1:35:34 PM 3408 (0x0D50)
    INFO: SQL Always On Nodes : XXXXXXXXX-02 detected. Configuration Manager Setup 2/23/2017 1:35:34 PM 3408 (0x0D50)
    INFO: SQL Always On Nodes : XXXXXXXXX-02 detected. Configuration Manager Setup 2/23/2017 1:35:34 PM 3408 (0x0D50)
    WARN: Failed to obtain handle to prereqcore.dll, 126 Configuration Manager Setup 2/23/2017 1:35:34 PM 3408 (0x0D50)

    The error code says the OS is not supported, the SCCM installation is on SCCM 2012 R2. I found another article suggesting using the setupwpf.exe rather than setup and this yielded the same result.

    There are very few articles detailing this error, would you be able to point me in the right direction?

    1. Run the setup from the CD.Latest folder in the ConfigMgr install directory.

      1. Peter Mueller says:

        Did running from the cd.latest fix the issue for you Sam? We encountered same issue trying to move site database to an alwayson cluster.

Skip to main content