Installing Highly Available SQL 2016 with SP1 for System Center 2016 – Part 1 (SQL Server AlwaysOn Availability Groups)

In this 3 part guide, I will go through the installation of a Highly Available SQL Server 2016 with the SP1 installation.  There are 2 SQL Servers hosting the Databases and 2 Servers hosting Reporting Services.  This is probably the most common installation I have seen in the field. 

Part 1 – This Article
Part 2 – Installing Highly Available SQL 2016 with SP1 for System Center 2016 – Part 2 (Network Load Balancing Reporting Services)
Part 3 – Installing Highly Available SQL 2016 with SP1 for System Center 2016 – Part 3 (Reporting Services on a SQL Cluster)

The following diagram gives a simple scenario of how Service Manager servers/services would connect in this SQL implementation:

This guide is suitable for:

• System Center Operations Manager 2016
• System Center Configuration Manager 2016
• System Center Virtual Machine Manager 2016
• System Center Orchestrator Manager 2016
• System Center Service Manager 2016

Prerequisites

Create a Service Account in Active Directory, it doesn’t need any special permissions.  In this article it will be called Srv-SQL

I have 2 Server 2016 machines joined to the domain with a static IP Address called CC-HO-SQL1 and CC-HO-SQL2.  We will be installing SQL Server 2016 Enterprise Edition with SP1.

The Installation

Insert the SQL Server Media and run setup.exe

Click Install and then click New SQL Server stand-alone installation or add features to an existing installation

Enter your Product Key or click Specify a free edition for a 180 free trial

Click I accept the licence terms and click Next

Select Microsoft Update if you wish and click Next

Acknowledge the prerequisites and click Next.

