Although you can use SQL Server Management Studio or T-SQL to manage AlwaysOn, SQL Server Denali CTP 3 includes 25 cmdlet which together provide complete coverage for creating, confiiguring and administering the AlwaysOn database feature. In this post we’ll look at using Powershell to perform various management tasks for AlwayOn.

Note: This blog post describes features in SQL Server Denali CTP 3 which may change on final product release.

Getting Started

You’ll need  a simple Windows 2008 R2 cluster with two standalone installs of SQL Server. I say simple because you don’t have to worry about shared storage, quorum disks and shared MSTDTC installations like you would in a traditional SQL Server installation on a Windows Server Failover Cluster. All you need are two servers running Windows Server 2008 R2 Enterprise Edition. For test purposes I’ve setup a two-node Windows Server Failover Cluster as follows:

1. Configure a Private virtual machine network for intra-cluster communication. Note this step is optional and not really necessary for a bare minimum cluster, but I setup it up anyways to mimic close to what I’ll have in production. This network uses a separate IP subnet than the Internal only network I had already setup in Hyper-V.

2. Setup the private only network  which allows communication between virtual machines only.

1. Right-click virtual machine
2. Select Virtual Network Manager > Select Private > Add

3. Add the private network to each of the virtual machines.

1. Shutdown each machine
2. Select machine in Hyper-V Manager
3. Select Settings
4. Select Add Hardware and choose Network Adapter and click Add
5. Select the private network you created from the Network drop down list and click OK

4. On the virtual machines assign IP addresses under Network and Sharing Center. Here’s a table of my setup:

DC1= Domain Controller

Cluster1 = cluster management IP (assigned during cluster configuration)

Availability Group Listener (assigned during AlwaysOn  Availability Group Listener configuration)

*Don’t worry about these for now.

5. Since we’re using a two-node cluster without a quorum disk it is suggested to use a Node and File Share Majority so I’ll setup network share which is read/write accessible by the Cluster Service account. For my testing purposes I created share on my DC1 machine called \\DC1\Share1 located on DC1 C:\Share1 folder.

Setting Up Windows Failover Clustering

1.  Add the Failover Cluster Manage feature to both modes by running the following  PowerShell commands

import-module ServerManager
Add-WindowsFeature -Name Failover-Clustering

2. Create the cluster by running the following PowerShell commands on one node:

import-module FailoverClusters
new-cluster clusterxm -Node node1,node2 -StaticAddress 192.168.1.70 -NoStorage

3. Set the quorum mode to Node and File Share Majority by running the following command on one node:

 Set-ClusterQuorum -NodeAndFileShareMajority \\DC1\share1">\\DC1\share1

Install SQL Server on Both Nodes

Install SQL Server and this important – As a standalone instance. Sorry no Powershell commands here just run through the installation screens. Be sure to set the SQL Server service account to a domain account (I had issues when using Local System).

Database Prerequisites

You need to have a database which is not already part of an AlwaysOn Availability Group in FULL recovery mode and has been backed up. As a test I’ll just use the old school pubs sample database. Run the instpubs.sql file and create a backup using Powershell.

Start SQL Server Management Studio and select “Start PowerShell” from Object Explorer. Run the following command to backup the database to the default backup directory:

PS SQLSERVER:\SQL\NODE1\DEFAULT\Databases\pubs> Backup-SqlDatabase -Database pubs

You’ll need to create a share accessible by both nodes for storing the SQL Server database and transaction log initialization backups. For my example I’ll create a folder called sqlrec under Node1’s C drive C:\sqlrec and share named sqlrec \\node1\sqlrec

AlwaysOn Powershell Documentation

The CTP3 version of Books Online contains some documentation and scripts for configuring AlwaysOn however as to be expected with pre-release software some topics are not covered and there are documentation errors in other sections. As I’ve encountered documentation errors I  submitted Connect Items  (see Connect Items below for details).  Relevant helps topics included in CTP3 are listed below:

Specify the Endpoint URL When Adding or Modifying an Availability Replica

Enable and Disable AlwaysOn

Create and Configure an Availability Group

Rather than use the scripts includes with Books Online which only handle part of the configuration or write my own script I think its more important to demonstrate the commands to create and configure AlwaysOn. By approaching Powershell as simply running command versus vs. writing a script you’ll learn how to use Powershell commands for new administration functions. Once you’re happy with the results of the commands you then can turn the series of commands into a script. Let’s get started…

Using Powershell to Create and Configure AlwaysOn

Note: The following examples work within the SQLServer provider while connected to specific SQL Server machines. In my example I’m using machines named node1 and node2  running a default instance. Pay particular attention to the context in which the commands are run on either Node1 (primary) or Node2 (secondary).

Enable HADRService on both nodes:

PS SQLSERVER:\SQL\NODE1\DEFAULT> Enable-SqlHADRService -ServerInstance NODE1 -force
PS SQLSERVER:\SQL\NODE1\DEFAULT> Enable-SqlHADRService -ServerInstance NODE2 -force

Note: The force switch. Enabling or disabling HADR requires SQL Server service to be restarted. If you omit the force switch you’ll be prompted to confirm SQL Server restart.

Optionally confirm HADRService enabled on both nodes:

PS SQLSERVER:\SQL\NODE1\DEFAULT> get-item . | select IsHadrEnabled
 
IsHadrEnabled
-------------
True
 
PS SQLSERVER:\SQL\NODE1\DEFAULT> pushd
PS SQLSERVER:\SQL\NODE1\DEFAULT> cd SQLServer:\SQL\NODE2\DEFAULT
PS SQLSERVER:\SQL\NODE2\DEFAULT> get-item . | select IsHadrEnabled
 
IsHadrEnabled
-------------
True
 
PS SQLSERVER:\SQL\NODE2\DEFAULT> popd

Note: In order to retrieve the IsHadrEnabled property I need to to cd to node2  and in order to easily change directories back I’m using the pushd and popd commands to store the current location (pushd) and switch back (popd).

Configure HADR Endpoints

Configure HADR Endpoints and set state to started:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

PS SQLSERVER:\SQL\NODE1\DEFAULT> cd .\Endpoints
PS SQLSERVER:\SQL\NODE1\DEFAULT\Endpoints> New-SqlHADREndpoint -Name "hadr_endpoint" -Port 5022
 
Name
----
hadr_endpoint
 
PS SQLSERVER:\SQL\NODE1\DEFAULT\Endpoints> dir | Set-SqlHADREndpoint -State "Started"
 
Name
----
hadr_endpoint
 
PS SQLSERVER:\SQL\NODE1\DEFAULT\Endpoints> cd SQLServer:\SQL\NODE2\DEFAULT\Endpoints
PS SQLSERVER:\SQL\NODE2\DEFAULT\Endpoints> New-SqlHADREndpoint -Name "hadr_endpoint" -Port 5022
 
Name
----
hadr_endpoint
 
PS SQLSERVER:\SQL\NODE2\DEFAULT\Endpoints> dir | Set-SqlHADREndpoint -State "Started"
 
Name
----
hadr_endpoint

Backup Database and Transaction Log

1
2
3

PS SQLSERVER:\SQL\NODE2\DEFAULT\Endpoints> cd SQLServer:\SQL\NODE1\DEFAULT
PS SQLSERVER:\SQL\NODE1\DEFAULT> Backup-SqlDatabase pubs \\NODE1\sqlrec\pubs.bak
PS SQLSERVER:\SQL\NODE1\DEFAULT> Backup-SqlDatabase pubs \\NODE1\sqlrec\pubs.trn -BackupAction Log

Create Replicas

Note: This doesn’t actually create the replicate, rather the –AsTemplate parameter allows you to create a definition of the replica which is stored in the $replica1 and $replica2 variables. These variables will be used when creating the availability group next.

1
2

PS SQLSERVER:\SQL\NODE1\DEFAULT> $replica1 = New-SqlAvailabilityReplica -Name NODE1 -EndpointURL "TCP://NODE1:5022" -AsTemplate -AvailabilityMode SynchronousCommit -FailoverMode Automatic  -ConnectionModeInSecondaryRole AllowAllConnections
PS SQLSERVER:\SQL\NODE1\DEFAULT> $replica2 = New-SqlAvailabilityReplica -Name NODE2 -EndpointURL "TCP://NODE2:5022" -AsTemplate -AvailabilityMode SynchronousCommit -FailoverMode Automatic -ConnectionModeInSecondaryRole AllowAllConnections

Create Availability Group

1
2
3
4
5

PS SQLSERVER:\SQL\NODE1\DEFAULT> New-SqlAvailabilityGroup AVGPubs -AvailabilityReplica ($replica1,$replica2) -Database pubs
 
Name                 PrimaryReplicaServerName
----                 ------------------------
AVGPubs              NODE1

Join Availability Group on Secondary Node

1
2
3
4

PS SQLSERVER:\SQL\NODE1\DEFAULT> pushd
PS SQLSERVER:\SQL\NODE1\DEFAULT> cd SQLServer:\SQL\NODE2\DEFAULT
PS SQLSERVER:\SQL\NODE2\DEFAULT> Join-SqlAvailabilityGroup -Name AVGPubs
PS SQLSERVER:\SQL\NODE2\DEFAULT> popd

Optionally Verify Availability Groups

1
2
3
4
5
6
7

PS SQLSERVER:\SQL\NODE1\DEFAULT> cd .\AvailabilityGroups
PS SQLSERVER:\SQL\NODE1\DEFAULT\AvailabilityGroups> dir | select -ExpandProperty AvailabilityReplicas | select name, ConnectionModeInPrimaryRole, ConnectionModeInSecondaryRole
 
Name                                                ConnectionModeInPrimaryRole           ConnectionModeInSecondaryRole
----                                                ---------------------------           -----------------------------
NODE1                                                       AllowAllConnections                     AllowAllConnections
NODE2                                                       AllowAllConnections                     AllowAllConnections

Restore Database and Transaction Log On Secondary Node

1
2
3

PS SQLSERVER:\SQL\NODE1\DEFAULT\AvailabilityGroups> cd SQLServer:\SQL\NODE2\DEFAULT
PS SQLSERVER:\SQL\NODE2\DEFAULT> Restore-SqlDatabase pubs \\NODE1\sqlrec\pubs.bak  -NoRecovery
PS SQLSERVER:\SQL\NODE2\DEFAULT> Restore-SqlDatabase pubs \\NODE1\sqlrec\pubs.trn  -RestoreAction "Log" -NoRecovery

Add Database to Availability Group on Secondary Node

1
2

PS SQLSERVER:\SQL\NODE2\DEFAULT> cd .\AvailabilityGroups
PS SQLSERVER:\SQL\NODE2\DEFAULT\AvailabilityGroups> dir | Add-SqlAvailabilityDatabase -Database pubs

Create Availability Group Listener

Note: This is what you connect to (or a least that’s my impression) from your client machines. The listener provides a network name and IP Address which will failover between nodes.

1
2
3
4
5

cd SQLSERVER:\
PS SQLSERVER:\> New-SqlAvailabilityGroupListener -Name Network1 -StaticIp 192.168.1.73/255.255.255.0 -path SQLSERVER:\SQL\NODE1\DEFAULT\AvailabilityGroups\AVGPubs
Name                 PortNumber      ClusterIPConfiguration
----                 ----------      ----------------------
Network1             1433            ('IP Address: 192.168.1.73')

Optionally Verify Listener Connectivity

1
2
3
4
5
6
7

PS SQLSERVER:\SQL\NODE1\DEFAULT>cd SQLSERVER:\SQL\NODE1\DEFAULT
PS SQLSERVER:\SQL\NODE1\DEFAULT> Invoke-Sqlcmd -ServerInstance Network1 -Database master -Query "select @@servername"
WARNING: Using provider context. Server = NODE1.
 
Column1
-------
NODE1

Determine AlwaysOn Health

SQL Server includes three cmdlets for verifying the health of the various AlwaysOn components:

1
2
3
4
5
6
7

PS SQLSERVER:\SQL\NODE1\DEFAULT> get-command -module sqlps -Name test-*
 
