Create an Availability Group (Transact-SQL)

This topic describes how to use Transact-SQL to create and configure an availability group on instances of SQL Server 2014 on which the Always On Availability Groups feature is enabled. 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, that support failover.

Before You Begin

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 instances of SQL Server that host availability replicas reside on different Windows Server Failover Clustering (WSFC) node within the same WSFC failover cluster. Also, verify that each of the server instance meets all other Always On Availability Groups prerequisites. For more information, we strongly recommend that you read Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server).

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 Transact-SQL Statements

The following table lists the basic tasks involved in creating and configuring an availability group and indicates which Transact-SQL statements to use for these tasks. The Always On Availability Groups tasks must be performed in the sequence in which they are presented in the table.

^* To perform a given task, connect to the indicated server instance or instances.

Using Transact-SQL to Create and Configure an Availability Group

1. Connect to the server instance that is to host the primary replica.

2. Create the availability group by using the CREATE AVAILABILITY GROUPTransact-SQL statement.

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

4. For each database in the availability group, create a secondary database by restoring recent backups of the primary database, using RESTORE WITH NORECOVERY. For more information, see Example: Setting Up an Availability Group Using Windows Authentication (Transact-SQL), starting with the step that restores the database backup.

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

Example: Configuring an Availability Group that Uses Windows Authentication

This example creates a sample Always On Availability Groups configuration procedure that uses Transact-SQL to set up database mirroring endpoints that use Windows Authentication and to create and configure an availability group and its secondary databases.

This example contains the following sections:

• Prerequisites for Using the Sample Configuration Procedure

• Sample Configuration Procedure

• Complete Code Example for Sample Configuration Procedure

Prerequisites for Using the Sample Configuration Procedure

This sample procedure has the following requirements:

• The server instances must support Always On Availability Groups. For more information, see Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server).

• Two sample databases, MyDb1 and MyDb2, must exist on the server instance that will host the primary replica. The following code examples create and configure these two databases and create a full backup of each. Execute these code examples on the server instance on which you intend to create the sample availability group. This server instance will host the initial primary replica of the sample availability group.

  1. The following Transact-SQL example creates these databases and alters them to use the full recovery model:

     -- Create sample databases:  
     CREATE DATABASE MyDb1;  
     GO  
     ALTER DATABASE MyDb1 SET RECOVERY FULL;  
     GO  
     
     CREATE DATABASE MyDb2;  
     GO  
     ALTER DATABASE MyDb2 SET RECOVERY FULL;  
     GO  
     

  2. The following code example creates a full database backup of MyDb1 and MyDb2. This code example uses a fictional backup share, \\FILESERVER\SQLbackups.

     -- Backup sample databases:  
     BACKUP DATABASE MyDb1   
     TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
         WITH FORMAT  
     GO  
     
     BACKUP DATABASE MyDb2   
     TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
         WITH FORMAT  
     GO
     

Sample Configuration Procedure

In this sample configuration, the availability replica will be created on two stand-alone server instances whose service accounts run under different, but trusted, domains (DOMAIN1 and DOMAIN2).

The following table summarizes the values used in this sample configuration.

1. Create a database mirroring endpoint named dbm_endpoint on the server instance on which you plan to create the availability group (this is an instance named AgHostInstance on COMPUTER01). This endpoint uses port 7022. Note that the server instance on which you create the availability group will host the primary replica.

   -- Create endpoint on server instance that hosts the primary replica:  
   CREATE ENDPOINT dbm_endpoint  
       STATE=STARTED   
       AS TCP (LISTENER_PORT=7022)   
       FOR DATABASE_MIRRORING (ROLE=ALL)  
   GO
   

2. Create an endpoint dbm_endpoint on the server instance that will host the secondary replica (this is the default server instance on COMPUTER02). This endpoint uses port 5022.

   -- Create endpoint on server instance that hosts the secondary replica:   
   CREATE ENDPOINT dbm_endpoint  
       STATE=STARTED   
       AS TCP (LISTENER_PORT=5022)   
       FOR DATABASE_MIRRORING (ROLE=ALL)  
   GO
   

