Что есть обслуживание базы данных

У меня база имеет:

Модель восстановления — Простая

Модель восстановления каждый системный администратор выбирает для себя Сам достаточно лишь раз прочитать документацию, как было дело со мной, ранее не особо разбиравшись что и для чего. Здесь я хочу составить небольшую шпаргалку по этой теме:

Простая модель восстановления — восстановить данные я могу только на момент полного резервировать базы данных. А все последующее добавление пользователями будет утеряно. На текущем месте мы просто делаем ежедневный бекап баз с последующей верификацией и максимальным сжатием. К примеру сделали ночью бекап, пользователи на следующий день поработали, а к примеру под вечер случился форс мажор, то откатившись из бекапа (только по приказу генерального), данные добавленные в течения дня будут безвозвратно утеряны. Вот такой вот минус.

Полная модель восстановления — восстановить данные можно на любой момент, но требуется в обязательном порядке осуществлять и резервное копирование журнала транзакций.

На заметку: перед любыми операциями всегда должна быть сделана полная резервная копия с последующим восстановлением из нее (и желательно задокумментированы все действия) с целью проверки, что бекап это бекап, а не битый бекап.

Резервная копия это обязательно, то также нужно и обслуживать базы — это так называемый «План обслуживания», запускать его следует в те часы когда его работа

ни как не скажет на текущем использовании базы, т. е. Преимущественно в ночное время. Данное время выбирается индивидуально с учетом особенностей Вашего рабочего места работы.

Создаваемый план обслуживания базы включает в себя следущую последовательность действий:

• Проверка целостности базы
• Перестроение индекса
• Обновление статистики
• Очистка процедурного кэша СУБД
• Резервное копирование базы данных
• Очистка после обслуживания
• Очистка журнала

Подключаюсь к серверу MSSQL и запускаю оснастку Microsoft SQL Server Management Studio:

Start — All Programs — Microsoft SQL Server 2008 R2 — SQL Server Management Studio, авторизуюсь: (local)(SQL Server 10.50.1600 — NEMDOMB\aollo) — после перехожу: Management (Управление) — Maintenance Plans (Планы обслуживания) и через клик правой кнопкой выбираю: New Maintenance Plan… (Создать план обслуживания). Присваиваю ему имя: LocalPlan, задаем посредством планировщика, как будет вызываться данный план:

После того как по стрелке выше на представленном скриншоте кликнули левой кнопкой мыши появится окно где настраивается работа запуска: (мне нужно каждый день в 02.00 ночи)

• Name: LocalPlan.Subplan_1
• Schedule type: Recurring.
• Enabled: Отмечаем галочкой
• Frequency:
• Occurs: Daily
• Recurs every: 1 day(s)
• Daily frequency:
• Occurs once at: 2:00:00

Остальные параметры я оставляю по умолчанию мне они не критичны.

