Журнал транзакций (sql server)

Таблица изменений

Первые пять столбцов таблицы изменений для системы отслеживания измененных данных являются столбцами метаданных. Они предоставляют дополнительные сведения, относящиеся к регистрируемому изменению. Остальные столбцы отражают опознанные отслеживаемые столбцы исходной таблицы по имени и типу. В этих столбцах хранятся данные отслеживаемых столбцов из исходной таблицы.

Каждая операция вставки или удаления, которая была выполнена в исходной таблице, отражается как одна строка в таблице изменений. Столбцы данных в строке, отражающей результаты операции вставки, содержат значения столбов после вставки. Столбцы данных в строке, отражающей результаты операции удаления, содержат значения столбов перед удалением. Операции обновления требуется одна строка для определения значений столбца перед обновлением, и еще одна строка — для значений столбца после обновления.

Каждая строка в таблице изменений содержит также дополнительные метаданные, позволяющие интерпретировать операции изменения. Столбец __$start_lsn определяет номер LSN фиксации, который был присвоен изменению. Зафиксированный номер LSN определяет как операции изменения, которые были проведены в рамках одной транзакции, так и порядок транзакций. Столбец __$seqval может использоваться для упорядочивания дополнительных изменений в этой транзакции. Столбец __$operation регистрирует операцию, связанную с изменением: 1 = удаление, 2 = вставка, 3 = обновление (исходный образ), 4 = обновление (результирующий образ). Столбец __$update_mask — это переменная в виде битовой маски, в которой каждому отслеживаемому столбцу соответствует один бит. Для записей операций вставки и удаления все биты в маске обновления всегда будут установленными. У строк операций обновления будут установлены только биты, соответствующие измененным столбцам.

Экземпляр системы отслеживания

Прежде чем можно будет отслеживать изменения в отдельных таблицах базы данных, необходимо явно активировать систему отслеживания измененных данных в этой базе данных. Это делается с помощью хранимой процедуры sys.sp_cdc_enable_db. После того как база данных будет активирована, с помощью хранимой процедуры sys.sp_cdc_enable_tableисходные таблицы можно определить как отслеживаемые. Если для таблицы активирована система отслеживания измененных данных, создается связанный экземпляр системы отслеживания изменений для распространения данных об изменениях в исходной таблице. Экземпляр системы отслеживания состоит из таблицы изменений и одной-двух функций запроса. Метаданные, подробно описывающие конфигурацию экземпляра системы отслеживания, сохраняются в таблицах отслеживания измененных метаданных cdc.change_tables, cdc.index_columnsи cdc.captured_columns. Эти сведения можно получить с помощью хранимой процедуры sys.sp_cdc_help_change_data_capture.

Все объекты, связанные с экземпляром системы отслеживания, создаются в схеме системы отслеживания измененных данных для активированной базы данных. Именем экземпляра системы отслеживания должно быть допустимое имя объекта, уникальное для всех экземпляров системы отслеживания этой базы данных. По умолчанию используется < имя схемы имя_таблицы> исходной таблицы. Связанная с ним таблица изменений именуется путем добавления ключевого слова _CT к имени экземпляра системы отслеживания. Функция, которая используется для запроса всех изменений, именуется путем добавления к началу имени экземпляра системы отслеживания префикса fn_cdc_get_all_changes_ . Если экземпляр отслеживания настроен для поддержки чистых изменений, функция запроса net_changes также создается и именуется путем добавления fn_cdc_get_net_changes_ к имени экземпляра отслеживания.

Журнал транзакций с упреждающей записью

В этом разделе описана роль, которую журнал транзакций с упреждающей записью играет в записи изменений данных на диск. SQL Server использует алгоритм с упреждающей записью журнала (WAL), который гарантирует, что сначала на диск будет записана соответствующая запись журнала, и только после этого изменения данных будут записаны на диск. Таким образом обеспечиваются свойства ACID для транзакции.

Чтобы понять, как работает упреждающее ведение журнала, важно знать, как измененные данные записываются на диск. SQL Server поддерживает буферный кэш (также называемый буферным пулом), в который он считывает страницы данных при извлечении данных

При изменении страницы в буферном кэше она не сразу записывается обратно на диск; Вместо этого страница помечается как «грязная». Перед физической записью на диск на странице данных может быть выполнено несколько логических операций записи. При каждой логической операции записи в кэш журнала, который записывает изменения, добавляется запись журнала транзакций. Записи журнала должны быть перенесены на диск до того, как соответствующая «грязная» страница будет удалена из буферного кэша и записана на диск. Заключается в том, что процесс контрольных точек производит периодический просмотр буферного кэша на наличие буферов со страницами определенной базы данных и запись всех «грязных» страниц на диск. Контрольные точки экономят время во время последующего восстановления при помощи создания точки, в которой все «грязные» страницы гарантированно записываются на диск.

Запись измененной страницы данных из буферного кэша на диск называется сбросом страницы на диск. SQL Server Имеет логику, которая предотвращает запись на диск «грязной» страницы до записи на него связанной записи журнала. Содержимое журнала записывается на диск при сбросе буферов журнала. Это происходит при фиксации транзакции или заполнении буферов журнала.

Логическая архитектура журнала транзакций

Логически журнал транзакций SQL Server работает так, как если бы он являлся последовательностью записей в журнале. Каждая запись журнала определяется порядковый номер журнала (LSN). Каждая новая запись добавляется в логический конец журнала с номером LSN, который больше номера LSN предыдущей записи. Записи журнала сохраняются в серийной последовательности по мере их создания, таким образом если LSN2 больше, чем LSN1, то изменение, описанное записью журнала, на которую ссылается LSN2, произошло после изменения, описанного записью журнала LSN1. Каждая запись журнала содержит идентификатор транзакции, к которой она относится. Все записи журнала, связанные с определенной транзакцией, с помощью обратных указателей связаны в цепочку, которая предназначена для ускорения отката транзакции.

Записи журнала для изменений данных записывают либо выполненную логическую операцию, либо образы до и после измененных данных. Изображение до является копией данных перед выполнением операции; изображение после является копией данных после выполнения операции.

Действия, которые необходимо выполнить для восстановления операции, зависят от типа журнальной записи:

Зарегистрирована логическая операция.
- Для наката логической операции выполняется снова.
- Для отката логической операции выполняется обратная логическая операция.
Зарегистрированы исходный и результирующий образы записи.
- Для наката операции применяется изображение после.
- Для отката операции применяется образ до.

В журнал транзакций записываются различные типы операций, например:

начало и конец каждой транзакции;
любые изменения данных (вставка, обновление или удаление), включая изменения в любой таблице (в том числе и в системных таблицах), производимые системными хранимыми процедурами или инструкциями языка DDL;
любое выделение и освобождение страниц и экстентов;
создание и удаление таблиц и индексов.

Кроме того, регистрируются операции отката. Каждая транзакция резервирует место в журнале транзакций, чтобы убедиться, что существует достаточно места в журнале для поддержки отката, вызванного явным оператором отката или при обнаружении ошибки. Объем зарезервированного пространства зависит от операций, выполняемых в транзакции, но обычно он равен объему пространства, используемого для регистрации каждой операции. Все это пространство после завершения транзакции освобождается.

Раздел файла журнала из первой записи, который должен присутствовать для успешного отката всей базы данных к последней зарегистрированной записи называется активной частью журнала, активным журналом или заключительным фрагментом журнала. Этот раздел журнала необходим для полного базы данных. Ни одна часть активного журнала не может быть усечена. Порядковый номер журнала (LSN) этой первой записи журнала называется минимальным номером LSN восстановления (MinLSN). Дополнительные сведения об операциях, поддерживаемых журналом транзакций, см. в разделе Журнал транзакций (SQL Server).

Разностные резервные копии и резервные копии журналов продвигают восстанавливаемую базу данных к более позднему моменту, которому соответствует больший регистрационный номер транзакции в журнале.

Выбор условия производительности

Можно определить, чтобы предупреждение создавалось в ответ на определенное условие производительности. В этом случае указывается отслеживаемый счетчик производительности, порог предупреждения и действие, по которому предупреждение создается. Чтобы назначить условие производительности, необходимо определить в агенте SQL Server значения следующих элементов на странице Общие диалогового окна Создание предупреждения или Свойства предупреждения :

Объект

Объект — область отслеживания производительности.
Счетчик

Счетчик — атрибут отслеживаемой области.
Экземпляр

Экземпляр SQL Server определяет конкретный экземпляр (если есть) отслеживаемого атрибута.
Создать предупреждение, если счетчик: и Значение

Пороговое значение и действие, по которому срабатывает предупреждение. Пороговое значение — число. Действие — одно из следующих значений: меньше, равноили больше по отношению к значению, указанному в поле «Значение». Значение — числовое значение счетчика условия производительности. Например, чтобы определить срабатывание предупреждения для объекта производительности SQLServer:Locks , если значение Время ожидания блокировки превышает 30 минут, необходимо выбрать больше и указать 30 в поле значения.

Или, например, можно указать, что предупреждение срабатывает для объекта производительности SQLServer:Transactions , когда свободное место в базе данных tempdb становится меньше 1000 КБ. Для этого выберите счетчик Свободное пространство в tempdb (КБ), меньшеи введите 1000 в поле Значение.

