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

Диспетчер настроек SQL Server - интерактивное приложение для управления всеми службами на основе SQL Server, сетевыми протоколами, портами средства прослушивания и создания псевдонимов серверов. Диспетчер настроек SQL Server (SSCM) доступен в иерархии пунктов меню Start\Microsoft SQL Server 20xx\Configuration Tools\SQL Server Configuration Manager в версиях Microsoft Windows, предшествующих выпуску Windows 8 и Windows Server 2012. В последних указанных версиях операционной системы просто выполните поиск по словам SQL Server и выберите «Диспетчер настроек SQL Server» из списка приложений. При первом обращении к SSCM программа выглядит примерно так, как показано на экране 1 (в качестве примера в данной статье используется SQL Server 2014).

Экран 1. Диспетчер настроек SQL Server

Настройка

Рассмотрим более подробно возможные действия, разрешенные в диспетчере SSCM. Каждый элемент на левой панели представляет одну или несколько задач, которые можно выполнить в диспетчере SSCM. В некоторых случаях есть как 64-разрядный, так и 32-разрядный вариант. В данной статье мы остановимся на 32-разрядном варианте. Сегодня Microsoft SQL Server размещается на 32-разрядных серверах только в том случае, если:

а) вы обладатель старой версии SQL Server;

б) скорее всего, лишаете экземпляр SQL Server ценных ресурсов оперативной памяти.

Перечислим действия, доступные в диспетчере SSCM (см. экран 2).

Экран 2. Действия, доступные в SSCM

• Службы SQL Server. Данное действие позволяет запускать, останавливать и перезапускать все службы, связанные с Microsoft SQL Server. Кроме того, вы можете изменять учетные записи службы, поведение при запуске и дополнительные функции и параметры запуска в зависимости от службы.
• Сетевые настройки SQL Server. Это действие позволяет включать и отключать конкретные сетевые протоколы: Shared Memory, Named Pipes и TCP/IP, а также настраивать дополнительные параметры для каждого из них.
• Настройки собственного клиента SQL Server (на сегодня версия 11.0). Это действие позволяет установить порядок, в котором клиенты будут использовать специально включенные протоколы для подключения к настраиваемому экземпляру SQL Server. С его помощью можно создавать псевдонимы для экземпляра SQL Server, чтобы различные приложения конечных пользователей могли подключаться к серверам с именами, отличными от действительного имени сервера. Это делается на случай, если вы не можете изменить строки подключения при переносе баз данных приложения, но все же хотите обеспечить преемственность или скрыть настоящее имя сервера от конечных пользователей. Рассмотрим каждое из этих действий подробнее.

Службы SQL Server

Связанные службы SQL Server могут (и по идее должны) управляться и настраиваться из диспетчера SSCM, а не API-интерфейса services.msc. Как отмечалось выше, мы можем управлять не только поведением при запуске и учетной записью службы, но и дополнительными параметрами каждой службы (см. экран 3).

• SQL Server Integration Services. Дополнительные настраиваемые параметры отсутствуют.
• SQL Server Analysis Services. Дополнительные настраиваемые параметры отсутствуют.
• SQL Server Service:

1. FILESTREAM. Эта настройка позволяет включить или отключить доступ T-SQL, доступ файлового ввода-вывода, доступ к удаленному клиенту и установить имя общего ресурса FILESTREAM.

2. Высокий уровень доступности AlwaysOn. Эта настройка дает возможность включить или отключить группы доступности AlwaysOn, а также настроить отказоустойчивый кластер Windows (WFCS), на котором построена группа доступности.

3. Параметры запуска. Диспетчер SSCM позволяет назначить специальные параметры запуска, вступающие в силу для экземпляра. В вашем распоряжении всегда будет по крайней мере три параметра запуска для любого экземпляра SQL Server, чтобы обеспечить корректный запуск Microsoft SQL Server:

• -d. Указывает местонахождение файла данных (.mdf) базы данных master.
• -l. Указывает местонахождение файла журнала транзакций (.ldf) базы данных master.
• -e. Задает местонахождение файла журнала ошибок экземпляра.

Почему именно эти три параметра? Потому что master функционирует как обращенный вовне «мозг» экземпляра SQL, работающий в сочетании со скрытой базой данных Resource, предоставляя все необходимые метаданные, необходимые для запуска экземпляра SQL Server. Требуется использовать журнал ошибок для записи каждого шага процесса запуска.

