Pete Hinchley: Install Microsoft SQL Server 2012 and Configure an AlwaysOn Availability Group

In this article I will describe how to install Microsoft SQL Server 2012 SP1 on Windows Server 2012 R2 (Server Core). I will also outline the steps required to deploy a highly available SQL implementation by configuring a two-node AlwaysOn availability group.

Overview of Environment

Let's start with an overview of the environment:

Prerequisites

Perform this step on both SQL Servers.

A prerequisite for the installation of SQL Server 2012 is Microsoft .NET Framework 3.5. This feature can be installed using the following commands:

$mount = Mount-DiskImage -ImagePath C:\Media\en_windows_server_2012_r2_with_update_x64.iso -PassThru
$drive = ($mount | Get-Volume).DriveLetter + ':'

Add-WindowsFeature NET-Framework-Core -Source $drive\sources\sxs

DisMount-DiskImage -ImagePath C:\Media\en_windows_server_2012_r2_with_update_x64.iso

Installation

Perform this step on both SQL Servers.

We will prepare for the installation of SQL by creating an installation script named C:\Scripts\SQL.ini. This file specifies the options required to perform a silent installation of SQL Server. In this example, the only SQL features installed are the database engine and replication.

[OPTIONS]
ACTION="Install"
ENU="True"
QUIET="True"
QUIETSIMPLE="False"
UpdateEnabled="False"
FEATURES=SQLENGINE,REPLICATION
UpdateSource="MU"
HELP="False"
INDICATEPROGRESS="False"
X86="False"
INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"
INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"
INSTANCENAME="SQL01"
INSTANCEID="SQL01"
SQMREPORTING="False"
ERRORREPORTING="False"
INSTANCEDIR="C:\Instances\SQL01"
SQLUSERDBDIR="C:\Databases\SQL01"
SQLUSERDBLOGDIR="C:\Logs\SQL01"
SQLBACKUPDIR="C:\Backups\SQL01"
AGTSVCACCOUNT="NT AUTHORITY\SYSTEM"
AGTSVCSTARTUPTYPE="Automatic"
COMMFABRICPORT="0"
COMMFABRICNETWORKLEVEL="0"
COMMFABRICENCRYPTION="0"
MATRIXCMBRICKCOMMPORT="0"
SQLSVCSTARTUPTYPE="Automatic"
FILESTREAMLEVEL="0"
ENABLERANU="False"
SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"
SQLSVCACCOUNT="NT AUTHORITY\SYSTEM"
SQLSYSADMINACCOUNTS="BUILTIN\Administrators"
ADDCURRENTUSERASSQLADMIN="False"
TCPENABLED="1"
NPENABLED="1"
BROWSERSVCSTARTUPTYPE="Automatic"
IACCEPTSQLSERVERLICENSETERMS="True"

Use the following commands to install SQL Server using the configuration file:

$mount = Mount-DiskImage -ImagePath C:\Media\SQL_Svr_Enterprise_Core_2012_SP1.iso -PassThru
$drive = ($mount | Get-Volume).DriveLetter + ':'

Set-Location $drive\
& .\setup.exe /configurationfile=C:\Scripts\SQL.ini

DisMount-DiskImage -ImagePath C:\Media\SQL_Svr_Enterprise_Core_2012_SP1.iso

After SQL Server is installed, download Shared Management Objects (ENU\x64\SharedManagementObjects.msi) and PowerShell Tools (ENU\x64\PowerShellTools.msi) from the SQL Server 2012 SP1 Feature Pack. Copy the files to C:\Media and install them with the following commands:

msiexec /i C:\Media\SharedManagementObjects.msi /qb
msiexec /i C:\Media\PowerShellTools.msi /qb

Ports and Firewall

Perform this step on both SQL Servers.

Use the following code to configure SQL Server to listen on port 1433 (instead of using dynamic ports):

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.SQLWMIManagement") | Out-Null

$c = New-Object Microsoft.SQLServer.Management.SMO.WMI.ManagedComputer .
$i = $C.ServerInstances['SQL01']
$p = $I.ServerProtocols['Tcp']
$a = $P.IPAddresses['IPAll']

$a.IPAddressProperties['TcpDynamicPorts'].Value = ''
$a.IPAddressProperties['TcpPort'].Value = '1433'

$p.Alter()

Use the following commands to create the required firewall rules:

netsh advfirewall firewall add rule name="SQL01" dir=in action=allow profile=domain localport="1433,5022" protocol=TCP
netsh advfirewall firewall add rule name="SQL Browser" dir=in action=allow profile=domain localport=1434 protocol=UDP

Enable Clustering

Perform this step on both SQL Servers.