Примечание

Сведения о производительности снимаются не в реальном времени, что может привести к небольшой задержке (в несколько секунд) между достижением порогового значения и срабатыванием предупреждения условия производительности.

Примечание

Переменная журнала событий, в которой хранится имя сервера, ограничена 32 символами. Поэтому, если общий размер имени узла и имени экземпляра превышает 32 символов, может появиться следующая ошибка:

Указание события SQL Server

Можно определить, чтобы предупреждение создавалось в ответ на одно или несколько событий. Для указания событий, по которым создается предупреждение, используются следующие параметры.

Номер ошибки

SQL Server Агент создает предупреждение при возникновении определенной ошибки. Например, можно указать код ошибки 2571 для отслеживания попыток неавторизованного обращения к консольным командам базы данных (DBCC).
Степень серьезности

SQL Server Агент создает предупреждение при возникновении любой ошибки определенного уровня серьезности. Например, можно указать уровень серьезности 15 для обработки ошибок синтаксиса в инструкциях на языке Transact-SQL.
База данных

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

SQL Server Агент создает предупреждение, если в сообщении о событии содержится определенная текстовая строка. Например, можно определить предупреждение, которое будет создаваться для сообщений, содержащих имя определенной таблицы или ограничения.

Совместимость с разными компонентами

Как правило, безопасность на уровне строк будет работать должным образом в разных компонентах. Однако существует несколько исключений. В этом разделе описано несколько заметок и предостережения по использованию безопасности на уровне строк с некоторыми другими функциями SQL Server.

DBCC SHOW_STATISTICS предоставляет статистику по нефильтрованным данным и может вызвать утечку информации, которая должна быть защищена политикой безопасности. По этой причине доступ к просмотру объектов статистики для таблицы с политикой безопасности на уровне строк ограничен. Пользователь должен быть владельцем таблицы, членом предопределенной роли сервера sysadmin, предопределенной роли базы данных db_owner или предопределенной роли базы данных db_ddladmin.
Filestream. Безопасность на уровне строк не совместима с компонентом Filestream.
PolyBase: безопасность на уровне строк поддерживается для внешних таблиц в Azure Synapse и SQL Server 2019 CU7 или более поздней версии.
Таблицы, оптимизированные для памяти. Встроенная функция с табличным значением, которая используется в качестве предиката безопасности для таблицы, оптимизированной для памяти, должна быть определена с помощью параметра . Этот параметр позволяет блокировать функции языка, не поддерживаемые в оптимизированных для памяти таблицах, и выдавать соответствующую ошибку во время создания. Дополнительные сведения см. в разделе Безопасность на уровне строк в таблицах, оптимизированных для памяти статьи Вводные сведения о таблицах, оптимизированных для памяти.
Индексированные представления. Как правило, политики безопасности можно создавать на основе представлений, а представления — на основе таблиц, связанных политиками безопасности. Тем не менее индексированные представления нельзя создать на основе таблиц с политикой безопасности, так как операции поиска строк через индекс будут обходить политику.
Отслеживание измененных данных. Система отслеживания измененных данных может вызвать утечку целых строк, которые должны быть отфильтрованы для доступа членов db_owner или пользователей, являющихся членами «шлюзовой» роли, указанной при включении этой системы для таблицы. (Примечание. Для этой функции можно явно задать значение NULL, чтобы все пользователи имели доступ к измененным данным.) В результате параметр db_owner и члены такой шлюзовой роли могут просматривать все изменения данных в таблице даже при наличии политики безопасности для таблицы.
Отслеживание изменений. Отслеживание изменений может вызвать утечку первичного ключа строк, которые должны быть отфильтрованы для доступа пользователей с разрешениями SELECT и VIEW CHANGE TRACKING. Доступ к фактическим значениям данных не предоставляется, становится известно только то, что столбец A был обновлен (вставлен или удален) для строки с первичным ключом B. Это создает проблему, если первичный ключ содержит конфиденциальные элементы, например номер социального страхования. Тем не менее на практике для получения последних данных инструкция CHANGETABLE почти всегда объединена с исходной таблицей.
Полнотекстовый поиск. Запросы, использующие следующие функции полнотекстового и семантического поиска, будут выполняться со сниженной производительностью из-за введения дополнительного соединения для применения безопасности на уровне строк и блокирования утечки первичных ключей строк, которые должны быть отфильтрованы: CONTAINSTABLE, FREETEXTTABLE, semantickeyphrasetable, semanticsimilaritydetailstable, semanticsimilaritytable.
Индексы Columnstore. Безопасность на уровне строк совместима с кластеризованными и некластеризованными индексами columnstore. Тем не менее, поскольку безопасность на уровне строк применяет функцию, оптимизатор может изменить план запроса таким образом, чтобы пакетный режим не использовался.
Секционированные представления. Предикаты блокировки нельзя определить в секционированных представлениях, а секционированные представления нельзя создавать на основе таблиц, использующих предикаты блокировки. Предикаты фильтров совместимы с секционированными представлениями.
Темпоральные таблицы. Темпоральные таблицы совместимы с безопасностью на уровне строк. Тем не менее предикаты безопасности в текущей таблице не реплицируются автоматически в прежнюю таблицу. Чтобы применить политику безопасности для текущей и прежней таблиц, необходимо по отдельности добавить предикат безопасности в каждую таблицу.

