Export (0) Print
Expand All

Creating an Availability Group (SQL Server)

[This documentation is for preview only, and is subject to change in later releases. Blank topics are included as placeholders.]

This topic describes how to create and configure a "HADR" availability group by using SQL Server Management Studio, Transact-SQL, or PowerShell. 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. For more information about availability groups, see "HADR" Overview (SQL Server).

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

Prerequisites and Restrictions

For more information, see "HADR" Prerequisites and Restrictions.

Recommendations

  • We recommend that, if possible, the file path (including the drive letter) of the secondary database be identical to the path of the corresponding primary database. If the file paths differ, for example, if the corresponding primary database is on drive 'F:' but the computer of the secondary replica location lacks an F: drive, you must restore the database files to the new file path. This requires restoring the database manually, specifying the target path for each of the database files.

    To restore a database to a new location

  • For a given availability group, all of the availability replicas should run on comparable systems that can handle identical workloads.

  • For best performance, use a dedicated network adapter (network interface card) for "HADR".

  • The New Availability Group wizard (of SQL Server Management Studio requires) that you provide a network share to hold your backups (that is, a backup share), and using a backup share for deploying secondary databases is generally a best practice. For more information, see "HADR" Prerequisites and Restrictions.

Security

Permissions

Requires CREATE AVAILABILITY GROUP server, CONTROL AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.

[Top]

In most cases, you can use the New Availability Group wizard to complete all of the tasks require to create and configure an availability group. However, you might need to complete some of the tasks manually in the following situations:

  • If a server instance that you select to host an availability replica does not yet have a database mirroring endpoint, the wizard can create the endpoint if the server instance is running under a domain service account. However, if the SQL Server service is running as a built-in account, such as Local System, Local Service, or Network Service, or a nondomain account, you must use certificates for endpoint authentication. In this case, we recommend that you create the database mirroring endpoints before you launch the New Availability Group wizard. For more information, see "HADR" Prerequisites and Restrictions.

  • Generally, you can use the wizard to start data synchronization. If you do, the wizard automatically backs up each primary database and its transaction log, restores these backups to the secondary replica location using RESTORE WITH NORECOVERY, and joins each secondary database to the availability group. However, using the New Availability Group wizard to start data synchronization is only possible only under following conditions:

    • All the database-file paths must be identical on the primary and secondary replica locations, and

    • No secondary databases can exist yet for the new availability group.

      Tip Tip

      If you have manually prepared any of the secondary databases for an availability group that you are going to create, you have the option of deleting them and then using the New Availability Group wizard to start data synchronization.

    For more information, see Starting Data Synchronization Manually, later in this section.