CommandType     Name                                                Definition
-----------     ----                                                ----------
Cmdlet          Test-SqlAvailabilityGroup                           Test-SqlAvailabilityGroup [[-Path] <string   []>] [...
Cmdlet          Test-SqlAvailabilityReplica                         Test-SqlAvailabilityReplica [[-Path] <string   []>]...
Cmdlet          Test-SqlDatabaseReplicaState                        Test-SqlDatabaseReplicaState [[-Path] <string   []>...

The easiest way to run the test cmdlets is within the SQL Server provider context as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

PS SQLSERVER:\SQL\NODE1\DEFAULT> cd .\AvailabilityGroups
PS SQLSERVER:\SQL\NODE1\DEFAULT\AvailabilityGroups> dir | Test-SqlAvailabilityGroup
 
HealthState            Name
-----------            ----
Healthy                AVGPubs
 
PS SQLSERVER:\SQL\NODE1\DEFAULT\AvailabilityGroups> dir .\AVGPubs\AvailabilityReplicas | Test-SqlAvailabilityReplica
 
HealthState            AvailabilityGroup    Name
-----------            -----------------    ----
Healthy                AVGPubs              NODE1
Healthy                AVGPubs              NODE2
 
PS SQLSERVER:\SQL\NODE1\DEFAULT\AvailabilityGroups> dir .\AVGPubs\DatabaseReplicaStates | Test-SqlDatabaseReplicaState
 
HealthState            AvailabilityGroup    AvailabilityReplica  Name
-----------            -----------------    -------------------  ----
Healthy                AVGPubs              NODE1                pubs
Healthy                AVGPubs              NODE2                pubs

Manually Failing Over an Availability Group

To manually fail over an Availability Group we use the Switch-SqlAvailabilityGroup cmdlet. Interestingly enough I could not figure out a way to fail over the availability resource using the GUI in CTP3, so Powershell is the only way to I could do this for now which is fine by me

Note: Be aware the context in which you run the Switch-SqlAvailabilityGroup cmdlet. The cmdlet should be run from whichever node is functioning as the secondary as we will see in a moment:

1
2

PS SQLSERVER:\SQL\NODE1\DEFAULT\AvailabilityGroups>cd AVGPubs
PS SQLSERVER:\SQL\NODE1\DEFAULT\AvailabilityGroups\AVGPubs> Switch-SqlAvailabilityGroup

Running the switch-sqlavailabilityGroup command on the prmiary produces the following error:

1
2
3
4
5
6
7
8
9
10

Switch-SqlAvailabilityGroup : The local availability replica of availability group 'AVGPubs' cannot accept signal 'FAIL
OVER_PENDING' in its current role 'PRIMARY_NORMAL' and state (configuration is in Windows Server Failover Clustering st
ore, local availability replica has joined).  The availability replica signal is invalid given the current replica role
.  Verify that the signal is permitted based on the current role of the local availability replica, and retry the opera
tion.
At line:1 char:28
+ Switch-SqlAvailabilityGroup <<<<
    + CategoryInfo          : InvalidOperation: (:) [Switch-SqlAvailabilityGroup], SqlException
    + FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.Hadr.FailoverSqlAvailabilityGr
   oupCommand

At first I thought I had configured something incorrectly, but then was able to to failover the availability group through Failover Cluster Manager. It was then I realized this needs to be run from the context of the secondary node.

1
2
3
4
5

PS SQLSERVER:\SQL\NODE1\DEFAULT\AvailabilityGroups\AVGPubs> pushd
PS SQLSERVER:\SQL\NODE1\DEFAULT\AvailabilityGroups\AVGPubs> cd SQLSERVER:\SQL\NODE2\DEFAULT\AvailabilityGroups\AVGPubs
PS SQLSERVER:\SQL\NODE2\DEFAULT\AvailabilityGroups\AVGPubs> Switch-SqlAvailabilityGroup
PS SQLSERVER:\SQL\NODE2\DEFAULT\AvailabilityGroups\AVGPubs> popd
PS SQLSERVER:\SQL\NODE1\DEFAULT\AvailabilityGroups\AVGPubs> Switch-SqlAvailabilityGroup

Pausing and Resuming an Availability Group

You can pause and then resume synchronization of the Always database using the suspend-SqlAvailabilityDatabase and Resume-SqlAvailabilityDatabase cmdlets:

1
2
3

PS SQLSERVER:\SQL\NODE1\DEFAULT\AvailabilityGroups\AVGPubs> cd .\AvailabilityDatabases
PS SQLSERVER:\SQL\NODE1\DEFAULT\AvailabilityGroups\AVGPubs\AvailabilityDatabases> dir | Suspend-SqlAvailabilityDatabase
PS SQLSERVER:\SQL\NODE1\DEFAULT\AvailabilityGroups\AVGPubs\AvailabilityDatabases> dir | Resume-SqlAvailabilityDatabase

Summary

This post has demonstrated the Powershell cmdlets available to help you manage your AlwaysOn configuration. The commands could be used to build a reusable script to provide a consistent configuration of AlwaysOn.

My testing was completed on SQL Server CTP3 as I encountered documentation issues I logged Connect Items. I’ve included a list of Connect Items below. There are other issues with the documentation which I did not log because of missing documentation rather than documentation bugs. I think not having all the documentation complete is to be expected while a product is still in CTP.

I’ll save my commentary on the AlwaysOn cmdlets for a future post, but for now I will say I’m impressed with the coverage and ease of use provided by the AlwaysOn cmdlets and SQL Server provider.

Connect Items

Determine Whether AlwaysOn Availability Groups is Enabled

Enable and Disable AlwaysOn (SQL Server) Documentation

Create and Configure an Availability Group (SQL Server PowerShell) Doc Error

New-SqlAvailabilityReplica cmdlt Allows Incompatible settings

Set-SqlAvailabilityReplica Cmdlet Does Not Take Pipeline input

New-SqlAvailabilityGroupListener Help Example Incorrect

Creating a Network Name for AlwaysOn Availability Groups Obsolete