ІТ-інфраструктура для вашого підприємства
Розробники додатків і баз даних, ймовірно, стикалися з надзвичайно тривалим виконанням такими утилітами, як bcp (програма експорту даних), служба передачі даних DTS і реплікації даних, копіювання великої кількості даних в різноманітні джерела. Наприклад, передача знімка бази, яка містить 500 Мбайт даних, з обмеженнями по ключам і індексами, між двома потужними чотирипроцесорні серверами з оперативною пам'яттю об'ємом 1 Гбайт і дисковим масивом RAID 5 зажадає 2 ч в робочий час або 1,5 ч в нічний час. Подібна продуктивність неприйнятна для серверів промислового призначення з цілодобовою навантаженням або розподіленого сховища даних підприємства, час стану якого в непікові може бути дуже мало, щоб забезпечити перенесення великої кількості даних на всі сервери. Отже, як можна прискорити процес розподілу необхідних даних?
Я зупинюся на тому, що потрібно оптимізувати процес копіювання великих знімків даних. Реплікація знімка даних копіює набір даних від вихідного SQL Server при її виклику реплікації, навіть якщо одночасно відбувається виконання інших запитів і зміна тих же даних. Реплікація знімка копіює цей набір даних відразу або пізніше на інший SQL Server. SQL Server, що приймає дані, бачить їх такими, якими вони були присутні на вихідному сервері в момент копіювання, поки наступна реплікація не оновиться повний набір даних. DTS копіює набір даних з джерела ODBC (або OLE DB) в інший цільовий ODBC-приймач негайно. І джерело, і приймач можуть бути абсолютно різними провайдерами даних ODBC. Bcp копіює одну таблицю або подання з SQL Server в файл власного формату, символьний файл або, навпаки, з файлу в таблицю або уявлення SQL Server.
Як працює реплікація знімка
На другій стадії агент розподілу Distribution Agent копіює набір файлів .bcp в базу-джерело (передплатник) за допомогою утиліти distrib.exe. Утиліти snapshot.exe і distrib.exe використовують bcp для того, щоб копіювати статті по одній за раз, агент знімків копіює дані в файли .bcp виконанням простого пропозиції SELECT, яке може містити фільтр за стовпцем і оператор WHERE (для фільтрації рядків). Потім агент Snapshot Agent записує результуючі набори в файли .bcp. Коли агент розподілу копіює (вставляє) файли .bcp в таблиці бази даних, йому доводиться видаляти існуючі рядки, а також протоколювати видалення і вставку і збирати заново індекси.
Перед початком другої стадії реплицирования даних необхідно створити для цього робочу середу. Розглянемо кроки, виконувані за замовчуванням для настройки реплікації знімків даних.
Екран 1. Призначення різних агентів розподілу різних публікацій.
Зв'язування знімків і завдань агента розподілу. Утиліта Enterprise Manager автоматично визначає завдання SQL Server для кожного Distribution Agent. Ці агенти виконують реплікацію, проходячи по етапах завдань. За замовчуванням завдання агента складається з трьох етапів - Log agent startup message, Run agent і Detect nonlogged agent shutdown. На Екрані 3 ці кроки показані на закладці Steps в вікні властивостей CGIS1-EGH-address-28 Properties-CGIS1. На Екрані 4 відображена команда, яка працює на етапі Run agent, коли використовується snapshot.exe.
Екран 4. Команда запуску Snapshot Agent.
Повний синтаксис команди наступний:
Малюнок 2. Файли сценарію T-SQL (.idx) для визначення індексів схеми.
Нарешті, дані зберігаються в файлах .bcp. Кожна зі статей має набір з файлів з розширенням .sch. idx і .bcp.
Також за замовчуванням завдання Distribution Agent виконує ті ж три кроки, що і Snapshot Agent. Однак на етапі Run agent виконується distrib.exe, як видно з коду на Екрані 5. Повний синтаксис цієї команди такий:
Екран 5. Команда запуску Distribution Agent.
Спочатку виконуваний файл distrib.exe застосовує схеми з файлів з розширенням .sch до бази підписки (для зручності я назвав його кроком sch). На наступному кроці використовуються файли .bcp, для того щоб переписати дані з файлів з розширенням .bcp в таблиці, які були створені на етапі sch (етап bcp). Нарешті, застосовуються індекси з файлів з розширенням .idx (етап idx).
Виконуваний файл Snapshot.exe генерує набір файлів з розширеннями .sch і .idx відповідно до властивостей статті, які встановлюються на закладці Snapshot діалогового вікна властивостей відповідної статті. Наприклад, як показано на Екрані 6,
Екран 6. Установка властивість статті.
вибір режиму Delete data in the existing table that matches the row filter statement ( «Видаляти дані в існуючих таблицях, які відповідають умові фільтра») в діалоговому вікні під назвою ADDRESS Properties додає команду DELETE FROM [ADDRESS] в файл .sch (див. Малюнок 1) для статті ADDRESS. Однак якщо вибрати Delete all data in the existing table (using TRUNCATE) ( «Видаляти всі дані в існуючих таблицях (використовуючи TRUNCATE)») (див. Екран 6), з'явиться команда TRUNCATE TABLE [ADDRESS].
Малюнок 1. Файли сценарію T-SQL (.sch) для визначення схеми статті.
Де очікувати перешкод
Агенти знімка і розподілу виконують свої завдання відповідно до завдання, але пара таких завдань може стати перешкодою в роботі сервера баз даних, яка буде негативно позначатися на продуктивності. Давайте оцінимо продуктивність агентів, застосовуючи стандартний набір критеріїв.
Введення критеріїв виконання. Щоб визначити час виконання програми, можна або емпірично оцінити його виконання, або математично оцінити його алгоритм. У цьому розділі я зроблю і емпіричну, і математичну оцінку кроків виконання агентів, щоб порівняти результати, отримані різними методами. Для дослідження необхідної кількості часу математично вважається, що час виконання алгоритму збільшується при збільшенні кількості даних на вході. Тенденція зростання часу виконання показує, як алгоритм добре масштабується з навантаженням. Наприклад, якщо час, витрачений на обробку даних розміру n, зростає в лінійній залежності від зростання кількості даних (наприклад, обробка 1000 записів займає 2 с, а обробка 10 тис. Записів займає 20 с), то час виконання алгоритму - лінійно разом з розміром даних. Ця тенденція зростання як функція від кількості даних називається тимчасової залежністю алгоритму і позначена як O (n) (або порядку n) для лінійної залежності від часу. Алгоритм, який завжди завершується за один і той же час незалежно від того, скільки даних запропоновано на вхід, має постійну величину залежно від часу, яку я означив як O (c).
Оскільки агент знімків не уповільнює процес реплікації знімка, виною всьому повинен бути агент розподілу. Застосування формул до кроків цього агента підкаже місце виникнення перешкод.
Оптимізація швидкості виконання
Очевидно, що перешкоди, які виникають, коли користувачі намагаються копіювати велику кількість даних, можуть зробити реплікацію великомасштабних знімків марною. Однак можна зробити кроки, для того щоб уникнути цих проблем.
Позбавлення від перешкоди, викликаної оператором DELETE. Можна легко усунути перешкоду, яку оператор DELETE створює в кроці sch, вибираючи параметр «Видалить, всі дані в існуючій таблиці (використання TRUNCATE)» на закладці Snapshot, в діалоговому вікні властивості статті, зображеному на Екрані 6. В результаті ми отримаємо команду TRUNCATE
замість DELETE FROM
На жаль, ця ідея не працює з двох причин. По-перше, можна подумати, що слід використовувати файл .idx, створений агентом знімка, для того, щоб перебудувати обмеження по зовнішнім ключам і індексам, які були видалені налаштованим сценарієм. Однак файл .idx не включає в себе обмежень по первинним і унікальним ключам, тому що в файлі .idx агент знімка захоплює ці ключі як індекси. Наприклад, на рисунку 2 UNIQUE CLUSTERED INDEX [PK_ADDRESS] викликано умовою первинного ключа на ім'я [PK_ADDRESS], але продовжує існувати тільки як індекс. По-друге, хоча параметр @schema_option збереженої процедури sp_addarticle дозволяє відключити сценарій агента знімка і використовувати параметр @creation_script, настройки параметра @creation_script також відключають генерацію файлів сценаріїв .sch і .idx. Тому жоден файл .idx НЕ буде доступний для відтворення індексів, віддалених файлом @creation_script. Установка параметра @creation_script в розширеному сценарії не дозволяє досягти мети.
Налаштування реплікації знімка
Екран 7. Змінені кроки завдання для Distribution Agent.
Підстроюємо під своє середовище
Єдина перешкода на шляху до оптимізації полягає в тому, що між кроками drop і recreate завдання агента розподілу крок drop видалив все індекси і ключі всіх цільових таблиць для одного передплатника. Дистанційні індекси і ключі можуть викликати уповільнення виконання одночасних запитів і порушити обмеження по ключам протягом цього вікна. Однак для більшості налаштованих тільки на читання сховищ даних головна вимога полягає в тому, що синхронізація даних сховища і даних уявлень повинна виконуватися швидко в межах обмежених непікові робочих годин при мінімальній підтримці паралельних запитів. Оптимізація пристосована для таких умов дуже добре.
Малюнок 3. Приблизний план виконання для пропозиції DELETE.
Чому не транзакційних реплицирования?
Можливо, у когось виникло питання, чому я не розглядав транзакційних реплицирования для розподілу тільки змін в великому знімку на цільові сервери. Давайте розглянемо приклад з реального світу, який проілюструє причину мого вибору. Хоча при використанні транзакційного реплицирования можна копіювати тільки зміни замість цілого знімка, що зменшило б обсяг даних дуже сильно, транзакційні реплицирования застосовується для багатьох ситуацій. Наприклад, географічна інформаційна система (GIS), в якій я працюю, підтримує додаток GIS, що об'єднує дані про план ділянки, його вартості та цільове використання. Однак міські служби ці дані не обслуговують. Замість цього дві окремі групи в кожній з трьох управ, податкова і земельна (а також департамент будівництва), підтримують дані в різних форматах. Повний знімок даних плану ділянки приходить в форматі Microstation? S CAD, і знімки даних оцінки вартості і цільового використання прибувають в текстовому форматі з роздільниками. Додаток GIS очікує табличні дані від SQL Server, а не ці файли, так що я повинен імпортувати файли в базу даних, перетворити дані з різних джерел в єдиний набір схем і усунути дублікати. Також через те, що імпортовані файли - це повні знімки замість набору змін, і перетворення - зовсім не один в один, зберігання послідовного набору змін даних занадто важко і не варто витраченого часу. Завантаження, перетворення і об'єднання повних наборів зовнішніх даних простіше і ефективніше. Для надання такого повного набору імпортованих даних декільком вузлам ми копіюємо повний знімок.
Крім того, якщо обраний режим Delete all data in the existing table (with TRUNCATE) ( «Видаляти всі присутні дані (Оператором TRUNCATE)») в діалоговому вікні властивостей статті, новий сценарій видаляє обмеження по зовнішнім ключам для цільової таблиці в базі підписки, виконуючи нову збережену процедуру sp_Msdropfkreferencingarticle в базі даних master. Потрібно мати на увазі, що агент розподілу не відновлює ці віддалені ключі до кінця роботи реплікації знімка. Крок recreate в моєму випадку відновлює всі обмеження по ключам і індекси.
Поділіться матеріалом з колегами і друзями