SQL Server Management Studio 17.4 újdonságok

calendar 2017-12-19 · 14 perc olvasás · SQL SQL Server SSMS  ·
Megosztás: linkedin copy

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:

 1IF EXISTS (select * from sys.server_event_sessions
 2                WHERE name = N'AlwaysOn_Data_Movement_Tracing')
 3                    BEGIN
 4                    DROP EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER
 5                    END
 6GO
 7 
 8CREATE EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER
 9ADD EVENT sqlserver.hadr_apply_log_block,
10ADD EVENT sqlserver.hadr_capture_log_block,
11ADD EVENT sqlserver.hadr_database_flow_control_action,
12ADD EVENT sqlserver.hadr_db_commit_mgr_harden,
13ADD EVENT sqlserver.hadr_log_block_send_complete,
14ADD EVENT sqlserver.hadr_send_harden_lsn_message,
15ADD EVENT sqlserver.hadr_transport_flow_control_action,
16ADD EVENT sqlserver.log_flush_complete,
17ADD EVENT sqlserver.log_flush_start,
18ADD EVENT sqlserver.recovery_unit_harden_log_timestamps,
19ADD EVENT sqlserver.log_block_pushed_to_logpool,
20ADD EVENT sqlserver.hadr_transport_receive_log_block_message,
21ADD EVENT sqlserver.hadr_receive_harden_lsn_message,
22ADD EVENT sqlserver.hadr_log_block_group_commit,
23ADD EVENT sqlserver.hadr_log_block_compression,
24ADD EVENT sqlserver.hadr_log_block_decompression,
25ADD EVENT sqlserver.hadr_lsn_send_complete,
26ADD EVENT sqlserver.hadr_capture_filestream_wait,
27ADD EVENT sqlserver.hadr_capture_vlfheader
28ADD TARGET package0.event_file(SET filename=N'AlwaysOn_Data_Movement_Tracing.xel',max_file_size=(25),max_rollover_files=(4))
29WITH (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)
30 
31GO               
32 
33ALTER EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER STATE = START
34GO

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)

  1-- Switch to SQLCMD mode by  using ALT+Q+M
  2:SETVAR JobOwner sa
  3:SETVAR AGName SQLAG
  4 
  5 
  6USE [msdb]
  7GO
  8 
  9/****** Object:  Job [AlwaysOn_Latency_Data_Collection]    ******/
 10BEGIN TRANSACTION
 11DECLARE @ReturnCode INT
 12SELECT @ReturnCode = 0
 13/****** Object:  JobCategory [[Uncategorized (Local)]]   ******/
 14IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
 15BEGIN
 16EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
 17IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 18 
 19END
 20 
 21DECLARE @jobId BINARY(16)
 22EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'AlwaysOn_Latency_Data_Collection',
 23        @enabled=0,
 24        @notify_level_eventlog=2,
 25        @notify_level_email=0,
 26        @notify_level_netsend=0,
 27        @notify_level_page=0,
 28        @delete_level=0,
 29        @description=N'No description available.',
 30        @category_name=N'[Uncategorized (Local)]',
 31        @owner_login_name=N'$(JobOwner)', @job_id = @jobId OUTPUT
 32IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 33/****** Object:  Step [Collect AG Information]    ******/
 34EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Collect AG Information',
 35        @step_id=1,
 36        @cmdexec_success_code=0,
 37        @on_success_action=3,
 38        @on_success_step_id=0,
 39        @on_fail_action=2,
 40        @on_fail_step_id=0,
 41        @retry_attempts=0,
 42        @retry_interval=0,
 43        @os_run_priority=0, @subsystem=N'TSQL',
 44        @command=N' USE TEMPDB
 45                  IF OBJECT_ID(''AGInfo'') IS NOT NULL
 46                      BEGIN
 47                        DROP TABLE AGInfo
 48                   END
 49                  IF OBJECT_ID(''LatencyCollectionStatus'') IS NOT NULL
 50                      BEGIN
 51                        DROP TABLE LatencyCollectionStatus
 52                      END
 53                   CREATE TABLE LatencyCollectionStatus(
 54                        [collection_status] [NVARCHAR](60)  NULL,
 55                        [start_timestamp] [DATETIMEOFFSET] NULL,
 56                        [startutc_timestamp] [DATETIMEOFFSET] NULL
 57                    )
 58                  INSERT INTO LatencyCollectionStatus(collection_status, start_timestamp, startutc_timestamp) values (''Started'', GETDATE(), GETUTCDATE())
 59                  SELECT
 60                  AGC.name as agname
 61                  , RCS.replica_server_name as replica_name
 62                  , ARS.role_desc as agrole
 63                  INTO AGInfo
 64                  FROM
 65                      sys.availability_groups_cluster AS AGC
 66                      INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
 67                      ON
 68                      RCS.group_id = AGC.group_id
 69                      INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
 70                      ON
 71                      ARS.replica_id = RCS.replica_id
 72                      where AGC.name =  N''$(AGName)''',
 73        @database_name=N'tempdb',
 74        @flags=0
 75IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 76/****** Object:  Step [Create XE Session]   ******/
 77EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Create XE Session',
 78        @step_id=2,
 79        @cmdexec_success_code=0,
 80        @on_success_action=3,
 81        @on_success_step_id=0,
 82        @on_fail_action=2,
 83        @on_fail_step_id=0,
 84        @retry_attempts=0,
 85        @retry_interval=0,
 86        @os_run_priority=0, @subsystem=N'TSQL',
 87        @command=N'IF EXISTS (select * from sys.server_event_sessions
 88                WHERE name = N''AlwaysOn_Data_Movement_Tracing'')
 89                    BEGIN
 90                    DROP EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER
 91                    END
 92                CREATE EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER ADD EVENT sqlserver.hadr_apply_log_block,
 93ADD EVENT sqlserver.hadr_capture_log_block,
 94ADD EVENT sqlserver.hadr_database_flow_control_action,
 95ADD EVENT sqlserver.hadr_db_commit_mgr_harden,
 96ADD EVENT sqlserver.hadr_log_block_send_complete,
 97ADD EVENT sqlserver.hadr_send_harden_lsn_message,
 98ADD EVENT sqlserver.hadr_transport_flow_control_action,
 99ADD EVENT sqlserver.log_flush_complete,
100ADD EVENT sqlserver.log_flush_start,
101ADD EVENT sqlserver.recovery_unit_harden_log_timestamps,
102ADD EVENT sqlserver.log_block_pushed_to_logpool,
103ADD EVENT sqlserver.hadr_transport_receive_log_block_message,
104ADD EVENT sqlserver.hadr_receive_harden_lsn_message,
105ADD EVENT sqlserver.hadr_log_block_group_commit,
106ADD EVENT sqlserver.hadr_log_block_compression,
107ADD EVENT sqlserver.hadr_log_block_decompression,
108ADD EVENT sqlserver.hadr_lsn_send_complete,
109ADD EVENT sqlserver.hadr_capture_filestream_wait,
110ADD 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))
111                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)
112                 
113                ALTER EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER STATE = START',
114        @database_name=N'master',
115        @flags=0
116IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
117/****** Object:  Step [Wait For Collection]    ******/
118EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Wait For Collection',
119        @step_id=3,
120        @cmdexec_success_code=0,
121        @on_success_action=3,
122        @on_success_step_id=0,
123        @on_fail_action=2,
124        @on_fail_step_id=0,
125        @retry_attempts=0,
126        @retry_interval=0,
127        @os_run_priority=0, @subsystem=N'TSQL',
128        @command=N'WAITFOR DELAY ''00:2:00''
129                                                       GO',
130        @database_name=N'master',
131        @flags=0
132IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
133/****** Object:  Step [End XE Session]    ******/
134EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'End XE Session',
135        @step_id=4,
136        @cmdexec_success_code=0,
137        @on_success_action=3,
138        @on_success_step_id=0,
139        @on_fail_action=2,
140        @on_fail_step_id=0,
141        @retry_attempts=0,
142        @retry_interval=0,
143        @os_run_priority=0, @subsystem=N'TSQL',
144        @command=N'ALTER EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER STATE = STOP',
145        @database_name=N'master',
146        @flags=0
147IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
148/****** Object:  Step [Extract XE Data]   ******/
149EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Extract XE Data',
150        @step_id=5,
151        @cmdexec_success_code=0,
152        @on_success_action=3,
153        @on_success_step_id=0,
154        @on_fail_action=2,
155        @on_fail_step_id=0,
156        @retry_attempts=0,
157        @retry_interval=0,
158        @os_run_priority=0, @subsystem=N'TSQL',
159        @command=N'
160                    BEGIN TRANSACTION
161                    USE TEMPDB
162                    IF OBJECT_ID(''#EventXml'') IS NOT NULL
163                    BEGIN
164                        DROP TABLE #EventXml
165                    END
166 
167                    SELECT
168                        xe.event_name,
169                        CAST(xe.event_data AS XML) AS event_data
170                    INTO #EventXml
171                    FROM
172                    (
173                    SELECT
174                            object_name AS event_name,
175                            CAST(event_data AS XML) AS event_data
176                        FROM sys.fn_xe_file_target_read_file(
177                                    ''AlwaysOn_Data_Movement_Tracing*.xel'',
178                                    NULL, NULL, NULL)
179                        WHERE object_name IN (''hadr_log_block_group_commit'',
180                                    ''log_block_pushed_to_logpool'',
181                                    ''log_flush_start'',
182                                    ''log_flush_complete'',
183                                    ''hadr_log_block_compression'',
184                                    ''hadr_capture_log_block'',
185                                    ''hadr_capture_filestream_wait'',
186                                    ''hadr_log_block_send_complete'',
187                                    ''hadr_receive_harden_lsn_message'',
188                                    ''hadr_db_commit_mgr_harden'',
189                                    ''recovery_unit_harden_log_timestamps'',
190                                    ''hadr_capture_vlfheader'',
191                                    ''hadr_log_block_decompression'',
192                                    ''hadr_apply_log_block'',
193                                    ''hadr_send_harden_lsn_message'',
194                                    ''hadr_log_block_decompression'',
195                                    ''hadr_lsn_send_complete'',
196                                    ''hadr_transport_receive_log_block_message'')
197     
198                    ) xe
199 
200                    IF OBJECT_ID(''DMReplicaEvents'') IS NOT NULL
201                    BEGIN
202                        DROP TABLE DMReplicaEvents
203                    END
204 
205                    SET ANSI_NULLS ON
206 
207                    SET QUOTED_IDENTIFIER ON
208 
209                    CREATE TABLE DMReplicaEvents(
210                        [server_name] [NVARCHAR](128) NULL,
211                        [event_name] [NVARCHAR](60) NOT NULL,
212                        [log_block_id] [BIGINT] NULL,
213                        [database_id] [INT] NULL,
214                        [processing_time] [BIGINT] NULL,
215                        [start_timestamp] [BIGINT] NULL,
216                        [publish_timestamp] [DATETIMEOFFSET] NULL,
217                        [log_block_size] [BIGINT] NULL,
218                        [target_availability_replica_id] [UNIQUEIDENTIFIER] NULL,
219                        [local_availability_replica_id] [UNIQUEIDENTIFIER] NULL,
220                        [database_replica_id] [UNIQUEIDENTIFIER] NULL,
221                        [mode] [BIGINT] NULL,
222                        [availability_group_id] [UNIQUEIDENTIFIER] NULL,
223                        [pending_writes]  [BIGINT] NULL
224                    )
225 
226                    IF OBJECT_ID(''LatencyResults'') IS NOT NULL
227                    BEGIN
228                        DROP TABLE LatencyResults
229                    END
230                    CREATE TABLE LatencyResults(
231                       [event_name] [NVARCHAR](60) NOT NULL,
232                       [processing_time] [BIGINT] NULL,
233                       [publish_timestamp] [DATETIMEOFFSET] NULL,
234                       [server_commit_mode] [NVARCHAR](60) NULL
235                    )
236 
237 
238                    INSERT INTO DMReplicaEvents
239                    SELECT
240                        @@SERVERNAME AS server_name,
241                        xe.event_name,
242                        AoData.value(''(data[@name="log_block_id"]/value)[1]'', ''BIGINT'') AS log_block_id,
243                        NULL AS database_id,
244                        AoData.value(''(data[@name="total_processing_time"]/value)[1]'', ''BIGINT'') AS processing_time,
245                        AoData.value(''(data[@name="start_timestamp"]/value)[1]'', ''BIGINT'') AS start_timestamp,
246                        CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 75, 24) AS DATETIMEOFFSET) AS publish_timestamp,
247                        AoData.value(''(data[@name="log_block_size"]/value)[1]'', ''BIGINT'') AS log_block_size,
248                        NULL AS target_availability_replica_id,
249                        NULL AS local_availability_replica_id,
250                        NULL AS database_replica_id,
251                        NULL AS mode,
252                        NULL AS availability_group_id,
253                        NULL AS pending_writes
254                    FROM #EventXml AS xe
255                    CROSS APPLY xe.event_data.nodes(''/event'')  AS T(AoData)
256                    WHERE xe.event_name = ''hadr_log_block_send_complete''
257 
258                    GO
259 
260 
261                    INSERT INTO DMReplicaEvents
262                    SELECT
263                        @@SERVERNAME AS server_name,
264                        xe.event_name,
265                        AoData.value(''(data[@name="log_block_id"]/value)[1]'', ''BIGINT'') AS log_block_id,
266                        AoData.value(''(data[@name="database_id"]/value)[1]'', ''INT'') AS database_id,
267                        AoData.value(''(data[@name="duration"]/value)[1]'', ''BIGINT'') AS processing_time,
268                        AoData.value(''(data[@name="start_timestamp"]/value)[1]'', ''BIGINT'') AS start_timestamp,
269                        CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 65, 24) AS DATETIMEOFFSET) AS publish_timestamp,
270                        NULL AS log_block_size,
271                        NULL AS target_availability_replica_id,
272                        NULL AS local_availability_replica_id,
273                        NULL AS database_replica_id,
274                        NULL AS mode,
275                        NULL AS availability_group_id,
276                        AoData.value(''(data[@name="pending_writes"]/value)[1]'',''BIGINT'') AS pending_writes
277                    FROM #EventXml AS xe
278                    CROSS APPLY xe.event_data.nodes(''/event'')  AS T(AoData)
279                    WHERE xe.event_name = ''log_flush_complete''
280 
281                    GO
282 
283                    INSERT INTO DMReplicaEvents
284                    SELECT
285                        @@SERVERNAME AS server_name,
286                        xe.event_name,
287                        NULL AS log_block_id,
288                        AoData.value(''(data[@name="database_id"]/value)[1]'', ''BIGINT'') AS database_id,
289                        AoData.value(''(data[@name="time_to_commit"]/value)[1]'', ''BIGINT'') AS processing_time,
290                        NULL AS start_timestamp,
291                        CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 72, 24) AS DATETIMEOFFSET) AS publish_timestamp,
292                        NULL AS log_block_size,
293                        AoData.value(''(data[@name="replica_id"]/value)[1]'', ''UNIQUEIDENTIFIER'') AS target_availability_replica_id,
294                        NULL AS local_availability_replica_id,
295                        AoData.value(''(data[@name="ag_database_id"]/value)[1]'', ''UNIQUEIDENTIFIER'') AS database_replica_id,
296                        NULL AS mode,
297                        AoData.value(''(data[@name="group_id"]/value)[1]'',''UNIQUEIDENTIFIER'') AS availability_group_id,
298                        NULL AS pending_writes
299                    FROM #EventXml AS xe
300                    CROSS APPLY xe.event_data.nodes(''/event'')  AS T(AoData)
301                    WHERE xe.event_name = ''hadr_db_commit_mgr_harden''
302 
303                    GO
304 
305 
306                    INSERT INTO DMReplicaEvents
307                    SELECT
308                        @@SERVERNAME AS server_name,
309                        xe.event_name,
310                        AoData.value(''(data[@name="log_block_id"]/value)[1]'', ''BIGINT'') AS log_block_id,
311                        AoData.value(''(data[@name="database_id"]/value)[1]'', ''BIGINT'') AS database_id,
312                        AoData.value(''(data[@name="processing_time"]/value)[1]'', ''BIGINT'') AS processing_time,
313                        AoData.value(''(data[@name="start_timestamp"]/value)[1]'', ''BIGINT'') AS start_timestamp,
314                        CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 82, 24) AS DATETIMEOFFSET) AS publish_timestamp,
315                        NULL AS log_block_size,
316                        NULL AS target_availability_replica_id,
317                        NULL AS local_availability_replica_id,
318                        NULL AS database_replica_id,
319                        NULL AS mode,
320                        NULL AS availability_group_id,
321                        NULL AS pending_writes
322                    FROM #EventXml AS xe
323                    CROSS APPLY xe.event_data.nodes(''/event'')  AS T(AoData)
324                    WHERE xe.event_name = ''recovery_unit_harden_log_timestamps''
325 
326                    GO
327 
328                    INSERT INTO DMReplicaEvents
329                    SELECT
330                        @@SERVERNAME AS server_name,
331                        xe.event_name,
332                        AoData.value(''(data[@name="log_block_id"]/value)[1]'', ''BIGINT'') AS log_block_id,
333                        AoData.value(''(data[@name="database_id"]/value)[1]'', ''BIGINT'') AS database_id,
334                        AoData.value(''(data[@name="processing_time"]/value)[1]'', ''BIGINT'') AS processing_time,
335                        AoData.value(''(data[@name="start_timestamp"]/value)[1]'', ''BIGINT'') AS start_timestamp,
336                        CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 73, 24) AS DATETIMEOFFSET) AS publish_timestamp,
337                        AoData.value(''(data[@name="uncompressed_size"]/value)[1]'', ''INT'') AS log_block_size,
338                        AoData.value(''(data[@name="availability_replica_id"]/value)[1]'', ''UNIQUEIDENTIFIER'') AS target_availability_replica_id,
339                        NULL AS local_availability_replica_id,
340                        NULL AS database_replica_id,
341                        NULL AS mode,
342                        NULL AS availability_group_id,
343                        NULL AS pending_writes
344                    FROM #EventXml AS xe
345                    CROSS APPLY xe.event_data.nodes(''/event'')  AS T(AoData)
346                    WHERE xe.event_name = ''hadr_log_block_compression''
347 
348                    GO
349 
350 
351                    INSERT INTO DMReplicaEvents
352                    SELECT
353                        @@SERVERNAME AS server_name,
354                        xe.event_name,
355                        AoData.value(''(data[@name="log_block_id"]/value)[1]'', ''BIGINT'') AS log_block_id,
356                        AoData.value(''(data[@name="database_id"]/value)[1]'', ''BIGINT'') AS database_id,
357                        AoData.value(''(data[@name="processing_time"]/value)[1]'', ''BIGINT'') AS processing_time,
358                        AoData.value(''(data[@name="start_timestamp"]/value)[1]'', ''BIGINT'') AS start_timestamp,
359                        CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 75, 24) AS DATETIMEOFFSET) AS publish_timestamp,
360                        AoData.value(''(data[@name="uncompressed_size"]/value)[1]'', ''BIGINT'') AS log_block_size,
361                        AoData.value(''(data[@name="availability_replica_id"]/value)[1]'', ''UNIQUEIDENTIFIER'') AS target_availability_replica_id,
362                        NULL AS local_availability_replica_id,
363                        NULL AS database_replica_id,
364                        NULL AS mode,
365                        NULL AS availability_group_id,
366                        NULL AS pending_writes
367                    FROM #EventXml AS xe
368                    CROSS APPLY xe.event_data.nodes(''/event'')  AS T(AoData)
369                    WHERE xe.event_name = ''hadr_log_block_decompression''
370 
371                    INSERT INTO DMReplicaEvents
372                    SELECT
373                        @@SERVERNAME AS server_name,
374                        xe.event_name,
375                        AoData.value(''(data[@name="log_block_id"]/value)[1]'', ''BIGINT'') AS log_block_id,
376                        NULL AS database_id,
377                        AoData.value(''(data[@name="total_sending_time"]/value)[1]'', ''BIGINT'') AS processing_time,
378                        AoData.value(''(data[@name="start_timestamp"]/value)[1]'', ''BIGINT'') AS start_timestamp,
379                        CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 69, 24) AS DATETIMEOFFSET) AS publish_timestamp,
380                        NULL AS log_block_size,
381                        NULL AS target_availability_replica_id,
382                        NULL AS local_availability_replica_id,
383                        NULL AS database_replica_id,
384                        NULL AS mode,
385                        NULL AS availability_group_id,
386                        NULL AS pending_writes
387                    FROM #EventXml AS xe
388                    CROSS APPLY xe.event_data.nodes(''/event'')  AS T(AoData)
389                    WHERE xe.event_name = ''hadr_lsn_send_complete''
390 
391                    INSERT INTO DMReplicaEvents
392                    SELECT
393                        @@SERVERNAME AS server_name,
394                        xe.event_name,
395                        AoData.value(''(data[@name="log_block_id"]/value)[1]'', ''BIGINT'') AS log_block_id,
396                        NULL AS database_id,
397                        AoData.value(''(data[@name="processing_time"]/value)[1]'', ''BIGINT'') AS processing_time,
398                        AoData.value(''(data[@name="start_timestamp"]/value)[1]'', ''BIGINT'') AS start_timestamp,
399                        CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 87, 24) AS DATETIMEOFFSET) AS publish_timestamp,
400                        NULL AS log_block_size,
401                        AoData.value(''(data[@name="target_availability_replica_id"]/value)[1]'', ''UNIQUEIDENTIFIER'') AS target_availability_replica_id,
402                        AoData.value(''(data[@name="local_availability_replica_id"]/value)[1]'', ''UNIQUEIDENTIFIER'') AS local_availability_replica_id,
403                        AoData.value(''(data[@name="target_availability_replica_id"]/value)[1]'', ''UNIQUEIDENTIFIER'') AS database_replica_id,
404                        AoData.value(''(data[@name="mode"]/value)[1]'', ''BIGINT'') AS mode,
405                        AoData.value(''(data[@name="availability_group_id"]/value)[1]'',''UNIQUEIDENTIFIER'') AS availability_group_id,
406                        NULL AS pending_writes
407                    FROM #EventXml AS xe
408                    CROSS APPLY xe.event_data.nodes(''/event'')  AS T(AoData)
409                    WHERE xe.event_name = ''hadr_transport_receive_log_block_message''
410 
411 
412                    DELETE
413                    FROM DMReplicaEvents
414                    WHERE CAST(publish_timestamp AS DATETIME) < DATEADD(minute, -2, CAST((SELECT MAX(publish_timestamp) from DMReplicaEvents) as DATETIME))
415                    COMMIT
416                    GO',
417        @database_name=N'tempdb',
418        @flags=0
419IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
420/****** Object:  Step [Create Result Set]    ******/
421EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Create Result Set',
422        @step_id=6,
423        @cmdexec_success_code=0,
424        @on_success_action=3,
425        @on_success_step_id=0,
426        @on_fail_action=2,
427        @on_fail_step_id=0,
428        @retry_attempts=0,
429        @retry_interval=0,
430        @os_run_priority=0, @subsystem=N'TSQL',
431        @command=N'
432                    BEGIN TRANSACTION
433                    USE TEMPDB
434                    declare @ag_id as nvarchar(60)
435                    declare @event as nvarchar(60)
436                    set @ag_id = (select group_id from  sys.availability_groups_cluster where name = N''$(AGName)'')
437                    IF OBJECT_ID(''DbIdTable'') IS NOT NULL
438                    BEGIN
439                        DROP TABLE DbIdTable
440                    END
441                    CREATE TABLE DbIdTable(
442                        [database_id] [INT] NULL
443                    )
444 
445                    INSERT INTO DbIdTable
446                    select distinct database_id  from sys.dm_hadr_database_replica_states where group_id=@ag_id
447 
448                    delete from tempdb.dbo.DMReplicaEvents where not (availability_group_id = @ag_id or availability_group_id is NULL)
449 
450                    delete from tempdb.dbo.DMReplicaEvents where not (database_id in (select database_id from DbIdTable) or database_id is NULL)
451 
452                    set @event = ''availability_mode_desc''
453                    INSERT INTO LatencyResults
454                    select @event, NULL as processing_time, NULL as publish_timestamp, availability_mode_desc as server_commit_mode from sys.availability_replicas  A
455                    inner join
456                    (select * from sys.dm_hadr_availability_replica_states) B
457                    on A.replica_id = B.replica_id and A.group_id = @ag_id and A.replica_server_name = @@SERVERNAME
458 
459                    set @event = ''start_time''
460                    INSERT INTO LatencyResults
461                    select @event as event_name, NULL as processing_time, min(publish_timestamp) as publish_timestamp, NULL as server_commit_mode from tempdb.dbo.DMReplicaEvents
462 
463                    set @event = ''recovery_unit_harden_log_timestamps''
464                    INSERT INTO LatencyResults
465                    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)
466 
467                    set @event = ''avg_recovery_unit_harden_log_timestamps''
468                    INSERT INTO LatencyResults
469                    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''
470 
471                    set @event = ''hadr_db_commit_mgr_harden''
472                    INSERT INTO LatencyResults
473                    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)
474 
475                    set @event = ''avg_hadr_db_commit_mgr_harden''
476                    INSERT INTO LatencyResults
477                    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''
478 
479                    set @event = ''avg_hadr_log_block_send_complete''
480                    INSERT INTO LatencyResults
481                    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''
482 
483                    set @event = ''avg_hadr_log_block_compression''
484                    INSERT INTO LatencyResults
485                    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''
486 
487                    set @event = ''avg_hadr_log_block_decompression''
488                    INSERT INTO LatencyResults
489                    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''
490 
491                    set @event = ''hadr_lsn_send_complete''
492                    INSERT INTO LatencyResults
493                    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)
494 
495                    set @event = ''avg_hadr_lsn_send_complete''
496                    INSERT INTO LatencyResults
497                    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''
498 
499                    set @event = ''avg_hadr_transport_receive_log_block_message''
500                    INSERT INTO LatencyResults
501                    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''
502 
503 
504                    set @event = ''avg_log_flush_complete''
505                    INSERT INTO LatencyResults
506                    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''
507                    COMMIT
508 
509            ',
510        @database_name=N'tempdb',
511        @flags=0
512IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
513/****** Object:  Step [Drop XE Session]    Script Date: 12/19/2017 3:49:59 PM ******/
514EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Drop XE Session',
515        @step_id=7,
516        @cmdexec_success_code=0,
517        @on_success_action=1,
518        @on_success_step_id=0,
519        @on_fail_action=2,
520        @on_fail_step_id=0,
521        @retry_attempts=0,
522        @retry_interval=0,
523        @os_run_priority=0, @subsystem=N'TSQL',
524        @command=N'DROP EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER
525                                                            UPDATE tempdb.dbo.LatencyCollectionStatus set collection_status =''Completed''',
526        @database_name=N'master',
527        @flags=0
528IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
529EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
530IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
531EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
532IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
533COMMIT TRANSACTION
534GOTO EndSave
535QuitWithRollback:
536    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
537EndSave:
538GO

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.