SQL Server AlwaysOn AG automatic Initialization (iii)

to create an availability group with automatic seeding

1. Create an Endpoint

Each replica requires a mirroring endpoint for communication, 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

2. Create an availability group

Executes on the primary replica

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

The above script creates the availability group App1on, and joins the database App1customers and App1sales.

From the Create Availability group syntax

“

CREATE Availability GROUP Group_name

With (<with_option_spec> [,... n])

for [DATABASE database_name [,... N]]

REPLICA on <add_replica_spec> [,... N]

”

You can omit database database_name [,... n], create an availability group, and then add the specific databases in the 4th step.

3. Join the secondary server instance to the availability group and grant the availability group permission to create the database.

Execute on each secondary replica

ALTER Availability Group App1on joingoalter availability Group App1on GRANT CREATE any Databasego

4. Add the database to the availability group.

Executes on the primary replica

ALTER availability GROUP app1on ADD DATABASE app2customers; GO

SQL Server will automatically create a copy of the database on the secondary server. If the database is large, it may take some time to complete the database synchronization.

SQL Server AlwaysOn AG automatic Initialization (iii)