Summary: Microsoft SQL Server PFE, Thomas Stringer, talks about using Windows PowerShell to set up an AlwaysOn availability group.

Microsoft Scripting Guy, Ed Wilson, is here. Today we kick off SQL Server Windows PowerShell Week. Our first blog of the series is a guest blog written by SQL Server PFE, Thomas Stringer. Here is a little bit about Thomas.

Thomas Stringer is a SQL Server premier field engineer at Microsoft. He specializes in scripting (Windows PowerShell), high availability, disaster recovery, performance tuning, internals, querying, and database administration. He also has a software development background in languages and platforms from C all the way through .NET (including C# for WinForm and ASP.NET WebForms/MVC applications).

Email: sqlsalt@outlook.com
Blog: sql salt…ride the SQL Server wave of data
Twitter: @SQLife
LinkedIn: Thomas Stringer

Take it away, Thomas…

If you have any connection to the SQL Server world, by now you have most likely heard of the cutting-edge, high-availability technology in SQL Server 2012 marketed as AlwaysOn. This includes failover cluster instances and stand-alone instances hosting a replica for an availability group. But the possibilities are endless with implementing this new technology. As administrators of data, it is our responsibility to ensure the highest amount of uptime and the quickest and safest way to facilitate disaster recovery. The AlwaysOn Availability Groups feature also gives us cool features, such as active secondaries, which allow us to run reports and backups against secondary replicas.

I will not be going into the nitty gritty about what AlwaysOn availability groups are capable of, but for some great reading, please feel free to check out this great reference about the topic: Overview of AlwaysOn Availability Groups (SQL Server). In today’s blog, I will show you how to set up and configure an AlwaysOn availability group through Windows PowerShell.

Now on to the fun part. I will illustrate this task through Windows PowerShell by creating a single availability group with two replicas. We will review the finer details with each operation, but this is a common scenario for a no-data-loss implementation. An availability group can contain multiple databases; but for the sake of brevity, I will have only one database (our trusty AdventureWorks2012 sample database). The following diagram shows what we will set up in this example:

The first step to getting to the diagram’s operational state, is to take the necessary backups on the primary replica and restore them on the secondary replica. We need the database sitting on the secondary replica for the database to be joined on this instance. We can accomplish this by using the Backup and Restore SMO classes. Before I jump into this, I am going to define a handful of parameters that will be consumed throughout this post:

$SqlServerPrimName = “ALWAYSON1”

$SqlServerSecName = “ALWAYSON2”

$SqlAgName = “MyTestAg”

$SqlAgDatabase = “AdventureWorks2012”

$AgListenerName = “AgListener1”

$AgListenerPort = 1433

$AgListenerIpAddress = “192.168.1.100”

$AgListenerSubnetMask = “255.255.255.0”

$HadrEndpointName = “HadrEndpoint”

$HadrEndpointPort = 5022

$BackupDirectory = “\\Your\Backup\Directory”

$SqlServerPrim = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerPrimName)

$SqlServerSec = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerSecName)

Most of these variables are self-explanatory, but they will be referenced in the following multiple code snippets. So, shifting back to our backups and restores to get the database on the secondary replica:

# backup the database on the primary replica/server (full database backup)

$DbBackup = New-Object Microsoft.SqlServer.Management.Smo.Backup

$DbBackup.Database = $SqlAgDatabase

$DbBackup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database

$DbBackup.Initialize = $true

$DbBackup.Devices.AddDevice(“$BackupDirectory\$($SqlAgDatabase)_AgSetup_full.bak”,

  [Microsoft.SqlServer.Management.Smo.DeviceType]::File)

$DbBackup.SqlBackup($SqlServerPrim)

 

# backup the database on the primary replica/server (transaction log backup)

$DbBackup = New-Object Microsoft.SqlServer.Management.Smo.Backup

$DbBackup.Database = $SqlAgDatabase

$DbBackup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Log

$DbBackup.Initialize = $true

$DbBackup.Devices.AddDevice(“$BackupDirectory\$($SqlAgDatabase)_AgSetup_log.trn”,

  [Microsoft.SqlServer.Management.Smo.DeviceType]::File)

$DbBackup.SqlBackup($SqlServerPrim)

 

# restore the database on the secondary replica/server (full database restore)

$DbRestore = New-Object Microsoft.SqlServer.Management.Smo.Restore

$DbRestore.Database = $SqlAgDatabase

$DbRestore.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Database

$DbRestore.Devices.AddDevice(“$BackupDirectory\$($SqlAgDatabase)_AgSetup_full.bak”,

  [Microsoft.SqlServer.Management.Smo.DeviceType]::File)

$DbRestore.NoRecovery = $true

$DbRestore.SqlRestore($SqlServerSec)

 

# restore the database on the secondary replica/server (transaction log restore)

