AlwaysOn Availability group Auto Seeding

Hi This is my first blog in 2018. I am writing about Alwayson availability group auto seeding feature in SQL server 2016. 

SQLHAPOCR01 (Primary Replica)

SQLHAPOCR02(Secondary Replica)

I have already enabled alwayson availability group feature in both SQL servers.

There are a few preparation steps to do before creating Availability group with auto seeding.

Turn on 9567 trace on Primary replica to enable compression backup.

DBCC TRACEON (9567,-1);  

GO  

Create Extended Events session monitor

CREATE EVENT SESSION [AlwaysOn_autoseed] ON SERVER

ADD EVENT sqlserver.hadr_automatic_seeding_state_transition,

ADD EVENT sqlserver.hadr_automatic_seeding_timeout,

ADD EVENT sqlserver.hadr_db_manager_seeding_request_msg,

ADD EVENT sqlserver.hadr_physical_seeding_backup_state_change,

ADD EVENT sqlserver.hadr_physical_seeding_failure,

ADD EVENT sqlserver.hadr_physical_seeding_forwarder_state_change,

ADD EVENT sqlserver.hadr_physical_seeding_forwarder_target_state_change,

ADD EVENT sqlserver.hadr_physical_seeding_progress,

ADD EVENT sqlserver.hadr_physical_seeding_restore_state_change,

ADD EVENT sqlserver.hadr_physical_seeding_submit_callback

ADD TARGET package0.event_file(SET filename=N'C:\autoseed.xel',max_file_size=(5),max_rollover_files=(4))

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)

GO

ALTER EVENT SESSION AlwaysOn_autoseed ON SERVER STATE=START

GO

After above steps are done, we will follow the steps below.

Step 1 

Create availability group with Auto Seeding is automatic. Run this on Primary Replica. Turn on SQLCMD

:Connect SQLHAPOCR01

CREATE AVAILABILITY GROUP SQLHAPOCDR

FOR

REPLICA ON N'SQLHAPOCR01' WITH (ENDPOINT_URL = N'TCP://SQLHAPOCR01.TEST.US.DOM:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SEEDING_MODE=AUTOMATIC),

N'SQLHAPOCR02' WITH (ENDPOINT_URL = N'TCP://SQLHAPOCR02.TEST.US.DOM:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SEEDING_MODE=AUTOMATIC);

Go

Step 2
Connect to a Secondary replica and join Secondary replica to AG. Give Create database permission to  AG group. Run this on Secondary Replica. Turn on SQLCMD

:Connect SQLHAPOCR02

ALTER AVAILABILITY GROUP SQLHAPOCDR JOIN

GO

:Connect SQLHAPOCR02

ALTER AVAILABILITY GROUP SQLHAPOCDR

GRANT CREATE ANY DATABASE

GO

Step 3
Join an existing database on Primary to AG group. Run on Primary. Turn on SQLCMD

:Connect SQLHAPOCR01

alter AVAILABILITY group SQLHAPOCDR add database TESTAUTOSEED

go

Step 4
I will monitor auto seeding status using the following DMVs and queries.

select * from sys.dm_hadr_automatic_seeding

select * from sys.dm_hadr_physical_seeding_stats

SELECT start_time, 

    completion_time, 

    is_source, 

    current_state, 

    failure_state, 

    failure_state_desc, 

    error_code 

FROM sys.dm_hadr_automatic_seeding

My test database is 100 Gbs and it took about 9 mins to finish auto seeding and synchronize.

Thank you for reading my blog.