Pár napja megjelent az újabb SQL Server Management Studio, amiben van néhány újdonság (a teljesség igénye nélkül):
- XE Profiler mostantól XEvent Profiler
- Új ikonok a futási terveknél
- Availability Group Latency Report
- Vulnerability Assessment
XEvent Profiler
Az átnevezésen túl van egy nagyon fontos újdonság. Az előző verzióban, amikor elindítottam, majd bezártam a Live Data ablakot, az Extended Event session továbbra is futott, azt kézzel le kellett állítani. Mostantól, amint bezártam a Live Data ablakot, már leállítja az adatgyűjtést.
Új ikonok a futási terveknél
Eddig, kb. ilyen ikonok voltak (SSMS 17.3-ig beleértve az előző verziókat is)
Mostantól az alábbi, új ikonokkal kell barátkozni. Nem olyan rosszak, de egy csomó prezentációmban frissíteni kell majd :)
Availability Group Latency Report
Ezzel egy kicsit mélyebben is foglalkoznék. Alapvetően ez arra hivatott, hogy lássuk, hogy mennyire van "elcsúszva" a secondary replica, illetve mekkora késleltetéssel működik az AG. Hogyan érhető el? Az SSMS-ben az AG Dashboard oldalon lehet bekapcsolni az adatgyűjtést. Az AG Dashboard az AG neén jobb gomb megnyomása utána a Show Dashboard menüponttal jeleníthető meg:
Miután megjelent a jobb oldalon lesz egy Collect Latency Data menüpont, amennyiben Windows felhasználóval csatlakoztunk! Sajnos az SQL loginok esetén ez nincs engedélyezve, de működhet ettől függetlenül :). Nem kell hozzá mást tenni, mint létre kell hozni az ehhez szükséges XE session-t és az Agent Job-ot. Ugyanis ez áll e mögött, így nem akadályoz meg nagyon abban, hogy ezt ne csak Windows felhasználóval érjem el.
AG Latency XE Session
Az XE Session kódja az alábbi:
IF EXISTS (select * from sys.server_event_sessions
WHERE name = N'AlwaysOn_Data_Movement_Tracing')
BEGIN
DROP EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER
END
GO
CREATE EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER
ADD EVENT sqlserver.hadr_apply_log_block,
ADD EVENT sqlserver.hadr_capture_log_block,
ADD EVENT sqlserver.hadr_database_flow_control_action,
ADD EVENT sqlserver.hadr_db_commit_mgr_harden,
ADD EVENT sqlserver.hadr_log_block_send_complete,
ADD EVENT sqlserver.hadr_send_harden_lsn_message,
ADD EVENT sqlserver.hadr_transport_flow_control_action,
ADD EVENT sqlserver.log_flush_complete,
ADD EVENT sqlserver.log_flush_start,
ADD EVENT sqlserver.recovery_unit_harden_log_timestamps,
ADD EVENT sqlserver.log_block_pushed_to_logpool,
ADD EVENT sqlserver.hadr_transport_receive_log_block_message,
ADD EVENT sqlserver.hadr_receive_harden_lsn_message,
ADD EVENT sqlserver.hadr_log_block_group_commit,
ADD EVENT sqlserver.hadr_log_block_compression,
ADD EVENT sqlserver.hadr_log_block_decompression,
ADD EVENT sqlserver.hadr_lsn_send_complete,
ADD EVENT sqlserver.hadr_capture_filestream_wait,
ADD EVENT sqlserver.hadr_capture_vlfheader
ADD TARGET package0.event_file(SET filename=N'AlwaysOn_Data_Movement_Tracing.xel',max_file_size=(25),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_Data_Movement_Tracing] ON SERVER STATE = START
GO
Agent job
Szerencsére az XE Session-t nem kell manuálisan létrehozni, hisz az Agent job tartalmazza ezt is. Csak ezt kell létrehozni, ami utána a következőket csinálja:
- Collect AG Information: ez a tempdb-ben létrehozza a szükséges objektumokat és az AG alapvető információit is elmenti.
- Create XE Session: ez létrehozza az XE adatgyűjtést.
- Wait For Collection: 2 percet várakoztatja az Agent job-ot.
- End XE Session: leállítja az XE adatgyűjtést.
- Extract XE Data: az összegyűjtött adatokból, a tempdb adatbázisba kirakja táblákba a riporthoz szükséges értékeket.
- Create Result Set: megcsinálja az adatbázisonkénti összesítést.
A teljes Agent job script alább megtalálható. Fontos, hogy az elején megadott változóknak a megfelelő értékeket meg kell adni, illetve SQLCMD módban kell futtatni:
- JobOwner változó: ez lesz az Agent Job owner-e, itt most az sa-t adtam meg. Ezt mindenki mérlegelje maga, hogy mi legyen.
- AGName változó: annak az Availability Group-nak a neve, amit mérni szeretnénk.
A job neve AlwaysOn_Latency_Data_Collection lesz. A kód SQL Server 2014-ig visszamenőleg működik (legalábbis SQL Server 2014 SP2-ig biztosan)
-- Switch to SQLCMD mode by using ALT+Q+M
:SETVAR JobOwner sa
:SETVAR AGName SQLAG
USE [msdb]
GO
/****** Object: Job [AlwaysOn_Latency_Data_Collection] ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'AlwaysOn_Latency_Data_Collection',
@enabled=0,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'$(JobOwner)', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Collect AG Information] ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Collect AG Information',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N' USE TEMPDB
IF OBJECT_ID(''AGInfo'') IS NOT NULL
BEGIN
DROP TABLE AGInfo
END
IF OBJECT_ID(''LatencyCollectionStatus'') IS NOT NULL
BEGIN
DROP TABLE LatencyCollectionStatus
END
CREATE TABLE LatencyCollectionStatus(
[collection_status] [NVARCHAR](60) NULL,
[start_timestamp] [DATETIMEOFFSET] NULL,
[startutc_timestamp] [DATETIMEOFFSET] NULL
)
INSERT INTO LatencyCollectionStatus(collection_status, start_timestamp, startutc_timestamp) values (''Started'', GETDATE(), GETUTCDATE())
SELECT
AGC.name as agname
, RCS.replica_server_name as replica_name
, ARS.role_desc as agrole
INTO AGInfo
FROM
sys.availability_groups_cluster AS AGC
INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
ON
RCS.group_id = AGC.group_id
INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
ON
ARS.replica_id = RCS.replica_id
where AGC.name = N''$(AGName)''',
@database_name=N'tempdb',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Create XE Session] ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Create XE Session',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'IF EXISTS (select * from sys.server_event_sessions
WHERE name = N''AlwaysOn_Data_Movement_Tracing'')
BEGIN
DROP EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER
END
CREATE EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER ADD EVENT sqlserver.hadr_apply_log_block,
ADD EVENT sqlserver.hadr_capture_log_block,
ADD EVENT sqlserver.hadr_database_flow_control_action,
ADD EVENT sqlserver.hadr_db_commit_mgr_harden,
ADD EVENT sqlserver.hadr_log_block_send_complete,
ADD EVENT sqlserver.hadr_send_harden_lsn_message,
ADD EVENT sqlserver.hadr_transport_flow_control_action,
ADD EVENT sqlserver.log_flush_complete,
ADD EVENT sqlserver.log_flush_start,
ADD EVENT sqlserver.recovery_unit_harden_log_timestamps,
ADD EVENT sqlserver.log_block_pushed_to_logpool,
ADD EVENT sqlserver.hadr_transport_receive_log_block_message,
ADD EVENT sqlserver.hadr_receive_harden_lsn_message,
ADD EVENT sqlserver.hadr_log_block_group_commit,
ADD EVENT sqlserver.hadr_log_block_compression,
ADD EVENT sqlserver.hadr_log_block_decompression,
ADD EVENT sqlserver.hadr_lsn_send_complete,
ADD EVENT sqlserver.hadr_capture_filestream_wait,
ADD EVENT sqlserver.hadr_capture_vlfheader ADD TARGET package0.event_file(SET filename=N''AlwaysOn_Data_Movement_Tracing.xel'',max_file_size=(25),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)
ALTER EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER STATE = START',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Wait For Collection] ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Wait For Collection',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'WAITFOR DELAY ''00:2:00''
GO',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [End XE Session] ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'End XE Session',
@step_id=4,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'ALTER EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER STATE = STOP',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Extract XE Data] ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Extract XE Data',
@step_id=5,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'
BEGIN TRANSACTION
USE TEMPDB
IF OBJECT_ID(''#EventXml'') IS NOT NULL
BEGIN
DROP TABLE #EventXml
END
SELECT
xe.event_name,
CAST(xe.event_data AS XML) AS event_data
INTO #EventXml
FROM
(
SELECT
object_name AS event_name,
CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file(
''AlwaysOn_Data_Movement_Tracing*.xel'',
NULL, NULL, NULL)
WHERE object_name IN (''hadr_log_block_group_commit'',
''log_block_pushed_to_logpool'',
''log_flush_start'',
''log_flush_complete'',
''hadr_log_block_compression'',
''hadr_capture_log_block'',
''hadr_capture_filestream_wait'',
''hadr_log_block_send_complete'',
''hadr_receive_harden_lsn_message'',
''hadr_db_commit_mgr_harden'',
''recovery_unit_harden_log_timestamps'',
''hadr_capture_vlfheader'',
''hadr_log_block_decompression'',
''hadr_apply_log_block'',
''hadr_send_harden_lsn_message'',
''hadr_log_block_decompression'',
''hadr_lsn_send_complete'',
''hadr_transport_receive_log_block_message'')
) xe
IF OBJECT_ID(''DMReplicaEvents'') IS NOT NULL
BEGIN
DROP TABLE DMReplicaEvents
END
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE DMReplicaEvents(
[server_name] [NVARCHAR](128) NULL,
[event_name] [NVARCHAR](60) NOT NULL,
[log_block_id] [BIGINT] NULL,
[database_id] [INT] NULL,
[processing_time] [BIGINT] NULL,
[start_timestamp] [BIGINT] NULL,
[publish_timestamp] [DATETIMEOFFSET] NULL,
[log_block_size] [BIGINT] NULL,
[target_availability_replica_id] [UNIQUEIDENTIFIER] NULL,
[local_availability_replica_id] [UNIQUEIDENTIFIER] NULL,
[database_replica_id] [UNIQUEIDENTIFIER] NULL,
[mode] [BIGINT] NULL,
[availability_group_id] [UNIQUEIDENTIFIER] NULL,
[pending_writes] [BIGINT] NULL
)
IF OBJECT_ID(''LatencyResults'') IS NOT NULL
BEGIN
DROP TABLE LatencyResults
END
CREATE TABLE LatencyResults(
[event_name] [NVARCHAR](60) NOT NULL,
[processing_time] [BIGINT] NULL,
[publish_timestamp] [DATETIMEOFFSET] NULL,
[server_commit_mode] [NVARCHAR](60) NULL
)
INSERT INTO DMReplicaEvents
SELECT
@@SERVERNAME AS server_name,
xe.event_name,
AoData.value(''(data[@name="log_block_id"]/value)[1]'', ''BIGINT'') AS log_block_id,
NULL AS database_id,
AoData.value(''(data[@name="total_processing_time"]/value)[1]'', ''BIGINT'') AS processing_time,
AoData.value(''(data[@name="start_timestamp"]/value)[1]'', ''BIGINT'') AS start_timestamp,
CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 75, 24) AS DATETIMEOFFSET) AS publish_timestamp,
AoData.value(''(data[@name="log_block_size"]/value)[1]'', ''BIGINT'') AS log_block_size,
NULL AS target_availability_replica_id,
NULL AS local_availability_replica_id,
NULL AS database_replica_id,
NULL AS mode,
NULL AS availability_group_id,
NULL AS pending_writes
FROM #EventXml AS xe
CROSS APPLY xe.event_data.nodes(''/event'') AS T(AoData)
WHERE xe.event_name = ''hadr_log_block_send_complete''
GO
INSERT INTO DMReplicaEvents
SELECT
@@SERVERNAME AS server_name,
xe.event_name,
AoData.value(''(data[@name="log_block_id"]/value)[1]'', ''BIGINT'') AS log_block_id,
AoData.value(''(data[@name="database_id"]/value)[1]'', ''INT'') AS database_id,
AoData.value(''(data[@name="duration"]/value)[1]'', ''BIGINT'') AS processing_time,
AoData.value(''(data[@name="start_timestamp"]/value)[1]'', ''BIGINT'') AS start_timestamp,
CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 65, 24) AS DATETIMEOFFSET) AS publish_timestamp,
NULL AS log_block_size,
NULL AS target_availability_replica_id,
NULL AS local_availability_replica_id,
NULL AS database_replica_id,
NULL AS mode,
NULL AS availability_group_id,
AoData.value(''(data[@name="pending_writes"]/value)[1]'',''BIGINT'') AS pending_writes
FROM #EventXml AS xe
CROSS APPLY xe.event_data.nodes(''/event'') AS T(AoData)
WHERE xe.event_name = ''log_flush_complete''
GO
INSERT INTO DMReplicaEvents
SELECT
@@SERVERNAME AS server_name,
xe.event_name,
NULL AS log_block_id,
AoData.value(''(data[@name="database_id"]/value)[1]'', ''BIGINT'') AS database_id,
AoData.value(''(data[@name="time_to_commit"]/value)[1]'', ''BIGINT'') AS processing_time,
NULL AS start_timestamp,
CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 72, 24) AS DATETIMEOFFSET) AS publish_timestamp,
NULL AS log_block_size,
AoData.value(''(data[@name="replica_id"]/value)[1]'', ''UNIQUEIDENTIFIER'') AS target_availability_replica_id,
NULL AS local_availability_replica_id,
AoData.value(''(data[@name="ag_database_id"]/value)[1]'', ''UNIQUEIDENTIFIER'') AS database_replica_id,
NULL AS mode,
AoData.value(''(data[@name="group_id"]/value)[1]'',''UNIQUEIDENTIFIER'') AS availability_group_id,
NULL AS pending_writes
FROM #EventXml AS xe
CROSS APPLY xe.event_data.nodes(''/event'') AS T(AoData)
WHERE xe.event_name = ''hadr_db_commit_mgr_harden''
GO
INSERT INTO DMReplicaEvents
SELECT
@@SERVERNAME AS server_name,
xe.event_name,
AoData.value(''(data[@name="log_block_id"]/value)[1]'', ''BIGINT'') AS log_block_id,
AoData.value(''(data[@name="database_id"]/value)[1]'', ''BIGINT'') AS database_id,
AoData.value(''(data[@name="processing_time"]/value)[1]'', ''BIGINT'') AS processing_time,
AoData.value(''(data[@name="start_timestamp"]/value)[1]'', ''BIGINT'') AS start_timestamp,
CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 82, 24) AS DATETIMEOFFSET) AS publish_timestamp,
NULL AS log_block_size,
NULL AS target_availability_replica_id,
NULL AS local_availability_replica_id,
NULL AS database_replica_id,
NULL AS mode,
NULL AS availability_group_id,
NULL AS pending_writes
FROM #EventXml AS xe
CROSS APPLY xe.event_data.nodes(''/event'') AS T(AoData)
WHERE xe.event_name = ''recovery_unit_harden_log_timestamps''
GO
INSERT INTO DMReplicaEvents
SELECT
@@SERVERNAME AS server_name,
xe.event_name,
AoData.value(''(data[@name="log_block_id"]/value)[1]'', ''BIGINT'') AS log_block_id,
AoData.value(''(data[@name="database_id"]/value)[1]'', ''BIGINT'') AS database_id,
AoData.value(''(data[@name="processing_time"]/value)[1]'', ''BIGINT'') AS processing_time,
AoData.value(''(data[@name="start_timestamp"]/value)[1]'', ''BIGINT'') AS start_timestamp,
CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 73, 24) AS DATETIMEOFFSET) AS publish_timestamp,
AoData.value(''(data[@name="uncompressed_size"]/value)[1]'', ''INT'') AS log_block_size,
AoData.value(''(data[@name="availability_replica_id"]/value)[1]'', ''UNIQUEIDENTIFIER'') AS target_availability_replica_id,
NULL AS local_availability_replica_id,
NULL AS database_replica_id,
NULL AS mode,
NULL AS availability_group_id,
NULL AS pending_writes
FROM #EventXml AS xe
CROSS APPLY xe.event_data.nodes(''/event'') AS T(AoData)
WHERE xe.event_name = ''hadr_log_block_compression''
GO
INSERT INTO DMReplicaEvents
SELECT
@@SERVERNAME AS server_name,
xe.event_name,
AoData.value(''(data[@name="log_block_id"]/value)[1]'', ''BIGINT'') AS log_block_id,
AoData.value(''(data[@name="database_id"]/value)[1]'', ''BIGINT'') AS database_id,
AoData.value(''(data[@name="processing_time"]/value)[1]'', ''BIGINT'') AS processing_time,
AoData.value(''(data[@name="start_timestamp"]/value)[1]'', ''BIGINT'') AS start_timestamp,
CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 75, 24) AS DATETIMEOFFSET) AS publish_timestamp,
AoData.value(''(data[@name="uncompressed_size"]/value)[1]'', ''BIGINT'') AS log_block_size,
AoData.value(''(data[@name="availability_replica_id"]/value)[1]'', ''UNIQUEIDENTIFIER'') AS target_availability_replica_id,
NULL AS local_availability_replica_id,
NULL AS database_replica_id,
NULL AS mode,
NULL AS availability_group_id,
NULL AS pending_writes
FROM #EventXml AS xe
CROSS APPLY xe.event_data.nodes(''/event'') AS T(AoData)
WHERE xe.event_name = ''hadr_log_block_decompression''
INSERT INTO DMReplicaEvents
SELECT
@@SERVERNAME AS server_name,
xe.event_name,
AoData.value(''(data[@name="log_block_id"]/value)[1]'', ''BIGINT'') AS log_block_id,
NULL AS database_id,
AoData.value(''(data[@name="total_sending_time"]/value)[1]'', ''BIGINT'') AS processing_time,
AoData.value(''(data[@name="start_timestamp"]/value)[1]'', ''BIGINT'') AS start_timestamp,
CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 69, 24) AS DATETIMEOFFSET) AS publish_timestamp,
NULL AS log_block_size,
NULL AS target_availability_replica_id,
NULL AS local_availability_replica_id,
NULL AS database_replica_id,
NULL AS mode,
NULL AS availability_group_id,
NULL AS pending_writes
FROM #EventXml AS xe
CROSS APPLY xe.event_data.nodes(''/event'') AS T(AoData)
WHERE xe.event_name = ''hadr_lsn_send_complete''
INSERT INTO DMReplicaEvents
SELECT
@@SERVERNAME AS server_name,
xe.event_name,
AoData.value(''(data[@name="log_block_id"]/value)[1]'', ''BIGINT'') AS log_block_id,
NULL AS database_id,
AoData.value(''(data[@name="processing_time"]/value)[1]'', ''BIGINT'') AS processing_time,
AoData.value(''(data[@name="start_timestamp"]/value)[1]'', ''BIGINT'') AS start_timestamp,
CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 87, 24) AS DATETIMEOFFSET) AS publish_timestamp,
NULL AS log_block_size,
AoData.value(''(data[@name="target_availability_replica_id"]/value)[1]'', ''UNIQUEIDENTIFIER'') AS target_availability_replica_id,
AoData.value(''(data[@name="local_availability_replica_id"]/value)[1]'', ''UNIQUEIDENTIFIER'') AS local_availability_replica_id,
AoData.value(''(data[@name="target_availability_replica_id"]/value)[1]'', ''UNIQUEIDENTIFIER'') AS database_replica_id,
AoData.value(''(data[@name="mode"]/value)[1]'', ''BIGINT'') AS mode,
AoData.value(''(data[@name="availability_group_id"]/value)[1]'',''UNIQUEIDENTIFIER'') AS availability_group_id,
NULL AS pending_writes
FROM #EventXml AS xe
CROSS APPLY xe.event_data.nodes(''/event'') AS T(AoData)
WHERE xe.event_name = ''hadr_transport_receive_log_block_message''
DELETE
FROM DMReplicaEvents
WHERE CAST(publish_timestamp AS DATETIME) < DATEADD(minute, -2, CAST((SELECT MAX(publish_timestamp) from DMReplicaEvents) as DATETIME))
COMMIT
GO',
@database_name=N'tempdb',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Create Result Set] ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Create Result Set',
@step_id=6,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'
BEGIN TRANSACTION
USE TEMPDB
declare @ag_id as nvarchar(60)
declare @event as nvarchar(60)
set @ag_id = (select group_id from sys.availability_groups_cluster where name = N''$(AGName)'')
IF OBJECT_ID(''DbIdTable'') IS NOT NULL
BEGIN
DROP TABLE DbIdTable
END
CREATE TABLE DbIdTable(
[database_id] [INT] NULL
)
INSERT INTO DbIdTable
select distinct database_id from sys.dm_hadr_database_replica_states where group_id=@ag_id
delete from tempdb.dbo.DMReplicaEvents where not (availability_group_id = @ag_id or availability_group_id is NULL)
delete from tempdb.dbo.DMReplicaEvents where not (database_id in (select database_id from DbIdTable) or database_id is NULL)
set @event = ''availability_mode_desc''
INSERT INTO LatencyResults
select @event, NULL as processing_time, NULL as publish_timestamp, availability_mode_desc as server_commit_mode from sys.availability_replicas A
inner join
(select * from sys.dm_hadr_availability_replica_states) B
on A.replica_id = B.replica_id and A.group_id = @ag_id and A.replica_server_name = @@SERVERNAME
set @event = ''start_time''
INSERT INTO LatencyResults
select @event as event_name, NULL as processing_time, min(publish_timestamp) as publish_timestamp, NULL as server_commit_mode from tempdb.dbo.DMReplicaEvents
set @event = ''recovery_unit_harden_log_timestamps''
INSERT INTO LatencyResults
select @event, avg(processing_time), min(publish_timestamp) as publish_timestamp, NULL as server_commit_mode from DMReplicaEvents where event_name=''recovery_unit_harden_log_timestamps'' GROUP BY DATEPART(YEAR, publish_timestamp), DATEPART(MONTH, publish_timestamp), DATEPART(DAY, publish_timestamp), DATEPART(HOUR, publish_timestamp), DATEPART(MINUTE, publish_timestamp), DATEPART(SECOND, publish_timestamp)
set @event = ''avg_recovery_unit_harden_log_timestamps''
INSERT INTO LatencyResults
select @event as event_name,AVG(processing_time) as processing_time, NULL as publish_timestamp, NULL as server_commit_mode from tempdb.dbo.DMReplicaEvents where event_name=''recovery_unit_harden_log_timestamps''
set @event = ''hadr_db_commit_mgr_harden''
INSERT INTO LatencyResults
select @event, avg(processing_time), min(publish_timestamp) as publish_timestamp, NULL as server_commit_mode from DMReplicaEvents where event_name=''hadr_db_commit_mgr_harden'' GROUP BY DATEPART(YEAR, publish_timestamp), DATEPART(MONTH, publish_timestamp), DATEPART(DAY, publish_timestamp), DATEPART(HOUR, publish_timestamp), DATEPART(MINUTE, publish_timestamp), DATEPART(SECOND, publish_timestamp)
set @event = ''avg_hadr_db_commit_mgr_harden''
INSERT INTO LatencyResults
SELECT @event as event_name, AVG(processing_time) as processing_time, NULL as publish_timestamp, NULL as server_commit_mode from tempdb.dbo.DMReplicaEvents where event_name=''hadr_db_commit_mgr_harden''
set @event = ''avg_hadr_log_block_send_complete''
INSERT INTO LatencyResults
SELECT @event as event_name, AVG(processing_time) as processing_time, NULL as publish_timestamp, NULL as server_commit_mode FROM tempdb.dbo.DMReplicaEvents WHERE event_name = ''hadr_log_block_send_complete''
set @event = ''avg_hadr_log_block_compression''
INSERT INTO LatencyResults
SELECT @event as event_name, AVG(processing_time) as processing_time, NULL as publish_timestamp, NULL as server_commit_mode from tempdb.dbo.DMReplicaEvents where event_name=''hadr_log_block_compression''
set @event = ''avg_hadr_log_block_decompression''
INSERT INTO LatencyResults
select @event as event_name, AVG(processing_time) as processing_time, NULL as publish_timestamp, NULL as server_commit_mode from tempdb.dbo.DMReplicaEvents where event_name=''hadr_log_block_decompression''
set @event = ''hadr_lsn_send_complete''
INSERT INTO LatencyResults
select @event, avg(processing_time), min(publish_timestamp) as publish_timestamp, NULL as server_commit_mode from DMReplicaEvents where event_name=''hadr_lsn_send_complete'' GROUP BY DATEPART(YEAR, publish_timestamp), DATEPART(MONTH, publish_timestamp), DATEPART(DAY, publish_timestamp), DATEPART(HOUR, publish_timestamp), DATEPART(MINUTE, publish_timestamp), DATEPART(SECOND, publish_timestamp)
set @event = ''avg_hadr_lsn_send_complete''
INSERT INTO LatencyResults
select @event as event_name, AVG(processing_time) as processing_time, NULL as publish_timestamp, NULL as server_commit_mode from tempdb.dbo.DMReplicaEvents where event_name=''hadr_lsn_send_complete''
set @event = ''avg_hadr_transport_receive_log_block_message''
INSERT INTO LatencyResults
select @event as event_name, AVG(processing_time) as processing_time, NULL as publish_timestamp, NULL as server_commit_mode from tempdb.dbo.DMReplicaEvents where event_name=''hadr_transport_receive_log_block_message''
set @event = ''avg_log_flush_complete''
INSERT INTO LatencyResults
select @event as event_name, AVG(processing_time*1000) as processing_time, NULL as publish_timestamp, NULL as server_commit_mode from tempdb.dbo.DMReplicaEvents where event_name=''log_flush_complete''
COMMIT
',
@database_name=N'tempdb',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Drop XE Session] Script Date: 12/19/2017 3:49:59 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Drop XE Session',
@step_id=7,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'DROP EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER
UPDATE tempdb.dbo.LatencyCollectionStatus set collection_status =''Completed''',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Riportok
Miután az Agent job végzett, a riportok megjeleníthetők az AG nevén jobb gomb, majd Reports --> Standard Reports --> Primary vagy Secondary Replica Report
Én most a Primary Replica riportot futtattam, aminek az eredménye alább látható:
Vulnerability Assessment
Ez a biztonsági felmérés Azure-ban már elérhető egy ideje, de úgy látszik, nekünk "földi" DBA-knak is megadatik a lehetőség ennek a használatára. Nem mondom, hogy minden egyes beépített ellenőrzéshez tartozó szabállyal egyetértek, de kiindulásnak nem is olyan rossz. Ezt az adatbázison jobb gomb, majd Tasks --> Vulnerability Assessment --> Scan for Vulnerabilities... menüpontra kattintva érhetjük el, illetve meg is nyithatunk egy már elvégzett felmérést is.
Az elkészült felmérés eredménye (ez egy demó szerveren készült eredmény):
További információk erről az https://docs.microsoft.com/en-us/sql/relational-databases/security/sql-vulnerability-assessment oldalon érhetőek el.