Аннотация: Управлять службами SQL Server – дело очень тонкое и требующее специфических знаний о принципах работы компонентах службы: – SQL Server Agent, Microsoft Distributed Transaction Coordinator и Microsoft Search. Рассматриваются доступ к часто изменяющимся параметрам системы. Инструментальные средства SQL Server Service Manager, SQL Server Enterprise Manager и Windows 2000 Service Control Manager позволяют расширить возможности управления службами. Приводятся примечания, защищающие администраторов от некорректных действий.
Установив Microsoft SQL Server 2000, вы можете начать им пользоваться. Но до того, как вы сможете входить в систему и начнете строить свою базу данных, нужно научиться запускать службу SQL Server и ее компоненты – SQL Server Agent, Microsoft Distributed Transaction Coordinator и Microsoft Search . Эти компоненты, описанные в данной лекции, исполняются как отдельные службы, дополняющие службу SQL Server . В данной лекции мы также расскажем, как запускать, останавливать и управлять этими службами при помощи трех инструментальных средств – и .
Примечание . В данной лекции сделан упор на описание работы SQL Server 2000 в операционной системе Microsoft Windows 2000, хотя SQL Server 2000 может работать и в Microsoft Windows NT 4. В операционной системе Microsoft Windows 98 SQL Server запускается как исполняемый файл, потому что Windows 98 не поддерживает службы.
Важно, чтобы вы научились управлять службой SQL Server 2000 при помощи Enterprise Manager . Обратите внимание, что данная лекция дает лишь краткое знакомство с Enterprise Manager . Многие задачи, решаемые с помощью Enterprise Manager , будут рассмотрены в следующих лекциях. Это, например, такие задачи, как создание баз данных и объектов, конфигурирование настроек сервера, конфигурирование репликации и управление репликацией, управление резервным копированием. А в другой лекции основное внимание будет уделено применению Enterprise Manager для управления службой SQL Server и другими службами.
Службы SQL Server
Служба – это программа или процесс, выполняющие специфические функции поддержки других программ. Когда вы запускаете SQL Server, в операционной системе Windows NT или Windows 2000 запускается служба SQL Server. Эта служба управляет файлами баз данных, исполняет операторы Transact-SQL (T-SQL) , распределяет ресурсы среди пользовательских соединений, исполняющихся одновременно, проверяет непротиворечивость данных и выполняет еще много других задач. Если вы инсталлируете один или несколько экземпляров SQL Server, то службы для отдельных экземпляров SQL Server будут иметь имена MSSQL$ИмяЭкземпляра , где ИмяЭкземпляра – имя экземпляра, назначенное вами при инсталляции. Соответственно, службы SQL Server Agent для экземпляров SQL Server будут иметь имена SQLAGENT$ИмяЭкземпляра . Однако для всех экземпляров SQL Server будет только по одной инсталляции Microsoft Distributed Transaction Coordinator и Microsoft Search.
Программные компоненты этих трех служб вы получаете в рамках лицензии на копию SQL Server. Инсталляция SQL Server Agent производится по умолчанию при инсталляции SQL Server. Если у вас не инсталлированы Microsoft Distributed Transaction Coordinator и Microsoft Search, то вы можете снова запустить инсталляционную программу SQL Server, чтобы установить эти компоненты, которые имеют там названия DTC Client Support и Full-Text Search , соответственно. А сейчас мы расскажем, что делает каждая из этих трех служб.
SQL Server Agent осуществляет планирование и исполнение заданий, оповещений, извещений и планов обслуживания базы данных. Без этой службы работа администратора баз данных станет гораздо более трудной, а может, вообще невозможной. Благодаря SQL Server Agent можно автоматизировать рутинные процедуры по обслуживанию базы данных. Например, вы можете создать задание, которое будет автоматически выполнять резервное копирование базы данных ежесуточно в 1 час пополуночи, и другое задание, которое будет автоматически выполнять резервное копирование журнала транзакций каждые полчаса. Чтобы следить за производительностью вашей системы, можно создать оповещение о состоянии производительности, которое будет информировать вас, если загруженность центрального процессора сервера превысит 90%. Для решения подобных задач нужно запускать службу SQL Server Agent, которую можно сконфигурировать на автоматический запуск при запуске SQL Server, а можно запускать и вручную. Вам следует сконфигурировать ее на автоматич еский запуск, что будет гарантировать исполнение ваших запланированных заданий, оповещений и извещений. В "Администрирование Microsoft SQL Server" будет рассказано, как создать план обслуживания базы данных, а в "Автоматизация административных задач" – как при помощи SQL Server Agent назначать задания, оповещения и извещения.
Microsoft Distributed Transaction Coordinator – это администратор транзакций ( transaction manager ), при помощи которого в транзакции ваших приложений можно включать данные из различных источников, в том числе данные из баз данных с удаленных компьютеров. Это значит, что при помощи одной транзакции можно обновлять данные на многих серверах, доступных через сеть. Администратор транзакций гарантирует, что все обновления станут постоянными для всех источников данных (если транзакция зафиксирована) или, в случае ошибки, что для всех источников данных будет произведен откат всех изменений. (Об администраторе транзакций Microsoft Distributed Transaction Coordinator см. "Службы компонентов и Microsoft Distributed Transaction Coordinator" .)
Запускайте службу Microsoft Search , когда вам нужна поддержка полнотекстного индексирования и поиска. Благодаря полнотекстному индексированию возможно выполнение более сложного поиска среди данных, содержащих текстовые строки. Например, вы можете искать слова, близкие к заданному слову, или можете искать определенную фразу.
Как мы уже говорили, имеется несколько инструментальных средств для остановки и запуска служб SQL Server: SQL Server Service Manager, SQL Server Enterprise Manager и Windows 2000 Service Control Manager . Давайте сначала рассмотрим , при помощи которого можно управлять службами SQL Server, SQL Server Agent, Microsoft Distributed Transaction Coordinator и Microsoft Search .
Применение SQL Server Service Manager
Для запуска или остановки служб SQL Server при помощи , выполните следующие действия (а ещё, как вы увидите, службу SQL Server можно и приостанавливать).
Вероятно, вы знаете, что обслуживание баз данных это целый комплекс процедур: создание бэкапов, проверка целостности, обслуживание индексов, статистики и т.д. На просторах сети (да и на Хабре в том числе) на эту тему написано множество статей и рекомендаций. Однако занимаясь внедрением «1С: Предприятие», нам частенько приходится сталкиваться с тем, что обслуживание баз данных настраивается либо неправильно, либо по очень упрощённой схеме. Например, чтобы не заморачиваться с управлением журналами транзакций, для «боевых» баз устанавливается Простая модель восстановления (Simple Recovery model). И это несмотря на то, что потеря информации за пару часов уже критична для компании. Иногда задача по сжатию файлов БД включается в регулярное обслуживание («шобы не росло»), или после обновления индексов идёт уничтожение статистики и прочие подобные ляпы. Так происходит потому, что чаще всего в компаниях нет опытного администратора БД и обслуживанием приходится заниматься кому-то из сотрудников ИТ-службы – «невольному» администратору баз данных (DBA). При этом такой DBA не всегда осознаёт все риски и возложенную на него ответственность.
Для обслуживания баз Microsoft предлагает планы обслуживания (Maintenance Plan) в SQL Server Management Studio (SSMS). Однако как показывает практика, создать и настроить качественный и надёжный план обслуживания может только опытный DBA. Отмечу, что надёжное обслуживание максимально автоматизировано и не требует регулярного ручного мониторинга администратором, а также гарантирует, что данные удастся восстановить в случае сбоя.
Сторонние программы, которые имеются на рынке и способны облегчить жизнь, в основном автоматизируют создание бэкапов. Выбор таких программ очень широк. Они позволяют делать бэкапы сжатые и шифрованные, на FTP/GoogleDrive/Amazon и так далее. Бэкапы тут можно сравнить с креветками, о которых говорил Бабба в картине «Форест Гамп»: «… их можно жарить, варить, печь, тушить, можно приготовить шашлык из креветок, креветки по-креольски, креветки гамбо, поджаренные с рисом … ».
Однако как было сказано, настройка бэкапов это далеко не всё, поэтому такие программы закрывают лишь часть вопросов.
В итоге «невольному» DBA приходится читать статьи, разбираться с SSMS, разрабатывать стратегию резервного копирования, искать скрипты, настраивать уведомления. Времени уходит много, но всегда есть, что обматерить… А хочется жить безмятежно! Так, чтобы один раз сделал и забыл.
В этой статье я хотел бы сделать обзор нашей программы Quick Maintenance & Backup (QMB), которая поможет вам просто и быстро настроить обслуживание баз данных на Microsoft SQL Server. Бесспорно, что для больших и высоконагруженных баз данных не обойтись без опытного DBA и индивидуального тюнинга производительности, но если вам приходится иметь дело с множеством небольших баз (как правило до 50-80 Гб), то данная утилита будет полезна как новичкам, так и продвинутым пользователям.
Основные возможности QMB
- Простая и быстрая настройка
- Обслуживание нескольких SQL Server в одной программе. Поддерживаются SQL Server версий 2000 и старше, в том числе Express редакции
- 30 встроенных задач с открытыми скриптами, в том числе популярные скрипты Ola Hallengren:
– бэкапы - полный, разностный, журнал транзакций
– проверка целостности
– обслуживание индексов и статистики
– обслуживание системных баз
– копирование файлов бэкапов с возможностью указания срока хранения
– автоматизированная проверка бэкапов через восстановление
– поддержание копий баз данных в актуальном состоянии - 7 предустановленных политик обслуживания для Полной и Простой модели восстановления
- Мониторинг свободного места на дисках SQL Server
- Пользовательские задачи на скриптовых языках Transact SQL, CMD, VBScript, JavaScript, PowerShell и других
- Статистика изменения размеров баз данных. Расчёт среднего прироста данных
- Уведомления по электронной почте
- Подробный журнал обслуживания
В коротком видеоролике ниже показан сквозной пример настройки обслуживания с помощью QMB. Последующее описание дополняет видеоролик и рассказывает о некоторых особенностях программы.
Концепция: доступно новичкам, удобно профессионалам
С одной стороны, мы старались сделать программу доступной новичкам и реализовать наиболее распространённые сценарии обслуживания. С другой стороны, мы хотели сделать так, чтобы программа была удобна продвинутым пользователям и помогала им настроить самые разнообразные сценарии, в том числе объединить операции обслуживания баз средствами 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)
- Копирование архивных копий (используется в одноименной системной задаче)
Обслуживание больших и маленьких баз данных. Шаблоны политик
Политику обслуживания можно создать из шаблона или вручную с нуля. Текущая версия программы включает 7 шаблонов, которые преимущественно различаются:- Моделью восстановления баз данных. 5 политик с полной моделью восстановления (Full recovery model) и 2 с простой (Simple recovery model).
- Порядком обслуживания индексов. Для небольших баз дефрагментация индексов выполняется каждую ночь, а обновление изменившейся статистики в течении дня; для больших баз дефрагментация индексов выполняется раз в неделю.
- Набором используемых задач в сценариях. Для обслуживания используются задачи/скрипты Ола Халенгрэн или QMB.
После создания политики из шаблона можно изменить любые её параметры – сценарии и расписание, задачи и порядок уведомлений. В дальнейшем созданную политику можно копировать для других серверов, зарегистрированных в программе.
Подробнее о различиях в шаблонах можно посмотреть в справке .
Задачи
Как было сказано выше, в 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)
Подобные ошибки случаются редко, как правило, по невнимательности или незнанию сотрудников. В таком случае мы просто делаем дополнительный полный бэкап.
Советы тем, кто захочет настроить подобную проверку:
- Операция восстановления ресурсоемкая, поэтому её следует включать в сценарии исполняемые только в нерабочее время.
- Для создания временной тестовой базы и восстановления бэкапов в неё требуется запас дискового пространства, равный как минимум самой большой базе данных в политике + 10% от её объёма.
- Восстановление больших баз может занимать значительное время. Не включайте задачу по проверке, если не уверены, что операция успеет завершиться в отведённое окно обслуживания.
- Правильно размещайте задачу в сценарии. Учитывайте, что восстановление выполняется на актуальный момент времени, т.е. на момент исполнения задачи. Например, если задачу проверки бэкапов разместить сразу после создания полной резервной копии, то будет протестирован только последний бэкап, т.к. его будет достаточно для восстановления базы на актуальный момент времени.
- Если на проверку бэкапов всех баз политики не хватает окна обслуживания, можно проверять бэкапы только определённых баз данных. Либо распределить задачи по дням недели. Например, сегодня ночью проверить бэкапы баз А и B, а завтра – баз C и D.
- Не рекомендуется делать бэкапы в сетевую папку, т.к. при восстановлении приходится «тащить» файлы бэкапов по сети, что значительно увеличивает время восстановления. Правильнее будет настроить создание бэкапов на локальный диск с ежедневным копированием в сетевую папку.
Автоматизированная поддержка копий баз в актуальном состоянии
С помощью программы можно поддерживать копии баз данных в актуальном состоянии. Например, для разработчиков 1С можно каждую ночь актуализировать тестовую базу. Для этого нужно создать задачу, аналогичную встроенной «Восстановление из арх. копий во временную БД». В задаче необходимо указать базу-источник бэкапов и базу, в которую будет выполняться восстановление. И уже потом разместить задачу в ночном сценарии. На рисунке ниже показана задача, которая выполняет восстановление бэкапов базы Accounting в базу данных AccountingCopy. Причём если на SQL Server нет базы данных AccountingCopy, то она будет создана автоматически.
Во время процедуры восстановления база AccountingCopy будет переведена в однопользовательский режим с отключением всех пользовательских соединений.
Копирование файлов бэкапов
В видеоролике было показано, как в программе настраивается дополнительное копирование бэкапов в сетевую или локальную папку. Копирование бэкапов позволяет в определённой степени застраховаться от повреждения файлов, диска или сервера целиком. В случаях же с виртуальным SQL Server, копирование бэкапов на реальный физический диск позволит быстро восстановить одну или несколько баз данных, не дожидаясь восстановления всей виртуальной машины.Ниже я хотел бы акцентировать внимание на нескольких особенностях копирования бэкапов c помощью QMB:
- Частота копирования определяется расписанием сценария, содержащего задачу «Копирование архивных копий». Задачу можно разместить в одном или нескольких сценариях.
- Копируются только новые и изменённые файлы бэкапов – это снижает нагрузку на сеть и позволяет выполнять частое копирование. Например, можно копировать каждый раз после создания нового бэкапа журнала транзакций.
- Для сетевой папки можно задать срок хранения файлов. Таким образом, на локальном диске SQL Server можно хранить бэкапы, например, за 1 неделю, а в сетевой папке за 1 месяц.
- Возможно настроить копирование бэкапов только для избранных баз политики.
Восстановление баз данных
Восстановить базу данных можно в стандартной консоли 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
С ошибками: 11. Задача "Восстановление из арх. копий во временную БД с последующей проверкой целостности" (база данных: 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 наносят вред репутации всей службе ИТ, в то время как потеря данных в большинстве случаев приводит к ещё более серьёзным последствиям. Если у вас создан план обслуживания, но нет уверенности в его надёжности, то вы сидите на мине замедленного действия – я настоятельно советую предотвратить ЧП заранее, чем расхлёбывать его последствия.
Спасибо за внимание, готов ответить на ваши вопросы в комментариях.
Теги: Добавить метки
В MS SQL Server есть несколько системных баз данных:
master — В этой базе данных хранятся все данные системного уровня для экземпляра SQL Server.
Model
— Используется в качестве шаблона для всех баз данных, создаваемых в экземпляре SQL Server. Изменение размера, параметров сортировки, модели восстановления и других параметров базы данных model приводит к изменению соответствующих параметров всех баз данных, создаваемых после изменения.
Msdb
— Используется агентом SQL Server для планирования предупреждений и задач, так же является хранилищем пакетов SSIS, хранилищем информации по резервному копированию.
tempdb
— База данных для временных объектов или для промежуточных результирующих наборов.
Resource
— База данных только для чтения. Содержит системные объекты, которые входят в состав SQL Server. Системные объекты физически хранятся в базе данных Resource, но логически отображаются в схеме sys любой базы данных.
Задачей обслуживания данных баз данных является возможность восстановления данных баз данных во время их повреждения, неудачного обновления, выхода из строя оборудования и других возможных проблем.
Типичные задачи обслуживания для системных баз данных (за исключением БД TempDb и resource):
— Создание резервной копии баз данных (с глубиной хранения минимум 7 дней)
— Проверка целостности баз данных инструкцией DBCC CHECKDB
Все эти операции можно завернуть в задание sql agent-а и выполнять ежедневно, благо объем данных баз данных обычно небольшой, то и операции проходят довольно быстро, а спокойствия прибавляет).
Но это еще не все, на базе данных msdb я бы хотел подробней остановиться, к типичным вышеуказанным задачам добавляются специфичные задачи обслуживания msdb.
Как известно, в базе данных msdb хранится история резервных копий по базам данных. Теперь представим сервер, у которого баз данных более 50, каждые 10-15 минут проходит создание резервное копирование файла транзакций, какой объем будет таблиц с данной информацией?
На одном месте работы, когда я только туда пришел, на сервере было более 70 баз данных, серверу было более 2,5 лет и информация по резервному копированию никогда не чистилась, в итоге объем базы данных msdb был более 20 Гб!! А это уже совсем другое время и для создания резервной копии баз данных и для проверки целостности самой базы данных, и лишняя дисковая активность, плюс и время восстановления при аварии, в итоге имеем полно минусов, которые мы можем спокойно решить.
Очистка истории резервного копирования осуществляется через процедуру:
sp_delete_backuphistory [ @oldest_date = ] ‘oldest_date’
[ @oldest_date = ] ‘oldest_date’
Самая ранняя дата, сохраненная в таблицах журнала резервного копирования и восстановления. Аргумент oldest_date имеет тип datetime и не имеет значения по умолчанию
Одну информацию почистили, что еще там хранится?!
Почта. Настроен ли у вас Database Mail и происходит ли отсылка писем, а если еще с вложениями письма?
Вся история по нему так же хранится в базе данных msdb. Для очистки данной истории тоже есть системные процедуры:
sysmail_delete_mailitems_sp [ [ @sent_before = ] ‘sent_before’ ] [ , [ @sent_status = ] ‘sent_status’ ]
где
[ @sent_before = ] ‘sent_before’
Удаляет сообщения электронной почты до даты и времени, указанных аргументом sent_before. Аргумент sent_before имеет тип datetime и не имеет значения по умолчанию. Значение NULL соответствует всем датам.
[ @sent_status = ] ‘sent_status’
Удаляет сообщения электронной почты, тип которых указан аргументом sent_status. Аргумент sent_status имеет тип varchar(8) и не имеет значения по умолчанию. Допустимые значения: sent, unsent, retrying и failed. Значение NULL соответствует всем состояниям.
sysmail_delete_log_sp [ [ @logged_before = ] ‘logged_before’ ] [, [ @event_type = ] ‘event_type’ ]
[ @logged_before = ] ‘logged_before’
Удаляет записи вплоть до даты и времени, указанных в аргументе logged_before. Аргумент logged_before имеет тип datetime и значение по умолчанию NULL. Значение NULL соответствует всем датам.
[ @event_type = ] ‘event_type’
Удаляет журнальные записи определенного типа, заданного аргументом event_type. Аргумент event_type имеет тип varchar(15) и не имеет значения по умолчанию. Допустимые записи: success, warning, error и informational. NULL соответствует всем типам событий.
С почтой мы решили, удалил старую информацию, что еще может быть там?
А есть ли у вас SSIS пакеты и как часто они запускаются? История по их выполнению хранится в таблице ...
Для очистки ее настроена простая инструкция:
FROM .. where starttime<@dt
Где @dt – дата, записи до которой следует удалить.
После этого, выше указанные операции:
— удаление истории резервного копирования
— очистка журнала Database Mail
— очистка таблицы истории ..
Мы заворачиваем в ms sql agent задание и запускаем пару раз в месяц, и в итоге имеем наши компактные системные базы данных:).
Часть данных операций реализуются автоматически во время создания Maintenance Plans, но я их редко использую, поэтому у меня всегда есть скрипт задания обслуживания системных баз данных, который просто разворачиваю на новом сервере при настройке.
Возможно что-то пропустил, так что буду рад комментариям.
Будь аккуратны, держите рабочее место в чистоте!:).
Достаточно нередко у разработчиков клиент-серверных приложений возникает необходимость организовать некий механизм, позволяющий по событию на 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 надо вызвать с клиента из отдельного потока (приоритет потока можно выбрать самый низкий). При получении события, процедуру надо перезапустить. Тайм-аут исполнения запроса надо задать бесконечный.
Ниже будет рассказано, как создать план обслуживания в с помощью программы «Среда SQL Sever Management Studio». В данной статье я просто постараюсь наглядно описать алгоритм создания плана обслуживания с помощью Мастера планов обслуживания, не вдаваясь в теоретическую часть вопроса. Получить больше информации по данной области можно изучив электронную документацию по SQL Server на сайте MSDN .
В описанный ниже план будут входить всего 2 задачи.
- Резервное копирование базы данных.
- Проверка целостности базы данных.
Я надеюсь, что после ознакомления с данной инструкцией, в дальнейшем каждый сможет самостоятельно создать любой требуемый план обслуживания SQL сервера.
1. Исходные данные
- Операционная система семейства Windows (в моем примере используется )
- Установленный Microsoft SQL Server 2008 R2 (об установке SQL Server можно прочитать )
- Существующая база данный в SQL Server (о создании баз данных в SQL Server читайте )
- Настроенная компонента 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) .
Здесь выберем те задачи, которые будет выполнять наш план обслуживания. В моем примере это:
- Проверка целостности базы данных (Check Database Integrity);
- Резервное копирование базы данных (полное) (The Back Up Database (Full));
Заметьте, что для каждой задачи приводится ее краткое описание в поле снизу. Выбрав необходимые задачи, жмем «Далее » (Next) .
Теперь необходимо задать порядок выполнения задач, используя кнопки «Вверх… » (Move Up) и «Вниз… » (Move Down). Установив порядок, жмем «Далее » (Next) .
Здесь требуется задать параметры для каждой задачи в плане. Первая задача в нашем списке это «Копирование БД (полное) » (Back Up Database (Full)).
Прежде всего необходимо выбрать базы данных для резервного копирования, нажав на кнопку выбора списка «Определенные базы данных » (Select one ore more). Выбрав необходимые для резервного копирования базы данных, нажимаем «ОК » .
Ниже зададим размещение и срок хранения резервных копий, а также установим дополнительные параметры:
- Если установить переключатель «Создать файл резервной копии для каждой базы данных » (Create a backup file for every database) , то при выполнении задания в выбранной директории будет создаваться несколько файлов резервных копий с именами, соответствующими названиям баз данных. Ну а установка флага «Создавать вложенный каталог для каждой базы данных » (Create a sub-directory for each database) разложит файлы по отдельным папкам. Обратите внимание, что необходимо оставить заполненным расширение файла резервной копии.
- Установка флага «Срок действия резервного набора данных истекает » (Backup set will expire) указывает SQL-серверу, когда этот набор может быть перезаписан без явного пропуска проверки на истечение срока.
- Для наибольшей надежности, можно установить флаг «Проверять целостность резервной копии » (Verify backup integrity).
- Также рекомендую выбрать режим «Сжимать резервные копии » (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 создан и работает.
Помогла ли Вам данная статья?
