IT Pro Camps are focused on serving the needs of  IT Pro’s (Windows system administrators). IT Pro’s haven’t had many events like our developer counterpart’s code camps and this is why I’m excited to be a part of IT Pro Camp. There’s a definite need for events which serve the IT Pro community. We’ve done Tampa, South Florida, Orlando and in two weeks we’ll add Sarasota to our growing list of cities. We’re also planning on Jacksonville, Pensacola and for the second year we’ll be returning to Tampa and South Florida. We hope to have dates for the remainder of 2012 finalized by the end of March. I’ll be sure to post an update once we work out scheduling and venues.

The Sarasota IT Pro Camp will feature 24 one-hour sessions on topics covering Powershell, BI, SQL Server, Cloud, Active Directory, System Center and Data Security. I’ll be presenting on Storing Powershell Output. Although there are many good sessions to choose from, I’d like to highlight a few sessions which peaked my interest:

We”ll have three sessions, on a topic we haven’t had at previous camps, Data Security.

Adam Malone – Cyber Crime and the FBI

Joseph Schorr – Rule 1: Cardio

Jeff Wolach - Introduction to Next Generation Firewalls

I think this this is one of the interesting things about having IT Pro Camps in different cities, there tends to be a strong technical community around particular disciplines and for Sarasota I’d say its Data Security.

Jose Chinchilla  (blog|twitter) is doing a two-part session on Business Intelligence.

Jose Chinchilla - Introduction to Microsoft Business Intelligence

Jose Chinchilla - Taking Business Intelligence to the next level with SharePoint 2010!

This is a good opportunity for attendees to get both an introduction and more advanced overview on business intelligence. Two-part series are kind of unique to community events and you tend not to see a two-part series at paid conferences.

Be sure to register to attend the free Sarasota IT Pro Camp event. A continental breakfast and lunch will be provided. Please tell your colleagues about IT Pro Camps. I look forward to seeing you there!

–Chad Miller

• Intro to SQL Server for IT Professionals by Michael Wells (Blog|Twitter)—An excellent overview of SQL Server for the system admin SQL noob.
• Version Control for IT Professionals by Jason Hofferle (Blog|Twitter) – A good overview of using Mercurial source control for sys admin scripts.
• Introduction to PowerShell Remoting also by Jason Hofferle (Blog|Twitter)  —An eye opening look at the benefits of using Powershell remoting in a large enterprise based on real-world benchmarks.

If you can’t make Orlando, the Sarasota IT Pro Camp will be held on Saturday, February 18th. 2012. See the IT Pro Camp site for more information.

• T-SQL BULK INSERT command
• LogParser command-line
• LogParser COM-based scripting
• A Windows Powershell-based approach using several functions

Task #1 Remove a SQL Server Login from the Sysadmin Role

Let’s say you’re given the task to remove a login from the sysadmin role on 50 servers.  For this task we can use the built-in system stored procedures sp_dropsrvrolemember  and sp_helpsrvrolemember.

Since we want to do this across multiple servers, we’ll use a multiserver query from the registered central management:

1. First create a before removal “backup” using sp_helpsrvrolemember:

sp_helpsrvrolemember 'sysadmin'

2. Save the output
3. Next run

EXEC sp_dropsrvrolemember 'CONTOSO\SQL_SecurityAdmin', 'sysadmin'

Error handling is generally good thing, but there are cases were can do a task without much error handling especially if the task is interactive and you have backups. Although you can wrap some T-SQL code to check if the login exists on the server or if the login is member of the server before attempting to removing from the sysadmin, its not necessary any errors can safely be ignored. A Central Management Server is great at one off commands with good T-SQL coverage

Task #2 Remove Linked Server Login Mappings

After removing the login from the sysadmin you discover they are also mapped to sa on over 100  Linked Servers. Since you can’t  remove the login from the server because they still need non-administration access, your task to to remove the linked server login mapping. Changing linked server security  in mass is something where Powershell provides a good solution. You’ll probably want to backup the linked server by scripting out the linked server before any changes which is difficult to do in T-SQL, but easy with Powershell. Also removing a login mappings from all linked servers  is very procedural which is awkward in T-SQL. So here’s a Powershell solution I created in the form of a few Powershell filters and functions called LibraryLinkedServer.

. .\LibraryLinkedServer.ps1
$logins = @(
'Contoso\Bill'
'Contoso\John'
'Contoso\Jill'
)
Get-CMRegisteredServer "Z001\SQL1" "PRD" | Backup-LinkedServer -LinkedServerLogins $logins
Get-CMRegisteredServer "Z00\SQL1" "PRD" | Remove-LinkedServerLogin -LinkedServerLogins $logins

I would suggest running the code from Powershell ISE so you can step through the code by highlighting and running each line.

The solution first sources the LibraryLinkedServer functions and filters i.e. loads the functions into our current Powershell session. Next we define our list of logins t removing from linked server mappings in an array called $logins. The final two steps involve obtaining a list of SQL Servers from the Central Managment Server Z001\SQL1 and the server group PRD. Next we’ll script out the linked servers using the Backup-LinkedServer function. At this stage I would manually verify the backup files before proceeding with removing the login mappings using Remove-LinkedServerLogin.

Task #3 Remove a Linked Server

You discover linked servers in development which point to production. The linked server should be removed entirely. Your task is to remove a specific linked server named PROD1  from all development servers.  Here again we’ll use the LibraryLinkedServer functions:

. .\LibraryLinkedServer.ps1
Get-CMRegisteredServer "Z001\SQL1" "DEV" | Backup-LinkedServer -LinkedServer "PROD1"
Get-CMRegisteredServer "Z001\SQL1" "DEV" | Remove-LinkedServer -LinkedServer "PROD1"

This task is very similar to task #2 and much of the code is the same, except here we’re specifying a linked server name rather than an array of logins. Again I would suggest manually verifying the backup files before proceeding with removing the linked server.

Note: If you use this solution make sure you test, make backups and verify.

The Problem

You have groups outside of database administration that need to have the ability to manage security of SQL Server logins, users, and roles. Using the out-of-the-box SQL Server roles or permissions doesn’t quite handle all of these use cases and you want to avoid  putting these groups into the sysadmin server role on SQL Server for obvious reasons. One thing you may consider is placing these groups into the  securityadmin server role, but the problem with the securityadmin role is the lack of certain rights. The securityadmin role can only add logins to the instance and not users to databases or in turn users to database roles.  You could add your delegated security  group to every database as db_securityadmin, but this would be difficult to maintain in a highly dynamic environment with thousands of databases. There’s also a larger problem of auditing the actions your security administrators perform. Ideally you want to show every security change is related to specific documented change order. 

You could use the various server and database level permissions over the fixed role approach and in fact that is what Books Online recommends, however you’ll have the same issues of per database permissions and auditable actions to overcome. If this problem statement sounds unfamiliar to you then you probably haven’t had deal with segregation of duties and reducing administration access that has become prevalent in our post-SOX IT world.

A Solution

Looking at web-based applications which run under a service account , you’ll notice  normal users and administrators users as part of almost any applications. Neither group has direct access to the database systems they touch instead a service account is used. A simple idea which can easily be applied to administration functions all we need to do is create a web-based application, assign a service account the necessary rights to perform administration tasks and restrict access to the web-based application. What’s that? You’re not web developer? Well, neither am I. Here’s where Powershell V3 and PowerGUI can help.

Powershell V3 Delegated Administration

One of the simple, but really useful changes made to the Powershell V3 is the ability to delegate administration by setting up  runas credentials for a remoting configuration. In the CTP 1 download there’s an example script called runas.ps1 located under Samples\WindowsPowerShell\DelegatedAdmin which demonstrates the functionality. This presents an interesting an idea, instead of having your security users connect to each SQL Server they could connect to a single “Proxy” server. The proxy server would then connect to various SQL Servers on their behalf.  Of course the account used for the delegated administration will need to have the necessary rights perhaps even sysadmin access. As an added bonus by using Powershell remoting the security administrator’s machine doesn’t need SQL Server tools or SMO installed. The only thing they needed is Powershell.

If you go this route you’ll want to create a distinct Session Configuration (endpoint) and ACL the configuration to the appropriate groups. You’ll l also need to create the various Powershell functions for the administration tasks on the remote server, here’s where I created a module called SqlProxy.

SQLProxy Module

SQL Server lacks Powershell coverage for security administration, so I created SqlProxy module which provides various functions for managing SQL Server logins, users and roles. In addition, because auditability is a key requirement I’ve added logging to a custom Windows Eventlog called “SqlProxy” for every function which change security settings. The module can be used in Powershell V2 or V3 with or without remoting. When used within a delegated administration setting the module will be loaded into the remote session and anyone granted access to the remote endpoint will be able to execute the SqlProxy functions. We could say our problem of providing delegated administration is solved, however unless your security admins are very adapt at Powershell you’ll probably want to provide GUI.

PowerGUI SQLProxy PowerPack

