SQLAlwaysOn and SCCM

Thought I’d give one of the new features in ConfigMgr Technical Preview build 1705 called Improvements for SQL Server Always On Availability Groupsa walk through, so that I could soak up all the SQL AlwaysOn Availability Group and Windows Server Failover Clustering knowledge.

From the docs:

With this release, you can now use asynchronous commit replicas in the SQL Server Always On availability groups you use with Configuration Manager. This means you can add additional replicas to your availability groups to use as off-site (remote) backups, and then use them in a disaster recovery scenario.

As it says, the reason why you’d run an Availability Group asynchronous Replica is for off-site backup, in the event that you lose your cluster fully, the asynchronous replica can be used for rapid disaster recovery.

To kick the tires on recovery would require restoring the site and loads of work, and since an asynchronous replica DB is the same but with possible data loss (the nature an asynchronous replication), it is the same process as restoring from a full cold backup, so I’ll not test it for now, instead I’ll get the feature working and leave it there.

So what does a SQL AlwaysOn Availability Group asynchronous replica mean or do.

Let’s take a step back. In an Availability Group, when a SQL transaction is to be committed to the Primary instance database it is first replicated to each Replica and committed into its database, once that is done successfully the commit at the Primary instance completes. This means the primary waits around a lot for replicas to commit the transactions being replicated to them, they need to perform well and be physically close network-wise. It makes for high integrity compared to the asynchronous replica, which the Primary instance doesn’t wait around for, it just sends transactions to it and doesn’t wait for acknowledgement, which means that some data “in transit” could be lost if an ‘event’ occurred. With Technical Preview 5 you can now restore from an asynchronous replica database as a supported scenario.

So let’s build us an Availability Group and get ConfigMgr to play ball with it.

The last time I built a cluster was NT4 days, things haven’t changed too much in terms of standing one up, so that wasn’t difficult, but I had several learning experiences around standing up SQL AlwaysOn, which I dare say helped me bottom out my knowledge on the subject.

This guide is not for production usage, you could certainly distil it into a procedure to be applied in production, instead this guide is for lab work only.

Before you can get things underway you’re going to need the following, so as to keep on the rails throughout this guide.

Media

  • Operating System – Windows 2016 DataCenter with UI
  • SQL Server 2016 (SP if you want, CU’s, whatever ConfigMgr supports)
  • SQL Management Console (E.g. SSMS-Setup-ENU-R16.5.3-B13.0.16106.4)

Virtual Machines

  1. Domain Controller
  2. SQL Server Replica\Node A (192.168.1.170) – 2GB Memory – Single disk
  3. SQL Server Replica\Node B (192.168.1.171) – 2GB Memory – Single disk
  4. SQL Server Replica\Node C (192.168.1.172 – 2GB Memory – Single disk)
  5. A pre-built ConfigMgr Technical Preview Build 1705 Site server with SQL locally installed (can be remote but heck …)

The account that you do all the action with, the one you’ll login, please make it a Domain Administrator, it’ll cut through the ‘butter’ faster, if you want to tighten down then follow the guide and analyse everything post-build.

Right then, get your cool juice and saddle up, as we’re about to ride out and get ourselves SQL AlwaysOn Availability Group  working with ConfigMgr.

Prepare the SQL Server AlwaysOn Replicas

Go ahead and stand up three virtual machines using Windows Server 2016 DataCenter with Desktop.

Once built do the following:

  • Give them a name

  • VM1CMSQLAONA – 192.168.1.170
  • VM2CMSQLAONB – 192.168.1.171
  • VM3CMSQLAONA – 192.168.1.172

  • Join to your Domain

  • Fix their IP and make a note

  • Add the Computer Account of the Technical Preview Primary Site server to the local administrator group on each replica

  • Make sure all replicas have been rebooted to commit the configuration changes

  • At this point I switch on Remote Access for RDP, and turn off the Windows Firewall on the Domain Profile, from there I add in the servers to RDCMan so I can move off of the Hyper-V host.

Install the Windows Failover Clustering feature

Let’s do this in one go across all three nodes.

  • Fire up the PowerShell ISA, paste in the below script and let it go:

$serverList = "CMSQLAONA","CMSQLAONB","CMSQLAONC"


ForEach ($server in $serverList)
{
    Install-WindowsFeature -computername $server –Name Failover-Clustering –IncludeManagementTools
}

  • After a while you’ll have the clustering feature enabled on all three nodes.

Create a Windows Cluster

We’re now going to create our Windows Cluster, this breaks down into two tasks:

  1. Create a Network Share for the File Share Witness
  2. Create the cluster itself.

The Network share will be used to enable a file system quorum, so that we can introduce a basic cluster into our lab.

Create a Network Share for the Quorum

I chose the Domain Controller to host this share, it is highly available to the lab. The file share witness will not handle large volumes of data, and as a result, will utilise a small footprint during its lifecycle.

  • Create a new folder on the Domain Controller anywhere of your choosing, call it CMSQLAOFSW, I’ll use C:\CMSQLAOFSW

  • Share the folder out, give the Full Control share permissions to Everyone

