Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Enable SQL Server 2012 AlwaysOn Availability Groups Using Windows PowerShell


By:   |   Read Comments   |   Related Tips: More > Availability Groups


SQL Server Conference Giveaway - click to learn 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.

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


Last Update:


signup button

next tip button



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.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools