June 12, 2016

SQL Server 2016 - Always On Availability Groups

In this blog post I will provide basic step by step guide how to install and configure SQL Server 2016 with Always On Availability Groups. Migration WSUS from Windows Internal Database to Availability Group will be as example.

This guide has the following steps:
1. Preparation.
2. Installing SQL Server 2016 on both SQL servers.
3. Installing Failover Clustering Feature on SQL servers and enable AlwaysOn.
4. Working with WSUS.
5. Create Availability Group.
6. Create logins and grant permissions.
7. Create Availability Group Listener.
8. Finish WSUS server reconfiguration.

Two Windows Servers 2012 R2 - SQL01 and SQL02.

Each SQL server has the following drive mapping:
C - System
D - SQL Installation
E - Database volume
F - SQL Logs
G - Backups

WSUS server is Windows Server 2012 R2 with default configuration.

Dedicated Active Directory Organizational Unit for SQL servers computer objects.

Opened firewall TCP ports on both SQL servers - 1433, 1434, 5022.

Installing SQL Server 2016 on both SQL servers. 
From SQL Server Installation Center click Installation then "New SQL Server stand-alone installation or add features to an existing installation".

Enter Product Key or select Evaluation, then click Next.

Accept the license terms and click Next.

Select Microsoft Update options and click Next.

On Install Rules page check all warnings. Then click Next.

On Feature Selection page select "Database Engine Services" and "SQL Server Replication". In my case I changed Directories paths to dedicated D: drive. Click Next to Proceed.

Leave default instance name and click Next.

Specify service accounts and collation settings. In my case I left it default and clicked Next.

On "Database Engine Configuration" page specify SQL Server administrators. In my case I selected domain administrator account.

Go to "Data Directories" tab and specify paths. Then click Next.

On Ready to Install page click Install.

Repeat the same steps on second SQL server.

Installing Failover Clustering Feature on SQL servers and enable AlwaysOn.

Open Server Manager - Manage - Add Roles and Features - Next - Role-Based or feature-based installation - Next - Select a server from the server pool - Next - Next - Select "Failover Clustering" checkbox (It will popup with additional features required) - Add Features then Next - Install

Repeat the same steps on second SQL server.

Open Failover Cluster Manager, right click on it and "Create Cluster..."

On Before You Begin page click Next.

On Select Servers page add SQL01 and SQL02 servers. Click Next to proceed.

Run validation tests by clicking Next.

Run all tests then Next. Wait until validation completes. Once all tests are passed specify Cluster Name and IP, then click Next. In my case it is SQL-CL01 with as IP.

Confirm configuration and click Next.

Then configure quorum settings.

Enable Always On Availability in SQL Server Configuration Manager on each SQL Server.

Restart SQL servers. Download and install SQL Server Management Studio.

Working with WSUS.

Download and install on WSUS server: "Microsoft® SQL Server® 2012 Native Client" and "Microsoft® SQL Server® 2012 Command Line Utilities".

Stop "IIS Admin Service" and "WSUS Service"

Run command line tool as administrator:
cd C:\Program Files\Microsoft SQL Server\110\Tools\Binn
sqlcmd -S \\.\pipe\MICROSOFT##WID\tsql\query
use master
alter database SUSDB set single_user with rollback immediate
sp_detach_db 'SUSDB'

Then copy SUSDB.mdf and SUSDB_log.ldf from "C:\windows\WID\Data" to SQL01.

Working with WSUS database: 
Attach database on SQL01.

Go to Database Properties - Options. Amend Recovery model to Full. Then backup the database, copy SUSDB.bak file to SQL02 and restore it with "RESTORE WITH NORECOVERY" option.

Create Availability Group on SQL01.

In SQL Server Management Studio - AlwaysOn High Availability - Availability Groups - New Availability Group Wizard... - Next.

Specify AG name and click Next. In my case it is WSUSAG.

Select SUSDB and click Next.

Add SQL02 as replica. Do not configure listener for now. Click Next and Yes on endpoint popup.

On Initial Data Synchronization page select Skip initial data synchronization and click Next. On validation page click Next then Finish.

Create logins and grant permissions.

On SQL01:
use master

On SQL02:
use master

Remove and add back SQL02 from replicas.

Create WSUSDB computer object in SQL OU and grant full access permissions to SQL-CL01 object.

Create Availability Group Listener.

Specify listener settings and click OK. In my case it is, 1433 port and WSUSDB (computer object which I created earlier).

On SQL02 join SUSDB to Availability Group.

The state of database will be changed to Synchronized.

Finish WSUS server reconfiguration.

Replace "MICROSOFT##WID" with "WSUSDB" in "SqlServerName" of in "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Update Services\Server\Setup"

Change WSUS Service to logon as Local System and grant dbo rights on SUSDB for WSUS01 computer.

Start "IIS Admin Service" and "WSUS Service".

Uninstall WSUS and Windows Internal Database features:
Uninstall-WindowsFeature UpdateServices-WidDB
Uninstall-WindowsFeature Windows-Internal-Database

Restart WSUS server.


  1. Hi Vyacheslav,

    I have a situation where secondary goes into resolving state during automatic failover. Node1 to node2 automatic failover works fine but node2 to node1 does not work and node1 goes into resolving state forever. Manual failover works fine both way. Automatic failover and synchronization is on for both servers. Here are the error messages I see in event viewer.

    Event ID: 1069
    Cluster resource 'XEN_DESKTOP' of type 'SQL Server Availability Group' in clustered role 'XEN_DESKTOP' failed.

    Event ID: 1205
    The Cluster service failed to bring clustered role 'XEN_DESKTOP' completely online or offline. One or more resources may be in a failed state. This may impact the availability of the clustered role.

    Event ID: 1254
    Clustered role 'XEN_DESKTOP' has exceeded its failover threshold.

    1. Hi,
      Is cluster quorum configured properly? What is the state of cluster core resources when you are getting node1 into forever resolving state?

  2. Vyacheslav,

    Thnx for your excellent article very useful to newbie like me. I have question

    I have 3 oltp DB's on 3 different sql servers 2008r2, we are planning on upgrading to 2016 with primary and secondary replicas for each 3priamary:3replica.

    Q1- we Can have 3primary :3replica(secondary) but New4th node with all 3 DBs on it for reporting purpose as we will need to join all 3DB's to generte reports .

    Can you guide me

    thanks in advance

    1. I would suggest to look at options in Microsoft Azure.