Getting HadronCore on SQL Server 2012

Article
10/31/2011

One of the new features of SQL Server 2012 is AlwaysOn Availability Groups. You can think of Availability Groups as Database Mirroring on steroids. Database mirroring is only limited to one database, one secondary and if you wanted to read off it you had to create a snapshot. And in the case of a failover the application has to reconnect to another machine.

AlwaysOn Availability Groups provide much more flexibility for high availability and disaster recovery. With AlwaysOn you can:

Use multiple databases within an availability group. The group becomes the unit of failover and not a single database.
Use up to four secondary's.
Secondary’s can be readable without using a snapshot.
Backups can be taken off secondary’s.
A Virtual Network Name can be used to redirect clients.

Another great feature is the support for Windows server core editions and the growing adaption of PowerShell. In this post I will guide you through a gui-less installation of AlwaysOn availability groups. Let’s get started.

If you want to use availability groups you will need a Windows Failover Cluster. No need to run off. This is not the cluster you are used too. Besides, cluster installations have become much easier with Windows Server 2008 and SQL Server 2008. You can still cluster a SQL instance with SQL Server 2012, in fact it has much improved as well. What would you say about SMB support, local storage support for TempDB, multi-subnet clustering and better diagnostics and failover policy. But let’s not get sidetracked, we are doing availability groups.

To prove that SQL isn’t clustered I will install SQL first on all machines and configure Windows Failover Clustering later. All machines are Windows Server 2008 R2 Sp1 Core editions with all updates installed including .NET 4.0. Every machine has one disk, the OS disk. No shared cluster disks or iSCSI drives here. The machines have one NIC and are all bound to a local Contoso domain. I’m using one domain account for all installations and I also added the account to the SYSADMIN server role afterwards. Installation is easy, pop in the disk or whatever you are using and fire away:
setup.exe /q /ACTION=Install /FEATURES=SQLENGINE /INSTANCENAME=MSSQLSERVER

/SQLSVCACCOUNT=CONTOSO\sqlsvc /SQLSVCPASSWORD=p@ssw0rd /AGTSVCACCOUNT=CONTOSO\sqlsvc /AGTSVCPASSWORD=p@ssw0rd /IACCEPTSQLSERVERLICENSETERMS /INDICATEPROGRESS
/AGTSVCSTARTUPTYPE=Automatic /SQLSYSADMINACCOUNTS=CONTOSO\SqlAdmins
/TCPENABLED=1

This will install the SQL Server engine on the box, and just SQL Server. That’s all I need, but you are not limited to only installing the engine on core. Second thing is to open up the firewall; netsh advfirewall firewall add rule name="SQL Server Default TCP Port" dir=in action=allow protocol=tcp localport=1433 profile=domain. To prepare thing for the Availability Groups I will open port 5022 as well. This is the port I will use for the mirroring endpoint. I’m using this command; netsh advfirewall firewall add rule name="SQL Server HADR Endpoint" dir=in action=allow protocol=tcp localport=5022 profile=domain.

That’s all. I repeated this on all my four machines and checked the connection between all of them. Next step is getting Windows Failover Clustering working.

For all machines we need to enable Failover Clustering. For this we use the trusty DISM command line tool; DISM /online /enable-feature /featurename:FailoverCluster-Core.

Now we got everything setup to get the cluster going. Installing a cluster on core couldn’t be easier. On the shell type: cluster /cluster:cls-sql-01 /create /nodes:"SQL-02 SQL-03 SQL-04 SQL-05" /ipaddr:192.168.200.8/24 and you’re done. Wait for it to finish and you’ve got yourself a nice cluster, a very basic cluster, but it’s enough to get AlwaysOn going. And you can use the cluster.exe to make it as complicated as you like.

The next step is to enable AlwaysOn. Remember that if you destroy a cluster and rebuild it you also have to disable and enable AlwaysOn. Enabling AlwaysOn has to be done with PowerShell or the SQL Configuration Manager. In my case it will be PowerShell because I don’t have a GUI. There are two ways to do this, start SQLPS, the SQL Server PowerShell host or import the SQLPS module in PowerShell. I prefer the second method. So I first start PowerShell, then import SQLPS using Import-Module SqlPS. This will give a warning which you can ignore. To enable AlwaysOn we use one of the new SQL Server cmdlets, Enable-SqlHadrService. This function accepts a parameter to tell which instance you want to enable and optionally you can force it to restart the instance. I’m using Enable-SqlHadrService –serverinstance SQL-02 –force to enable AlwaysOn and force a restart. If you don’t supply –force you will be prompted to restart the instance. This is repeated for all instances.

Now we have to make a sidestep. In CTP3 we have to enable a traceflag to get all the functionality I wanted in my little demonstration. For this I once again restorted to PowerShell and used New-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQLServer\Parameters" -Name "SQLArg3" -Value "-T9532" -PropertyType "String" . Now this it limited to my setup and machine. The general idea is to add an extra value to the startup arguments. If this will work in your environment depends on your configuration. So, it is the registry you are dealing with, if you are not comfortable or don’t know what’s going on in this statement don’t do it.

Now the big moment has arrived. The following long line of PowerShell will setup the availability groups for me, and again for me. Tweak and alter it to satisfy your needs. This script is based on the one provided in Books Online (https://msdn.microsoft.com/en-us/library/gg492181(v=SQL.110).aspx).

  $Primary = "SQL-02" 
 $SecondaryList = "SQL-03","SQL-04","SQL-05" 
 $AgName = "AG_PS_DEMO" 
 $Database = "SAMPLE" 
 $BackupDirectory = "\\DC-01\temp\" 
 $ENDPOINTPORT = 5022 
 pushd
 
 # Backup databases and logs 
 foreach($db in $Database) 
 { 
 Write-Host -ForegroundColor Green "Backup $db (and log) on $Primary" 
 $fullBackupFile = Join-Path $BackupDirectory "$db.bak" 
 $logBackupFile = Join-Path $BackupDirectory "$db.trn" 
 
 Backup-SqlDatabase $db $fullbackupFile -ServerInstance $Primary 
 Backup-SqlDatabase $db $logBackupFile -ServerInstance $Primary -BackupAction "Log" 
 }
 
 New-SqlHadrEndpoint -Path SQLSERVER:\SQL\$Primary\Default -Name HadrEndpoint -Port $ENDPOINTPORT 
 Set-SqlHadrEndpoint -Path SQLSERVER:\SQL\$Primary\Default\Endpoints\HadrEndpoint -State Started 
 New-SqlHadrEndpoint -Path SQLSERVER:\SQL\SQL-03\Default -Name HadrEndpoint -Port $ENDPOINTPORT 
 Set-SqlHadrEndpoint -Path SQLSERVER:\SQL\SQL-03\Default\Endpoints\HadrEndpoint -State Started 
 New-SqlHadrEndpoint -Path SQLSERVER:\SQL\SQL-04\Default -Name HadrEndpoint -Port $ENDPOINTPORT 
 Set-SqlHadrEndpoint -Path SQLSERVER:\SQL\SQL-04\Default\Endpoints\HadrEndpoint -State Started 
 New-SqlHadrEndpoint -Path SQLSERVER:\SQL\SQL-05\Default -Name HadrEndpoint -Port $ENDPOINTPORT 
 Set-SqlHadrEndpoint -Path SQLSERVER:\SQL\SQL-05\Default\Endpoints\HadrEndpoint -State Started
 
 # Create availability replicas as templates 
 $replica1 = New-SqlAvailabilityReplica -Name $Primary -EndpointURL "TCP://SQL-02:$ENDPOINTPORT" -AsTemplate -FailOverMode 'Automatic' -AvailabilityMode 'SynchronousCommit' -Version 11 
 $replica2 = New-SqlAvailabilityReplica -Name $SecondaryList[0] -EndpointURL "TCP://SQL-03:$ENDPOINTPORT" -AsTemplate -FailOverMode 'Automatic' -AvailabilityMode 'SynchronousCommit' -Version 11 
 $replica3 = New-SqlAvailabilityReplica -Name $SecondaryList[1] -EndpointURL "TCP://SQL-04:$ENDPOINTPORT" -AsTemplate -FailOverMode 'Manual' -AvailabilityMode 'AsynchronousCommit' -Version 11 
 $replica4 = New-SqlAvailabilityReplica -Name $SecondaryList[2] -EndpointURL "TCP://SQL-05:$ENDPOINTPORT" -AsTemplate -FailOverMode 'Manual' -AvailabilityMode 'AsynchronousCommit' -ConnectionModeInSecondaryRole 'AllowAllConnections' -Version 11
 
 # Create AG on primary 
 cd SQLSERVER:\SQL\$Primary\default 
 Write-Host -ForegroundColor Green "Create availability group $AgName on $Primary" 
 New-SqlAvailabilityGroup $AgName -AvailabilityReplica ($replica1,$replica2,$replica3,$replica4) -Database $Database
 
 foreach($Secondary in $SecondaryList) 
 { 
 # Join AG on secondary 
 Write-Host -ForegroundColor Green "Join $Secondary to availability group $AgName" 
 cd SQLSERVER:\SQL\$Secondary\default 
 Join-SqlAvailabilityGroup -Name $AgName
 
 # Restore databases and logs 
 foreach($db in $Database) 
 { 
 Write-Host -ForegroundColor Green "Restore $db (and log) on $Secondary" 
 $fullBackupFile = Join-Path $BackupDirectory "$db.bak" 
 $logBackupFile = Join-Path $BackupDirectory "$db.trn"
 
 Restore-SqlDatabase $db $fullbackupFile -ServerInstance $Secondary -NoRecovery 
 Restore-SqlDatabase $db $logBackupFile -ServerInstance $Secondary -RestoreAction "Log" -NoRecovery
 
 trap { Write-Warning "Error while restoring, but continuing." ; continue } 
 }
 
 # Join databases on secondary 
 Write-Host -ForegroundColor Green "Join databases to availability group $AgName" 
 $availabilityGroup = get-item SQLSERVER:\SQL\$Secondary\DEFAULT\AvailabilityGroups\$AgName
 
 foreach($db in $Database) 
 { 
 Add-SqlAvailabilityDatabase -InputObject $availabilityGroup -Database $db 
 } 
 } 
 popd

To finish it off a fancy screenshot of the hard work.

To get things going on Windows Server 2008 Core is not hard. Up to point where you setup the availability groups it’s much faster, easier and repeatable. Setting up the availability groups can be done from a remote machine using the wizard if you like. The script I demonstrated here worked on my setup, you have to change it to get things going on your side.

Getting HadronCore on SQL Server 2012

Additional resources