使用 Transact-SQL (T-SQL) 创建 Always On 可用性组

适用于：SQL Server

本主题介绍如何使用 Transact-SQL 在启用了 Always On 可用性组功能的 SQL Server 实例上创建和配置可用性组。 可用性组定义了一组用户数据库，这些数据库将作为一个整体进行故障转移；还定义了一组支持故障转移的伙伴，称为可用性副本。

注意

有关可用性组的简介，请参阅 Always On 可用性组概述 (SQL Server)。

注意

除了使用 Transact-SQL 之外，还可以使用“创建可用性组”向导或 SQL Server PowerShell cmdlet。有关详细信息，请参阅使用可用性组向导 (SQL Server Management Studio)、使用“新建可用性组”对话框 (SQL Server Management Studio) 或创建可用性组 (SQL Server PowerShell)。

先决条件、限制和建议

创建可用性组之前，请先验证承载可用性副本的 SQL Server 实例位于同一 WSFC 故障转移群集内的不同 Windows Server 故障转移群集 (WSFC) 节点上。此外，还请验证每个服务器实例是否都满足所有其他 Always On 可用性组先决条件。有关详细信息，我们强烈建议你参阅针对 Always On 可用性组的先决条件、限制和建议 (SQL Server)。

权限

要求具有 sysadmin 固定服务器角色的成员身份，并具有以下权限之一：CREATE AVAILABILITY GROUP 服务器权限、ALTER ANY AVAILABILITY GROUP 权限或 CONTROL SERVER 权限。

使用 Transact-SQL 创建和配置可用性组

任务及相应 Transact-SQL 语句摘要

下表列出了涉及创建和配置可用性组的基本任务，并且指出了要用于这些任务的 Transact-SQL 语句。必须按照表中列出的顺序执行 Always On 可用性组任务。

任务	Transact-SQL 语句	执行任务的位置*****
创建数据库镜像端点（每个 SQL Server 实例一次）	CREATE ENDPOINT endpointName ... FOR DATABASE_MIRRORING	在缺少数据库镜像端点的每个服务器实例上执行。
创建可用性组	CREATE AVAILABILITY GROUP	在要承载初始主副本的服务器实例上执行。
将次要副本加入可用性组	ALTER AVAILABILITY GROUP group_name 加入	在承载辅助副本的各服务器实例上执行。
准备辅助数据库	BACKUP 和 RESTORE。	在承载主副本的服务器实例上创建备份。使用 RESTORE WITH NORECOVERY 在托管辅助副本的每个服务器实例上还原备份。
通过将各辅助数据库联接到可用性组，开始数据同步	ALTER DATABASE database_nameSET HADRAVAILABILITY GROUP = group_name	在承载辅助副本的各服务器实例上执行。

*若要执行某个给定任务，请连接到指示的服务器实例。

“使用 Transact-SQL”

注意

有关包含上述各 Transact-SQL 语句的代码示例的配置过程示例，请参阅示例：配置使用 Windows 身份验证的可用性组。

连接到要承载主副本的服务器实例。
使用 CREATE AVAILABILITY GROUPTransact-SQL 语句创建可用性组。
将新的辅助副本加入可用性组。有关详细信息，请参阅将次要副本联接到可用性组 (SQL Server)。
对于可用性组中的每个数据库，请使用 RESTORE WITH NORECOVERY 还原主数据库的最新备份来创建辅助数据库。有关详细信息，请参阅示例：使用 Windows 身份验证设置可用性组 (Transact-SQL)，从还原数据库备份的步骤开始。
将每个新的辅助数据库联接到可用性组。有关更多信息，请参阅将次要副本加入可用性组 (SQL Server)。

示例：配置使用 Windows 身份验证的可用性组

此示例创建一个示例 Always On 可用性组配置过程，该过程使用 Transact-SQL 设置使用 Windows 身份验证的数据库镜像端点并且创建和配置一个可用性组及其辅助数据库。

此示例包含以下部分：

使用示例配置过程的先决条件
示例配置过程
示例配置过程的完整代码示例

使用示例配置过程的先决条件

此示例过程具有以下要求：

服务器实例必须支持 Always On 可用性组。有关详细信息，请参阅针对 Always On 可用性组的先决条件、限制和建议 (SQL Server)。
两个示例数据库 MyDb1 和 MyDb2必须在将承载主副本的服务器实例上存在。下面的代码示例创建和配置这两个数据库，并且创建这两个数据库的完整备份。在您想要创建示例可用性组的服务器实例上执行这些代码示例。此服务器实例将承载示例可用性组的初始主副本。
1. 下面的 Transact-SQL 示例创建这些数据库并且将它们更改为使用完整恢复模式：
```
-- 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. 下面的代码示例创建 MyDb1 和 MyDb2的完整数据库备份。此代码示例使用虚构的备份共享：\\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]

示例配置过程

在这个示例配置中，将在两个独立服务器实例上创建可用性副本，这些服务器实例的服务帐户在不同的可信域（DOMAIN1 和 DOMAIN2）下运行。

下表总结了此示例配置中使用的值。

初始角色	系统	承载 SQL Server 实例
主要	`COMPUTER01`	`AgHostInstance`
次要	`COMPUTER02`	默认实例。

在计划创建可用性组的服务器实例上创建一个名为 dbm_endpoint 的数据库镜像端点（这是 COMPUTER01 上名为 AgHostInstance 的实例）。此端点使用端口 7022。请注意，创建可用性组时所在的服务器实例将托管主副本。
```
-- 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  
```

在将承载辅助副本的服务器实例上创建端点 dbm_endpoint（这是 COMPUTER02 上的默认服务器实例）。此端点使用端口 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

注意

如果要承载您的可用性副本的服务器实例的服务帐户基于相同的域帐户运行，则不需要执行此步骤。跳过它，直接进入下一步。

如果该服务器实例的服务帐户基于不同的域用户运行，则在各服务器实例上，为其他服务器实例创建一个登录名，并且授予此登录名访问本地数据库镜像端点的权限。

下面的代码示例说明用于创建一个登录名并且向该登录名授予针对某一端点的权限的 Transact-SQL 语句。远程服务器实例的域帐户在此处表示为 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  
```
在用户数据库驻留的服务器实例上，创建可用性组。

下面的代码示例在创建了示例数据库 MyDb1 和 MyDb2 的服务器实例上创建名为 MyAG的可用性组。首先指定 AgHostInstanceCOMPUTER01 上的本地服务器实例 。该实例将承载初始主副本。指定远程服务器实例（ COMPUTER02上的默认服务器实例）承载辅助副本。两个可用性副本都配置为使用异步提交模式，并使用手动故障转移（对于异步提交副本，手动故障转移意味着强制故障转移，并且可能导致数据丢失）。
```
-- 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  
```
有关创建可用性组的其他 Transact-SQL 代码示例，请参阅CREATE AVAILABILITY GROUP（Transact-SQL）。

在承载辅助副本的服务器实例上，将辅助副本联接到可用性组。

下面的代码示例将 COMPUTER02 上的辅助副本联接到 MyAG 可用性组。

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

在承载辅助副本的服务器实例上，创建辅助数据库。

下面的代码示例通过使用 WITH NORECOVERY 还原数据库备份来创建 MyDb1 和 RESTORE 辅助数据库。

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

在承载主副本的服务器实例上，备份每个主数据库上的事务日志。

重要

在您配置真实的可用性组时，我们建议在进行此日志备份之前，挂起针对您的主数据库的日志备份任务，直到您将相应的辅助数据库联接到该可用性组。

下面的代码示例在 MyDb1 和 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'   
    WITH NOFORMAT;  
GO
```
提示

通常，日志备份必须在每个主数据库上进行，然后在相应的辅助数据库上还原（使用 WITH NORECOVERY）。但是，如果数据库刚刚创建而尚未进行日志备份，或者如果恢复模式刚刚从 SIMPLE 更改为 FULL，则不必进行此日志备份。
在承载辅助副本的服务器实例上，将日志备份应用于辅助数据库。

下面的代码示例通过使用 WITH NORECOVERY 还原数据库备份，将备份应用到 MyDb1 和 RESTORE 辅助数据库。

重要

准备实际的辅助数据库时，需要应用自用于创建该辅助数据库的数据库备份之后获取的每个日志备份，并且要从最早的日志备份开始，且始终使用 RESTORE WITH NORECOVERY。当然，如果您还原了完整数据库备份和差异备份，那么只需还原在差异备份之后进行的日志备份。
```
-- 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  
```

在承载辅助副本的服务器实例上，将新的辅助数据库联接到可用性组。

下面的代码示例依次将 MyDb1 辅助数据库和 MyDb2 辅助数据库联接到 MyAG 可用性组。

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

示例配置过程的完整代码示例

下面的示例合并了示例配置过程的所有步骤中的代码示例。下表总结了此代码示例中使用的占位符值。有关此代码示例中的步骤的详细信息，请参阅本主题中前面的使用示例配置过程的先决条件和示例配置过程。

占位符	说明
\\ FILESERVER\SQLbackups	虚构的备份共享。
\\ FILESERVER\SQLbackups\MyDb1.bak	MyDb1 的备份文件。
\\ FILESERVER\SQLbackups\MyDb2.bak	MyDb2 的备份文件。
7022	分配给各数据库镜像端点的端口号。
COMPUTER01\AgHostInstance	承载初始主副本的服务器实例。
COMPUTER02	承载初始辅助副本的服务器实例。这是 `COMPUTER02`上的默认服务器实例。
dbm_endpoint	为每个数据库镜像端点指定的名称。
MyAG	示例可用性组的名称。
MyDb1	第一个示例数据库的名称。
MyDb2	第二个示例数据库的名称。
DOMAIN1\user1	要承载初始主副本的服务器实例的服务帐户。
DOMAIN2\user2	要承载初始辅助副本的服务器实例的服务帐户。
TCP:// COMPUTER01.Adventure-Works.com：7022	COMPUTER01 上 SQL Server 的 AgHostInstance 实例的端点 URL。
TCP:// COMPUTER02.Adventure-Works.com：5022	COMPUTER02 上 SQL Server 的默认实例的端点 URL。

注意

有关创建可用性组的其他 Transact-SQL 代码示例，请参阅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'   
    WITH NOFORMAT  
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

另请参阅

数据库镜像端点 (SQL Server)
AlwaysOn 可用性组概述 (SQL Server)
可用性组侦听器、客户端连接和应用程序故障转移 (SQL Server)
针对 AlwaysOn 可用性组的先决条件、限制和建议 (SQL Server)

反馈

此页面是否有帮助？

Last updated on 2025-01-02