Creating a SQL Server Availability Group via PowerShell

This is tangibly related to SharePoint and this took me awhile to figure out the correct syntax. I’ve been building a SQL AG on Server 2012 with the Core installation option. To accomplish this, you will need a workstation with SQL Management Studio installed (which also installed the SQL PowerShell module). Some background on this setup:

2 servers with Windows Server 2012 and SQL Server 2012 SP1 CU5

3 network adapters per server

Features installed (note that “D:” has the Windows Server ISO loaded) on the servers:

Install-WindowsFeature Failover-Clustering,NET-Framework-Core -Source:D:\sources\sxs

IP all of the adapters appropriately. Next, rename the secondary and tertiary network adapters for clarity:

Rename-NetAdapter "Ethernet 2" -NewName "Data"
Rename-NetAdapter "Ethernet 3" -newName "Heartbeat"

Note that you may want to disable or modify the Firewall Policy for the “Domain” and “Private” profiles. You can do this via the Set-NetFirewallProfile cmdlet.

On the client workstation with the RSAT tools installed (Failover Cluster), run the following to import the Failover Cluster module, create the cluster with a static IP address, then rename the network adapters to fit the adapter name on the server. Finally, set the Ethernet adapter (this is the adapter that is used for standard client to server communication) Host Record TTL to 300 seconds.

Import-Module FailoverClusters
New-Cluster -Name "SQLCLUSTER" -Node "SQLSERVER1","SQLSERVER2" -StaticAddress ""
$n1 = Get-ClusterNetwork -Cluster "SQLCLUSTER" -Name "Cluster Network 1"
$n1.Name = "Heartbeat"
$n2 = Get-ClusterNetwork -Cluster "SQLCLUSTER" -Name "Cluster Network 2"
$n2.Name = "Data"
$n3 = Get-ClusterNetwork -Cluster "SQLCLUSTER" -Name "Cluster Network 3"
$n3.Name = "Ethernet"

Get-ClusterResource "Ethernet" | Set-ClusterParameter -ClusterParameter HostRecordTTL 300

Next, on each server, install SQL via batch script. Real quick hint here, make sure to exit the PowerShell prompt prior to running this. The SQL installation media needs .NET 3.5, but because we’re running Server 2012 and PowerShell 3.0, we’re using .NET 4. This will cause the installation to fail. The following will install SQL Server 2012 with the Database Engine, SQL Agent, Replication, Integration Services, and Client Connectivity with the specified username and password to the E: drive while the CD/ISO is present in the D: drive. Do not forget to edit the /PID value. It will also enable TCP/IP connectivity to the instance.

set Domain=DomainName
set SQLUser=ServiceAccount
set SQLPass=Password
set SQLRoot=E:
set CDRoot=D:

msiexec.exe /i %CDROOT%\1033_ENU_LP\x64\Setup\sqlsupport_msi\SqlSupport.msi /passive /norestart


Once complete, download SQL Server 2012 SP1 and I’ve chosen to also install CU5. Once downloaded, extract the executable using the following command, making sure to extract each package to a unique path:

<executable file name>.exe /extract:C:\sp1

Next, for each package, run:

setup.exe /qs /IAcceptSQLServerLicenseTerms /Action=Patch /AllInstances

This will install the package applying the patch to all instances on the server. You may or may not need to reboot in between patches.

Next, move to the workstation. I have a workstation running Windows 8 x64 with SQL Management Studio 2012 SP1. Open Management Studio and go to View -> Registered Servers. Add the two instances under Local Server Groups.

Next, run PowerShell as Administrator on the Workstation. Run sqlps or Import-Module sqlps to import the SQL PowerShell module. Validate the previous server registration by executing cd SQLSERVER:\SQLRegistration\"Database Engine Server Group" , and running ls. Both servers should appear here. The next step is to connect to each server, which can be done by executing cd SQLSERVER:\SQL\SERVERNAME.

For each SQL Server, the next step is to enable AlwaysOn and create the HADR Endpoint. Both cmdlets have a few options, so review them prior to execution. Note that when enabling AlwaysOn, the Database Engine service must be restarted, which the -Force switch does (or should do, it didn’t work in my case).

Enable-SqlAlwaysOn -Force
New-SqlHADREndPoint -Name "SPHADR" -Encryption 'Required'

Next, create the Availability Replicas (in memory), create the Availability Group with the Primary server specified, and finally Join the secondary server to the Availability Group. Again, these cmdlets have a lot of options, so it is best to review them so the setup fits your environment.

$ps = New-Object Microsoft.SQLServer.Management.SMO.Server("SQLSERVER1")
$ss = New-Object Microsoft.SQLServer.Management.SMO.Server("SQLSERVER2")

$pr = New-SqlAvailabilityReplica -Name "SQLSERVER1" -EndpointUrl "TCP://" -FailoverMode "Automatic" -AvailabilityMode "SynchronousCommit" -BackupPriority 50 -ConnectionModeInSecondaryRole AllowAllConnections -ReadonlyRoutingConnectionUrl TCP://  -Version ($ps.Version) -AsTemplate

$sr = New-SqlAvailabilityReplica -Name "SQLSERVER2" -EndpointUrl "TCP://" -FailoverMode "Automatic" -AvailabilityMode "SynchronousCommit" -BackupPriority 0 -ConnectionModeInSecondaryRole AllowAllConnections -ReadonlyRoutingConnectionUrl TCP:// -Version ($ss.Version) -AsTemplate

New-SqlAvailabilityGroup -InputObject $ps -Name SharePointAG -AvailabilityReplica ($pr, $sr)
Join-SqlAvailabilityGroup -InputObject $ss -Name SharePointAG

After this, via Management Studio, you should now be able to review the Availability Group status. In my case, I had a critical error which was due to my HADR endpoint being in a stopped state, preventing the secondary replica from connecting to the primary. To resolve this, I ran the following T-SQL:


Once this completed, the secondary replica joined automatically. Note it is normal to have warnings as there are no synchronized databases at this point.

The final step to create the Availability Group is to create the Listener, which can be done with the following cmdlet:

New-SqlAvailabilityGroupListener -Name SPAGL -StaticIp '' -Path SQLSERVER:\SQL\SQLSERVER1\DEFAULT\AvailabilityGroups\SharePointAG

And now you have two complete SQL Servers, ready to have SharePoint databases added to the Aavailabity Group! Make sure to test failover to validate functionality. Do not forget the following resources with regards to supportability of SharePoint databases on an Availability Group.

Trevor Seward is a Microsoft Office Apps and Services MVP who specializes in SharePoint Server administration, hybrid scenarios, and SharePoint Online. He has been working with SharePoint for 16 years from SharePoint 2003 on up, managing environments with terabytes of content for 150,000+ user organizations. Trevor is an author of Deploying SharePoint 2016 and Deploying SharePoint 2019. You can find him on Twitter and in /r/sharepoint.