Skip to content

Latest commit

 

History

History
57 lines (38 loc) · 5.18 KB

transact-sql-statements-for-always-on-availability-groups.md

File metadata and controls

57 lines (38 loc) · 5.18 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic helpviewer_keywords
Transact-SQL statements for availability groups
Introduces Transact-SQL (T-SQL) statements that support deploying, creating, and managing Always On availability groups.
MashaMSFT
mathoma
05/17/2016
sql
availability-groups
reference
Availability Groups [SQL Server], about
Availability Groups [SQL Server], Transact-SQL statements

Transact-SQL statements for Always On availability groups

[!INCLUDE SQL Server]

This topic introduces the [!INCLUDEtsql] statements that support deploying [!INCLUDEssHADR] and creating and managing an given availability group, availability replica and availability database.

CREATE ENDPOINT

CREATE ENDPOINT ... FOR DATABASE_MIRRORING creates a database mirroring endpoint, if none exists on the server instance. Every server instance on which you intend to deploy [!INCLUDEssHADR] or database mirroring requires a database mirroring endpoint.

Execute this statement on the server instance on which you are creating the endpoint. You can create only one database mirroring endpoint on a given server instance. For more information, see The Database Mirroring Endpoint (SQL Server).

CREATE AVAILABILITY GROUP

CREATE AVAILABILITY GROUP creates a new availability group and optionally an availability group listener. Minimally, you must specify your local server instance, which will become the initial primary replica. Optionally, you can also specify up to four secondary replicas.

Execute CREATE AVAILABILITY GROUP on the instance of [!INCLUDEssNoVersion] that you want to host the initial primary replica of your new availability group. This server instance must reside on a node of a Windows Server Failover Cluster (WSFC) (for more information, see Prerequisites, Restrictions, and Recommendations for Always On Availability Groups (SQL Server).

ALTER AVAILABILITY GROUP

ALTER AVAILABILITY GROUP supports changing an existing availability group or availability group listener and for failing over an availability group.

Execute ALTER AVAILABILITY GROUP on the instance of [!INCLUDEssNoVersion] that hosts the current primary replica.

ALTER DATABASE ... SET HADR ...

The options of the SET HADR clause of the ALTER DATABASE statement enables you to join a secondary database to the availability group of the corresponding primary database, remove a joined database, and suspend data synchronization on a joined database, and resume data synchronization.

DROP AVAILABILITY GROUP

DROP AVAILABILITY GROUP removes a specified availability group and all of its replicas. DROP AVAILABILITY GROUP can be run from any [!INCLUDEssHADR] node in the WSFC failover cluster.

Restrictions on the AVAILABILITY GROUP Transact-SQL Statements

The CREATE AVAILABILITY GROUP, ALTER AVAILABILITY GROUP, and DROP AVAILABILITY GROUP [!INCLUDEtsql] statements have the following limitations:

  • With the exception of DROP AVAILABILITY GROUP, executing these statements requires that the HADR service is enabled on the instance of [!INCLUDEssNoVersion]. For more information, see Enable and Disable Always On Availability Groups (SQL Server).

  • These statements cannot be executed within transactions or batches.

  • Though they make a best effort to clean up after a failure, these statements do not guarantee that they will roll back all changes on failure. However, systems should be able cleanly handle and then ignore partial failures.

  • These statements do not support expressions or variables.

  • If a [!INCLUDEtsql] statement is executed while another availability group action or recovery is in process, the statement returns an error. Wait for the action or recovery to complete, and retry the statement, if necessary.

See Also

Overview of Always On Availability Groups (SQL Server)