Automatic Seeding in Always On: List and Monitor It

Automatic seeding in Always On lets the primary send a database to a secondary without a manual restore. Three queries show what it is doing. They are read-only, so you can run them on a live group. Each one answers a different question. The test instance has no availability group, so the queries only parse and run there. Every statement about columns and states below is documented, not measured.

What Automatic Seeding Does

Before SQL Server 2016, you added a database to a group with a backup, a file copy and a restore. With automatic seeding, the primary streams the backup over the endpoint the group already uses. The secondary creates the files itself and joins the database.

The choice is made per replica. A replica has a seeding mode of AUTOMATIC or MANUAL. That is why one group can seed to some replicas for you and leave other replicas to a manual restore.

It saves work, and it has a price. The primary runs a backup for the whole time a database seeds. On a large database or a slow link, that backup competes with your workload for disk, CPU and network. Seeding uses a virtual device backup. Waits such as VDI_CLIENT_OTHER can show up in your wait statistics while it runs.

Which Replicas Use It

Start with the setting itself. This query lists every replica of every group on the instance with its seeding mode.

SELECT ag.name AS GroupName,
       ar.replica_server_name AS ReplicaName,
       ar.seeding_mode_desc AS SeedingMode
FROM sys.availability_groups AS ag
JOIN sys.availability_replicas AS ar ON ar.group_id = ag.group_id
ORDER BY ag.name, ar.replica_server_name;

The test server has no availability group, so this query returns no rows there. On a real group you get one row per replica. A replica marked AUTOMATIC is the one that receives databases without a manual restore.

Read the Seeding History

The next query reads the history that SQL Server keeps for automatic seeding. Mind the commas in a script like this. A missing comma after completion_time turns it into an alias named is_source. This version also adds the replica name and shows how long each run took.

SELECT ag.name AS GroupName,
       adc.database_name AS DatabaseName,
       ar.replica_server_name AS OtherReplica,
       s.is_source,
       s.current_state,
       s.start_time,
       DATEDIFF(SECOND, s.start_time, s.completion_time) AS Seconds,
       s.number_of_attempts,
       s.failure_state_desc,
       s.error_code
FROM sys.dm_hadr_automatic_seeding AS s
JOIN sys.availability_groups AS ag ON ag.group_id = s.ag_id
JOIN sys.availability_databases_cluster AS adc ON adc.group_database_id = s.ag_db_id
LEFT JOIN sys.availability_replicas AS ar ON ar.replica_id = s.ag_remote_replica_id
ORDER BY s.start_time DESC;

Run it on the primary and again on the secondary. Each instance reports its own rows, so read both.

Watch a Seeding Run in Progress

The history tells you what finished. To see a run that is still moving, read the physical seeding view. It shows the size of the database, how much has been sent and the current rate.

SELECT local_database_name AS DatabaseName,
       remote_machine_name AS OtherMachine,
       role_desc,
       internal_state_desc,
       transferred_size_bytes / 1048576 AS SentMB,
       database_size_bytes / 1048576 AS TotalMB,
       transfer_rate_bytes_per_second / 1048576 AS MBPerSecond,
       estimate_time_complete_utc,
       failure_message
FROM sys.dm_hadr_physical_seeding_stats;

Divide the remaining megabytes by the rate for a rough finish time. The estimate column does that for you. When the rate stays near zero, look at the network and at the disk on the secondary before anything else.

Why Automatic Seeding Fails

Three causes are worth checking first. The group needs the CREATE ANY DATABASE permission on the secondary, or the secondary cannot create the database. The folders for the files must exist and the disk needs room. The endpoint connection between the replicas must also be up.

The error code in the history query points at one of them. Fix the cause, then read the history again to see the next attempt.

Msg 208, Invalid object name, on these views means the instance is older than SQL Server 2016. The views do not exist there.

When to Turn It Off

Automatic seeding suits small and medium databases on a fast network. For a multi-terabyte database it can hurt, because you cannot schedule the backup or choose its target. Trace flag 9567 compresses the stream and cuts network use. It also raises CPU use on the primary, so test it first.

You can switch the mode back to manual for one replica. The next block is a template for an availability group of your own. Replace the two names, and keep the second statement for the way back.

ALTER AVAILABILITY GROUP [YourGroup] MODIFY REPLICA ON N'SecondReplica' WITH (SEEDING_MODE = MANUAL);

This is the way back. Run it only when you want automatic seeding on that replica again.

ALTER AVAILABILITY GROUP [YourGroup] MODIFY REPLICA ON N'SecondReplica' WITH (SEEDING_MODE = AUTOMATIC);

These statements change a live group, so run them in a quiet window. With manual seeding, you back up the database and restore it on the secondary WITH NORECOVERY. Then you join it to the group.

You could argue that manual seeding is a step backward. For a one-off small database it is. For a large database it gives you a backup you can schedule, compress and check before the restore.

What to Remember

Check the seeding mode of each replica first. Then read the history, and watch the physical view while a run is active. A failed run leaves a row with an error code, and a slow run leaves a low transfer rate.

Treat every seeding run as a backup that the primary runs for you. Plan it for a quiet hour and check the disk space on the secondary. Switch it off for databases that are too large to stream.

Automatic seeding is not a free copy, it is a backup the primary runs for you.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.