Установка служб SQL Server R Services (в базе данных). Сервис событий в SQL-сервере


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

  • Сервер 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С: Предприятие», нам частенько приходится сталкиваться с тем, что обслуживание баз данных настраивается либо неправильно, либо по очень упрощённой схеме. Например, чтобы не заморачиваться с управлением журналами транзакций, для «боевых» баз устанавливается Простая модель восстановления (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 наносят вред репутации всей службе ИТ, в то время как потеря данных в большинстве случаев приводит к ещё более серьёзным последствиям. Если у вас создан план обслуживания, но нет уверенности в его надёжности, то вы сидите на мине замедленного действия – я настоятельно советую предотвратить ЧП заранее, чем расхлёбывать его последствия.

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

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

Итак в продолжении темы обслуживания баз 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

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

Более точно сказать было даже несколько проблем, которые, как обычно, наслоились друг на друга (или их “наслоили” администраторы пытавшиеся решить проблему).
Статья не ставит перед собой целью всестороннее и подробнейшее описание всей системы 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-сервере уведомить того или иного клиента. Ещё чаще это является розово-голубой мечтой заказчика, чтобы разработчик реализовал такой механизм. Например, при превышении лимитов отгрузки какому-либо потребителю, должны быть немедленно уведомлены менеджеры, работающие с этим потребителем. Некоторые заказчики систем требуют (а мечтают об этом все заказчики без исключения), чтобы при изменении каких-то данных, у остальных пользователей системы эта информация автоматически обновлялась, причем незамедлительно. Здесь не будет обсуждаться целесообразность такого требования (оно имеет много оснований для критики), здесь будут обсуждаться только пути решения. microsoft sql-сервер имеет штатное средство для организаций уведомлений — alerts, но это средство имеет весьма ограниченное применение, по большому счету не дающее возможность создать на его основе гарантированно работающий механизм. И вот почему: Связь с клиентской программой может быть осуществлена путем посылки e-mail или эмуляцией посылки "net send". И то, и другое неудобно для получения уведомления.

Средство e-mail неудобно по причинам:

a) нет гарантии доставки, почта может теряться.
b) почта может "застрять" на промежуточных узлах.
c) требуется обязательно наличие протокола tcp/ip
d) требуется наличие smtp-сервера и настройка почтового профиля.
e) требуется особая настройка sql-сервера, чтобы он смог посылать письма.
f) требуется наличие у каждого клиента, ждущего события, почтового ящика.
g) в клиентской программе требуется организовать почтовый клиент.

Посылка путем «net send» неудобна по следующим причинам:

a) нет гарантии доставки, так как это организовано через средство mailslot, не имеющее такой гарантии.
b) требуется наличие корректного разрешения имен netbios в сети.
c) требуется наличие на клиенте "Клиент для сетей Микрософт".
d) задействован стандартный mailslot, это может иметь пересечение с другими программами.

И в целом средство alerts неудобно необходимостью регистрации каждого клиента в качестве оператора и соответствующей настройкой. Т.е. для простейших случаев alerts применить можно. Но для большинства случаев оно неприменимо.

Известные реализации и концепции

Широкой общественности известны несколько вариантов реализации механизма уведомления сервером клиента. Это:

1. Создание объекта (extended stored procedure или activex), посредством которого sql-сервер уведомляет клиента через сокеты tcp/ip. При этом на клиенте организована прослушка, т.е. клиентская программа стала сервером tcp/ip.
Недостатки этого метода:
a) Привязка к протоколу tcp/ip. В сети, где используется только ipx, netbeui или appletalk, такой механизм не применить.
b) Нет асинхронности. Если это событие генерируется из триггера, будут проблемы производительности.

2. Создание объекта (extended stored procedure или activex), посредством которого sql-сервер уведомляет клиента через named pipes или mailslots. При этом на клиенте организована прослушка того или другого.
Недостатки этого метода:
a) требуется наличие корректного разрешения имен netbios в сети.
b) требуется наличие на клиенте "Клиент для сетей Микрософт".
c) в случае использования mailslot нет гарантии доставки.
d) в случае использования named pipes, это нельзя применить на клиентских компьютерах с операционной системой windows 95/98/me, так как named pipe можно создать только в операционной системе на архитектуре nt.
e) Нет асинхронности. Если это событие генерируется из триггера, будут проблемы производительности.

