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

Я думаю, каждый разработчик СУБД рано или поздно сталкивается с задачей отслеживания обращений к БД и событий сервера в целом. И прежде чем выбрать инструмент (или написать его самому), конечно, стоит обратить внимание на решения, которые предлагают сами разработчики СУБД. Хочу поделиться нашим опытом в Brights решения этой задачи для 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).

• *_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 (SELECT, UPDATE, INSERT, DELETE ON dbo.Orders BY PUBLIC),<br/>
ADD (SELECT, UPDATE, INSERT, DELETE 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

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

Спасибо.
В планах написать подробно о каждом инструменте, если конечно тема вызовет интерес.