Отслеживание изменений в SQL Server 2008

Я думаю, каждый разработчик СУБД рано или поздно сталкивается с задачей отслеживания обращений к БД и событий сервера в целом. И прежде чем выбрать инструмент (или написать его самому), конечно, стоит обратить внимание на решения, которые предлагают сами разработчики СУБД. Хочу поделиться опытом решения этой задачи для SQL Server 2008.

На данный момент есть 4 таких решения для SQL Server 2008 (для SQL Server 2011 существенных изменений в этой области не предвидится). Некоторые из этих средств пришли из более ранних версий, некоторые появились в 2008. Эти средства во многом пересекаются, поэтому, порой, не просто выбрать один (или более) инструмент для решения конкретной задачи. В этом я постараюсь помочь, проведя краткий обзор каждого инструмента с примером.

1. CT (Change Tracking).


Зачастую путают с CDC (Change Data Capture). Но эти инструменты различны как в назначении, так и в реализации. CT предназначен для отслеживания фактов изменений (в каких строках, какие данные были изменены (CRUD)), в то время как CDC хранит историю изменений (все версии строк, в том числе те, которые были удалены). Что касается реализации, CDC основан на чтении журнала транзакций (асинхронен), в то время как CT работает синхронно.
Для каждой таблицы, для которой включено отслеживание изменений, создается системная таблица, в которой хранился ID измененной строки, битовая маска для идентификации измененных колонок, тип операции.
Для включения CT нужно активировать его на уровне БД и для конкретной таблицы:
ALTER DATABASE ChangeTracking SET change_tracking = ON<br/>
(change_retention = 10 minutes, auto_cleanup = ON) <br/>
 <br/>
ALTER TABLE Orders enable change_tracking WITH (track_columns_updated = ON)


Более детально (описание параметров, примеры использования и детальная информация) в отличной статье.

2. CDC (Change Data Capture)


Средство для отслеживания измененных данных. Основными отличиями от CT являются асинхронная реализация (как писалось выше) и хранение всех версий измененных (CRUD) данных. Для хранения измененных данных CDC использует системные таблицы в схеме cdc. Для каждой таблицы, для которой активирован CDC, создается таблица с названием по типу cdc.dbo_Orders_CT (для таблицы dbo.Orders).

image
  • *_lsn – (log sequence number ) — своего рода идентификаторы транзакции в логе.
    $operation — тип операции (1 — delete, 2 — insert, 3 — update (версия строки до обновления), 4 — update (версия строки после обновления).
  • $update_mask — битовая маска, указывающая на изменившиеся ячейки в строке.
  • ID и все остальные справа — ячейки соотв. структуре таблицы, изменения в которой отслеживаются.

Для активации CDC Вам нужно активировать его на уровне БД для конкретной таблицы:
EXEC sys.sp_cdc_enable_db<br/>
 <br/>
EXEC sys.sp_cdc_enable_table<br/>
@source_schema = N'dbo',<br/>
@source_name = N'Orders',<br/>
@role_name = N'cdc',<br/>
@capture_instance = N'dbo_Orders',<br/>
@supports_net_changes = 1,<br/>
@index_name = 'id_idx',<br/>
@captured_column_list = null,<br/>
@FILEGROUP_NAME = null;


  • @source_schema — схема, которой принадлежит таблица, для которой активируем CDC
  • @source_name — название таблицы, для которой активируем CDC
  • @role_name — имя роли, которая будет иметь право на просмотр изменений (при отсутствии — создается автоматически)
  • @capture_instance — соответствует части названия, которое будет выдано соответстующей системной таблице
  • @supports_net_changes — поддержка возможности отображения результирующего набора изменений (только последние версии данных). Для этого необходимо наличие уникального индекса
  • @index_name — собственно, название уникального индекса
  • @captured_column_list — список полей, для которых будет активировано отслеживание изменений. По умолчанию — все.
  • @filegroup_name — файловая группа, в которой будут размещена системная таблица

С чисто практической точки зрения, значительный минус CDC это то, что невозможно зафиксировать автора изменений. Конечно, никто не мешает добавить столбец в системную таблицу cdc.dbo_Orders_CT с дефолтным значением suser_sname() (в моей практике это работает), но подобные манипуляции с системными таблицами — не лучший способ построения отказоустойчивой системы.

Примеры запросов к сохраненным данным и развернутое описание в статье.

3. SQL Server Audit


Мощное средство, предназначенное для отслеживания всех событий и запросов и серверу (в том числе select). Область применения этого средства достаточно широка — от профилирования до вопросов, связанных с безопасностью и выявление активности пользователей в не предназначенной им части БД.
SQL Server Audit позволяет гибко настраивать фильтры отслеживаемых событий.
Для использования аудита необходимо активировать его на уровне сервера:
CREATE server audit ServerAudit<br/>
TO FILE (filepath = `D:\Audit\`, maxsize = 1GB)<br/>
WITH (on_failture = CONTINUE)<br/>
 <br/>
ALTER server audit ServerAudit WITH (STATE=ON)


Пример создания спецификации аудита (трейса) на уровне сервера:
CREATE server audit specification ServerAudit_Permissions<br/>
FOR server audit ServerAudit<br/>
ADD (server_principal_change_group),<br/>
ADD (server_permission_change_group),<br/>
ADD (server_role_member_change_group);<br/>
 <br/>
ALTER server audit specification ServerAudit_Permissions<br/>
WITH (STATE=ON);


Пример создания спецификации аудита на уровне БД:
USE MyDb<br/>
CREATE DATABASE audit specification SA_MyDb_Orders <br/>
FOR server audit ServerAudit<br/>
ADD (SELECTUPDATEINSERTDELETE ON dbo.Orders BY PUBLIC),<br/>
ADD (SELECTUPDATEINSERTDELETE ON dbo.OrderDetails BY PUBLIC)


Для настойки аудита, есть удобный визуальный интерфейс в SQL Server Management Studio.

Также, следует отметить, наличие средств стандартизированного аудита спецификации c2 (государственный стандарт США, если верить MSDN, ссылку на стандарт я не нашел), для активации которого следует выполнить:
SP_CONFIGURE 'show advanced options'1;<br/>
RECONFIGURE;<br/>
 <br/>
SP_CONFIGURE 'c2 audit mode'1;<br/>
RECONFIGURE;


4. SQL Server Profiler


Всем давно знакомая утилита, поэтому пока на ней останавливаться не буду.

Спасибо.
В планах написать подробно о каждом инструменте, если конечно тема вызовет интерес.
  • +5
  • 44,7k
  • 2
Поделиться публикацией
AdBlock похитил этот баннер, но баннеры не зубы — отрастут

Подробнее
Реклама

Комментарии 2

    0
    Спасибо за статью. Возможно не совсем в тему, но как вы отслеживаете «мертвые» блокировки?
      0
      подписаться на соответствующие ивенты профайлером или аудитом. ну и sp_who2

      Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

      Самое читаемое