$DbRestore = New-Object Microsoft.SqlServer.Management.Smo.Restore

$DbRestore.Database = $SqlAgDatabase

$DbRestore.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Log

$DbRestore.Devices.AddDevice(“$BackupDirectory\$($SqlAgDatabase)_AgSetup_log.trn”,

  [Microsoft.SqlServer.Management.Smo.DeviceType]::File)

$DbRestore.NoRecovery = $true

$DbRestore.SqlRestore($SqlServerSec)

The previous code simply creates a full database backup and a transaction log backup on the primary server, and it restores those backups on the secondary server (with NoRecovery).

Availability groups move data between endpoints on the replicas, and this is done through a database mirroring endpoint. What is worth noting is that each instance can only have one database mirroring endpoint (which all availability groups can use). It doesn’t exist out-of-the-box. Therefore, we will perform a quick test for the existence of it on each replica, and if one isn’t already created, we will create it.

# create the endpoint if it doesn’t exist on the primary replica

$EndpointPrim = $SqlServerPrim.Endpoints |

  Where-Object {$_.EndpointType -eq [Microsoft.SqlServer.Management.Smo.EndpointType]::DatabaseMirroring}

if(!$EndpointPrim) {

  $EndpointPrim = New-Object Microsoft.SqlServer.Management.Smo.Endpoint($SqlServerPrim, $HadrEndpointName)

  $EndpointPrim.EndpointType = [Microsoft.SqlServer.Management.Smo.EndpointType]::DatabaseMirroring

  $EndpointPrim.ProtocolType = [Microsoft.SqlServer.Management.Smo.ProtocolType]::Tcp

  $EndpointPrim.Protocol.Tcp.ListenerPort = $HadrEndpointPort

  $EndpointPrim.Payload.DatabaseMirroring.ServerMirroringRole = [Microsoft.SqlServer.Management.Smo.ServerMirroringRole]::All

  $EndpointPrim.Payload.DatabaseMirroring.EndpointEncryption = [Microsoft.SqlServer.Management.Smo.EndpointEncryption]::Required

  $EndpointPrim.Payload.DatabaseMirroring.EndpointEncryptionAlgorithm = [Microsoft.SqlServer.Management.Smo.EndpointEncryptionAlgorithm]::Aes

 

  $EndpointPrim.Create()

  $EndpointPrim.Start()

}

 

# create the endpoint if it doesn’t exist on the secondary replica

$EndpointSec = $SqlServerSec.Endpoints |

  Where-Object {$_.EndpointType -eq [Microsoft.SqlServer.Management.Smo.EndpointType]::DatabaseMirroring}

if(!$EndpointSec) {

  $EndpointSec = New-Object Microsoft.SqlServer.Management.Smo.Endpoint($SqlServerSec, $HadrEndpointName)

  $EndpointSec.EndpointType = [Microsoft.SqlServer.Management.Smo.EndpointType]::DatabaseMirroring

  $EndpointSec.ProtocolType = [Microsoft.SqlServer.Management.Smo.ProtocolType]::Tcp

  $EndpointSec.Protocol.Tcp.ListenerPort = $HadrEndpointPort

  $EndpointSec.Payload.DatabaseMirroring.ServerMirroringRole = [Microsoft.SqlServer.Management.Smo.ServerMirroringRole]::All

  $EndpointSec.Payload.DatabaseMirroring.EndpointEncryption = [Microsoft.SqlServer.Management.Smo.EndpointEncryption]::Required

  $EndpointSec.Payload.DatabaseMirroring.EndpointEncryptionAlgorithm = [Microsoft.SqlServer.Management.Smo.EndpointEncryptionAlgorithm]::Aes

 

  $EndpointSec.Create()

  $EndpointSec.Start()

}

We can reference these variables against our declarations earlier in this post. Feel free to change the variables. For instance, if you don’t want your database mirroring endpoints to be on port 5022, alter the $HadrEndpointPort variable value. At this point, we are ready to start defining replicas, availability group databases, and listeners. Then we can create the availability group. We will go through this step-by-step.

$AvailabilityGroup = New-Object Microsoft.SqlServer.Management.Smo.AvailabilityGroup($SqlServerPrim, $SqlAgName)

In this script, we defined our base availability group object. Its parent (the constructor’s first parameter) will be the primary Server Class object (defined in our initial variable code block), which we would create the availability group on (that is, the server you want to initially be the primary replica for the availability group). The name (the constructor’s second parameter) is set to the content of our $SqlAgName variable. (Again, feel free to rework any and all code here, including variable definitions and configuration specifications.)

It is time to define our replica (primary and secondary) objects. There are many variations, and your implementation and SLAs will dictate the specifics of commit modes, access allowances, and so on.

# create the primary replica object

