创建具有自动种子设定的可用性组
1. 创建端点
每个副本都需要一个镜像端点进行通信,默认TCP端口为5022。
CREATE ENDPOINT [Hadr_endpoint] STATE=STARTED AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL) FOR DATA_MIRRORING ( ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM AES ) GO
2. 创建可用性组
在主副本上执行
CREATE AVAILABILITY GROUP [App1On] FOR DATABASE App1Customers, App1Sales REPLICA ON N'TEST-GS-ZHXT1\SQL2017' -- primary WITH (ENDPOINT_URL = N'TCP://TEST-GS-ZHXT1.jztest.com:5023', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL), SEEDING_MODE = AUTOMATIC), N'TEST-GS-ZHXT2\SQL2017' -- secondary WITH (ENDPOINT_URL = N'TCP://TEST-GS-ZHXT2.jztest.com:5023', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL), SEEDING_MODE = AUTOMATIC), N'TEST-GS-ZHXT3\SQL2017' -- secondary WITH (ENDPOINT_URL = N'TCP://TEST-GS-ZHXT3.jztest.com:5023', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL), SEEDING_MODE = AUTOMATIC), N'TEST-GS-ZHXT4\SQL2017' -- secondary WITH (ENDPOINT_URL = N'TCP://TEST-GS-ZHXT4.jztest.com:5023', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL), SEEDING_MODE = AUTOMATIC); GO
以上脚本创建可用性组App1On,同时加入了数据库App1Customers和App1Sales。
从CREATE AVAILABILITY GROUP语法
“
CREATE AVAILABILITY GROUP group_name
WITH (<with_option_spec> [ ,...n ] )
FOR [ DATABASE database_name [ ,...n ] ]
REPLICA ON <add_replica_spec> [ ,...n ]
”
可知,可以省略DATABASE database_name [ ,...n ],先创建可用性组,最后第4步再添加具体数据库。
3. 将辅助服务器实例联接到可用性组,并向可用性组授予创建数据库的权限。
在各辅助副本上执行
ALTER AVAILABILITY GROUP App1On JOIN GO ALTER AVAILABILITY GROUP App1On GRANT CREATE ANY DATABASE GO
4. 添加数据库到可用性组。
在主副本上执行
ALTER AVAILABILITY GROUP App1On ADD DATABASE App2Customers; GO
SQL Server 将在辅助服务器上自动创建数据库副本。 如果数据库较大,则可能需要一些时间才能完成数据库同步。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。