Shulou(Shulou.com)06/01 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.