Теперь создаваемый план LocalPlan наполним содержимым проверок/заданий. Для этого из левой части окна Toolbox выделяем нужное задание (Check Database Integrity Task (Проверка целостности базы данных) и перетаскиваем в правую часть:

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

После чего по такому же принципу добавляю в создаваемый план LocalPlan задания Перестроение индекса (Rebuild Index Task) и Обновление статистики (Update Statistics Task) и также заходим в данные задания свойства, выбираем нужную базу (в моем случае zup)

• Rebuild Index Task:
• Connection: Local Server Connection
• Database(s): zup
• Object: Tables and Views
• Reorganize pages with the default amount of free space

• Update Statistics Taks:
• Connection: Local Server Connection
• Database(s): zup
• Object: Tables and Views
• Update: All existing statistics
• Scan type: Full scan

На заметку:

• Перестроение индекса – пересоздает индекс с новым коэффициентом заполнения.
• Обновление статистики – обновляет сведения о данных таблиц для MS SQL, только после нужно провести очистку кэша.

Но пока задания хоть и наполнены свойствами просто выполнятся, но так не вполне корректно, нужно чтобы данные задания отрабатывали в порядке указанной очередности. Для этого задания нужно связать между собой в порядке очередности.

У каждого задания если по нему нажать левой кнопкой мыши один раз появится стрелка:

Берем данную стрелку и ведем ее к другому заданию этим мы производим объединение заданий между собой, т. е. Сперва выполнится одно а потом по стрелке запуститься другое.

Если щелкнуть по данной стрелке (у нее тоже имеются свойства), то можно обнаружить способы активации подразделенные на три цвета для удобства визуального вспоминания и анализа:

• Зеленый — Success (Успех)
• Красный — Failure (Ошибка)
• Синий — Completion (Завершение)

У связи может быть 3 цвета: синий, зеленый и красный, каждый из которых означает три типа срабатывания перехода: при простом завершении предыдущего задания – Завершение, в случае успешного завершения – Успех, а в случае возникновения ошибки при выполнение предыдущего задания – Ошибка.

Приводим создаваемый план к виду:

На заметку: может так случиться, что для создаваемого плана нет шаблонных заданий, в таком случае на помощь приходит задание: Execute T-SQL Statement Task в свойства которого вводится sql команда вашего действа. К примеру, по умолчанию отсутствует (да и вообще) задание по очистке кэша процедур, а посредством задания Execute мы создаем его, в свойства которого вписываем команду:

DBCC FREEPROCCACHE

Далее в создаваемый план добавлю задание которое будет уже осуществлять резервное копирование (в ручную этого делать не придется) — Back Up Database Task:

• Connection: Local Server Connection
• Backup type: Full
• Database(s): Specific databases (zup)
• Create a backup file for every database
• Folder: d:\backupbd
• Backup file extension: bak
• Verify backup integrity: отмечаем галочкой
• Set backup compression: Compress backup

Следом настраиваем задачу: Maintenance Cleanup Task (Очистка после обслуживания) входе которой удаляются старые архивы.

• Maintenance Cleanup Task:
• Connection: Local Server Connection
• Delete file of the following type: Backup files
• Search folder and delete files based on an extension:
• Folder: d:\backupbd
• File extension: bak
• File Age:
• Delete files older than the following: 1 Monts(s)

Следом настраиваем задачу: History Cleanup Task (Очистка журнала) в ходе которой происходит удаление журнала данных (завязанный на процессы резервного копирования и планов обслуживания баз).

• History Cleanup Task:
• Connection: Local Server Connection
• Backup and restory history: отмечаем галочкой
• SQL Server Agent job history:
• Maintenance plan history:
• Remove historical data older than: 2 Day(s)

Теперь когда задания в плане LocalPlan Настроены — данный план нужно сохранить: File — Save Selected Items

В итоге план обслуживания выглядит следующим образом:

Данный план будет работать в том случае если служба SQL Server Agent работает и запущена (и лучше бы он у Вас запускалась от имени системы)

В рамках теста запускаю созданный план в ручную:

Start — All Programs — Microsoft SQL Server 2008 R2 — SQL Server Management Studio, авторизуюсь: (local)(SQL Server 10.50.1600 — NEMDOMB\aollo) — Management — Maintenance Plan — выделяю LocalPlan и через правый клик на нем в ниспадающем меню выбираю Execute (Запустить), следом появляется окно работы данного плана:

Ожидаем, по окончании можно будет посмотреть отчет и журнал.

И в каталоге d:\backupbd\ уже находится резервная копия базы данных:

zup_backup_2015_11_21_130613_0503913.bak

На этом я хочу закончить данное повествование, оформляя ввиде заметки действия я еще раз для себя самого повторил все действия которые мне нужно будет сделать если я еще раз буду создавать план бекапа. А вообще как я считаю мне нужно почитать про составление планов обслуживания из книг по SQL серверу как факультатив. Только так можно понять и сделать выжимку знаний и навыков чтобы ниоткого не зависеть в дальнейшем, но послушать конечно же на до. До встречи, с уважением автор блога — ekzorchik.