Для сохранения целостности структуры баз данных и обеспечения нормальной производительности необходимо проводить периодическое обслуживание. В этой статье рассмотрим какие задания по обслуживанию необходимо выполнять для баз данных 1С Предприятия, размещенных в MS SQL.
Настройка плана обслуживания баз данных MS SQL Server выполняется через программу Microsoft SQL Management Studio. Рассмотрим задачи, которые мы будем выполнять в рамках регулярного обслуживания баз данных:
- Создание полного бэкапа (раз в неделю, в воскресенье в 2:00);
- Создание разностного бэкапа (раз в день, с понедельника по субботу в 2:00);
- Очистка устаревших бэкапов (раз в день);
- Дефрагментация индекса (раз в день в 4:00);
- Обновление статистики (раз в день).
В чем отличие полного бэкапа от разностного?
Полное резервное копирование сохраняет всю базу данных целиком.
Разностное резервное копирование сохраняет все изменения созданные в базе данных с момента последнего полного бэкапа.
Такой подход к резервному копированию позваляет экономить свободное пространство на носителях информации.
Создание полного бэкапа базы.
В обозревателе объектов переходим к пункту "Управление \ Планы обслуживания". В контекстном меню выбираем "Создать план обслуживания".
В этом основном плане обслуживания будем создавать вложенные планы полного бэкапа, промежуточного (разностного) бэкапа, перестроение индекса и обновление статистики.
В созданном плане нажимаем кнопку "Добавление вложенного плана"
Вводим название "Полный бэкап" и описание. Задаем расписание для выполнения задания: Раз в неделю в воскресенье в 2:00.
Добавляем в созданный план задание. Для этого с панели элементов перетаскиваем в поле заданий вложенного плана элемент с названием Задача "Резервное копирование базы данных".
Открываем задание на редактирование: правой клавишей мыши по заданию, выбираем пункт "Изменить".
- Тип резервной копии: Полное;
- Базы данных: если выбрать "Все пользовательские базы данных", то будет выполняться бэкап всех созданных вами баз данных, но есть возможность указать на конкретные базы;
- Создать файл резервной копии для каждой базы данных: отмечаем пункт "Создавать вложенный каталог для каждой базы данных", чтобы удобнее было ориентироваться в бэкапах и указываем путь как папке, в которой будут храниться резервные копии;
- Отмечаем пункт "Проверять целостнойсть резервной копии";
- Устанавливаем параметр "Сжимать резервные копии".
Создание разностного бэкапа.
Создание плана на выполнение разностного бэкапа выполняется аналогично полному бэкапу.
Отметим некоторые отличия в настройке:
- Расписание выполнения заданий: с понедельника по субботу в 2:00;
- Тип резервной копии выбираем "Разностное"
Очистка устаревших бэкапов.
Для очистки устаревших бэкапов баз 1С Предприятия в MS SQL выбираем на панели элементов плана обслуживания Задачу "Очистка после обслуживания".
В моем случае разностный и полный бэкап хранятся в одной папке. Поэтому я добавляю только одно такое задание во вложенный план для разностного бэкапа. Если вы резервные копии будете хранить отдельно, то лучше создать отдельное задание в плане каждого бэкапа.
Перетаскиваем задачу с Панели элементов в план и задаем такие настройки:
- Удалить файлы следующего типа: Файлы резервных копий;
- Удалить из папки файлы с определенным расширением: указываем папку хранения бэкапов баз 1С;
- Включить вложенные папки первого уровня: отмечаем галочкой, потому-что у нас для бэкапов баз создаются отдельные папки
- Удалить файлы на основе возраста во время выполнения задачи: здесь все ограничивается лишь вашими потребностями и объемом жесткого диска, а мне достаточно 4 недель.
Чтобы в текущем плане после выполнения первого задания начало выполнятся следующее, их необходимо соединить между собой стрелками. Для этого выделяем первое задание и ведем стрелку от него к следующему.
Через стрелки можно задавать условие, при котором будет выполнять следующее задание: ошибка, успешное завершение, выполнение. Изменить условие можно щелкнув правой клавишей мыши по стрелке.
По умолчанию стрелка зеленого цвета. Это значит, что следующее задание будет выполняться только при успешном завершении первого. Это условие подходит для моего случая.
Переходим к очень важному и ответственному пункту: Перестроение индекса и обновление статистики.
Дефрагментация индекса (реорганизация или перестроение).
В процессе работы базы данных 1С Предприятия, в результате постоянной записи и удаления данных, образуются пустые (фрагментированные) области. По этой причине может увеличиваться бесполезный объем БД и замедляться скорость взаимодействия с ней.
Для устранения фрагментированных областей баз данных в MS SQL существует возможность проведения Реорганизации индекса и Перестроение индекса.
В чем разница между реорганизацией и перестроением?
Перестроение индекса означает, что фрагментация будет устранена путем удаления и пересоздания индексов.
При Реорганизации индекска происходит перестроение индексов в соответствии с логическим порядком. Этот способ наименее ресурсозатратный и является более предпочтительным для регулярного обслуживания баз данных.
В каких случаях требуется реорганизация индекса?
- Уровень фрагментации от 5% до 30%, то проводим реорганизацию.
- Фрагментация свыше 30% необходимо проводить перестроение индекса
Под выполнение этих задач очень подходит инструкция Transact-SQL со следующим содержимым:
DECLARE @SQL NVARCHAR(MAX)
DECLARE @MIN_IND_SIZE integer = 128
DECLARE @MIN_FRAGMENTATION_LEVEL integer = 10
DECLARE @CRITICAL_FRAGMENTATION_LEVEL integer = 30
DECLARE currentIndex CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
SELECT 'ALTER INDEX [' + ind.name + N'] ON [' +
SCHEMA_NAME(obj.[schema_id]) + '].[' + obj.name + '] ' +
CASE WHEN stat.avg_fragmentation_in_percent > @CRITICAL_FRAGMENTATION_LEVEL
THEN 'REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = ON)'
ELSE 'REORGANIZE'
END + ';'
FROM (
SELECT stat.[object_id], stat.index_id,
avg_fragmentation_in_percent = MAX(stat.avg_fragmentation_in_percent)
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') stat
WHERE stat.page_count > @MIN_IND_SIZE AND stat.index_id > 0
AND stat.avg_fragmentation_in_percent > @MIN_FRAGMENTATION_LEVEL
GROUP BY stat.[object_id], stat.index_id
) stat
JOIN sys.indexes ind WITH(NOLOCK) ON stat.[object_id] = ind.[object_id]
AND stat.index_id = ind.index_id
JOIN sys.objects obj WITH(NOLOCK) ON obj.[object_id] = stat.[object_id]
OPEN currentIndex
FETCH NEXT FROM currentIndex INTO @SQL
WHILE @@FETCH_STATUS = 0 BEGIN
print @sql
EXEC sys.sp_executesql @SQL
FETCH NEXT FROM cur INTO @SQL
END
CLOSE currentIndex
DEALLOCATE currentIndex Создаем вложенный план с названием "Дефрагментация индекса и обновление статистики" с расписанием раз в день в 4:00 и перетаскиваем в него из Панели элементов Задачу "Выполнение инструкции T-SQL".
Вставляем в задачу приведенную выше инструкцию T-SQL.
Обновление статистики.
Обновление статистики в базах данных MS SQL, как и дефрагментация индекса, имеет большое значение для повышения производительности работы SQL сервера. Благодаря обновлению статистики SQL Server способен более эффективно выполнять планы запроса.
Выбираем на панели элементов Задача "Обновление статистики" и добавляем ее во вложенный план "Дефрагментация индекса и обновление статистики".
- Базы данных: все пользовательские базы данных;
- Обновить: вся собранная статистика;
- Тип просмотра: полный просмотр.
При помощи стрелки связываем условием выполнение задачи по обновлению индекса с задачей по дефрагментации. Таким образом в случае успешного выполнения дефрагментации будет проведено обновление статистики.
Не забываем сохранить созданный план обслуживания...
... и убедиться, что запущен Агент SQL Server.
