SQL Server 2008 изменяет сбор данных и триггеры в контрольном журнале

Ответ 1

Я использую CDC в своем приложении WPF. Работает очень хорошо, но я обнаружил три проблемы:

• Вам часто нужно резервировать таблицы изменений (я использую оператор MERGE для добавления записей в исторические таблицы). Потому что запись остается в таблице изменений только около 2-3 дней, как я узнал. Не забудьте создать резервную копию таблицы cdc.lsn_time_mapping.
• Вы не можете обрезать таблицы с включенным CDC.
• Проблема с отключением cdc и повторным подключением (должна быть решена в новом пакете обновлений, как указано в MS). У меня есть эта проблема только один раз, так что это не так раздражает.

  http://blogs.technet.com/b/claudia_silva/archive/2010/06/04/cdc-cdc-hangs-when-i-try-to-disable-it.aspx

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

Ответ 2

Всего в SQL Server найдено четыре решения: далее:

• Отслеживание изменений SQL Server [CTC]
• SQL Server Change Data Capture [CDC]
• Аудит аудита SQL Server с триггерами [Generic - Manual]
• Аудит SQL Server

Отслеживание изменений SQL Server [CTC]

Pros

• Работает со всеми выпусками SQL Server
• DML Триггеры и дополнительные таблицы не требуются, поскольку одна временная таблица создается CTC с именем CHANGETABLE
• Минимальные затраты на дисковое пространство
• Пакетные функции, доступные для запроса данных
• Конфигурируемая политика хранения и автоматическая очистка CHANGETABLE, также автоматическая очистка может быть отключена при необходимости
• Не нужно обращаться к LDF файлу этой конкретной базы данных
• Очистка/усечение возможно при включении CTC

против

• Необходимо включить как в базе данных, так и в каждой требуемой таблице
• Механизм синхронного отслеживания
• Данные отслеживания всех таблиц хранятся в одной временной таблице с именем CHANGETABLE
• Невозможно получить исторические данные, а также не дать точную информацию о предыдущих и новые данные в столбцах отслеживания
• Проблема с композитными ключами
• Требуется первичный ключ
• Поддерживает только текущее состояние работы w.r.t данных я = Вставка, U = Обновить, D = Удалить
• Пользовательская группа SQL Server 'sysadmin' может включать только CTC

SQL Server Change Data Capture [CDC]

Pros

• Эти функции предоставляют пошаговое решение, после включения уровня базы данных.
• Эффективный и быстрый механизм асинхронного отслеживания
• Можно получить исторические данные, а также дать точную информацию о предыдущие и новые данные в теневой таблице
• Триггеры DML не требуются, так как новая таблица создается CDC с префикс 'dbo_' и суффикс '_CT' для каждой таблицы.
• Все таблицы и функции, относящиеся к CDC, будут передаваться с помощью 'cdc' схема.
• Все хранимые процедуры, связанные с CDC, будут передаваться с помощью "sys" или Схема 'cdc'

против

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

  CreatedAt datetime default (getdate()),  CreatedBy nvarchar (100) default (suser_sname()),  Обновлено по умолчанию datetime (getdate()),  Обновлено: nvarchar (100) default (suser_sname())

• Агент SQL Server должен быть включен для CDC, также CDC не может работать правильно, когда служба Database Engine или агент SQL Server служба работает под учетной записью NETWORK SERVICE.
• Необходимо включить как в базе данных, так и в каждой требуемой таблице
• Работает только с выпуском Enterprise, Developer и DataCenter
• Необходимо получить доступ к LDF файлу этой конкретной базы данных
• Агент SQL Server должен быть включен для захвата данных.
• Очистка/усечение невозможно на главной таблице при включении CDC, Автоматическая очистка теневой таблицы не может быть отключена после включения.
• Для журнала транзакций (файл LDF) требуется дополнительное пространство
• Группа пользователей Db 'db_owner' или роль сервера 'sysadmin' может только включить CDC
• Существуют ограничения при отслеживании изменений в столбцах, содержащих XML, Sparse, Timestamp, CLOB и BLOB.
• Когда у вызывающего абонента нет разрешения на просмотр исходных данных, функция возвращает ошибку 229

Аудит трейла с триггерами [Общее решение]

Pros

• Можно получить исторические данные, а также дать точную информацию о предыдущие и новые данные в теневой таблице
• Хорошо управляемый w.r.t выбор столбцов, операций [I = Вставка, U = Обновить, D = Удалить]
• Процесс может быть автоматизирован из-за сложности, процесса аудита может управляться какой-то рутиной или службой.
• Очистка/усечение, а также автоматическая очистка также могут быть автоматизированы.

против

• Точная копия таблицы с некоторыми дополнительными столбцами требуется как Тень Таблицы
• Комплексный процесс разработки w.r.t, управления данными и очистки.
• Производительность попадает, когда данные становятся огромными в теневых таблицах, Purging - это требуется
• Занимает много времени, чтобы автоматизировать процесс, учитывая все важные аспекты надежности, безопасности и производительности. Который в родном решение, DB Engine по умолчанию.