Дополнительные параметры могут быть настроены, в частности, для запуска сервера в однопользовательском режиме с целью поиска неисправностей (-m); запуска экземпляра с минимальным набором настроек (-f) в случаях, когда необходимо обойти неудачный параметр, способный привести к ухудшению отклика экземпляра; установки флагов трассировки, изменяющих базовое поведение Microsoft SQL Server (-T). Мне приходилось использовать -f для обхода неудачных настроек, когда я пытался продемонстрировать проблемы ограничения памяти и устанавливал максимальное значение памяти сервера столь малым, что не мог запустить SQL Server. Конечно, большинство администраторов баз данных устанавливают несколько флагов трассировки за время своей профессиональной деятельности на каждом из серверов SQL Server через параметр -T, но подробнее об этом я расскажу в другой статье.

Полный список параметров запуска можно найти в официальной документации Microsoft (https://msdn.

microsoft.com/en-us/library/ms190

4. Advanced («Дополнительно»). Вкладка Advanced для службы SQL Server (см. экран 4) обеспечивает возможность изменить каталог дампа для экземпляра, а также настроить механизм передачи отзывов в компанию Microsoft для дальнейшей работы над продуктом. Кроме того, вы получаете возможность читать (но не изменять) дополнительные параметры, перечисленные ниже.

• SQL Server Reporting Services. Мы можем управлять некоторыми базовыми параметрами служб для SQL Server Reporting Services (SSRS), но для этой службы существует отдельный интерфейс. Я не рекомендую использовать SSCM для всех аспектов настройки SSRS, хотя некоторые из них доступны.
• SQL Server Browser («Обозреватель SQL Server»). Помимо поведения при запуске и учетной записи службы, существует несколько дополнительных настроек этой службы, которыми можно управлять, кроме каталога дампа и журнала ошибок. Рекомендуется отключить эту службу, если только вы не располагаете несколькими экземплярами SQL Server на одном узле.
• SQL Server Agent Service («Служба агента SQL Server»). Аналогично многим другим службам, вы можете настраивать только каталог дампа, ведение журнала ошибок и передачу отзывов, наряду с поведением при запуске и учетной записью службы.

Сетевые настройки SQL Server

Действия по сетевой настройке SQL Server позволяют включить любой или все три сетевых протокола, доступные в Microsoft SQL Server: Shared Memory, Named Pipes и TCP/IP.

Shared Memory и Named Pipes обеспечивают доступ сетевого компьютера к SQL Server, а TCP/IP определяет способы связи сетевых устройств с экземпляром SQL Server. Да, Named Pipes можно применять в среде Windows, но вы теряете все преимущества обхода сетевого стека при использовании Named Pipes между удаленными серверами. Параметры настройки Shared Memory начинаются и кончаются статусом включения. Named Pipes, в дополнение к статусу «включен-отключен», позволяет задать имя канала для SQL Server. Наконец, TCP/IP позволяет включать и отключать этот протокол, наряду с изменением порта, по которому SQL Server прослушивает запросы.

Настройка собственного клиента SQL Server

Этот набор действий позволяет установить очередность обработки запросов в SQL Server. Кроме того, вы сможете отключить названные протоколы для клиента, а также назначить имя канала и номер порта.

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

Вероятно, вы знаете, что обслуживание баз данных это целый комплекс процедур: создание бэкапов, проверка целостности, обслуживание индексов, статистики и т.д. На просторах сети (да и на Хабре в том числе) на эту тему написано множество статей и рекомендаций. Однако занимаясь внедрением «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. Последующее описание дополняет видеоролик и рассказывает о некоторых особенностях программы.

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

С одной стороны, мы старались сделать программу доступной новичкам и реализовать наиболее распространённые сценарии обслуживания. С другой стороны, мы хотели сделать так, чтобы программа была удобна продвинутым пользователям и помогала им настроить самые разнообразные сценарии, в том числе объединить операции обслуживания баз средствами Transact SQL с другими регламентными процедурами ваших приложений. Например, в QMB можно сделать сценарий, который вначале загрузит данные в «1С: Предприятие», а только потом сделает бэкап и выполнит остальное обслуживание. В итоге получился планировщик, предоставляющий свой фреймворк для исполнения T-SQL скриптов и пакетных файлов (с возможностью хранения результатов их исполнения).

Архитектура

Программа имеет три компонента: GUI клиента, службу QMB Service и файловую базу для хранения своих данных. При установке QMB устанавливаются все три компонента программы. Планы обслуживания не создаются, поэтому не требуется наличия службы агента SQL Server. Подробнее об архитектуре читайте .

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

Как было сказано выше, QMB не создаёт планов обслуживания на SQL Server. Вместо этого создаётся Политика обслуживания , которая сохраняется в локальном хранилище (файловой базе данных). По сути, политика – это группировка баз данных со схожими свойствами, которые обслуживаются по одним правилам. Политика содержит список баз данных, настройки хранения и копирования бэкапов. В политику входит один или несколько сценариев обслуживания. Сценарий содержит набор задач , исполняемых последовательно для каждой (включенной в политику) базы данных. Если проводить аналогию с планами обслуживания, то сценарий можно сравнить с вложенными Планами обслуживания (Maintenance Plan).

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

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

В программе имеется два набора встроенных задач. Первый набор задач базируется на T-SQL скриптах, полученных из открытых источников и созданных разработчиками QMB. Второй набор базируется на скриптах Ола Халенгрэн (администратор баз данных из Швеции), который разработал три популярные хранимые процедуры для обслуживания баз данных. Процедуры Ола устанавливаются автоматически в системную базу данных master, при создании политики из шаблона.

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

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

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

Для рабочих баз данных, с ежедневным оперативным вводом информации (OLTP -базы), рекомендуется выбирать политику с Полной моделью восстановления – например, для баз «1С: Предприятие», в которые ежедневно вводятся данные. Такая модель позволяет восстанавливать базу данных на актуальный или на произвольный момент времени.

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

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

Задачи

Как было сказано выше, в QMB имеется 5 типов задач – ниже описаны некоторые их особенности.

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

Большинство системных задач это T-SQL скрипты. Сам скрипт можно просмотреть в форме задачи:

Тексты скриптов (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-уведомлении.

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

Наличие бэкапов ещё не означает, что удастся восстановить данные при сбое – восстановление может завершаться ошибкой по самым различным причинам. Например, может случится так, что цепочка архивных копий будет прервана, а вы даже не будете знать об этом, пока не попробуете восстановить данные. Именно поэтому лучшие практики говорят, что хороший DBA должен регулярно проверять созданные архивные копии, выполняя восстановление из них. Другого 100% способа просто не существует. Microsoft также рекомендует хотя бы единожды протестировать все архивные копии. Задачи для автоматизированного восстановления в SSMS нету, а ежедневно проверять бэкапы вручную желающих найдётся не много.

В 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. Не рекомендуется делать бэкапы в сетевую папку, т.к. при восстановлении приходится «тащить» файлы бэкапов по сети, что значительно увеличивает время восстановления. Правильнее будет настроить создание бэкапов на локальный диск с ежедневным копированием в сетевую папку.

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

С помощью программы можно поддерживать копии баз данных в актуальном состоянии. Например, для разработчиков 1С можно каждую ночь актуализировать тестовую базу. Для этого нужно создать задачу, аналогичную встроенной «Восстановление из арх. копий во временную БД». В задаче необходимо указать базу-источник бэкапов и базу, в которую будет выполняться восстановление. И уже потом разместить задачу в ночном сценарии. На рисунке ниже показана задача, которая выполняет восстановление бэкапов базы Accounting в базу данных AccountingCopy. Причём если на SQL Server нет базы данных AccountingCopy, то она будет создана автоматически.

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

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

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

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

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

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

Восстановить базу данных можно в стандартной консоли SSMS. Однако в QMB есть аналог с более простыми настройками:

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

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

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

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

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

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

В отличие от 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
В процессе восстановления возникла ошибка

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

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

Полную версию программы можно скачать на нашем сайте . Есть пробный период (30 дней с момента первой регистрации SQL Server), после которого на каждый SQL Server необходимо приобрести лицензию. Однако QMB позволяет бесплатно (с некоторыми ограничениями) обслуживать базы данных на SQL Express . Также для SQL Express есть недорогие коммерческие лицензии от 1560 руб. На текущий момент стоимость Профессиональной лицензии для российских компаний составляет 7100 руб. Характеристики и цены можно посмотреть .

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

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

Заключение

Иногда я сталкиваюсь с мнением, что сторонние программы к SQL Server рассматривают исключительно как «костыли». Что, якобы, бэкапы или обслуживание, настроенное с помощью таких программ, по определению хуже, чем с использованием штатного агента SQL Server. В таком случае мне приходится объяснять, что SQL Server понимает только инструкции Transact SQL и ему совершенно всё равно, кто именно отправит эту инструкцию – агент SQL Server или другая программа. Например, чтобы проверить целостность базы данных ему нужно отправить команду DBCC CHECKDB , а чтобы сделать бэкап – BACKUP DATABASE . Очевидно, что результат всегда будет идентичным, вне зависимости от того, кто отправит эту команду.

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

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

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

Ниже будет рассказано, как создать план обслуживания в с помощью программы «Среда SQL Sever Management Studio». В данной статье я просто постараюсь наглядно описать алгоритм создания плана обслуживания с помощью Мастера планов обслуживания, не вдаваясь в теоретическую часть вопроса. Получить больше информации по данной области можно изучив электронную документацию по SQL Server на сайте MSDN .

В описанный ниже план будут входить всего 2 задачи.

• Резервное копирование базы данных.
• Проверка целостности базы данных.

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

1. Исходные данные

1. Операционная система семейства Windows (в моем примере используется )
2. Установленный Microsoft SQL Server 2008 R2 (об установке SQL Server можно прочитать )
3. Существующая база данный в SQL Server (о создании баз данных в SQL Server читайте )
4. Настроенная компонента Database Mail, в случае если требуется уведомлять по электронной почте операторов о результатах выполнения плана обслуживания (о том как настроить компоненту Database Mail и создать оператора системы я писал ).

2. Проверка работы Агента SQL Server

Первое что нам необходимо сделать, это убедиться что Агент SQL Server установлен и работает. Для этого запустим оснастку «Службы » («Пуск » (Start ) — «Администрирование » (Administrative Tools ) — «Службы » (Services )) и в списке служб найдем службу «Агент SQL сервер » (SQL Server Agent ).

Откроем свойства этой службы (кликнув по ней 2 раза) и убедимся что:

• Тип запуска стоит «Автоматически » (Startup type: Automatic);
• Состояние «Работает » (Service status: Started);

В противном случае, необходимо изменить параметры как на скриншоте выше и сохранить настройки нажав «Применить » (Apply) .

Теперь запустим программу «Среда SQL Sever Management Studio» («Пуск » (Start ) — «Все программы » (All programs) — «Microsoft SQL Server 2008 R2 » — «Средства SQL Server 2008 R2 «) и введем данные для авторизации.

После чего, еще раз убедимся что Агент SQL Server работает (в обозревателе объектов должна быть вкладка «Агент SQL Server » (SQL Server Agent) с зеленой иконкой слева.

3. Создание плана обслуживания

Теперь перейдем непосредственно к созданию плана обслуживания. В обозревателе объектов (Object Explorer) раскроем вкладку «Управление » (Management), кликнем правой кнопкой мыши по вкладке «Планы обслуживания » (Maintenance Plans) и в контекстном меню выберем «Мастер планов обслуживания » (Maintenance Plan Wizard) .

В запустившемся мастере планов обслуживания на странице приветствия нажимаем «Далее » (Next) и в следующем окне вводим имя и описание нового плана.

Затем необходимо определиться с расписанием, по которому будет выполняться данный план обслуживания. Для этого установим переключатель на «Единое расписание для всего плана или без расписания » (Single schedule for the entire plan ore no schedule ) и нажмем «Изменить… » (Change…) для назначения расписания.

Откроется окно «Свойства расписания задания » . Здесь зададим те параметры, согласно которым должен выполняться план обслуживания и нажмем «ОК » . В моем примере это:

• Выполняется — «Еженедельно » (Occurs — Weekly);
• Повторяется каждые — «1 нед. » в «Воскресенье » (Recurs every: 1 week(s) on Sunday);
• Выполняться один раз в день в: — «2:00:00» (Occurs onсe at: «2:00:00»);

Еще раз убедимся, что расписание задано верно, и нажмем «Далее » (Next) .

Здесь выберем те задачи, которые будет выполнять наш план обслуживания. В моем примере это:

1. Проверка целостности базы данных (Check Database Integrity);
2. Резервное копирование базы данных (полное) (The Back Up Database (Full));

Заметьте, что для каждой задачи приводится ее краткое описание в поле снизу. Выбрав необходимые задачи, жмем «Далее » (Next) .

Теперь необходимо задать порядок выполнения задач, используя кнопки «Вверх… » (Move Up) и «Вниз… » (Move Down). Установив порядок, жмем «Далее » (Next) .

Здесь требуется задать параметры для каждой задачи в плане. Первая задача в нашем списке это «Копирование БД (полное) » (Back Up Database (Full)).

Прежде всего необходимо выбрать базы данных для резервного копирования, нажав на кнопку выбора списка «Определенные базы данных » (Select one ore more). Выбрав необходимые для резервного копирования базы данных, нажимаем «ОК » .

Ниже зададим размещение и срок хранения резервных копий, а также установим дополнительные параметры:

1. Если установить переключатель «Создать файл резервной копии для каждой базы данных » (Create a backup file for every database) , то при выполнении задания в выбранной директории будет создаваться несколько файлов резервных копий с именами, соответствующими названиям баз данных. Ну а установка флага «Создавать вложенный каталог для каждой базы данных » (Create a sub-directory for each database) разложит файлы по отдельным папкам. Обратите внимание, что необходимо оставить заполненным расширение файла резервной копии.
2. Установка флага «Срок действия резервного набора данных истекает » (Backup set will expire) указывает SQL-серверу, когда этот набор может быть перезаписан без явного пропуска проверки на истечение срока.
3. Для наибольшей надежности, можно установить флаг «Проверять целостность резервной копии » (Verify backup integrity).
4. Также рекомендую выбрать режим «Сжимать резервные копии » (Compress backup) для экономии дискового пространства, если используемая версия SQL Server поддерживает данную функцию.

Если дисковое пространство ограничено, можно также выбрать один файл для хранения резервной копии, который будет перезаписываться после каждого выполнения плана обслуживания. Для этого установим соответствующий переключатель на «Создать резервную копию баз данных в одном или нескольких файлах » (Back up databases across one ore more files) и указжем соответствующее имя файла (будьте внимательны, файл резервной копии следует задавать с расширением.bak), а также выберем режим «Перезаписать » в случае, если файлы резервной копии существуют (If backup files exist: Overwrite).

Теперь очередь задачи «Проверка целостности базы данных » (Database Check Integrity). Для нее всего лишь необходимо выбрать базу данных. В моем примере это все та же база данных, что и на предыдущем шаге. Определившись с базами, жмем «Далее » (Next).

На следующей странице возможно выбрать директорию, куда будет сохраняться лог выполнения задания, а также указать SQL Server для отправки отчета по электронной почте. Задав параметры, снова жмем «Далее » (Next).

Проверим еще раз все настройки плана обслуживания, и если все верно, нажимаем «Готово » (Finish).

Мастер начнет построение плана обслуживания. Если мастер не обнаружит ошибок, то увидим сообщение об успешном построении плана. В противном случае необходимо устранить ошибки и повторить процедуру снова. Закроем окно, нажав «Закрыть » (Close).

4. Запуск выполнения плана обслуживания

Для запуска выполнения плана обслуживания перейдем в программу «Среда Microsoft SQL Server Management Studio». Здесь, раскрыв вкладку «Планы обслуживания » (Maintenance Plans) увидим наш только что созданный план. Чтобы проверить его работу, кликнем по нему правой кнопкой мыши, и в контекстном меню выберем пункт «Выполнить » (Execute) .

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

А в соответствующих директориях должны появиться файл резервной копии

и файл лога выполнения плана.

Открыв, этот файл, вы должны увидеть примерно следующее:

Если все так, поздравляю! План обслуживания 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)

Зачастую база работает в «нормальных» условиях. Что под этим подразумевается:

• Сервер 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, возможно мои комментарии весьма поверхностны, с удовольствием прочту замечания в комментариях и личке.