Thought I’d give one of the new features in ConfigMgr Technical Preview build 1705 called Improvements for SQL Server Always On Availability Groups a 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 SSMS-Setup-ENU-R16.5.3-B13.0.16106.4

Virtual Machines

Domain Controller
System Center Configuration Manager Technical Preview with TP1705 installed
SQL Server Replica\Node A (192.168.1.170) – 2GB Memory – Single disk
SQL Server Replica\Node B (192.168.1.171) – 2GB Memory – Single disk
SQL Server Replica\Node C (192.168.1.172 – 2GB Memory – Single disk)
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

VM1 – CMSQLAONA – 192.168.1.170
VM2 – CMSQLAONB – 192.168.1.171
VM3 – CMSQLAONA – 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:

Create a Network Share for the File Share Witness
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

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

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

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

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

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

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

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

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

Enter SQLAOCluster as the Cluster Name
Give the Cluster Name the IP of 192.168.1.174 or whatever IP you’ve set aside for you lab environment
Select Next

Select Next

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

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

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

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

Select Next

Select Select the quorum witness
Select Next

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

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

Confirm and click Next

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

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

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

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:

Select Installation

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

Tick to accept the license terms and select Next

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

Select Next

If it finds anything, let it install

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

Select Next

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)

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

Select OK

Add the local Administrators group
Add your domain account as well
Leave Data Directories, TempDB and FILESTREAM alone
Select Next

Put that path into the clipboard as we’re about to automate the silent unattended installation of two SQL Servers using a modified version of it

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

Edit the file and make the following amendments:

False to True for IACCEPTROPENLICENSETERMS
False to True for QUIET
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="" /AGTSVCPASSWORD=""

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

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!

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 CMSQLAONA, CMSQLAONB and CMSQLAONC
With the root nodes expanded it’ll look like this:

Right click CMSQLAONA and select Properties, then select Memory

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

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

ConfigMgr Task Sequence Agent
Configuration Manager Remote Control
CONFIGURATION_MANAGER_UPDATE
SMS Agent Host (if installed)
SMS_NOTIFICATION_SERVER
SMS_SITE_COMPONENT_MANAGER
SMS_EXECUTIVE
SMS_SITE_SQL_BACKUP
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…

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-.BAK (TRN if you are moving from one SQL AlwaysOn Availability Group to another)
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 node 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.

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:

SQL Management Studio Status Bar

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
Paste the same as above and click Execute
Select CMSQLAONC
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…

You’ll be presented with the Restore Database page

Select Device and select the ellipses …

Select Add
Navigate to C:\CMBackup and select the CMBackup file

Select OK

Select OK

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

Select OK to get on with the recovery of CM_CTP

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

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…

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

Select Server Roles

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…

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

Make sure public is ticked

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

Select SQL Server Services, right click SQL Server (MSSQLSERVER) and select Properties
Now visit the AlwaysOn High Availability tab
Select Enable AlwaysOn Availability Groups

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

Give the Full Control share permissions to Everyone

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

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

Select New Availability Group Wizard…

Have a read
Select Next

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

It see’s our database, select it

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

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…

Enter CMSQLAONB and select Connect

Select Add Replica…
Enter CMSQLAONB and select Connect

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

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

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!

Select Full
Enter the full path to the CMDBTransfer share you created earlier(\\CMSQLAONA\CMDBTransfer)
Select Next

And all the ducks line up
Select Next

Select Finish to get things underway

Time for more tea

More tea

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

From CMSQLAONA visit the SQL Management Studio
Expand out CMSQLAONA and the Availability Group nodes

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

Select Show Dashboard

Click on ConfigMgrAG to drill down

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

From SQL Management Studio right click your Availability Group, 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

Select Next

Tick CMSQLAONB which should be the nominated Replica for Failover
Select Next

Select Connect

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

Select Next

Select Next

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

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

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

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

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

Set the Failover mode for CMSQLAONA and CMSQLAONB to Manual

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

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

Select Next

Select Perform site maintenance or reset this site

Select Modify SQL Server configuration

Enter the Availability group Listener FQDN for your ConfigMgrAG Availability Group
Select Next

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

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

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

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.

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.

Source From : http://wmug.co.uk/wmug/b/r0b/archive/2017/06/14/sqlalwayson-and-sccm

SCCM Knowledge and Sharing

Wednesday, June 14, 2017

SQL AlwaysOn and SCCM