image


The ACL’s to this folder will be changed during the Cluster creation process, during which a domain user account representing the name you give the Cluster will be created and given Full Control permissions. The account you are installing with will need to be a Local Administrator on the Domain Controller for all this magic to happen.

  • Navigate into your share by referencing it as a UNC in Start\Run, to confirm you are able to access it

Easy. You’ll be creating another share later on.

Create the Windows Cluster

Head over to CMSQLAONA and fire up the Failover Cluster Manager. Its an admin-interface based on ancient MMC technology, so do the right thing, and turn off the Action menu like a Boss!

  • Right click Failover Cluster Manager in the left hand pane, then select Create Cluster

image

  • Once the wizard fires up, click through the welcome page and add in your Cluster Nodes CMSQLAONA, CMSQLAONB and CMSQLAONC using the browse button.

  • Once you have all three Nodes listed as below, then click Next


image

  • Next up is testing the cluster nodes by running a set of validation steps, you don’t have to run this but I’d let it run in case there is something exotic about your environment that’ll catch you out later on

image

  • Click Next to pass through the Before You Begin page, select Run all tests (recommended), its the default anyway, and proceed with the wizard by clicking Next

image

  • Have a nose through the list of tests, click Next when ready to run them

image

  • The validation test should pass if you stand the virtual machines up from the ISO’s, but scrolling down you’ll notice that it complains about redundancy for network connections in the Validate Network Communications step, which you’d resolve as part of hardening a procedure for production use, but can ignore in the lab

image

  • You can click on View Report to see a nice HTML based report

image

  • Let’s carry on configuring the Cluster, select Finish in the wizard

image

  • Enter SQLAOCluster as the Cluster Name

If you are prompted to enter an IP address then a DHCP reservation was not created, most likely permissions based issue, otherwise you’ll see a dialog like this:

image

  • Give the Cluster Name the IP of 192.168.1.174 or whatever IP you’ve set aside for you lab environment

  • Select Next

image

  • Select Next

image

  • Note that warning about the disk witness, we’re going to use a File Share Witness (FSW) so this can be ignored

  • Select Finish to wrap up

  • Once the wizard has finished, RDP to your DNS server which is no doubt on your Domain Controller, and check out the new record created for the Cluster Name

image

  • You now have a Windows Cluster, but to complete it you will need to add in the Quorum Witness. Our choice of a File Share Witness is driven by the need for simplicity, in a lab environment a disk can be shared amongst VM’s, but it takes the complexity level up a notch, fortunately we can use a SMB File Share to perform the Quorum duties, which is highly uncomplicated to setup, so let’s get it done

  • Return to the Failover Cluster Manager

image

  • Right click SQLAOCluster, select More Actions, and select Configure Cluster Quorum Settings…

image

  • If you do not understand what a Quorum Witness is, please stop and deep dive the subject for a bit, get this foundational knowledge under your belt so as to help round off. I recommend doing this for any part of the guide that takes you out of your comfort zone, when you get that twinge its your brain prompting you to fill the gap, for Quorum Witnesses read here

  • Have a read of the Before You Begin notes before you skip over

image

  • Select Next

image

  • Select Select the quorum witness

  • Select Next

image

  • Select Configure a file share witness, note the cool Cloud Witness as well as the classic disk witness

  • Select Next

image

  • Punch in the UNC to your File Share Witness share, you can browse to it or manually enter it. If you manually enter then here’s a top tip, always test a UNC by copy\pasting it into Start\Run to make sure you can browse it, obviously if your account doesn’t have rights this method doesn’t work, in those cases I NET USE and pass the credentials to make sure all is well

  • Select Next

image

  • Confirm and click Next

image

  • Select Finish

  • Note that Witness now changes to File Share Witness (FSW), and shows the UNC path to it

image

  • Paste the File Share Witness UNC into Start\Run, to see the file system objects just created by the Cluster

image

You now have a Windows Server Failover Cluster.

Ping the Cluster Name and you will get a ping response back from the IP you specified during the setup of the Cluster Name, I keep wanting to say Cluster Interface, I think this is what we called it years ago, or perhaps it just makes more sense to my brain to call it an interface as that is what it is.

Worth noting that we won’t be using the Cluster Name at all for the SQL AlwaysOn Availability Groups. The SQL AO AG’s have their own Cluster Name concept called a Listener. You’ll come to that shortly.

Install SQL 2016 on the SQL AlwaysOn Replicas

  • We’ll run SQL using a domain user account so visit your Domain Controller and create a user called CMClusterService, make note of the password, it only needs to be a Domain User, add the account to Local Administrators on all SQL nodes

If you run SQL Server using SYSTEM context you’ll need to handle the certificate transfer, I didn’t test this but saw it called out in some Microsoft SQL documentation, it might handle transferring the certificates for you, I did see a call out in some logs when creating the Availability Group that suggest certificates are being exchanged automatically between the Replicas in the Availability Group.

We’ll cookie cut the installation of CMSQLAONB and CMSQLAONC using an INI file we create from installing SQL 2016 manually on CMSQLAONA.

  • Insert the SQL ISO media into the CMSQLAONA, CMSQLAONA and CMSQLAONA virtual machines

  • Login to CMSQLAONA

  • Run the Setup.exe on the SQL DVD Media:

image

  • Select Installation

image

  • Select New SQL Server stand-alone installation or add features to an existing installation


image

  • Tick to accept the license terms and select Next

image

  • Tick Use Microsoft Updates to check for updates (recommended) unless you do not have access to the Internet

  • Select Next

image

  • Select Next

image

  • If it finds anything, let it install

image

  • Choose Database Engine Services and keep default paths

  • You could add in Reporting Services, it’ll require another account and also some special handling for the ConfigMgr Reporting Point role. I’ve skipped doing this, since I have SQL Reporting Services and its database on the Primary, along with the ConfigMgr Reporting Point role, which will point to the SQL AlwaysOn Availability Group Listener you’re about to encounter later on in the guide

  • Select Next

image

  • Select Next

image

  • Okay so now we want to set the startup type for the Agent and Server to Automatic

  • Enter the domain user account credentials (CMClusterService) you created for the SQL Cluster for both the Agent and Server services

  • Select the Collation Tab (this is a highly important step)

image

  • Select the SQL collation, used for backwards compatibility radio button, change the collation to SQL_Latin1_General_CP1_CI_AS

  • Select OK

 

image

  • Select OK

image

  • Add the local Administrators group

  • Add your domain account as well

  • Leave Data Directories, TempDB and FILESTREAM alone

  • Select Next

image

C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\20170613_143153\ConfigurationFile.ini

  • Edit the file and make the following amendments:

    1. False to True for IACCEPTROPENLICENSETERMS
    2. False to True for QUIET
    3. Put a ; semicolon character before UIMODE
  • Copy the file to a newly created folder C:\SQLInstall on both CMSQLAONB and CMSQLAONC

  • Now click Install to get the SQL installation underway on CMSQLAONA

  • Login to CMSQLAONB and fire up a CMD prompt

  • Begin the SQL installation by modifying the passwords in the command below then running it:

D:\SETUP.EXE /CONFIGURATIONFILE=”C:\SQLInstall\ConfigurationFile.ini” /IAcceptSQLServerLicenseTerms /SQLSVCPASSWORD="<PASSWORD>" /AGTSVCPASSWORD="<PASSWORD>"

  • Follow through with the same unattended silent SQL installation steps on CMSQLAONC

  • While those SQL installations are bubbling away, go back to CMSQLAONA, and install SQL Management Studio. Back in the day, the studio was part and parcel of the SQL Setup, they diverged into separate installs, which means the console is unlinked from the SQL setup, and can easily be updated with a smaller media footprint

  • Launch the SQL Management Studio installer

image

  • Select Install

  • You can install this on one all three nodes, or just the primary one you’ll be working from, I’ll install to CMSQLAONA only, and do all administration of SQL from there

  • This one can take forever, even on a fast system. Go get yourself some Tea!

image

  • I recommend pinning the Studio to the Taskbar

Configure SQL

We now need to make some configuration changes to the SQL Server engines on all three of our SQL servers.

Set SQL Engine Memory limits

I recommend that you calculate the memory limits based on how much you added when you created the virtual machines, they can in a small environment run on as little as 1GB.

  • RDP onto CMSQLAONA and open Microsoft SQL Server Management Studio using Run as administrator option

  • Connect the studio to all three nodes CMSQLAONACMSQLAONB and CMSQLAONC

  • With the root nodes expanded it’ll look like this:

image

  • Right click CMSQLAONA and select Properties, then select Memory

image

  • Repeat for CMSQLAONB and CMSQLAONC

Permission all Nodes for Primary Site access

  • Add the computer account of the Primary Site server to the local Administrator group on both CMSQLAONA, CMSQLAONB and CMSQLAONC

  • The CMSQLAONC entry above is redundant in this lab setup, since CMSQLAONC is running in Asynchronous mode and the Primary Site will not establish a direct connection

  • While you are at it, add the SQL Service account to the local administrator group on CMSQLAONB and CMSQLAONC

Bring in the CM Database

Now this is where my lab complicates things. I’ve already taken my Technical Preview 5 Primary Site towards the dark side, and its currently running its brain on a 3-node SQL AlwaysOn Availability Group. I’m going to export the DB and import it CMSQLAONA, but it means the shots will be off when it shows recovery mode.

Since my site database already resides on a SQL AlwaysOn Availability Group, it is running as FULL And not SIMPLE recovery mode. You will most likely be running SQL on the Primary, and it will most likely already be running in SIMPLE recovery mode, in this case the backup steps are the same to liberate a backup of the CM database from that SQL installation.

Take down ConfigMgr

