Freigeben über

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

  • Artikel

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 EngineManagement 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.
  • Change Recovery model = Full

Backup ConfigMgr DB

On the existing SQL Server -

  • Open SQL Server Mgmt Studio and Right click ConfigMgr DB > Tasks > Backup
  • Make sure Full is selected for Backup type
  • Set an expiry date

Optionally you can add additional destination.

  • Copy the .bak file to one of the SQL servers participating SQL AlwaysOn

    Restore ConfigMgr DB

    Select one of the participating SQL AlwaysOn node to restore the database. (where you copied the .bak file)

    • Open SQL Server Mgmt Studio and Right click Databases > Restore Databases
    • Choose Device and click elipse

  • Add .bak file
  • Click OK to restore.

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.

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

  • Click Next to Select Database. Ensure status is Meets prerequisites.

  • 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

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

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

INFO: Targetted database is CM_P01.

INFO: Running verifications....

ERROR: CLR is not enabled!

ERROR: Max text repl size is not correct!

ERROR: Database owner is not sa account!

ERROR: Trustworthy bit is not on!

PASS: Recursive triggers are enabled.

ERROR: Service broker is not enabled!

ERROR: Service broker priority is not set!

PASS: Change tracking is enabled.

PASS: Snapshot isolation is enabled.

PASS: Read committed snapshot is enabled.

PASS: Quoted identifier is on.

PASS: ANSI warnings are on.

PASS: ANSI padding is on.

PASS: Concat null yields null is on.

PASS: Arithabort is on.

PASS: Numeric roundabort is off.

Done!

  • 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

  • Click Connect on the Instance and click Next

  • Verify the settings and click Finish

  • Verify the successful completion.

  • Open the Availability Dashboard to confirm a successful failover

SQL2 is now the Primary Instance and SQL1 is Secondary.

  • Don't forget to run the DB reconfiguration remediation script. 

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

  • Choose the option Modify SQL Server configuration and click Next

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

  • Validate a successful completion of wizard. Click Close

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

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

Thanks,

Arnab Mitra

 

Download File - Verify-Database-Settings Download File - Remediate-Database-Settings

Comments

  • Anonymous
    May 16, 2016
    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?
    • Anonymous
      May 16, 2016
      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
  • Anonymous
    June 16, 2016
    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!
  • Anonymous
    July 26, 2016
    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 $$
  • Anonymous
    September 14, 2016
    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!
  • Anonymous
    September 20, 2016
    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 saysThe ssl Error says : the target principal name is incorrect
  • Anonymous
    November 08, 2016
    Apply the below as a work aroundhttps://support.microsoft.com/en-us/kb/3189594That 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.
    • Anonymous
      November 08, 2016
      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: SecureERROR: 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.
  • Anonymous
    February 23, 2017
    The comment has been removed
    • Anonymous
      February 23, 2017
      Run the setup from the CD.Latest folder in the ConfigMgr install directory.
      • Anonymous
        April 06, 2017
        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.
  • Anonymous
    January 15, 2019
    Great article - Our current SQL is Standard Edition (located on a remote server). Would we need both SQL servers to be Enterprise edition? And can I promote our existing server to Enterprise? Thanks