3. Note

   If the service accounts of the server instances that are to host your availability replicas run under the same domain account this step is unnecessary. Skip it and go directly to the next step.

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

   The following code example shows the Transact-SQL statements for creating a login and granting it permission on an endpoint. The domain account of the remote server instance is represented here as domain_name\user_name.

   -- If necessary, create a login for the service account, domain_name\user_name  
   -- of the server instance that will host the other replica:  
   USE master;  
   GO  
   CREATE LOGIN [domain_name\user_name] FROM WINDOWS;  
   GO  
   -- And Grant this login connect permissions on the endpoint:  
   GRANT CONNECT ON ENDPOINT::dbm_endpoint   
      TO [domain_name\user_name];  
   GO  
   

4. On the server instance where the user databases reside, create the availability group.

   The following code example creates an availability group named MyAG on the server instance on which the sample databases, MyDb1 and MyDb2, were created. The local server instance, AgHostInstance, on COMPUTER01 is specified first. This instance will host the initial primary replica. A remote server instance, the default server instance on COMPUTER02, is specified to host a secondary replica. Both availability replica are configured to use asynchronous-commit mode with manual failover (for asynchronous-commit replicas manual failover means forced failover with possible data loss).

   -- Create the availability group, MyAG:   
   CREATE AVAILABILITY GROUP MyAG   
      FOR   
         DATABASE MyDB1, MyDB2   
      REPLICA ON   
         'COMPUTER01\AgHostInstance' WITH   
            (  
            ENDPOINT_URL = 'TCP://COMPUTER01.Adventure-Works.com:7022',   
            AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,  
            FAILOVER_MODE = MANUAL  
            ),  
         'COMPUTER02' WITH   
            (  
            ENDPOINT_URL = 'TCP://COMPUTER02.Adventure-Works.com:5022',  
            AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,  
            FAILOVER_MODE = MANUAL  
            );   
   GO  
   

   For additional Transact-SQL code examples of creating an availability group, see CREATE AVAILABILITY GROUP (Transact-SQL).

5. On the server instance that hosts the secondary replica, join the secondary replica to the availability group.

   The following code example joins the secondary replica on COMPUTER02 to the MyAG availability group.

   -- On the server instance that hosts the secondary replica,   
   -- join the secondary replica to the availability group:  
   ALTER AVAILABILITY GROUP MyAG JOIN;  
   GO  
   

6. On the server instance that hosts the secondary replica, create the secondary databases.

   The following code example creates the MyDb1 and MyDb2 secondary databases by restoring database backups using RESTORE WITH NORECOVERY.

   -- On the server instance that hosts the secondary replica,   
   -- Restore database backups using the WITH NORECOVERY option:  
   RESTORE DATABASE MyDb1   
       FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
       WITH NORECOVERY  
   GO  
   
   RESTORE DATABASE MyDb2   
       FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
       WITH NORECOVERY  
   GO
   

7. On the server instance that hosts the primary replica, back up the transaction log on each of the primary databases.

   Important

   When you are configuring a real availability group, we recommend that, before taking this log backup, you suspend log backup tasks for your primary databases until you have joined the corresponding secondary databases to the availability group.

   The following code example creates a transaction log backup on MyDb1 and on MyDb2.

   -- On the server instance that hosts the primary replica,   
   -- Backup the transaction log on each primary database:  
   BACKUP LOG MyDb1   
   TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
       WITH NOFORMAT  
   GO  
   
   BACKUP LOG MyDb2   
   TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
       WITHNOFORMAT  
   GO
   

   Tip

   Typically, a log backup must be taken on each primary database and then restored on the corresponding secondary database (using WITH NORECOVERY). However, this log backup might be unnecessary if the database has just been created and no log backup has been taken yet or the recovery model has just been changed from SIMPLE to FULL.