We need to stop ConfigMgr from talking to the Database. At this point the show is over for ConfigMgr, service will resume once the Availability Group is setup and ConfigMgr told to use it.

For my lab all I need to do is wind down the services on the Primary, no site systems exist which could be connecting to the database, but its the primary that’d do the writing to the database anyway.

  • RDP over to your ConfigMgr Current Branch Technical Preview 5 Site server

  • Open the Services (MMC) console

  • Stop the following services in this order

  1. ConfigMgr Task Sequence Agent
  2. Configuration Manager Remote Control
  3. CONFIGURATION_MANAGER_UPDATE
  4. SMS Agent Host (if installed)
  5. SMS_NOTIFICATION_SERVER
  6. SMS_SITE_COMPONENT_MANAGER
  7. SMS_EXECUTIVE
  8. SMS_SITE_SQL_BACKUP
  9. SMS_SITE_VSS_WRITER
  • You could use the PREINST /STOPSITE command, but that’ll mark everything for reinstallation the moment you start the services up. This isn’t necessary, since we’re going to start the site up by changing the SQL configuration from the Setup program, which’ll induce component installation behaviour

  • A good working practice is to disable services when you are putting them to sleep for administrative purposes, so that if the server reboots by accident, it won’t come back up and start the application before you are ready

Backup and Move the ConfigMgr Database

I’m now going to backup the ConfigMgr CTP database (CM_CTP), and move the backup file to the CMSQLAONA node in the following location C:\SQLBackup

  • Visit SQL Management Studio on the target SQL Server currently hosting your site database

  • Find the site database, right click it and select Tasks and then Back Up…

image

  • For a standalone primary with SQL locally installed, you’ll be backing up a database running in SIMPLE recovery mode, which means the file extension for your backup will be BAK and not TRN, which is the case when you are already running in FULL recovery mode

  • Enter your preferred backup path location, and enter the file name as CMBackup-<CURRENTDATE>.BAK (TRN if you are moving from one SQL AlwaysOn Availability Group to another)

  • Select OK

  • Copy the TRN or BAK file over to a new folder called C:\CMBackup on CMSQLAONA

Configure the SQL Engine on all Nodes

We’ll modify the SQL Engine on all three SQL nodes before creating the Availability Group, as doing it later will require micro-managing the Availability Group, failing over between the nodes so as to configure each of them.

  • RDP to CMSQLAONA and launch SQL Management Studio

  • Connect to CMSQLAONA and CMSQLAONB

  • Select CMSQLAONA and click New Query

  • To confirm you are about to run queries against the correct node, take a look at the bottom right of the yellow status bar, it’ll show you the name of the server the query will be run against, as will the studio applications status bar:

SQL Management Studio Application Title bar:



image

SQL Management Studio Status Bar

image

  • Paste this lot in and click Execute

USE Master;

GO

SP_CONFIGURE 'show advanced options', 1;

RECONFIGURE WITH OVERRIDE;

GO

EXEC SP_CONFIGURE 'max text repl size',2147483647 ;

RECONFIGURE WITH OVERRIDE;

GO

EXEC SP_CONFIGURE 'clr enabled', 1;

RECONFIGURE WITH OVERRIDE;

GO

The result should be:

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.


Configuration option 'max text repl size (B)' changed from 65536 to 2147483647. Run the RECONFIGURE statement to install.


Configuration option 'clr enabled' changed from 0 to 1. Run the RECONFIGURE statement to install.

  • Select CMSQLAONB and select New Query

  • Paste the same as above and click Execute

  • Select CMSQLAONC and select New Query

  • Paste the same as above and click Execute

Restore the ConfigMgr Database

It’s time to restore the ConfigMgr database..

  • Remain within SQL Management Studio on CMSQLAONA

  • Select CMSQLAONA and right click Databases, select Restore Database…

image

  • You’ll be presented with the Restore Database page

 

image

  • Select Device and select the ellipses


image

  • Select Add


  • Navigate to C:\CMBackup and select the CMBackup file

image

  • Select OK

image

  • Select OK

image

  • You can click on Files and you’ll see it is going to restore the Database to the default SQL locations:

image

  • Select OK to get on with the recovery of CM_CTP

image

Configure SQL Database CM_CTP

Next up is more SQL configuration, but this time not the SQL Engine but the site database.

For this series of steps, you’ll stay on CMSQLAONA and inside SQL Management Studio, and you will change the database name used in the steps to whatever you called your site database, mine is called CM_CTP.

  • Select CMSQLAONA and then select New Query, copy\paste the below SQL code snippets and execute each time

Change Recovery Model from SIMPLE to FULL

Use CM_CTP;

ALTER DATABASE CM_CTP SET RECOVERY FULL;

Take stock and contemplate how you’re going to handle SQL Transaction Logs now that you’ll be in FULL recovery mode, and how they mass up on you consuming disk space, and regularly need backing up. I’m going to totally leave you hanging, and let you read up on how to handle this.

Set SA owner on DB

Use CM_CTP;

EXEC sp_changedbowner 'sa'

Set TRUSTWORTHY bit on DB

USE CM_CTP;

ALTER DATABASE CM_CTP SET TRUSTWORTHY ON;

Enable SERVICEBROKER on DB

Use CM_CTP;

ALTER DATABASE CM_CTP SET ENABLE_BROKER;

Enable SERVICEBROKERPRIORITY on DB

Use CM_CTP;

ALTER DATABASE CM_CTP SET HONOR_BROKER_PRIORITY ON;

You can verify the configuration of SQL by running the following SQL script which is taken from the ConfigMgr Documentation:

Note: You can paste this into your existing query window or create a new one making sure you are focused on the correct SQL server, select what you paste in and then click Execute to execute the selection (change the USE CM_CTP to your database name)

USE CM_CTP

SET NOCOUNT ON

DECLARE @dbname NVARCHAR(128)

SELECT @dbname = sd.name FROM sys.sysdatabases sd WHERE sd.dbid = DB_ID()

IF (@dbname = N'master' OR @dbname = N'model' OR @dbname = N'msdb' OR @dbname = N'tempdb' OR @dbname = N'distribution' ) BEGIN
RAISERROR(N'ERROR: Script is targetting a system database.  It should be targeting the DB you created instead.', 0, 1)
GOTO Branch_Exit;
END ELSE
PRINT N'INFO: Targetted database is ' + @dbname + N'.'

PRINT N'INFO: Running verifications....'

IF NOT EXISTS (SELECT * FROM sys.configurations c WHERE c.name = 'clr enabled' AND c.value_in_use = 1)
PRINT N'ERROR: CLR is not enabled!'
ELSE
PRINT N'PASS: CLR is enabled.'

DECLARE @repltable TABLE (
name nvarchar(max),
minimum int,
maximum int,
config_value int,
run_value int )

INSERT INTO @repltable
EXEC sp_configure 'max text repl size (B)'

IF NOT EXISTS(SELECT * from @repltable where config_value = 2147483647 and run_value = 2147483647 )
PRINT N'ERROR: Max text repl size is not correct!'
ELSE
PRINT N'PASS: Max text repl size is correct.'

IF NOT EXISTS (SELECT db.owner_sid FROM sys.databases db WHERE db.database_id = DB_ID() AND db.owner_sid = 0x01)
PRINT N'ERROR: Database owner is not sa account!'
ELSE
PRINT N'PASS: Database owner is sa account.'

IF NOT EXISTS( SELECT * FROM sys.databases db WHERE db.database_id = DB_ID() AND db.is_trustworthy_on = 1 )
PRINT N'ERROR: Trustworthy bit is not on!'
ELSE
PRINT N'PASS: Trustworthy bit is on.'

IF NOT EXISTS( SELECT * FROM sys.databases db WHERE db.database_id = DB_ID() AND db.is_broker_enabled = 1 )
PRINT N'ERROR: Service broker is not enabled!'
ELSE
PRINT N'PASS: Service broker is enabled.'

IF NOT EXISTS( SELECT * FROM sys.databases db WHERE db.database_id = DB_ID() AND db.is_honor_broker_priority_on = 1 )
PRINT N'ERROR: Service broker priority is not set!'
ELSE
PRINT N'PASS: Service broker priority is set.'

PRINT N'Done!'

Branch_Exit:

That script is dead handy!

We’re looking for INFO and PASS only

INFO: Targetted database is CM_CTP.
INFO: Running verifications....
PASS: CLR is enabled.
PASS: Max text repl size is correct.
PASS: Database owner is sa account.
PASS: Trustworthy bit is on.
PASS: Service broker is enabled.
PASS: Service broker priority is set.
Done!

Configure SQL Security for Primary Site server

We’re going to recreate the SQL login for the site server to use, this will have been lost as SQL Engine logins are not backed up when you backup a database, the SQL logins are present in the database but not on the new SQL server.

  • Expand CMSQLAONA, Security and right click Logins, now select New Login…

image

My Primary’s hostname is CMCBTP so I’ll create the new login as its computer account

image

  • Select Server Roles

image

  • Make sure public is ticked, it should be by default

  • I added sysadmin as I was troubleshooting on the first go around setting up a Availability Group, the site server doesn’t need these rights, but the account you are logged in as when working through this guide must have sysadmin rights

  • Select OK

  • Expand CMSQLAONB, Security and right click Logins, now select New Login…

image

Again use the Primary’s site servers hostname, CMCBTP$

image

  • Make sure public is ticked

image

  • Select OK

Enable SQL AlwaysOn Feature

Next up is enabling the SQL AlwaysOn feature on all three Replicas. This feature will not light up until a replica is a member of a Windows Server Failover Cluster.

  • From CMSQLAONA open SQL Server Configuration Manager

image 

  • Select SQL Server Services, right click SQL Server (MSSQLSERVER) and select Properties

  • Now visit the AlwaysOn High Availability tab

  • Select Enable AlwaysOn Availability Groups

