实例：Examples:
A 、在辅助副本上配置备份、灵活的故障转移策略和连接访问 A. Configuring Backup on Secondary Replicas, Flexible Failover Policy, and Connection Access
下面的示例为两个用户数据库（ThisDatabase和 ThatDatabase ）创建一个名为 MyAg 的可用性组。The following example creates an availability group named MyAg for two user databases, ThisDatabase and ThatDatabase . 下表汇总了为整个可用性组设置的选项指定的值。The following table summarizes the values specified for the options that are set for the availability group as a whole.
| 可用性组选项 Group Option | 设置 Setting | 说明 Description |
| AUTOMATED_BACKUP_PREFERENCE | SECONDARY | 此自动备份首选项指示备份应发生在辅助副本上，除非主副本是唯一联机副本(这是默认行为)。This automated backup preference indicates that backups should occur on a secondary replica except when the primary replica is the only replica online (this is the default behavior).要使“自动备份”首选项设置具有任何效果，您需要在可用性数据库上编写备份作业脚本，以将“自动备份”首选项考虑在内。For the AUTOMATED_BACKUP_PREFERENCE setting to have any effect, you need to script backup jobs on the availability databases to take the automated backup preference into account. |
| FAILURE_CONDITION_LEVEL | 3 | 此故障条件级别设置指定应在关键的SQL Server内部错误(如孤立的spinlocks、严重的写访问冲突或转储过多)上启动自动故障转移。This failure condition level setting specifies that an automatic failover should be initiated on critical SQL Server internal errors, such as orphaned spinlocks, serious write-access violations, or too much dumping. |
| HEALTH_CHECK_TIMEOUT | 600000 | 此运行状况检查超时值(60秒)指定WSFC群集等待60000毫秒，以便sp_server_diagnostics系统存储过程返回有关服务器实例的服务器运行状况信息，该服务器实例在主机服务器实例慢或挂起之前，群集自动关联同步提交副本。(默认值为30000毫秒)。This health check timeout value, 60 seconds, specifies that the WSFC cluster waits 60000 milliseconds for the sp_server_diagnostics system stored procedure to return server-health information about a server instance that is hosting a synchronous-commit replica with automatic before the cluster assumes that the host server instance is slow or hung. (The default value is 30000 milliseconds). |
三个可用性副本将由名为computer01、computer02和computer03的计算机上的默认服务器实例托管。Three availability replicas are to be hosted by the default server instances on computers named COMPUTER01, COMPUTER02 , and COMPUTER03 . 下表汇总了为每个副本的副本选项指定的值。The following table summarizes the values specified for the replica options of each replica.
| 副本选项 Replica Option | 设置 Setting on COMPUTER01 、COMPUTER02、COMPUTER03 | 说明 Description |
| ENDPOINT_URL | TCP://COMPUTER01:5022 TCP://COMPUTER02:5022 TCP://COMPUTER03:5022 | 在本例中，系统是相同的域，因此端点URL可以使用计算机系统的名称作为系统地址。In this example, the systems are the same domain, so the endpoint URLs can use the name of the computer system as the system address. |
| AVAILABILITY_MODE | SYNCHRONOUS_COMMIT SYNCHRONOUS_COMMIT ASYNCHRONOUS_COMMIT | 两个副本使用同步提交模式。Two of the replicas use synchronous-commit mode.同步时，它们支持故障转移而不丢失数据。When synchronized, they support failover without data loss. 第三个副本，使用异步提交可用性模式。The third replica, which uses asynchronous-commit availability mode. |
| FAILOVER_MODE | AUTOMATIC AUTOMATIC MANUAL | 同步提交副本支持自动故障转移和计划的手动故障转移。The synchronous-commit replicas support automatic failover and planned manual failover. 异步提交可用性模式副本只支持强制手动故障转移。The asynchronous-commit availability mode replica supports only forced manual failover. |
| BACKUP_PRIORITY | 30 30 90 | 与同步提交副本相比，异步提交副本具有更高的优先级90。A higher priority, 90, is assigned to the asynchronous-commit replica, than to the synchronous-commit replicas.备份通常发生在承载异步提交副本的服务器实例上。Backups tend to occur on the server instance that hosts the asynchronous-commit replica. |
| SECONDARY_ROLE | ( ALLOW_CONNECTIONS = NO,READ_ONLY_ROUTING_URL = 'TCP://COMPUTER01:1433' ) ( ALLOW_CONNECTIONS = NO,READ_ONLY_ROUTING_URL = 'TCP://COMPUTER02:1433' ) ( ALLOW_CONNECTIONS = READ_ONLY, READ_ONLY_ROUTING_URL = 'TCP://COMPUTER03:1433' ) | 只有异步提交副本可用作可读的辅助副本。Only the asynchronous-commit replica serves as a readable secondary replica. 指定计算机名和默认数据库引擎端口号(1433)。Specifies the computer name and default Database Engine port number (1433). 此参数是可选的。This argument is optional. |
| PRIMARY_ROLE | ( ALLOW_CONNECTIONS = READ_WRITE, READ_ONLY_ROUTING_LIST = (COMPUTER03) ) ( ALLOW_CONNECTIONS = READ_WRITE, READ_ONLY_ROUTING_LIST = (COMPUTER03) ) ( ALLOW_CONNECTIONS = READ_WRITE, READ_ONLY_ROUTING_LIST = NONE ) | 在主角色中，所有副本都拒绝读意图连接尝试。In the primary role, all the replicas reject read-intent connection attempts. 如果本地副本在辅助角色下运行，则将读意向连接请求路由到computer03。Read-intent connection requests are routed to COMPUTER03 if the local replica is running under the secondary role.当该副本在主角色下运行时，将禁用只读路由。When that replica runs under the primary role, read-only routing is disabled. 此参数是可选的。This argument is optional. |
| SESSION_TIMEOUT | 10 10 10 | 此示例指定默认会话超时值(10)。This example specifies the default session timeout value (10). 此参数是可选的。This argument is optional. |
最后，该示例指定可选的LISTENER子句来为新的可用性组创建可用性组侦听器。Finally, the example specifies the optional LISTENER clause to create an availability group listener for the new availability group. 为此侦听器指定唯一的DNS名称myagListenerivp6。A unique DNS name, MyAgListenerIvP6 , is specified for this listener. 这两个副本位于不同的子网上，因此侦听器必须使用静态IP地址。The two replicas are on different subnets, so the listener must use static IP addresses. 对于这两个可用性副本中的每一个，WITH IP子句都指定使用IPv6格式的静态IP地址2001:4898:f0:f0f :: cf3c 和2001:4898:e0:f213 :: 4ce2 。For each of the two availability replicas, the WITH IP clause specifies a static IP address, 2001:4898:f0:f00f::cf3c and 2001:4898:e0:f213::4ce2 , which use the IPv6 format. 此示例还指定使用可选端口参数将端口60173指定为侦听器端口。This example also specifies uses the optional PORT argument to specify port 60173 as the listener port.
CREATE AVAILABILITY GROUP MyAg
WITH (
AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
FAILURE_CONDITION_LEVEL = 3 ,
HEALTH_CHECK_TIMEOUT = 600000
)
FOR
DATABASE ThisDatabase, ThatDatabase
REPLICA ON
'COMPUTER01' WITH (
ENDPOINT_URL = 'TCP://COMPUTER01:5022' ,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = AUTOMATIC ,
BACKUP_PRIORITY = 30 ,
SECONDARY_ROLE (ALLOW_CONNECTIONS = NO ,
READ_ONLY_ROUTING_URL = 'TCP://COMPUTER01:1433' ),
PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE,
READ_ONLY_ROUTING_LIST = (COMPUTER03) ),
SESSION_TIMEOUT = 10
),
'COMPUTER02' WITH (
ENDPOINT_URL = 'TCP://COMPUTER02:5022' ,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = AUTOMATIC ,
BACKUP_PRIORITY = 30 ,
SECONDARY_ROLE (ALLOW_CONNECTIONS = NO ,
READ_ONLY_ROUTING_URL = 'TCP://COMPUTER02:1433' ),
PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE,
READ_ONLY_ROUTING_LIST = (COMPUTER03) ),
SESSION_TIMEOUT = 10
),
'COMPUTER03' WITH (
ENDPOINT_URL = 'TCP://COMPUTER03:5022' ,
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL ,
BACKUP_PRIORITY = 90 ,
SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY,
READ_ONLY_ROUTING_URL = 'TCP://COMPUTER03:1433' ),
PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE,
READ_ONLY_ROUTING_LIST = NONE ),
SESSION_TIMEOUT = 10
);
GO
ALTER AVAILABILITY GROUP [MyAg]
ADD LISTENER 'MyAgListenerIvP6' (
WITH IP ( ( '2001:db88:f0:f00f::cf3c' ),( '2001:4898:e0:f213::4ce2' ) )
, PORT = 60173 );
GO
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/81227/viewspace-2654668/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/81227/viewspace-2654668/
扫一扫
3213
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
