Enable SQL Server 2012 AlwaysOn Availability Groups Using Windows PowerShell

By: Edwin Sarmiento | Read Comments | Print Edwin works as a SQL Server DBA for The Pythian Group in Ottawa and is a SQL Server MVP. Related Tips: More

Problem

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?

Solution

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.

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).

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

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

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}

Next Steps

• Review this tip on New SQL Server AlwaysOn Feature - Part 1 configuration
• Explore all the Windows PowerShell cmdlets available for managing AlwaysOn Availability Groups in this MSDN article

Share:

Share

Tweet

Free SQL Server Learning Introduction to SQL Azure Join Herve Roggero on Wednesday, June 13 @ 3:00 PM EST to learn more.

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code