3. Периодический опрос sql-сервера клиентом (периодическое чтение специальной таблички евентов). Это очень простой путь, но, тем не менее, свободный от большинства вышеперечисленных недостатков. К сожалению, этот метод имеет свои специфичные 2 недостатка: a) получение уведомления может быть задержано на величину таймаута опроса и b) при маленьком таймауте возникает существенный трафик. Тем не менее, при небольшом кол-ве сессий, этот метод вполне пригоден и незаслуженно обойден вниманием.

Предлагаемый вариант решения

Вашему вниманию предлагается вариант решения проблемы, свободный от вышеперечисленных (всех вышеперечисленных!) проблем, но вместе с тем достаточно простой. Идея такова: на сервер помещается некий двоичный объект, который sql-сервер может вызывать (а это может быть только extended stored procedure или activex-объект), имеющий два невзаимосвязанных метода.
Первый метод создает с помощью функции win32api createevent объект ядра win32, именуемый "event" с уникальным наименованием, переданным параметром. Далее вызывается функция win32api waitforsingleobject, наткнувшись на которую, поток останавливается и стоит в ожидании, пока этот объект ядра не засигналит. Обращаю ваше внимание, на тот факт, что таких объектов ядра может быть создано сколько угодно. Это ограничено только кол-вом хендлов в системе.
Второй метод вызывает объект ядра event по имени, заданным параметром, с помощью функции win32api setevent и выставляет ему свойство "signaled". Как только это произойдет, поток с первым методом пробуждается и возвращает управление вызвавшему процессу. Второй метод, не ожидает результата, а возвращает управление своему вызвавшему процессу сразу же после выставления свойства "signaled". Таким образом достигается асинхронность.
Теперь остается только сделать хранимые процедуры t-sql, управляющие этим объектом и нужная функциональность "у нас в кармане". Клиентская программа в отдельном потоке запускает хранимую процедуру ожидания события, передавая параметром уникальный признак-адрес. Это может быть и имя пользователя, и имя компьютера, и любая строка. Главное, чтобы это была уникальный идентификатор в пределах клиент-серверной системы. Хранимая процедура вернет результат только в случае, если для этого адресата будет сгенерировано событие. При получении события, процедура перезапускается. При закрытии программы поток ожидания события просто прибивается через terminatethread.
На первый взгляд эта метода обладает "ужасным" недостатком — существует постоянный коннект с sql-сервером, который большую часть времени ничего не делает. Но это только первое впечатление. На самом деле, задействуются ресурсы здесь только на поддержание коннекта — это что-то несколько килобайт на сессию. И все! Больше никаких осязаемых ресурсов не тратиться, особенно на фоне преимуществ, которые описаны ниже. О дополнительных лицензиях можно тоже не беспокоиться, если выбрана модель лицензирования "per server". В этом случае с одной машины может быть сколько угодно коннектов к sql-серверу, это все равно будет занимать ровно одну клиентскую лицензию.

Готовое решение

Решение состоит из activex-объекта в виде файла algoevt.dll и двух хранимых процедур spwaitforevent и spraiseevent. Перед использованием этот файл надо поместить на сервер и зарегистрировать activex-объект с помощью системной утилиты regsvr32.exe. Дальше вся работа будет производиться через хранимые процедуры. В готовом решении реализована несколько бОльшая функциональность, чем в описанной концепции. Кроме самого факта события, можно передать также произвольную информацию в виде строки в размере до 250 символов. Каждая процедура имеет два параметра. Первая — это уникальный идентификатор-адрес, о котором говорилось выше, а второй параметр — дополнительная передающаяся информация. spwaitforevent надо вызвать с клиента из отдельного потока (приоритет потока можно выбрать самый низкий). При получении события, процедуру надо перезапустить. Тайм-аут исполнения запроса надо задать бесконечный.


2024 © gtavrl.ru.