Відстеження змін у корпоративній базі даних SQL Server

Для розробників однієї з складних проблем в SQL Server є відстеження того, які дані змінилися в базі. Ще більш серйозним завданням є створення простого рішення, яке не завдає серйозного удару по продуктивності робочих навантажень і нескладно у створенні, реалізації та управлінні. Так навіщо йти на всі ці турботи заради відстеження змін? Чи справді відстеження змін варто всіх цих зусиль? Двома часто цитованими прикладами є підтримка оновлень у сховищі даних і підтримка синхронізації гетерогенних, зрідка підключаються систем.

У сховищі даних зазвичай як-небудь представлені таблиці з бази даних оперативної обробки транзакцій (OLTP), але схеми таблиць можуть істотно відрізнятися. Це означає необхідність процесу вилучення, перетворення і завантаження даних (extract, transform, load – ETL), що переміщує дані з бази даних OLTP у сховищі даних.


Я можу собі уявити три можливі способи виконати це завдання. Перший – це періодичне оновлення всього сховища даних. Він очевидно непрактичний для великих обсягів даних, а також означає, що оновлення сховища даних не безперервні. Другий полягає у використанні схеми секціонування в базі даних OLTP, щоб дозволити процесу ETL працювати лише над даними, що з'явилися з часу попереднього процесу ETL. Цей метод працює тільки для вставки даних, а не їх оновлення чи видалення і вимагає складного механізму для управління визначенням меж розділів і перемикання розділів. Третій метод – Відслідковувати зміни в даних OLTP і виконувати процес ETL лише на змінених даних. Цей метод найбільш ефективний в плані обсягу даних.


Мобільні пристрої всюдисущі в сучасному бізнес-середовищі, а це значить, що робота з зрідка підключаються системами обов'язкове. З точки зору систем баз даних проблема полягає в ефективному відновленні сховища даних на пристрої, який підключається нечасто, особливо якщо саме сховище маленьке і радикально відрізняється за схемою від основної бази даних.


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


Тепер система бази даних може підготувати всю потрібну частину каталогу продуктів для завантаження на пристрій, а пристрій – завантажувати її. Іншими словами, всі дані завантажуються при кожному підключенні пристрою, навіть якщо вони не змінювалися. Очевидно, що це досить неефективний підхід.


Інший підхід – змусити базу даних відслідковувати зміни в потрібній частині каталогу продуктів. Тоді кишеньковий пристрій при підключенні запитує дані, що змінилися з моменту останнього підключення. У даному рішенні системі бази даних потрібно підготувати лише частина даних, і завантаження настільки ефективна, наскільки можливо.


Іншою причиною для відстеження змін є підтримка аудиту, що важливо в наші дні. Аудит відстежує вносяться зміни, а також те, коли відбулася зміна і хто його виконав. Це безумовно переносить справу на новий рівень, з жорсткими обмеженнями щодо стійкості, безпеки і вірності закінченого журналу аудиту.


Технології, які були розроблені для відстеження змін даних в SQL Server 2008, Не призначалися для підтримки аудиту, проте SQL Server 2008 пропонує новий компонент, іменований підсистемою аудиту SQL Server, Призначений спеціально для аудиту. Рік Біхем (Rick Byham) розповідав про компонент підсистеми аудиту SQL Server Audit у своїй статті "SQL Server 2008: Security” “SQL Server 2008: Безпека ", у квітневому випуску журналу TechNet Magazine за 2008 рік (доступний за адресою technet.microsoft.com/magazine/cc434691).


Як можна бачити, існує ряд переконливих причин відстежувати зміни в даних. Отже, важливе питання полягає в тому, як це робити найкраще.


Як відстежувати зміни в SQL Server 2005


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



Простіші способи відстеження змін в SQL Server 2008


SQL Server 2008 представляє дві нові технології, набагато спрощують відстеження змін в даних: відстежування змін та збір даних змін. Обидві функції відстежують змінилися дані (а також використовують операції вставки, оновлення та видалення, щоб простежити, як саме змінилися дані) і усувають потребу в призначених для користувача рішеннях. За винятком цих подібностей механізми цих функцій і об'єкти їх відстеження на ділі досить різні.


Збір даних змін використовує асинхронний механізм, що відслідковує всі зміни, що відбуваються в таблиці (чи певному наборі стовпців у таблиці), включаючи самі значення стовпців. Він розроблений для ситуацій, подібних до описаного мною раніше процесу сховища даних ETL.


Рис. 1 ілюструє споживання цих змін через тимчасові інтервали. Механізм збору даних змін витягує змінені дані в набір таблиць, з останніми змінами на верху таблиці. Процес ETL може потім запросити в таблиць, що містять дані змін, всі зміни, що відбулися за певний часовий період. Цей механізм дозволяє процесу ETL обмежити обсяг даних, який повинен бути спожитий у кожному пакеті.

Рис. 1. Дані історії змін споживаються через тимчасові інтервали


Відстеження змін, з іншого боку, використовує синхронний механізм, що відслідковує тільки зміна визначеної рядки в таблиці (і, при бажанні, список змінених стовпців). Це розроблено як відповідь на проблеми, на зразок описаного мною вище випадку з зрідка підключати системи. Цей підхід проілюстровано на рис. 2.

Рис. 2. Використання даних відстеження змін зрідка підключати системи


Обидві функції збільшують об'єм введення / виводу і кількість записів в журнал, але це також вірно і для призначених для користувача рішень – дані змін потрібно десь зберігати. Принципова відмінність цих функцій від призначеного для користувача рішення в тому, що таблиці, які використовуються для зберігання даних змін, повинні бути в тій же базі даних, що і відслідковують таблиці. Це означає, що всі дані змін будуть включені в резервні копії і потім передані по мережі, шляхом доставки журналів або дзеркального відображення баз даних.


У плані розробки дві ці функції повинні істотно зменшити складність відстеження змін. Жодна з них не вимагає змін схеми або тригерів. Обидві мають настроюються, автоматичні процеси очищення, сортують зміни за часом завершення транзакцій і надають вбудовані функції для добування інформації про зміни.


З точки зору управління у кожного з підходів є свої "за" і "проти". Як і у випадку з будь технологією, існує маса інформації, яку необхідно засвоїти, перш ніж братися за розробку і розгортання рішень, що використовують ці функції. У частині статті я дам огляд обох цих функцій, торкаючись того, як вони працюють і практичних моментів, які слід обміркувати, перед використанням їх у робочому середовищі.


Як працює збір даних змін


Збір даних змін у процесі транзакцій не робить нічого, що змінювало б відстежувати таблицю. Замість цього операції вставки, запису та видалення, записуються в журнал транзакцій як зазвичай і періодично збираються з журналу. Збір виконується завданням читання журналу агента SQL, і зібрані операції зберігаються в окремій таблиці, що називається таблицею змін. У якій-небудь інший час таблицю змін можна запитати, щоб отримати дані змін, використовуючи одну з двох функцій. Комбінація таблиці змін і двох функцій називається екземпляром збору. Рис. 3 показує потік даних, що використовує збір даних змін для управління процесом ETL сховища даних.


Включення збору даних змін – процес з двох частин. Спершу член фіксованого серверної ролі адміністратора системи повинен включити збір даних змін для бази даних, використовуючи sys.sp_cdc_enable_db. Потім, член фіксованого серверної ролі db_owner ("власника бази даних") повинен включити збір даних змін для конкретної таблиці, використовуючи sys.sp_cdc_enable_table. Ці вимоги до безпеки викликані потенціалом підвищеного використання місця на диску при невірній налаштуванні збору даних змін. Зрозуміло, чому власник таблиці не може сам включити цю функцію і піднести адміністратору бази даних сюрприз у вигляді підвищеного використання диска.


Коли для бази даних включається збір даних змін, до бази даних дещо додається, включаючи схему (іменовану cdc), деякі таблиці метаданих і тригер для запису подій мови визначення даних (Data Definition Language – DDL). (Одна з можливостей, яка мені здається чудової – занедбаність списку зміни DDL в таблицю.)


Включення збору даних змін також створює екземпляр збору для таблиці – таблицю змін і до двох функцій, для повернення даних змін. Ім'я таблиці змін ідентично імені примірника збору, з доданим _CT. Перша функція створюється завжди і використовується для повернення даних змін з таблиці змін. Друга функція створюється, якщо вказано допускати підсумкові зміни. Це означає, що повертається лише фінальний результат всіх зібраних змін, без усіх проміжних змін, які повертаються першою функцією. Імена двох функцій це, відповідно, fn_cdc_get_all_changes_ і fn_cdc_get_net_changes_, з доданим ім'ям екземпляра збору. Відзначте, що (подібно до функції відстеження змін) ця функція вимагає, щоб у таблиці був основний ключ або інший унікальний індекс.


При роботі з першою таблицею в базі даних, щоб включити збір даних змін, можна створити два завдання агента SQL: завдання збору і завдання очищення. Я кажу "можна", оскільки завдання збору ідентична використовуваної для збору транзакцій в транзакційної реплікації. Якщо транзакційна реплікація вже налаштована, то буде створена тільки завдання очищення та існуюча завдання читання журналу буде також використана як завдання збору. Це добре тим, що наявність двох завдань читання журналу дуже швидко призведе до проблем конфліктів з журналом і, отже, проблем продуктивності. Так чи інакше, агент SQL повинен працювати для використання збору даних змін.


Логіка всередині кошти читання журналу автоматично справляється з включенням і вимиканням таблиць для збору даних змін і змінює зібране з журналу транзакцій відповідно. Один важливий момент, який слід зауважити тут, полягає в тому, що після включення збору даних журнал транзакцій поводиться точно так само, як у випадку реплікації транзакцій – журнал не можна обрізати, поки засіб читання журналу не опрацювало його. Це означає, що операція контрольної точки, навіть у режимі відновлення SIMPLE, не буде обрізаний журнал, якщо він вже не був оброблений засобом читання журналу.


Крім того, якщо модель відновлення BULK_LOGGED використовується для зменшення кількості записів у журнал, збір даних змусить записувати в журнал все, крім операцій створення / скидання / відновлення індексу. Якщо ви ніколи не стикалися з такою поведінкою, майте на увазі, що воно може викликати проблеми з розміром журналу транзакцій, особливо якщо параметри за замовчуванням завдання збору змінені, щоб обробляти журнал менш часто.


За замовчуванням завдання збору працює безперервно, перевіряючи журнал кожні п'ять секунд і обробляючи максимум 500 транзакцій з журналу. За замовчуванням робота очищення виконується щодня о другій годині ночі і видаляє всі записи зміни даних, яким більше трьох днів з таблиць змін. Ці параметри можна змінити, використовуючи процедуру sys.sp_cdc_change_job після чого зміни не вступлять в силу, поки не перезапустити завдання, використовуючи sys.sp_cdc_stop_job and sys.sp_cdc_start_job.


Незважаючи на те, що процес читання журналу звичайно не буде мати великого ефекту на продуктивність системи, на сильно завантажених системах OLTP, з великими обсягами змінюються даних можливе виникнення конфліктів на журналі транзакцій через додавання всього лише одного процесу читання журналу. Сам конфлікт буде викликаний необхідністю для головок жорсткого диска переміщатися назад і вперед між точкою, в яку транзакції записують журнал, і точкою, в якому він зчитується процесом кошти читання журналу. У цьому випадку може бути необхідно змінити частоту роботи завдання збору, щоб продуктивність OLTP не страждала. Це, однак, створює класичний конфлікт між простором на диску і продуктивністю – журнал продовжить рости, поки завдання збору не обробить його.


