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.
