Automatic Seeding in Always On Availability Groups

dbtut

5 years ago

What is Automatic Seeding?

Automatic Seeding is a feature introduced with SQL Server 2016. We need to add SEEDING_MODE = AUTOMATIC when creating the Availability Groupor or alter the existing availability group as follows. If you set this feature AUTOMATIC with the following script, each database automatically added to the secondary server by SQL Server.

Enable Automatic Seeding

In order to do this, there should be paths of the same name for the data and log files on the primary and secondary servers.

ALTER AVAILABILITY GROUP [AG_Name] 
    MODIFY REPLICA ON 'secondary_instance' 
    WITH (SEEDING_MODE = AUTOMATIC)

ALTER AVAILABILITY GROUP [AG_Name]

MODIFY REPLICA ON 'secondary_instance'

WITH (SEEDING_MODE = AUTOMATIC)

Disable Automatic Seeding

We can set it MANUAL with the help of the script below.

ALTER AVAILABILITY GROUP [AG_Name] 
    MODIFY REPLICA ON 'secondary_instance' 
    WITH (SEEDING_MODE = MANUAL)

ALTER AVAILABILITY GROUP [AG_Name]

MODIFY REPLICA ON 'secondary_instance'

WITH (SEEDING_MODE = MANUAL)

Check Automatic Seeding Status of an Availability Group

You can query the status of automatic seeding with the help of the following queries.

SELECT * FROM sys.dm_hadr_automatic_seeding

SELECT * FROM sys.dm_hadr_physical_seeding_stats;

SELECT * FROM sys.dm_hadr_automatic_seeding

SELECT * FROM sys.dm_hadr_physical_seeding_stats;

You may want to read the article “SQL Server Always ON Availability Group” to create a new availability group. If you have SQL Server Standart Edition you should read the article named “How To Create Basic Availability Group in SQL Server”