SQL Server 2017 AlwaysOn AG 自动初始化（三）

创建具有自动种子设定的可用性组

1. 创建端点

每个副本都需要一个镜像端点进行通信，默认TCP端口为5022。

CREATE ENDPOINT [Hadr_endpoint]STATE=STARTEDAS 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, App1SalesREPLICA ONN'TEST-GS-ZHXT1\SQL2017' -- primaryWITH (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' -- secondaryWITH (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' -- secondaryWITH (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' -- secondaryWITH (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 ( [ ,...n ] )

FOR [ DATABASE database_name [ ,...n ] ]

REPLICA ON [ ,...n ]

"

可知，可以省略DATABASE database_name [ ,...n ]，先创建可用性组，最后第4步再添加具体数据库。

3. 将辅助服务器实例联接到可用性组，并向可用性组授予创建数据库的权限。

在各辅助副本上执行

ALTER AVAILABILITY GROUP App1On JOINGOALTER AVAILABILITY GROUP App1On GRANT CREATE ANY DATABASEGO

4. 添加数据库到可用性组。

在主副本上执行

ALTER AVAILABILITY GROUP App1On ADD DATABASE App2Customers;GO

SQL Server 将在辅助服务器上自动创建数据库副本。 如果数据库较大，则可能需要一些时间才能完成数据库同步。