Because PowerGUI provides an easy way to create MMC-style UI’s over Powershell, I applied PowerGUI to the SQLProxy module and created the SqlProxy PowerPack. The security administrators can now use GUI-based tool without having to know Powershell. The complete solution does requires some additional setup and configuration as documented below.

Putting it Together

Diagram courtesy of yuml.me

A security administer will use PowerGUI with the SqlProxy PowerPack installed to connect to a proxy server via Poewershell remoting. The proxy server has a session configuration with delegated credentials. The proxy server also has a SqlProxy module which is then used to connect to the various SQL Servers through SMO. Note Powershell remoting is not used between the proxy server and SQL Server. The only area where Powershell remoting is used is between the user’s machine and the proxy server.

Setup

Setup Proxy Server

Requirements:

• Windows 7 with Sp1, Windows 2008 R2 with SP1 or Windows 8.
• Powershell V3 CTP1 or higher

Installation

1. Install SMO or SQL Server clients tools (Express edition will work)

2, Copy the SqlProxy.psm1 module from http://poshcode.org/3040 to C:\Windows\system32\WindowsPowerShell\v1.0\Modules\SqlProxy folder

3. Create the SqlProxy Eventlog and Source by running following command

New-EventLog -LogName SqlProxy -Source SqlProxy

4. Create the SqlProxy Session Configuration with delegated credentials

$cred = Get-Credential
Register-PSSessionConfiguration -Name "SqlProxy" -RunAsCredential $cred

5. ACL the SqlProxy session configuration to the appropriate AD groups (by default only administrators on the proxy server will have access to the remote session):

Set-PSSessionConfiguration -Name SqlProxy –ShowSecurityDescriptorUI

6. Optional: As noted on the Powershell team blog the default settings  of Powershell remoting are way too low. The low default settings are particularly problematic in a fan-in scenario like SqlProxy. You should make Powershell remoting more robust by changing the default concurrent and memory settings:

cd WSMan:\localhost\Shell
Set-Item .\MaxShellsPerUser 25
Set-Item .\MaxConcurrentUsers 25
Set-Item .\MaxMemoryPerShellMB 1024
 
#Do the same for the SqlProxy Session Configuration
 
cd WSMan:\localhost\Plugin\SqlProxy\Quotas
Set-Item .\MaxShellsPerUser 25
Set-Item .\MaxConcurrentUsers 25
Set-Item .\MaxMemoryPerShellMB 1024

Setup Client

Requirements:

• Powershell V2 or higher (that’s right you can remote from V2 client to V3 server!)
• PowerGUI version 3.0 or higher

Installation

1. Download and install the SqlProxy PowerPack
2. Right click "Connect to Server" Node
3. Select "Shared Scripts" and change $global:SqlProxy variable to your proxy server
4. Uncomment #-ConfigurationName "sqlproxy" in the Get-ValidSession function of Shared Scripts
5. Save script and exit script editor
6. Optional: Re-export the PowerPack with your customizations for reuse in your environment:
   1. Select File, PowerPack Management
   2. Highlight the SQL Security Administration PowerPack
   3. Select edit and change the PowerPack file link to a UNC share in your environment all of your users will have access to.
   4. Export the PowerPack to the UNC share.
   5. Have your colleagues install the customized PowerPack. PowerGUI has nice feature to automatically update PowerPack where you specify a file link whenever you update your customized version all of your users will get update notifications

Using SqlProxy

The SqlProxy PowerPack was written to duplicate functionality available in SQL Server Management Studio. If you’re familiar with logins, user mappings, database users and roles the interface should look familiar. I’ve posted some screen shots as an additional download on the PowerGUI SqlProxy PowerPack site.

Testing On Single Machine

The solution described in this blog post will work on a single machine running Powershell V2 with minimal configuration. The only caveat is that the delegated credentials requires v3. In the course of developing this solution I used Windows 7 with a local SQL Server instance and then would test using a 3-tier solution. A minimal setup for testing on a Windows 7 machine looks like this:

Requirements

• Powershell V2
• Local SQL Server instance
• PowerGUI 3.o or higher

1. Copy the SqlProxy.psm1 module from http://poshcode.org/3040 to C:\Windows\system32\WindowsPowerShell\v1.0\Modules\SqlProxy folder

2. Create the SqlProxy Eventlog and Source by running following command

New-EventLog -LogName SqlProxy -Source SqlProxy

3. Enable Powershell remoting. This  enables remoting but also will allow you to remote into the local machine from the local machine for testing purposes:

Enable-PSRemoting

If you’re on a non-domain machine you will need to add your localhost to the trustedhosteds as described here.

Additional Configurations

