Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 3 | 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.
1 2 3 | 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.
1 2 3 | 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”
