Копіювання і відновлення баз даних в Microsoft SQL Server 2012/2008

1. Причини втрати даних

Можна виділити 5 основних причин втрати даних:

  1. програмні помилки – Виникнення умов, що призводять до аварійного завершення системи. Оскільки такі помилки грунтуються на дефектах програмної логіки, система баз даних не може виконати відновлення в подібних ситуаціях. Тому відновлення повинен проводити сам програміст, виконавши обробку таких винятків.
  2. Помилки адміністратора (людський фактор) – Випадки, в яких користувач з великими повноваженнями може ненавмисно (або навмисне) зруйнувати дані. Необхідно постаратися створити такий режим роботи, який зробить подібну ситуацію малоймовірною, проте зовсім виключати таку можливість не можна.
  3. Вихід з ладу комп’ютера (збій системи) – Виникає в результаті помилок в обладнанні та програмному забезпеченні. У цьому випадку вміст оперативної пам’яті комп’ютера може бути розгублено. В якості захисту, можна рекомендувати використання резервного сервера, дзеркальне відображення баз даних тощо.
  4. Відмова дискового накопичувача – Фізичне руйнування жорсткого диска. Рекомендується використання технологій RAID для зберігання файлів баз даних, крім того необхідно, щоб файли резервних копій зберігалися на дисковому носії, відмінним від пристрою, на якому розташовуються файли баз даних.
  5. Катастрофи (пожежа, повінь, землетрус) або крадіжка – Обійти цю ситуацію стане можливим, якщо пристрій, що містить необхідну для відновлення даних інформацію, буде зберігатися окремо від основного устаткування і не буде втрачено в результаті катастроф або крадіжок.

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

2. Типи резервного копіювання

Існує 2 режиму створення резервних копій:

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

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

3. Моделі відновлення баз даних

Вибір моделі відновлення бази даних визначає обсяг даних, який може бути втрачений під час руйнування бази даних, а також швидкість використання, розмір резервної копії протоколу транзакцій і період часу, необхідний для резервного копіювання протоколу. MS SQL Server підтримує три моделі відновлення:

  1. Повна модель відновлення – модель, при якій всі операції записуються в протокол транзакцій. Тому ця модель надає повний захист проти збоїв зовнішніх пристроїв.
    • переваги:
      1. Є можливість відновити базу даних з останньої підтвердженої транзакції, яка була збережена в файлі протоколу.
      2. Можливо відновити дані на будь-який момент часу.
      3. Можливо відновити дані на позначку в протоколі. Відмітки в протоколі відповідають заданій транзакції і додаються тільки якщо ця транзакція підтверджується.
      4. Протоколюються всі операції, пов’язані зі зміною індексу. В цьому випадку пересозданіе індексу виконується швидше, тому що не треба створювати їх заново.
    • недоліки:
      1. Відповідний протокол транзакцій може бути дуже великим за обсягом, і файли на диску, що містять цей протокол, можуть збільшуватися в розмірі дуже швидко. У зв’язку з цим можливе заповнення протоколу транзакцій.
      2. Протокол транзакцій повинен бути захищений від збоїв зовнішніх пристроїв. З цієї причини використання технології RAID для захисту протоколу транзакцій строго рекомендується.
      3. Потрібно значно більше часу на резервне копіювання.
    • висновок:
      Дана модель відновлення рекомендована для виробничих баз даних, якщо дозволяє апаратна частина сервера баз даних.
  2. Модель відновлення з неповним протоколированием – Те ж, що і повна модель відновлення, за тим винятком, що не ведеться протоколювання масових або bulk-операцій. А резервні копії протоколу транзакцій містять в цьому випадку результат такої операції.
    • переваги:
      1. Як і з повною моделлю відновлення, є можливість відновити базу даних з останньої підтвердженої транзакції, яка була збережена в файлі протоколу.
      2. Можливо відновити дані на будь-який момент часу, якщо не виконувалося об’ємних операцій.
      3. Можливо відновити дані на позначку в протоколі, якщо не було об’ємних операцій.
      4. Об’ємні операції виконуються набагато швидше, ніж під повною моделлю відновлення, так як вони не записуються.
      5. Для резервної копії протоколу потрібно набагато менше пам’яті, ніж в разі повного відновлення.
    • недоліки:
      1. Ті ж, що і при повній моделі відновлення.
      2. Чи не протоколюється операції зі зміною індексу. При відновленні, індекси потрібно створити заново.
      3. Відновлення з резервної копії протоколу довше, ніж при повній моделі відновлення.
      4. Немає можливості відновити дані на момент часу або на позначку в протоколі в разі об’ємних операцій.
    • висновок:
      Модель відновлення з неповним протоколированием використовується для виробничих баз даних в тих випадках, коли періодично відбуваються великомасштабні або об’ємні bulk-операції.
  3. Проста модель відновлення – У простій моделі відновлення протокол транзакцій усікається, якщо з’являється точка відновлення. Але це не означає, що взагалі не існує протоколювання, вміст протоколу використовується під час створення контрольної точки, де всі транзакції протоколу підтверджені або для них виконаний відкат.
    • переваги:
      1. Продуктивність всіх об’ємних операцій дуже висока.
      2. Низькі вимоги до обсягу пам’яті протоколу.
    • недоліки:
      1. Дані можливо відновити тільки на момент останнього резервного копіювання, а значить неприпустимі відновлення на конкретний момент часу або на позначку в протоколі. Всі зміни з останнього резервного копіювання повинні бути відновлені вручну.
    • висновок:
      Рекомендується використовувати просту модель відновлення для виробничих баз, тільки в тих випадках, коли сервер баз даних не володіє достатнім обсягом пам’яті або коли апаратна частина сервера баз даних нижче рекомендованої.

4. Методи резервного копіювання

MS SQL Server надає 4 різних методи резервного копіювання:

  1. Повне копіювання бази даних (Full) – При повному резервному копіюванні створюється резервна копія всієї бази даних цілком, вона включає в себе схему всіх таблиць, відповідні файлові структури, а також містить всі дані з цієї бази на момент завершення резервного копіювання. Це здійснюється завдяки тому, що повне копіювання захоплює стан бази даних на момент початку копіювання. А потім, якщо копіювання виконується динамічно, система записує будь-які дії, які мають місце в процесі створення копії.
    • переваги:
      Швидка швидкість відновлення бази даних.
    • недоліки:
      Повне резервне копіювання вимагає більше часу і вимагає більше простору для зберігання, ніж інші методи копіювання.
    • висновок:
      Для невеликих баз даних, які можна скопіювати швидко, найкраще застосовувати повне резервне копіювання. Але для великих баз потрібно крім повних резервних копій, створювати також і диференційовані (різницеві) копії баз даних.
  2. Диференційоване (разностное) резервне копіювання (Differential) – У цьому випадку створюється копія тільки частин баз даних, які змінювалися з моменту останнього повного копіювання баз даних. Ця повна резервна копія називається основою для різницевої копії. Як і в разі повного резервного копіювання, будь-які дії, які відбуваються під час створення копії, також копіюються.
    • переваги:
      Цей тип резервного копіювання мінімізує час, необхідний для копіювання, т. К. Кількість копійованих даних значно менше, ніж при повному копіюванні.
    • недоліки:
      Для відновлення необхідно завантажити спочатку повну копію баз даних, а потім разностную, що займає більше часу. І, відповідно, немає сенсу застосовувати разностное копіювання без повного.
    • висновок:
      Використовується на великих базах даних в зв’язці з повним резервним копіюванням.
  3. Створення резервних копій протоколу транзакцій (Transaction log) – Даний вид копіювання застосовується при повній моделі відновлення (або при неповному протоколюванні) баз даних, і враховує тільки зміни, записані в протокол транзакцій. Тому така форма резервного копіювання не ґрунтується на фізичних сторінках баз даних, а тільки на логічних операціях.
    • переваги:
      1. Найшвидша швидкість створення копії.
      2. На відміну від диференційованих резервних копій, де можливо відновити базу даних тільки на момент останнього копіювання, дозволяє відновити базу даних на конкретний момент часу.
      3. Правильне «закриття» протоколу транзакцій перед початком нової порції дій з цим протоколом. Одна з найбільш загальних помилок системи виникає, коли переповнюється протокол транзакцій. Якщо пам’ять, яка використовується для протоколу транзакцій, заповнюється на 100%, то система повинна зупинити всі виконують транзакції, поки пам’ять не буде звільнена. Ця проблема може бути усунена тільки при частому виконанні резервного копіювання протоколу транзакцій: кожен раз відбувається «закриття» порції існуючого протоколу і збереження на іншому зовнішньому пристрої. Ця порція протоколу стає повторно використовуваної, отже, система відновлює дисковий простір.
    • недоліки:
      Більш довгий процес відновлення, ніж при диференційованому резервне копіювання, де потрібна повна копія бази даних і остання разностная копія, в даному випадку буде потрібно повна копія і Усе існуючі копії протоколів транзакцій.
    • висновок:
      Рекомендується застосовувати на виробничих базах даних в зв’язці з повним резервним копіюванням.
  4. Резервне копіювання файлів або файлових груп – Даний метод дозволяє копіювати зазначені файли баз даних замість копіювання всієї бази даних. Окремі файли можуть бути відновлені з копії, дозволяючи виконати відновлення після збою, який вплинув лише на невелику підмножину файлів баз даних.
    •  висновок:
      Копіювання файлів бази даних рекомендується виконувати тільки коли база даних є дуже великий, і немає достатньо часу для повного копіювання бази даних.