Та ж проблема виникає при зміні частоти роботи завдання очищення або зміну періодів зберігання даних – таблиці змін продовжать рости, поки дані змін не будуть очищені. Це веде до загальних архітектурним роздумів про те, що потрібно відстежувати і скільки зберігати. Важливі питання, які варто врахувати, включають в себе наступні.



Збір даних змін може бути встановлений на просте відстеження змін у таблиці та відстеження частини стовпців в таблиці. Використання тільки частини може бути корисно, якщо частина несуттєвих стовпців – дуже широкі стовпці varchar, або стовпці великих двійкових об'єктів (BLOB), таких як текст, зображення або XML); інакше простір, що використовується таблицею змін може дуже швидко стати незручно великим.


З огляду на підвищений потенціал використання простору на диску, положення таблиці змін до файлової групи можна встановити за включення збору даних змін. Це спрощує управління простором що лежить в основі диска і значить, що всі дані про зміни потенційно можуть бути збережені в томі з менш витратним рівнем RAID, ніж основна база даних. Крім того, хоча параметри завдання очищення застосовуються до всіх примірників збору, окремий екземпляр збору можна очистити окремо і в будь-який час, якщо простір на диску стає проблемою. Використання простору диска можна легко відслідковувати, використовуючи sp_spaceused на таблицях збору.


Власне рядок вписана в таблицю змін містить метадані про транзакцію (номер послідовності журналу фіксації – LSN), а також порядок усередині транзакції, в якому відбулася зміна, що за операція проводилася, бітову маску змінених стовпців і значення власне стовпця.


Зміни DDL необмежені, коли включено збір даних зміни. Однак вони можуть здобути певний ефект на зібраних даних змін у разі додавання або скидання стовпців. Якщо відстежується стовпець скинутий, у всіх подальших записах в екземплярі збору для цього примірника буде стояти NULL. Якщо стовпець доданий, він буде проігнорований примірником збору. Іншими словами, форма примірника збору визначається в момент його створення.


Якщо потрібні зміни стовпців, то можливо створити інший примірник збору для таблиці (до максимуму в два примірники збору на таблицю) і дозволити споживачам даних змін перейти на нову схему таблиці. Але при цьому слід дотримуватися обережності, оскільки два примірники збору для відслідковується таблиці означають подвоєні введення / висновок, запис в журнал і споживання простору диска.


Не заглиблюючись дуже далеко, дані беруться з таблиць змін за допомогою описаних мною функцій. Функції беруть початковий номер послідовності журналу і кінцевий номер послідовності журналу, надані також й інші функції, що дозволяють перетворити звичайний час в номер послідовності журналу. Під час вилучення оновлень можна навіть вказати, чи потрібно побачити попереднє і наступне значення або тільки попереднє. Демонстраційний ролик, де я використовую збір даних змін, доступний за адресою www.technetmagazine.com/video.


Як працює відстеження змін


Як я вже згадував, відстеження змін – це синхронний процес, який куди менш складний, ніж збір даних змін. Це частина транзакції, яка вносить зміну в рядок у відслідковується таблиці і той факт, що рядок змінилася, відслідковується в окремій таблиці. Дана таблиця – це те, що називається внутрішньої таблицею, і її ім'я з місцем зберігання не можна контролювати. Я не вважаю це проблемою, оскільки в цій таблиці має бути значно менше даних, ніж у таблиці змін, використовуваної для збору даних змін. Але, як я зараз поясню, проблеми із простором на диски все ж таки можливі.


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


Відстеження змін включається і вимикається з використанням звичайного синтаксису ALTER DATABASE і ALTER TABLE і слід тієї ж моделі, що і збір даних змін, де воно має бути включено на рівні бази даних перед включенням на рівні таблиці. Послідовність операцій буде виглядати приблизно так:


ALTER DATABASE AdventureWorks2000 SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON); GO USE AdventureWorks2000; GO ALTER TABLE Person.Person ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON); GO


Необхідні дозволи для включення відстеження змін на рівнях бази даних і таблиці також відрізняються від необхідних для включення збору даних змін: db_owner і власника таблиці, відповідно. Коли відстеження змін включається на рівні бази даних, можна встановити період зберігання, а також вказати, чи будуть дані змін очищатися автоматично. Період зберігання за замовчуванням – 2 дні, з максимумом в 90 днів і мінімумом в одну хвилину.


Автоматичне очищення також включається за замовчуванням. При внесенні змін в ці параметри, необхідно оцінити ті ж пріоритети, про які я говорив стосовно збору даних змін, – що важливіше для програми: простір на диску або продуктивність.


За замовчуванням для кожного рядка збирається лише факт її зміни. Це виконується шляхом створення нотатки з первинного ключа змінилася рядки (що означає неможливість відстежувати зміни на таблиці без первинного ключа), разом з номером версії (після того як на базі даних включено відстеження змін, встановлюється номер версії, що дозволяє визначити порядок операцій) і тип операції, яка виконала зміна. Як варіант, можна також відстежити, які стовпці змінилися, це вимагає 4 байт на змінений стовпець.


Спостереження за простором на диску дещо відрізняється від відстеження змін, оскільки дані про зміни зберігаються у внутрішніх таблицях. Щоб знайти імена використовуваних внутрішніх таблиць, просто використовуйте представлення каталогу системи sys.internal_tables:


SELECT [name] FROM sys.internal_tables WHERE [internal_type_desc] = "CHANGE_TRACKING"; GO


Потім передайте ім'я в sp_spaceused, щоб побачити, скільки використовується дискового простору.


На відміну від збору даних змін, коли включено відстеження змін, існують обмеження на DDL, який можна виконувати на відслідковується таблиці. Найбільш помітним обмеженням є неможливість як-небудь змінювати основний ключ. Інше обмеження, що заслуговує згадки тут, полягає в тому, що ALTER TABLE SWITCH потерпить невдачу, якщо на будь-який з порушених таблиць було включено відстеження змін. Це, швидше за все, викликається тим фактом, що не має сенсу автоматично починати або видаляти відстеження змін для відключається з відслідковується, розбитою на розділи, таблиці, розділу або відслідковується таблиці, що включається в розбиту на розділи таблицю, відповідно.


Зміни витягуються з внутрішніх таблиць змін з використанням нової функції CHANGETABLES (CHANGES …). Вона приймає ім'я відслідковується на предмет змін таблиці плюс номер версії з попереднього рази, коли вона використовувалася, і повертає інформацію про всіх рядках, що змінилися з минулого разу. Існують різні функції для пошуку поточної і найбільш старої допустимої версії. Додаток може потім використовувати повернуту інформацію для запиту таблиці, відслідковується на предмет змін, щоб отримати реальні значення стовпців. Це, само собою, багатоетапний процес – спершу виходить поточна версія, потім ця версія використовується для запиту відстеження змін і потім власне таблиці запитуються на предмет даних стовпців, відповідних цій версії.


У постійно мінливих системі можливо отримати непослідовні або неправильні результати, якщо не підтримувати будь-якого роду незмінне подання версії, даних змін і власне даних стовпців. Щоб зробити це, можна використовувати ізоляцію знімка і помістити багатоетапний процес в пряму транзакцію. Цей спосіб працює ефективно, але має потенційні недоліки. Ізоляція знімка може позначитися на продуктивності робочого навантаження і впливає на продуктивність і використання простору в tempdb. Додаткові відомості про це можна знайти за адресою technet.microsoft.com/library/cc280358.

Схожі статті:


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

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*