Администрирование и мониторинг отслеживания измененных данных

Выбор подходящего средства

Определив цели мониторинга, следует выбрать соответствующие средства для этого типа мониторинга: В состав SQL Server и операционной системы Windows входит полный набор средств мониторинга серверов в средах с большим количеством транзакций. Эти средства подробно описывают состояние экземпляра компонента SQL Server Database Engine или экземпляра служб SQL Server Analysis Services.

Windows предоставляет следующие средства мониторинга приложений, запущенных на сервере.

• Системный монитор, который позволяет собирать и просматривать данные об использовании памяти, диска и процессора в реальном времени.
• Журналы и предупреждения производительности
• Диспетчер задач

Дополнительные сведения о средствах Windows Server и Windows см. в документации по операционной системе Windows.

SQL Server предоставляет следующие средства мониторинга компонентов SQL Server:

• Расширенные события
• Трассировка SQL
• Приложение SQL Server Profiler
• Программа распределенного воспроизведения
• Монитор активности
• SQL Server Management Studio Графическое отображение инструкции Showplan
• Системные хранимые процедуры
• Консольные команды базы данных (DBCC)
• Динамические административные представления и функции
• Функции
• Флаги трассировки

Важно!

Трассировка SQL и Приложение SQL Server Profiler являются устаревшими. Пространство имен Microsoft.SqlServer.Management.Trace, которое содержит объекты трассировки Microsoft SQL Server и Replay, также устаревшее.

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

Вместо этого используйте расширенные события. Дополнительные сведения о расширенных событиях см. в разделе Быстрое начало. Расширенные события в SQL Server и SSMS XEvent Profiler.

Примечание

Приложение SQL Server Profiler для рабочей нагрузки служб Analysis Services не устарел и будет по-прежнему поддерживаться.

Дополнительные сведения о средствах наблюдения SQL Server см. в статье Средства контроля и настройки производительности.

Условия, ограничения и вопросы использования репликации

В этом разделе описаны вопросы развертывания репликации при помощи Группы доступности AlwaysOn, в том числе предварительные условия, ограничения и рекомендации.

Предварительные требования

• Если используется репликация транзакций, а база данных публикации находится в группе доступности, издатель и распространитель должны иметь версию не ниже SQL Server 2012 (11.x). Подписчик может использовать SQL Serverболее низкого уровня.

• При использовании репликации слиянием и базы данных публикации в группе доступности:

  • Принудительная подписка: издатель и распространитель должны иметь версию не ниже SQL Server 2012 (11.x).

  • Подписка по запросу: базы данных издателя, распространителя и подписчика должны находиться на экземпляре с версией не ниже SQL Server 2012 (11.x). Это связано с тем, что агент слияния на подписчике должен иметь сведения о том, как группа доступности может выполнить отработку отказа на базу данных-получатель.

• Экземпляры издателя должны удовлетворять всем предварительным условиям, необходимым для участия в группе доступности AlwaysOn. Дополнительные сведения см. в разделе Предварительные требования, ограничения и рекомендации для групп доступности AlwaysOn (SQL Server).

Ограничения

Поддерживаемые сочетания репликации в Группы доступности AlwaysOn:

Репликация	Издатель	Распространитель1	Подписчик
Транзакционная	Да Примечание. Не включает поддержку двунаправленной и возвратно-поступательной репликации транзакций.	Да	Да
Одноранговая сеть2	Да	Да3	Да
Объединить	Да	Нет	Нет
Моментальный снимок	Да	Нет	Да

1 База данных распространителя не поддерживается для использования с зеркальным отображением базы данных.

2 Требуется SQL Server 2019 CU13 и выше.

3 Требуется SQL Server 2019 CU 17 или более поздней версии.

Рекомендации

• База данных распространителя не поддерживается в сочетании с зеркальным отображением базы данных, но поддерживается с Группы доступности AlwaysOn с учетом некоторых ограничений. Ограничения приведены в . Конфигурация репликации работает совместно с экземпляром SQL Server, на котором настроен распространитель. Именно по этой причине база данных распространителя не может участвовать в репликации или зеркальном отображении. Чтобы обеспечить высокий уровень доступности для распространителя, можно также использовать отказоустойчивый кластер SQL Server. Дополнительные сведения см. в статье Экземпляры отказоустойчивого кластера AlwaysOn (SQL Server).

  

  

  

  

  

  

  

  

  

  

• Отработка отказа подписчика в базу данных-получатель — это ручная процедура для объединения подписчиков репликации. Процедура, по существу, идентична методу, используемому для переключения на зеркальную базу данных подписчика. Подписчики репликации транзакций не требуют особого отношения при участии в Группы доступности AlwaysOn. Чтобы подписчики могли участвовать в группе доступности, они должны использовать SQL Server 2012 (11.x) или более позднюю версию. Дополнительные сведения см. в разделе Подписчики репликации и группы доступности AlwaysOn (SQL Server)

• Метаданные и объекты, которые существуют за пределами базы данных, не распространяются на вторичные реплики. Это касается имен входа, заданий, связанных серверов и так далее. Если после отработки отказа в базе данных-источнике нужны метаданные и объекты, их необходимо скопировать вручную. Подробные сведения см. в статье Управление именами входа для заданий, использующих базы данных в группе доступности Always On.

Распределенные группы доступности

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

Функции запросов

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

Функция cdc.fn_cdc_get_all_changes_ возвращает все изменения, произошедшие в течение указанного интервала. Эта функция создается всегда. Записи всегда возвращаются отсортированными, вначале по номеру LSN зафиксированной транзакции изменения, затем по порядковому значению изменения в его транзакции. В зависимости от выбранного параметра фильтра строки возвращается обновленная строка (параметр фильтра строки «all») или новое и старое значения обновленной строки (параметр фильтра строки «all update old»).

Функция cdc.fn_cdc_get_net_changes_ создается, если параметр @supports_net_changes имеет значение 1, если включена исходная таблица.

Примечание
Этот параметр поддерживается, только если в исходной таблице определен первичный ключ либо параметр @index_name использовался для идентификации уникального индекса.

Функция netchanges возвращает одно изменение на каждую измененную строку исходной таблицы. Если в течение интервала запроса для строки было зарегистрировано несколько изменений, то значения столбцов будут отражать конечное содержимое строки. Чтобы правильно определить операцию, необходимую для обновления целевой среды, возвращающая табличное значение функция должна учитывать как начальную, так и конечную операции со строкой в течение интервала запроса. При указании параметра фильтра строк «all» запрос netchanges будет возвращать операции вставки, удаления или обновления (новые значения)

Обратите внимание, что этот параметр всегда возвращает значение маски обновления как значение NULL, потому что вычисление статистической маски требует значительных затрат. Если требуется статистическая маска, отражающая все изменения строки, используется параметр «all with mask»

Если для последующей обработки не требуется разделения операций вставки и обновления, то используется параметр «all with merge». В этом случае будут присутствовать только два значения операции: 1— для операции удаления и 5 — для операций вставки и обновления. Этот параметр отключает дополнительные вычисления, позволяющие определить тип производной операции: операция вставки или обновления. Если в таком различении нет необходимости, то использование этого параметра может увеличить производительность.

Маска обновления, возвращаемая функцией запроса — это компактное представление всех изменений столбцов, связанных со строкой информации об изменениях. Обычно такие данные нужны только для небольшого подмножества отслеживаемых столбцов. Имеются функции, способные помочь при извлечении информации из маски в форме, которая напрямую может использоваться приложениями. Функция sys.fn_cdc_get_column_ordinal возвращает порядковый номер именованного столбца для данного экземпляра системы отслеживания. Функция sys.fn_cdc_is_bit_set возвращает четность бита в предоставленной маске на основе переданного функцией порядкового номера. Совместное использование этих двух функций позволяет эффективно извлекать информацию из маски обновления и возвращать ее в информации об изменениях. См. представленный ниже шаблон «Перечисление суммарных изменений с помощью параметра «All With Mask»», в котором показано использование этих функций.

Приложения односторонней и двусторонней синхронизации

Приложения, которым требуется синхронизировать данные с экземпляром компонента Компонент SQL Server Database Engine , должны иметь возможность направлять запрос об изменениях. Отслеживание изменений может послужить основой для односторонней и двусторонней синхронизации приложений.

Приложения односторонней синхронизации

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

Приложения двусторонней синхронизации

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

для

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

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

Обработка изменений в базе данных при отслеживании изменений

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

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

• Проверить наличие конфликтов.

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

• Сохранить контекстные сведения приложения.

  Приложение хранит данные, с которыми связана информация отслеживания изменений. Эта информация должна быть доступна вместе с другими данными отслеживания изменений, если изменения были получены из локальной базы данных. Типичный пример таких контекстных сведений – идентификатор хранилища данных, которое было источником изменений.

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

• CHANGETABLE(VERSION…)

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

• WITH CHANGE_TRACKING_CONTEXT

  Приложение может использовать это предложение для хранения контекстных данных.

Проверка на наличие конфликтов

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

В следующем примере показано использование функции CHANGETABLE(VERSION …) для проверки конфликтов самым эффективным способом — без отдельного запроса. В примере определяет для строки, заданной аргументом . может получить те же сведения, но это будет менее эффективным. Если значение для строки больше, чем значение , возникает конфликт. В случае конфликта строка не обновляется. Проверка необходима, поскольку для строки может не иметься информации об изменениях. Сведения об изменениях не существовали бы, если бы строка не была обновлена с момента включения отслеживания изменений или после очистки сведений об изменениях.

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

Настройка сведений о контексте

С помощью предложения WITH CHANGE_TRACKING_CONTEXT приложение может хранить контекстные данные вместе с информацией об изменениях. Затем эти данные можно получить в столбце SYS_CHANGE_CONTEXT, который возвращает функция CHANGETABLE(CHANGES …).

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

Обеспечение согласованности и правильности результатов

Приложение должно учитывать процесс очистки при проверке значения параметра @last_sync_version. Это объясняется тем, что данные могли быть удалены после вызова функции CHANGE_TRACKING_MIN_VALID_VERSION(), но перед тем, как было выполнено обновление.

Следует использовать изоляцию моментального снимка и внести изменения в транзакцию моментального снимка.

Примечание

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

Определение целей мониторинга

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

• Формирование основных критериев оценки производительности.
• Определение изменений производительности во времени.
• Диагностика проблем, связанных с производительностью.
• Определение компонентов и процессов, требующих оптимизации.
• Сравнение влияния различных клиентских приложений на производительность.
• Аудит активности пользователей.
• Проверка работы сервера под воздействием различных нагрузок.
• Проверка архитектуры базы данных.
• Проверка расписания обслуживания.
• Проверка планов резервного копирования и восстановления.
• Определение необходимости изменения конфигурации оборудования.

Задание очистки

В данном разделе предоставляются сведения о функционировании задания очистки для отслеживания измененных данных.

Структура задания очистки

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

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

Примечание

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

Если выполняется очистка, то значение нижнего предела всех экземпляров системы отслеживания изначально обновляется в одной транзакции. Затем производится попытка удаления устаревших записей из таблиц изменений и таблицы cdc.lsn_time_mapping. Настраиваемое пороговое значение ограничивает количество записей, удаляемое в любой одиночной инструкции. Неуспешное выполнение удаления в любой отдельной таблице не повлияет на выполнение операции в остальных таблицах.

Настройка задания очистки

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

Примечание

В Базах данных SQL Azure планировщик отслеживания измененных данных периодически вызывает хранимую процедуру для записи и очистки таблиц изменений. Таким образом, настройка процесса записи и очистки в Базе данных SQL Azure в настоящее время невозможна. Хотя планировщик запускает хранимые процедуры автоматически, их также можно запустить вручную.

Наблюдение за процессом

Наблюдение за процессом отслеживания измененных данных позволяет определить, правильно ли записываются изменения и насколько приемлема задержка при записи в таблицы изменений. Наблюдение также помогает выявить возможные ошибки. SQL Server включает два динамических представления управления, которые помогают отслеживать фиксацию измененных данных: sys.dm_cdc_log_scan_sessions и sys.dm_cdc_errors.

Выявление сеансов с пустыми результирующими наборами

Каждая строка в административном представлении sys.dm_cdc_log_scan_sessions представляет сеанс просмотра журнала (за исключением строки с идентификатором 1). Сеанс просмотра журнала является эквивалентом одного выполнения хранимой процедуры sp_cdc_scan. Во время сеанса просмотр может возвратить изменения или пустой результат. Если результирующий набор пуст, то для столбца empty_scan_count в представлении sys.dm_cdc_log_scan_sessions устанавливается значение 1. Если пустые результирующие наборы встречаются последовательно (например, при непрерывном выполнении задания отслеживания), то счетчик empty_scan_count в последней существующей строке увеличивается. Например, если в представлении sys.dm_cdc_log_scan_sessions уже существует 10 строк просмотров, возвративших данные об изменениях, и пять результатов подряд были пусты, то в представлении будет содержаться 11 строк. В столбце empty_scan_count последней строки содержится значение 5. Чтобы определить сеансы, возвратившие пустой результирующий набор, выполните следующий запрос.

Определение задержки

В административное представление включен столбец, записывающий задержку для каждого сеанса отслеживания. Задержка представляет собой время, прошедшее между фиксацией транзакции в исходной таблице и фиксацией последней отслеженной транзакции в таблицу изменений. Столбец задержки заполняется только для активных сеансов. У сеансов, значение в столбце empty_scan_count для которых больше 0, для столбца задержки устанавливается значение 0. Следующий запрос возвращает среднее время задержки для наиболее новых сеансов.

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

Еще одним важным показателем эффективности процесса отслеживания является пропускная способность. Это среднее число команд в секунду, обрабатываемых в каждом сеансе. Для определения пропускной способности сеанса следует разделить значение в столбце command_count column на значение в столбце продолжительности. Следующий запрос возвращает среднюю пропускную способность для наиболее новых сеансов.

Получение выборки данных с помощью сборщика данных

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

Настройка сбора данных

1. Включите сборщик данных и настройте хранилище данных управления. Дополнительные сведения см. в разделе Управление сбором данных.

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

3. В среде SQL Server Management Studioразверните вкладку Управление, затем вкладку Сбор данных. Щелкните правой кнопкой мыши пункт Сборщик данных о производительности CDC, затем пункт Запустить набор сбора данных.

4. В хранилище данных, которое было настроено в шаге 1, найдите таблицу custom_snapshots.cdc_log_scan_data. В данной таблице предоставлен архивный моментальный снимок данных из сеансов просмотра журнала. Эти данные могут быть использованы для анализа задержки, пропускной способности и других показателей производительности во времени.

Обзор

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

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

Как отслеживать изменения в SQL Server 2005

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

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