Share via


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 2012 on which the AlwaysOn 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.

Note

For an introduction to availability groups, see Overview of AlwaysOn Availability Groups (SQL Server).

  • Before you begin:  

    Prerequisites

    Security

    Summary of Tasks and Corresponding Transact-SQL Statements

  • To create and configure an availability group, using:  Transact-SQL

  • **Example: ** Configuring an Availability Group that Uses Windows Authentication

  • Related Tasks

  • Related Content

Note

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

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 AlwaysOn 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.

Arrow icon used with Back to Top link[Top]

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 AlwaysOn Availability Groups tasks must be performed in the sequence in which they are presented in the table.

Task

Transact-SQL Statement(s)

Where to Perform Task*

Create database mirroring endpoint (once per SQL Server instance)

CREATE ENDPOINT endpointName … FOR DATABASE_MIRRORING

Execute on each server instance that lacks database mirroring endpoint.

Create availability group

CREATE AVAILABILITY GROUP

Execute on the server instance that is to host the initial primary replica.

Join secondary replica to availability group

ALTER AVAILABILITY GROUP group_name JOIN

Execute on each server instance that hosts a secondary replica.

Prepare the secondary database

BACKUP and RESTORE.

Create backups on the server instance that hosts the primary replica.

Restore backups on each server instance that hosts a secondary replica, using RESTORE WITH NORECOVERY.

Start data synchronization by joining each secondary database to availability group

ALTER DATABASE database_name SET HADR AVAILABILITY GROUP = group_name

Execute on each server instance that hosts a secondary replica.

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

Arrow icon used with Back to Top link[Top]

Using Transact-SQL to Create and Configure an Availability Group

Note

For a sample configuration procedure containing code examples of each these Transact-SQL statements, see Example: Configuring an Availability Group that Uses Windows Authentication.

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

  2. Create the availability group by using the CREATE AVAILABILITY GROUP Transact-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 Create an Availability Group (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).

Arrow icon used with Back to Top link[Top]

Example: Configuring an Availability Group that Uses Windows Authentication

This example creates a sample AlwaysOn 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 AlwaysOn 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
      

[TopOfExample]

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.

Initial role

System

Host SQL Server Instance

Primary

COMPUTER01

AgHostInstance

Secondary

COMPUTER02

Default instance.

  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
    

[TopOfExample]

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.

Placeholder

Description

\\FILESERVER\SQLbackups

Fictional backup share.

\\FILESERVER\SQLbackups\MyDb1.bak

Backup file for MyDb1.

\\FILESERVER\SQLbackups\MyDb2.bak

Backup file for MyDb2.

7022

Port number assigned to each database mirroring endpoint.

COMPUTER01\AgHostInstance

Server instance that hosts the initial primary replica.

COMPUTER02

Server instance that hosts the initial secondary replica. This is the default server instance on COMPUTER02.

dbm_endpoint

Name specified for each database mirroring endpoint.

MyAG

Name of sample availability group.

MyDb1

Name of first sample database.

MyDb2

Name of second sample database.

DOMAIN1\user1

Service account of the server instance that is to host the initial primary replica.

DOMAIN2\user2

Service account of the server instance that is to host the initial secondary replica.

TCP://COMPUTER01.Adventure-Works.com:7022

Endpoint URL of the AgHostInstance instance of SQL Server on COMPUTER01.

TCP://COMPUTER02.Adventure-Works.com:5022

Endpoint URL of the default instance of SQL Server on COMPUTER02.

Note

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

-- 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

Arrow icon used with Back to Top link[TopOfExample]

To configure availability group and replica properties

To complete availability group configuration

Alternative ways to create an availability group

To enable AlwaysOn Availability Groups

To configure a database mirroring endpoint

To troubleshoot AlwaysOn Availability Groups configuration

Arrow icon used with Back to Top link[Top]

Arrow icon used with Back to Top link[Top]

See Also

Concepts

The Database Mirroring Endpoint (SQL Server)

Overview of AlwaysOn Availability Groups (SQL Server)

Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server)

Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server)