image

  • Select OK

  • Restart the SQL Server service for the change to take effect, as it warned you. You can restart the service inside the Configuration Manager console, right click SQL Server (MSSQLSERVER) and select Restart

Note: The AlwaysOn feature would not light up and be greyed out, if this server wasn’t a node in a Windows Cluster, didn’t I just say that? It is worth repeating!

  • Perform this step on each of the remaining replicas CMSQLAONB and CMSQLAONC

Create a Network Share to facilitate the transfer of the site database CM_CTP to all Availability Group Replicas

When we create the Availability Group we need to somehow get the site database copied across to all remaining replicas, CMSQLAONB and CMSQLAONC. Well we can do this manually or we can let SQL do it for us using a file share.

I created this file share on CMSQLAONA, as this is a bridgehead for doing all the SQL administrative work so far, let’s create it here.

  • Open File Explorer

  • Create a new folder on CMSQLAONA called C:\CMDBTransfer

  • Share the folder out

image

  • Give the Full Control share permissions to Everyone

image

  • Select OK

  • Right click the CMDBTransfer folder, select Properties and then the Security tab

  • Set the ACL’s so that the SQL Account YourDomain\CMClusterService has full permissions

image

  • Select OK

Create a SQL AlwaysOn Availability Group

Good to create the Availability Group now. We’ll begin the creation of the Availability Group on CMSQLAONA.

  • Return to SQL Management Studio

  • Navigate to CMSQLAONA

  • Right click AlwaysOn High Availability

image

  • Select New Availability Group Wizard…

image

  • Have a read

  • Select Next

image

  • We’ll now name our Availability Group as ConfigMgrAG

  • You could call it whatever you want, if you change it keep an eye out for references to ConfigMgrAG fruther into the guide, and substitute for whatever you chose

  • Select Next

image

  • It see’s our database, select it

Note: You may be prompted that a Full backup is required of the newly imported database, if so stop, backup the database that you just imported so as to overcome this constraint and proceed.

Note: When I first took my ConfigMgr Tech Preview site database from a local install into the first availability group lab that I created, the text in this dialog was different, some actions are needed, and size of DB was mentioned, as well as results of a prerequisite check. I might move my ConfigMgr Current branch into an Availability Group, which means I’ll see this wizard again, return here and amend for the differences. In the meantime you might need to wing this section a bit, by doing whatever the prerequisite checker asks of you

  • Select Next

image

We are now going to add in the Replicas that will participate in the Availability Group, that’ll be all three cluster nodes CMSQLAONA, CMSQLAONB and CMSQLAONC

  • Select Add Replica…

image

  • Enter CMSQLAONB and select Connect

image

  • Select Add Replica…

  • Enter CMSQLAONB and select Connect

image

  • Note that CMSQLAONA is listed as a Primary Replica, and CMSQLAONB and CMSQLAONB are listed as Secondary Replica’s

  • For CMSQLAONA tick Automatic Failover (Up to 3), tick Synchronous Commit (Up to 3) and select Yes for Readable Secondary

  • For CMSQLAONB tick Automatic Failover (Up to 3), tick Synchronous Commit (Up to 3) and select Yes for Readable Secondary

  • For CMSQLAONC, which we’ll use to enable the Technical Preview Build 1705 feature for AlwaysOn, Asynchronous Availability Group replica support, untick Automatic Failover (Up to 3), untick Synchronous Commit (Up to 3) and select Yes for Readable Secondary

  • Select the Listener tab

image

  • Select Create an availability group listener

Note: The Listener is actually a DNS record that will be used by Applications when they attempt to connect to the Availability Group, earlier in the guide I called out a link on tooling up knowledge-wise on what a Listener is, so I’ll forgo being an echo-chamber and repeat what you already knew or just learnt.

  • Enter ConfigMgrAGL as the listener DNS name

  • Enter 1433 as the Port

  • Enter an IP address for the listener to use, I used 192.168.1.173 for ConfigMgrAGL

  • If you build further Availability Groups, they will have their own unique listener, much like a Cluster Name (interface!)

  • Select Next

image

The site database needs to be transferred to the other two Replica in the Availability Group, SQL can automatically do this for us, with that share we created, or we can handle it manually ourselves. Guess which method we’re opting for!

image

  • And all the ducks line up

  • Select Next

image

image

  • Select Finish to get things underway

  • Time for more tea

image

  • More tea

image

  • Great we’re done here

  • Visit your DNS server and you’ll see a new A record has been created for your new Availability Group ConfigMgrAGL

image

  • From CMSQLAONA visit the SQL Management Studio

  • Expand out CMSQLAONA and the Availability Group nodes

image

  • You did that :-)

  • I am assuming it all worked out, well done!

  • Let’s see if its really working, bring up the Dashboard, a right click off the Availability Node

image

  • Select Show Dashboard

image

  • Click on ConfigMgrAG to drill down

image

  • If you are not seeing Green you’ve derailed somewhere, not very helpful of me but I would recommend reading any critical or warning messages it produces, take it from there

  • Note that you can administer most of the Availability Group from here, a primary to note is that you launch the failover wizard from the dashboard but can launch it from a right click on your Availability Group in Object Explorer

Final SQL Configurations

The site database now residing on CMSQLAONB was transferred there using SQL Backup and Restore, which means that some database settings haven’t been carried across. These need to be set, or normalised, but we cannot straight connect and configure, we need to tell the Availability Group to failover to that replica then perform the tasks. So let’s get on with that

  • Make sure the SQL Availability Group nodes are healthy (green tick) and that they have synchronised, this could take some time, observe from the Dashboard, more tea may be required

  • From SQL Management Studio right click your Availability Group and select Failover…, you can do this from any of the Replica nodes but the assumption for this guide is that CMSQLAONA is your Primary instance, and the others replicas are Secondary Replicas, so do your right click from CMSQLAONA

image

  • Select Next

image

  • Tick CMSQLAONB which should be the nominated Replica for Failover

  • Select Next

image

  • Select Connect

image

  • It won’t let you interact other than with the buttons, select Connect

image

  • Select Next

image

  • Select Next

image

  • Select Close

  • You’ll see the dashboard show critical, leave it alone for a few moments, it auto refreshes, proceed when it returns to a healthy state

  • Notice our Primary instance is now CMSQLAONB

image

  • Select CMSQLAONB in the Object Explorer then select New Query:

Use CM_CTP;

EXEC sp_changedbowner 'sa'

USE CM_CTP;

 

ALTER DATABASE CM_CTP SET TRUSTWORTHY ON;

Use CM_CTP;

 

ALTER DATABASE CM_CTP SET HONOR_BROKER_PRIORITY ON;

  • Execute that lot on CMSQLAONB

  • I did not have to set ENABLE_BROKER in my lab, so I've pulled the following step as not required:

Use CM_CTP;

ALTER DATABASE CM_CTP SET ENABLE_BROKER;

  • Rerun the following validation script:

SET NOCOUNT ON

DECLARE @dbname NVARCHAR(128)

SELECT @dbname = sd.name FROM sys.sysdatabases sd WHERE sd.dbid = DB_ID()

IF (@dbname = N'master' OR @dbname = N'model' OR @dbname = N'msdb' OR @dbname = N'tempdb' OR @dbname = N'distribution' ) BEGIN
RAISERROR(N'ERROR: Script is targetting a system database.  It should be targeting the DB you created instead.', 0, 1)
GOTO Branch_Exit;
END ELSE
PRINT N'INFO: Targetted database is ' + @dbname + N'.'

PRINT N'INFO: Running verifications....'

IF NOT EXISTS (SELECT * FROM sys.configurations c WHERE c.name = 'clr enabled' AND c.value_in_use = 1)
PRINT N'ERROR: CLR is not enabled!'
ELSE
PRINT N'PASS: CLR is enabled.'

DECLARE @repltable TABLE (
name nvarchar(max),
minimum int,
maximum int,
config_value int,
run_value int )

INSERT INTO @repltable
EXEC sp_configure 'max text repl size (B)'

IF NOT EXISTS(SELECT * from @repltable where config_value = 2147483647 and run_value = 2147483647 )
PRINT N'ERROR: Max text repl size is not correct!'
ELSE
PRINT N'PASS: Max text repl size is correct.'

IF NOT EXISTS (SELECT db.owner_sid FROM sys.databases db WHERE db.database_id = DB_ID() AND db.owner_sid = 0x01)
PRINT N'ERROR: Database owner is not sa account!'
ELSE
PRINT N'PASS: Database owner is sa account.'

IF NOT EXISTS( SELECT * FROM sys.databases db WHERE db.database_id = DB_ID() AND db.is_trustworthy_on = 1 )
PRINT N'ERROR: Trustworthy bit is not on!'
ELSE
PRINT N'PASS: Trustworthy bit is on.'

IF NOT EXISTS( SELECT * FROM sys.databases db WHERE db.database_id = DB_ID() AND db.is_broker_enabled = 1 )
PRINT N'ERROR: Service broker is not enabled!'
ELSE
PRINT N'PASS: Service broker is enabled.'

IF NOT EXISTS( SELECT * FROM sys.databases db WHERE db.database_id = DB_ID() AND db.is_honor_broker_priority_on = 1 )
PRINT N'ERROR: Service broker priority is not set!'
ELSE
PRINT N'PASS: Service broker priority is set.'

PRINT N'Done!'

Branch_Exit:

  • Again we’re looking for INFO and PASS only:

INFO: Targetted database is CM_CTP.
INFO: Running verifications....
PASS: CLR is enabled.
PASS: Max text repl size is correct.
PASS: Database owner is sa account.
PASS: Trustworthy bit is on.
PASS: Service broker is enabled.
PASS: Service broker priority is set.
Done!

That’s it, use the failover wizard to switch back to the CMSQLAONA replica node as the Primary instance

Set the SQL SPN’s

While we’re running SQL with a domain user account we’re going nowhere at the application layer (ConfigMgr) without Service Principal Names (SPN’s). We’ll create them by hand.

  • RDP to your Domain Controller or wherever you are hosting Microsoft DNS

  • Open ADSIEDIT

  • Navigate down to Users and find the CN-CMClusterService entry

image

  • Double click it or select Edit

  • Add in the following SPN entries so that ConfigMgr can find the SQL Service:

MSSQLSvc/CMSQLAONA:1433

MSSQLSvc/CMSQLAONA.SMSM.COM:1433

MSSQLSvc/CMSQLAONB:1433

MSSQLSvc/CMSQLAONB.SMSM.COM:1433

MSSQLSvc/CMSQLAONC:1433

MSSQLSvc/CMSQLAONC.SMSM.COM:1433

image

  • We only need the CMSQLAONA and CMSQLAONB entries, since they are the active replicas in synchronous mode that ConfigMgr will use, but we’ll add CMSQLAONC so that you can play around with changing the roles in the Availability Group

  • Select OK

image

  • Select OK

Configure the Availability Group for Maintenance Mode (failover = Manual)

Before we can let ConfigMgr take a look at the new Availability Group we need to set it to Manual Failover mode. Perhaps in the future we’ll be able to work on Availability Groups without having to put them into manual mode, which would be every single time you service the ConfigMgr product, as is the case with Management Point Replicas which need to literally brought down before you can upgrade. I’d like to see these two areas ironed out to reduce administrator burden from lighting up cool features.

  • Return to CMSQLAONA and to the SQL Management Studio

  • Select CMSQLAONA which should be the Primary instance, check the Dashboard to confirm

  • Right click your Availability Group and select Properties

image

  • Set the Failover mode for CMSQLAONA and CMSQLAONB to Manual

image

  • You have to do this on the Primary instance, which means the correct SQL server, as I said the dashboard lets you know which Replica is the Primary instance.

  • You can see the Failover mode in the Dashboard

image

  • Once they are set to Manual you are good to move onto configuring ConfigMgr

Configure ConfigMgr to use the SQL AlwaysOn Availability Group

We can now turn to ConfigMgr and ask it politely to start using our new Availability Group.

  • Launch ConfigMgr Setup from the installation folder, not the installation media. Run C:\Program Files\Microsoft Configuration Manager\bin\X64\Setup.exe and select Run as administrator

image

  • Select Next

image

  • Select Perform site maintenance or reset this site

image

  • Select Modify SQL Server configuration

image

  • Enter the Availability group Listener FQDN for your ConfigMgrAG Availability Group

  • Select Next

image

  • It’ll detect the SQL AlwaysOn Availability Group and thrown a warning before you proceed

  • Select Begin

  • Open the ConfigMgrSetup log using LogLauncher

  • Keep an eye on that log and pray!

  • Once it is done you’ll get green in the configuration wizard

image

  • You’ll also see happiness in the log, tailing off with the following

image

  • If you are seeing SSPI errors review your SPN work, most likely related, other issues could be connectivity, not enough account permissions, some steps missed

  • Go check out SMSDBMON on your Primary, it’ll show you DB activity, if there is a problem it’ll show there

Reconfigure Availability Group Failover mode

We’ve now got ConfigMgr pointing at the Availability Group, we can now return the Failover Mode to Automatic.

  • Return to CMSQLAONA and to the SQL Management Studio

  • Select CMSQLAONA which should be the Primary instance, check the Dashboard to confirm

  • Right click your Availability Group and select Properties

image

  • Set the Failover mode for CMSQLAONA and CMSQLAONB to Automatic

  • Select OK

Reporting Point

If you had one running its most likely pointing at the old SQL Database that was home for the site database, that’ll be all ugly and broken now, so remove the Reporting Point role, add the role back, when configuring the role use the Availability Group Listener. The SRSRP log should show that the data sources have been updated with the Availability Group Listener name.

image

If your going to edit reports you’ll need the latest Report Builder V3, which you can find here

WSUS

Forget it. It’s unsupported.

Are there any options?

Yes, read here, it has everything to do with WSUS trying to be a bad boy and putting the SUSDB database into single-user mode, which an Availability Group won’t have any of. As you’ll see in this guide, its just a matter of procedure, and you can have WSUS running its database in an Availability Group, not the one being used by ConfigMgr, a new one dedicated for WSUS usage.

A great source of information alongside the SQL Documentation library came from here Prepare to use SQL Server Always On availability groups with Configuration Manager, which takes you to the ConfigMgr Documentation library, a world-class documentation library.

Another source very worthy of a mention is this article from Benjamin Reynolds at Microsoft titled Moving the ConfigMgr site database to an Always On Availability Group, he writes up on how to do what I do above, but he does it to a production database while minimising down time. You’ll find both articles complement each other in that Benjamin skips over some aspects whereas I document their steps. Using both you can build out your lab and put together an ace plan to do a production run at some point.