Перед началом работы

Чтобы подключиться к файлам журнала в автономном режиме, необходимо установить экземпляр SQL Server на компьютере, который используется для просмотра файлов журнала в автономном режиме, а также на компьютере, где находятся файлы журналов, которые требуется просмотреть. Если экземпляр SQL Server установлен на обоих компьютерах, вы можете просматривать автономные файлы для экземпляров SQL Server и для экземпляров, работающих под управлением более ранних версий SQL Server на любом компьютере.

При использовании списка «Зарегистрированные серверы» экземпляр, к которому нужно подключиться, должен быть зарегистрирован в Группы локальных серверов или Центральные серверы управления. (Экземпляр может быть зарегистрирован самостоятельно или быть членом группы серверов.) Дополнительные сведения о добавлении экземпляра SQL Server на зарегистрированные серверы см. в следующих разделах:

Дополнительные сведения о просмотре файлов журналов вне сети программным способом с помощью запросов WMI и WQL см. в следующих разделах:

SqlErrorLogEvent, класс (В этом разделе рассматривается извлечение значений событий, зарегистрированных в указанном файле журнала.)
Класс SqlErrorLogFile (в этом разделе показано, как получить сведения обо всех SQL Server файлах журнала в указанном экземпляре SQL Server.)

Логическая архитектура журнала транзакций

Действия, которые необходимо выполнить для восстановления операции, зависят от типа журнальной записи:

Зарегистрирована логическая операция.
- Для наката логической операции выполняется снова.
- Для отката логической операции выполняется обратная логическая операция.
Зарегистрированы исходный и результирующий образы записи.
- Для наката операции применяется изображение после.
- Для отката операции применяется образ до.

В журнал транзакций записываются различные типы операций, например:

начало и конец каждой транзакции;
любые изменения данных (вставка, обновление или удаление), включая изменения в любой таблице (в том числе и в системных таблицах), производимые системными хранимыми процедурами или инструкциями языка DDL;
любое выделение и освобождение страниц и экстентов;
создание и удаление таблиц и индексов.

Выбор события инструментария WMI

Можно указать, чтобы предупреждение создавалось в ответ на определенное событие инструментария WMI. Чтобы назначить событие инструментария WMI, необходимо определить в агенте SQL Server следующие элементы на странице Общие диалогового окна Создание предупреждения или Свойства предупреждения :

Пространство имен

SQL Server Агент регистрируется в качестве клиента WMI в пространстве имен инструментария WMI, выделенном для запроса событий.
Запрос

SQL Server Агент для определения конкретного события пользуется инструкцией на языке запросов инструментария управления Windows (WQL).

Ниже приведены ссылки на часто выполняемые задачи.

Создание предупреждения по номеру сообщения

Создание предупреждения по уровню серьезности

Создание предупреждения по событию инструментария WMI

Определение ответа на предупреждение

Создание сообщения об ошибке пользовательского события

Transact-SQL

Изменение сообщения об ошибке пользовательского события

Transact-SQL

Удаление сообщения об ошибке пользовательского события

Transact-SQL

Отключение или повторное включение предупреждения

Логическая архитектура журнала транзакций

Действия, которые необходимо выполнить для восстановления операции, зависят от типа журнальной записи:

Зарегистрирована логическая операция.
- Для наката логической операции выполняется снова.
- Для отката логической операции выполняется обратная логическая операция.
Зарегистрированы исходный и результирующий образы записи.
- Для наката операции применяется изображение после.
- Для отката операции применяется образ до.

В журнал транзакций записываются различные типы операций, например:

начало и конец каждой транзакции;
любые изменения данных (вставка, обновление или удаление), включая изменения в любой таблице (в том числе и в системных таблицах), производимые системными хранимыми процедурами или инструкциями языка DDL;
любое выделение и освобождение страниц и экстентов;
создание и удаление таблиц и индексов.