skreebydba

Microsoft released Availability Groups (AG) as a feature in SQL Server 2012. Prior to SQL Server 2016, there were two methods of adding a database to a new AG replica.

1. You could provide the Add Database to Availability Group wizard a file share accessible by the primary and secondary replicas.  SQL Server would run FULL and LOG backups of each database to the share and use them to restore the database(s) to each replica.
2. You could manually run a FULL and LOG backup of each database, copy the backup files to each replica, and restore the databases WITH NORECOVERY.

With SQL Server 2016. Microsoft has provided a third option, Automatic Seeding.  With Automatic Seeding, you specify the databases and the replicas and SQL Server will begin transferring data to each replica.  The duration of the seeding process depends on the size of the database and the network bandwidth available between primary and secondary replica.

There are a few considerations to keep in mind when considering Automatic Seeding.  Virtual log files in the primary database cannot be freed while the automatic seeding process is running.  For a large, active database this can lead to excessive log growth.  Automatic seeding is a single-threaded process and is limited to 5 databases.  Additionally, Automatic Seeding will not work if Change Data Capture is enabled.  Finally, compression is disabled for Automatic Seeding by default, but can be enabled by turning on trace flag 9567.  This can improve performance of the seeding process but will increase CPU overhead.

The status of the Automatic Seeding process can be monitored using the following query: (download)

USE master;

SELECT local_database_name,
internal_state_desc,
remote_machine_name,
database_size_bytes / 1045876 AS [Database Size (mb)],
transferred_size_bytes / 1045876 AS [Transferred Size (mb)],
transfer_rate_bytes_per_second / 1045876 AS [Transfer Rate/Sec (mb)],
(database_size_bytes - transferred_size_bytes) / transfer_rate_bytes_per_second AS [Time Remaining (sec)],
CASE
WHEN ((database_size_bytes - transferred_size_bytes) / transfer_rate_bytes_per_second) < 360000
THEN '0'
ELSE ''
END
+ RTRIM(((database_size_bytes - transferred_size_bytes) / transfer_rate_bytes_per_second) / 3600)
+ ':' + RIGHT('0' + RTRIM(((database_size_bytes - transferred_size_bytes) / transfer_rate_bytes_per_second) % 3600 / 60), 2)
+ ':' + RIGHT('0' + RTRIM(((database_size_bytes - transferred_size_bytes) / transfer_rate_bytes_per_second) % 60), 2) AS [Time Remaining]
FROM sys.dm_hadr_physical_seeding_stats
WHERE internal_state_desc = 'ReadingAndSendingData'
ORDER BY remote_machine_name;

The query returns the following result set:

Note that one row will be returned per replica being seeded.

Automatic Seeding makes initializing AG replicas simple. I hope you can put the status query to good use.