MS SQL Server. Обслуживание системных баз данных

Аннотация: Управлять службами SQL Server – дело очень тонкое и требующее специфических знаний о принципах работы компонентах службы: – SQL Server Agent, Microsoft Distributed Transaction Coordinator и Microsoft Search. Рассматриваются доступ к часто изменяющимся параметрам системы. Инструментальные средства SQL Server Service Manager, SQL Server Enterprise Manager и Windows 2000 Service Control Manager позволяют расширить возможности управления службами. Приводятся примечания, защищающие администраторов от некорректных действий.

Установив Microsoft SQL Server 2000, вы можете начать им пользоваться. Но до того, как вы сможете входить в систему и начнете строить свою базу данных, нужно научиться запускать службу SQL Server и ее компоненты – SQL Server Agent, Microsoft Distributed Transaction Coordinator и Microsoft Search . Эти компоненты, описанные в данной лекции, исполняются как отдельные службы, дополняющие службу SQL Server . В данной лекции мы также расскажем, как запускать, останавливать и управлять этими службами при помощи трех инструментальных средств – и .

Примечание . В данной лекции сделан упор на описание работы SQL Server 2000 в операционной системе Microsoft Windows 2000, хотя SQL Server 2000 может работать и в Microsoft Windows NT 4. В операционной системе Microsoft Windows 98 SQL Server запускается как исполняемый файл, потому что Windows 98 не поддерживает службы.

Важно, чтобы вы научились управлять службой SQL Server 2000 при помощи Enterprise Manager . Обратите внимание, что данная лекция дает лишь краткое знакомство с Enterprise Manager . Многие задачи, решаемые с помощью Enterprise Manager , будут рассмотрены в следующих лекциях. Это, например, такие задачи, как создание баз данных и объектов, конфигурирование настроек сервера, конфигурирование репликации и управление репликацией, управление резервным копированием. А в другой лекции основное внимание будет уделено применению Enterprise Manager для управления службой SQL Server и другими службами.

Службы SQL Server

Служба – это программа или процесс, выполняющие специфические функции поддержки других программ. Когда вы запускаете SQL Server, в операционной системе Windows NT или Windows 2000 запускается служба SQL Server. Эта служба управляет файлами баз данных, исполняет операторы Transact-SQL (T-SQL) , распределяет ресурсы среди пользовательских соединений, исполняющихся одновременно, проверяет непротиворечивость данных и выполняет еще много других задач. Если вы инсталлируете один или несколько экземпляров SQL Server, то службы для отдельных экземпляров SQL Server будут иметь имена MSSQL$ИмяЭкземпляра , где ИмяЭкземпляра – имя экземпляра, назначенное вами при инсталляции. Соответственно, службы SQL Server Agent для экземпляров SQL Server будут иметь имена SQLAGENT$ИмяЭкземпляра . Однако для всех экземпляров SQL Server будет только по одной инсталляции Microsoft Distributed Transaction Coordinator и Microsoft Search.

Программные компоненты этих трех служб вы получаете в рамках лицензии на копию SQL Server. Инсталляция SQL Server Agent производится по умолчанию при инсталляции SQL Server. Если у вас не инсталлированы Microsoft Distributed Transaction Coordinator и Microsoft Search, то вы можете снова запустить инсталляционную программу SQL Server, чтобы установить эти компоненты, которые имеют там названия DTC Client Support и Full-Text Search , соответственно. А сейчас мы расскажем, что делает каждая из этих трех служб.

