Более простой способ реализовать отслеживание данных об изменениях на основе триггеров в SQL Server

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

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

В этом блоге будет представлен обзор того, как CDC полезен в управлении данными, и будет обсуждаться сбор данных об изменениях на основе триггеров в SQL-сервер.

CDC и управление данными

CDC меняет правила игры в управлении данными. При использовании CDC вам нужно обновлять изменения только в целевом пункте назначения. В результате вы можете быстро выполнять процессы ETL.

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

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

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

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

Типы CDC

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

1. CDC на основе триггера

CDC на основе триггеров в SQL Server предполагает использование триггеров базы данных для фиксации изменений. Триггер — это хранимая процедура в базе данных, которая автоматически реагирует на определенное событие, например операцию вставки, обновления или удаления. При возникновении любого из этих событий триггер срабатывает и записывает изменения в отдельную таблицу, известную как таблица изменений. Хотя этот метод относительно прост в реализации и работает с различными типами баз данных, он может повлиять на производительность SQL Server из-за дополнительных затрат на управление триггерами.

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

2. CDC на основе журнала

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

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

3. CDC на основе запросов

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

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

Увеличение масштаба CDC на основе триггеров

CDC на основе триггера — это метод сигнализации целевой системе о том, что в исходной системе произошли изменения. Как следует из названия, процесс сбора измененных данных инициируется на основе определенных триггеров.

В базе данных SQL есть три триггера: «Вставить» для обозначения новой записи, «Обновить» для обозначения изменения и «Удалить» для удаления записи. Всякий раз, когда эти команды выполняются, они записываются в теневую таблицу или таблицу изменений, в которой ведется подробная запись всех изменений. После записи эти изменения затем распространяются в целевой пункт назначения.

Поскольку все триггеры основаны на SQL, CDC на основе триггеров является предпочтительным выбором для SQL Server.

Работа с Microsoft SQL Server

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

Существуют различные причины, по которым разработчики предпочитают работать с Microsoft SQL Server:

Сбор измененных данных в SQL Server

Microsoft предлагает систему отслеживания измененных данных в SQL Server, Azure SQL и Управляемом экземпляре Azure SQL.

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

Вот типичная база данных в Microsoft SQL Server:

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

CDC значительно облегчает извлечение данных из SQL Server и загрузку их в базу данных или хранилище данных. Инструмент ETL/ELT обычно извлекает данные из SQL Server и постепенно загружает их в хранилище данных. При добавочной загрузке вы загружаете только последние данные, а не всю базу данных, что экономит время и повышает производительность. Сочетание ETL/ELT и CDC гарантирует доступность надежного хранилища данных с минимальными ресурсами.

Вы можете использовать CDC в Microsoft SQL Server, используя собственные функции Microsoft или сторонний инструмент ETL.

Вариант 1. Включение системы отслеживания измененных данных Microsoft SQL Server в исходном виде

Вам необходимо выполнить определенные условия, чтобы включить CDC в SQL Server изначально. Только пользователь с фиксированной ролью сервера sysadmin или db_owner может включить CDC в базе данных. Поскольку CDC недоступен в веб-версии, вам необходима версия SQL Server Developer, Enterprise или Standard Edition.

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

Включение системы отслеживания измененных данных Microsoft SQL Server в исходном виде

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

Вы можете использовать сторонний инструмент, например Astera Centerprise для преодоления этих ограничений. Astera Centerprise — это более простой способ использовать комбинацию CDC и ETL/ELT. Этот инструмент без кода оснащен мощным механизмом ETL/ELT, который быстро обрабатывает большие объемы данных. В сочетании с интуитивно понятным пользовательским интерфейсом и короткой кривой обучения, Astera Centerprise упрощает реализацию CDC в SQL Server.

Вариант 2. Реализация системы отслеживания измененных данных на основе триггеров в SQL Server с помощью Astera Centerprise

Astera Centerprise существенно упрощает процесс реализации CDC в SQL Server. Просто перетащите базу данных SQL Server в конструктор потоков данных и выберите в раскрывающемся меню параметр «Включить сбор измененных данных в таблице».

Реализация системы отслеживания измененных данных в SQL Server с помощью Astera Centerprise

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

Чтение изменений базы данных из исходных таблиц

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