Use the following command to install the Failover Clustering feature:

Install-WindowsFeature Failover-Clustering

Active Directory Security Group

Perform this step once on a server with the Active Directory PowerShell module installed.

We will now create an Active Directory security group to hold the computer accounts for both SQL nodes and the cluster. Note: We can't add the cluster account into the group yet, as it doesn't currently exist.

To create a global security group named sql-nodes-sheryl with EDWARD and RONALD as members, run the following command:

New-ADGroup -Name "sql-nodes-sheryl" -GroupScope Global -Path "OU=Groups, DC=lab, DC=hinchley, DC=net" -PassThru | Add-ADGroupMember -Members "EDWARD$", "RONALD$"

Reboot both servers to ensure they pick up the change of group membership.

Active Directory Service Accounts

Perform this step once on a server with the Active Directory PowerShell module installed.

The next step is to create two service accounts in Active Directory. The first, which will be named sql-sheryl, will be used for the SQL Server service, and the second, named sql-sheryl-a, will be used for the SQL Agent service. I will specifically create the latter as a Group Managed Service Account (gMSA); unfortunately, this type of account isn't supported for the database engine when using SQL Server 2012. We could use a non-group MSA, but this would require the creation of two accounts for the SQL Server service - one for each node (an MSA can only be used on a single system) - thereby breaking Kerberos authentication.

Use the following command to create the "standard" service account to be used by the SQL Server service:

New-ADUser -Name "sql-sheryl" -SamAccountName "sql-sheryl" -DisplayName "sql-sheryl" -UserPrincipalName "sql-sheryl@lab.hinchley.net" -Path "ou=Service Accounts,dc=lab,dc=hinchley,dc=net" -AccountPassword (Read-Host "Password" -AsSecureString) -ChangePasswordAtLogon $false -Enabled $true

If you haven't previously created a gMSA in your environment, you will need to create the Key Distribution Services KDS Root Key prior to creating the second service account. This can be done by running the following command on a domain controller:

Add-KdsRootKey –EffectiveImmediately

Unfortunately, the EffectiveImmediately parameter really means: "wait up to 10 hours". In the context of a lab, this can be short circuited as follows:

Add-KdsRootKey –EffectiveTime ((Get-Date).AddHours(-10))

Now we can create the service account. Note: Access to the account is restricted to the members of the sql-nodes-sheryl group we previously created.

New-ADServiceAccount –Name "sql-sheryl-a" -DNSHostName "sheryl.lab.hinchley.net" -PrincipalsAllowedToRetrieveManagedPassword "sql-nodes-sheryl"

The next step is to configure the two SQL servers to use the managed service account. Unfortunately, this requires the Active Directory PowerShell module to be installed on each node:

Add-WindowsFeature RSAT-AD-PowerShell

Now use the following command to install the service account on each node:

Install-AdServiceAccount "sql-sheryl-a"

If the gMSA is working as expected, the following command should return True:

Test-AdServiceAccount "sql-sheryl-a"

File Share Witness

Perform this step once on the server that will host the file share witness. Note: The witness should not be accessed using DFS-N.

To create the file share witness, and configure the appropriate permissions, run the following commands:

# Create C:\Shares\Witness and share as "Witness".
New-Item -Type Directory "C:\Shares\Witness" -Force
New-SmbShare –Name Witness –Path "C:\Shares\Witness" -FullAccess Everyone

function New-Rule($account, $permission) {
  return New-Object System.Security.AccessControl.FileSystemAccessRule($account, $permission, "ContainerInherit, ObjectInherit", "None", "Allow")
}

# Grant Administrators and System "Full Control" to C:\Shares.
$acl = Get-Acl "C:\Shares"
$acl.SetAccessRuleProtection($True, $False)
$acl.AddAccessRule((New-Rule "system" "FullControl"))
$acl.AddAccessRule((New-Rule "administrators" "FullControl"))
Set-Acl "C:\Shares" $acl

# Grant the "LAB\sql-nodes-sheryl" group "Full Control" to C:\Shares\Witness\SHERYL.
$folder = "C:\Shares\Witness\SHERYL"
New-Item -Type Directory $folder -Force
$acl = Get-Acl $folder
$acl.AddAccessRule((New-Rule "lab\sql-nodes-sheryl" "FullControl"))
Set-Acl $folder $acl

Failover Cluster Configuration

Perform this step from a server with the cluster management tools (RSAT-Clustering) installed.

Ok, let's kick on with the creation of the Windows Server Failover Cluster by running the following command:

New-Cluster -Name SHERYL -Node @("EDWARD.lab.hinchley.net", "RONALD.lab.hinchley.net") -NoStorage -StaticAddress 10.0.0.22
Get-ADGroup "sql-nodes-sheryl" | Add-ADGroupMember -Members "SHERYL$"
Set-ClusterQuorum -Cluster SHERYL -NodeAndFileShareMajority \\WINSTON.lab.hinchley.net\Witness\SHERYL

Test Database

Perform this step once from the first SQL Server.

To create an AlwaysOn availability group we need a database. To create a database named Test, run the following command:

sqlcmd -S "EDWARD\SQL01" -Q "CREATE DATABASE Test;"

Backup Share

Perform this step once on a file server.

A database must be backed up before it can be added to an AlwaysOn availability group. In this step we will create a UNC share, accessible to both SQL nodes, where we can backup and restore the test database.

I will use the process I described in an earlier blog post to create a highly available share (via DFS-N and DFS-R) for storing the SQL backups. I won't repeat the details, other than to say I created a share named SQL, mapped to C:\Shares\SQL, and referenced as \\lab.hinchley.net\sql. The SQL folder includes a sub-directory named SHERYL that is only accessible to Administrators and System (both inherited), and the explicitly assigned sql-sheryl-a account (with Modify permissions). The SQL share is replicated across two file servers using an existing DFS-R replication group named LAN.

As a quick tip, here is the command I used to set the permissions on C:\Shares\SQL\SHERYL:

icacls C:\Shares\SQL\SHERYL /grant "lab\sql-sheryl":(OI)(CI)M

Service Configuration

Perform this step on each SQL Server.

We will now reconfigure the SQL Server service and the SQL Agent service to run under the context of the service accounts we previously created.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
$c = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer .
$s = $c.Services | ? Name -eq 'MSSQL$SQL01'
$s.SetServiceAccount("lab\sql-sheryl", (Read-Host "Password"))
$s = $c.Services | ? Name -eq 'SQLAgent$SQL01'
$s.SetServiceAccount('lab\sql-sheryl-a$', "")
Restart-Service 'MSSQL$SQL01' -Force
Restart-Service 'SQLAgent$SQL01'

A couple of points of clarification:

Availability Group Listener

Perform this step once on a server with the Active Directory PowerShell module installed.

The WSFC cluster will attempt to create a computer object in Active Directory when instantiating a new availability group listener. This action will fail if the cluster does not have the required privileges. To avoid this issue, we will pre-stage the account, and then grant the cluster Full Control permissions to the object.

New-ADComputer -Name SHERYL-01 -Path "OU=Servers,DC=lab,DC=hinchley,DC=net"
$computer = [ADSI]"LDAP://CN=SHERYL-01,OU=Servers,DC=lab,DC=hinchley,DC=net"
$cluster = Get-ADComputer SHERYL
$sid = [System.Security.Principal.SecurityIdentifier] $cluster.SID
$identity = [System.Security.Principal.IdentityReference] $sid
$rights = [System.DirectoryServices.ActiveDirectoryRights] "GenericAll"
$type = [System.Security.AccessControl.AccessControlType] "Allow"
$inheritance = [System.DirectoryServices.ActiveDirectorySecurityInheritance] "All"
$ace = New-Object System.DirectoryServices.ActiveDirectoryAccessRule $identity, $rights, $type, $inheritance
$computer.psbase.ObjectSecurity.AddAccessRule($ace)
$computer.psbase.CommitChanges()

AlwaysOn Availability Group

Perform the steps within this section on the first SQL node. The commands should be run from a PowerShell prompt with the sqlps module imported (i.e. import-module sqlps).

We will start by backing up and restoring the Test database we previously created.

# Set variables.
$database  = "Test"
$backups   = "\\lab.hinchley.net\sql\SHERYL"
$instance1 = "EDWARD\SQL01"
$instance2 = "RONALD\SQL01"

# Backup the test database on the primary server.
Backup-SqlDatabase `
    -Database $database `
    -BackupFile "$backups\$database.bak" `
    -ServerInstance $instance1
Backup-SqlDatabase `
    -Database $database `
    -BackupFile "$backups\$database.log" `
    -ServerInstance $instance1 `
    -BackupAction Log

# Restore the test database on the secondary server.
Restore-SqlDatabase `
    -Database $database `
    -BackupFile "$backups\$database.bak" `
    -ServerInstance $instance2 `
    -NoRecovery
Restore-SqlDatabase `
    -Database $database `
    -BackupFile "$backups\$database.log" `
    -ServerInstance $instance2 `
    -RestoreAction Log `
    -NoRecovery

The next step is to create the database mirroring endpoints required by an AlwaysOn availability group:

$endpoint = New-SqlHADREndpoint -Port 5022 -Owner sa -Encryption Supported -EncryptionAlgorithm Aes -Name AlwaysOnEndpoint -Path "SQLSERVER:\SQL\$instance1"
Set-SqlHADREndpoint -InputObject $endpoint -State Started
$endpoint = New-SqlHADREndpoint -Port 5022 -Owner sa -Encryption Supported -EncryptionAlgorithm Aes -Name AlwaysOnEndpoint -Path "SQLSERVER:\SQL\$instance2"
Set-SqlHADREndpoint -InputObject $endpoint -State Started

To grant the SQL Server service access to the endpoints:

sqlcmd -S $instance1 -Q "CREATE LOGIN [LAB\sql-sheryl] FROM WINDOWS; GRANT CONNECT ON ENDPOINT::AlwaysOnEndpoint TO [LAB\sql-sheryl]";
sqlcmd -S $instance2 -Q "CREATE LOGIN [LAB\sql-sheryl] FROM WINDOWS; GRANT CONNECT ON ENDPOINT::AlwaysOnEndpoint TO [LAB\sql-sheryl]";

The next step creates an in-memory representation of the replicas:

$replica1 = New-SqlAvailabilityReplica `
  -Name $instance1 `
  -EndpointURL "TCP://EDWARD.lab.hinchley.net:5022" `
  -AvailabilityMode "SynchronousCommit" `
  -FailoverMode "Automatic" `
  -Version 11 `
  -AsTemplate

$replica2 = New-SqlAvailabilityReplica `
  -Name $instance2 `
  -EndpointURL "TCP://RONALD.lab.hinchley.net:5022" `
  -AvailabilityMode "SynchronousCommit" `
  -FailoverMode "Automatic" `
  -Version 11 `
  -AsTemplate

We will now enable the use of AlwaysOn availability groups on both nodes:

Enable-SqlAlwaysOn -Path "SQLSERVER:\SQL\$instance1" -Force
Enable-SqlAlwaysOn -Path "SQLSERVER:\SQL\$instance2" -Force

And then create the availability group:

New-SqlAvailabilityGroup `
  -Name "SHERYL-01" `
  -Path "SQLSERVER:\SQL\$instance1" `
  -AvailabilityReplica @($replica1, $replica2) `
  -Database $database

Next, we join the secondary replica to the availability group:

Join-SqlAvailabilityGroup -Path "SQLSERVER:\SQL\$instance2\AvailabilityGroups" -Name "SHERYL-01"

And then we join the secondary database to the availability group:

Add-SqlAvailabilityDatabase -Path "SQLSERVER:\SQL\$instance2\AvailabilityGroups\SHERYL-01" -Database $database

The final step is to create the availability group listener:

New-SqlAvailabilityGroupListener -Name "SHERYL-01" -StaticIp "10.0.0.23/255.255.255.0" -Path "SQLSERVER:\SQL\$instance1\AvailabilityGroups\SHERYL-01"

Kerberos Authentication

Perform this step once from any server.

To setup the SPNs required for Kerberos authentication to the availability group:

SetSPN -s "MSSQLSvc/SHERYL-01.lab.hinchley.net:SQL01" "LAB\sql-sheryl"
SetSPN -s "MSSQLSvc/SHERYL-01.lab.hinchley.net:1433" "LAB\sql-sheryl"

You should also set SPNs for direct connections to each node. For example:

SetSPN -s "MSSQLSvc/EDWARD.lab.hinchley.net:SQL01" "LAB\sql-sheryl"
SetSPN -s "MSSQLSvc/EDWARD.lab.hinchley.net:1433" "LAB\sql-sheryl"

The following code can be used to test a Kerberos connection to the availability group:

$Server = New-Object Microsoft.SqlServer.Management.Smo.Server("SHERYL-01.lab.hinchley.net")
$Server.ConnectionContext.ApplicationName = "Listener Connection"
$Server.Status

Now run the following and confirm that KERBEROS is displayed:

sqlcmd -S SHERYL-01 -Q "select c.auth_scheme from sys.dm_exec_connections c inner join sys.dm_exec_sessions s on c.session_id = s.session_id where program_name like 'Listener Connection';"

Management Console

Perform this step on a "management server" with the GUI (i.e. not on the SQL nodes).

Assuming you have mounted the SQL installation media, use the following command to install the SQL Server Management Studio:

setup.exe /ACTION="Install" /IACCEPTSQLSERVERLICENSETERMS /Q /INDICATEPROGRESS /FEATURES="Tools"

Next Steps

The next steps include creating a database maintenance plan, configuring delegated management, and testing database failover. But that can wait for another day.