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.
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.
- Right-click virtual machine
- Select Virtual Network Manager > Select Private > Add
3. Add the private network to each of the virtual machines.
- Shutdown each machine
- Select machine in Hyper-V Manager
- Select Settings
- Select Add Hardware and choose Network Adapter and click Add
- 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:
|Machine||Internal Network||Private Network|
|Availability Group* Listener||192.168.1.73||N/A|
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).
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:
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
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.
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
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:
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
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.