8. On the server instance that hosts the secondary replica, apply log backups to the secondary databases.

   The following code example applies backups to MyDb1 and MyDb2 secondary databases by restoring database backups using RESTORE WITH NORECOVERY.

   Important

   When you are preparing a real secondary database, you need to apply every log backup taken since the database backup from which you created the secondary database, starting with the earliest and always using RESTORE WITH NORECOVERY. Of course, if you restore both full and differential database backups, you would only need to apply the log backups taken after the differential backup.

   -- Restore the transaction log on each secondary database,  
   -- using the WITH NORECOVERY option:  
   RESTORE LOG MyDb1   
       FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
       WITH FILE=1, NORECOVERY  
   GO  
   RESTORE LOG MyDb2   
       FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
       WITH FILE=1, NORECOVERY  
   GO  
   

9. On the server instance that hosts the secondary replica, join the new secondary databases to the availability group.

   The following code example, joins the MyDb1 secondary database and then the MyDb2 secondary databases to the MyAG availability group.

   -- On the server instance that hosts the secondary replica,   
   -- join each secondary database to the availability group:  
   ALTER DATABASE MyDb1 SET HADR AVAILABILITY GROUP = MyAG;  
   GO  
   
   ALTER DATABASE MyDb2 SET HADR AVAILABILITY GROUP = MyAG;  
   GO
   

Complete Code Example for Sample Configuration Procedure

The following example merges the code examples from all the steps of the sample configuration procedure. The following table summarized the placeholder values used in this code example. For more information about the steps in this code example, see Prerequisites for Using the Sample Configuration Procedure and Sample Configuration Procedure, earlier in this topic.

-- on the server instance that will host the primary replica,   
-- create sample databases:  
CREATE DATABASE MyDb1;  
GO  
ALTER DATABASE MyDb1 SET RECOVERY FULL;  
GO  
  
CREATE DATABASE MyDb2;  
GO  
ALTER DATABASE MyDb2 SET RECOVERY FULL;  
GO  
  
-- Backup sample databases:  
BACKUP DATABASE MyDb1   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH FORMAT  
GO  
  
BACKUP DATABASE MyDb2   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITH FORMAT  
GO  
  
-- Create the endpoint on the server instance that will host the primary replica:  
CREATE ENDPOINT dbm_endpoint  
    STATE=STARTED   
    AS TCP (LISTENER_PORT=7022)   
    FOR DATABASE_MIRRORING (ROLE=ALL)  
GO  
  
-- Create the endpoint on the server instance that will host the secondary replica:   
CREATE ENDPOINT dbm_endpoint  
    STATE=STARTED   
    AS TCP (LISTENER_PORT=7022)   
    FOR DATABASE_MIRRORING (ROLE=ALL)  
GO  
  
-- If both service accounts run under the same domain account, skip this step. Otherwise,   
-- On the server instance that will host the primary replica,   
-- create a login for the service account   
-- of the server instance that will host the secondary replica, DOMAIN2\user2,   
-- and grant this login connect permissions on the endpoint:  
USE master;  
GO  
CREATE LOGIN [DOMAIN2\user2] FROM WINDOWS;  
GO  
GRANT CONNECT ON ENDPOINT::dbm_endpoint   
   TO [DOMAIN2\user2];  
GO  
  
-- If both service accounts run under the same domain account, skip this step. Otherwise,   
-- On the server instance that will host the secondary replica,  
-- create a login for the service account   
-- of the server instance that will host the primary replica, DOMAIN1\user1,   
-- and grant this login connect permissions on the endpoint:  
USE master;  
GO  
  
CREATE LOGIN [DOMAIN1\user1] FROM WINDOWS;  
GO  
GRANT CONNECT ON ENDPOINT::dbm_endpoint   
   TO [DOMAIN1\user1];  
GO  
  
-- On the server instance that will host the primary replica,   
-- create the availability group, MyAG:  
CREATE AVAILABILITY GROUP MyAG   
   FOR   
      DATABASE MyDB1, MyDB2   
   REPLICA ON   
      'COMPUTER01\AgHostInstance' WITH   
         (  
         ENDPOINT_URL = 'TCP://COMPUTER01.Adventure-Works.com:7022',  
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  
         FAILOVER_MODE = AUTOMATIC  
         ),  
      'COMPUTER02' WITH   
         (  
         ENDPOINT_URL = 'TCP://COMPUTER02.Adventure-Works.com:7022',  
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  
         FAILOVER_MODE = AUTOMATIC  
         );   
GO  
  
