Configuring AlwaysOn with SQL Server PowerShell

  • Artigo

In this post, I’ll give examples of PowerShell scripts for configuring your SQL server instances for AlwaysOn. PowerShell is a a great tool for automating management tasks across multiple servers, which is useful for an inherently multiserver environment like AlwaysOn. Moreover, if your deployment includes machines running Server Core, PowerShell gives you a way to accomplish management tasks on these machines.

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 local files 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 RemoteSigned. Since SQLPS has an execution policy separate from the system execution policy, you have to specify the - Scope parameter, as follows: 

Set-ExecutionPolicy RemoteSigned -Scope Process 

This indicates that we're setting the execution local execution policy for the process SQLPS. 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 RemoteSigned -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. This is especially useful if you need to enable AlwaysOn on a machine running Server Core, where you don't have access to SQL Configuration Manager. Here is a link to a script that performs this task:

https://gallery.technet.microsoft.com/scriptcenter/Configuring-a-Server-for-0b95eb8e

Let’s walk through this script. At the top, we define a few parameters. The ServerList parameter specifies the names of the servers we wish to configure. This is a mandatory parameter. The EndpointPort parameter specifies the port number we assign to the endpoints we create, and by default is 5022. The EndpointName parameter specifies the name we assign by to the endpoints we create, and by default is "AlwaysOn_Endpoint".

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:

  1. 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.
  2. 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.
  3. Next, we can create the availability group.
  4. Finally, we need to execute a join command on the secondary replicas and databases.

Here is a link to a script that performs these tasks:

https://gallery.technet.microsoft.com/scriptcenter/Creating-An-Availability-2c973b94

As before, the script begins with a few parameters that you should specify when you invoke the script. 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 parameter specifies the name of the availability group we will create, and by default is "MyAvailabilityGroup". DatabaseList specifies 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 theJoin-SqlAvailabilityGroup cmdlet and joining the secondary databases therein with the Add-SqlAvailabilityDatabasecmdlet.

Comments

  • Anonymous
    September 20, 2012
    Very helpful post