SQL Server Agent осуществляет планирование и исполнение заданий, оповещений, извещений и планов обслуживания базы данных. Без этой службы работа администратора баз данных станет гораздо более трудной, а может, вообще невозможной. Благодаря SQL Server Agent можно автоматизировать рутинные процедуры по обслуживанию базы данных. Например, вы можете создать задание, которое будет автоматически выполнять резервное копирование базы данных ежесуточно в 1 час пополуночи, и другое задание, которое будет автоматически выполнять резервное копирование журнала транзакций каждые полчаса. Чтобы следить за производительностью вашей системы, можно создать оповещение о состоянии производительности, которое будет информировать вас, если загруженность центрального процессора сервера превысит 90%. Для решения подобных задач нужно запускать службу SQL Server Agent, которую можно сконфигурировать на автоматический запуск при запуске SQL Server, а можно запускать и вручную. Вам следует сконфигурировать ее на автоматич еский запуск, что будет гарантировать исполнение ваших запланированных заданий, оповещений и извещений. В "Администрирование Microsoft SQL Server" будет рассказано, как создать план обслуживания базы данных, а в "Автоматизация административных задач" – как при помощи SQL Server Agent назначать задания, оповещения и извещения.

Microsoft Distributed Transaction Coordinator – это администратор транзакций ( transaction manager ), при помощи которого в транзакции ваших приложений можно включать данные из различных источников, в том числе данные из баз данных с удаленных компьютеров. Это значит, что при помощи одной транзакции можно обновлять данные на многих серверах, доступных через сеть. Администратор транзакций гарантирует, что все обновления станут постоянными для всех источников данных (если транзакция зафиксирована) или, в случае ошибки, что для всех источников данных будет произведен откат всех изменений. (Об администраторе транзакций Microsoft Distributed Transaction Coordinator см. "Службы компонентов и Microsoft Distributed Transaction Coordinator" .)

Запускайте службу Microsoft Search , когда вам нужна поддержка полнотекстного индексирования и поиска. Благодаря полнотекстному индексированию возможно выполнение более сложного поиска среди данных, содержащих текстовые строки. Например, вы можете искать слова, близкие к заданному слову, или можете искать определенную фразу.

Как мы уже говорили, имеется несколько инструментальных средств для остановки и запуска служб SQL Server: SQL Server Service Manager, SQL Server Enterprise Manager и Windows 2000 Service Control Manager . Давайте сначала рассмотрим , при помощи которого можно управлять службами SQL Server, SQL Server Agent, Microsoft Distributed Transaction Coordinator и Microsoft Search .

Применение SQL Server Service Manager

Для запуска или остановки служб SQL Server при помощи , выполните следующие действия (а ещё, как вы увидите, службу SQL Server можно и приостанавливать).

Вероятно, вы знаете, что обслуживание баз данных это целый комплекс процедур: создание бэкапов, проверка целостности, обслуживание индексов, статистики и т.д. На просторах сети (да и на Хабре в том числе) на эту тему написано множество статей и рекомендаций. Однако занимаясь внедрением «1С: Предприятие», нам частенько приходится сталкиваться с тем, что обслуживание баз данных настраивается либо неправильно, либо по очень упрощённой схеме. Например, чтобы не заморачиваться с управлением журналами транзакций, для «боевых» баз устанавливается Простая модель восстановления (Simple Recovery model). И это несмотря на то, что потеря информации за пару часов уже критична для компании. Иногда задача по сжатию файлов БД включается в регулярное обслуживание («шобы не росло»), или после обновления индексов идёт уничтожение статистики и прочие подобные ляпы. Так происходит потому, что чаще всего в компаниях нет опытного администратора БД и обслуживанием приходится заниматься кому-то из сотрудников ИТ-службы – «невольному» администратору баз данных (DBA). При этом такой DBA не всегда осознаёт все риски и возложенную на него ответственность.

Для обслуживания баз Microsoft предлагает планы обслуживания (Maintenance Plan) в SQL Server Management Studio (SSMS). Однако как показывает практика, создать и настроить качественный и надёжный план обслуживания может только опытный DBA. Отмечу, что надёжное обслуживание максимально автоматизировано и не требует регулярного ручного мониторинга администратором, а также гарантирует, что данные удастся восстановить в случае сбоя.

Сторонние программы, которые имеются на рынке и способны облегчить жизнь, в основном автоматизируют создание бэкапов. Выбор таких программ очень широк. Они позволяют делать бэкапы сжатые и шифрованные, на FTP/GoogleDrive/Amazon и так далее. Бэкапы тут можно сравнить с креветками, о которых говорил Бабба в картине «Форест Гамп»: «… их можно жарить, варить, печь, тушить, можно приготовить шашлык из креветок, креветки по-креольски, креветки гамбо, поджаренные с рисом … ».

Однако как было сказано, настройка бэкапов это далеко не всё, поэтому такие программы закрывают лишь часть вопросов.

В итоге «невольному» DBA приходится читать статьи, разбираться с SSMS, разрабатывать стратегию резервного копирования, искать скрипты, настраивать уведомления. Времени уходит много, но всегда есть, что обматерить… А хочется жить безмятежно! Так, чтобы один раз сделал и забыл.

В этой статье я хотел бы сделать обзор нашей программы Quick Maintenance & Backup (QMB), которая поможет вам просто и быстро настроить обслуживание баз данных на Microsoft SQL Server. Бесспорно, что для больших и высоконагруженных баз данных не обойтись без опытного DBA и индивидуального тюнинга производительности, но если вам приходится иметь дело с множеством небольших баз (как правило до 50-80 Гб), то данная утилита будет полезна как новичкам, так и продвинутым пользователям.

Основные возможности QMB

• Простая и быстрая настройка
• Обслуживание нескольких SQL Server в одной программе. Поддерживаются SQL Server версий 2000 и старше, в том числе Express редакции
• 30 встроенных задач с открытыми скриптами, в том числе популярные скрипты Ola Hallengren:

  – бэкапы - полный, разностный, журнал транзакций
  – проверка целостности
  – обслуживание индексов и статистики
  – обслуживание системных баз
  – копирование файлов бэкапов с возможностью указания срока хранения
  – автоматизированная проверка бэкапов через восстановление
  – поддержание копий баз данных в актуальном состоянии

• 7 предустановленных политик обслуживания для Полной и Простой модели восстановления
• Мониторинг свободного места на дисках SQL Server
• Пользовательские задачи на скриптовых языках Transact SQL, CMD, VBScript, JavaScript, PowerShell и других
• Статистика изменения размеров баз данных. Расчёт среднего прироста данных
• Уведомления по электронной почте
• Подробный журнал обслуживания

Концепция: доступно новичкам, удобно профессионалам

Архитектура

Политика обслуживания, сценарии и задачи

Задача в QMB может иметь один из пяти типов:

• Скрипт T-SQL
• Создание архивной копии (скрипт T-SQL)
• Восстановление архивной копии (динамический скрипт T-SQL)
• Произвольный скрипт (не T-SQL)
• Копирование архивных копий (используется в одноименной системной задаче)

Обслуживание больших и маленьких баз данных. Шаблоны политик

• Моделью восстановления баз данных. 5 политик с полной моделью восстановления (Full recovery model) и 2 с простой (Simple recovery model).
• Порядком обслуживания индексов. Для небольших баз дефрагментация индексов выполняется каждую ночь, а обновление изменившейся статистики в течении дня; для больших баз дефрагментация индексов выполняется раз в неделю.
• Набором используемых задач в сценариях. Для обслуживания используются задачи/скрипты Ола Халенгрэн или QMB.

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

Подробнее о различиях в шаблонах можно посмотреть в справке .

Задачи

Исполнение скриптов

Тексты скриптов (T-SQL, CMD, VBS, PowerShell и других) могут содержать маркеры, которые будут заменены на соответствующие значения, перед его исполнением. Например, маркер ?DataBaseName? будет заменён на имя базы данных, а маркер ?BackupDirectory? – на путь к каталогу архивных копий, указанному в политике. Полный список маркеров можно посмотреть в справке .

