Составление планов обслуживания SQL для нужд 1С: Предприятия 8.х

Сегодня рассмотрим один из вариантов обслуживания баз 1С в СУБД MS SQL.Содержание:1. Немного теории по планам обслуживания2. Постановка задачи по созданию планов обслуживания3. Создание плана обслуживания (Полная копия)4. Создание плана обслуживания (Разностная копия)5. Создание плана обслуживания (Резервная копия журналов транзакций)6. Мониторинг планов обслуживания1. Немного теории по планам обслуживания Может многие со мой не согласятся, но для меня главной целью использования Планов обслуживания в MS SQL является создание резервных копий. Местные ITишники либо еще не делают резервные копии, либо уже делают, после печальных последствий отсутствия резервных копий. Да, не спорю, Планы обслуживания также нужны для оптимизации БД и выгрузки журналов транзакций, в последнем случаи, если не выполнять выгрузку журналов транзакций, у вас может вырасти база данных и занять все пространство на диске, 1С встанет колом и пользователи не смогут работать с базой, а вам придется выполнять шринк (Shrink) базы, это наверно самое страшное для ITишники после поломки базы и отсутствии резервных копий. Но об шринке (Shrink) поговорим в другой раз. MS SQL Server поддерживает три модели восстановления: 1) Simple (Простая) — хранится только необходимый для жизни остаток журнала транзакций. 2) Full (Полная) — хранится весь журнал транзакций с момента последнего резервного копирования журнала транзакций. 3) Bulk logged (С неполным протоколированием) — часть операций записываются в очень компактном формате. В остальном идентична Full. Модель восстановления базы можно посмотреть, в свойствах базы данных, на вкладке Параметры. Там же ее можно поменять. На практике я использую Full (Полная). MS SQL поддерживает три типа формирования резервных копий: 1) Full (Полная копия) 2) Differential (Дифференциальная копия, Разностная копия) 3) Log (Резервная копия журналов транзакций) Не путайте понятия: полная модель восстановления и полная резервная копия — разные вещи. Рассмотрим подробно три типа формирования резервных копий. 1) Полная резервная копия Позволяет восстановить состояние базы данных на некоторый момент времени. Состоит из копии файлов данных и журнала транзакций на момент завершения формирования резервной копии. 2) Разностная резервная копия Хранит данных, изменившиеся с момента последней Полной резервной копии. При восстановлении нужно сначала восстановить Полную резервную копию в режиме NORECOVERY, потом можно применить любую из последующих Разностных копий. За счет этого можно значительно снизить объём дискового пространства для хранения резервной копии. Обратите внимание: без предыдущей Полной резервной копии Разностная копия бесполезна. Каждая последующая Разностная копия будет хранить все данные, входящие в предыдущую Разностную резервную копию, сделанную после предыдущей Полной копии. Поэтому каждая следующая Разностная копия больше предыдущих, пока снова не сделать Полную копию. Соответственно для восстановления на какой-то момент времени достаточно последней Полной резервной копии и последней Разностной копии. Промежуточные копии для восстановления не нужны. 3) Резервная копия журналов транзакций Содержит копию журналов транзакций за некоторый период. Обычно с момента прошлой Резервной копии журналов транзакций до момента формирования текущей Резервной копии журналов транзакций. За счет этого Резервные копии журналов транзакций позволяют (с учетом Полной и Разностной копий) восстановить базу данных на любой момент времени. Резервная копия журналов транзакций высвобождает место в файле журнала транзакций, что позволяет ITишники избавиться от шринка базы данных. Обратите внимание: набор Резервных копий журналов транзакций по сути бесполезен, если он не является непрерывной цепочкой, причем момент начала последнего успешного Полного или Разностного резервного копирования должен быть внутри периода этой цепочки.2. Постановка задачи по созданию планов обслуживания В организации N работают по шестидневке с 8:00 до 17:00. Обед с 12:00 до 13:00. Имеется в MS SQL база данных с именем Moodle. Что нужно сделать: 1) Проверить модель восстановления базы данных, должна быть Полная. 2) Создать план обслуживания, который будет создавать Полную резервную копию базы данных каждое воскресение в 17:00. Очищать хранилище от устаревших резервных копий старше 15 дней. 3) Создать план обслуживания, который будет создавать Разностную копию базы данных каждый день в 21:00 кроме воскресения. 4) Создать план обслуживания, который будет создавать Резервную копию журналов транзакций два раза в день, в 12:00 и в 17:00, кроме воскресения.3. Создание плана обслуживания (Полная копия) Запускаем SQL Server Management Studio, в Обозревателе объектов проходим по ветке Управление — Планы обслуживания. Правой кнопкой по пункту Планы обслуживания и в контекстном меню выбираем Создать план обслуживания… Указываем имя, к примеру: Moodle. В открывшемся конструкторе будем создавать вложенные планы обслуживания. щелкните два раза по строке ВложенныйПлан _1 Задайте Имя, Описание и обязательно настройте Расписание выполнения вложенного плана обслуживания: еженедельно в воскресение 17:00:00

Используя Панель элементов создадим первый вложенный план. Достаточно нужный элемент в панели ухватить, перенести на рабочую область и там бросить. Для открытия мастера настройки элемента достаточно два раза щелкнуть по элементу. Ниже на рисунке представлен результат настройки, который должен у нас получится, но все по порядку. Размещаем задачу «Проверка целостности базы данных», двойным щелчком мыши открываем диалог настройки задачи, в первую очередь в свойстве Базы данных отмечаем нужную базу, а остальное настраиваем как показано на рисунке. При желании можно посмотреть T-SQL код полученной задачи. Размещаем следующую задачу «Перестроение индекса» она у нас будет выполнятся только после успешно выполненной предыдущей задачи. Настраиваем как показано на рисунке, не забываем указать конкретную базу данных.  Для связи двух задач щелкните по первой задаче «Проверка целостности базы данных» у этой задачи появится стрелка, щелкните по ней и не отпуская соедините со второй задачей «Перестроение индекса». Для изменения значения условия выполнение следующей задачи, щелкните два раза по линии и в открывшемся диалоговом окне выполните необходимые настройки. Размещаем задачу «Обновление статистики» которая будет выполнятся после завершения предыдущей. Настраиваем эту задачу как на рисунке, не забываем выбрать базу данных. Размещаем задачу «Выполнение инструкции T-SQL» с кодом:  DECLARE @intDBID INTEGER SET @intDBID = (SELECT dbid FROM master.dbo.sysdatabases WHERE name = ‘Moodle‘)  DBCC FLUSHPROCINDB (@intDBID) Инструкция DBCC FREEPROCCACHE используется для аккуратной очистки кэша планов. Освобождение кэша планов приводит, например, к тому, что хранимая процедура повторно компилируется, а не используется из кэша. При настройки для своей базы не забываем изменить имя БД Moodle. Размещаем следующую задачу «Резервное копирование базы данных» она у нас будет выполнятся полную резервную копию базы данных. Размещать резервные копии желательно на на СХД, если нет, то на физически другом диске, но не в коем случаи на том же диске где  сама база данных, иначе теряется весь смысл резервных копий. Настраиваем как показано на рисунке, не забываем указать конкретную базу данных.  Размещаем следующую задачу «Очистка журнала» она у нас будет выполнятся очистку журналов. Настраиваем как показано на рисунке. Размещаем следующую задачу «Очистка после обслуживания» она у нас будет выполнятся удаление старых файлов резервных копий, так как свойстве Расширение файла указана маска *.*, то удаляются будут все файлы, и полной резервной копии, и разностной, и журнала транзакций. Настраиваем как показано на рисунке. Обратите внимание, две последние задачи выполняются после выполнения задачи «Резервное копирование базы данных» и самое главное, задачу «Очистка после обслуживания» нужно выполнять только после успешно выполненной задачи «Резервное копирование базы данных». Что бы не получилось, что у вас уже который раз не создаются резервные копии, а вы задачей «Очистка после обслуживания» удаляете последние актуальные копии.4. Создание плана обслуживания (Разностная копия) Добавим вложенный план обслуживания, на рисунке ниже красной рамкой выделена данная кнопка и показан результат схемы обслуживания, который должен получится, но все по порядку. Заполним поля свойств и настроим расписание как показано на рисунке. Размещаем две задачи «Проверка целостности базы данных» и «Резервное копирование базы данных», обратите внимание последняя задача выполняется только после успешного завершения предыдущей. Иначе какой смысл делать резервную копию если она не корректна. На рисунке представлена настройка задачи «Проверка целостности базы данных». На рисунках представлены настройки задачи «Резервное копирование базы данных». Обратите внимание на Тип резервной копии, должен стаять Разностное. И не забудьте указать конкретную базу данных.5. Создание плана обслуживания (Резервная копия журналов транзакций) Добавим два вложенных плана обслуживания, один настроим на 12:00 второй на 17:00. На рисунке представлен результат плана обслуживания на 12:00, на 17:00 отличатся ничем не будет, только временем выполнения. Разместим одну задачу «Резервное копирование базы данных». Обратите внимание на Тип резервной копии, должен стаять Журнал тарнзакций. И не забудьте указать конкретную базу данных.6. Мониторинг планов обслуживания После создания всех Планов обслуживания они появятся в ветке Агент SQL Server. Откройте Мониторинг активности заданий, в этом мониторинге можно увидеть какие задачи, когда выполнялись, когда следующее выполнение и успешно ли они выполнялись.  Для запуска определенного плана, достаточно в контекстном меню выбрать пункт Запустить задание на шаге…P/S Сегодня рассмотрели минимальные азы создания планов обслуживания в MS SQL по созданию трех типов резервных копий баз данных: Full (Полная копия), Differential (Дифференциальная копия, Разностная копия) и Log (Резервная копия журналов транзакций).После очередной просьбы рассказать как составить план обслуживания sql-баз используемый <nobr>1С: Предприятием</nobr>, решил поделиться опытом со всеми сразу. Зачем это надо — если в sql не обслуживать базы данных, то его смысл теряется вовсе. Основной инструмент — индексы и их надо держать в актуальном состоянии. Каких-то догматов я не встретил не в практике, не в нете, не на курсах в самой 1С, а потому делюсь своим опытом. Зачастую база работает в «нормальных» условиях. Что под этим подразумевается:

• Сервер SQL хорошо «питается», т.е. объем ОЗУ предоставляемой для работы SQL сервера выбирать из расчёта 70% от размера всех mdf файлов баз данных.
• Процессор не загружен более чем на 50% в течении 90% времени.
• Имеется достаточное место на дисках (в частности для сортировки используется база temp.db, 1С ее использует вообще для всей своей жизнедеятельности, потому стоит заранее озаботиться местом на диске с этой базой).
• Режим восстановления базы данных — «Простой». (Эмпирически выяснено, что большой ldf файл тормозит 1с-ку, а возможность восстановления по лог-файлу весьма сомнительна).

Так же стоит учитывать несколько нюансов:

• При использовании Standard редакции SQL, при полном перестроении индекса, все пользователи будут отключены от базы, потому стоит это учитывать при решении проведения Weekly плана обслуживания (план будет описан ниже).
• Стоит учитывать, что сервер 1С тоже потребляет память, особенно если используются тонкие клиенты или веб-службы.
• Самому SQL лучше ограничить в параметрах сервера максимальный объем ОЗУ, дабы по достижению критической массы, он заранее начинал очищать ненужные данные из ОЗУ. Да и чтоб разрастаясь не вгонять весь сервер в ступор.

Рационально при нормальных условиях использовать 2 плана обслуживания Weekly (раз в неделю) и Daily (в остальные 6 дней недели).

Weekly

Общий вид По пунктам плана обслуживания:

1. Перестроение индекса. Смысл задачи в удалении всех имеющихся индексов и установки новых. (грубо говоря инвентаризация и расстановка всего по порядку). В качестве параметров:
   • Выбор целевой базы (это будет почти во всех задачах, потому далее на этот параметр я не буду обращать внимание в пределах этой статьи).
   • Объект, в котором мы выбираем «Таблицы и представления».
   • Параметры свободного места – при малом объеме жесткого диска можно выбирать пункт «по умолчанию», однако я рекомендую использовать «Изменить долю свободного места на странице», рекомендуемое значение 20%. Это позволит оставить запас свободных страниц, и позволит дольше держать индексы в актуальном состоянии. ВНИМАНИЕ: Увеличивает размер базы данных.
   • Отсортировать результаты в tempdb. Думаю пояснять не требуется, однако предупредить хочу, в это время tempdb, будет очень сильно разрастаться, хоть и сортировка в ней и призвана ускорить процесс, будьте осторожны, имейте запас пространства.
   • Сохранять индекс в режиме «в сети» — фишка доступная для enterprise версии SQL. Позволяет делать переиндексацию без отключения клиентов.

   !!! ВНИМАНИЕ!!! В Standard версии при переиндексации происходит отключение клиентов от базы данных на время работы данного шага.Пример настроек

2. Обновление статистики. Задача сбора информации о состоянии индексов в базе. (В общем-то мало актуальная после переиндексации, но все же я делаю). Параметры:
   • Объект. Все те же таблицы и представления, что и для перестроения индекса.
   • Обновить. Тут обновляем всю статистику.
   • Тип просмотра – Полный просмотр.

   Таким образом, мы обновляем статистику по всей базе данных.Пример настроек

3. Выполнение инструкции T-SQL. Это выполнение произвольной команды на языке SQL, в частности нас интересует

   dbcc proccache 

   Как следует из название – чистка кэша.Пример

4. Проверка целостности базы данных. Тут кажется излишни пояснения – убеждаемся, что ничего не сломалось. В параметрах «включаем индексы» в проверку, не зря же перестраивали.Пример настроек
5. Резервное копирование базы данных. Тут поговорить надо побольше, ввиду многих особенностей. Лучше изучить данный пункт отдельно самостоятельно в других руководствах, формат данной статьи не предусматривает углубленного изучения резервного копирования. Но о паре нюансов хочу предупредить:
   • SQL не умеет чистить контейнер свой, потому если добавлять резервные копии в файл (оно же обзывается «Устройство резервного копирования»), в итоге забьете все свободное место.
   • SQL помнит о своих резервных копиях, потому сделав ручками бэкап, единоразовый (например, отнести базу в другое место, или чтоб развернуть для теста в еще одну базу из бэкапа), следующий «разностный» будет отсчитываться от него. Дабы предотвратить это, требуется ставить галочку «Только резервное копирование». В задаче резервного копирования такого пункта нет. Вообще в недельном плане рекомендую все же использовать полный тип резервной копии.
   • И хорошо бы проверять копию, пусть спиться спокойнее.
   • Сжатие, в общем-то, использовать можно, но будьте аккуратны, разностные тогда надо тоже сжимать.

   Пример настроек

6. Очистка журнала.
   • Журнал резервного копирования и восстановления.
   • Журнал заданий агента SQL Server.
   • Журнал плана обслуживания.

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

7. Уведомление оператора. Пунктик опять-таки для самостоятельного изучения. Но как понятно из названия, для сообщения о проблемах в ходе выполнения плана.

Daily

Общий видГоворить отдельно не имеет смысла. Почти все аналогично Weekly. Различие в первой задаче – «Реорганизации индексов». Задачи отличаются тем, что реорганизация пытается выправить имеющиеся индексы, а не делает все с чистого листа. Чем больше фрагментация – тем чаще стоить запускать. Но в нормальных условиях раз в день достаточно, чтобы поддерживать индекс в актуальном состоянии до следующего перестроения.ПараметрыТак же можно использовать разностное резервное копирование. На этом все. Повторяюсь, догматов в этом моменте я не видел, этот вариант был разработан и протестирован мной. Актуально для баз размером от 6 до 100 ГБ. Желаю Вам быстрой и надежной работы. P.S. В силу того что не являюсь полноценным DBA, возможно мои комментарии весьма поверхностны, с удовольствием прочту замечания в комментариях и личке.

Итак в продолжении темы обслуживания баз 1С присмотримся к системе управления реляционными базами данных Microsoft SQL Server. Этот продукт предоставляет нам большие возможности обработки, хранения, резервирования и восстановления баз. Я начну небольшой цикл статей, посвященных этой теме. Все, что будет написано ниже, является личным мнением по данному вопросу и подлежит критике. В данной статье рассмотрен процесс создания планов обслуживания баз. Оповещение оператора, а так же пример восстановления базы рассмотрим в следующих статьях.В тестовой лаборатории у нас следующее:

• Сервер Windows Server 2008 Enterprise: SRV-1C-TEST.
• Microsoft SQL Server 2008: SRV-1C-TEST.
• Тестовая база BuhFirma.

Как обычно, поставим перед собой задачу:Проводить обслуживание базы в период 00:30 — 01:00, при этом обслуживание не должно быть заметным (либо слабозаметным) для пользователей базы.Начнём с важных моментов. MS SQL база данных может иметь один из трех типов модели восстановления:

• Простая.
• Полная.
• С неполным протоколированием.

Так же при резервном копировании нам предоставляется на выбор три варианта копирования:

• Полное.
• Разностное.
• Копирование журнала транзакций (логов).

При полном варианте копирования происходит сохранение базы mdf и журнала транзакций. Разностное копирование (по-другому дифференциальное) производит копирование данных, изменившихся с момента создания последней полной резервной копии. Копирование журнала транзакций соответственно производит сохранение только самого журнала транзакций.При выборе простой модели восстановить базу данных можно с момента создания последней разностной или полной резервной копии. При выборе полной модели восстановления мы можем восстанавливать базу до минуты, создав полную резервную копию, например, ночью, и в течение дня создавать копии журнала транзакции. Ниже мы увидим, где всплывает этот момент. Хотелось так же привести некоторые выдержки из MSDN: «Модель восстановления с неполным протоколированием предназначена исключительно как дополнение к модели полного восстановления. В общем случае модель восстановления с неполным протоколированием похожа на модель полного восстановления, за исключением того, что протоколирование большинства массовых операций в ней производится в минимальной степени».Модель восстановления своей базы вы можете посмотреть, зайдя в свойства базы данных, например BuhFirma и перейдя на строку — Параметры.В MSSQL 2008 по умолчанию в созданных базах данных модель восстановления Полная.Как выбрать модель восстановления? Надо лишь ответить на вопрос: смертельна ли потеря информации за время, прошедшее после полного резервного копирования? Если ответ да, тогда выбираем полную модель восстановления, если нет, простую. Модель с неполным протоколированием стоит применять только на время массовых операций в БД.Таким образом, если вы выбрали простую модель, то восстановить данные вы сможете только на момент ночного полного или разностного копирования, а всю информацию после этого пользователи будут восстанавливать вручную. Выбирая Полную модель, вы обязательно должны делать резервное копирование журнала транзакций, иначе логи будут сильно расти. При любой модели восстановления вы всегда должны иметь полную резервную копию.В начале создадим ночной план обслуживания базы, который будет включать в себя последовательность следующих действий:

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

Для этого подключимся к MSSQL серверу с помощью среды Microsoft SQLServer Management Studio. Запустить среду можно перейдя в Пуск — Все программы — Microsoft SQL Server 2008.Подключимся с серверу SQL и перейдем в Управление — Планы Обслуживания. Кликнем правой кнопкой по Планы обслуживания и выберем Создать план обслуживания. Дадим ему имя: SRV1CTEST.Перед нами окно SRV1CTEST, в котором мы и будем создавать последовательность действий, обозначенных раннее. Сразу видим появившейся Вложенный_План1. Справа от названия вложенного плана вы увидите иконку в виде таблички. Нажимаем на нее и попадаем в свойства расписания задания. Здесь можно менять название вложенного плана, выставить частоту повторения в Ежедневно и установить время. И так теперь осталось наполнить наш план заданиями. Для этого с Панели инструментов, которая находится справой стороны, перетаскиваем задания.Начнем с Проверки целостности базы данных.После того, как вы перетащили задание, щелкните по нему два раза. Откроется окно, в котором в строке Базы данных мы выбираем созданную нашу базу BuhFirma. Далее таким же образом добавляем задания Перестроение индекса и Обновление статистики, не забыв выбрать в них нужную базу данных.Процедура Перестроение индекса пересоздает индекс с новым коэффициентом заполнения. За счет этого мы увеличиваем производительность работы в БД.Задача Обновление статистики обновляет сведения о данных таблиц для MS SQL. Что тоже повышает производительность. Но после этой операции надо обязательно проводить очистку кэша.Пока остановимся и поговорим о настройке связей между заданиями. Связи отражают последовательность выполнения. Что бы провести связь между заданиями надо нажать один раз на задание и увидите появившуюся стрелку. Её надо перетащить на следующее задание. У связи может быть 3 цвета: синий, зеленый и красный, каждый из которых означает три типа срабатывания перехода: при простом завершении предыдущего задания — Завершение, в случае успешного завершения — Успех, а в случае возникновения ошибки при выполнение предыдущего задания — Ошибка. Все эти параметры вы можете увидеть, нажав правой кнопкой мыши на проведенную между заданиями стрелку. Таким образом, если нам надо, чтобы Перестроение индекса срабатывало только после успешного завершения задания Проверка целостности базы данных, мы должны связать их стрелкой. Нажав правой кнопкой мыши на стрелку, сменим ее режим на Успешно, как видим, ее цвет изменился на зеленый.На данный момент мы имеем 3 созданных задания в нашем вложенном плане. Как вы могли заметить, задания Очистка процедурного кэша СУБД в панели элементов нету. Мы воспользуемся задачей Выполнение инструкции T-SQL. Перетащим ее в план, и щелкнем на ней два раза. Мы видим окно, в которое впишем следующее:

DBCC FREEPROCCACHE

Нажмем ОК. Далее стоит добавить задание задачу Резервное копирование базы данных. Так же щелкнув на добавленном задании, увидим опции настройки задания.Здесь, исходя из поставленной задачи, выбираем полное резервное копирование, место, куда будем помещать архивы, а так же не забудем установить параметр Сжимать резервные копии.Задача Очистка после обслуживания позволяет удалять устаревшие архивы. В нем мы можем установить место расположения архивов, а так же время, по истечению которого они будут удаляться.Задача Очистка журнала производит удаление данных журнала, связанных с процессами резервного копирования, восстановления, планами обслуживания баз, а также с деятельностью агента SQLServer.Таким образов в конце мы получим список последовательно выполняемых задач.Сохранив план обслуживания, надо удостовериться в том, что на нашем сервер запущен Агент SQL Server. Для этого перейдем в Пуск — Все программы — Microsoft SQL Server 2008 — Средства настройки — Диспетчер конфигурации SQL Server. Перейдя на строчку Службы SQLServer, проверим, что служба Агент SQLServer находится в состоянии Работает и режим запуска выставлен в Авто.В конце хотелось бы сказать о том, что использование задачи Перестроение индекса можно заменить или совместить с задачей Реорганизация индекса. Реорганизация индекса представляет собой инструмент для дефрагментации индексов. Для того что бы просмотреть какие операции требуются индексу, необходимо просмотреть физическую статистику индекса. Для этого правой кнопкой мыши нажмите на базу данных, перейдите в Отчеты — Стандартные отчеты — Физическая статистика индекса.Следить за состоянием выполняемых операций вы можете из Управление — Планы обслуживания. Для этого в свойствах плана SRV1CTEST выберите Просмотр журнала. Так же можно просмотреть журнал, который ведет Агент MS SQL по этому заданию. Для этого перейдите на строку Агент MS SQL и в свойствах задания SRV1CTEST выберите Просмотр журнала.Дополнительные материалы:

1. Обслуживание баз 1С в MS SQL Server. Часть 1. Настройка планов обслуживания БД.
2. Обслуживание баз 1С в MS SQL Server. Часть 2. Оповещение по e-mail.
3. Обслуживание баз 1С в MS SQL Server. Часть 3. Восстановление из резервных копий.
4. Резервное копирование баз данных Microsoft SQL Server.

Используемые источники:

• https://www.uroki-1c.ru/2019/01/1-ms-sql.html
• https://habr.com/post/158433/
• https://interface31.ru/tech_it/2012/02/obsluzhivanie-baz-1s-v-ms-sql-server-chast-1.html