Система відстеження змінених даних реєструє операції вставки, оновлення та видалення, які застосовуються до таблиці SQL Server. Тим самим забезпечується доступ до подробиць цих змін в легко оброблюваному реляционном форматі. Відомості про шпальтах і метаданих, які потрібні для застосування змін до цільової середовищі, відстежуються в змінених рядках і зберігаються в таблицях змін, що відображають структуру стовпців вихідних таблиць. Щоб споживачі даних могли систематично отримувати доступ до інформації про зміни, надаються повертають табличні значення функції.
Хорошим прикладом споживача даних, для якого призначена ця технологія, є додаток для вилучення, перетворення і завантаження даних (ETL). ETL-додаток поступово завантажує інформацію про зміни з вихідних таблиць SQL Server в сховище або вітрину даних. Хоча уявлення вихідних таблиць в сховище даних має відображати зміни в цих таблицях, використання технології, яка оновлювала б репліки вихідної таблиці, в даному випадку не підходить. Замість цього потрібен надійний потік даних про зміни, структурований таким чином, щоб споживачі могли застосовувати його до різнорідним цільовим уявленням даних. У SQL Server таку технологію надає система відстеження змінених даних.
Система відстеження змін в даних доступна тільки в наступних випусках: Enterprise, Developer і Evaluation SQL Server.
На наступному малюнку показаний основний потік даних для системи відстеження змінених даних.
Джерелом інформації про зміни для системи відстеження змінених даних є журнал транзакцій SQL Server. У міру того як у вихідних відслідковуються таблицях виробляються вставки, оновлення та видалення, в журнал додаються записи, що описують ці зміни. Журнал подається на вхід процесу відстеження змінених даних. Той зчитує дані журналу і додає інформацію про зміни в таблицю змін, пов'язану з відслідковується таблицею. Надаються функції для перерахування змін, що з'являються в таблицях змін в заданому діапазоні, а також для повернення відомостей у вигляді відфільтрованого результуючого набору. Відфільтрований результуючий набір зазвичай використовується процесом додатки для поновлення уявлення джерела в зовнішньому середовищі.
Перш ніж можна буде відстежувати зміни в окремих таблицях бази даних, необхідно явно активувати систему відстеження змінених даних в цій базі даних. Це можна виконати за допомогою процедури, що 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.
Всі об'єкти, пов'язані з екземпляром системи відстеження, створюються в схемі системи відстеження змінених даних для активованої бази даних. Іменем примірника системи відстеження має бути припустиме ім'я об'єкта, унікальне для всіх екземплярів системи відстеження цієї бази даних. За замовчуванням це ім'я
Перші п'ять стовпців таблиці змін для системи відстеження змінених даних є стовпцями метаданих. Вони надають додаткові відомості, які стосуються реєструється зміни. Інші стовпці відображають пізнані відслідковують стовпці вихідної таблиці по імені і типу. Ці стовпці зберігаються дані відслідковуються стовпців з вихідної таблиці.
Кожна операція вставки або видалення, яка була виконана в початковій таблиці, відбивається як один рядок в таблиці змін. Стовпці даних в рядку, що відображає результати операції вставки, містять значення стовпів після вставки. Стовпці даних в рядку, що відображає результати операції видалення, містять значення стовпів перед видаленням. Операція поновлення вимагає одного рядка для значень стовпця перед оновленням і другого рядка - для значень стовпця після оновлення.
Кожен рядок в таблиці змін містить також додаткові метадані, що дозволяють інтерпретувати операції зміни. Стовпець __ $ start_lsn ідентифікує реєстраційний номер транзакції в журналі (номер LSN), пов'язаний зі зміною. Зафіксований номер LSN визначає як операції зміни, які були проведені в рамках однієї транзакції, так і порядок транзакцій. Для впорядкування інших змін в тій же транзакції можна користуватися стовпцем __ $ seqval. У стовпець __ $ operation записується операція, пов'язана зі зміною: 1 = видалення, 2 = вставка, 3 = оновлення (вихідний образ) і 4 = оновлення (образ після операції). Стовпець __ $ update_mask - це змінна-бітова маска, де кожному відстежувати одну відповідає один біт. Для записів операцій вставки і видалення всі біти в масці поновлення завжди будуть встановленими. У рядків операцій оновлення будуть встановлені тільки біти, що відповідають зміненим стовпчиках.
Період дії системи відстеження змінених даних для бази даних - це час, протягом якого дані змін доступні для примірників відстеження. Період дії починається зі створення першого екземпляра відстеження для таблиці бази даних і триває до теперішнього часу.
Дані, що зберігаються в таблицях змін, будуть некеровано рости, якщо періодично і систематично не усікати ці дані. Процес очищення системи відстеження змінених даних відповідає за політику примусової очищення даних після закінчення терміну їх зберігання. Перш за все він переміщує нижню кінцеву точку періоду дії відповідно до обмежень по часу. Потім записи таблиці змін, у яких закінчився термін дії, видаляються. За замовчуванням ці дані зберігаються три дні.
У верхній кінцевій точці в міру того, як процес відстеження фіксує кожен новий пакет інформації про зміни, нові записи додаються до cdc.lsn_time_mapping для кожної транзакції, для якої є записи в таблиці змін. У таблиці зіставлень зберігаються реєстраційний номер транзакції в журналі і час фіксації транзакції (стовпці start_lsn і tran_end_time відповідно). Максимальне значення номера LSN, виявлене в cdc.lsn_time_mapping. представляє верхню кінцеву точку діапазону періоду дії. Відповідне їй час фіксації використовується як базове значення, з якого очищення даних після закінчення терміну зберігання обчислює нове значення нижньої кінцевої точки.
Оскільки процес відстеження витягує інформацію про зміни з журналу транзакцій, є природна затримка між часом, коли зміна фіксується в початковій таблиці, і часом, щоб ці поправки записується в пов'язану таблицю змін. Хоча ця затримка зазвичай мала, важливо пам'ятати, що інформація про зміни недоступна, поки процес спостереження, не обробить відповідні записи журналу.
Хоча зазвичай періоди дії для бази даних і окремого примірника відстеження збігаються, іноді це буває не так. Період дії примірника відстеження починається, коли процес відстеження розпізнає екземпляр відстеження і починає записувати пов'язані з ним зміни в таблицю змін. В результаті, якщо екземпляри відстеження створюються в різний час, кожен матиме свою нижню кінцеву точку. Стовпець start_lsn результуючого набору, який повертається функцією sys.sp_cdc_help_change_data_capture. показує поточну нижню кінцеву точку для кожного певного примірника відстеження. Коли процес очищення очищає записи таблиці змін, він коригує значення start_lsn для всіх примірників відстеження, щоб відобразити нову нижню кінцеву точку для кожного доступного набору інформації про зміни. Коригуються тільки ті екземпляри відстеження, поточні значення start_lsn яких менше, ніж нова нижня кінцева точка. Згодом, якщо не створюються нові екземпляри відстеження, періоди дії для всіх окремих екземплярів поступово збігаються з періодом дії для бази даних.
Період дії важливий для споживачів даних про зміни, оскільки інтервал вилучення для запиту повинен повністю покриватися періодом дії системи відстеження змінених даних для примірника відстеження. Якщо нижня кінцева точка інтервалу вилучення перекриває нижню кінцеву точку періоду дії, можлива втрата інформації про зміни внаслідок занадто агресивною очищення. Якщо верхня кінцева точка інтервалу вилучення перекриває верхню кінцеву точку періоду дії, процес відстеження не встигне обробити дані за час, представлене інтервалом вилучення, тому також можлива втрата інформації про зміни.
Функція sys.fn_cdc_get_min_lsn використовується для отримання поточного мінімального номера LSN для примірника відстеження, а функція sys.fn_cdc_get_max_lsn використовується для отримання поточного максимального номера LSN. Функції запиту системи відстеження змінених даних завершаться помилкою при запиті даних про зміни, якщо заданий діапазон номерів LSN вийде за межі цих двох значень номерів LSN.
Обробка змін стовпців в відстежуються вихідних таблицях - досить важке запитання для споживачів потоку даних. Хоча включення системи відстеження змінених даних у вихідній таблиці не перешкоджає такого роду DDL-змін, система відслідковування допомагає знизити їх вплив на споживачів за рахунок незмінності результуючих наборів, які повертаються через API-інтерфейс, навіть при змінах структури базової початкової таблиці. Структура з фіксованими стовпцями також відбивається в базових таблицях змін, до яких отримують доступ телефон запитувати його.
Для підтримки роботи з таблицею змін, що має фіксовану структуру стовпців, процес відстеження, який відповідає за заповнення таблиці змін, не враховує нові, не визначені для відстеження стовпці, якщо в початковій таблиці активована система відстеження змінених даних. Якщо відстежується стовпець видаляється, у відповідних записах змін вказуються значення null. Але якщо у існуючого стовпця змінюється тип даних, це зміна відбивається в таблиці змін, щоб не допустити втрату даних в відстежуються шпальтах. Процес відстеження також відправляє всі зміни в структурі стовпців відслідковується таблиці в таблицю cdc.ddl_history. Споживачі, які бажають отримати попередження про коригування, яку, можливо, доведеться внести в спадні додатки, використовують збережену процедуру sys.sp_cdc_get_ddl_history.
Як правило, поточний екземпляр відстеження продовжує зберігати свій формат, коли DDL-зміни застосовуються до відповідної вихідної таблиці. Однак для таблиці, що відбиває нову структуру стовпців, можна створити другий примірник відстеження. Це дозволить процесу відстеження відтворити зміни однієї початкової таблиці в двох таблицях змін, що мають різну структуру стовпців. Таким чином, в той час як одна таблиця змін буде поставляти дані в поточні робочі додатки, друга буде служити джерелом даних для середовища розробки, що приймає дані нового стовпчика. Дозволивши засобу відстеження одночасно заповнювати обидві таблиці змін, можна добитися переходу від одного формату до іншого без втрати інформації. Це може статися, коли відбудеться перекриття тимчасових шкал двох систем відстеження змінених даних. Після завершення переходу застарілий екземпляр відстеження можна видалити.
З одного вихідною таблицею одночасно можна зв'язати не більше двох примірників відстеження.