Alexander Gladchenko

MSMQ 3.0 в Кластере

Здесь будет идти речь о кластере Windows Server 2003 Enterprise Edition и о запуске в кластере службы MSMQ. MSMQ, как кластерный ресурс, зависит от двух кластерных ресурсов: сетевого имени и физического диска. Зависимость от сетевого имени позволяет идентифицировать кластерную группу по IP адресу, и позволяет клиентам обращаться к группе как к виртуальному серверу. Физический диск нужен для хранилища сообщений и очередей.

Без кластера, клиенты из сети должны подключаться к серверу по имени или IP адресу, подключаясь к приложению или службе. Служба кластера позволяет создать виртуальные сервера. В дополнение к клиентам MSMQ, работающим со стандартным, не кластерным сервером MSMQ, который обслуживается на узле кластера, написанное для работы в составе кластера приложение MSMQ может связаться с сервером MSMQ, который работает в контексте виртуального сервера. Виртуальный сервер MSMQ, это кластерная группа, состоящая из кластерного ресурса MSMQ и его зависимых ресурсов, о которых уже упоминалось выше. Виртуальный сервер MSMQ не привязан с определенному компьютеру, и в случае отказа он будет перемещён на другой узел, который станет обслуживать клиентов. Очереди создаются на виртуальном сервере, и сообщения можно посылать в очереди виртуального сервера, используя обычный синтаксис VirtualServerName\QueueName.

При создании и настройке MSMQ в кластере, нужно установить MSMQ 3.0 на каждом узле. Кластер должен быть создан до установки MSMQ 3.0 на любом из узлов кластера. MSMQ должен быть установлен до создания кластерного ресурса MSMQ, и набор компонент на каждом узле должен быть одним и тем же.

После установки MSMQ 3.0 на всех узлах кластера можно приступать к созданию кластерного ресурса MSMQ. Можно создавать несколько ресурсов MSMQ, но работа таких ресурсов выходит за рамки поддержки Майкрософт. Поэтому, желательно создавать только один ресурс MSMQ и потом его не переименовывать. После выбора ресурса физического диска в группу MSMQ (он должен быть доступен всем узлам кластера), на этом диске будет создано хранилище очередей, которое желательно создать в папке \msmq\storage. После создания хранилища, местоположение папки хранилища изменять не следует.

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

Если в процессе установки ресурса MSMQ возникнут проблемы, обратитесь к файлу журнала кластера, который обычно расположен по этой ссылке: %systemroot%\Cluster\cluster.log.

Для создания и настройки кластерного ресурса и группы MSMQ в качестве виртуального сервера выполните следующие действия:

1. Нажмите Пуск (Start), выберете пункт Программы (All Programs), пункт Администрирование (Administrative Tools), и затем запустите программу Cluster Administrator.
2. В пункте Open Connection to Cluster нужно выбрать соответствующее имя кластера.
3. В дереве кластерных групп нужно выбрать ту кластерную группу, которой должен принадлежать ресурс MSMQ.
4. После выбора группы, в пункте меню File нужно выбрать New, а затем щелкнуть Resource.
5. В мастере New Resource Wizard заполните поля Name и Description, и выберете Message Queuing в списке Resource type. Потом нажмите Next.
6. В поле Group укажите ту группу, которой Вы хотите, чтобы принадлежал ресурс MSMQ.
7. На закладке возможных владельцев, добавьте те узлы, которые смогут обслуживать ресурс MSMQ.
8. На странице зависимостей (Dependencies в Available resources) добавьте ресурс сетевого имени и физического диска.
9. Нажмите Finish.
10. В дереве кластерных групп нужно выбрать Resources, а затем в окне подробностей выделить ресурс MSMQ.
11. В меню File, выберите Bring Online.
12. В дереве кластерных групп нужно выбрать ту кластерную группу, которой должен принадлежит ресурс MSMQ. Нажать New, а затем щелкнуть Resource, чтобы запустился мастера создания нового ресурса.
13. В текстовом поле Resource Name введите название: "Computer Management".
14. В списке Resource Type, выберите Generic Application, и затем нажмите Next.
15. В поле Group укажите ту группу, которой принадлежит ресурс MSMQ.
16. На закладке Possible Owners, выберите возможных владельцев и нажмите Next.
17. На странице зависимостей добавьте ресурс сетевого имени и ресурс MSMQ, который Вы перед этим создавали. Нажмите Next.
18. В командной строке введите: mmc compmgmt.msc
19. В Current Directory введите: %windir%\system32
20. Нужно выбрать Use Network Name для имени компьютера, и Allow application to interact with desktop. Нажмите Next, а затем Finish.
21. В дереве кластерных групп нужно выбрать Resources, а затем в окне подробностей выделить ресурс Computer Management, который Вы только что создали.
22. В меню File, выберите Bring Online.

Для управление виртуальным сервером, Вы должны подключиться к нулевой сессии, т.е. Console Session (Для подключения через RDP к Session 0 нужно запустить следующую команду: %systemroot%\system32\mstsc.exe /Admin). Также для управления очередями MSMQ в кластерной среде можно использовать утилиту MMCV.exe. Для получения более подробной информации об этой утилите ознакомьтесь со статьёй How to use the Mmcv.exe utility to manage clustered Message Queuing resources. Однако, мне чаще всего хватало тривиальной оснастки mmc, правда для подключения именно к виртуальной очереди MSMQ следует явно подключиться в Computer Management к виртуальному имени, от которого зависит кластерный ресурс MSMQ. После настройки MSMQ для работе в качестве виртуальной службы кластера, не забывайте дать необходимые права на саму виртуальную службу тем учётным записям пользователей, которые будут создавать очереди, а также раздать права к тем очередям, которые будут вами созданы.

Если установка и настройка MSMQ в кластере выполнена правильно, на каждом из узлов кластера должна появиться вторая, дополнительная служба MSMQ. Службы MSMQ локальные для каждого узла не обязательно должны быть запущены. Если Вы не планируете их использование в своих приложениях, можно отменить их автоматический запуск. Для виртуальной службы MSMQ достаточно того, что необходимые компоненты были установлены на каждом из узлов кластера.

Если необходимо, чтобы в случае административного или из-за отказа перемещения ресурсов MSMQ на другой узел, содержимое очереди не очищалось, создавайте очередь транзакционной и обрамляйте отправку сообщений транзакциями (в терминах Windows, а не MSSQL).

Полезные ссылки:

• Очередь сообщений в кластерах серверов
• Очередь сообщений: инструкции
• Администрирование очереди сообщений
• Deploying Message Queuing (MSMQ) 3.0 in a Server Cluster
• MSMQ from the plumber's mate
• MSMQ FAQ document

SQL Server: Методика тестирования дисковой подсистемы

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

1. С помощью поставляемых с дисковым контроллером специализированных утилит, конфигурируем все диски полки как 14 массивов RAID0, каждый из которых должен состоять из одного диска, размер каждого массива выбирается равным всему доступному размеру диска, размер блока низкоуровневой разметки (размер сегмента) выбираем равным 64Кб, политики кэширования должны исключать кэширование чтения и записи. В некоторых контроллерах дисковых массивов выбор размера сегмента может быть ограничен несколькими предопределёнными значениями. Можно встретить рекомендованные для типовых конфигураций значения, например, для баз данных предлагают установить размер блока в 128Кб, а для хранения видеофильмов задать 256Кб. В этих случаях резонно выбрать рекомендованные вендором значения.
2. С помощью оснастки управления дисками, входящей в состав mmc-консоли управления компьютером, и системной утилиты DISKPART необходимо создать для каждого физического диска полки RAW-раздел (без форматирования NTFS) величиной на весь диск, и без присвоения буквы диска (буквы присваивать можно, но это не обязательно, к тому же, букв в алфавите может оказаться меньше числа дисков). Большие диски (более двух Терабайт) может потребоваться предварительно перевести в состояние Online, и конвертировать в GPT (GUID Partition Table). Для выравнивания начального смещения за счёт MBR используйте следующие команды DISKPART:

SELECT DISK=1
CREATE PARTITION PRIMARY ALIGN=128

В этом примере выбран диск 1 и смещение установлено в 128Кб. Выбор смещения зависит от размера сегмента.

1. Установить программу SQLIO. Везде по тексту настоящей статьи местоположение программы sqlio.exe выбрано следующее: C:\SQLIO\ sqlio.exe
2. Подготовьте командный файл, который будет запускать программу sqlio.exe в разных режимах для каждого диска и сохранять результаты в файлы. Пример командного файла можно найти в Приложении 1.
3. Запустите командный файл на исполнении, а потом сведите собранные в файлы результаты в общую таблицу, для дальнейшего сравнения и анализа.

Пример аппаратной конфигурации дисков представлен в Таблице 1.

Таблица 1. Конфигурация дисковых массивов для калибровки дисков.

На Рисунке 2 показано, как должны выглядеть 14 дисков, подготовленные к тестированию.

Рис.2.

Ниже представлен сокращённый пример командного файла для запуска калибровочного теста. В этом файле каждый вызов программы sqlio.exe имеет одинаковые аргументы, кроме аргумента R, который изменяется от 1 до 14. Каждый диск тестируется на чтение и, после 30 секундной паузы, на запись (аргумент k). Время тестирования каждого диска 300 секунд (аргумент s), размер блока 64Кб (аргумент b), Число блоков в строке равно единице (аргумент f), число строк равно 2000000 (аргумент i), число очередей тоже равно единице (аргумент o), число потоков равно единице (аргумент t). Суммарный размер файла рабочей нагрузки на каждом диске составляет 128Гб, что почти совпадает с размером диска.

sqlio -kR -s300 -b64 -f1 -i2000000 -o1 -t1 -R1 -LP -a0xf -BN > R01-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kW -s300 -b64 -f1 -i2000000 -o1 -t1 -R1 -LP -a0xf -BN > W01-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kR -s300 -b64 -f1 -i2000000 -o1 -t1 -R2 -LP -a0xf -BN > R02-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kW -s300 -b64 -f1 -i2000000 -o1 -t1 -R2 -LP -a0xf -BN > W02-b64-f1-i2000000-o1-t1.log

timeout /T 30

……

sqlio -kR -s300 -b64 -f1 -i2000000 -o1 -t1 -R13 -LP -a0xf -BN > R13-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kW -s300 -b64 -f1 -i2000000 -o1 -t1 -R13 -LP -a0xf -BN > W13-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kR -s300 -b64 -f1 -i2000000 -o1 -t1 -R14 -LP -a0xf -BN > R14-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kW -s300 -b64 -f1 -i2000000 -o1 -t1 -R14 -LP -a0xf -BN > W14-b64-f1-i2000000-o1-t1.log

Ниже представлен пример отчёта о тесте операций записи для первого диска:

sqlio v1.5.SG

calibrating timestamp counter for latency timings... done (MHz measured at 2359)

1 thread reading for 300 secs from file 1:

                        using 64KB IOs over 64KB stripes with 2000000 IOs per run

                        enabling multiple I/Os per thread with 1 outstanding

                        buffering set to not use file nor disk caches (as is SQL Server)

                        affinity mask is 0xf (15)

