SQL Server AlwaysOn AG automatic Initialization (eight)

monitoring of automatic seed setting

• System dynamic management view

Sys.dm_hadr_automatic_seeding

on the primary replica, query sys.dm_hadr_automatic_seeding to check the status of the automatic seeding process. for each seeding process, the view returns a row. For example:

SELECT start_time, Completion_time, Is_source, Current_state, Failure_state, Failure_state_desc, Error_c Odefrom sys.dm_hadr_automatic_seeding

Sys.dm_hadr_physical_seeding_stats

on the primary replica, query the sys.dm_hadr_physical_seeding_stats DMV to see the physical statistics for each seeding process that is currently running. When seeding is running, the following query returns multiple rows:

SELECT Local_database_name, Role_desc, Internal_state_desc, Transfer_rate_bytes_per_second, Transferred_size_b Ytes, Database_size_bytes, START_TIME_UTC, END_TIME_UTC, ESTIMATE_TIME_COMPLETE_UTC, total_disk_io_wait_time_m S, Total_network_wait_time_ms, Is_compression_enabledfrom sys.dm_hadr_physical_seeding_stats

• Error log

• Extended Events

The following table lists the extended events related to automatic seeding:

Property	Describe
hadr_db_manager_seeding_request_msg	Seeding Request message
Hadr_physical_seeding_backup_state_change	physical seed set backup side state change
Hadr_physical_seeding_restore_state_change	physical seed set restore end state change
Hadr_physical_seeding_forwarder_state_change	physical seed Set transponder end state change
Hadr_physical_seeding_forwarder_target_state_change	physical seed Set transponder target State change
Hadr_physical_seeding_submit_callback	physical seeding Commit callback event
hadr_physical_seeding_failure	physical seed set failure event
hadr_physical_seeding_progress	Physical seeding Progress event
hadr_physical_seeding_schedule_long_task_failure	Physical seed set scheduled long task failure event
Hadr_automatic_seeding_start	Occurs when automatic seeding operation is committed
hadr_automatic_seeding_state_transition	Occurs when automatic seeding operation changes state
hadr_automatic_seeding_success	Occurs when the automatic seeding operation succeeds
hadr_automatic_seeding_failure	Occurs when automatic seeding operation fails
Hadr_automatic_seeding_timeout	Occurs when an automatic seeding operation times out

Create an extended event

Create event session [directseed] on serveradd event sqlserver.hadr_ar_ Controller_debug (    action (Sqlserver.database_id,sqlserver.sql_text,sqlserver.tsql_stack) ), Add event sqlserver.hadr_automatic_seeding_failure (    action ( Sqlserver.database_id,sqlserver.sql_text,sqlserver.tsql_stack)), Add event sqlserver.hadr_automatic_ Seeding_start (    action (sqlserver.database_id,sqlserver.sql_text,sqlserver.tsql_stack)), Add event sqlserver.hadr_automatic_seeding_state_transition (    action ( Sqlserver.database_id,sqlserver.sql_text,sqlserver.tsql_stack)), Add event sqlserver.hadr_automatic_ Seeding_success (    action (Sqlserver.database_id,sqlserver.sql_text,sqlserver.tsql_stack)) , Add event sqlserver.hadr_automatic_seeding_timeout (    action ( Sqlserver.database_id,sqlserver.sql_text,sqlserver.tsql_staCK)) Add target package0.event_file (set filename=n ' C:\XE\DirectSeed.xel ', max_rollover_files= (10) ) go create event session [physicalseed] on serveradd event  Sqlserver.hadr_physical_seeding_backup_state_change (    action (sqlserver.database_id, Sqlserver.sql_text,sqlserver.tsql_stack)), Add event sqlserver.hadr_physical_seeding_failure (     action (sqlserver.database_id,sqlserver.sql_text,sqlserver.tsql_stack)),ADD EVENT  Sqlserver.hadr_physical_seeding_forwarder_state_change (    action (sqlserver.database_id, Sqlserver.sql_text,sqlserver.tsql_stack)), Add event sqlserver.hadr_physical_seeding_forwarder_target _state_change (    action (sqlserver.database_id,sqlserver.sql_text,sqlserver.tsql_stack)), Add event sqlserver.hadr_physical_seeding_progress (    action (sqlserver.database _id,sqlserver.sql_text,sqlserver. Tsql_stack)), Add event sqlserver.hadr_physical_seeding_restore_state_change (     action (sqlserver.database_id,sqlserver.sql_text,sqlserver.tsql_stack)),add event  Sqlserver.hadr_physical_seeding_schedule_long_task_failure (    action (sqlserver.database_ Id,sqlserver.sql_text,sqlserver.tsql_stack)), Add event sqlserver.hadr_physical_seeding_submit_ Callback (    action (sqlserver.database_id,sqlserver.sql_text,sqlserver.tsql_stack)) ADD  target package0.event_file (set filename=n ' C:\XE\PhysicalSeed ', max_rollover_files= (Ten)) GO  alter event session [directseed] on server state = startalter  Event session [physicalseed] on server state = start

Analyze Extended Events

if object_id (' tempdb.. #DirectSeed ')  is not null   drop table [#DirectSeed]; create  table [#DirectSeed]        (          [id] int identity (1, 1)                    NOT NULL ,          [eventxml] xml ,         constraint  [PK_DirectSeed] PRIMARY KEY CLUSTERED  ( [ID] )           insert  [#DirectSeed]         (  [EventXML] ) Select  convert (Xml, [event_data])  AS [EventXML]FROM     [sys]. [Fn_xe_file_target_read_file] (' C:\xe\directseed*.xel ',  null, null, null) &nBsp create primary xml index [directseedxml] on [#DirectSeed] ([EventXML]);  create xml index [directseedxmlpath] on [#DirectSeed] ([eventxml]) using xml  INDEX [DIRECTSEEDXML] FOR VALUE; SELECT[DS]. [Eventxml]. [Value] (' (/event/@name) [1] ',  ' VARCHAR (MAX) ')  AS [event_name],                      [DS]. [Eventxml]. [Value] (' (/event/@timestamp) [1] ',  ' DATETIME2 (7) ')  as [event_time],[ds]. [Eventxml]. [Value] (' (/event/data[@name = "Debug_message"]/value) [1] ',  ' VARCHAR (8000) ')  as [debug_message],/*hadr_ AUTOMATIC_SEEDING_STATE_TRANSITION*/[DS]. [Eventxml]. [Value] (' (/event/data[@name = "Previous_state"]/value) [1] ',  ' VARCHAR (8000) ')  as [previous_state],[ds]. [Eventxml]. [Value] (' (/event/data[@name = "Current_state"]/value) [1] ',  ' VARCHAR (8000) ')  as [currENT_STATE],/*HADR_AUTOMATIC_SEEDING_START*/[DS]. [Eventxml]. [Value] (' (/event/data[@name = "Operation_attempt_number"]/value) [1] ',  ' BIGINT ')  as [operation_attempt_ NUMBER],[DS]. [Eventxml]. [Value] (' (/event/data[@name = "ag_id"]/value) [1] ',  ' VARCHAR (8000) ')  as [ag_id],[ds]. [Eventxml]. [Value] (' (/event/data[@name = "ag_db_id"]/value) [1] ',  ' VARCHAR (8000) ')  as [ag_id],[ds]. [Eventxml]. [Value] (' (/event/data[@name = "ag_remote_replica_id"]/value) [1] ',  ' VARCHAR (8000) ')  as [ag_remote_replica _ID],/*HADR_AUTOMATIC_SEEDING_SUCCESS*/[DS]. [Eventxml]. [Value] (' (/event/data[@name = "required_seeding"]/value) [1] ',  ' VARCHAR (8000) ')  as [required_seeding],/* HADR_AUTOMATIC_SEEDING_TIMEOUT*/[DS]. [Eventxml]. [Value] (' (/event/data[@name = "Timeout_ms"]/value) [1] ',  ' BIGINT ')  as [timeout_ms],/*hadr_automatic_ SEEDING_FAILURE*/[DS]. [Eventxml]. [Value] (' (/event/data[@name = "Failure_state"]/value) [1] ',  ' BIGINT ')  as [failure_state],[DS]. [Eventxml]. [Value] (' (/event/data[@name = "Failure_state_desc"]/value) [1] ',  ' VARCHAR (8000) ')  as [failure_state_desc] from [#DirectSeed] as [ds]order by [ds]. [Eventxml]. [Value] (' (/event/@timestamp) [1] ',  ' DATETIME2 (7) ')  desc

SQL Server AlwaysOn AG automatic Initialization (eight)