By: Edwin Sarmiento | Comments (3) | Related: > Availability Groups
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
About the author
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