Оптимизация окна обслуживания
Бывает, что в ограниченное временное окно необходимо уместить не только обслуживание баз средствами SQL Server, но и исполнение других регламентных операций вашего приложения. Например, тестирование и исправление баз 1С, выгрузку средствами платформы «1С: Предприятие», проведение обмена и т.п. Обычно для этого используется планировщик заданий Windows или планировщик «1С: Предприятие». Однако при этом приходится разносить процедуры во времени с хорошим запасом – так, чтобы они гарантированно не пересекались. В итоге задачи могут не уложиться в имеющееся временное окно.

С QMB можно максимально эффективно использовать окно обслуживания, объединив в сценарии исполнение T-SQL скриптов и пакетных файлов на языках VBS, JavaScript, CMD, PowerShell и других. Ниже показан простой пример задачи альтернативного копирования бэкапов с помощью утилиты Robocopy:

Нужно отметить, что пакетный файл может выполняться как на машине, где установлена программа, так и на стороне SQL Server. Это позволяет оперировать файлами бэкапов на стороне SQL Server. Например, можно написать скрипт, который будет архивировать последний бэкап и выкладывать его в любое облачное хранилище или реализовать собственный алгоритм копирования. В следующих статьях я планирую подробнее рассказать об этой возможности и привести скрипты для работы с базами «1С: Предприятие 8».

Вывод сообщений и журнал обслуживания
Все сообщения, выводимые при исполнении скрипта, перенаправляются в журнал обслуживания программы. Это касается сообщений, выводимых командами print, raiserror для T-SQL скриптов, а также сообщений, выводимых в консоль командами echo, для прочих CMD-скриптов и пакетных файлов. И это здорово! Потому что читабельные и понятные логи – это колоссальная экономия времени, а в качестве бонуса – текст ошибок отправляется в email-уведомлении.

Автоматизированная проверка бэкапов через восстановление

В QMB имеется специальная задача, которая последовательно восстановит всю цепочку бэкапов для каждой базы данных политики: Full backup –> Differential backup –> Transaction log backup. Восстановление выполняется во временную тестовую базу, которая удаляется после проверки её целостности.

Например, у нас в компании на виртуальном SQL Server имеется около 60 небольших баз данных, общим объёмом под 100 Гб. QMB каждую ночь выполняет проверку возможности восстановления всех баз. Проверка занимает около полутора часов и это даёт нам гарантию того, что все резервные копии проверены. Если цепочка бэкапов будет прервана, то придёт уведомление с примерно такой ошибкой:

1. Задача "Восстановление из арх. копий во временную БД с последующей проверкой целостности" (база данных: Buh_Oazis)

Советы тем, кто захочет настроить подобную проверку:

1. Операция восстановления ресурсоемкая, поэтому её следует включать в сценарии исполняемые только в нерабочее время.
2. Для создания временной тестовой базы и восстановления бэкапов в неё требуется запас дискового пространства, равный как минимум самой большой базе данных в политике + 10% от её объёма.
3. Восстановление больших баз может занимать значительное время. Не включайте задачу по проверке, если не уверены, что операция успеет завершиться в отведённое окно обслуживания.
4. Правильно размещайте задачу в сценарии. Учитывайте, что восстановление выполняется на актуальный момент времени, т.е. на момент исполнения задачи. Например, если задачу проверки бэкапов разместить сразу после создания полной резервной копии, то будет протестирован только последний бэкап, т.к. его будет достаточно для восстановления базы на актуальный момент времени.
5. Если на проверку бэкапов всех баз политики не хватает окна обслуживания, можно проверять бэкапы только определённых баз данных. Либо распределить задачи по дням недели. Например, сегодня ночью проверить бэкапы баз А и B, а завтра – баз C и D.
6. Не рекомендуется делать бэкапы в сетевую папку, т.к. при восстановлении приходится «тащить» файлы бэкапов по сети, что значительно увеличивает время восстановления. Правильнее будет настроить создание бэкапов на локальный диск с ежедневным копированием в сетевую папку.