$PrimaryReplica = New-Object Microsoft.SqlServer.Management.Smo.AvailabilityReplica($AvailabilityGroup, $SqlServerPrimName)

$PrimaryReplica.EndpointUrl = “TCP://$($SqlServerPrim.NetName):$($EndpointPrim.Protocol.Tcp.ListenerPort)”

$PrimaryReplica.FailoverMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaFailoverMode]::Automatic

$PrimaryReplica.AvailabilityMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaAvailabilityMode]::SynchronousCommit

$AvailabilityGroup.AvailabilityReplicas.Add($PrimaryReplica)

Notice that our primary replica’s object is set with the endpoint URL of our database mirroring endpoint on this replica. This is one of the benefits of using variables: If you need to change the port on which you want the database mirroring endpoint to be created, and you forget to alter a hard-coded value for the URL (if you chose that route), you would not be able to successfully send and receive data due to an endpoint port mismatch. Both of these replicas are going to be automatic failover partners with synchronous commit (a prerequisite for automatic failover capability). Now to create the secondary replica:

# create the secondary replica object

$SecondaryReplica = New-Object Microsoft.SqlServer.Management.Smo.AvailabilityReplica($AvailabilityGroup, $SqlServerSecName)

$SecondaryReplica.EndpointUrl = “TCP://$($SqlServerSec.NetName):$($EndpointSec.Protocol.Tcp.ListenerPort)”

$SecondaryReplica.FailoverMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaFailoverMode]::Automatic

$SecondaryReplica.AvailabilityMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaAvailabilityMode]::SynchronousCommit

$AvailabilityGroup.AvailabilityReplicas.Add($SecondaryReplica)

This is almost identical to the primary replica object definition, except the EndpointUrl property uses the secondary replica’s NetName property. (The end result is to have “TCP://MyPrimaryServer:5022” and “TCP://MySecondaryServer:5022” for the respective replicas.)

Now that the replica objects are created, we will create the availability group database object by defining the constructor’s first parameter as the AvailabilityGroup object that it will belong to, and the database name. (In my case, this is stored in the $SqlAgDatabase variable and set to AdventureWorks2012.)

# create the availability group database object

$AvailabilityDb = New-Object Microsoft.SqlServer.Management.Smo.AvailabilityDatabase($AvailabilityGroup, $SqlAgDatabase)

$AvailabilityGroup.AvailabilityDatabases.Add($AvailabilityDb)

The next step is optional. You do not have to create an availability group listener, but I highly advise it. The reason is because the listener is the virtual network name that always points to the current primary replica. It is the layer of abstraction for clients, and the actual replica server names are not necessary when connecting to the primary replica. For more information about availability group listeners and client connectivity, please see Availability Group Listeners on MSDN.

# create the listener object

$AgListener = New-Object Microsoft.SqlServer.Management.Smo.AvailabilityGroupListener($AvailabilityGroup, $AgListenerName)

$AgListenerIp = New-Object Microsoft.SqlServer.Management.Smo.AvailabilityGroupListenerIPAddress($AgListener)

$AgListener.PortNumber = $AgListenerPort

$AgListenerIp.IsDHCP = $false

$AgListenerIp.IPAddress = $AgListenerIpAddress

$AgListenerIp.SubnetMask = $AgListenerSubnetMask

$AgListener.AvailabilityGroupListenerIPAddresses.Add($AgListenerIp)

$AvailabilityGroup.AvailabilityGroupListeners.Add($AgListener)

There are a handful of parameters set here, and many of these are predefined variables from the earlier script. This will define the specifications for our listener (for instance, IP address and port number).

The last step to the actual availability group creation is simple: We add the availability group object we’ve been working with to the collection of availability groups on the primary server, and we call the Create() method.

# create the availability group

$SqlServerPrim.AvailabilityGroups.Add($AvailabilityGroup)

$AvailabilityGroup.Create()

You might be thinking that we’re done here, but there are two more quick actions to complete. This availability group was created on the primary replica, but now we need to go to the secondary replica and join that replica and its appropriate database to the availability group.

# on the secondary replica, join the replica to the AG, and join the database to the AG

$SqlServerSec.JoinAvailabilityGroup($SqlAgName)

$SqlServerSec.AvailabilityGroups[$SqlAgName].AvailabilityDatabases[$SqlAgDatabase].JoinAvailablityGroup()

And there you have it! We’ve created a functioning availability group for high-availability—all without touching SQL Server Management Studio or writing a line of T-SQL! Not to mention…wisely putting these calls into a script or cmdlets will provide us with reusable code to set up and configure a future availability group. The full script can be viewed and downloaded from the Script Center Repository: Create an AlwaysOn Availability Group with PowerShell.

~Tom

Thank you, Tom, for a great blog post. Join me tomorrow for our second blog in SQL Server Windows PowerShell Week.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy