创建可用性组 (Transact-SQL)
本主题介绍如何使用 Transact-SQL 在启用了 Always On 可用性组功能的 SQL Server 2014 实例上创建和配置可用性组。 “可用性组” 定义一组用户数据库,这些用户数据库将以支持故障转移的单个单元和一组故障转移伙伴(称作“可用性副本” )的形式进行故障转移。
注意
有关可用性组的简介,请参阅 alwaysOn 可用性组概述 (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可用性组先决条件。 有关详细信息,强烈建议阅读 AlwaysOn 可用性组的先决条件、限制和建议 (SQL Server) 。
安全性
权限
需要 sysadmin 固定服务器角色的成员资格,以及 CREATE AVAILABILITY GROUP 服务器权限、ALTER ANY AVAILABILITY GROUP 权限或 CONTROL SERVER 权限。
任务和相应 Transact-SQL 语句摘要
下表列出了涉及创建和配置可用性组的基本任务,并且指出了要用于这些任务的 Transact-SQL 语句。 Always On可用性组任务必须按照表中显示的顺序执行。
任务 | Transact-SQL 语句 | 执行任务的位置***** |
---|---|---|
创建数据库镜像端点(每个 SQL Server 实例一次) | CREATE ENDPOINTendpointName ... FOR DATABASE_MIRRORING | 在缺少数据库镜像端点的每个服务器实例上执行。 |
创建可用性组 | CREATE AVAILABILITY GROUP | 在要承载初始主副本的服务器实例上执行。 |
将辅助副本联接到可用性组 | ALTER AVAILABILITY GROUPgroup_name JOIN | 在承载辅助副本的各服务器实例上执行。 |
准备辅助数据库 | BACKUP 和 RESTORE。 | 在承载主副本的服务器实例上创建备份。 使用 RESTORE WITH NORECOVERY 在承载辅助副本的各服务器实例上还原备份。 |
通过将各辅助数据库联接到可用性组,开始数据同步 | ALTER DATABASEdatabase_name SET HADR AVAILABILITY GROUP = group_name | 在承载辅助副本的各服务器实例上执行。 |
* 若要执行给定任务,请连接到一个或多个指示的服务器实例。
使用 Transact-SQL 创建和配置可用性组
注意
有关包含上述各 Transact-SQL 语句的代码示例的配置过程示例,请参阅示例:配置使用 Windows 身份验证的可用性组。
连接到要承载主副本的服务器实例。
通过使用 CREATE AVAILABILITY GROUP Transact-SQL 语句创建可用性组。
将新的辅助副本联接到可用性组。 有关详细信息,请参阅将次要副本联接到可用性组 (SQL Server)。
对于可用性组中的每个数据库,通过使用 RESTORE WITH NORECOVERY 还原主数据库的最近的备份,创建辅助数据库。 有关详细信息,请参阅示例:使用 Windows 身份验证设置可用性组 (Transact-SQL),从还原数据库备份的步骤开始。
将每个新的辅助数据库联接到可用性组。 有关详细信息,请参阅将次要副本联接到可用性组 (SQL Server)。
示例:配置使用 Windows 身份验证的可用性组
此示例创建一个示例Always On可用性组配置过程,该过程使用 Transact-SQL 设置使用 Windows 身份验证的数据库镜像终结点,以及创建和配置可用性组及其辅助数据库。
此示例包含以下部分:
使用示例配置过程的先决条件
此示例过程具有以下要求:
服务器实例必须支持Always On可用性组。 有关详细信息,请参阅 AlwaysOn 可用性组的先决条件、限制和建议 (SQL Server) 。
两个示例数据库 MyDb1 和 MyDb2必须在将承载主副本的服务器实例上存在。 下面的代码示例创建和配置这两个数据库,并且创建这两个数据库的完整备份。 在您想要创建示例可用性组的服务器实例上执行这些代码示例。 此服务器实例将承载示例可用性组的初始主副本。
下面的 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
下面的代码示例创建 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
示例配置过程
在这个示例配置中,将在两个独立服务器实例上创建可用性副本,这些服务器实例的服务帐户在不同的可信域(DOMAIN1
和 DOMAIN2
)下运行。
下表总结了此示例配置中使用的值。
初始角色 | 系统 | 主机 SQL Server 实例 |
---|---|---|
主 | COMPUTER01 |
AgHostInstance |
辅助副本 | COMPUTER02 |
默认实例。 |
在你计划创建可用性组的服务器实例(这是 上名为 的实例)上创建名为
AgHostInstance
dbm_endpointCOMPUTER01
的数据库镜像端点。 此端点使用端口 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的可用性组。 首先指定
AgHostInstance
COMPUTER01 上的本地服务器实例 。 该实例将承载初始的主副本。 指定远程服务器实例( 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
在承载辅助副本的服务器实例上,创建辅助数据库。
以下代码示例通过使用 RESTORE WITH NORECOVERY 还原数据库备份来创建 MyDb1 和 MyDb2 辅助数据库。
-- 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' WITHNOFORMAT GO
提示
通常,日志备份必须在每个主数据库上进行,然后在相应的辅助数据库上还原(使用 WITH NORECOVERY)。 但是,如果数据库刚刚创建而尚未进行日志备份,或者如果恢复模式刚刚从 SIMPLE 更改为 FULL,则不必进行此日志备份。
在承载辅助副本的服务器实例上,将日志备份应用于辅助数据库。
以下代码示例通过使用 RESTORE WITH NORECOVERY 还原数据库备份来将备份应用于 MyDb1 和 MyDb2 辅助数据库。
重要
在您准备真实的辅助数据库时,需要应用您从其创建了辅助数据库的数据库备份之后发生的每个日志备份,从最早的开始并且始终使用 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 上的默认服务器实例。 |
上名为 | 为每个数据库镜像端点指定的名称。 |
MyDb1 | 示例可用性组的名称。 |
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'
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
Related Tasks
配置可用性组和副本属性
完成可用性组配置
用于创建可用性组的其他方法
启用 AlwaysOn 可用性组
配置数据库镜像端点
解决 AlwaysOn 可用性组配置问题
相关内容
博客:
AlwaysON - HADRON 学习系列:启用了 HADRON 的数据库的工作线程池用法
视频:
Microsoft SQL Server Code-Named "Denali" AlwaysOn 系列,第一部分:介绍下一代高可用性解决方案
Microsoft SQL Server Code-Named "Denali" AlwaysOn 系列,第二部分:使用 AlwaysOn 生成关键任务高可用性解决方案
白皮书:
用于高可用性和灾难恢复的 Microsoft SQL Server AlwaysOn 解决方案指南
另请参阅
数据库镜像端点 (SQL Server)
AlwaysOn 可用性组概述 (SQL Server)
可用性组侦听器、客户端连接和应用程序故障转移 (SQL Server)