Create an SQL 2016 AlwaysOn Availability Group – Lab

Server Configuration

  • 2x Windows Server 2016 virtual servers; Server01, Server02
  • Disk Config:
    • Apps Disk on SCSI Controller 0
    • SQL DB Disk on SCSI Controller 1
    • SQL Log Disk on SCSI Controller 1
    • SQL TempDB Disk on SCSI Controller 2
  • SQL 2016 with SP2 Installed using DEFAULT instance
  • SQL Server service account and computer accounts granted access to backup share
  • An empty database called db1 on Server01
  • Latest Windows Updates applied
  • File Share configured on a third server for hosting the file share witness

Prepare Cluster

Install Failover Clustering

Install-WindowsFeature Failover-Clustering -IncludeManagementTools
Test-Cluster -Node Server01, Server02

Configure Failover Clustering

New-Cluster -Name SQLAAG01 -Node Server01, Server02 -StaticAddress
Set-ClusterQuorum -NodeAndFileShareMajority \\Server03\AAGFSW$

Configure SQL AlwaysOn

Set Parameters

$Domain = "sqldev.local"
$SQLBackup = "\\Server01\backups"
$DB = "DB01"
$Server01Object = Get-Item "SQLSERVER:\Sql\Server01\DEFAULT"
$Server02Object = Get-Item "SQLSERVER:\Sql\Server02\DEFAULT"
$Server01Nm = $Server01Object.Name
$Server02Nm = $Server02Object.Name
$Server01Instance = "DEFAULT"
$Server02Instance = "DEFAULT"
$ReplicaPort = "5022"
$FailoverMode = "Automatic"
$AvailabilityMode = "SynchronousCommit"
$EndpointURLSrv01 = "$Server01Nm" + "." + "$Domain" + ":" + "$ReplicaPort"
$EndpointURLSrv02 = "$Server02Nm" + "." + "$Domain" + ":" + "$ReplicaPort"
$AGName = "AG01"

Enable AlwaysOn

Import-Module sqlps
Enable-SqlAlwaysOn -ServerInstance Server01 -Force
Enable-SqlAlwaysOn -ServerInstance Server02 -Force

Create AlwaysOn Group

# Backup the database
Backup-SqlDatabase -Database "$DB" -BackupFile "$SQLBackup\$DB.bak" -ServerInstance "$Server01Nm"
Backup-SqlDatabase -Database "$DB" -BackupFile "$SQLBackup\$DB.log" -ServerInstance "$Server01Nm" -BackupAction Log

# Restore the database and log on node2
Restore-SqlDatabase -Database "$DB" -BackupFile "$SQLBackup\$DB.bak" -ServerInstance "$Server02Nm" -NoRecovery
Restore-SqlDatabase -Database "$DB" -BackupFile "$SQLBackup\$DB.log" -ServerInstance "$Server02Nm" -RestoreAction Log -NoRecovery

# Create an in-memory representation of the primary replica.
$primaryReplica = New-SqlAvailabilityReplica -Name "$Server01Nm" -EndpointURL "TCP://$EndpointURLSrv01" -AvailabilityMode "$AvailabilityMode" -FailoverMode "$FailoverMode" -Version $Server01Object.Version -AsTemplate

# Create an in-memory representation of the secondary replica.
$secondaryReplica = New-SqlAvailabilityReplica -Name "$Server02Nm" -EndpointURL "TCP://$EndpointURLSrv02" -AvailabilityMode "$AvailabilityMode" -FailoverMode "$FailoverMode" -Version $Server02Object.Version -AsTemplate

# Create the availability group
New-SqlAvailabilityGroup -Name "$AGName" -Path "SQLSERVER:\SQL\$Server01Nm\$Server01Instance" -AvailabilityReplica @($primaryReplica,$secondaryReplica) -Database "$DB"

Verify in SQL whether it’s created properly

Check the ports:

SELECT type_desc, port FROM sys.tcp_endpoints;

Check the endpoints are started:

SELECT state_desc FROM sys.database_mirroring_endpoints;

Start an endpoint:

ALTER ENDPOINT Endpoint_Mirroring
FOR database_mirroring (ROLE = ALL);

Check permissions:

SELECT 'Metadata Check';
   CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))
   FROM sys.server_permissions SP , sys.endpoints EP
   WHERE SP.major_id = EP.endpoint_id
   ORDER BY Permission,grantor, grantee;

Continue the Availability Group configuration

# Join the secondary replica to the availability group.
Join-SqlAvailabilityGroup -Path "SQLSERVER:\SQL\$Server02Nm\$Server02Instance" -Name "$AGName" 

# Join the secondary database to the availability group.
Add-SqlAvailabilityDatabase -Path "SQLSERVER:\SQL\$Server02Nm\$Server02Instance\AvailabilityGroups\$AGName" -Database "$DB"

This is an alternate scripting method to create an Availability Group

# Alternative way to create the group
$PrimaryServer = Get-Item "SQLSERVER:\SQL\Server01\DEFAULT"
$SecondaryServer = Get-Item "SQLSERVER:\SQL\Server02\DEFAULT"
$PrimaryReplica = New-SqlAvailabilityReplica -Name "Server01" -EndpointUrl "TCP://Server01.sqldev.local:5022" -FailoverMode "Automatic" -AvailabilityMode "SynchronousCommit" -AsTemplate -Version 13
$SecondaryReplica = New-SqlAvailabilityReplica -Name "Server02" -EndpointUrl "TCP://Server02.azuredev.local:5022" -FailoverMode "Automatic" -AvailabilityMode "SynchronousCommit" -AsTemplate -Version 13
New-SqlAvailabilityGroup -InputObject $PrimaryServer -Name "AG01" -AvailabilityReplica ($PrimaryReplica, $SecondaryReplica) -Database @("DB01")

Configure Listener

New-SqlAvailabilityGroupListener -Name AG01-Listener ` -StaticIp '' ` -Path SQLSERVER:\Sql\$Server01\$Server01Instance\AvailabilityGroups\$AGName

Runbooks Missing from the Service Manager Console

Sometimes when you create a Runbook in Orchestrator and then synchronise the Orchestrator connector the Runbook doesn’t appear in the Runbooks view in the Service Manager Console.  This is because the Orchestrator authorisation cache hasn’t updated yet to allow the connector access to the folder / Runbook.

Run the following SQL script against the Orchestrator database to resolve the issue:

TRUNCATE TABLE [Microsoft.SystemCenter.Orchestrator.Internal].AuthorizationCache


Installing Service Manager – “A required SQL Server service is not running”

N.B. Credit to Stefan Johner who first blogged about it here!

So you’ve setup your Windows Server Failover Cluster and deployed SQL AlwaysOn Availability Group to it.  You’ve tested the port, and failover using a test database to ensure it’s accessible through the firewall or network security group.

You come to install the Service Manager database but when you input the name of your AAG listener, the instance is automatically detected but you receive the entitled message “A required SQL Server service is not running on MSSQLServer”.  Similarly if you attempt the deployment from the command line you’ll get an “Access was denied” error in the log.

There are two solutions to this issue.  One, very cumbersome method is to deploy to a single SQL server, then go through the process of updating the registry and SQL table properties (as you would if you were migrating SQL servers).  This will resolve the problem, but it can be very painful.  It also doesn’t solve your problem if you are installing a secondary Management Server, or recovering a Management Server.

An alternate solution is to add a simple entry to the registry of all SQL servers participating in your SQL AAG.  This will allow named pipes to be opened in a fashion that the Service Manager installer understands.

Registry setting as follows:

Property Value
Key: HKLM\System\CurrentControlSet\Services\LanmanServer\Parameters
Value Type: Multi-String value
Name: NoRemapPipes
Data: winreg

Once this value is in place the installer will instantly see the SQL AAG Listener and you can continue deploying Service Manager!

N.B. Edit the registry at your own risk!