-- On the server instance that hosts the secondary replica,   
-- join the secondary replica to the availability group:  
ALTER AVAILABILITY GROUP MyAG JOIN;  
GO  
  
-- Restore database backups onto this server instance, using RESTORE WITH NORECOVERY:  
RESTORE DATABASE MyDb1   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH NORECOVERY  
GO  
  
RESTORE DATABASE MyDb2   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITH NORECOVERY  
GO  
  
-- Back up the transaction log on each primary database:  
BACKUP LOG MyDb1   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH NOFORMAT  
GO  
  
BACKUP LOG MyDb2   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITHNOFORMAT  
GO  
  
-- Restore the transaction log on each secondary database,  
-- using the WITH NORECOVERY option:  
RESTORE LOG MyDb1   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH FILE=1, NORECOVERY  
GO  
RESTORE LOG MyDb2   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITH FILE=1, NORECOVERY  
GO  
  
-- On the server instance that hosts the secondary replica,   
-- join each secondary database to the availability group:  
ALTER DATABASE MyDb1 SET HADR AVAILABILITY GROUP = MyAG;  
GO  
  
ALTER DATABASE MyDb2 SET HADR AVAILABILITY GROUP = MyAG;  
GO

Related Tasks

To configure availability group and replica properties

• Change the Availability Mode of an Availability Replica (SQL Server)

• Change the Failover Mode of an Availability Replica (SQL Server)

• Create or Configure an Availability Group Listener (SQL Server)

• Configure the Flexible Failover Policy to Control Conditions for Automatic Failover (AlwaysOn Availability Groups)

• Specify the Endpoint URL When Adding or Modifying an Availability Replica (SQL Server)

• Configure Backup on Availability Replicas (SQL Server)

• Configure Read-Only Access on an Availability Replica (SQL Server)

• Configure Read-Only Routing for an Availability Group (SQL Server)

• Change the Session-Timeout Period for an Availability Replica (SQL Server)

To complete availability group configuration

• Join a Secondary Replica to an Availability Group (SQL Server)

• Manually Prepare a Secondary Database for an Availability Group (SQL Server)

• Join a Secondary Database to an Availability Group (SQL Server)

• Create or Configure an Availability Group Listener (SQL Server)

Alternative ways to create an availability group

• Use the Availability Group Wizard (SQL Server Management Studio)

• Use the New Availability Group Dialog Box (SQL Server Management Studio)

• Create an Availability Group (SQL Server PowerShell)

To enable AlwaysOn Availability Groups

• Enable and Disable AlwaysOn Availability Groups (SQL Server)

To configure a database mirroring endpoint

• Create a Database Mirroring Endpoint for AlwaysOn Availability Groups (SQL Server PowerShell)

• Create a Database Mirroring Endpoint for Windows Authentication (Transact-SQL)

• Use Certificates for a Database Mirroring Endpoint (Transact-SQL)

• Specify the Endpoint URL When Adding or Modifying an Availability Replica (SQL Server)

To troubleshoot AlwaysOn Availability Groups configuration

• Troubleshoot AlwaysOn Availability Groups Configuration (SQL Server)deleted

• Troubleshoot a Failed Add-File Operation (AlwaysOn Availability Groups)

Related Content

• Blogs:

  AlwaysON - HADRON Learning Series: Worker Pool Usage for HADRON Enabled Databases

  SQL Server AlwaysOn Team Blogs: The official SQL Server AlwaysOn Team Blog

  CSS SQL Server Engineers Blogs

• Videos:

  Microsoft SQL Server Code-Named "Denali" AlwaysOn Series,Part 1: Introducing the Next Generation High Availability Solution

  Microsoft SQL Server Code-Named "Denali" AlwaysOn Series,Part 2: Building a Mission-Critical High Availability Solution Using AlwaysOn

• Whitepapers:

  Microsoft SQL Server AlwaysOn Solutions Guide for High Availability and Disaster Recovery

  Microsoft White Papers for SQL Server 2012

  SQL Server Customer Advisory Team Whitepapers

See Also

The Database Mirroring Endpoint (SQL Server)
Overview of AlwaysOn Availability Groups (SQL Server)
Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server)