ALTER DATABASE SET HADR (Transact-SQL)

This topic contains the ALTER DATABASE syntax for setting AlwaysOn Availability Groups options on a secondary database. Only one SET HADR option is permitted per ALTER DATABASE statement. These options are supported only on secondary replicas. 

Transact-SQL Syntax Conventions

Syntax

ALTER DATABASE database_name
   SET HADR 
   {
        { AVAILABILITY GROUP = group_name | OFF }
   | { SUSPEND | RESUME }
   }
;

Arguments

• database_name
  Is the name of the secondary database to be modified.

• SET HADR
  Executes the specified AlwaysOn Availability Groups command on the specified database.

• { AVAILABILITY GROUP **=**group_name | OFF }
  Joins or removes the availability database from the specified availability group, as follows:

  • group_name
    Joins the specified database on the secondary replica that is hosted by the server instance on which you execute the command to the availability group specified by group_name.

    The prerequisites for this operation are as follows:

    • The database must already have been added to the availability group on the primary replica.

    • The primary replica must be active. For information about how troubleshoot an inactive primary replica, see Troubleshooting AlwaysOn Availability Groups Configuration (SQL Server).

    • The primary replica must be online, and the secondary replica must be connected to the primary replica.

    • The secondary database must have been restored using WITH NORECOVERY from recent database and log backups of the primary database, ending with a log backup that is recent enough to permit the secondary database to catch up to the primary database.

      Note

      To add a database to the availability group, connect to the server instance that hosts the primary replica, and use the ALTER AVAILABILITY GROUP group_name ADD DATABASE database_name statement.

    For more information, see Join a Secondary Database to an Availability Group (SQL Server).

  • OFF
    Removes the specified secondary database from the availability group.

    Removing a secondary database can be useful if it has fallen far behind the primary database, and you do not want to wait for the secondary database to catch up. After removing the secondary database, you can update it by restoring a sequence of backups ending with a recent log backup (using RESTORE … WITH NORECOVERY).

    Important

    To completely remove an availability database from an availability group, connect to the server instance that hosts the primary replica, and use the ALTER AVAILABILITY GROUP group_name REMOVE DATABASE availability_database_name statement. For more information, see Remove a Primary Database from an Availability Group (SQL Server).

• SUSPEND
  Suspends data movement on a secondary database. A SUSPEND command returns as soon as it has been accepted by the replica that hosts the target database, but actually suspending the database occurs asynchronously.

  The scope of the impact depends on where you execute the ALTER DATABASE statement:

  • If you suspend a secondary database on a secondary replica, only the local secondary database is suspended. Existing connections on the readable secondary remain usable. New connections to the suspended database on the readable secondary are not allowed until data movement is resumed.

  • If you suspend a database on the primary replica, data movement is suspended to the corresponding secondary databases on every secondary replica. Existing connections on a readable secondary remain usable and new connections can be made.

  • When data movement is suspended due to a forced manual failover, connections to the new secondary replica are not allowed while data movement is suspended.

  When a database on a secondary replica is suspended, both the database and replica become unsynchronized and are marked as NOT SYNCHRONIZED.

  Important

  While a secondary database is suspended, the send queue of the corresponding primary database will accumulate unsent transaction log records. Connections to the secondary replica return data that was available at the time the data movement was suspended.

  Note

  Suspending and resuming an AlwaysOn secondary database does not directly affect the availability of the primary database, though suspending a secondary database can impact redundancy and failover capabilities for the primary database, until the suspended secondary database is resumed. This is in contrast to database mirroring, where the mirroring state is suspended on both the mirror database and the principal database until mirroring is resumed. Suspending an AlwaysOn primary database suspends data movement on all the corresponding secondary databases, and redundancy and failover capabilities cease for that database until the primary database is resumed.

  For more information, see Suspend an Availability Database (SQL Server).

• RESUME
  Resumes suspended data movement on the specified secondary database. A RESUME command returns as soon as it has been accepted by the replica that hosts the target database, but actually resuming the database occurs asynchronously.

  The scope of the impact depends on where you execute the ALTER DATABASE statement:

  • If you resume a secondary database on a secondary replica, only the local secondary database is resumed. Data movement is resumed unless the database has also been suspended on the primary replica.

  • If you resume a database on the primary replica, data movement is resumed to every secondary replica on which the corresponding secondary database has not also been suspended locally. To resume a secondary database that was individually suspended on a secondary replica, connect to the server instance that hosts the secondary replica and resume the database there.

    Under synchronous-commit mode, the database state changes to SYNCHRONIZING. If no other database is currently suspended, the replica state also changes to SYNCHRONIZING.

    For more information, see Resume an Availability Database (SQL Server).

Database States

When a secondary database is joined to an availability group, the local secondary replica changes the state of that secondary database from RESTORING to ONLINE. If a secondary database is removed from the availability group, it is set back to the RESTORING state by the local secondary replica. This allows you to apply subsequent log backups from the primary database to that secondary database.

Restrictions

Execute ALTER DATABASE statements outside of both transactions and batches.

Security

Permissions

Requires ALTER permission on the database. Joining a database to an availability group requires membership in the db_owner fixed database role.

Examples

The following example joins the secondary database, AccountsDb1, to the local secondary replica of the AccountsAG availability group.

ALTER DATABASE AccountsDb1 SET HADR AVAILABILITY GROUP = AccountsAG;

Note

To see this Transact-SQL statement used in context, see Create an Availability Group (Transact-SQL).

See Also

Reference

ALTER DATABASE (Transact-SQL)

ALTER AVAILABILITY GROUP (Transact-SQL)

CREATE AVAILABILITY GROUP (Transact-SQL)

Concepts

Overview of AlwaysOn Availability Groups (SQL Server)

Troubleshoot AlwaysOn Availability Groups Configuration (SQL Server)