SQL Server 2016 AlwaysOn Availability Group Enhancements: Initial Data Synchronization Without Database and Log Backup
Overview
In SQL Server 2012 and 2014, initializing a secondary replica is cumbersome to do manually, and requires backup and restore of your database and transaction log from the primary to a network share, and then a restore of the database and log backup files to the secondary replica. The availability group wizard can automate this initialization, but still requires additional configuration of a network share and permissions to access that network share by the SQL Server service startup accounts that SQL Server is running with.
SQL Server 2016 introduces a simpler way to initialize databases on secondary replicas, by using direct seeding. With direct seeding, when a database is added to the availability group at the primary, it is automatically initialized at the secondary with no further user intervention.
Behind the scenes, a VDI backup is performed over the network to the secondary replica where it is restored.
Benefits
The benefits are simpler, more automated initialization of your availability group databases to the secondary replica. When using direct seeding there is no need to configure a file share for backup and restore which is one less configuration headache.
When Would You not use Direct Seeding
Some scenarios may not be optimal for using direct seeding when initializing a secondary replica. Initialization of the availability database using direct seeding does not use compression, while the availability group wizard, performs backup and restore using compression.
For that reason, initialization can be slow if some databases are very large, or the secondary replica is remote.
Compression is disabled by default for direct seeding. You can enable compression by enabling trace flag 9567 as a startup trace flag or using DBCC TRACEON. When adding a large database or several databases using direct seeding you may observe high cpu utilization on the SQL Server during direct seeding with compression enabled. It is recommended that you test the trace flag prior to implementing in a production environment.
IMPORTANT The transaction log for these databases cannot be truncated during direct seeding, so a prolonged initialization process on a busy database can result in significant transaction log growth. For more information on operations that can delay log truncation check out Factors That Can Delay Log Truncation.
It is recommended that database size, replication distance and primary database load be considered before using direct seeding.
Demo: Initialize a secondary replica with direct seeding
The new CREATE and ALTER AVAILABILITY GROUP syntax includes a new parameter, SEEDING_MODE and is configured on a per-replica basis. SEEDING_MODE has two settings:
- Manual which indicates legacy backup and restore to initialize the databases onto the secondary. This is the default.
- Automatic which defines the new automated initialization of the availability group databases on the secondary replica
This feature is available when creating and configuring an availability group using transact-sql. The new availability group wizard does not provide knobs for enabling SEEDING_MODE.
The following demo shows how to automatically seed availability group databases to the secondary replica.
Create an empty availability group
Create an empty availability group on the primary replica (no databases) and configure each replica for SEEDING_MODE=AUTOMATIC:
:Connect SQL16N1
CREATE AVAILABILITY GROUP [SEEDag]
FOR
REPLICA ON N'SQL16N1' WITH (ENDPOINT_URL = N'TCP://SQL16N1.AGDC.COM:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SEEDING_MODE=AUTOMATIC),
N'SQL16N2' WITH (ENDPOINT_URL = N'TCP://SQL16N2.AGDC.COM:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SEEDING_MODE=AUTOMATIC);
GO
With the empty availability group created, connect to SQL Server that is your secondary replica and join the availability group. Then, configure the newly joined replica permissions to create database:
:Connect SQL16N2
ALTER AVAILABILITY GROUP [SEEDag] JOIN
GO:Connect SQL16N2
ALTER AVAILABILITY GROUP SEEDag
GRANT CREATE ANY DATABASE
GO
On the primary replica, add databases to your availability group. When you add the database to the availability group, seeding of the database to the secondary replica(s) commences. When complete, these databases should appear on the primary and secondary as part of the availability group.
:Connect SQL16N1
alter AVAILABILITY group SEEDag add database db1
go
alter AVAILABILITY group SEEDag add database db2
go
Troubleshooting Direct Seeding
Monitor and investigate automatic availability replica seeding using new DMVs, XEvents and the SQL Server error log.
Direct Seeding DMVs
Two new DMVs report information on direct seeding and each report unique information. Query them at the primary and the secondary when trouble-shooting seeding failures, since the errors may be reported more accurately at the replica where the failure occurred.
Query sys.dm_hadr_automatic_seeding for information on direct seeding Here you can find information on successful or failed database seedings and error messages explaining why the seeding may have failed.
select * from sys.dm_hadr_automatic_seeding
Query sys.dm_hadr_physical_seeding_stats for statistical information on completed and ongoing availability databases seeding You can get information on currently running seedings like estimated completion time, and also statistics on completed seedings.
select * from sys.dm_hadr_physical_seeding_stats
Here is a sample of the output when this is queried during an automatic seed.
Diagnose Database Initialization Using direct seeding in the SQL Server error log
When you add a database to your availability group configured for direct seeding, a VDI backup is performed over the availability group endpoint connection that is used for synchronization. The SQL Server error log can be reviewed to find information on when the backup completed and the secondary was synchronized:
Primary replica SQL Server error log
2016-04-27 17:32:02.61 spid38s DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 0883A09E-7587-42D7-A6E6-D7EBF96079F4:0
2016-04-27 17:32:02.63 spid38s DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 0883A09E-7587-42D7-A6E6-D7EBF96079F4:0
2016-04-27 17:32:02.89 spid99s Processed 296 pages for database 'db1', file 'db1' on file 1.
2016-04-27 17:32:02.96 spid99s Processed 13 pages for database 'db1', file 'db1_log' on file 1.
2016-04-27 17:32:03.10 Backup Database backed up. Database: db1, creation date(time): 2016/04/27(15:40:44), pages dumped: 530, first LSN: 34:112:65, last LSN: 34:320:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{840AB572-75B6-4577-894C-85505ABAC89A}'}). This is an informational message only. No user action is required.
2016-04-27 17:32:03.16 spid99s BACKUP DATABASE successfully processed 309 pages in 0.347 seconds (6.956 MB/sec).
2016-04-27 17:32:03.16 Backup BACKUP DATABASE successfully processed 309 pages in 0.347 seconds (6.956 MB/sec).
2016-04-27 17:32:03.98 spid96s DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 0883A09E-7587-42D7-A6E6-D7EBF96079F4:0
2016-04-27 17:32:03.98 spid96s Always On Availability Groups connection with secondary database established for primary database 'db1' on the availability replica 'SQL16N2' with Replica ID: {0883a09e-7587-42d7-a6e6-d7ebf96079f4}. This is an informational message only. No user action is required.
2016-04-27 17:32:04.00 spid96s DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 0883A09E-7587-42D7-A6E6-D7EBF96079F4:1
2016-04-27 17:32:04.01 spid96s DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 0883A09E-7587-42D7-A6E6-D7EBF96079F4:1
2016-04-27 17:32:04.31 spid96s DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 0883A09E-7587-42D7-A6E6-D7EBF96079F4:1
2016-04-27 17:32:04.31 spid96s DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 0883A09E-7587-42D7-A6E6-D7EBF96079F4:1
2016-04-27 17:32:04.32 spid96s DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 0883A09E-7587-42D7-A6E6-D7EBF96079F4:1
2016-04-27 17:32:04.32 spid96s DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 0883A09E-7587-42D7-A6E6-D7EBF96079F4:1
2016-04-27 17:32:04.32 spid96s Always On Availability Groups connection with secondary database established for primary database 'db1' on the availability replica 'SQL16N2' with Replica ID: {0883a09e-7587-42d7-a6e6-d7ebf96079f4}. This is an informational message only. No user action is required.
On the secondary replica we see the database restored and the hardened LSN for the database on the secondary is reported.
Secondary replica SQL Server error log
2016-04-27 17:32:02.65 spid69s Error: 911, Severity: 16, State: 1.
2016-04-27 17:32:02.65 spid69s Database 'db1' does not exist. Make sure that the name is entered correctly.
2016-04-27 17:32:03.41 spid69s Processed 296 pages for database 'db1', file 'db1' on file 1.
2016-04-27 17:32:03.41 spid69s Processed 13 pages for database 'db1', file 'db1_log' on file 1.
2016-04-27 17:32:03.53 spid69s [INFO] HkHostDbCtxt::Initialize(): Database ID: [5] 'db1'. XTP Engine version is 0.0.
2016-04-27 17:32:03.53 spid69s Starting up database 'db1'.
2016-04-27 17:32:03.57 spid69s [INFO] HkHostDbCtxt::Initialize(): Database ID: [5] 'db1'. XTP Engine version is 0.0.
2016-04-27 17:32:03.60 spid69s The database 'db1' is marked RESTORING and is in a state that does not allow recovery to be run.
2016-04-27 17:32:03.91 Backup Database was restored: Database: db1, creation date(time): 2016/04/27(15:40:44), first LSN: 34:112:65, last LSN: 34:320:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{0657A8FD-46D1-44FF-9A74-9C12D9D5E152}'}). Informational message. No user action required.
2016-04-27 17:32:03.95 spid69s RESTORE DATABASE successfully processed 309 pages in 0.103 seconds (23.437 MB/sec).
2016-04-27 17:32:03.95 Backup RESTORE DATABASE successfully processed 309 pages in 0.103 seconds (23.437 MB/sec).
2016-04-27 17:32:04.02 spid69s Always On Availability Groups connection with primary database established for secondary database 'db1' on the availability replica 'SQL16N1' with Replica ID: {12dc5154-baa8-475d-b2ff-bf093c6f97eb}. This is an informational message only. No user action is required.
2016-04-27 17:32:04.04 spid47s State information for database 'db1' - Hardened Lsn: '(34:320:1)' Commit LSN: '(0:0:0)' Commit Time: 'Jan 1 1900 12:00AM'
Here is the error log from the secondary for a failed direct seeding The failure occurred because the path to the database and log files on the primary did not exist on the secondary, resulting in failed seeding:
2016-04-27 18:18:29.57 spid58s Starting up database 'db1'.
2016-04-27 18:18:29.62 spid58s [INFO] HkHostDbCtxt::Initialize(): Database ID: [6] 'db1'. XTP Engine version is 0.0.
2016-04-27 18:18:29.65 spid58s The database 'db1' is marked RESTORING and is in a state that does not allow recovery to be run.
2016-04-27 18:18:29.66 spid40s The state of the local availability replica in availability group 'SEEDag' has changed from 'RESOLVING_NORMAL' to 'NOT_AVAILABLE'. The state changed because either the associated availability group has been deleted, or the local availability replica has been removed from another SQL Server instance. For more information, see the SQL Server error log, Windows Server Failover Clustering (WSFC) management console, or WSFC log.
2016-04-27 18:19:34.22 spid57 The state of the local availability replica in availability group 'SEEDag' has changed from 'NOT_AVAILABLE' to 'RESOLVING_NORMAL'. The state changed because the local availability replica is joining the availability group. For more information, see the SQL Server error log, Windows Server Failover Clustering (WSFC) management console, or WSFC log.
2016-04-27 18:19:34.26 spid37s The state of the local availability replica in availability group 'SEEDag' has changed from 'RESOLVING_NORMAL' to 'SECONDARY_NORMAL'. The state changed because the availability group state has changed in Windows Server Failover Clustering (WSFC). For more information, see the SQL Server error log, Windows Server Failover Clustering (WSFC) management console, or WSFC log.
2016-04-27 18:19:34.37 spid36s A connection for availability group 'SEEDag' from availability replica 'SQL16N2\STAND' with id [B4B833CB-032E-45D6-9CEB-CB8C34C61224] to 'SQL16N1' with id [DC980B48-B7CE-41AC-A54B-EB67E6F1B98F] has been successfully established. This is an informational message only. No user action is required.
2016-04-27 18:19:44.68 spid47s Error: 911, Severity: 16, State: 1.
2016-04-27 18:19:44.68 spid47s Database 'db1' does not exist. Make sure that the name is entered correctly.
2016-04-27 18:19:44.91 spid47s Error: 5133, Severity: 16, State: 1.
2016-04-27 18:19:44.91 spid47s Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\db1.mdf" failed with the operating system error 2(The system cannot find the file specified.).
2016-04-27 18:19:44.91 spid47s Error: 3156, Severity: 16, State: 3.
2016-04-27 18:19:44.91 spid47s File 'db1' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\db1.mdf'. Use WITH MOVE to identify a valid location for the file.
Diagnose direct seeding with extended events
Direct seeding has new XEvents for tracking state change, failures, and performance statistics as an initialization is progressing.
Here is a script to create an XEvent session that captures this event.
CREATE EVENT SESSION [AlwaysOn_autoseed] ON SERVER
ADD EVENT sqlserver.hadr_automatic_seeding_state_transition,
ADD EVENT sqlserver.hadr_automatic_seeding_timeout,
ADD EVENT sqlserver.hadr_db_manager_seeding_request_msg,
ADD EVENT sqlserver.hadr_physical_seeding_backup_state_change,
ADD EVENT sqlserver.hadr_physical_seeding_failure,
ADD EVENT sqlserver.hadr_physical_seeding_forwarder_state_change,
ADD EVENT sqlserver.hadr_physical_seeding_forwarder_target_state_change,
ADD EVENT sqlserver.hadr_physical_seeding_progress,
ADD EVENT sqlserver.hadr_physical_seeding_restore_state_change,
ADD EVENT sqlserver.hadr_physical_seeding_submit_callback
ADD TARGET package0.event_file(SET filename=N'autoseed.xel',max_file_size=(5),max_rollover_files=(4))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
ALTER EVENT SESSION AlwaysOn_autoseed ON SERVER STATE=START
GO
Here are a list of useful direct seeding XEvents.
| name | description |
| hadr_db_manager_seeding_request_msg | Seeding request message. |
| hadr_physical_seeding_backup_state_change | Physical Seeding Backup Side State Change. |
| hadr_physical_seeding_restore_state_change | Physical Seeding Restore Side State Change. |
| hadr_physical_seeding_forwarder_state_change | Physical Seeding Forwarder Side State Change. |
| hadr_physical_seeding_forwarder_target_state_change | Physical Seeding Forwarder Target Side State Change. |
| hadr_physical_seeding_submit_callback | Physical Seeding Submit Callback Event. |
| hadr_physical_seeding_failure | Physical Seeding Failure Event. |
| hadr_physical_seeding_progress | Physical Seeding Progress Event. |
| hadr_physical_seeding_schedule_long_task_failure | Physical Seeding Schedule Long Task Failure Event. |
| hadr_automatic_seeding_start | Occurs when an automatic seeding operation is submitted. |
| hadr_automatic_seeding_state_transition | Occurs when an automatic seeding operation changes state. |
| hadr_automatic_seeding_success | Occurs when an automatic seeding operation succeeds. |
| hadr_automatic_seeding_failure | Occurs when an automatic seeding operation fails. |
| hadr_automatic_seeding_timeout | Occurs when an automatic seeding operation times out. |
XEvent Comments
- Collect ‘backup’ XEvents on the primary, and collect ‘restore’ XEvents on the secondary.
- The hadr_automatic_seeding_state_transition XEvent occurs on the primary and secondary and report previous and current states to document state transition.
- The hadr_physical_seeding_progress XEvent reports performance information related to the seeding.
- When trouble-shooting failures and performance, the DMVs may be a better first step for diagnosis.
- Be careful when collecting XEvents, some may generate numbers of events that can impact SQL Server performance.
FAQs
I used direct seeding mode to start initializing my secondary, when will it be complete?
Query the sys.dm_hadr_physical_seeding_stats DMV for status. One row for each database is listed. Here is a query that returns the data points from the DMV that might be useful when monitoring progress.
select local_database_name, role_desc, internal_state_desc, transfer_rate_bytes_per_second, transferred_size_bytes, database_size_bytes,
start_time_utc, end_time_utc, estimate_time_complete_utc, total_disk_io_wait_time_ms, total_network_wait_time_ms, is_compression_enabled
from sys.dm_hadr_physical_seeding_stats
I setup direct seeding and added a database at the primary replica, but the database does not appear as part of the availability group at the primary or the secondary in SQL Server Management Studio or in the availability group dashboard
If you add a database and it fails to appear as part of the availability group, direct seeding likely failed, which prevents addition of the database at the primary and secondary.
Query sys.dm_hadr_automatic_seeding at the primary and secondary. Here I queried it at the primary to find out why direct seeding failed:
select start_time, completion_time, is_source, current_state, failure_state, failure_state_desc, error_code from sys.dm_hadr_automatic_seeding
I want to direct seeding to a secondary, but the path to the database data and log files is different, will direct seeding succeed?
No, direct seeding assumes that the path to the data and log files on the primary exist on the secondary. If the paths do not exist, direct seeding will fail.