To use the New Availability Group wizard to create a new availability group

  1. In Object Explorer, connect to the server instance that hosts the primary availability replica, and expand the server tree.

  2. To launch the New Availability Group Wizard, expand the Management node, right-click the Availability Groups node, and click New Availability Group.

  3. The first time you run this wizard, an Introduction page appears. To bypass this page in the future, you can click Do not show this page again. After reading this page, click Next.

  4. On the Specify Availability Group Name page, enter the name of the new availability group in the Availability group name field. This name must be a valid SQL Server identifier that is unique on the WSFC failover cluster and in your domain as a whole.

  5. On the Select Databases page, the User databases meeting high-availability requirements grid lists local user databases that are eligible to become the availability databases for the new availability group. Select one or more of the listed databases to participate as availability databases in the availability group. These local availability databases will initially be the primary databases of the new availability group.

    Optionally, to see all local user databases, including those that are not currently eligible for the new availability group, click Show user databases not meeting requirements. For each listed database, the Comments column explains why the database is not currently eligible, for example, if it does not use the full recovery model. If you change a database to make it eligible, click Refresh to update the User databases meeting high-availability requirements grid.

    For information about the prerequisites for a database to be eligible to be added to an availability group, see "HADR" Prerequisites and Restrictions.

  6. The Specify Replicas page contains two tabs, the Replicas tab and the Endpoints tab, which are as follows:

    • On the Replicas tab, the Selected instances grid initially displays only the instance of SQL Server to which you are connected. This server instance will host the initial primary replica. To specify the server instance that will host the secondary replica, click Add. Note that in CTP1, you must add a single secondary replica now.

      Note Note

      To change the location of the specified secondary replica, click Remove. and then click Add again to select a different secondary location.

      For each selected server instance, the Selected instances grid contains the following columns:

      Column

      Description

      Replica Location

      Displays the name of the server instance that will host the availability replica.

      Read Mode in Secondary Role

      Specifies whether the availability databases on this replica location will be readable when the availability replica is serving as a secondary replica (performing the secondary role).

      Select one of the following values from the drop-down list:

      Value Description
      Disallow Connections No direct connections are allowed to secondary databases of this replica. They are not available for read access.
      Allow Only Read Intent Connections Only direct read-only connections are allowed to secondary databases of this replica. The secondary database(s) are all available for read access.
      Allow All Connections All connections are allowed to secondary databases of this replica, but only for read access. The secondary database(s) are all available for read access.

      Initial Role

      Indicates the role that the new replica will initially perform: Primary or Secondary.

    • For each server instance that is selected as a replica location, the Endpoints tab displays actual values of the existing database mirroring endpoint, if any, or suggested values for a potential new endpoint that would use Windows Authentication. For both existing and potential endpoints, the Endpoint values grid displays the following information:

      Column

      Description

      Replica Location

      The instance of SQL Server that will host the new availability replica.

      Endpoint URL

      The URL of the endpoint. For information the format of these URLs, see Specifying the Endpoint URL When Adding or Modifying a "HADR" Availability Replica (SQL Server).

      Port Number

      The port number of the endpoint.

      Endpoint Name

      The name of the endpoint.

      Encrypt Data

      Whether to encrypt data sent over the endpoint.

      SQL Server Service Account

      Username of the SQL Server service account.

      For a server instance to use an endpoint that uses Windows Authentication, its SQL Server service account must be a domain account. This requirement determines your next configuration step, as follows:

      • If every server instance is running under a domain service account, that is, if the SQL Server Service Account column displays a domain service account for every server instance, click Next.

      • If any server instance is running under a non-domain service account, you need to do make a manual change to your server instance before you can proceed in the wizard. In this case, clicking Next brings up a warning dialog box; you should click No, which returns you to the Endpoints tab. While leaving the wizard on the Specify Replicas page, make one of the following changes to each server instance for which the SQL Server Service Account column displays a nondomain service account, either:

        When you are ready to continue creating the availability group, return to the New Availability Group wizard. On the Endpoints tab of the Specify Availability Replicas page, and to update the Endpoints values grid by clicking Refresh. Then click Next.

  7. On the Summary page, review your choices for the new availability group. To make a change, click Previous to return to the relevant page. After making the change, click Next to return to the Summary page. When you are satisfied with your selections, click Finish.

  8. The Progress page displays the progress of the steps for creating the availability group (configuring endpoints, creating the availability group, and joining the secondary replica to the group).

  9. When these steps complete, the Results page displays the result of each step. If all these steps succeed, no secondary databases exist yet, and the file paths are identical on every server instance that hosts one of the availability replicas, click Start Data Synchronization. This will automatically finish configuring the availability group.

    Important note Important

    The following restrictions exist for using the New Availability Group wizard to start data synchronization:

    • If the file paths on the secondary replica location from the file paths on the primary location, click Close to exit the New Availability Group wizard now and then start data synchronization manually.

    • If any secondary database already exists, using the New Availability Group wizard to start data synchronization requires manually deleting these secondary databases before you click Start Data Synchronization. If want to use your existing secondary databases, click Close to exit the New Availability Group wizard now and then start data synchronization manually.

    For more information, see Starting Data Synchronization Manually, later in this section.

    If you have clicked Start Data Synchronization the Start Data Synchronization page opens. This page requires a network share (backup share). Either browse for your backup share, or enter its fully qualified universal naming convention (UNC) path name, \\Systemname\ShareName\Path\, in the Specify a shared network location for backups field. Optionally, click Test to verify the path.

    Note Note

    For information about the requirements for this backup share, see "HADR" Prerequisites and Restrictions.

    For each database in the availability group, the Start Data Synchronization page displays the progress of the following operations:

    1. Creating a full database backup of the primary database on the network share.

    2. Creating a log backup (which will be part of the backup log chain) on the network share.

    3. Restoring these backups onto the secondary replica location. These restore operations both use RESTORE WITH NORECOVERY, leaving the new secondary database in the RESTORING state.

    4. Joining the secondary database to the availability group. This step puts the secondary database in to the ONLINE state, and starts data synchronization for this database.

Starting Data Synchronization Manually

If file paths differ between server instances or you want to use any existing secondary databases, you must manually prepare any secondary databases that do not yet exist and join every secondary database to the availability group.

To back up databases and transaction logs

To restore databases and transaction logs

In CTP1 SQL Server Management Studio does not support manually joining a secondary database, but you can use either Transact-SQL or PowerShell. For more information, see Joining a Secondary Database to an Availability Group (SQL Server).

To configure a new availability group

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

Important note Important

For a sample configuration procedure containing code examples of each these Transact-SQL statements, see Example: Setting Up an Availability Group Using Windows Authentication (Transact-SQL).

Task

Transact-SQL Statement(s)

Where to Perform Task*

Create database mirroring endpoint

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 availability replica (the primary location).

Join secondary replica to availability group

ALTER AVAILABILITY GROUP group_name JOIN

Execute on the server instance that is to host the initial secondary availability replica (the secondary location).

Prepare the secondary database

BACKUP and RESTORE.

Create backups on the primary location.

Restore backups on the secondary location, using RESTORE WITH NORECOVERY.

Join secondary databases to availability group

ALTER DATABASE database_name SET HADR AVAILABILITY GROUP = group_name

Execute on the secondary location.

*  To perform a given task, connect to the indicated server instance (replica location).

[Top]

To configure an availability group

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

Note 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 SQL Server PowerShell Help.

Task

PowerShell Cmdlets (if Available) or Transact-SQL Statement

Where to Perform Task*

Create database mirroring endpoint

Transact-SQL statement: CREATE ENDPOINT endpointName … FOR DATABASE_MIRRORING

Execute on each server instance that lacks database mirroring endpoint.

Create availability group

In CTP1, you must 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 availability replica (the primary location).

Join secondary replica to availability group

Join-SqlAvailabilityGroup

Execute on the server instance that is to host the initial secondary availability replica (the secondary location).

Prepare the secondary database

In CTP1, PowerShell does not support backup and restore operations. For information about using Transact-SQL to prepare secondary databases, see Example: Setting Up an Availability Group Using Windows Authentication (Transact-SQL), starting with the step that restores the database backup.

Create backups on the primary location.

Restore backups on the secondary location, using RESTORE WITH NORECOVERY.

Join secondary databases to availability group

Add-SqlAvailabilityGroupDatabase

Execute on the secondary location.

*  To perform a given task, set default (cd) to the indicated location or locations.

Tip Tip

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

To set up and use the SQL Server PowerShell provider

[Top]

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

Show:
© 2014 Microsoft