Create an Availability Group (SQL Server PowerShell)

 

Published: May 17, 2016

Updated: May 17, 2016

Applies To: SQL Server 2016

This topic describes how to use PowerShell cmdlets to create and configure an Always On availability group by using PowerShell in SQL Server 2016. An availability group defines a set of user databases that will fail over as a single unit and a set of failover partners, known as availability replicas, which support failover.

System_CAPS_ICON_note.jpg Note


For an introduction to availability groups, see [Overview of Always On Availability Groups (SQL Server)](../Topic/Overview%20of%20Always On%20Availability%20Groups%20(SQL%20Server).md).

System_CAPS_ICON_note.jpg Note


As an alternative to using PowerShell cmdlets, you can use the Create Availability Group wizard or Transact-SQL. For more information, see Use the New Availability Group Dialog Box (SQL Server Management Studio) or Create an Availability Group (Transact-SQL).

We strongly recommend that you read this section before attempting to create your first availability group.

Prerequisites, Restrictions, and Recommendations

  • Before creating an availability group, verify that the host instances of SQL Server each resides on a different Windows Server Failover Clustering (WSFC) node of a single WSFC failover cluster. Also, verify that your server instances met the other server-instance prerequisites and that all of the other Always On Availability Groups requirements are meet and that you are aware of the recommendations. For more information, we strongly recommend that you read [Prerequisites, Restrictions, and Recommendations for Always On Availability Groups (SQL Server)](../Topic/Prerequisites,%20Restrictions,%20and%20Recommendations%20for%20Always On%20Availability%20Groups%20(SQL%20Server).md).

Security

Permissions

Requires membership in the sysadmin fixed server role and either CREATE AVAILABILITY GROUP server permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.

Summary of Tasks and Corresponding PowerShell Cmdlets

The following table lists the basic tasks involved in configuring an availability group and indicates those that are supported by PowerShell cmdlets. The Always On Availability Groups tasks must be performed in the sequence in which they are presented in the table.

TaskPowerShell Cmdlets (if Available) or Transact-SQL StatementWhere to Perform Task*
Create database mirroring endpoint (once per SQL Server instance)New-SqlHadrEndPointExecute on each server instance that lacks database mirroring endpoint.

Note: To alter an existing database mirroring endpoint, use Set-SqlHadrEndpoint.
Create availability groupFirst, use the New-SqlAvailabilityReplica cmdlet with the -AsTemplate parameter to create an in-memory availability-replica object for each of the two availability replicas that you plan to include in the availability group.

Then, create the availability group by using the New-SqlAvailabilityGroup cmdlet and referencing your availability-replica objects.
Execute on the server instance that is to host the initial primary replica.
Join secondary replica to availability groupJoin-SqlAvailabilityGroupExecute on each server instance that is hosts a secondary replica.
Prepare the secondary databaseBackup-SqlDatabase and Restore-SqlDatabaseCreate backups on the server instance that hosts the primary replica.

Restore backups on each server instance that hosts a secondary replica, using the NoRecovery restore parameter. If the file paths differ between the computers that host the primary replica and the target secondary replica, also use the RelocateFile restore parameter.
Start data synchronization by joining each secondary database to availability groupAdd-SqlAvailabilityDatabaseExecute on each server instance that hosts a secondary replica.

*To perform a given task, change directory (cd) to the indicated server instance or instances.

To Set Up and Use the SQL Server PowerShell Provider

System_CAPS_ICON_note.jpg Note


To view the syntax and an example of a given cmdlet, use the Get-Help cmdlet in the SQL Server PowerShell environment. For more information, see Get Help SQL Server PowerShell.

  1. Change directory (cd) to the server instance that is to host the primary replica.

  2. Create an in-memory availability-replica object for the primary replica.

  3. Create an in-memory availability-replica object for each of the secondary replicas.

  4. Create the availability group.

    System_CAPS_ICON_note.jpg Note


    The maximum length for an availability group name is 128 characters.

  5. Join the new secondary replica to the availability group. For more information, see Join a Secondary Replica to an Availability Group (SQL Server).

  6. For each database in the availability group, create a secondary database by restoring recent backups of the primary database, using RESTORE WITH NORECOVERY.

  7. Join every new secondary database to the availability group. For more information, see Join a Secondary Replica to an Availability Group (SQL Server).

  8. Optionally, use the Windows dir command to verify the contents of the new availability group.

System_CAPS_ICON_note.jpg Note


If the SQL Server service accounts of the server instances run under different domain user accounts, on each server instance, create a login for the other server instance and grant this login CONNECT permission to the local database mirroring endpoint.