If you use this solution you may want to consider additional steps to secure the environment. You can configure a Powershell session configuration to only allow specific commands to be executed. If this is something you’d like to do I encourage you read about constraining a Powershell session. In addition PowerGUI supports a lockdown script to enable administrators to customize PowerGUI configurations for their environment. You read about PowerGUI lockdown features here.

Final Thoughts

I deployed this solution in my environment with around three dozen users. Was it successful? Well, it did allow me to remove various groups from syadmin role. My only gripe is the touch point on each workstation. Because of this you end up dealing with some inconsistencies in configurations. The touch point issue becomes more of a problem as you deal with various operating systems, PowerGUI versions and even antivirus and firewall settings (I found several instances where a client firewall would block Powershell remoting traffic through PowerGUI, but allow it from Powershell). One of the things I’m looking at is moving PowerGUI into a Citrix environment to ensure a single working configuration, but for now I’m happy with the solution. That said, looking at the general problem of delegated administration I wish there was an easier way to create web-based solutions which leverage Powershell.

Someone or an application installs SQL Server, doesn’t grant access to the DBA group and asks for DBA support.

In SQL Server 2008 and higher the built-in local administrators group is no longer automatically part of the SQL Server sysadmin role. You should add necessary logins to the sysadmin role as part of your SQL Server installation. Not automatically granting local administrators access to SQL Server is generally a good thing, however when the SQL Server installation is done by say another application then we see issues were support groups do not have access to SQL Server even though they are local administrators on the box. In the last few months I’ve seen this scenario several times and so has Argenis Fernandez (blog|twitter) as he has a helpful blog post entitled  “Think Your Windows Administrators Don’t Have Access to SQL Server 2008 by Default? Think Again.”  The post describes a technique of using the Sysinternals tool PsExec to gain SQL sysadmin access to SQL Server on which you already have local administrator access. The  post also links to a documented way of starting SQL Server in single user mode in order to gain SQL Server sysadmin access (see “Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out).” And finally the post mentions, but does not demonstrate a method of using the Windows Task Scheduler. If you’re interested in the how’s and why’s this works and how different versions of SQL Servers are well, different in security settings defaults I encourage you to give the post and comments a read.

Armed with information on how to gain SQL Server administration I looked at the various options. The psexec utility is blacklisted in my environment, blocked from download and listed as an untrusted application. The approach of starting SQL Server in single user mode requires taking SQL Server down and since the application is a quasi-production system restarting SQL Server would have to be coordinated or done after hours. So, I chose to to use the Windows Scheduler trick. This should work on Windows 2003/XP and higher and I’ve tested on Windows 2008 and Windows 7. The typical UAC things apply—you’ll need to run as administrator. The script I came up with is listed below. I figured if I’m getting a server were some other group performed the installation or configuration there’s no guarantee PowerShell will be installed, so I’m going old school Windows batch file on this. It feels wrong to post a Windows batch file  instead of a Powershell script on my blog, but I think using a batch file is the best approach for the problem. To use, save the script as AddDBA.bat and see the example syntax. The script must be run locally.

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
26
27
28
29
30
31
32
33
34
35
36

@echo off
@if "%1"=="?" goto Syntax
@if "%1"==""  goto Syntax
@if "%2"==""  goto Syntax
rem **********************************
rem Script AddDBA.bat
rem Creation Date: 10/21/2011
rem Last Modified: 10/21/2011
rem Author: Chad Miller
rem ***********************************
rem Description: Adds a Windows Account to SQL Sysadmin Role
rem Use when you have local Windows admin access but lost SQL Sysadmin access
rem ***********************************
 
@echo ************************
@echo *** ServerInstance: %1
@echo ************************
set TMPFILE=%TMP%\AddDBA-%RANDOM%-%TIME:~6,5%.tmp
schtasks  /Create /TN AddDBA /SC Once /ST 12:00 ^
/TR "sqlcmd -S %1 -Q \"CREATE LOGIN [%2] FROM WINDOWS; EXEC sp_addsrvrolemember [%2],[sysadmin];\" -o \"%TMPFILE%\" -e" ^
/RU "NT AUTHORITY\SYSTEM" /F
schtasks /Run /TN AddDBA
schtasks /Query /TN AddDBA /V /FO List
rem Wait 5 seconds
PING 127.0.0.1 -n 6  >NUL
rem Display output file
type %TMPFILE%
schtasks /Delete /TN AddDBA /F
goto :EXIT
 
:Syntax
@echo Syntax: AddDBA ServerInstance WindowsGroupOrLogin
@echo Example: AddDBA Z001\SQL1 Contoso\DBAGroup
goto :EXIT
 
:EXIT

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