Configuring AlwaysOn with SQL Server PowerShell

Posted by Code Monkey on February 4, 2012

In this post, I’ll give examples of PowerShell scripts for configuring your SQL server instances for AlwaysOn.

Before continuing, please review the system requirements for AlwaysOn Availability Groups: Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server)

A note on running scripts: To run the script samples below, copy the samples into a local file on one of your domain-joined servers (ensure the file has the *.ps1 extension). Then open a PowerShell console and launch SQLPS, the SQL PowerShell scripting environment, by typing “SQLPS”. Note that you need to have SQL Server Management tools installed to run SQLPS. Next, ensure that your execution policy is set to Unrestricted. Since SQLPS has an execution policy separate from the system execution policy, you have to specify the -Scope parameter, as follows:

Set-ExecutionPolicy Unrestricted -Scope Process

Then, you can run the script from the local file. For example, if I copy a script to C:\scripts\temp.ps1, my session would look like:

PS C:\> SQLPS

Microsoft SQL Server PowerShell
Version 11.0.2100.54
Microsoft Corp. All rights reserved.

PS SQLSERVER:\> Set-ExecutionPolicy Unrestricted -Scope Process
PS SQLSERVER:\> C:\scripts\temp.ps1

Step 0: Creating a Cluster

All machines that host server instances in your AlwaysOn Availability Groups configuration must be members of a Windows Failover Cluster. I’ll give a brief overview of how to create a cluster, in case you do not already have one. First, ensure that the Windows Server Failover Clustering feature is enabled on all your servers. You can do so through the server manager UI, see Install the Failover Clustering Feature, or you can use the following PowerShell commands from an elevated PowerShell prompt (in Windows Server 2008 R2 only):

Import-Module ServerManager
Add-WindowsFeature Failover-Clustering

Run this command on every machine that you wish to join to the cluster. Next you have to create the actual cluster. Again, you can either user the server manager (see Create a New Failover Cluster in Server Manager) or PowerShell commands (see Using Windows PowerShell Cmdlets on Failover Clusters in Windows Server 2008 R2). If you wish to use PowerShell, take a look at the Test-Cluster and New-Cluster cmdlets. The former is used to run the Windows Clustering verification tests on your machines, a prerequisite for creating a cluster. The latter is used to create the cluster.

Step 1: Enabling AlwaysOn and Creating Endpoints

Once you have created the failover cluster, you need to enable the AlwaysOn feature and create endpoints on all of your instances of SQL Server. These endpoints facilitate the movement of data between servers. Happily, we can do this entire configuration in PowerShell, as demonstrated in the script below.

###########################################################
# SCRIPT VARIABLES
# Set these values before running the script!
###########################################################

# Name of the server instances that will participate in the availability group
$ServerList = @(“mymachine\myinstance”, ”myothermachine\myotherinstance”)

# The default port used by the endpoints, if we need to create them
$EndpointPort = 5022

# The name of the endpoint created on each server, if we need to create one
$EndpointName = ”AlwaysOn_Endpoint”

###########################################################
# SCRIPT BODY
###########################################################

foreach ($server in $ServerList)
{
    # Connection to the server instance, using Windows authentication
    Write-Host -Foreground Green ”Creating SMO Server object for server: $server”
    $serverObject = New-Object Microsoft.SQLServer.Management.SMO.Server($server)

    # Enable AlwaysOn. We use the -Force option to force a server restart without confirmation.
    # This WILL result in your SQL Server instance restarting.
    Write-Host -Foreground Green ”Enabling AlwaysOn on server instance: $server”
    Enable-SqlAlwaysOn -InputObject $serverObject -Force

    # Check if the server already has a mirroring endpoint
    $endpointObject = $serverObject.Endpoints `
        | Where-Object { $_.EndpointType -eq ”DatabaseMirroring” } `
        | Select-Object -First 1

    # Create an endpoint if one doesn’t exist
    if($endpointObject -eq $null)
    {
        Write-Host -Foreground Green ”Creating endpoint on server instance: $server”
        $endpointObject = New-SqlHadrEndpoint `
                            -InputObject $serverObject `
                            -Name $EndpointName `
                            -Port $EndpointPort
    }
    else
    {
        Write-Host -Foreground Yellow ”An endpoint already exists on ’$server’.”
    }

    # Start the endpoint
    Write-Host -Foreground Green ”Starting endpoint on server instance: $server”
    Set-SqlHadrEndpoint -InputObject $endpointObject -State ”Started” | Out-Null
}

Let’s walk through this script. At the top, are a few variables that you should set based on your local environment. The ServerList variable holds the names of the servers we wish to configure. The EndpointPort variable holds the port number we assign by default to the endpoints we create. The EndpointName variable holds the name we assign by default to the endpoints we create.

In the body of the script, we iterate through the server list, and perform the following for each entry: first, we connect to the instance using Windows authentication (therefore, the user running this script is assumed to have sufficient privileges on each server instance). Next, we enable the AlwaysOn feature using the Enable-SqlAlwaysOn cmdlet. It’s important to note the -Force parameter we provide to this cmdlet: enabling AlwaysOn requires a restart of the SQL Server service, and the -Force option tells the cmdlet to go ahead and perform this restart without user confirmation. Without this parameter, the script will pause and ask you to confirm the restart. Next, we check if a database mirroring endpoint exists on the server. If not, we create an endpoint using the New-SqlHadrEndpoint cmdlet. Finally, we use the Set-SqlHadrEndpoint cmdlet to set the state of the endpoint to “Started”.

Some important notes about endpoints: In the example above, we assume that (1) the SQL Server service account on each server instance is a domain account, and (2) the same domain account is used for all service accounts. If this is not the case in your environment, you will have to grant the CONNECT permission on the endpoints created by this script to all of your service accounts. There is no explicit cmdlet support for this operation, but you can still script this step using the SQL Server Management Objects API (see the following API reference: Endpoint.Grant Method). Otherwise, you can always use the Invoke-Sqlcmd cmdlet to directly execute Transact-SQL statements.

Step 2: Creating the Availability Group

Now we can, at last, create our availability group. There are several steps to this process, all of which are supported by PowerShell cmdlets:

First, we need to decide how the replicas in our availability group should be configured. The two most important settings on a replica are its failover mode and availability mode. There are a few other settings as well, for example whether the replica is readable in the secondary role. In the script below, all of our replicas use the asynchronous-commit availability mode and the manual failover mode.
Next, we need to decide which databases we want to include in the availability group. Assume that these databases all exist on a single server instance, our initial primary. Before we can create the availability group, we need to “seed” these databases to all of the secondaries. That is, we need to take a backup of each database and its log, and then restore these backups on each secondary.
Next, we can create the availability group.
Finally, we need to execute a join command on the secondary replicas and databases.

Here is a script demonstrating these steps:

###########################################################
# SCRIPT VARIABLES
# Set these values before running the script!
###########################################################

# Name of the server instances that will participate in the availability group.
# The first server is assumed to be the initial primary, the others initial secondaries.
$ServerList = @(“mymachine\myinstance”, ”myothermachine\myotherinstance”)

# Name of the availability group
$AgName = ”MyAvailabilityGroup”

# Names of the databases to add to availability group
$DatabaseList = @(“mydatabase”)

# Directory for backup files
$BackupShare = ”\my\backup\share”

###########################################################
# SCRIPT BODY
###########################################################

# Initialize some collections
$serverObjects = @()
$replicas = @()

    # Get the mirroring endpoint on the server
    $endpointObject = $serverObject.Endpoints `
        | Where-Object { $_.EndpointType -eq ”DatabaseMirroring” } `
        | Select-Object -First 1

    # Create an endpoint if one doesn’t exist
    if($endpointObject -eq $null)
    {
        throw ”No Mirroring endpoint found on server: $server”
    }

    $fqdn = $serverObject.Information.FullyQualifiedNetName
    $port = $endpointObject.Protocol.Tcp.ListenerPort
    $endpointURL = ”TCP://${fqdn}:${port}”

    # Create an availability replica for this server instance.
    $replicas += (New-SqlAvailabilityReplica `
            -Name $server `
            -EndpointUrl $endpointURL `
            -AvailabilityMode ”AsynchronousCommit” `
            -FailoverMode ”Manual” `
            -AsTemplate `
            -Version 11)
}

$primary, $secondaries = $serverObjects

# Create the initial copies of the databases on the secondaries,
# via backup/restore
foreach ($db in $DatabaseList)
{
$bakFile = Join-Path $BackupShare ”$db.bak”
$trnFile = Join-Path $BackupShare ”$db.trn”

    Write-Host -Foreground Green ”Backing up database ’$db’ on $primary to $bakFile”
    Backup-SqlDatabase `
           -InputObject $primary `
           -Database $db `
           -BackupFile $bakFile `
           -Init

    Write-Host -Foreground Green ”Backing up the log of ’$db’ on $primary to $trnFile”
    Backup-SqlDatabase `
            -InputObject $primary `
            -Database $db `
            -BackupFile $trnFile `
            -BackupAction ”Log” `
            -Init

    foreach($secondary in $secondaries)
    {
        Write-Host -Foreground Green ”Restoring database ’$db’ on $secondary from $bakFile”
        Restore-SqlDatabase `
          -InputObject $secondary `
          -Database $db `
          -BackupFile $bakFile `
          -NoRecovery

        Write-Host -Foreground Green ”Restoring the log of ’$db’ on $secondary from $trnFile”
        Restore-SqlDatabase `
          -InputObject $secondary `
          -Database $db `
          -BackupFile $trnFile `
          -RestoreAction ”Log” `
          -NoRecovery
    }
}

# Create the availability group
New-SqlAvailabilityGroup `
  -Name $AgName `
  -InputObject $primary `
  -AvailabilityReplica $Replicas `
  -Database $DatabaseList

# Join the secondary replicas, and join the databases on those replicas
foreach ($secondary in $secondaries)
{
    Write-Host -Foreground Green ”Joining instance ’$secondary’ to the AG ’$AgName’”
    Join-SqlAvailabilityGroup -InputObject $secondary -Name $AgName
    $ag = $secondary.AvailabilityGroups[$AgName]
    Write-Host -Foreground Green ”Joining databases on ’$secondary’ to the AG ’$AgName’”
    Add-SqlAvailabilityDatabase -InputObject $ag -Database $DatabaseList
}

As before, the script begins with a few variables that you should define based on your environment. ServerList is the same as before, however the ordering is relevant. We assume that the first element of the list is the server instance that will host the initial primary replica, where all of the databases are stored. The AgName variable holds the name of the availability group we will create. DatabaseList holds the names of the databases on the initial primary replica that we want to include in our availability group. Lastly, BackupShare is the file system location where we shall store the backups done as part of the “seeding” operation.

In the script body, we again iterate through the server list and establish connections. Then, for each server, we create an Availability Replica object, using the New-SqlAvailabilityReplica cmdlet. We pass the various replica configuration options, such as the failover mode and availability mode, to this cmdlet. We specify the -AsTemplate parameter here, which creates the availability replica object in memory (as opposed to committing the change on the server, an impossibility since we haven’t created the availability group yet). The -Version parameter indicates the server version for which we should create this in-memory object (this should match the version of the server where you ultimately create the availability group). We access the endpoint on the server to generate an endpoint URL for the replica (explained here). Next, we perform the data seeding step. We back up each database and its log, using the Backup-SqlDatabase cmdlet. Then we restore the database and log to each secondary, using the Restore-SqlDatabase cmdlet. Crucially, we use the -NoRecovery option when performing the restores, a requirement for AlwaysOn.

Next, we create the availability group with the New-SqlAvailabilityGroup cmdlet. We pass in the in-memory replica configurations that we created above, as well as the list of database names. Finally, we iterate through all the secondary replicas, joining them to the availability group with the Join-SqlAvailabilityGroup cmdlet and joining the secondary databases therein with the Add-SqlAvailabilityDatabase cmdlet.