Чтение изменений базы данных из исходных таблиц

Запись изменений базы данных в файл формата Destination

Вы можете записать изменения базы данных в любой формат файла по вашему выбору. Astera Centerprise поддерживает различные форматы файлов, включая JSON, Excel, CSV и с разделителями. Вы можете просто перетащить пункт назначения в конструкторе потоков данных и сопоставить данные из источника в пункт назначения без написания какого-либо кода.

Запись изменений базы данных в место назначения формата файла с помощью Astera Centerprise

Запись изменений базы данных в хранилище данных или базу данных

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

Если у вас нет записи в месте назначения, опция Upsert вставит ее. Если запись уже есть, она обновит ее.

Использование Upsert в Astera Centerprise

После этого вы сможете легко сопоставить изменения в вашей базе данных или хранилище данных. Astera Centerprise имеет встроенное подключение к популярным базам данных и хранилищам данных, таким как MySQL, Snowflake, Amazon Redshift, PostgresSQL и другим.

Запись изменений базы данных в место назначения базы данных/хранилища данных с помощью Astera Centerprise

Автоматизация сбора данных об изменениях на основе триггеров в SQL Server

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

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

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

CDC как решение для аудита

CDC в SQL Server служит решением для аудита, обеспечивая систематическое и детальное отслеживание всех изменений, вносимых в данные в базах данных. Вот как это работает:

• Подробное отслеживание: CDC в SQL Server работает путем создания таблиц изменений, которые отражают структуру столбцов отслеживаемых исходных таблиц. В этих таблицах изменений хранятся подробные записи всех изменений данных, включая вставки, обновления и удаления. Поскольку CDC обеспечивает контрольный журнал и позволяет тщательно анализировать и исследовать изменения данных с течением времени, это имеет решающее значение для поддержания целостности данных.
• Ответственность: Помимо отслеживания изменений данных, сбор данных об изменениях в SQL Server также фиксирует важные метаданные, такие как характер изменения (вставка, обновление, удаление), время изменения и конкретная транзакция, связанная с изменением. Ведение подробного учета способствует подотчетности, поскольку пользователи знают, что их действия в базе данных фиксируются, что способствует развитию культуры ответственности и прозрачности.
• Юридические вопросы: CDC использует процесс, называемый агентом чтения журнала в SQL Server, для чтения журнала транзакций и заполнения таблиц изменений. Агент чтения журнала — это задание, которое постоянно выполняется в фоновом режиме, сканируя журнал транзакций базы данных и копируя любые изменения в базу данных распространителя. Активно отслеживая и контролируя изменения, вносимые в систему, организации могут соблюдать нормативные требования, такие как Общий регламент по защите данных (GDPR).
• Мониторинг в реальном времени: Сбор измененных данных в SQL Server осуществляется практически в режиме реального времени. Он использует экземпляры захвата, каждый из которых состоит из таблицы изменений и функций запроса, для постоянного мониторинга изменений данных в определенных таблицах. При возникновении изменений экземпляры отслеживания записывают подробности в таблицы изменений. Отслеживая данные в режиме реального времени, организации могут быстро обнаруживать любые несанкционированные или неуместные изменения и реагировать на них, тем самым повышая безопасность данных.

Почему вам следует выбрать Astera Centerprise?

Astera Centerprise устраняет сложность управления данными. Среда без кода и интуитивно понятный пользовательский интерфейс позволяют бизнес-пользователям взять на себя ответственность за свои инициативы, основанные на данных. Вот некоторые особенности, которые делают Astera Centerprise отличный выбор для включения CDC:

• Astera Centerprise поддерживает различные коннекторы для популярных баз данных, хранилищ данных и форматов файлов.
• Доступно Astera Centerprise, вы можете управлять CDC для всех реляционных баз данных на одной платформе, а не управлять ими по отдельности.
• Astera поддерживает встроенные преобразования, которые вы можете использовать для очистки и управления вашими данными.
• Вы можете использовать Astera функции профилирования и качества данных для обеспечения точности и надежности данных.
• Вы можете использовать Astera Centerprise функции автоматизации и планирования заданий для ускорения передачи данных

Скачать Astera Centerprise сегодня и попробуйте бесплатно в течение 14 дней.