Automatic initialization of SQL Server 2017 AlwaysOn AG (3)
Create an availability group with automatic seed settings
1. Create an endpoint
Each replica requires a mirror endpoint to communicate, and the default TCP port is 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
two。 Create an availability group
Execute on the master copy
CREATE AVAILABILITY GROUP [App1On] FOR DATABASE App1Customers, App1SalesREPLICA ONN'TEST-GS-ZHXT1\ SQL2017'-- primaryWITH (ENDPOINT_URL = nasty TCPGV ZHXT1.jztest.comlux 5023mt. FAILOVERTER mode = AUTOMATIC,AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,BACKUP_PRIORITY = 50mt. PRIMARYROLE (ALLOW_CONNECTIONS = ALL), SEEDING_MODE = AUTOMATIC), N'TEST-GS-ZHXT2\ SQL2017'-secondaryWITH (ENDPOINT_URL = nasty TCPGUP and TestmerGSXT2.jztest.comlux 5023 AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,BACKUP_PRIORITY = 50Particular (ALLOW_CONNECTIONS = ALL), SEEDING_MODE = AUTOMATIC), N'TEST-GS-ZHXT3\ SQL2017'-- secondaryWITH (ENDPOINT_URL = nasty TCPWR), TestVERMODE = MANUAL,AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,BACKUP_PRIORITY = 50MET ALL (ALLOW_CONNECTIONS = ALL), SEEDING_MODE = AUTOMATIC) N'TEST-GS-ZHXT4\ SQL2017'-- secondaryWITH (ALLOW_CONNECTIONS = ALL) (ENDPOINT_URL = ALLOW_CONNECTIONS = ALL), SEEDING_MODE = AUTOMATIC) GO
The above script creates the availability group App1On with the addition of the database App1Customers and App1Sales.
From CREATE AVAILABILITY GROUP syntax
"
CREATE AVAILABILITY GROUP group_name
WITH ([,... n])
FOR [DATABASE database_name [,... n]]
REPLICA ON [,... n]
"
You can omit DATABASE database_name [,... n], create an availability group first, and then add a specific database in step 4.
3. Join the secondary server instance to the availability group and grant the availability group permission to create the database.
Execute on each auxiliary copy
ALTER AVAILABILITY GROUP App1On JOINGOALTER AVAILABILITY GROUP App1On GRANT CREATE ANY DATABASEGO
4. Add a database to the availability group.
Execute on the master copy
ALTER AVAILABILITY GROUP App1On ADD DATABASE App2Customers;GO
SQL Server automatically creates a copy of the database on the secondary server. If the database is large, it may take some time to complete the database synchronization.