initialization done

CUMULATIVE DATA:

throughput metrics:

IOs/sec:   999.67

MBs/sec:    66.91

latency metrics:

Min_Latency(ms): 0

Avg_Latency(ms): 0

Max_Latency(ms): 802

histogram:

ms: 0  1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+

%: 87 13  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0

В этом отчёте нас интересует метрика скорости чтения, именно эту метрику мы будем сводить в общий график (MBs/sec: 66.91). Метрика IOs/sec практически линейно зависит от MBs/sec, поскольку в каждом из тестов у нас будет фиксированный размер запроса ввода-вывода. Метрики для задержки запроса ввода-вывода (latency) могут быть интересны вкупе с MBs/sec для сравнения разных дисковых подсистем. Нас такое сравнение не интересует, поэтому метрики MBs/sec вполне достаточно.

Автоматизировать извлечение из файлов с результатами тестов необходимых для дальнейших оценок значений показателей производительности можно, например, с помощью PowerShell. В примере ниже показана команда, которая сохраняет в CSV-файле имена файлов и соответствующее им значение MBs/sec:

(Select-String -Pattern MBs/sec: -Path *.log | format-table `

{$_.Filename -replace ".log", ""}, `

{($_.Line).replace(‘.’, ‘,’) -replace "MBs/sec: ", ";"} `

-HideTableHeaders) | Out-File SQLIO.csv -Encoding ASCII –Append

Если результаты сильно выбиваются из общей массы, желательно для такого диска повторить измерения, существенно (например, в два раза) увеличив время теста (аргумент s). На полученные результаты может сильно влиять посторонняя активность на сервере. Старайтесь не подключаться к консоли сервера или посещать его в терминальном режиме, пока не будет закончен очередной пакет тестов. Кроме того, постарайтесь отключить на время тестирования любую другую постороннюю активность на сервере.

На Рисунке 3 показан результат калибровочного тестировании 13 дисков.

Рис. 3.

Наиболее важной задачей нагрузочного тестирования является выявление дисков, характеристики которых существенно отличаются от остальных. На Рисунке 2 диски практически идентичны, как по записи, так и по чтению. Однако, при тестировании большого числа дисков вероятность столкнуться с «подозрительными» дисками много выше. Например, такой пример показан на Рисунке 4. Обратите внимание на четырнадцатый диск.

Рис. 4.

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

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

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

Особо хочется остановиться на вопросах документирования результатов измерений. Обратите внимание на заголовок таблиц каждого из представленных ранее двух результатов. Это строки:

sqlio -kRW -s300 -f1 -i2000000 -o1 -b64 -t1 -LP -R(2-14) -a0xff

sqlio -kRW -s300 -f1 -i2000000 -o1 -b64 -t1 -LP -R(0-13) -a0xff

Мне видится наиболее удобным и практичным именно такой вид описания тестовой конфигурации. Суть состоит в том, чтобы просто скопировать из командного файла строку запуска программы SQLIO и внести изменение в написание изменяемых параметров. В данном случае для параметра –R в скобках был задан диапазон изменения порядковых номеров дисков. В первом случае это диски со второго по четырнадцатый, а во втором случае – это диски с нулевого по тринадцатый. Подобные описатели конфигурации будут встречаться далее в этой статье для демонстрации результатов измерений.

Этап №2. Масштабирование дисков

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

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

Производительность отдельных компонент дисковой подсистемы в данной методике не исследуется, хотя понятно, что абстрагироваться от этого полностью невозможно. В предлагаемой методике мы смотрим на дисковую подсистему, условно, как на "чёрный ящик". Ограничения, а также разного рода ошибки, могут быть совершенно в разных местах. Чтобы начать разговор об этом с администраторами дисковой подсистемы, нужно иметь аргументы. Масштабирование позволяет получить на руки факты именно из этой области. Кроме того, поскольку мы хотим получить в итоге некий эталон производительности, подобные измерения бывают весьма полезны.

В качестве подготовки к этому этапу, нужно убедиться, что сервер и полка подключены друг к другу оптимальным способом и задействованы все особенности подключений, позволяющие повысить производительность передачи данных между дисками и сервером. Достигается это, например, увеличением числа используемых «петель» подключений, выбором оптимальных способов балансирования нагрузки через множество путей ввода-вывода, выбором более производительных интерфейсов, режимом работы и способом привязки дисковых контроллеров и т.д. и т.п. Для получения полной картины возможностей оптимизации тестируемой системы, обратитесь к технической документации вендора.

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

Рис.5.

Для документирования подобного приращения числа дисков я использую сочетание символов «à». Т.о. для обозначения приращения дисков, которое начинается с диска под номером 2 в нумерации операционной системы и заканчивается обоймой из тринадцати дисков по диск номер 14 включительно, можно использовать следующую запись: -R(2->14).

В командном файле это выглядит примерно так:

sqlio -kR -s180 -b64 -f1 -i2000000 -o1 -t1 -R2 -LP -a0xf -BN > R2-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kW -s180 -b64 -f1 -i2000000 -o1 -t1 -R2 -LP -a0xf -BN > W2-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kR -s180 -b64 -f1 -i2000000 -o1 -t1 -R2,3 -LP -a0xf -BN > R23-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kW -s180 -b64 -f1 -i2000000 -o1 -t1 -R2,3 -LP -a0xf -BN > W23-b64-f1-i2000000-o1-t1.log

timeout /T 30

… результаты сокращены…

sqlio -kR -s180 -b64 -f1 -i2000000 -o1 -t1 -R2,3,4,5,6,7,8,9,10,11,12,13 -LP -a0xf -BN > R2345678910111213-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kW -s180 -b64 -f1 -i2000000 -o1 -t1 -R2,3,4,5,6,7,8,9,10,11,12,13 -LP -a0xf -BN > W2345678910111213-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kR -s180 -b64 -f1 -i2000000 -o1 -t1 -R2,3,4,5,6,7,8,9,10,11,12,13,14 -LP -a0xf -BN > R234567891011121314-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kW -s180 -b64 -f1 -i2000000 -o1 -t1 -R2,3,4,5,6,7,8,9,10,11,12,13,14 -LP -a0xf -BN > W234567891011121314-b64-f1-i2000000-o1-t1.log

Получив информацию о том, какова реальная пропускная способность вашей дисковой подсистемы, вы можете спланировать дальнейшие тестовые конфигурации. Выбор тестируемых конфигураций может быть обусловлен вашими бизнес - требованиями к доступности и готовности серверов, а также отводимыми на тестирование сроками (полный цикл тестирования согласно данной методике может занимать порядка 7 суток круглосуточной работы тестового сервера). Зная, какое количество шпинделей потенциально может «запрудить» шину или контроллер, вы можете выбрать, сколько дисков оптимально собирать в один массив. Кроме того, в целях оптимизации времени тестов, данная методика предполагает допущение, что сервер баз данных, как и утилита SQLIO, балансирует нагрузку между дисками лучше, чем контроллер дискового массива. Как вы помните, база данных может состоять из нескольких файлов, и SQL Server будет резервировать в этих файлах пространство под новые страницы пропорционально размеру файлов. Учитывая эту особенность, мы ограничим число создаваемых массивов только некоторыми избранными конфигурациями, которые в лучшей мере используют преимущества балансировки ввода-вывода с помощью SQL Server. Т.е. это будут те конфигурации, которые не противоречат здравому смыслу и предлагают максимальное количество файлов. Ещё одним допущением для данной методики является то, что в каждой из тестовых конфигураций число шпинделей остаётся неизменным. Это нужно для того, чтобы сравнивать производительность разных массивов при равной стоимости оборудования.

Лучше всего рассмотреть вариант подобной тестовой схемы на примере, с графическими изображениями сайзинга дисков в массиве. Поскольку у нас уже собрана самая простая, и, во многих случаях, самая эффективная конфигурация дисков в массивы, начнём с неё. Похожая конфигурация показана на рисунке 6, и условно обозначается noRAID.

Рис.6.

Современные дисковые контроллеры часто не предусматривают возможности конфигурирования отдельных дисков, однако, оставляют возможность создания RAID0 из одного диска.

Следующие конфигурации, показанные на рисунках 7 и 8, представляют RAID1 и RAID0.

Рис.7.

Рис.8.

Конфигурации массивов RAID5 и RAID10 тоже внешне очень похожи, у них будет одинаковое количество логических дисков (LUN).

Рис.9.

Рис.10.

Каждая из показанных на рисунках конфигурация используется для прогона тестов, которые описаны на этапе 4.

Этап №3. Выбор размера сегмента

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

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

Таблица 2. Характеристики для основных типов нагрузки ввода – вывода SQL Server

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

Рис.11.

Многие специалисты рекомендуют устанавливать размер блока для форматирования NTFS раздела операционной системы Windows равным 64 Кб и делать такого же размера размер сегмента при монтировании дискового массива. Глядя на представленный Рисунком 11 график, становится понятно, откуда взялась эта рекомендация.

Этап №4. Сравнение производительности разных типов дисковых массивов

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

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

Каждый уровень RAID будет подвержен пяти тестовым сериям измерения скорости чтения и записи, для разных размеров блока. Размер блока (который задаётся параметром –b) будет принимать следующий набор значений: 1024, 512, 256, 64, 8. Как вы, наверное, заметили, размеры блока соответствуют размерам основных типов запросов ввода-вывода. Выбор размеров блока для тестирования важен, поскольку не стоит включать в тест те размеры запросов ввода – вывода, которые не важны, или не характерны для ваших приложений. Например, если в вашей промышленной среде существуют достаточно продолжительные окна для проведения технологических планов обслуживания баз данных, тогда можно исключить из теста размер блока в 128Кб. Если время создания базы данных тоже не критично (например, если вы используете RAW-партиции), можно убрать тест с размером блока 512Кб.

Кроме изменения размера блока (под блоком будем понимать размер запроса ввода-вывода), данная методика предполагает изменение ещё нескольких, критически важных характеристик рабочей нагрузки. Нам нужно оценить производительность не только для одного запроса, но и в условиях относительно глубоких очередей запросов ввода-вывода, а также распараллеливания этих запросов. Глубина очереди будет увеличиваться до 64. Шаг приращения величины очереди тоже стоит выбирать таким, какой характерен для ваших приложений или, как это будет показано в нашем примере, это могут быть отдельные, характерные для ваших задач значения. Мы будем использовать следующие параметры очереди: о1, о2, о4, о8, о16, о32, о64.

Число потоков у нас будет изменяться от одного до четырёх, ровно столько на тестовом сервере имеется ядер процессора. Синхронно с числом потоков будет изменяться страйп-фактор. Здесь, под числом страйпов (страйп-фактор) понимается число блоков в строке файла нагрузки. Значение переменной основного цикла программы (число страйпов в файле нагрузки) тоже будет меняться, оно вычисляется каждый раз таким образом, чтобы при текущем размере блока и числе блоков в страйпе, размер файла нагрузки был близок к ёмкости диска. Поскольку число вычислений значений параметра i (основной цикл) достаточно велико, лучше заранее подготовить небольшие таблички, охватывающую все выбранные для тестирования варианты параметров и конфигурации. Например, на рисунке 12 представлена подобная таблица.

Рис.12.

Здесь, в ячейке A1 указан размер раздела диска (в данном случае у всех дисков он одинаков). Количество строк файла нагрузки, помещаемого на каждый из задействованных дисков, вычисляется делением размера раздела в Кб на размер блока (b), потом на число потоков (t) блоков в страйпе (f).

Настоящая методика использует несколько эмпирических допущений, которые существенно уменьшают объём выполняемых тестов. Рисунок 12 как раз и демонстрирует одно из таких допущений. Т.е. мы тестируем не все возможные варианты сочетаний параметров запуска SQLIO, а только те, которые перечислены в таблице. Т.е. Значения для параметров t и f могут быть 1,2,4,8,16. Поскольку максимальное значение для параметра t рекомендуется избирать не выше числа ядер процессоров (а в пример на рисунке 12 их число было 16). Значение f не должно превышать значение t, диапазон изменения этих параметров будет именно такой, как вы видите на рисунке. В колонке A, начиная со строки 2, заданы размеры запросов ввода вывода, которые характерны для приложения. Эти значения используются для параметра b. В таблице не хватает только одного из определяющих конфигурацию каждого теста параметра, это глубина очереди, задающаяся параметром o. Как и в нашем случае, в примере на рисунке 12 использовались следующие параметры очереди: о1, о2, о4, о8, о16, о32, о64.

Документирование конфигурации проще показать и подробно разобрать на следующем примере:

f(1,2,4) -i(1950,975,487) -o(1-64) -b1024 -t(1,2,4) -LP -R2,3,4 -a0xf

Здесь, размер блока был установлен в 1024Кб. Число блоков в страйпе файла нагрузки изменялось синхронно с числом потоков, т.е. значения были равны, и составляли: 1, 2 и 4. В зависимости от последнего числа изменяется число страйпов файла рабочей нагрузки. В этом тесте для мегабайтного размера блока значения такие: i1950, i975, i487. Глубина очереди изменялась так, как я показывал ранее, от единицы до 64.

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

Таблица 3. RAID1:

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

Таблица 4. RAID1: f(1,2,4) -i(3900,1900,970) -o(1-64) -b512 -t(1,2,4) -LP -R2,3,4 -a0xf

 Таблица 5. RAID1: f(1,2,4) -i(500000,250000,125000) -o(1-64) -b256 -t(1,2,4) -LP -R2,3,4 -a0xf

 Таблица 6. RAID1: f(1,2,4) -i(2000000,1000000,500000) -o(1-64) -b64 -t(1,2,4) -LP -R2,3,4 -a0xf

 Таблица 7. RAID1: f(1,2,4) -i(16000000,8000000,4000000) -o(1-64) -b8 -t(1,2,4) -LP -R2,3,4 -a0xf

По каждому из пяти режимов тестирования мы считаем среднее значение. Моя практика показывает, что такой простой формулы оказывается вполне достаточно, однако, это не исключает таких конфигураций, когда потребуется более взвешенный выбор статистических методов. Для каждого из тестируемых уровней RAID средние значения сводятся в общую таблицу, по которой уже можно строить график, удобный для сравнения эффективности разных уровней RAID в используемой тестовой среде.

На Рисунке 13 показан график средних значений производительности троек массивов для разных уровней RAID. Разными цветами обозначены размеры блоков. Шкала показывает производительность в Мб/сек.

Рис.13.

Этот график отражает эффективность работы разных уровней RAID на нашей аппаратной платформе и для тех режимов рабочей нагрузки, которые характерны приложениям заказчика (это аналитические запросы).

 При выборе другого оборудования или в условиях другого характера нагрузки, например, OLTP, график вполне может стать другим. На Рисунке 14 показан график для другой аппаратной платформы, у которой можно было создавать массивы RAID10.

Рис.14.

На рисунках 14 и 15 показаны результаты для варианта прямого подключения дисковой подсистемы. На рисунке 16 показаны результаты тестирования дисковой подсистемы и сервера  другого вендора, и в варианте подключения SAN.

Рис.15.

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

Выводы

Сегодня типичные объёмы обслуживаемых СУБД данных настолько велики, что для дисковых подсистем уже недостаточно нескольких дисков, всё чаще, типовое приложение оперирует дисковой полкой в 14 или 15 дисков, а объёмы дисковых массивов достигают Терабайта. В таких условиях задачи сайзинга становятся очень важны, и грамотное распределение файлов вкупе с правильным выбором дисковых массивов позволяют существенно повысить производительность приложений, а также добиться требуемых значений производительности без лишних издержек энергопотребления.

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

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

Приложение 1. Пример командного файла.

SQLIO_noRAID.zip

Благодарности

Хочу выразить благодарность Ирине Наумовой, которая предложила более изящное решение по замене точки в сценарии PowerShell.

Tips for DBA: Log Flush Performance

Tips for DBA: Percentage of work completed for DBA commands

Tips for DBA: Logical Disk FreeSpace Notification

Tips for DBA: Table operational/physical stats

Недокументированная процедура sp_tablecollations_100

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

Tips for DBA: INSERT Overclocking

Tips for DBA: Signal Waits vs. Resource Waits

USE master
GO
--DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
--GO
WITH ByWaitTypes([Тип ожидания], [ожидания сигнала %], [ожидания ресурса %], [ожидания ms]) AS
(
SELECT TOP 20 wait_type
   , cast(100.0 * sum(signal_wait_time_ms)/sum(wait_time_ms) AS NUMERIC (20,2))
   , cast(100.0 * sum(wait_time_ms - signal_wait_time_ms)/sum(wait_time_ms) AS NUMERIC(20,2))
   , sum(wait_time_ms)
FROM sys.dm_os_wait_stats
WHERE wait_time_ms <> 0
GROUP BY wait_type
ORDER BY sum(wait_time_ms) DESC
)
SELECT TOP 1 'Тип ожидания' = N'BCE!'
   , 'ожидания сигнала %' = (SELECT cast(100.0 * sum(signal_wait_time_ms)/
    sum (wait_time_ms) AS NUMERIC (20,2)) FROM sys.dm_os_wait_stats)
   , 'ожидания ресурса %' =(SELECT cast(100.0 * sum(wait_time_ms - signal_wait_time_ms)/
    sum(wait_time_ms) AS NUMERIC(20,2)) FROM sys.dm_os_wait_stats)
   , 'ожидания ms' =(SELECT sum(wait_time_ms) FROM sys.dm_os_wait_stats)
FROM sys.dm_os_wait_stats
UNION
SELECT [Тип ожидания], [ожидания сигнала %], [ожидания ресурса %], [ожидания ms]
FROM ByWaitTypes
ORDER BY 'ожидания ms' DESC

Один из моих подопечных серверов вернул следующие результаты:

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

В книге утверждается что, как правило, системы, имеющие низкий показатель ожидания сигнал/ресурс демонстрируют лучшую общую производительность. Так ли это, я надеюсь, обсудить вместе с вами, поскольку мои наблюдения говорят о том, сто слишком низкие показатели тоже говорят о проблемах (например, ввода-вывода). А как обстоит дело на ваших серверах?

Tips for DBA: Store Performance Counters in Database (Job-Step: Power Shell)

Остались в прошлом те времена, когда средствами SQL Server можно было получить состояния счётчиков производительности, которые относились только к самому SQL Server. Для этого традиционно использовались системные представления sys.sysperfinfo и sys.dm_os_performance_counters.

С помощью Power Shell и нового в SQL Server 2008 типа шага в заданиях по расписанию, который позволяет запускать под управлением SQL Server Agent сценарии  Power Shell, теперь можно получить абсолютно любые счётчики производительности. Причём, сделать это можно как для локального, так и удалённого в сети сервера. А получение сведений о счётчиках посредством WMI избавляет от необходимости агрегации сырых значений, что делает этот метод простым и понятным.

Ниже представлен макет сценария, который создаёт администраторскую базу данных и в ней таблицу для хранения данных о двух счётчиках производительности: PercentDiskTime и PercentProcessorTime. Данные в эту таблицу поставляет создаваемое в сценарии задание по расписанию, в единственном шаге которого запускается сценарий Power Shel. Это сценарий подключается к указанному серверу, посредством WMI получает значения заданных счётчиков и записывает эти значения в созданную ранее таблицу.

Чтобы протестировать этот метод, в примере сценария замените имена серверов и учётных записей на те, которые действительны в вашей тестовой среде.

USE [master]

GO

CREATE DATABASE [_DBA_]

GO

ALTER DATABASE [_DBA_] SET COMPATIBILITY_LEVEL = 100

GO

USE [_DBA_]

GO

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING OFF

GO

CREATE TABLE [dbo].[HostUtilization](

      [countertime] [datetime] NOT NULL,

      [PercentDiskTime] [tinyint] NOT NULL,

      [PercentProcessorTime] [tinyint] NOT NULL,

      [Host] [varchar](30) NOT NULL,

 CONSTRAINT [PK_HostUtilization] PRIMARY KEY CLUSTERED

(

      [Host] ASC,

      [countertime] ASC

))

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[HostUtilization]

ADD  CONSTRAINT [DF_HostUtilization_countertime] 

DEFAULT (getdate()) FOR [countertime]

GO

ALTER TABLE [dbo].[HostUtilization]

ADD  CONSTRAINT [DF_HostUtilization_PercentDiskTime] 

DEFAULT ((100)) FOR [PercentDiskTime]

GO

ALTER TABLE [dbo].[HostUtilization]

ADD  CONSTRAINT [DF_HostUtilization_PercentProcessorTime] 

DEFAULT ((100)) FOR [PercentProcessorTime]

GO

ALTER TABLE [dbo].[HostUtilization]

ADD  CONSTRAINT [DF_HostUtilization_Host] 

DEFAULT ('.') FOR [Host]

GO

USE [msdb]

GO

DECLARE @jobId BINARY(16)

EXEC msdb.dbo.sp_add_job

     @job_name=N'HostUtilization',

     @enabled=1,

     @notify_level_eventlog=2,

     @notify_level_email=0,

     @notify_level_netsend=0,

     @notify_level_page=0,

     @delete_level=0,

     @description=N'Monitoring server name: MICROSOFT'

     @category_name=N'[Uncategorized (Local)]',

     @owner_login_name=N'RU\AGladchenko',

     @job_id = @jobId OUTPUT

EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'1',

     @step_id=1,

     @cmdexec_success_code=0,

     @on_success_action=3,

     @on_success_step_id=0,

     @on_fail_action=3,

     @on_fail_step_id=0,

     @retry_attempts=0,

     @retry_interval=0,

     @os_run_priority=0,

     @subsystem=N'PowerShell',

     @command=N'$RemoteHost = "MICROSOFT"

$PercentDiskTime=(gwmi Win32_PerfFormattedData_PerfDisk_PhysicalDisk -computerName $RemoteHost -filter "name = ''_Total''").PercentDiskTime

$PercentProcessorTime=(gwmi Win32_PerfFormattedData_PerfOS_Processor -computerName $RemoteHost -filter "name = ''_Total''").PercentProcessorTime

$conn = new-object system.data.oledb.oledbconnection

$connstring = "provider=sqloledb;data source=MYSUPERPUPERSERVERNAME;initial catalog=_DBA_;integrated security=SSPI"

$conn.connectionstring = $connstring

$conn.open()

$RemoteHost = "''"+$RemoteHost+"''"

$sqlquery = "INSERT INTO [_DBA_].[dbo].[HostUtilization] ([countertime],[PercentDiskTime],[PercentProcessorTime],[Host] ) VALUES (DEFAULT, $PercentDiskTime, $PercentProcessorTime, $RemoteHost)"

$cmd = New-Object system.data.oledb.oledbcommand

$cmd.connection = $conn

$cmd.commandtext = $sqlquery

$cmd.executenonquery() > NULL

$conn.close()',

     @database_name=N'master',

     @flags=32

EXEC msdb.dbo.sp_update_job

     @job_id = @jobId,

     @start_step_id = 1

EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'1',

     @enabled=1,

     @freq_type=4,

     @freq_interval=1,

     @freq_subday_type=2,

     @freq_subday_interval=30,

     @freq_relative_interval=0,

     @freq_recurrence_factor=0,

     @active_start_date=20090206,

     @active_end_date=99991231,

     @active_start_time=0,

     @active_end_time=235959,

     @schedule_uid=N'1b20bdf0-4627-4d31-8036-d85ff30cd664'

EXEC msdb.dbo.sp_add_jobserver

     @job_id = @jobId,

     @server_name = N'(local)'

Tips for DBA: SQL Server Inventory (PowerShell)

Tips for DBA: The Spy for stored procedures

Tips for DBA: How to trace Deprecated Features

Если необходимо отследить статистику использования отключаемого в будущих версиях функционала SQL Server 2008, можно воспользоваться представленным ниже сценарием. За основу этого сценария взятя информация о статистике значений счётчиков производительности, извлекаемая из административного динамического представления sys.dm_os_performance_counters

Типовой сценарий настройки обслуживания новой установки SQL Server 2005

USE master
GO

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE 
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'MS-SQL-Admins', 
		@enabled=1, 
		@weekday_pager_start_time=0, 
		@weekday_pager_end_time=235900, 
		@saturday_pager_start_time=0, 
		@saturday_pager_end_time=235900, 
		@sunday_pager_start_time=0, 
		@sunday_pager_end_time=235900, 
		@pager_days=127, 
		@email_address=N'MS-SQL-Admins@domain.ru', 
		@pager_address=N'MS-SQL-Admins@domain.ru', 
		@category_name=N'[Uncategorized]', 
		@netsend_address=N'servermonitoringa'
GO

EXEC msdb.dbo.sp_add_alert @name=N'Access denied', 
		@message_id=10011, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'Access denied', 
		@operator_name=N'MS-SQL-Admins', 
		@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Access is denied due to a password failure', 
		@message_id=3279, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Access is denied due to a password failure', 
		@operator_name=N'MS-SQL-Admins', 
		@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Login fails', 
		@message_id=4060, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Login fails', 
		@operator_name=N'MS-SQL-Admins', 
		@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Permission Denied', 
		@message_id=229, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Permission Denied', 
		@operator_name=N'MS-SQL-Admins', 
		@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Server shut down', 
		@message_id=6006, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1,
		@database_name=N'', 
		@notification_message=N'Остановлена служба', 
		@event_description_keyword=N'Server shut down', 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Server shut down', 
		@operator_name=N'MS-SQL-Admins', 
		@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Переполнен журнал транзакций', 
		@message_id=9002, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=5, 
		@notification_message=N' ', 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Переполнен журнал транзакций', 
		@operator_name=N'MS-SQL-Admins', 
		@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev. 19 Errors', 
		@message_id=0, 
		@severity=19, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@notification_message=N' ', 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev. 19 Errors', 
		@operator_name=N'MS-SQL-Admins', 
		@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev. 20 Errors', 
		@message_id=0, 
		@severity=20, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@notification_message=N' ', 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev. 20 Errors', 
		@operator_name=N'MS-SQL-Admins', 
		@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev. 21 Errors', 
		@message_id=0, 
		@severity=21, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@notification_message=N' ', 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev. 21 Errors', 
		@operator_name=N'MS-SQL-Admins', 
		@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev. 22 Errors', 
		@message_id=0, 
		@severity=22, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@notification_message=N' ', 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev. 22 Errors', 
		@operator_name=N'MS-SQL-Admins', 
		@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev. 23 Errors', 
		@message_id=0, 
		@severity=23, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@notification_message=N' ', 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev. 23 Errors', 
		@operator_name=N'MS-SQL-Admins', 
		@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev. 24 Errors', 
		@message_id=0, 
		@severity=24, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@notification_message=N' ', 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev. 24 Errors', 
		@operator_name=N'MS-SQL-Admins', 
		@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev. 25 Errors', 
		@message_id=0, 
		@severity=25, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@notification_message=N' ', 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev. 25 Errors', 
		@operator_name=N'MS-SQL-Admins', 
		@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'tempdb: full used space', 
		@message_id=0, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=60, 
		@include_event_description_in=5, 
		@category_name=N'[Uncategorized]', 
		@performance_condition=N'SQLServer:Databases|Log File(s) Used Size (KB)|tempdb|>|9000000', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'tempdb: full used space', 
		@operator_name=N'MS-SQL-Admins', 
		@notification_method = 1;
GO

USE master
GO

-- Компонент Database Mail
EXECUTE sp_configure 'show advanced options',1
RECONFIGURE
EXECUTE sp_configure 'Database Mail XPs',1
RECONFIGURE
EXEC sp_configure 'default trace enabled', 0
RECONFIGURE
EXECUTE sp_configure 'show advanced options',0
RECONFIGURE
GO
DECLARE @email_address nvarchar(50)
SELECT	@email_address = @@servername + '-MSSQL@domain.ru'
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'Administrator',
    @description = 'Mail account for administrative e-mail.',
	@replyto_address = 'MS-SQL-Admins@domain.ru',
    @email_address = @email_address,
    @display_name = @email_address,
    @mailserver_name = 'SMTP.domain.ru',
	@mailserver_type = 'SMTP',
	@port = 25,
	@use_default_credentials = 0;
GO
EXECUTE msdb.dbo.sysmail_add_profile_sp
       @profile_name = 'AlertProfile',
       @description = 'Profile used for administrative mail.' ;
GO
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'AlertProfile',
    @account_name = 'Administrator',
    @sequence_number = 1 ;
GO
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @principal_name = 'public',
    @profile_name = 'AlertProfile',
    @is_default = 1 ;
GO


USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1
GO
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
, N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
, N'UseDatabaseMail'
, N'REG_DWORD', 1
GO
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
, N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
, N'DatabaseMailProfile'
, N'REG_SZ'
, N'AlertProfile'
GO

EXECUTE msdb.sys.sp_helprolemember 'DatabaseMailUserRole';
--EXECUTE sp_addrolemember @rolename = 'DatabaseMailUserRole',@membername = '.......';
EXECUTE msdb.dbo.sysmail_help_queue_sp @queue_type = 'Mail';
EXECUTE msdb.dbo.sysmail_help_account_sp;
EXECUTE msdb.dbo.sysmail_help_profile_sp;
EXECUTE msdb.dbo.sysmail_help_profileaccount_sp @profile_name = 'AlertProfile'
EXECUTE msdb.dbo.sysmail_help_principalprofile_sp;
SELECT * FROM msdb.dbo.sysmail_event_log
SELECT * FROM msdb.dbo.sysmail_allitems
SELECT * FROM msdb.dbo.sysmail_faileditems
GO

USE master
GO

CREATE ENDPOINT SBEndpoint
  STATE = STARTED
  AS TCP 
    (
       LISTENER_IP = ALL, 
       LISTENER_PORT = 9669
    )
  FOR SERVICE_BROKER 
    (
       AUTHENTICATION = WINDOWS,
       MESSAGE_FORWARDING = DISABLED
    )
GO
CREATE ENDPOINT MirroringEndpoint
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 7022, LISTENER_IP = ALL )
    FOR DATABASE_MIRRORING (
       AUTHENTICATION = WINDOWS KERBEROS,
       ENCRYPTION = SUPPORTED,
       ROLE=ALL);
GO


-- Открытие нового файла журнала ошибок SQL Server

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
	N'Software\Microsoft\MSSQLServer\MSSQLServer', 
	N'NumErrorLogs', 
	REG_DWORD, 21
GO
USE [msdb]
GO
DECLARE @jobId BINARY(16), @ReturnCode int
EXEC	@ReturnCode =  msdb.dbo.sp_add_job 
		@job_name=N'Открытие нового файла журнала ошибок SQL Server', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'--USE msdb ;
--GO
--EXEC dbo.sp_cycle_agent_errorlog ;
--GO
--EXEC sp_cycle_errorlog ;
--GO', 
		@category_name=N'Database Maintenance', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT

EXEC	@ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, 
		@step_name=N'sp_cycle_errorlog', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'EXEC sp_cycle_errorlog', 
		@database_name=N'master', 
		@flags=0
EXEC	@ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, 
		@start_step_id = 1
EXEC	@ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, 
		@name=N'sp_cycle_errorlog', 
		@enabled=1, 
		@freq_type=32, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=1, 
		@freq_recurrence_factor=1, 
		@active_start_date=20080422, 
		@active_end_date=99991231, 
		@active_start_time=235900, 
		@active_end_time=235959
EXEC	@ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, 
		@server_name = N'(local)'
GO

--

EXEC master..xp_regwrite 
     @rootkey='HKEY_LOCAL_MACHINE', 
     @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer', 
     @value_name='BackupDirectory', 
     @type='REG_SZ', 
     @value='D:\MSSQL\BACKUP' -- Тут нужно указать правильный путь к папке, куда решили класть копии


-- Шаблон задания для автоматического создания недостающих индексов

USE [msdb]
GO

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories 
WHERE name=N'[Обслуживание индексов]' AND category_class=1)
BEGIN
EXEC	msdb.dbo.sp_add_category @class=N'JOB', 
		@type=N'LOCAL', 
		@name=N'[Обслуживание индексов]'
END

DECLARE @jobId BINARY(16)
EXEC	msdb.dbo.sp_add_job @job_name=N'Создание недостающих индексов', 
		@enabled=0, 
		@notify_level_eventlog=3, 
		@notify_level_email=2, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Обслуживание индексов]', 
		@owner_login_name=N'sa', 
		@notify_email_operator_name=N'MS-SQL-Admins', 
		@job_id = @jobId OUTPUT
EXEC	msdb.db