Настройка плана обслуживания MS SQL Server для 1С Предприятия.
Для сохранения целостности структуры баз данных и обеспечения нормальной производительности необходимо проводить периодическое обслуживание. В этой статье рассмотрим какие задания по обслуживанию необходимо выполнять для баз данных 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 со следующим содержимым:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
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.