(Note: You may need to open up the SQL ports in the Windows Firewall depending on your environment.  Use this guide if necessary https://technet.microsoft.com/en-GB/library/ms175043(v=sql.110).aspx)

Select the following features:

• Database Engine Services
• Full-Text and Semantic Extractions…..
• Analysis Services

Then click Next

On the Instance Configuration screen you may change the Instance Name if you wish to something like SCOM but MSSQLSERVER is more than suitable.  Click Next

Now enter your SQL Service Account details

They should look like this, note set the SQL Server Agent to Automatic.  Once complete click the Collation tab.

Click Customize for the Database Engine

Click the SQL collation, used for backwards compatibility radio button and select SQL_Latin1_General_CP1_CI_AS Collation and click OK

Do not change the Analysis Services Collation.  Click Next

On the Database Engine Configuration screen click Add Current User and then click Add… and enter your SQL Service Account from AD.

You can also change the Database and Log file locations by clicking the Data Directories Tab if you wish.  Once done click Next

On the Analysis Services Configuration screen, add the Current User and Add your SQL Server Service Account.  Click Next

Click Install on the summary screen

Once the installation has completed successfully click Close

For those of you who are “Eagle Eyed” you may of noticed we didn’t install the SQL Server Management Studio.  This is because its now a separate download and installer for SQL Server 2016.  Open your favourite browser and navigate to https://msdn.microsoft.com/en-us/library/mt238290.aspx.  Download the latest version that is in GA (General Availability) and run the setup file.

Click Install and the installation will start

Once the installation is complete click Close

Now click Start > SQL Server 2016 > SQL Server Management Studio and ensure it opens correctly and can connect to your SQL Instance.

On the Connect to Server screen click Connect

You can see here everything is fine.

Now do exactly the same on your second SQL Server.

Configuring SQL Server 2016 AlwaysOn Availability Groups

Without doubt SQL Server 2016 AlwaysOn Availability Groups are awesome, especially for the System Center suite. AlwaysOn Availability Groups allow you to fail over a group of databases as a single entity, unlike database mirroring where you can only do so one database at a time.  It brings SQL Server high availability and disaster recovery to a whole new level by allowing multiple copies of a database be highly available and allowing you to use them for read-only workloads and offloading management tasks such as backups.

Note.  It is possible to use SQL Server 2016 Standard Edition.  The Standard Edition Basic Availability Groups are managed and created in the same way like the traditional Enterprise AlwaysOn Availability Group, but there are more limitations which are almost equally when you should run Database Mirroring on a Standard Edition.  The limitations of Basic AG’s are:

Basic Availability Groups

• The main limitations of Basic HA is that you’re only allowed to add 1 database per group!  However, to get around this you could create additional Availability Groups  
• They are not suitable for the Service Manager DW Role as it creates multiple DB’s!
• Limit of two replicas (primary and secondary). After you’ve added the secondary replica, the “Add Replica” button will automatically be disabled.
• Basic HA doesn’t allow you to use a readable secondary, which means that there are no backups possible on the secondary replicaNo read access on secondary replica. The wizard only has the “No” option, so you can’t configure a readable secondary.

Create File Share Witness

Somewhere on your network NOT on the SQL servers create a file share with both SQL Servers having Modify access. 

Windows Failover Cluster Feature Installation

AlwaysOn Availability Groups require a Windows Server Failover Cluster, first we need to add the Windows Failover Cluster Feature to both SQL Servers. To add the Failover Clustering feature:

Open the Server Manager console and select Add roles and features. This will launch the Add Roles Features Wizard

Click Next until you reach the Select Features dialog box. Select the Failover Clustering checkbox. When prompted with the Add features that are required for Failover Clustering dialog box, click Add Features. Click Next.

Click Install to install the Failover Clustering feature.

Do the same on the secondary SQL Server.

Windows Failover Clustering Configuration for SQL Server 2016 AlwaysOn Availability Groups

It is assumed that you have the appropriate rights in Active Directory. If unsure see the following guide Failover Cluster Step-by-Step Guide: Configuring Accounts in Active Directory.

To configure Windows Failover Clustering

Launch Failover Cluster Manager from the tools menu in the Server Manager console.

Within Failover Cluster Manager, click the Validate Configuration… link.

In the Validate a Configuration Wizard dialog box, click Next.

In the Select Servers or a Cluster dialog box, add the server hostnames of the SQL Server instances that you want to configure as replicas in your Availability Group. Click Next.

In the Testing Options dialog box, make sure that the option Run all tests (recommended) is selected. Click Next.

In the Confirmation dialog box, click Next.

In the Summary dialog box select Create the cluster now using the validated nodes… and click Finish to create the Windows Failover Cluster.

(Note:  It is likely you receive several warning messages.  However, if you see any Error messages, you need to remediate those first prior to creating the Windows Server Failover Cluster)

Click Next on the Before You Begin page.

Enter a Cluster name.  In this case I will be using CC-HO-SQL-AO.  Once entered click Next

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

The cluster will now  create the Windows Failover Cluster using the servers as nodes of the cluster, add DNS and Active Directory entries for the cluster hostname.

One the Summary screen note there is a warning about an appropriate disk this is fine as we will be configuring Quorum shortly.

To configure the cluster quorum configuration to use a file share, right-click on the cluster name, select More Actions and click Configure Cluster Quorum Settings… We will be configuring a file share witness for our cluster quorum setting. By default, the wizard will configure the cluster attempt to use Node Majority which in this scenario will not work due to there being two.

Click Next on the Before You Begin page.

Select Select the quorum witness and click Next

In the Select Quorum Witness page, select the Configure a file share witness option. Click Next

In the Configure File Share Witness page, type the UNC path of the file share that you created earlier in the File Share Path: text box. Click Next

In the Confirmation page, click Next.

In the Summary page, click Finish.

In the Failover Cluster Manager console click Nodes and ensure both are UP

Prepare AD For AlwaysOn Availability Groups Listener Creation

First of all you need to allow the Cluster Service to create Computers in Active Directory.  Log onto your Domain Controller and open Active Directory Users and Computers.

Click the View menu and select “Advanced Features.”

Right-click the Computers container and choose Properties.  (Or the OU which your SQL/SQL Cluster servers are located)

Click on the Security Tab and click Add. 

Click Object Types and check the Computers option and click Ok.

Enter your SQL Cluster name and click OK.

Back in the Properties dialog, click the Advanced button and the “Advanced Security Settings for Computers” dialog should appear.

Highlight the SQL Cluster account and click Edit

Select “Read all properties” and “Create Computer objects.” Click OK until you’re back to the Active Directory Users and Computer window

That’s AD prepared.

Enable SQL Server 2016 AlwaysOn Availability Groups Feature

Once the Windows Server Failover Cluster has been created, we can now proceed with enabling the AlwaysOn Availability Groups feature in SQL Server 2016.  This needs to be done on all of the SQL Server instances that you will configure as replicas in your Availability Group. To enable the SQL Server 2016 AlwaysOn Availability Groups feature.

Open SQL Server Configuration Manager and select SQL Server Services

Double-click the SQL Server (MSSQLSERVER) service to open the Properties dialog box.

In the Properties dialog box, select the AlwaysOn High Availability tab. Check the Enable AlwaysOn Availability Groups check box. This will prompt you to restart the SQL Server service. Click OK.

Right Click SQL Server (MSSQLSERVER) and click Restart.

Close all the consoles.

Do the same steps to enable AlwaysOn Availability Groups on the second SQL Server.

Create and Configure SQL Server 2016 AlwaysOn Availability Groups

Availability Groups can be created on existing databases or even a temporary one in preparation for application installation.  First of all you will need to create a temporary database. This is so that the System Center products will use the AlwaysOn Availability Group when creating the farm configuration and the admin content databases. After the System Center product has been created, this database can be removed from the Availability Group configuration and deleted from the instance.

To create and configure a SQL Server 2012 AlwaysOn Availability Group,

Open SQL Server Management Studio on either SQL Server and connect to the SQL Server instance

Right Click Databases and click New Database.  Give the DB a name such as “Temp” and click Ok.  No other settings are required.

Right Click the New DB “Temp” you have created and navigate to Tasks > Back Up…

Click OK to create a backup and Click OK once successful.  (You may change the location of the backup if you wish)

In Object Explorer, expand the AlwaysOn High Availability folder. Right-click on the Availability Groups folder and select the New Availability Group Wizard… option. This will launch the New Availability Group Wizard.

In the Introduction page, click Next.

In the Specify Availability Group Name page, enter the name of the Availability Group in the Availability group name: field. Click Next.

In the Select Databases page, select the checkbox beside the database that you want to include in your Availability Group. The databases have to be in Full recovery model prior to joining them in the Availability group. Click Next.

In the Specify Replicas page, under the Replicas tab, click the Add Replicas button and connect to the secondary SQL Server.

Configure the following options

• Automatic Failover (Up to 2) :          Checked
• Synchronous Commit (Up to 3) :      Checked
• Readable Secondary:                           Yes

In the Endpoints tab, verify that the port number value is 5022.

In the Backup Preferences Tab ensure Prefer Secondary radio button is selected.

In the Listener tab, select the Create an availability group listener option. Enter the following details.

• Listener DNS name: Name that you will use in your application connection string
• Port: 1433

Click the Add… button to provide an IP address. In the Add IP Address dialog box, enter your preferred virtual IP address in the IPv4 Address field. Click OK. Click Next.

In the Select Initial Data Synchronization page, select the Full option. Provide a shared folder that is accessible the replicas and that the SQL Server service account used by both replicas has Write permissions to. This is just a temporary file share to store the database backups that will be used to initialize the databases in an Availability group. If you are dealing with large databases, it is recommended that you manually initialize the databases prior to configuring them as your network bandwidth may not be able to accommodate the size of the database backups. Click Next.

In the Validation page, verify that all validation checks return successful results. Click Next.

In the Summary page, verify all configuration settings and click Finish. This will create and configure the AlwaysOn Availability Group and join the databases.

In the Results page, verify that all tasks have been completed successfully.

You can now use the Availability Groups listener name in your application connection string. Keep in mind that you need to manually add new databases in the Availability Group even though your application has already been using the listener name!

If you get the following error (Error Code 52) then permissions are incorrect in AD.  Go back to the AD Section above.

Now that’s SQL Server complete.  In the next part of this series we will install SQL Server Reporting Services using NLB.  You can view the next part here.