Останнім часом у одній з груп на Facebook, за якою я слідкую, з'явився запит, де хтось питав, як вони можуть відслідковувати зміни в таблицях SQL Server, щоб перенести дані в іншу базу даних. На перший погляд, це здається простим завданням, не таким уже й складним, і навіть я писав про це раніше. Однак у людини, яка поставила запитання, були кілька вимог, що дещо ускладнюють ситуацію:
- Таблиці містять дані, які змінюються, а не просто додаються (тому простого "сканування" стовпця ID недостатньо).
- Час модифікації не зберігається в таблиці, тому неможливо визначити, що рядок було змінено, просто подивившись на цей стовпець.
- Вони не можуть використовувати CDC або налаштовувати щось на рівні бази даних (тригери та подібні рішення були запропоновані в коментарях, але ці варіанти не підходять для їхнього використання).
Це дещо складна ситуація, що призвела до кількох пропозицій, наприклад, копіювати всі дані в інший сервіс і виконувати з'єднання там, або використовувати запитувальний движок, який підтримує федеративні запити (як Trino чи Redshift), щоб виконати JOIN між цільовою та вихідною базою даних тощо.
Цілком можливо, що ці рішення є найпростішими для впровадження. Однак, думаючи про цю проблему, я задумався, чи можливо реалізувати незалежне "відстеження змін" і які наслідки це може мати. Можливо, з різних точок зору рішення, яке я пропоную в цьому дописі, не є економічно ефективним для впровадження (особливо якщо база даних невелика, у такому разі легко копіювати дані щоразу або виконувати запити, які знову з'єднують дані), але це технічно цікаво.
Отже, яке ж рішення?
Я збираюся спиратися на (і сподіваюся), що база даних, про яку йдеться, має резервні копії та налаштована на використання Full Recovery Model, що означає, що робляться резервні копії журналів транзакцій. Також я сподіваюся, що людина, яка поставила запитання, матиме доступ до цих резервних копій (хоча є можливість зробити це навіть без доступу, і ми обговоримо це пізніше).
Щоб зрозуміти суть рішення, важливо знати одну річ про файл журналу транзакцій: кожна зміна записується в нього до того, як буде застосована до файлів даних.
У випадку з Full recovery model це означає, що зміни зберігаються в журналі транзакцій до того, як буде зроблена резервна копія журналу транзакцій. Іншими словами, якщо ми прочитаємо вміст журналу транзакцій, ми зможемо визначити, що саме змінилося (і таким чином програма може використати цю інформацію).
Отже, ідея буде виглядати так:
- Ми збираємо всі файли резервних копій журналу транзакцій.
- Ми запускаємо скрипт на кожному файлі, який витягує інформацію про рядки, де були внесені зміни.
- Ми хочемо витягнути ID (тобто значення первинного ключа рядка, щоб унікально ідентифікувати його) з журналу транзакцій, а потім використовувати ці значення для виконання запиту до бази даних для отримання правильних значень.
Як ми можемо це зробити? Ось де нам допоможуть кілька документованих (і менш документованих) функцій, які дозволяють нам отримати доступ до "закулісної" частини бази даних і безпосередньо запитувати зберігання даних для вмісту сторінки або читати вміст журналу транзакцій.
Крок 1: Давайте прочитаємо вміст журналу транзакцій
Щоб прочитати вміст поточного журналу транзакцій, ми можемо використати функцію fn_dblog.
Щоб прочитати вміст файлу резервної копії журналу транзакцій, ми можемо використати функцію fn_dump_dblog. Вона приймає багато параметрів, більшість з яких нам не дуже важливі:
SELECT [Current LSN] as LSN, Operation, Context, [Page ID] as PageId
FROM fn_dump_dblog(NULL, NULL, 'DISKl1.bak',DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
WHERE Operation IN ('LOP_MODIFY_ROW', 'LOP_INSERT_ROWS', 'LOP_MODIFY_ROW') AND Context = 'LCX_CLUSTERED'
Ось результати:
Результати ```fndumpdblog~~
Зверніть увагу, що ми зосереджуємося на певних типах дій (зокрема, на тих, що цікавили автора запиту — вставка та оновлення; можливо, вам також будуть цікаві інші дії). Кожен запис у журналі містить PageId, що був змінений. PageId складається з ID файлу, за яким йде двокрапка, а потім ID сторінки в шістнадцятковому вигляді. Для наступного кроку нам потрібно перевести його в десятковий формат.
Крок 2: Читання вмісту сторінки
Щоб прочитати вміст сторінки, ми використовуємо команду DBCC PAGE:
DBCC PAGE ('SO-2016',1,581463,3) WITH TABLERESULTS
Ось результати:
Результати виконання DBCC PAGE
Зверніть увагу, що ми фактично можемо побачити вміст зміненого рядка. У межах кожної сторінки ми також бачимо object_id, що пов'язаний з таблицею, до якої належить сторінка (на одній сторінці будуть дані тільки з однієї таблиці), що допоможе нам пов'язати інформацію з таблицею і звідти з метаданими, такими як первинні ключі, за якими ми будемо запитувати.
Крок 3: Створення динамічного запиту та отримання останнього значення з таблиці
Хоча можливо витягти всі значення з журналу транзакцій, це вимагатиме вирішення деяких неприємних крайніх випадків (наприклад, сторінки переповнення, або в разі купок — обробка пересланих записів).
Набагато простіше (хоча й менш ефективно) витягти тільки значення первинного ключа (PK) рядка, а потім виконати запит до самої бази даних, щоб отримати дані всього рядка.
Приклад скрипту
Щоб об’єднати все разом (принаймні настільки, щоб пояснити поточний приклад), наступний скрипт PowerShell автоматизує ці кроки, враховуючи наявність резервної копії журналу транзакцій і те, що ім’я колонки первинного ключа фіксоване.
param (
[Parameter(Mandatory = $true)]
[string]$LogBackupPath,
[Parameter(Mandatory = $true)]
[string]$ServerName,
[Parameter(Mandatory = $true)]
[string]$DatabaseName
)
#if not installed, execute: install-module -name dbatools -scope currentuser
Import-Module dbatools -ErrorAction Stop
Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true -Register
Set-DbatoolsConfig -FullName sql.connection.encrypt -Value $false -Register
$pkColumnName = "Id" #у реальному світі це було б конфігуровано або виявлялося динамічно
$allTablesQuery = "SELECT name, object_id FROM sys.tables"
$tablesList = Invoke-DbaQuery -SqlInstance $ServerName -Database $DatabaseName -Query $allTablesQuery
$transactionLogContentQuery = @"
SELECT [Current LSN] as LSN, Operation, Context, [Page ID] as PageId
FROM fn_dump_dblog(NULL, NULL, 'DISK', 1, N'$LogBackupPath',DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
WHERE Operation IN ('LOP_MODIFY_ROW', 'LOP_INSERT_ROWS', 'LOP_MODIFY_ROW') AND Context = 'LCX_CLUSTERED'
"@
$transactionLogContent = Invoke-DbaQuery -SqlInstance $ServerName -Database $DatabaseName -Query $transactionLogContentQuery
$processedPages = [System.Collections.Generic.HashSet[string]]::new()
$processedIds = [System.Collections.Generic.HashSet[string]]::new()
foreach ($trow in $transactionLogContent) {
if ($processedPages.Contains($trow.PageId)) {
continue
}
$pageIdSplitted = $trow.PageId.Split(':')
$pageId = $pageIdSplitted[1]
$fileId = $pageIdSplitted[0]
$pageIdNum = [Convert]::ToInt32($pageId, 16)
$pageInfoQuery = "dbcc page ('$DatabaseName',$fileId,$pageIdNum,3) WITH TABLERESULTS"
$pageInfo = Invoke-DbaQuery -SqlInstance $ServerName -Database $DatabaseName -Query $pageInfoQuery
$pageGroupedByParent = $pageInfo | Group-Object -Property ParentObject
$tableObjectId = ($pageInfo | Where-Object { $_.Field -eq "Metadata: ObjectId" }).Value
$tableName = (($tablesList | Where-Object { $_.object_id -eq $tableObjectId }) | Select-Object -First 1).Name
$currentRow = @{}
$currentSlot = $null
foreach ($parentGrp in $pageGroupedByParent) {
if ($parentGrp.Name.StartsWith("Slot ") -eq $false) {
continue
}
if ($parentGrp.Name -ne $currentSlot) {
if ($null -ne $currentSlot) {
$pkColumnValue = $currentRow[$pkColumnName]
$rowId = "$tableName-$pkColumnValue"
if ($processedIds.Contains($rowId) -eq $false) {
$dbQuery = "SELECT TOP 1 * FROM $tableName WHERE $pkColumnName=$pkColumnValue"
Write-Host "Закінчили обробку вмісту рядка, тепер виконуємо запит до бази даних, щоб отримати поточний стан за запитом: $dbQuery"
$updatedRowData = (Invoke-DbaQuery -SqlInstance $ServerName -Database $DatabaseName -Query $dbQuery) | Select-Object -First 1
Write-Host "Ось рядок, який був змінений у цій резервній копії журналу транзакцій: $($updatedRowData| Select-Object * -ExcludeProperty ItemArray, Table, RowError, RowState, HasErrors | ConvertTo-Json)"
$processedIds.Add($rowId) | Out-Null
}
$currentRow = @{}
}
$currentSlot = $parentGrp.Name
}
foreach ($field in $parentGrp.Group) {
if ($field.Field -ne "" -and $field.Object -clike "Slot * Column *") {
$currentRow[$field.Field] = $field.Value
}
}
}
Write-Host "Обробка файлу '{$fileId}' сторінки '$pageIdNum' завершена"
$processedPages.Add($trow.PageId) | Out-Null
}
Обмеження поточної реалізації
Є кілька недоліків у цьому скрипті, які слід враховувати тим, хто планує використовувати цей метод у виробничих умовах:
- Ми будемо обробляти рядки, які насправді не були оновлені, як ті, що були оновлені.
Дані в SQL Server зберігаються на рівні сторінок, кожна сторінка завжди має розмір 8 КБ.
У транзакційному журналі ми можемо легко визначити, на якій сторінці відбулося змінення. Також відносно легко можна отримати вміст цієї сторінки. Однак те, що нам важче зробити, це правильно розібратися і зрозуміти, який саме рядок був змінений. Оскільки мета оригінального дописувача полягала в ідентифікації рядків зі змінами для їх подальшого перенесення в іншу інфраструктуру БД, і оскільки всі альтернативи включають масивне копіювання даних в іншу БД (як тимчасово для запитів, так і постійно з кожним новим передаванням), це рішення все ще є економічно ефективним. - Є кілька інших ситуацій, коли ми можемо визначити сторінку як таку, що була оновлена, хоча насправді нічого не змінилося: наприклад, якщо транзакція була виконана, зміни були внесені, а потім вона була скасована. Хоча можна ускладнити реалізацію для обробки цього випадку, оскільки транзакція може охоплювати кілька файлів резервних копій, які ми не обробляємо одночасно, повна обробка цієї ситуації потребує підтримки стану між виконаннями.
- Ми будемо виконувати велику кількість запитів (по одному для кожного рядка, який змінився в файлі, що обробляється). Це є проблемою в цій наївній реалізації, але її можна легко вирішити, обробивши запити пакетно для отримання всіх рядків разом.
- Ми не обробляємо сторінки переповнення: коли використовуються типи даних
varchar(n)абоnvarchar(n)тощо, і якщо сторінка рядка вичерпує свій розмір, SQL Server може вирішити виділити окрему сторінку в іншому місці в файлі даних для зберігання фактичних даних і з’єднати кілька сторінок за допомогою вказівників. У такому випадку на сторінці рядка зберігається лише вказівник на сторінку переповнення. Якщо сторінка, яка містить фактичний рядок, змінюється (наприклад, якщо оновлюється вказівник або яке-небудь інше значення), це рішення буде працювати коректно. Оскільки ми отримуємо поточний стан рядка за допомогою звичайного запиту, ми просто отримаємо справжні значення. Однак поточний код не обробляє ситуацію, коли була оновлена лише сторінка переповнення без зміни самого рядка. У такому випадку ми проігноруємо рядок і не отримаємо фактичне значення колонки PK. - Я протестував реалізацію лише на таблиці з кластеризованим індексом. Потрібні коригування для роботи з купами (heaps).
Якщо хтось хоче використовувати це рішення для виробництва, їм також слід забезпечити моніторинг і обробку можливих сценаріїв, які можуть виникнути (бажано з автоматичним відновленням). Наприклад, що відбувається, якщо файл резервної копії відсутній? Це можна виявити за допомогою прогалини в LSN, і в таких випадках може знадобитися виконати повне копіювання даних, щоб уникнути втрати оновлень.
Зображення заголовка, просто для цікавості.
Перекладено з: SQL Server manual change tracking using Transaction Log Backups