The following PowerShell example creates and configures a simple availability group named MyAG with two availability replicas and one availability database. The example:

  1. Backs up MyDatabase and its transaction log.

  2. Restores MyDatabase and its transaction log, using the -NoRecovery option.

  3. Creates an in-memory representation of the primary replica, which will be hosted by the local instance of SQL Server (named PrimaryComputer\Instance).

  4. Creates an in-memory representation of the secondary replica, which will be hosted by an instance of SQL Server (named SecondaryComputer\Instance).

  5. Creates an availability group named MyAG.

  6. Joins the secondary replica to the availability group.

  7. Joins the secondary database to the availability group.

# Backup my database and its log on the primary  
Backup-SqlDatabase `  
    -Database "MyDatabase" `  
    -BackupFile "\\share\backups\MyDatabase.bak" `  
    -ServerInstance "PrimaryComputer\Instance"  
  
Backup-SqlDatabase `  
    -Database "MyDatabase" `  
    -BackupFile "\\share\backups\MyDatabase.log" `  
    -ServerInstance "PrimaryComputer\Instance" `  
    -BackupAction Log   
  
# Restore the database and log on the secondary (using NO RECOVERY)  
Restore-SqlDatabase `  
    -Database "MyDatabase" `  
    -BackupFile "\\share\backups\MyDatabase.bak" `  
    -ServerInstance "SecondaryComputer\Instance" `  
    -NoRecovery  
  
Restore-SqlDatabase `  
    -Database "MyDatabase" `  
    -BackupFile "\\share\backups\MyDatabase.log" `  
    -ServerInstance "SecondaryComputer\Instance" `  
    -RestoreAction Log `  
    -NoRecovery  
  
# Create an in-memory representation of the primary replica.  
$primaryReplica = New-SqlAvailabilityReplica `  
    -Name "PrimaryComputer\Instance" `  
    -EndpointURL "TCP://PrimaryComputer.domain.com:5022" `  
    -AvailabilityMode "SynchronousCommit" `  
    -FailoverMode "Automatic" `  
    -Version 12 `  
    -AsTemplate  
  
# Create an in-memory representation of the secondary replica.  
$secondaryReplica = New-SqlAvailabilityReplica `  
    -Name "SecondaryComputer\Instance" `  
    -EndpointURL "TCP://SecondaryComputer.domain.com:5022" `  
    -AvailabilityMode "SynchronousCommit" `  
    -FailoverMode "Automatic" `  
    -Version 12 `  
    -AsTemplate  
  
# Create the availability group  
New-SqlAvailabilityGroup `  
    -Name "MyAG" `  
    -Path "SQLSERVER:\SQL\PrimaryComputer\Instance" `  
    -AvailabilityReplica @($primaryReplica,$secondaryReplica) `  
    -Database "MyDatabase"  
  
# Join the secondary replica to the availability group.  
Join-SqlAvailabilityGroup -Path "SQLSERVER:\SQL\SecondaryComputer\Instance" -Name "MyAG"  
  
# Join the secondary database to the availability group.  
Add-SqlAvailabilityDatabase -Path "SQLSERVER:\SQL\SecondaryComputer\Instance\AvailabilityGroups\MyAG" -Database "MyDatabase"  

To configure a server instance for Always On Availability Groups

  • [Enable and Disable Always On Availability Groups (SQL Server)](../Topic/Enable%20and%20Disable%20Always On%20Availability%20Groups%20(SQL%20Server).md)

  • [Create a Database Mirroring Endpoint for Always On Availability Groups (SQL Server PowerShell)](../Topic/Create%20a%20Database%20Mirroring%20Endpoint%20for%20Always On%20Availability%20Groups%20(SQL%20Server%20PowerShell).md)

To configure availability group and replica properties

To complete availability group configuration

Alternative ways to create an availability group

To troubleshoot Always On Availability Groups configuration

  • [Troubleshoot Always On Availability Groups Configuration (SQL Server)](../Topic/Troubleshoot%20Always On%20Availability%20Groups%20Configuration%20(SQL%20Server).md)

  • [Troubleshoot a Failed Add-File Operation (Always On Availability Groups)](../Topic/Troubleshoot%20a%20Failed%20Add-File%20Operation%20(Always On%20Availability%20Groups).md)

The Database Mirroring Endpoint (SQL Server)
[Overview of Always On Availability Groups (SQL Server)](../Topic/Overview%20of%20Always On%20Availability%20Groups%20(SQL%20Server).md)

Community Additions

ADD
Show: