Part 1: Deploy SQL Server 2016 Availability Groups

As I was about to start deploying a SQL server for use by VMM, I was reminded about how SQL is licensed when it’s for the purpose of hosting System Center databases. If you’re licensing your Hyper-V hosts with Cloud Platform Datacentre licenses, any SQL licences required for your System Center DBs are already covered.

With that in mind, I decided that if this was actually a production build, I’d build out a 2 node SQL AlwaysON cluster that uses Availability Groups for hosting all System Center databases.

The only database we will place here (during the guide) is the VMM database. For reasons I’ll explain a little further on, I had to go with the Enterprise edition of SQL, be aware though that only SQL Standard licensing is covered when using the Cloud Platform Datacentre licensing model for System Center.

The SQL AlwaysON Availability Group feature is basically (to quote Microsoft):

…a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Introduced in SQL Server 2012, Always On Availability Groups maximizes the availability of a set of user databases for an enterprise. An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases. Optionally, secondary databases can be made available for read-only access and/or some backup operations.

An availability group fails over at the level of an availability replica. Failovers are not caused by database issues such as a database becoming suspect due to a loss of a data file, deletion of a database, or corruption of a transaction log.

For more information on the benefits I’d suggest having a read through the rest of the article: https://msdn.microsoft.com/en-us/library/hh510230.aspx

Prerequisites

This part of the guide won’t be focusing on the installation of SQL itself and is not intended as a best practice example, but more on the configuration of Availability groups. Before we can do that though, you will need the following:

Deploy 2 domain joined Virtual Machines to your preferred specifications, see example below

Virtual Machine	vCPU	RAM (GB)	Storage (GB)
SCSQL1	2	16	50GB Database Drive (E:) 50GB Log Drive (L:)
SCSQL2	2	16	50GB Database Drive (E:) 50GB Log Drive (L:)

• Install .NET Framework 3.5.1 on both nodes
• Install Failover Cluster manager on both nodes

Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools

• Create a Failover Cluster from both SQL nodes – full guide HERE

Test-Cluster –Node SCSQL1, SCSQL2
New-Cluster –Name MyCluster –Node Server1, Server2 –StaticAddress 192.168.1.12 -NoStorage

• Install SQL Server 2016 with SP1 on both nodes – Full guide HERE:  Make sure to select “New SQL Server stand-alone installation”
• Install Microsoft SQL Server Management Studio (I install on both nodes for convenience) – Download Link
• Apply all available Windows Updates to the VMs

Configure Cluster Quorum

A nice feature called Cloud Witness was added to Failover Clustering in Server 2016 which allows users to create a cluster quorum that sits in an Azure Storage Account.

This guide assumes that you already have an Azure account and have set up the relevant storage accounts and keys, with that in mind, here is a quick how-to on how to make use of that storage account for creating a Failover Cluster Manager Cloud Witness. Depending on your role within the organisation, you may have to request this information from another IT team, if that is the case, then you’ll need to know the following pieces of information:

• Azure storage account name
• Azure storage account key
• Azure service endpoint – this is auto-configured as core.windows.net and should only really need changing when we start to consume services like this from Azure Stack

If you have access to your companies Azure portal you can find the information you need by navigating to (once logged in):

Storage Accounts > Relevant Storage Account > Access Keys

From here you should be able to copy the key you need to the clipboard (See screenshot).

Armed with this information, let’s go back to Failover Cluster Manager and configure our quorum.

If you don’t have access to an Azure account, you can still configure a quorum using the methods available in Server 2012 R2 and before. The following TechNet article provides a wealth of information of cluster quorums and their setup (the article is for Server 2012 but is still relevant in 2016 unless you’re using a Cloud quorum).

https://technet.microsoft.com/en-us/library/jj612870(v=ws.11).aspx

However, we’re using a cloud witness so let’s get on with it shall we?

Within Failover Cluster Manager, right-click the cluster name on the left pane and select “More Actions” and “Configure Cluster Quorum Settings”

Select “Next”, “Advanced quorum configuration” and “Next”

Select “All Nodes” then “Next”

Select “Configure a cloud witness” then click “Next”

Populate the “Azure storage account name” and “Azure storage account key” fields with the information you grabbed from Azure earlier and click “Next”, “Next” and “Finish”

You should now be able to see your quorum listed under the core resources of your cluster…good job!

Configuring Availability Groups

Armed with a fresh new SQL deployment, we can now start to configure our availability groups. As I mentioned above, we’ll be using this SQL deployment to host our SCVMM DB but the intention would be that any other System Center DBs you have or will add later should be placed here also.

One of the limitations of using SQL Standard is that you can only have one DB per Availability Group, which doesn’t sound like an issue as you can just create a new availability group per DB. However, it does mean that you can’t group databases together that will fail over together. The reason I chose to go with SQL Enterprise though is because of the way Availability Groups are created and the issue this caused me while running through the SCVMM installation, more on this to follow.

First we need to enable the Availability Group feature on your SQL instance. To do this:

Open up “SQL Server Configuration Manager”, select “SQL Server Services” on the left pane and right-click your SQL instance on the right pane and select “Properties”.

Select the “AlwaysOn High Availability” tab and place a tick in “Enable AlwaysOn Availability Groups” and click “OK”

Now restart the service.

We can now go on and create an Availability Group using Microsoft SQL Server Management Studio

You can’t create an Availability Group without first having a DB to reside in it. Being that on Standard, you can only have one DB per group, SCVMM wouldn’t be able to add its own DB to this group. Here are the ways I tried to work round this:

Pre-stage the SCVMM DB on SCSQL1 and point the SCVMM installer towards it (at this point you could also just let the installer create the DB, the outcome will be the same). The installation completed successfully but we now have a DB that isn’t a member to an Availability Group. When adding the DB to an Availability Group, its connection string and IP will change and SCVMM is still set to connect to SCSQL1. To change this it’s basically an uninstall/reinstall of the SCVMM management server while retaining the DB. When running through the reinstall, it rolled back and threw an error as it kept trying to change the DB recovery model back to Simple (even though Full was selected when it was created). POINT TO NOTE, it’s entirely possible that you can get this working but for me the installer just wouldn’t complete irrelevant of what I tried.

I then decided to install SQL Enterprise as it allows multiple DBs per Availability Group. The idea here was to create a temporary DB to get the Availability Group up and running and then point the SCVMM installer towards the group listener and let it create its own DB as normal. This worked without issue.

With the above in mind, let’s move on and run through that process.

Create a Temporary Database

Right-click on “Databases” on the left pane and select “New Database…”

Type a name for your database, this can be anything as this DB can be deleted later.

Under “Options” make sure the “Recovery Model” is “Full” and click “OK”

Now before we can create an Availability Group there are two last task that need completed:

• Carry out a full backup of the database you just created
• Give your SQL CNO (Cluster Name Object) permission to create object within its OU in Active Directory. This is a requirement as the cluster needs to create a computer object for the Availability Group that you’re about to add.

Backup your Database

Right-click on your database on the left pane and select “Tasks” and then “Backup”

Just select the defaults here and click “OK”, we don’t care about this backup as it’s only to allow us to use the DB in the creation of the Availability Group

Add Permissions for Cluster CNO

We need to give your cluster computer object specific permissions over the OU it resides in. It needs these to be able to create other computer objects for any cluster roles it deploys.

You may have to enable Advanced Features within the Active Directory Users and Computers MMC to view the security tab on an OU. You can do this as follows:

Press “Windows +R” and type “dsa.msc” to launch the Active Directory Users and Computers MMC

Click on “View” in the menu bar and select “Advanced Features”

Using Active Directory Users and Computers MMC, browse to the OU that holds your cluster CNO, right click and select “Properties”

Select the “Security” tab and click “Add”

Click Object Types and place a tick in “Computers” to include them in/limit the search scope and click “OK”

Type your SQL cluster CNO under “Enter the object names to select” and click “OK”

Now click “Advanced”, highlight the account you just added and select “Edit”

Under “Permissions”, place a tick in “List contents” and “Create Computer Objects”.

Scroll down to “Properties” and make sure there is also a tick in “Read all properties” and click “OK” 3 times

Create Availability Group…FINALLY!

Browse to “AlwaysOn High Availability” and right-click on “Availability Group”. Now select “New Availability Group Wizard”

Enter a name that makes sense and place a tick in “Database Level Heath Detection”. Now click “Next”

Select the temp database that you created earlier. Note that the status shows as “Meets prerequisites”. This is because we did a full backup earlier. Now click “Next”.

Click “Add Replica…”. Under “Server Name”, type the name of your second SQL node and click “Connect”

Configure Automatic Failover and Synchronous Commit as show below and click on the “Listener” tab.

Select “Create an availability group listener now”. Enter a name, 1433 for the port and select “Static IP” from the dropdown.

Click “Add…”, select the correct subnet from the dropdown and type in an IP address. Now click “OK” and “Next”.

Select “Full” as the synchronization preference and type in a file share name that both nodes have access to and click “Next”

This screen will run through the validation, assuming all it well, click “Next” and then “Finish”. If there are any errors, you can click on the error link and it’ll give you additional information to assist in troubleshooting.

Here’s how your “AlwaysOn High Availability” tree should look now.

Now you have your Availability Group and listener up and running, we can continue on to our SCVMM installation