Enable SQL Server 2012 AlwaysOn Availability Groups Using Windows PowerShell

By:   |   Comments (3)   |   Related: > Availability Groups


In a previous tip on New SQL Server AlwaysOn Feature - Part 1 configuration, we have seen how we can configure AlwaysOn Availability Groups using SQL Server Configuration Manager. Since AlwaysOn Availability Groups now support up to four sets of corresponding secondary databases, this means we need to enable the feature on as many as four SQL Server instances. Is there a way to automate the process of enabling the feature?


SQL Server 2012 has introduced new PowerShell cmdlets that make managing SQL Server easier using Windows PowerShell. This includes a set of cmdlets specifically for managing AlwaysOn Availability Groups. MSDN provides us with a list of those PowerShell cmdlets for reference. One cmdlet in particular is Enable-SqlAlwaysOn. This cmdlet enables AlwaysOn Availability Groups on a SQL Server 2012 instance that supports the feature. It is equivalent to checking the box on the AlwaysOn High Availability tab in the Properties dialog box for the SQL Server 2012 instance.

AlwaysOn High Availability tab in SQL Server Management Studio

You can invoke the cmdlet by running Windows PowerShell from within SQL Server Management Studio or importing the SQLPS module from within the native Windows PowerShell console (you have to first enable script execution from within your Windows PowerShell console by running the Set-ExecutionPolicy cmdlet as defined in this tip).

Start PowerShell in SQL Server Management Studio

PS C:\> Import-Module "SQLPS" -DisableNameChecking

Import SQL Server PowerShell module

For now, you can use the Get-Help cmdlet to explore how you can use the Enable-SqlAlwaysOn cmdlet to enable the AlwaysOn Availability Group feature since there isn't much documentation about it yet (maybe the TechNet and MSDN documentation will get updated when SQL Server 2012 hits the release to manufacturing status). I have provided a simplified syntax below.

PS C:\> Enable-SqlAlwaysOn -ServerInstance INSTANCENAME -Force

The -ServerInstance parameter is the name of the SQL Server 2012 instance where AlwaysOn Availability Groups is to be enabled. This can be a default instance - where the machine hostname is used for the instance name - or a named instance. The -Force parameter is used to continue the cmdlet execution without any confirmation from the user. This is very helpful when you want to create scripts for automating the process because the cmdlet will restart the SQL Server 2012 service after being enabled, thus prompting the user for confirmation.

An example of running the Enable-SqlAlwaysOn cmdlet on a remote machine named DENALISRV1 with a default instance is shown below.

PS C:\> Enable-SqlAlwaysOn -ServerInstance DENALISRV1 -Force

Enabling AlwaysOn Availability Groups on all Cluster Nodes

The real benefit of using this cmdlet is when you have configured your Windows Failover Cluster to have four nodes. This means that you are maximizing your database availability by providing four sets of secondary databases. Using the cmdlet minimizes the effort of logging in to each of the cluster nodes, opening SQL Server Configuration Manager and opening up the Properties window of the SQL Server 2012 instance just to enable the feature. To do this, we need to use the Failover Clustering cmdlets that are built in to Windows Server 2008 R2. You can do this by importing the FailoverClusters module from within your Windows PowerShell console as defined here.

PS C:\> Import-Module FailoverClusters

We can use the Get-ClusterNode cmdlet to list all of the nodes in our Windows Failover Cluster. In my cluster environment, I have three nodes - DENALISRV1, DENALISRV2 and DENALISRV3.

PS C:\> Get-ClusterNode

Get-ClusterNode PowerShell cmdlet

Combining this with the Enable-SqlAlwaysOn cmdlet, we can pass the results of the Get-ClusterNode cmdlet to enable AlwaysOn Availability Groups on all the default SQL Server 2012 instances running on all of the nodes in my Windows Failover Cluster - all within a single line of PowerShell code.

PS C:\> foreach ($node in Get-ClusterNode) {Enable-SqlAlwaysOn -ServerInstance $node -Force}

enable AlwaysOn Availability Groups on all the default SQL Server 2012 instances in my Windows Failover Cluster
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Saturday, January 12, 2019 - 12:10:05 PM - bass_player Back To Top (78747)


Always On Availability Groups require a Windows Server operating system, not a client operating system like Windows 8 or Windows 10.

How much memory resources do you have on your box? I can create a virtual lab environment with an Active Directory domain controller and 2 Windows Server machines for the Windows Server Failover Cluster and Always On Availability Groups in a physical machine with 4GB of RAM. Not ideal but it works.

Sunday, December 30, 2018 - 10:00:50 PM - Bruce Wexler Back To Top (78582)

 Hi Edwin,

Enjoyed this and other highly informative and useful articles. Quick question, is there any way possible to run AlwaysOn Availability oin an installation of Windows 8.1 or Windows 10? I am using these version in place of Windows 2008 Server in the meantime. I don't have the option of running a virtual machine due to limited resources on this box. 

Monday, December 25, 2017 - 2:40:14 PM - Eric C. Singer Back To Top (74450)


Hi Edwin,

Just wanted to say thank you for this.  Was a huge help in automating the final steps of my SQL provisioning process.  I actually took your steps and used them to help me setup a 2017 cluster with seeding instead.  I published that here http://www.ericcsinger.com/powershell-scripting-installing-sql-setting-up-alwayson-availability-groups/ along with other things I do to setup a SQL server / cluster.

Anway, as always, thanks so much for taking the time to put this together.

get free sql tips
agree to terms