Автоматизированная поддержка копий баз в актуальном состоянии

Во время процедуры восстановления база AccountingCopy будет переведена в однопользовательский режим с отключением всех пользовательских соединений.

Копирование файлов бэкапов

Ниже я хотел бы акцентировать внимание на нескольких особенностях копирования бэкапов c помощью QMB:

1. Частота копирования определяется расписанием сценария, содержащего задачу «Копирование архивных копий». Задачу можно разместить в одном или нескольких сценариях.
2. Копируются только новые и изменённые файлы бэкапов – это снижает нагрузку на сеть и позволяет выполнять частое копирование. Например, можно копировать каждый раз после создания нового бэкапа журнала транзакций.
3. Для сетевой папки можно задать срок хранения файлов. Таким образом, на локальном диске SQL Server можно хранить бэкапы, например, за 1 неделю, а в сетевой папке за 1 месяц.
4. Возможно настроить копирование бэкапов только для избранных баз политики.

Восстановление баз данных

Команда «Восстановление из архивной копии» позволяет:

• Восстанавливать базу данных из бэкапов на указанный момент времени с автоматическим подбором цепочки бэкапов (если бэкапы создавались на этом же SQL Server).
• Восстанавливать базу данных из файла с полной резервной копией.
• Восстанавливать бэкапы одной базы данных в другую базу данных, в том числе новую.
• Выполнять проверку целостности базы данных после её восстановления.

Оповещения на Email

ЗАДАНИЕ ВЫПОЛНЯЕТСЯ:
«Рабочие базы данных.ВложенныйПлан_1» началось в 19.05.2015 17:00:00
ДЛИТЕЛЬНОСТЬ:
0 час., 0 мин., 5 сек.
СОСТОЯНИЕ:
Ошибка
СООБЩЕНИЯ:
Не удалось завершить задание. Запуск задания был произведен Расписание 9 (MaintenancePlan). Последним выполнявшимся шагом был шаг 1 (Бэкап журнала транзакций).

В отличие от Database Mail, QMB отправляет в уведомлении первые 15 строк текста ошибки. Обычно этого бывает достаточно, чтобы понять причину и предпринять нужные действия. Посмотреть полный лог можно в журнале обслуживания программы. Пример сообщения с ошибкой:

Сценарий "Ресурсоемкие задачи для средних OLTP баз (каждую ночь)" был выполнен с ошибками на сервере "Srv05".

Старт сценария: 06.06.2015 1:00
Окончание работы: 06.06.2015 1:29
Длительность: 00:29:28

Всего задач: 7
Выполнено задач: 7
С ошибками: 1

1. Задача "Восстановление из арх. копий во временную БД с последующей проверкой целостности" (база данных: IPGor)
Message: 4305, Level: 16, State: 1, Line: 21
Журнал в этом резервном наборе данных начинается с номера LSN 5235000000291100001, который еще не может применяться к базе данных. Может быть восстановлена более ранняя резервная копия журналов, включающая номер LSN 5228000000281600001.

Message: 3013, Level: 16, State: 1, Line: 21
RESTORE LOG прервано с ошибкой.

Message: 50000, Level: 16, State: 1, Line: 119
В процессе восстановления возникла ошибка

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

Лицензионная политика и стоимость

Лицензии вечные и не ограничены по времени. При необходимости лицензию можно легко перенести с одного сервера на другой.

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

Заключение

Надеюсь, что этот обзор оказался вам полезен. Помните, что низкая производительность и внезапные остановки SQL Server наносят вред репутации всей службе ИТ, в то время как потеря данных в большинстве случаев приводит к ещё более серьёзным последствиям. Если у вас создан план обслуживания, но нет уверенности в его надёжности, то вы сидите на мине замедленного действия – я настоятельно советую предотвратить ЧП заранее, чем расхлёбывать его последствия.

Спасибо за внимание, готов ответить на ваши вопросы в комментариях.

Теги: Добавить метки

Данную статью меня сподвигла написать проблема, которую я недавно решал у одного из заказчиков.

Более точно сказать было даже несколько проблем, которые, как обычно, наслоились друг на друга (или их “наслоили” администраторы пытавшиеся решить проблему).
Статья не ставит перед собой целью всестороннее и подробнейшее описание всей системы Service Broker, со всеми ее возможностями.
Здесь лишь описана среда, с которой я сталкивался наиболее часто в ходе решения проблем.
Наверное все, кто читают данный блог знают, что такое Service Broker в SQL Server, но, для того чтобы начать с одной исходной точки, я скажу пару слов об этой штуковине.

Впервые эта полезная вещь появилась в SQL Server 2005, и с тех пор не сильно изменилась. Точнее сказать она приросла некоторыми новыми возможностями, но принципы, заложенные в те годы, так и остались неизменными.
Итак.
Как следует из https://technet.microsoft.com/ru-ru/library/ms166049(v=sql.105). aspx, компонент Service Broker помогает создавать асинхронные слабосвязанные приложения, в которых независимые компоненты совместно выполняют ту или иную задачу. Эти компоненты обмениваются сообщениями, которые содержат данные, необходимые для выполнения задачи. В этом разделе описываются следующие аспекты компонента Service Broker:
диалоги;

• упорядочение и координация сообщений;
• асинхронное программирование на основе транзакций;
• поддержка слабосвязанных приложений;
• составные части компонента Service Broker.

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

• Сообщения. Блоки информации, которыми обмениваются участники.
• Контракт. Сообщения собираются в контракт для того, что бы обмен сообщениями был более формализован.
• Очереди. Сообщения для отправки и при получении помещаются в специальные очереди, которые есть как у отправителя, так и у получателя.
• Сервис. Все вышеперечисленные компоненты соединяются сервисом, который и является единицей взаимодействия в системе.
• Маршруты. Для доставки сообщений на сервисы создаются маршруты доставки.
• Диалог. Программируемый способ отправки сообщения от инициатора к получателю и обратно.
• Транзакции. Вся обработка данных при передаче и получении производится по транзактному принципу, исключающему утрату данных.

Моя тестовая среда включает в себя:

• Два экземпляра SQL Server установленных на разных виртуальных машинах
• Контроллер домена.
• Точки доступа (Endpoints) настроены для использования Windows аутентификации.
• На обоих серверах созданы пользователи без логинов, использующие сертификаты для взаимной аутентификации.
• Сертификаты скопированы на оба сервера с использованием backup.

Как это все работает вместе. Ниже приведена упрощенная схема обмена сообщениями и ее описание.

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

Для просмотра сообщений, находящихся в очереди, во вне “выставлено” динамическое представление sys.transmission_queue, выполнив запрос к которому вы получите почти тот же результат. Однако в этом представлении есть очень полезный элемент, это столбец transmission_status, содержащий информацию об ошибке, возникшей при передаче и обработке сообщения.
Например: “Connection attempt failed with error: ‘10061(No connection could be made because the target machine actively refused it.)‘ .”
Также сообщение логируется в журнале транзакций, что обеспечивает его транзактную обработку.

Все сообщения от всех создаваемых сервисов проходят через эту внутреннюю таблицу и соответственно через просмотр, и при отсутствии ошибок передаются далее. Перед передачей сообщения могут шифроваться с использованием сертификатов. Будут сообщения шифроваться или нет зависит от настроек диалога инициирующего соединение.
2. После помещения сообщения в очередь передачи (sys.transmission_queue) выполняется его классифицирование.
Суть классификации состоит в том, чтобы определить, где размещен сервис, которому это сообщение адресуется. Для определения направления передачи используются маршруты доставки,созданные на этапе развертывания сервиса. В данном случае настроено два маршрута. Один указывает на удаленный сервис-получатель, другой указывает на локальный экземпляр SQL Server для доставки локальных оповещений.

Мы можем увидеть выполнение этапа классификации с помощью трасс SQL Profiler, если выберем события относящиеся к Service Broker. Обращаю внимание, что часть событий относящихся к Service Broker размещены в разделе Security Audit.

3. После выполнения классификации сообщения, устанавливается соединение с удаленной (как в данном случае) точкой соединения (Endpoint). В данном случае – это сервер использующий протокол TCP, с именем SQL2014-I1 и номером порта (Endpoint) 4022. Если при выполнении соединения возникнет ошибка, то она появится в трассе (как показано ниже) и в столбце transmission_status просмотра sys.transmission_queue.

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

В трассе на инициаторе и отправителе мы увидим подтверждение передачи.

5. Поскольку сообщение доставлено до получателя, то оно должно появиться во входной очереди получателя.

Далее, целевой сервис должен прочитать сообщения из очереди (желательно по одному) и отправить подтверждение о получении инициатору диалога. Делается эта процедура с использованием специального синтаксиса, который есть в прилагаемых скриптах https://technet.microsoft.com/en-us/library/bb839483(v=sql.105).aspx .
По мере чтения данных из очереди она пустеет, а отправителю посылается подтверждающие сообщения (или не посылаются) форма, состав которых, а также посылаются они, или нет, зависит от разработчика сервиса.
Если по мере чтения очереди программируется отправка подтверждающих сообщений, то они проходят тот же путь, что и от получателя, только в обратном порядке.
В данном блоге мы достаточно подробно рассмотрели весь путь от отправителя до получателя и в следующем начнем рассмотрение вопросов связанных с решением проблем с Service Broker на каждом из этапов обработки и передачи..

Александр Каленик, Senior Premier Field Engineer (PFE), MSFT (Russia)

Итак в продолжении темы обслуживания баз 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 . Перетащим ее в план, и щелкнем на ней два раза. Мы видим окно, в которое впишем следующее:

23/09/2015

Типичные ошибки настройки планов обслуживания СУБД MS SQL Server для 1С

Добрый день, коллеги.

В сегодняшней статье мы бы хотели рассмотреть достаточно востребованную и популярную тему, как настройка планов обслуживания MS SQL Server. В результате проведения аудитов мы д остаточно часто (более чем в 60% случаев) обнаруживаем некорректности в настройке СУБД MS SQL Server, используемой для работы с продуктами фирмы “1С”. Практика показывает, что эта СУБД является наиболее распространенной, поэтому в данной статье рассмотрим основные нюансы работы именно с ней.

Итак, с чего начинается настройка плана обслуживания? Конечно же с бэкапа! Первое правило DBA гласит: "Ничего не начинай делать без бэкапа". Ну и мы не будем. Давайте рассмотрим два основных варианта создания бэкапов, а точнее две модели резервного копирования, или модели восстановления (https://msdn.microsoft.com/ru-ru/library/ms189275(v=sql.120).aspx )

Восстановление по модели simple

Ваша база данных находится в SIMPLE режиме восстановления. Что это означает? Это означает, что бэкапы бывают только полные, журналы транзакций бэкапировать не нужно, производительность в этом смысле максимальная, но восстановиться можно только на точку бэкапа. Восстановление базы “на указанный момент времени” невозможно.
Следовательно, еженочно (или чаще, в зависимости от потребности) мы должны снимать свеженькую копию нашей базы данных и складывать ее в надежное место, и обязательно не в то, в котором лежит наша основная база данных
В целом, использование модели SIMPLE для реальных рабочих баз оправданно только в случаях исключительно высокой нагрузки и незначительности события потери данных с момента последнего бэкапа.

Кроме того, затрону сразу вопрос работы с журналом транзакций. Так как нам журнал тразакций в этом режиме восстановления не очень-то и помощник, нет необходимости в его резервном копировании - вся информация из журнала уже ушла в бэкап. Обычно журналы в такой модели восстановления почти не растут, но иногда полезно его усекать. Например, после массового изменения данных эта операция может быть крайне полезна с точки зрения места, занимаемого журналом транзакций. Если диск с журналом переполнится - проблемы функционирования базы данных вам обеспечены.

Усечение данных может быть проведено, как стандартным мастером настройки плана обслуживания, так и с помощью несложно скрипта на T-SQL:

DBCC SHRINKFILE (DatabaseName, 1);
GO

Этот скрипт уменьшит размер лог-файла базы данных до начального размера (по умолчанию, чаще всего это будет 1 Мб). Однако, не стоит выполнять эту операцию постоянно. В идеале, ваши файлы не должны изменяться в размере в ходе планомерной работы, но об этом мы поговорим как-нибудь в другой раз.

Восстановление по модели full

Давайте рассмотрим основные принципы настройки резервного копирования и управления размером журнала лога транзакций с точки зрения самого массового варианта - полной модели восстановления БД.

Полная модель восстановления отличается от простой тем, что в течение всей работы базы данных мы можем (а еще точнее - ДОЛЖНЫ!) делать бэкапы лога транзакций, тем самым обеспечивая возможность восстановления БД между точками основных бэкапов или откаты на конкретные промежутки времени функционирования базы, а также обеспечивая освобождение места в файле журнала (усечение). Если этого не делать, он будет расти постоянно до тех пор, пока однажды не заполнит все доступное ему место (либо на диске, либо до ограничения, заданного в СУБД). Последствия кажутся очевидными, и не самыми приятными.

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

Как уже было сказано выше, при выполнении резервной копии журнала транзакций базы данных в полной модели он усечется автоматически (только не путайте усечение со сжатием!).

Пересчет статистики и работа с индексами

Достаточно ошибочной является сложившаяся практика работы с индексами и статистикой у наших клиентов. Очень часто мы сталкиваемся вообще с полным отсутствием этих процедур в планах обслуживания баз данных. Часто они выполняются в неправильном порядке. Часто просто неоптимально (например, одновременно!).

Правильная последовательность действий выглядит так:

Определяем степень фрагментированности индекса

Если индекс маленький или мало фрагментирован, запускаем процедуру реорганизации индекса и пересчета статистики.

В противном случае запускам процедуру перестроения индекса. Процедура перестроения индекса фактически приведет к обновлению статистики, поэтому пересчитывать статистику после полного перестроения индекса не нужно.

1. Пересчитываем всю остальную статистику, где это требуется.

Если рассмотреть мини-скрипт для пересчета статистики и перестроения индексов (не претендуем на супер полноту и универсальность), то выглядеть он будет примерно так (с перебором индексов через курсор):

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.) + "].[" + 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., 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., stat.index_id

) stat

JOIN sys.indexes ind WITH(NOLOCK) ON stat. = ind.

AND stat.index_id = ind.index_id

JOIN sys.objects obj WITH(NOLOCK) ON obj. = stat.

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

CLOSE currentIndex

DEALLOCATE currentIndex

Обратите внимание на использование tempdb, а также на сохранение индекса доступным во время перестроения - в зависимости от редакции вашей СУБД последняя функция может быть недоступна.

Уведомления

Кроме всех технических аспектов, очень правильно настраивать такие планы обслуживания, которые при некорректном выполнении все-таки уведомят вас о произошедшей беде. И это будет самый короткий абзац в моей статье. :)

Если вам все показалось слишком сложным, или вы не уверены в том, что можете сделать подобные настройки самостоятельно - не стесняйтесь, обращайтесь к нам - мы поможем!