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

image

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.

image

 

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

image

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.

For more information see the following resources:

CREATE AVAILABILITY GROUP (Transact-SQL)


Comments (0)

Skip to main content