5. Які бази даних і як часто копіювати?

  1.  База даних master є найбільш важливою базою даних системи, тому що вона містить інформацію про всі базах даних в цій системі. Тому резервне копіювання бази даних master має відбуватися на регулярній основі. Крім того, рекомендується створювати копію кожного разу, коли виконуються дії, що призводять до модифікації бази даних master. Ось деякі з них:
    • Виконання операторів і збережених процедур;
    • Створення, зміна та видалення бази даних;
    • Зміни протоколу транзакцій.
  2. Слід створити резервну копію всіх виробничих баз даних на регулярній основі. Додатково, необхідно робити резервну копію після того як з базами даних були виконані наступні зміни:
    • Після створення бази даних;
    • Після створення індексів;
    • Після створення протоколу транзакцій;
    • Після виконання непротоколіруемих операцій (операції, які не заносяться до протоколу транзакцій).

6. Приклад стратегії резервного копіювання

Нижче наводиться деякий план резервного копіювання, у реальній організації:

І деякі характеристики пов’язані з виконанням резервного копіювання бази даних DB1:

Апаратна частина:

  • Процесор Opteron 8220 8 × 1.0 Ghz
  • ОЗУ 24 Гб
  • HDD 250 Гб RAID 1 + 0

Програмне забезпечення:

Бази даних:

  • Загальний обсяг баз даних: ~ 95 Гб
  • Обсяг бази даних master: ~ 5 Мб
  • Обсяг бази даних DB1: ~ 23 Гб
  • Модель відновлення бази даних DB1: Повна
  • Приріст бази даних DB1 в день: ~ 200 Мб

Резервні копії бази даних DB1:

  • Час створення повної резервної копії: ~ 5 хв.
  • Час створення копії протоколу транзакцій: ~ 4 сек.
  • Розмір повної резервної копії (з стисненням): ~ 1,6 Гб
  • Розмір копії протоколу транзакцій: ~ 20 Мб

Необхідна дисковий простір для плану резервного копіювання DB1:

  • Зберігання повних резервних копій (1 місяць): ~ 50 Гб
  • Зберігання копій протоколу транзакцій (1 місяць): ~ 10 Гб
  • Зберігання повних копій від 1-ого числа кожного місяця (1 рік): ~ 20 Гб
  • Разом розмір дискового простору: ~ 80 Гб
  • Разом розмір дискового простору в% від розміру бази: ~ 350%

Час відновлення бази даних DB1:

  • Час відновлення повного резервного файлу: ~ 5 хв.
  • Час відновлення копії протоколу транзакцій: ~ 5 сек.
Ссылка на основную публикацию