将数据库加入 AG（自动种子设定）并监控种子设定进度

命令

-- Prerequisite (primary): FULL recovery and at least one FULL backup
SELECT name, recovery_model_desc FROM sys.databases WHERE name = N'Musteri';
BACKUP DATABASE [Musteri] TO DISK = N'D:\Backup\Musteri_pre_ag.bak' WITH COMPRESSION, CHECKSUM, INIT;

-- 1) Primary: switch the replica to automatic seeding
ALTER AVAILABILITY GROUP [AG_Musteri]
  MODIFY REPLICA ON N'SQL02' WITH (SEEDING_MODE = AUTOMATIC);

-- 2) On each secondary: allow the AG to create databases
ALTER AVAILABILITY GROUP [AG_Musteri] GRANT CREATE ANY DATABASE;

-- 3) Primary: add the database
ALTER AVAILABILITY GROUP [AG_Musteri] ADD DATABASE [Musteri];

-- 4) Progress (primary or secondary):
SELECT local_database_name, role_desc, internal_state_desc,
       transferred_size_bytes / 1048576         AS transferred_mb,
       database_size_bytes / 1048576            AS db_mb,
       transfer_rate_bytes_per_second / 1048576 AS mb_s,
       estimate_time_complete_utc, failure_message
FROM   sys.dm_hadr_physical_seeding_stats;

-- 5) Seeding history and failure reason:
SELECT s.start_time, s.completion_time, adc.database_name, ar.replica_server_name,
       s.current_state, s.failure_state_desc, s.error_code, s.number_of_attempts
FROM   sys.dm_hadr_automatic_seeding s
  JOIN sys.availability_databases_cluster adc ON adc.group_database_id = s.ag_db_id
  JOIN sys.availability_replicas ar           ON ar.replica_id = s.ag_remote_replica_id
ORDER BY s.start_time DESC;

作用

通过终结点（VDI）将数据库流式传输到辅助副本，从而将其加入 AG，无需手动备份/还原；显示种子设定吞吐量、预计完成时间和失败原因。

适用场景

向 AG 添加新数据库、添加新副本或重建损坏的辅助数据库时。

注意

种子设定会占满终结点链路，且默认不压缩；对于超大数据库或较弱的 WAN，手动备份/还原 + JOIN 更可预测。种子设定期间主副本日志不会截断（log_reuse_wait_desc = AVAILABILITY_REPLICA）。在 2016 上辅助副本需要与主副本相同的数据/日志文件夹路径；在 2017+ 上，若主副本文件位于实例默认路径，辅助副本使用其自身的默认路径。对于 TDE 数据库，必须先在每个辅助副本上还原证书。若辅助副本上未执行 GRANT CREATE ANY DATABASE，种子设定会一直挂起。