Alexander Gladchenko

Обновите свои новостные каналы лентами из SQL Server BLOGROLL 2010

gladchenko — Tue, 02 Feb 2010 12:02:00 GMT

Продолжаю традицию в начале года верстать очередной BlogROLL. За основу этого списка новостных лент берётся мой актуализированный OPML, т.е. счастливым обладателям IE7 и выше достаточно просто импортировать его каналы. Как это сделать уже было подробно и наглядно описано в моём блоге. Тех же, кто предпочитает список блогов, я пригашаю перейти по этой ссылке на страницу, где я сгруппировал блоги по нескольким тематическим направлениям: SQL Server BLOGROLL 2010

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

Высокая доступность в репликации SQL Server 2008 с зеркалированием и доставкой журналов

gladchenko — Fri, 18 Dec 2009 14:39:00 GMT

Под общей редакцией А. Гладченко и В. Щербинина.

Москва. ЭКОМ Паблишерз, 2008Г. 288 страниц. ISBN: 978-5-9790-0086-2. Книга уже в продаже.

Эта книга - сборник статей, которые посвящены ключевым для понимания репликации SQL Server моментам. Кроме переводов наиболее интересных зарубежных авторов, являющимисяхся признанными во всём мире специалистами, в книге вы найдёте ряд статей участников сообщества SQL.RU, которые многие годы помогаю решать разнообразные проблемы на технических форумах этого замечательного интернет - ресурса. Если во время прочтения книги у вас возникнут сомнения или вопросы по поводу изложенных тут материалов, смело обращайтесь к авторам статей на форумах SQL.RU, они с удовольствием вам помогут.
В сборник включены переводы и статьи следующих участников сообщества SQL.RU: Дмитрий Артёмов, Александр Волок, Александр Гладченко, Ильдар Даутов, Григорий Кoрнилов, Алексей Ковалёв, Наталья Кривонос, Ян Либерман, Ирина Наумова и Владислав Щербинин. Причём, на момент написания книги, Александр Гладченко, Ян Либерман и Ирина Наумова являлись SQL Server MVP.
Книга в первую очередь ориентирована на администраторов баз данных, которые собираются углубить свои познания в репликации SQL Server. Назначение этой книги состоит в том, чтобы предоставить читателям набор апробированных в течение нескольких лет рецептов по использованию и настройке репликации в SQL Server.

ЗАКАЗАТЬ

Обсудить книгу можно тут: http://www.sql.ru/forum/actualthread.aspx?tid=643700

Пример статьи из сборника:

Высокая доступность в репликации SQL Server 2008 с зеркалированием и доставкой журналов

Автор Александр Юрьевич Гладченко

Статья написана по мотивам технического документа Майкрософт: "SQL Server Replication: Providing High Availability using Database Mirroring" и описания в электронной документации SQL Server 2008 Books Online (далее BOL): "Репликация и зеркальное отображение базы данных".
В этой статье мы рассмотрим новые возможности обеспечения высокой доступности тиражируемых данных, используя для этого Репликацию транзакций, Доставку журналов и зеркальные копии баз данных.
Основанная на Репликации транзакций распределённая система хранения данных может обеспечить высокую устойчивость к отказам серверов баз данных. Подобные решения позволяют достичь высокой степени доступности, за счёт поддержки избыточных копий данных. Кроме Репликации, избыточность на уровне баз данных способны обеспечить несколько механизмов SQL Server 2008. Это такие возможности, как резервное копирование с последующим восстановлением, Доставка журналов и Зеркальное отображение базы данных. Причём, Зеркальное отображение является единственным механизмом, который поддерживает точную копию защищаемой базы данных практически в реальном масштабе времени, и гарантирует отсутствие потерь данных.
В этой статье на примерах мы посмотрим, как можно использовать Зеркальное отображение реплицируемой базы данных для повышения её доступности. Мы рассмотрим как Репликация и Зеркальное отображение влияют друг на друга, а также, как Зеркальное отображение совместимо с Доставкой журналов и как Доставка журналов совместима с Репликацией. Кроме того, в этой статье мы коснёмся возможностей использования для первоначальной синхронизации баз данных механизмов Доставки журналов, и вкратце рассмотрим принципы работы инициализации подписчика, основанной на логических номерах виртуальных журналов (LSN), которая позволяет сократить время восстановления после отказа при наличии зеркальной копии базы данных Подписчика.

Совместимость Доставки журналов с Зеркальным отображением и Репликацией

В настоящей статье мы не ставим целью полностью раскрыть тему совмещения Доставки журналов с Зеркальным отображением баз данных и Репликацией. Эта тема достаточно подробно раскрыта в BOL. Здесь мы рассмотрим только те аспекты Доставки журналов, которые нам пригодятся для первоначальной синхронизации основной базы данных Зеркального отображения и баз данных Подписчиков в топологии репликации. Коротко будет сказано и о том, как можно использовать Доставку журналов в целях резервного копирования участвующих в Репликации баз данных.
Целью Доставки журналов SQL Server 2008 является обеспечение автоматической синхронизации баз данных, которая осуществляется путём резервного копирования журналов транзакций в базе данных Источника, и последующей доставке и восстановление копий журнала транзакций в базе данных Получателя. Сервер, который обслуживает базу данных Получателя, выступает в роли резервного сервера или сервера отчётов. Сервер отчётов предоставляет возможность обработки запросов пользователей на чтение данных. Одна база данных Источника может синхронизироваться с одной или с несколькими базами данных Получателей. Доставка журналов применима к тем базам данных, которые используют полную модель восстановления или модель восстановления с неполным протоколированием.
Давайте договоримся, что сервер, первоначально обслуживающий базу данных Источника, будем называть сервером источника (термин "основной сервер" был бы удобнее, но он используется в Зеркальном отображении, и мы его не станем использовать, чтобы не было путаницы), а сервер, первоначально обслуживающий базу данных Получателя, называть резервным сервером. После настройки, сервер источника обслуживает базу данных Источника, но может сменить роль и обслуживать базу данных Получателя. Изменение роли приводит к одновременному изменению настроек на всех участвующих в Доставке журналов серверах, поскольку сервер источника может быть только один.
В Доставке журналов предусмотрены средства мониторинга и оповещения обо всех штатных и нештатных состояниях участников процесса Доставки журналов. Начиная с SQL Server 2008, поддерживается сжатие резервных копий, и такие копии можно использовать для Доставки журналов. Копии журналов создаются и доставляются с помощью заданий автоматизации службы SQL Server Agent, всего таких заданий четыре: задание резервного копирования Источника, задание копирования файлов Получателю, задание восстановления копий на Получателе и задание рассылки предупреждений.
Формат хранения данных на диске для SQL Server x64 и x86 одинаков, т.е. в Доставке журналов могут участвовать сервера баз данных под управлением 32-х и 64-х разрядных операционных систем. Доставка журналов поддерживается следующими редакциями SQL Server 2005: Enterprise Edition, Standard Edition и Workgroup Edition. Серверы, задействованные в доставке журналов, должны иметь одинаковые параметры сортировки, а базы данных источника и получателей могут использовать только модель полного восстановления (Full) или модель восстановления с неполным протоколированием (Bulk Logged).
Доставка журналов может быть средством тиражирования данных, однако, в этой статье мы рассмотрим возможность использования Доставки журналов для первоначальной синхронизации баз данных в Зеркальном отображении и Репликации.
Из BOL мы знаем, что Доставку журналов допустимо совмещать с Зеркальным отображением. В таком случае база данных Источника должна являться основной базой данных Зеркального отображения. Причём, Зеркальное отображение может быть настроено в любом из трёх режимов его работы. Базы данных Получателей не должны находиться на том же экземпляре сервера, что и база источника или на экземпляре Зеркального отображения базы Источника. Поскольку Зеркальное отображение позволяет делать резервные копии только базы данных основного сервера, использование в качестве базы данных Источника зеркальной копии основной базы невозможно. Настраивать Доставку журналов и Зеркальное отображение на основном сервере можно в любом порядке.
Допустимо совмещать Доставку журналов с Репликацией, и это справедливо для издаваемой базы данных, баз данных Подписок, и для базы данных Распространителя. Следует учитывать, что процесс репликации будет прерван в случае перехода на резервный сервер, поскольку агенты репликации не умеют правильно реагировать на изменение ролей серверов в Доставке журналов, и это приведёт к тому, что транзакции перестанут тиражироваться подписчикам. После того, как роль базы данных Источника вернётся в первоначальное состояние, репликация возобновляется, и все те транзакции, которые были скопированы Доставкой журналов с резервного сервера на сервер источник, реплицируются подписчикам. В случае безвозвратной потери базы данных Источника, достаточно переименовать Получателя, чтобы возобновить процесс репликации.
Первоначальная инициализация базы данных Получателя в процессе настройки Доставки журналов осуществляется путём восстановления полной резервной копии базы данных источника с параметрами NORECOVERY или STANDBY. Для организации доставки журналов необходимо создать сетевой ресурс, в котором будут создаваться резервные копии журналов транзакций и который будет доступен всем участвующим в Доставке журналов серверам. В нашем примере в качестве такого ресурса будет использоваться каталог сервера источника C:\MSSQL\LogShip\.
Существует возможность синхронизировать процесс доставки журналов с тиражированием данных в репликации транзакций. Такая синхронизация устанавливается с помощью системной хранимой процедуры sp_replicationdboption, которая позволяет для публикуемой базы данных и базы данных Распространителя установить опцию "sync with backup". Когда эта опция установлена для базы данных Распространителя, это гарантирует, что транзакции в журнале публикуемой базы данных не будут усечены до тех пор, пока не будет создана их резервная копия в базе данных Распространителя. Усечение журнала транзакций публикуемой базы данных откладывается до завершения резервного копирования усекаемых транзакций в базе данных Распространителя. Установка этой опции позволяет управлять точкой усечения базы данных Публикации. Новое значение вступает в силу после очередного запуска Агента чтения журнала или по истечении заданного параметром агента -MessageInterval интервала времени, если Агент чтения журнала работает в непрерывном режиме. За счёт того, что в базе данных распространителя не будет транзакций, которых нет в резервной копии публикуемой базы данных, можно не боятся рассогласованности издаваемой базы данных и базы данных Распространителя в случае восстановления издаваемой базы данных из резервной копии. Однако, следует помнить, что такой способ синхронизации добавляет задержку в процесс тиражирования транзакций и не гарантирует отсутствия потерь данных, в случае повреждения журнала транзакций. Кроме того, для Доставки журналов опция важна только для издаваемой базы данных.
В рамках этой статьи мы не будем рассматривать совместную работу Доставки журналов и Репликации. Доставка журналов нами будет использоваться только для целей первоначальной синхронизации баз данных подписчиков и Зеркального отображения базы данных Издателя. Поэтому сценарии включения Доставки журналов будут использоваться в примерах с демонстрацией включения Зеркального отображения и Репликации.
Дополнительную информацию о Доставке журналов можно получить в следующих статьях BOL:

Развертывание доставки журналов
Репликация и доставка журналов
Зеркальное отображение баз данных и доставка журналов
Резервное копирование и восстановление из копий реплицируемых баз данных
Стратегии резервного копирования и восстановления из копии репликации моментальных снимков и репликации транзакций

Не все базы данных в топологии Одноранговой репликации транзакций допускают Зеркальное отображение (зеркалирование). Например, это невозможно для базы данных Распространителя. В Репликации транзакций зеркалирование базы данных подписчика требует учёта некоторых ограничений, которых нет для издаваемой базы или в одноранговой топологии. Возможность использования в репликации зеркалирования зависит от того, насколько будут способны задействованные агенты репликации отрабатывать состояние отказа основной базы данных и автоматически переключаться на зеркальную копию. Так, например, ни один из соответствующих агентов репликации не может правильно отреагировать на отказ базы данных Распространителя и переключиться на работу с зеркальной копией этой базы данных. Для обеспечения высокой доступности Распространителя его следует вынести на выделенный компьютер и отдать его под управление отказоустойчивого кластера. Однако, те агенты репликации, которые соединяются с публикуемой базой данных, умеют переключаться на зеркальную копию, и в случае отказа способны подключиться автоматически, обеспечив непрерывность процесса репликации.
С подписчиками всё не так просто, как с издателем. К сожалению, ни один из работающих с базами на подписчике агентов репликации не предназначен для автоматического переключения в случае отказа. Самым естественным путём переключения потока данных репликации является удаление подписки и создание её заново.
Дополнительные сведения можно получить в следующей статье BOL: "Репликация и зеркальное отображение базы данных".

Влияние зеркалирования на работу Агента чтения журнала

Зеркалирование публикуемой базы данных влияет на поведение Агента чтения журнала, его состояние становится зависимым от состояния зеркалирования. Агент чтения журнала зеркалируемой базы данных будет копировать из журнала вначале записи тех транзакций, которые были до этого скопированы и завершены в журнале регистрации транзакций зеркальной базы данных (процесс, с помощью которого пишутся записи в журнал транзакций зеркальной базы данных, в документации называют hardening - закрепление). Т.е. реплицироваться будут только записи с таким LSN, который больше LSN последней закреплённой в журнале транзакций зеркала.
Это позволяет выставить из топологии основной сервер (зеркало доступно, но существуют такие записи в журнале транзакций, которые еще не были закреплены на зеркале) или изолировать его (когда зеркало недоступно). В обоих случаях, пока основной сервер работоспособен и его база данных доступна, любые изменения в его базе данных не будут реплицированы, пока соответствующие записи журнала транзакций не будут закреплены на зеркале.
Такое поведение добавляет задержки в поток репликации, и если произойдёт отказ зеркалирования, будет гарантировано, что записи в журнале Подписчика не обгонят фиксацию в основной базе данных.
Ели Агент чтения журнала вынужден ждать закрепления записей в журнале транзакций зеркальной базы данных, в хронологии работы Агента чтения журнала будут появляться уведомления следующего вида: "Replicated transactions are waiting for next Log backup or for mirroring partner to catch up".

Изменение поведения Агента чтения журнала при установке флага трассировки 1448

В редких случаях, эффект задержек из-за зеркалирования для логики работы использующих репликацию приложений может оказаться не приемлем. Для решения этой проблемы добавлен новый флаг трассировки 1448, который предписывает репликации продолжаться даже в тех случаях, когда основная база данных выставлена или изолирована. Обычно, Агента чтения журнала ждет, пока не будут закреплены записи в журнале регистрации транзакций зеркальной базы данных, после чего он копирует их в базу данных Распространителя. Когда сервер запущен с флагом трассировки 1448, это ожидание исключается, и Агента чтения журнала может сразу копировать изменения, независимо от состояния зеркалирования.
Этот флаг трассировки можно применять в SQL Server 2008, а для SQL Server 2005 он появился в составе Cumulative update package 2 для SQL Server 2005 Service Pack 2. Подробности о флаге трассировки №1448 можно узнать в статье базы знаний Майкрософт №937041.
Обратите внимание на потенциальную опасность исключения задержек тиражирования в репликации. Для приложений отсутствие задержек может иметь решающее значение, и это может послужить причиной установки флага трассировки №1448 при использовании зеркалирования с репликацией. Надо понимать, что существует вероятность того, что использование этого флага приведёт к проблемам в случае отказа Зеркального отображения. Эти проблемы будут рассмотрены ниже.

Влияние отказа зеркалирования на работу Агента чтения журнала

Когда происходит автоматическая или ручная отработка отказа Зеркального отображения, Агент чтения журнала должен автоматически соединиться с новым основным сервером и продолжить копировать транзакций (если параметр -PublisherFailoverPartner был установлен правильно, как это будет описано ниже). Есть два случая, когда этого может не произойти. Первый, это когда отказ не может быть отработан правильно, потому что зеркальный сервер по каким либо причинам не может стать основным сервером. Второй случай может произойти только тогда, когда включён флаг трассировки №1448, о котором только что упоминалось выше.
Когда Зеркальное отображение не работает в режиме высокой доступности или основной сервер был выставлен или изолирован, может оказаться, что на основном сервере есть завершённые транзакции, которых еще не были закреплены на зеркале. Если основная база данных станет недоступной, и произойдёт передача её роли зеркалу, может случиться потеря тех транзакций, которые были завершены на основном сервере, но еще не были закреплены на зеркале. Если к тому же включён флаг трассировки №1448, некоторые из завершённых транзакций основного сервера могут быть скопированы, но ещё не закреплены на зеркале. Это приведёт к тому, что состояние метаданных о тиражируемых транзакциях на Распространителе будет опережать реальное состояние транзакций на принявшем роль основной базы данных зеркале. Т.е. Агент чтения журнала уже передавал Распространителю некоторое количество транзакций. После обнаружения таких расхождений, Агент распространителя выдаст ошибку: "The process could not execute 'sp_repldone/sp_replcounters' on ' GLADCHENKO-TEST'". Кроме этого сообщения об ошибке, будут и детализирующие ситуацию сообщения, из которых можно узнать, какой номер виртуального журнала пытался считать Агент чтения журнала. Исправить положение можно запустив с помощью системной хранимой процедуры sp_replrestart принудительную синхронизацию метаданных Издателя и Распространителя. Однако, велика вероятность того, что из-за расхождений в метаданных часть не синхронизированных в результате отказа основного сервера транзакций уже может попасть в базы данных Подписчиков. Это приведёт к тому, что Агент распространителя тоже прекратит свою работу, выдав сообщение об ошибке синхронизации данных. Поскольку в результате сбоя синхронизации Распространитель просто повторит изменения, передаваемые не прошедшими синхронизацию транзакциями, для преодоления ошибки работы Агента распространителя достаточно сменить его профиль на стандартный профиль с именем: "Continue on data consistency errors".

Как влияет нарушение зеркалирования на работу Агента чтения журнала

Как уже отмечалось, в случае отказа Зеркального отображения Агент чтения журнала станет работать с журналом транзакций зеркала основной базы данных. Возможность переключения на сменивший свою роль новый основной сервер обеспечивается параметром запуска агента -PublisherFailoverPartner. Пока между обоими участниками Зеркального отображения действует партнёрство, т.е. они продолжают работать по схеме основная база - зеркало основной базы, в случае отказа основной базы данных Агент чтения журнала подключится к принявшей роль основной, зеркальной базе данных.
Если основной сервер будет недоступен очень долго, лучше всего удалить зеркалирование, что позволит избежать проблем с ростом журнала транзакций зеркальной базы данных. Если отказ приведёт к тому, что будет нарушено партнёрство основной и зеркальной баз данных, Агент чтения журнала будет продолжать работать с зеркалом, пока ситуация с партнёрством не восстановится или зеркальное отображение не будет удалено. Если потом агента перезапустить, он попробует соединяться с основной базой данных, которая больше не является издаваемой базой данных, что приведёт к сбою репликации. В хронологии работы Агента чтения журнала появятся сообщения такого типа: "User-specified agent parameter values: -Publisher GLADCHENKO-TEST'". Также будут более детальные сообщения об отказе подключения. Для того чтобы решить подобную проблему, и заставить Агент чтения журнала продолжить свою работу, создайте на Распространителе псевдоним действующего сервера издателя.

Настройка периода хранения Распространителя

По умолчанию на Распространителе значение периода хранения транзакций установлено в ноль. Это означает, что транзакции очищаются сразу же, как только они будут доставлены всем Подписчикам. При использовании Зеркального отображения, в момент, когда из-за отказа Агент распространителя переключается на работу с зеркальной копией базы данных, нужно чтобы в базе данных распространителя ещё оставались транзакции, которые уже доставлены всем Подписчикам. Такое случается, когда зеркалирование работает в высокопроизводительном режиме, т.е. передача транзакций зеркалу идёт асинхронно и состояние зеркала может сильно отставать от состояния основной базы данных. Тогда, при необходимости добавления нового Подписчика, которого нежелательно инициализировать, у Агента распространителя должна существовать возможность догрузить новому Подписчику те транзакции, которые на момент добавления подписчика ещё не успели закрепиться в зеркале. Другой такой случай может произойти при отказе сервера Издателя, когда данные в базе данных публикации больше не доступны, и это не позволяет корректно инициализировать новую базу данных подписчика. В таком случае, при отсутствии транзакций на Распространителе, может произойти потеря данных.
Чтобы избегать потери данных или длительного простоя в системе с зеркалированием базы данных подписчика, рекомендуется установить адекватный период задержки транзакций у распространителя.
В наших примерах мы будем использовать следующий вариант настройки параметра задержки распространения:

USE distribution EXEC sp_changedistributiondb N'distribution', N'min_distretention', 3 EXEC sp_helpdistributiondb; GO

Типы ручной синхронизации Подписчика

Репликация не поддерживает автоматический переход на зеркальное отображение подписанной на публикацию базы данных, т.е. хотя зеркалирование и возможно, Агент распространителя не сможет переключиться на зеркало после отказа основной базы данных Зеркального отображения. Работа Агента распространителя закончится ошибкой сразу, как только он поймёт, что не может подключиться к базе данных, являющейся основной в партнёрстве Зеркального отображения. Зеркалирование Подписчика можно настраивать как до, так и после подписки. Однако, если планируется инициализация Подписчика моментальным снимком Публикации, эффективнее будет настраивать Зеркальное отображение после инициализации базы данных Подписчика.
В случае отказа Зеркального отображения базы данных Подписчика, необходимо перенаправить Репликацию на зеркальный сервер, туда, где теперь располагается новая база данных Подписчика, на новом основном сервере. Хотя этот метод требует заново оформить подписку, полной её инициализации не потребуется, и это благодаря новому типу синхронизации SQL Server 2008.
Все методы ручной инициализации Подписчика основаны на том, что база данных приводится в согласованное с Издателем состояние, и это становится отправной точкой тиражирования последовавших за этим состоянием транзакций Издателя. После создания Подписки с ручной синхронизацией, Агент распространителя должен иметь информацию о том, какие транзакции нужно тиражировать, не потеряв при этом новые данные или изменения в имеющихся данных. В качестве такой отправной точки используется тот номер LSN, который применим к соответствующему методу инициализации. Например, в методе с указанием параметра "replication support only" определение LSN происходит автоматически. Когда же инициализация осуществляется восстановлением из резервной копии, LSN берётся из заголовка файла резервной копии. В случае инициализации по LSN, необходимый номер виртуального журнала задаётся пользователем. Этот номер нужно указывать аккуратно, т.к. относящиеся к нему транзакции должны присутствовать на этот момент в базе данных Распространителя. Вот почему так важна задержка транзакций у Распространителя, это очень помогает в случае отказа.

Примеры

Для демонстрации новых возможностей обеспечения доступности Репликации SQL Server 2008 нам потребуется сервер Распространитель, который в представленном ниже сценарии создания Распространителя называется GLADCHENKO-VHD:

-- Сценарий создания Распространителя use master exec sp_adddistributor @distributor = N'GLADCHENKO-VHD' , @password = N'' -- безопасность репликации не тема этой статьи, -- оставляем уязвимой GO exec sp_adddistributiondb @database = N'distribution' , @data_folder = N'C:\MSSQL\DATA' , @log_folder = N'C:\LOG\DATA' , @log_file_size = 2 , @min_distretention = 0 , @max_distretention = 72 , @history_retention = 48 , @security_mode = 1 GO use [distribution] if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U ')) create table UIProperties(id int) if (exists (select * from ::fn_listextendedproperty('SnapshotFolder' , 'user', 'dbo', 'table', 'UIProperties', null, null))) EXEC sp_updateextendedproperty N'SnapshotFolder' , N'C:\MSSQL\ReplData' , 'user' , dbo , 'table' , 'UIProperties' else EXEC sp_addextendedproperty N'SnapshotFolder' , N'C:\MSSQL\ReplData' , 'user' , dbo , 'table' , 'UIProperties' GO exec sp_adddistpublisher @publisher = N'GLADCHENKO-VHD' , @distribution_db = N'distribution' , @security_mode = 1 , @working_directory = N'C:\MSSQL\ReplData' , @trusted = N'false' , @thirdparty_flag = 0 , @publisher_type = N'MSSQLSERVER' GO

Зеркальное отображение издаваемой базы данных

Мы будем использовать сервер GLADCHENKO-TEST в качестве основного сервера в зеркале и сервера издателя в Репликации. Публиковаться будет база данных MIR, и она же будет иметь своё Зеркальное отображение. Публикация тоже будет называться MIR. Сервер, на котором будет размещаться зеркало, называется GLADCHENKO-VHD и этот же сервер является в нашей тестовой топологии репликации Распространителем. В качестве сервера Подписчика будет выступать экземпляр GLADCHENKO-A\SUB, на котором база данных подписки будет называться MIR. Хотя репликацию можно настроить с помощью SQL Server Management Studio, мы будем её настраивать с помощью системных процедур Transact-SQL.
Для простоты демонстрации, все учётные записи, от имени которых будут запускаться службы, и имена входа, в контексте безопасности которых будут выполняться работы на серверах, будут сведены к одной, доменной учётной записи пользователя AG@troika.ru. Этот пользователь является локальным администратором на всех серверах, и включён в серверную роль sysadmins всех указанных выше серверов баз данных.

Шаг 1. Создание Публикации

Публикуемая база данных MIR состоит из четырёх таблиц: PerformanceCounter, PerformanceSignature, PerformanceSignatureData и PerformanceSignatureHistory. На самом деле, эти таблицы тиражируются средствами репликации транзакций с сервера, на котором размещена база данных System Center Operations Manager 2007 SP1. Сделано это для того, чтобы эмулировать работу "живого" приложения, поскольку данные мониторинга инфраструктуры серверов поступают в эти таблицы постоянно и без длительных перерывов. Именно этот поток реплицируемой информации мы и будет реплицировать с сервера GLADCHENKO-TEST на сервер GLADCHENKO-A\SUB.
Для получения более подробной информации о настройке Распространителя, смотрите следующие темы в SQL Server 2008 Books Online:

Ниже представлен сценарий создания Публикации.

Вначале, нужно разрешить на Распространителе ещё одного Издателя. Для этого на сервере GLADCHENKO-VHD выполните следующий сценарий:

exec sp_adddistpublisher @publisher = N'GLADCHENKO-TEST' , @distribution_db = N'distribution' , @security_mode = 1 , @working_directory = N'C:\MSSQL\ReplData' , @trusted = N'false' , @thirdparty_flag = 0 , @publisher_type = N'MSSQLSERVER' GO

После этого можно приступить к созданию Публикации, для чего на сервере GLADCHENKO-TEST нужно выполнить следующий сценарий:

use master -- Указываем Распространителя exec sp_adddistributor @distributor = N'GLADCHENKO-VHD' , @password = N'*********' -- тут нужно указать заданный -- на Распространителе пароль -- В ответ должны получить подтверждение: -- "You have updated the Publisher property 'active' successfully" GO use [MIR] exec sp_replicationdboption @dbname = N'MIR' , @optname = N'publish' , @value = N'true' GO -- Добавляет Публикацию exec sp_addpublication @publication = N'MIR' , @description = N'Публикация БД MIR в репликации транзакций, на сервере GLADCHENKO-TEST' , @sync_method = N'native' , @retention = 0 , @allow_push = N'true' , @allow_pull = N'true' , @allow_anonymous = N'false' , @enabled_for_internet = N'false' , @snapshot_in_defaultfolder = N'true' , @compress_snapshot = N'false' , @ftp_port = 21 , @ftp_login = N'anonymous' , @allow_subscription_copy = N'false' , @add_to_active_directory = N'false' , @repl_freq = N'continuous' , @status = N'active' , @independent_agent = N'true' , @immediate_sync = N'true' , @allow_sync_tran = N'false' , @autogen_sync_procs = N'false' , @allow_queued_tran = N'false' , @allow_dts = N'false' , @replicate_ddl = 1 , @allow_initialize_from_backup = N'true' , @enabled_for_p2p = N'false' , @enabled_for_het_sub = N'false' , @p2p_conflictdetection = N'false' , @p2p_originator_id = 1 -- В ответ должны получить сообщение: -- Job 'GLADCHENKO-TEST-MIR-1' started successfully. -- "Warning: The logreader agent job has been implicitly created -- and will run under the SQL Server Agent Service Account" GO -- Создаём инициализирующий моментальный снимок exec sp_addpublication_snapshot @publication = N'MIR' , @frequency_type = 1 , @frequency_interval = 1 , @frequency_relative_interval = 1 , @frequency_recurrence_factor = 0 , @frequency_subday = 8 , @frequency_subday_interval = 1 , @active_start_time_of_day = 0 , @active_end_time_of_day = 235959 , @active_start_date = 0 , @active_end_date = 0 , @job_login = null , @job_password = null , @publisher_security_mode = 1 GO -- Добавляем наши таблицы в виде статей публикации exec sp_addarticle @publication = N'MIR' , @article = N'PerformanceCounter' , @source_owner = N'dbo' , @source_object = N'PerformanceCounter' , @type = N'logbased' , @description = null , @creation_script = null , @pre_creation_cmd = N'drop' , @schema_option = 0x000000000803509F , @identityrangemanagementoption = N'manual' , @destination_table = N'PerformanceCounter' , @destination_owner = N'dbo' , @vertical_partition = N'false' , @ins_cmd = N'CALL sp_MSins_dboPerformanceCounter' , @del_cmd = N'CALL sp_MSdel_dboPerformanceCounter' , @upd_cmd = N'SCALL sp_MSupd_dboPerformanceCounter' GO exec sp_addarticle @publication = N'MIR' , @article = N'PerformanceSignature' , @source_owner = N'dbo' , @source_object = N'PerformanceSignature' , @type = N'logbased' , @description = null , @creation_script = null , @pre_creation_cmd = N'drop' , @schema_option = 0x000000000803509F , @identityrangemanagementoption = N'manual' , @destination_table = N'PerformanceSignature' , @destination_owner = N'dbo' , @vertical_partition = N'false' , @ins_cmd = N'CALL sp_MSins_dboPerformanceSignature' , @del_cmd = N'CALL sp_MSdel_dboPerformanceSignature' , @upd_cmd = N'SCALL sp_MSupd_dboPerformanceSignature' GO exec sp_addarticle @publication = N'MIR' , @article = N'PerformanceSignatureData' , @source_owner = N'dbo' , @source_object = N'PerformanceSignatureData' , @type = N'logbased' , @description = null , @creation_script = null , @pre_creation_cmd = N'drop' , @schema_option = 0x000000000803509F , @identityrangemanagementoption = N'manual' , @destination_table = N'PerformanceSignatureData' , @destination_owner = N'dbo' , @vertical_partition = N'false' , @ins_cmd = N'CALL sp_MSins_dboPerformanceSignatureData' , @del_cmd = N'CALL sp_MSdel_dboPerformanceSignatureData' , @upd_cmd = N'SCALL sp_MSupd_dboPerformanceSignatureData' GO exec sp_addarticle @publication = N'MIR' , @article = N'PerformanceSignatureHistory' , @source_owner = N'dbo' , @source_object = N'PerformanceSignatureHistory' , @type = N'logbased' , @description = null , @creation_script = null , @pre_creation_cmd = N'drop' , @schema_option = 0x000000000803509F , @identityrangemanagementoption = N'manual' , @destination_table = N'PerformanceSignatureHistory' , @destination_owner = N'dbo' , @vertical_partition = N'false' , @ins_cmd = N'CALL sp_MSins_dboPerformanceSignatureHistory' , @del_cmd = N'CALL sp_MSdel_dboPerformanceSignatureHistory' , @upd_cmd = N'SCALL sp_MSupd_dboPerformanceSignatureHistory' GO -- настраиваем и запускаем Агента чтения журнала exec [MIR].sys.sp_addlogreader_agent @job_login = null , @job_password = null , @publisher_security_mode = 1 GO exec [MIR].sys.sp_addqreader_agent @job_login = null , @job_password = null , @frompublisher = 1 GO -- Формируем лист доступа к публикации (PAL) -- Сюда нужно даобавить все задействованные агентами логины exec sp_grant_publication_access @publication = N'MIR' , @login = N'sa' GO exec sp_grant_publication_access @publication = N'MIR' , @login = N'NT AUTHORITY\SYSTEM' GO

Шаг 2. Настройка Доставки журналов для инициализации Подписчика

На этом шаге мы должны подготовить базу данных на сервере Подписчика, для того, чтобы она могла быть инициализирована из резервной копии. Поскольку поток транзакций в базе данных нашего Издателя не прерывается 24 часа в сутки, 7 дней в неделю и 365 дней в году, нам будет удобно задействовать для этого механизм Доставки журналов.
Для включения Доставки журналов нужно инициализировать базу данных Получателя путем восстановления полной резервной копии базы данных Источника. В нашем случае, резервным сервером, а заодно и сервером Подписчика будет компьютер GLADCHENKO-A\SUB.
Делаем полную копию издаваемой базы данных, для чего, на сервере GLADCHENKO-TEST выполняем следующий сценарий:

BACKUP DATABASE [MIR] TO DISK = N'C:\MSSQL\BACKUP\MIR.bak' WITH NOFORMAT, INIT , NAME = N'Полная копия БД MIR' , SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO

Дожидаемся получения подтверждения успешности этой операции, в окне результатов появится приблизительно такой текст:

10 percent processed. 20 percent processed. 30 percent processed. 40 percent processed. 50 percent processed. 60 percent processed. 70 percent processed. 80 percent processed. 90 percent processed. Processed 18104 pages for database 'MIR', file 'MIR' on file 1. 100 percent processed. Processed 3 pages for database 'MIR', file 'MIR_log' on file 1. BACKUP DATABASE successfully processed 18107 pages in 4.394 seconds (32.193 MB/sec).

После того, как копия успешно создана, нужно её скопировать на сервер Источника. В нашем случае, нужно взять файл с компьютера GLADCHENKO-TEST из папки C:\MSSQL\BACKUP\MIR.bak и скопировать его на компьютер GLADCHENKO-A, в папку: C:\MSSQL\BACKUP\MIR.bak.
После того, как файл резервной копии окажется на сервере Получателя, нужно восстановить базу данных Получателя, что мы и сделаем на экземпляре GLADCHENKO-A\SUB, используя следующий сценарий:

RESTORE DATABASE [MIR] FROM DISK = N'D:\MSSQL\BACKUP\MIR.bak' WITH FILE = 1 , MOVE N'MIR' TO N'C:\MSSQL\DATA\MIR.mdf' , MOVE N'MIR_log' TO N'D:\MSSQL\LOG\MIR_1.ldf' , NOUNLOAD, REPLACE, STATS = 10, NORECOVERY GO

После успешного восстановления базы данных, приступаем к настройке Доставки журналов между публикуемой базой данных на Издателе, и только что восстановленной базой на Подписчике. Подробное описание настройки Доставки журналов можно найти в статье электронной документации: "Как включить доставку журналов (Transact-SQL)". Создадим папку для обмена резервными копиями журналов транзакций: C:\MSSQL\LogShip. На эту папку у учётных записей служб должен быть полный доступ.
Далее, настраивает базу данных Источника вместе с заданием резервного копирования, а также записями локального и удаленного мониторов:

DECLARE @LS_BackupJobId AS uniqueidentifier DECLARE @LS_PrimaryId AS uniqueidentifier EXEC master.dbo.sp_add_log_shipping_primary_database @database = N'MIR' ,@backup_directory = N'C:\MSSQL\LogShip' ,@backup_share = N'\\GLADCHENKO-TEST\lsbackup' ,@backup_job_name = N'LSBackup_MIR' ,@backup_retention_period = 1440 ,@backup_compression = 0 ,@monitor_server = N'GLADCHENKO-A' ,@monitor_server_security_mode = 1 ,@backup_threshold = 60 ,@threshold_alert_enabled = 1 ,@history_retention_period = 1440 ,@backup_job_id = @LS_BackupJobId OUTPUT ,@primary_id = @LS_PrimaryId OUTPUT ,@overwrite = 1 GO

Значения идентификаторов получились соответственно следующие:

@LS_BackupJobId = DDCD7E77-F045-4649-A9F1-CF8FE843C711 @LS_PrimaryId = 3346F94D-3DB6-4A56-BE99-DF2E60E0A25F

Если процедура отработала без ошибок, должно появиться новое задание, предусматривающее один шаг, в котором выполняется следующая команда:

c:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqllogship.exe" -Backup 3346F94D-3DB6-4A56-BE99-DF2E60E0A25F -server GLADCHENKO-TEST'

Создаём расписание для задания резервного копирования журналов транзакций базы данных MIR:

DECLARE @schedule_id int EXEC msdb.dbo.sp_add_jobschedule @job_id=N'DDCD7E77-F045-4649-A9F1-CF8FE843C711' ,@name=N'1' ,@enabled=1 ,@freq_type=4 ,@freq_interval=1 ,@freq_subday_type=4 ,@freq_subday_interval=10 ,@freq_relative_interval=0 ,@freq_recurrence_factor=1 ,@active_start_date=20081202 ,@active_end_date=99991231 ,@active_start_time=0 ,@active_end_time=235959 ,@schedule_id = @schedule_id OUTPUT SELECT @schedule_id GO

Это расписание предусматривает выполнение задания каждые 10 минут в течение суток.
Мы не планируем создавать полное решение Доставки журналов, наша задача только облегчить первоначальную инициализацию Подписчика. Однако, давайте полностью следовать упомянутой выше инструкции BOL по включению Доставки журналов.
Далее, нам нужно на сервере Источника задать базу данных Получателя:

EXEC master.dbo.sp_add_log_shipping_primary_secondary @primary_database = N'MIR' ,@secondary_server = N'GLADCHENKO-A\SUB' ,@secondary_database = N'MIR' ,@overwrite = 1 GO

После этого, на сервере Получателя (Подписчик, в нашем примере это компьютер GLADCHENKO-A\SUB) нужно запустить процедуру, которая создаст все необходимые задания по применению резервных копий на базе данных Получателя.

DECLARE @LS_Secondary__CopyJobId AS uniqueidentifier DECLARE @LS_Secondary__RestoreJobId AS uniqueidentifier DECLARE @LS_Secondary__SecondaryId AS uniqueidentifier EXEC master.dbo.sp_add_log_shipping_secondary_primary @primary_server = N'GLADCHENKO-TEST' ,@primary_database = N'MIR' ,@backup_source_directory = N'\\GLADCHENKO-TEST\lsbackup' ,@backup_destination_directory = N'\\GLADCHENKO-A\D$\MSSQL\LogShip' ,@copy_job_name = N'LSCopy_GLADCHENKO-TEST_MIR' ,@restore_job_name = N'LSRestore_GLADCHENKO-TEST_MIR' ,@file_retention_period = 4320 ,@monitor_server = N'GLADCHENKO-A' ,@monitor_server_security_mode = 1 ,@overwrite = 1 ,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT ,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT ,@secondary_id = @LS_Secondary__SecondaryId OUTPUT GO

Получаем следующий набор идентификаторов:

@LS_Secondary__CopyJobId = 338E7DF6-F266-40CD-902E-86F424E12E6C @LS_Secondary__RestoreJobId = 6C0FE890-5FC5-4BA7-BD89-EC13214514E8 @LS_Secondary__SecondaryId = DC7800CE-767C-4F2B-9A42-70DC5F807184

После этого, среди заданий экземпляра GLADCHENKO-A\SUB появятся два новых задания, для которых определены шаги, но ещё не настроены расписания их работы. Первое задание с именем: LSCopy_GLADCHENKO-TEST_MIR. У этого задания только один шаг и он выполняет следующий сценарий:

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqllogship.exe -Copy DC7800CE-767C-4F2B-9A42-70DC5F807184 -server GLADCHENKO-A\SUB

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

EXEC msdb.dbo.sp_add_schedule @schedule_name =N'DefaultCopyJobSchedule' ,@enabled = 1 ,@freq_type = 4 ,@freq_interval = 1 ,@freq_subday_type = 4 ,@freq_subday_interval = 15 ,@freq_recurrence_factor = 0 ,@active_start_date = 20081203 ,@active_end_date = 99991231 ,@active_start_time = 0 ,@active_end_time = 235900 ,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT ,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT EXEC msdb.dbo.sp_attach_schedule @job_id = '338E7DF6-F266-40CD-902E-86F424E12E6C' ,@schedule_id = @LS_SecondaryCopyJobScheduleID GO

Второе задание называется LSRestore_GLADCHENKO-TEST_MIR и предназначено для восстановления скопированных с Источника резервных копий на базе данных Получателя. Единственный шаг этого задания выполняет следующую инструкцию:

С:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqllogship.exe -Restore DC7800CE-767C-4F2B-9A42-70DC5F807184 -server GLADCHENKO-A\SUB

Для добавления этому заданию расписания запуска необходимо на сервере GLADCHENKO-A\SUB выполнить следующий сценарий:

EXEC msdb.dbo.sp_add_schedule @schedule_name =N'DefaultRestoreJobSchedule' ,@enabled = 1 ,@freq_type = 4 ,@freq_interval = 1 ,@freq_subday_type = 4 ,@freq_subday_interval = 15 ,@freq_recurrence_factor = 0 ,@active_start_date = 20081203 ,@active_end_date = 99991231 ,@active_start_time = 0 ,@active_end_time = 235900 ,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT ,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT EXEC msdb.dbo.sp_attach_schedule @job_id = '6C0FE890-5FC5-4BA7-BD89-EC13214514E8' ,@schedule_id = @LS_SecondaryRestoreJobScheduleID GO

И теперь осталось только добавить в Доставку журналов базу данных Получателя, что можно сделать, выполнив следующий сценарий на сервере GLADCHENKO-A\SUB:

EXEC master.dbo.sp_add_log_shipping_secondary_database @secondary_database = N'MIR' ,@primary_server = N'GLADCHENKO-TEST' ,@primary_database = N'MIR' ,@restore_delay = 0 ,@restore_mode = 0 ,@disconnect_users = 0 ,@restore_threshold = 45 ,@threshold_alert_enabled = 1 ,@history_retention_period = 1440 ,@overwrite = 1 GO

Следующим действием в настройке Доставки журналов будет подготовка сервера мониторинга, в качестве которого будет выступать экземпляр GLADCHENKO-A. На этом экземпляре нужно выполнить следующий сценарий:

EXEC msdb.dbo.sp_processlogshippingmonitorprimary @mode = 1 ,@primary_id = N'3346F94D-3DB6-4A56-BE99-DF2E60E0A25F' ,@primary_server = N' GLADCHENKO-TEST' ,@monitor_server = N'GLADCHENKO-A' ,@monitor_server_security_mode = 1 ,@primary_database = N'MIR' ,@backup_threshold = 60 ,@threshold_alert = 14420 ,@threshold_alert_enabled = 1 ,@history_retention_period = 1440 GO

После этого, нужно включить те задания обслуживания Доставки журналов, которые были отмечены как выключенные. Это должно привести к тому, что Доставка журналов начнёт свою работу и будет поддерживать актуальность базы данных MIR на сервере GLADCHENKO-A\SUB в автоматическом режиме.
Теперь, когда придёт время создавать Подписку, достаточно будет ненадолго отключить клиентов от базы данных Издателя, дождаться или запустить вручную поочерёдно все задания Доставки журналов на Источнике и потом на Получателе, отключить все эти задания, и подготовить базу данных Получателя к настройке Подписки, выполнив этот сценарий:

RESTORE DATABASE MIR WITH RECOVERY GO

Шаг 3. Настройка Подписки

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

use [MIR] exec sp_addsubscription @publication = N'MIR' , @subscriber = N'GLADCHENKO-A\SUB' , @destination_db = N'MIR' , @sync_type = N'initialize with backup' , @backupdevicetype = N'disk' , @backupdevicename = 'LogShip\MIR_20081203195807.trn' , @fileidhint = 1 , @subscription_type = N'pull' , @update_mode = N'read only' GO

Все последующие действия по оформлению Подписки нужно выполнять на сервере GLADCHENKO-A\SUB. Следующий сценарий создаёт Подписку по запросу:

use [MIR] exec sp_addpullsubscription @publisher = N'GLADCHENKO-TEST' , @publication = N'MIR' , @publisher_db = N'MIR' , @independent_agent = N'True' , @subscription_type = N'pull' , @description = N'' , @update_mode = N'read only' , @immediate_sync = 1

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

exec sp_addpullsubscription_agent @publisher = N'GLADCHENKO-TEST' , @publisher_db = N'MIR' , @publication = N'MIR' , @distributor = N'GLADCHENKO-VHD' , @distributor_security_mode = 1 , @distributor_login = N'' , @distributor_password = null , @enabled_for_syncmgr = N'False' , @frequency_type = 64 , @frequency_interval = 0 , @frequency_relative_interval = 0 , @frequency_recurrence_factor = 0 , @frequency_subday = 0 , @frequency_subday_interval = 0 , @active_start_time_of_day = 0 , @active_end_time_of_day = 235959 , @active_start_date = 20081203 , @active_end_date = 99991231 , @alt_snapshot_folder = N'' , @working_directory = N'' , @use_ftp = N'False' , @job_login = null , @job_password = null , @publication_type = 0 GO

Когда все необходимые действия по оформлению подписки завершены, на сервер GLADCHENKO-A\SUB можно полностью удалить Доставку журналов и позаботиться об удалении созданных там заданий Доставки журналов.

Шаг 4. Зеркалирование публикуемой базы данных

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

В последней из перечисленных выше статей говориться, что предварительно на зеркальном сервере должна быть восстановлена резервная копия основной базы данных, и все последующие резервные копии журнала транзакций, восстановленные с использованием предложения WITH NORECOVERY. Как уже отмечалось выше, лучше всего подготовку синхронной копии зеркалируемой базы данных позволяет сделать Доставка журналов. Поскольку в настоящей статье мы уже уделили много внимания настройке Доставки журналов, повторение этой процедуры для инициализации копии основной базы данных мы опустим, будем считать, что все эти процедуры уже выполнены. Копия базы данных на момент начала настройки Зеркального отображения должна находиться в актуальном по отношению к основной базе данных состоянии.
В нашем примере, основной экземпляр и зеркало настраиваются так, чтобы использовать одного и того же Распространителя. В конфигурации Агента чтения журнала мы изменим настройки, чтобы получить нужное нам поведение агента в случае отказа сервера Издателя. Для этого на сервере Распространителя GLADCHENKO-VHD нужно установить значение для параметра запуска Агент чтения журнала -PublisherFailoverPartner, например так:

exec sp_add_agent_parameter @profile_id = 1 -- ID Агента моментальных снимков , @parameter_name = N'-PublisherFailoverPartner' , @parameter_value = N'GLADCHENKO-VHD' -- сервер зеркала GO exec sp_add_agent_parameter @profile_id = 2 -- ID Агента чтения журнала , @parameter_name = N'-PublisherFailoverPartner' , @parameter_value = N'GLADCHENKO-VHD' -- сервер зеркала GO

Для принятия изменений профиля, нужно перезапустить задание Агента чтения журнала.
Зеркалирование публикуемой базы данных начнём с создания конечных точек зеркалирования. В первую очередь создадим конечную точку на Издателе, это у нас компьютер GLADCHENKO-TEST:

CREATE ENDPOINT [MirroringEndpoint] AUTHORIZATION [AG@troika.ru] STATE=STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATA_MIRRORING ( ROLE = PARTNER , AUTHENTICATION = WINDOWS KERBEROS , ENCRYPTION = REQUIRED ALGORITHM RC4 ) GO

На сервере Зеркального отображения GLADCHENKO-VHD (это у нас Распространитель) тоже нужна конечная точка:

CREATE ENDPOINT [MirroringEndpoint] AUTHORIZATION [AG@troika.ru] STATE=STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATA_MIRRORING ( ROLE = ALL , AUTHENTICATION = WINDOWS KERBEROS , ENCRYPTION = REQUIRED ALGORITHM RC4 ) GO

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

SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints

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

GRANT CONNECT ON ENDPOINT:: MirroringEndpoint TO AG@troika.ru GO

После раздачи прав, нужно объявить участников Зеркального отображения. На сервере Зеркальной копии GLADCHENKO-VHD выполним следующую команду:

ALTER DATABASE [MIR] SET PARTNER = 'TCP://GLADCHENKO-TEST.troika.ru:5022'

После этого, с основного сервера GLADCHENKO-TEST запустим симметричную команду:

ALTER DATABASE [MIR] SET PARTNER = 'TCP://GLADCHENKO-VHD.troika.ru:5022'

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

ALTER DATABASE [MIR] SET PARTNER OFF

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

telnet GLADCHENKO-VHD 5022

Шаг 5. Переключение Публикации на зеркальную копию

Ну а теперь мы перейдём к самому интересному, ради чего были все эти подготовительные действия. Итак, мы имеем репликацию транзакций между двумя серверами, и Распространителя на третьем сервере. Кроме того, у нас настроено Зеркальное отображение базы данных Издателя на сервер Распространителя. Перед нами стоит задача - возобновить репликацию, которая была прервана в результате отказа Издателя. Доступность Издателя определяется Зеркальным отображением базы данных, на основе объектов которой создана наша Публикация. Отказ этой базы данных расценивается нами, как отказ Зеркального отображения и поводом для ручной или автоматической смены роли, когда зеркальная копия становится основной базой данных.
Далее, имитируем отказ основного сервера, который у нас является Издателем:

ALTER DATABASE MIR SET PARTNER FAILOVER

В ответ получаем сообщение: "Nonqualified transactions are being rolled back. Estimated rollback completion: 100%".
После этого основная база данных Зеркального отображения меняется ролями со своим зеркалом. Причём, на Распространителе появляется публикация и видно, что у неё уже существует Подписка (которую мы создавали на сервере GLADCHENKO-TEST). Изменения, которые пользователи будут делать в базе данных MIR после её переезда на сервер GLADCHENKO-VHD, будут вначале закрепляться в новом зеркале, в базе данных на сервере GLADCHENKO-TEST. Потом они будут попадать в базу данных Распространителя, считанные Агентом чтения журнала, который умеет обращаться ко второму партнёру Зеркального отображения в случае отказа основного сервера или основной базы данных.
Если на сервере GLADCHENKO-TEST запустим следующую команду:

SELECT PUBLISHINGSERVERNAME()

Эта команда возвратит имя исходного издателя для опубликованной базы данных, участвующей в Зеркальном отображении. Эта функция показывает первоначального издателя опубликованной базы данных, и поэтому у нас она вернёт, и всегда будет возвращать: "GLADCHENKO-TEST".
Если после этого выполнить на сервере GLADCHENKO-VHD ту же саму команду: "ALTER DATABASE MIR SET PARTNER FAILOVER", то всё вернётся в изначальное состояние. Публикация снова будет располагаться на сервере GLADCHENKO-TEST.
В отличие от агентов репликации, Монитор репликации не очень приспособлен для отслеживания работы с перемещённой на зеркальный сервер базой данных. Хотя его интерфейс будет показывать перемещение Публикации, информация о сеансах репликации может вводить администратора репликации в заблуждение. Трассировочные маркеры тоже не всегда помогают контролировать процесс репликации. В таком режиме работы репликации лучше всего контролировать сами данные, сверяя последние их изменения на издателе и подписчиках.
Существует опасность, что отказ основной базы данных приведёт к возникновению проблем репликации транзакций. Это особенно актуально, когда не соблюдается порядок первоначальной фиксации транзакции в зеркале базе данных. Если случиться так, что несколько некорректных транзакций будут препятствовать продолжению репликации данных, можно принудительно проигнорировать эти транзакции, что позволяет сделать системная процедура sp_setsubscriptionxactseqno.

Выводы

Зеркальное отображение можно использовать для повышения доступности Издателя в топологиях Репликации транзакций и Репликации слияния. Если важно, чтобы клиенты продолжали получать возможность изменения данных в издаваемой базе данных и автоматически переключались на её зеркальную копию в случае отказа, применение зеркалирования является оправданным.
В упомянутом в самом начале настоящей статьи техническом документе Майкрософт: " SQL Server Replication: Providing High Availability using Database Mirroring" предлагается вариант решения для зеркалирования баз данных Подписчиков. Там же вы найдёте аргументы для выбора такого решения.
Для повышения доступности базы данных Распространителя существует пока только одно решение - отказоустойчивая кластеризация.

Сравнения списка объектов SQL Server в Powershell на примере сравнения логинов на двух серверах

gladchenko — Wed, 18 Nov 2009 12:52:00 GMT

В данном примере я покажу как с помощью Powershell можно сравнивать списки объектов на двух серверах на примере сравнения логинов: поиск одинаковых, поиск разницы. Для этого сначала выполним подключение к серверам. В данном примере первый сервер - локальный. К нему будем подключаться с использованием Windows аутентификации.
Второй сервер - удаленный, к нему будем подключаться с использованием аутентификации SQL Server и запросом на ввод логина и пароля при подключении.

Читаем в блоге Ирины Наумовой.

Вариант стратегии быстрого и надежного резервного копирования/восстановления VLDB по сети

gladchenko — Fri, 13 Nov 2009 12:17:00 GMT

По материалам технической статьи Майкрософт: A Case Study: Fast and Reliable Backup and Restore of a VLDB over the Network

Автор: Томас Грохсер (Thomas H. Grohser)
При содействии: Линдсей Аллен (Lindsey Allen)
Техническая экспертиза статьи: Sanjay Mishra, Lubor Kollar, Stuart Ozer, Thomas Kejser, Juergen Thomas, James Podgorski, Burzin Patel
Перевод: Александр Гладченко, Ирина Наумова
Редактура перевода: Алексей Халако
Дата издания: июнь 2009г.
Тематика статьи: SQL Server 2008

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

Читать статью в блоге Ирины Наумовой

Данный перевод опубликован на сайте SQLCAT: http://sqlcat.com/whitepapers_russian/archive/2009/11/25/pagelatch-insert.aspx

HDD DEAD?!

gladchenko — Tue, 20 Oct 2009 13:24:00 GMT

…и наконец моё любопытство возобладало, и я решил таки заглянуть, что же такого понаделел HP в TPC-H…

Не удивляйтесь, но поначалу я не предавал особого значения появляющимся с завидной регулярностью (раз в месяц) новым, ничем с виду не примечательным результатам. Но, когда появился третий из них, я заметил тенденцию, что они становятся с каждым разом немного ХУЖЕ и ДОРОЖЕ. Именно тогда мне стало любопытно, в чём там "порылась собака"…
Конечно, виною всему было то, как представлены результаты в сводной таблице: Complete TPC-H Results List - Sorted by Date Submitted
Просто, в представлении на сайте не видно главного изменения, поскольку испокон веков эта составляющая была неизменной и только в последние год - два ситуация начала выправляться к лучшему. На самом деле, HP в последнем из трёх своих результатов показал, как SAS диски могут оказаться более дорогим решением, чем SSD диски, при близкой производительности.
Вот несколько видоизменённое представление результатов, с детализацией по конфигурации дисковой подсистемы:

Ссылки на подробные описания тестов:

Результаты потрясающие! Оказывается решение на базе SSD получается чуть ли не в двое дешевле, чем на привычных нам SAS.
Похоже, гегемонии производителей жёстких дисков приходит долгожданный конец, а точнее, последний рудимент зари IBM PC, этот кошмарный механический монстр, самое слабое звено любого компьютера - HDD может уйти в небытие!

Tips for DBA: Замена для стандартного "Back Up Database Task"

gladchenko — Wed, 07 Oct 2009 14:08:00 GMT

Очень часто получается так, что реальные бизнес -требования оказываются сложнее, чем возможности мастеров программного инструментария, поставляемого разными производителями ПО для задач администрирования SQL Server 2008. Например, недавно мне стало недостаточно гибкости мастера создания задачи резервного копирования базы данных для стандартного плана обслуживания БД. У меня возникла необходимость делать копию в несколько фалов на разных дисковых массивах и поддерживать хронологию копий по единым правилам. В несколько файлов выполнять резервное копирование бывает необходимо для повышения производительности этой операции, например, как это рекомендовано в этой статье: "A Technical Case Study: Fast and Reliable Backup and Restore of Multi-Terabytes Database over the Network".
Мастер SSMS может либо поддерживать хронологию для одного файла копий, либо копировать в фиксированные имена нескольких указанных файлов. К счастью, совершенно не составляет труда заменить задачу резервного копирования на задачу исполнения сценария T-SQL, в котором выполнить нужную работу. Ниже представлен соответствующий шаблон сценария, взяв который за основу можно составить необходимую задачу исполнения сценария T-SQL.

DECLARE @path varchar(128) DECLARE @DBName AS nvarchar(4000) = 'ИМЯБАЗЫДАННЫХ' DECLARE @File1 AS nvarchar(4000), @File2 AS nvarchar(4000), @File3 AS nvarchar(4000) -- Если диск для копий один, и он корректно задан, узнать путь к папке копий можно так: EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer', @value_name='BackupDirectory', @value=@path OUTPUT -- Формируем часть имени файла, в которой фиксируется имя БД и время создания копии DECLARE @FileName AS nvarchar(4000) = @path + '\' + @DBName +'\' + @DBName + '_' + 'backup_' + CONVERT(nvarchar(4),YEAR(CURRENT_TIMESTAMP), 112 ) + '_' + CASE MONTH(CURRENT_TIMESTAMP) WHEN 1 THEN '01' WHEN 2 THEN '02' WHEN 3 THEN '03' WHEN 4 THEN '04' WHEN 5 THEN '05' WHEN 6 THEN '06' WHEN 7 THEN '07' WHEN 8 THEN '08' WHEN 9 THEN '09' ELSE CONVERT(nvarchar(2),MONTH(CURRENT_TIMESTAMP), 112 ) END + '_' + CASE DAY(CURRENT_TIMESTAMP) WHEN 1 THEN '01' WHEN 2 THEN '02' WHEN 3 THEN '03' WHEN 4 THEN '04' WHEN 5 THEN '05' WHEN 6 THEN '06' WHEN 7 THEN '07' WHEN 8 THEN '08' WHEN 9 THEN '09' ELSE CONVERT(nvarchar(2),DAY(CURRENT_TIMESTAMP), 112 ) END + '_' + REPLACE(REPLACE(CAST(CONVERT(time(7),CURRENT_TIMESTAMP, 109 ) AS nvarchar(14)),':',''),'.','_'); -- Добавляем к пути и имени файла идентификаторы и расширения SELECT @File1 = @FileName + + '01.bak', @File2 = @FileName + + '02.bak', @File3 = @FileName + + '03.bak' -- Запускаем резервное копирование в три файла. BACKUP DATABASE [ИМЯБАЗЫДАННЫХ] TO DISK = @File1, DISK = @File2, DISK = @File3 WITH NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10

Как справиться с PAGELATCH при больших INSERT-нагрузках

gladchenko — Mon, 28 Sep 2009 12:30:00 GMT

По материалам статьи: "Resolving PAGELATCH Contention on Highly Concurrent INSERT Workloads".
Авторы: Thomas Kejser, Lindsey Allen, Arvind Rao и Michael Thomassy
При участии и с рецензиями: Mike Ruthruff, Lubor Kollar, Prem Mehra, Burzin Patel, Michael Thomassy, Mark Souza, Sanjay Mishra, Peter Scharlock, Stuart Ozer, Kun Cheng и Howard Yin
Перевод Александра Гладченко

Введение

Недавно, мы проводили лабораторные испытания в Microsoft Enterprise Engineering Center, при которых использовалась большая рабочая нагрузка, характерная для OLTP систем. Целью этой лабораторной работы было определить, что случится при увеличении числа процессоров с 64 до 128, при обслуживании Microsoft SQL Server интенсивной рабочей нагрузки (примечание: эта конфигурация была ориентирована на релиз Microsoft SQL Server 2008 R2). Рабочая нагрузка представляла собой хорошо распараллеленные операции вставки, направляемые в несколько больших таблиц.
Рабочая нагрузка масштабировалась до 128 процессорных ядер, но в статистике ожиданий было очень много кратких блокировок PAGELATCH_UP и PAGELATCH_EX. Средняя продолжительность ожидания была десятки миллисекунд, и таких ожиданий было очень много. Такое их количество оказалось для нас неожиданностью, ожидалось, что продолжительность не будет превышать несколько миллисекунд.
В этой технической заметке вначале будет описано, как диагностировать подобную проблему и как для разрешения подобной проблемы использовать секционирование таблиц.

Диагностика проблемы

Когда в sys.dm_os_wait_stats наблюдается большое число PAGELATCH, с помощью sys.dm_os_waiting_tasks можно определить сессию и ресурс, который задача ожидает, например, с помощью этого сценария:

SELECT session_id, wait_type, resource_description FROM sys.dm_os_waiting_tasks WHERE wait_type LIKE 'PAGELATCH%'

Пример результата:

В столбце resource_description указаны местоположения страниц, к которым ожидают доступ сессии, местоположение представлено в таком формате:

<database_id>:<file_id>:<page_id>

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

SELECT wt.session_id, wt.wait_type, wt.wait_duration_ms , s.name AS schema_name , o.name AS object_name , i.name AS index_name FROM sys.dm_os_buffer_descriptors bd JOIN ( SELECT * , CHARINDEX(':', resource_description) AS file_index , CHARINDEX(':', resource_description , CHARINDEX(':', resource_description)) AS page_index , resource_description AS rd FROM sys.dm_os_waiting_tasks wt WHERE wait_type LIKE 'PAGELATCH%' ) AS wt ON bd.database_id = SUBSTRING(wt.rd, 0, wt.file_index) AND bd.file_id = SUBSTRING(wt.rd, wt.file_index, wt.page_index) AND bd.page_id = SUBSTRING(wt.rd, wt.page_index, LEN(wt.rd)) JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id JOIN sys.partitions p ON au.container_id = p.partition_id JOIN sys.indexes i ON p.index_id = i.index_id AND p.object_id = i.object_id JOIN sys.objects o ON i.object_id = o.object_id JOIN sys.schemas s ON o.schema_id = s.schema_id

Сценарий показал, что ожидаемые страницы относятся к кластеризованному индексу, определённому первичным ключом таблицы с представленной ниже структурой:

CREATE TABLE HeavyInsert ( ID INT PRIMARY KEY CLUSTERED , col1 VARCHAR(50) ) ON [PRIMARY]

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

Основная информация

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

В журнале транзакций создаётся запись о том, что строка изменилась.
Осуществляется поиск в В-дереве местонахождения той страницы, куда должна будет попасть новая запись.
Осуществляется наложение на эту страницу краткой блокировки PAGELATCH_EX, которая призвана воспрепятствовать изменениям из других потоков.
Осуществляется добавление строки на страницу и, если это необходимо, осуществляется пометка этой страницы как "грязной".
Осуществляется снятие краткой блокировки со страницы.

В итоге, страница должна будет быть сброшена на диск процессом контрольной точкой или отложенной записи.
Если же все вставки строк направлены на ту же самую страницу, можно наблюдать рост очереди к этой странице. Даже притом, что краткая блокировка весьма непродолжительна, она может стать причиной конкуренции при высоком параллелизме рабочей нагрузки. У нашего клиента, первый и единственный столбец в индексе являлся монотонно возрастающим ключом. Из-за этого, каждая новая вставка шла на ту же самую страницу в конце В-дерева, пока эта страница не была заполнена. Рабочие нагрузки, которые используют в качестве первичного ключа IDENTITY или другие столбцы с последовательно увеличивающимися значениями, также могут столкнуться с подобной проблемой, если распараллеливание достаточно высоко.

Решение

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

CREATE PARTITION FUNCTION pf_hash (INT) AS RANGE LEFT FOR VALUES (0,1,2) CREATE PARTITION SCHEME ps_hash AS PARTITION pf_hash ALL TO ([PRIMARY])

Представленный выше пример использует четыре секции. Число необходимых секций зависит от числа активных процессов, выполняющих операции INSERT в описанную выше таблицу. Есть некоторая сложность в секционировании таблицы с помощью хэш-столбца, например, в том что всякий раз, когда происходит выборка строк из таблицы, будут затронуты все секции. Это означает, что придётся обращаться более чем к одному В-дереву, т.е. не будет отброшенных оптимизатором за ненадобностью ненужных секций. Будет дополнительная нагрузка на процессоры и некоторое увеличение времени ожиданий процессоров, что побуждает минимизировать число планируемых секций (их должно быть минимальное количество, при котором не наблюдается PAGELATCH). В рассматриваемом нами случае, в системе нашего клиента имелось достаточно много резерва в утилизации процессоров, так что было вполне возможно допустить небольшую потерю времени для инструкций SELECT, и при этом увеличить до необходимых объёмов норму инструкций INSERT.
Ещё одной сложностью является то, что нужен дополнительный столбец, по которому будет выполняться секционирование, т.е. на основании значения которого будут распределяться вставки по четырем секциям. Такого столбца изначально в сценарии Microsoft Enterprise Engineering Center не было. Однако, его достаточно просто было создать. Для этого использовался тот факта, что столбец ID монотонно увеличивается с приращением равным единице, и здесь легко применима довольно простая хеш-функция:

CREATE TABLE HeavyInsert_Hash( ID INT NOT NULL , col1 VARCHAR(50) , HashID AS CAST(ABS(ID % 4) AS TINYINT) PERSISTED NOT NULL)

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

CREATE UNIQUE CLUSTERED INDEX CIX_Hash ON HeavyInsert_Hash (ID, HashID) ON ps_hash(HashID)

Используя новую схему таблицы с секционированием вместо первоначального варианта таблицы, мы сумели избавиться от очередей PAGELATCH и повысить скорость вставки. Этого удалось достичь за счёт балансировки вставки между несколькими секциями и высокого параллелизма. Вставка происходит в несколько страниц, и каждая страница размещается в своей собственной структуре В-дерева. Для нашего клиента удалось повысить производительность вставки на 15 процентов, и справится с большой очередью PAGELATCH к горячей странице индекса одной таблицы. Но при этом удалось также оставить достаточно большой резерв циклов процессоров, что оставило возможность сделать аналогичную оптимизацию для другой таблицы, тоже с высокой нормой вставки.
Строго говоря, суть этой уловки в оптимизации логической схемы первичного ключа таблицы. Однако, потому что ключ просто стал длиннее на величину хеш-функции относительно изначального ключа, дубликатов для столбца ID удалось избежать. Уникальные индексы по единственному столбцу таблицы зачастую становятся причиной проблем с очередями PAGELATCH. Но даже если эту проблему удастся устранить, у таблицы может оказаться другой, некластеризованный индекс, который будет подвержен той же самой проблеме. Как правило, проблема наблюдается для уникальных ключей на единственном столбце, где каждая вставка попадает на одну и ту же страницу. Если и у других таблиц индексы подвержены проблеме с PAGELATCH, можно применить эту же уловку с секционированием к индексам таких таблиц, используя такой же ключ с хэшем в качестве первичного ключа.
Не всегда возможно внести изменения в приложение, особенно, если оно является плодом усилий третьих фирм. Но если изменение запросов возможно, становится доступной их оптимизация за счёт добавления к ним условий фильтрации по предикатам первичного ключа.

Пример: Чтобы отбросить ненужные секции, можно внести следующие изменения в сценарий:

SELECT * FROM HeavyInsert_Hash WHERE ID = 42

Который после изменений будет выглядеть так:

SELECT * FROM HeavyInsert_Hash WHERE ID = 42 AND HashID = 42 % 4

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

Query Processor Modeling Extensions

gladchenko — Mon, 31 Aug 2009 13:13:00 GMT

По материалам стать Иан Джоз (Ian Jose): Query Processor Modelling Extensions in SQL Server 2005 SP1
Перевод Александра Гладченко
Редактура Алексея Халако

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

Появившееся в SQL Server 2005 SP1 расширение оптимизатора Query Processor Modelling Extensions можно включить с помощью флага трассировки 2301. Это расширение обеспечивает возможность системы моделирования оптимизатора запросов выбирать более производительные планы исполнения сложных запросов к базе данных. Улучшенное моделирование планов запроса в некоторых случаях может привести к существенному повышению производительности исполнения запросов. Однако, эти расширения моделирования процессора запросов, могут привести к заметному увеличенному времени компиляции, и поэтому рекомендуются для использования только в тех приложениях, в которых компиляций бывает немного, и они происходят нечасто. Были добавлены следующие расширения моделирования:

Целочисленное моделирование (Integer Modelling)

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

Обычно, гистограммы игнорируются, когда количество элементов отношения оказывается меньше числа шагов в гистограмме. Это - логика, которая определяет вероятность того, что гистограмма и далее будет описывать отношение. Это расширение применяет гистограмму в оценке количества элементов независимо от оценки количества элементов для отношения.
Предположение основного ограничения (Base Containment Assumption)

Обычно, при соединении двух отношений мы предполагаем, что для Х определенных кодовых точек (которые относятся к тому же самому ключевому диапазону, что и отношение R) при соединении с Y уникальных точек того же самого ключевого диапазона, что и в отношении S, всегда найдётся пара MIN(X,Y). Такое допущение называют "Простое ограничение". Мы предполагаем, что наименьшее число из уникальных точек кода соотносится с точками кода на другой стороне. Это моделирование игнорирует относительную совокупность уникальных точек кода в основных формах R и S, и также игнорирует любую фильтрацию, которая была применена к основными формами R и S перед соединением. Основное ограничение основывается на предположение, что ограничение применяется только к основным отношениям и использует вероятностные методы вычисления степени соединения. Кроме того, подразумевается, что модели фильтров применены правильно, так как их поведение очень отличается от ортогональных фильтров.
Углублённое изменение отображения плотности (Comprehensive Density Remapping)

Плотность (Density) - это мера числа дубликатов значений для каждого уникального значения. Обычно, после CONVERT-тации столбцов довольно незначительное число плотностей для этих столбцов заново отображается как новые определения столбцов. Обратите внимание, что такие операции, как конвертация довольно редко меняют плотность столбца. Когда задействовано это расширение моделирования, все такие повторные отображения будут обязательно сделаны, что делает возможным последующее использование плотности для целей оценки количества элементов. В некоторых случаях, это может привести к чрезмерной утилизации памяти.
Углублённое согласование плотности (Comprehensive Density Matching)

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

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

Флаги трассировки, рекомендованные Майкрософт для эталонного теста производительности TPC-E

gladchenko — Mon, 24 Aug 2009 10:05:00 GMT

По материалам статьи: Microsoft SQL Server 2008 TPC-E Trace Flags
Вольный перевод Александра Гладченко

Наиболее часто используемым способом изменения поведения SQL Server является выставление флагов трассировки. Следующие флаги трассировки поддерживаются в настоящее время Майкрософт для публикации результатов тесов производительности TPC-E. Если сотрудники Майкрософт рекомендуют Вам использовать другие флаги трассировки, которые не представлены в списке ниже, пожалуйста, сообщите об этом Джейми Редингу (Jamie.Reding@Microsoft.com) или Чарльзу Левину (Charles.Levine@Microsoft.com) до того, как вы опубликуете использование этих флагов.
Единственными поддерживаемыми для SQL Server 2008 флагами трассировки для TPC-E являются флаги: -T661 -T834 -T3502 -T8744.
Единственным поддерживаемыми для SQL Server 2008 параметрами запуска сервера для теста TPC-E являются параметры: -c -E -x, которые хорошо описаны в BOL.

661: Disable the ghost record removal process

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

834: Use Microsoft Windows large-page allocations for the buffer pool

Флаг трассировки 834 применяется в SQL Server 2008 для включения механизма распределения буферному пулу больших страницы памяти, которыми умеют оперировать последние версии Microsoft Windows. У разных аппаратных платформ может быть разный размер страниц, он может изменяться от 2 до 16 Мбайт. Большие страницы распределяются при запуске и сохраняются на протяжении всей жизни процесса. Флаг трассировки 834 повышает производительность, увеличивая эффективность TLB буфера процессоров.
Флаг трассировки 834 применим только к 64-битным версиям SQL Server 2008. Включить флаг трассировки 834 может только та учётная запись, для которой разрешена локальная политика "Lock pages in memory". Включать флаг трассировки 834 можно только при запуске SQL Server.
Флаг трассировки 834 может препятствовать запуску сервера, если память сильно фрагментирована и это мешает распределению больших страниц. Поэтому, флаг трассировки 834 безопаснее использовать на серверах, которые обслуживают только SQL Server 2008.
Для получения более подробной информации о поддержке больших страниц Windows, перейдите на следующую страницу сайта Microsoft Developer Network (MSDN): Large-Page Support.

3502: Log Database Checkpoint Start and End times in the SQL Server ErrorLog

Флаг трассировки 3502 не влияет на производительность, но он нужен для контроля выполнения эталонных тестов TPC. Этот флаг трассировки заставляет SQL Server регистрировать в SQL Server ErrorLog время начала и окончания работы системного процесса контрольной точки.

8744: Disable pre-fetching for ranges

Флаг трассировки 8744 отключает предварительную выборку для таких операторов, как "Nested Loops". Неуместное использование этого флага может спровоцировать дополнительные физические чтения, при реализации плана с оператором "Nested Loops".
Когда флаг трассировки 8744 включён при запуске сервер, он получает глобальный контекст. Когда он включен в сеансе пользователя, контекст ограничивается сеансом.

Флаги трассировки, рекомендованные Майкрософт для эталонного теста производительности TPC-C

gladchenko — Fri, 21 Aug 2009 15:07:00 GMT

По материалам статьи: Microsoft SQL Server 2005 TPC-C Trace Flags
Вольный перевод Александра Гладченко

652: Disable page pre-fetching
Флаг трассировки 652 отключает предварительную выборку страниц во время полных просмотров. Если флаг трассировки 652 включается при запуске SQL Server, он получает глобальный контекст. Если он взводится в пользовательском сеансе, то контекст ограничивается сеансом.

661: Disable the ghost record removal process
Флаг трассировки 661 отключает системный процесс удаления фантомных записей. Фантомные записи появляются в результате исполнения операций удаления, после которых удалённые записи могут оставаться в файле как фантомные записи. Через некоторое время, удаленные записи вычищаются процессом удаления фантомных записей. Когда этот процесс отключается, удаленные записи не вычищаются. Поэтому, место, которое занимают удаленные записи, не высвобождается. Это влияет на занимаемое данными место и на производительность операций просмотра.
Флаг трассировки 661 всегда действует в контексте всего сервера, т.е. имеет глобальный контекст. Вы можете включать флаг трассировки 661 при запуске сервера или в пользовательском сеансе.

834: Use Microsoft Windows large-page allocations for the buffer pool
Флаг трассировки 834 применяется в SQL Server 2005 для включения механизма распределения буферному пулу больших страницы памяти, которыми умеют оперировать последние версии Microsoft Windows. У разных аппаратных платформ может быть разный размер страниц, он может изменяться от 2 до 16 Мбайт. Большие страницы распределяются при запуске и сохраняются на протяжении вей жизни процесса. Флаг трассировки 834 повышает производительность, увеличивая эффективность TLB буфера процессоров.
Флаг трассировки 834 применим только к 64-битным версиям SQL Server 2005. Включить флаг трассировки 834 может только та учётная запись, для которой разрешена локальная политика "Lock pages in memory". Включать флаг трассировки 834 можно только при запуске SQL Server.
Флаг трассировки 834 может препятствовать запуску сервера, если память сильно фрагментирована и это мешает распределению больших страниц. Поэтому, флаг трассировки 834 безопаснее использовать на серверах, которые обслуживают только SQL Server 2005.
Для получения более подробной информации о поддержке больших страниц Windows, перейдите на следующую страницу сайта Microsoft Developer Network (MSDN): Large-Page Support.

836: Use the max server memory option for the buffer pool
Флаг трассировки 836 нужен для того, чтобы размер буферного пула при запуске SQL Server 2005 устанавливался на основе значения параметра глобальной конфигурации "max server memory", вместо того, чтобы использовать в качестве ориентира размер физической памяти. Вы можете использовать флаг трассировки 836, чтобы уменьшить число описателей буфера, которые распределяются при запуске службы в 32-разрядном режиме Address Windowing Extensions (AWE). Это позволяет предоставить в памяти больше места для заимствованных из буферного пула страниц.
Флаг трассировки 836 применяется только с 32-разрядным версиям SQL Server 2005, которым разрешено распределение страниц через окно AWE. Включить флаг трассировки 836 можно только при запуске.

1228 и 1229 - Enable lock partitioning and disable lock partitioning
По умолчанию, секционирование блокировок становится возможным, когда сервер имеет 16 или более процессоров. Иначе, секционирование блокировок заблокировано. Флаг трассировки 1228 включает секционирование блокировок для двухпроцессорных и более систем. Для отключения секционирования блокировок используется флаг трассировки 1229.
Секционирование блокировок полезно на мультипроцессорных серверах, где для некоторых таблиц очень высоки нормы блокировок. Включить флаги трассировки 1228 и 1229 можно только при запуске.

2301: Enable advanced decision support optimizations
Флаг трассировки 2301 включает дополнительную оптимизацию, которая улучшает работу запросов систем поддержки принятия решений, характерных большим объёмом затрагиваемых запросом данных.
Когда флаг трассировки 2301 включается при запуске сервера, он получает глобальный контекст, иначе, он будет иметь контекст сеанса.

Флаги трассировки, которые отключают некоторые кольцевые буферы

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

8011: Disable the ring buffer for Resource Monitor
Флаг трассировки 8011 отключает сбор дополнительной диагностической информации, относящейся к Resource Monitor. Информация этого кольцевого буфера можете использовать для диагностики состояния исчерпания памяти. Флаг трассировки 8011 всегда применяется ко всему серверу и имеет глобальный контекст. Вы можете включить флаг 8011 при запуске или в сеансе пользователя.

8012: Disable the ring buffer for schedulers
Флаг трассировки 8012 отключает кольцевой буфер планировщиков. Информация этого кольцевого буфера можете использовать для диагностики проблем планирования потоков процессорам. Например, информацию этого кольцевого буфера можно использовать для выявления проблем, проявляющихся как потеря отклика от SQL Server 2005. Включать флаг трассировки 8012 можно только при запуске сервера.

8018 and 8019: Disable the exception ring buffer and disable stack collection for the exception ring buffer Этот кольцевой буфер хранит данные о последних 256 исключениях, которые были зафиксированы для узла. Каждая запись содержит немного информации об ошибке и трассировку стека. Запись добавляется кольцевым буфером в момент срабатывания исключения.
Флаг трассировки 8018 отключает создание кольцевого буфера, и после этого информация об исключения не сохраняется. Флаг трассировки 8019 отключает сбор трассировок стека, когда фиксируется информация об исключении. Флаг 8019 нельзя использовать без предварительного включения флага трассировки 8018. Отключение этого кольцевого буфера затрудняет диагностику связанных с проблемами исключений, т.е. с внутренними ошибками сервера. Включить флаги трассировки 8018 и 8019 можно только при запуске сервера.

8020: Disable working set monitoring
SQL Server 2005 использует информацию о размере рабочего множества, когда возникает необходимость интерпретировать глобальные сигналы операционной системы о состояния оперативной памяти. Флаг трассировки 8020 исключает из этой интерпретации информацию о размере рабочего множества. Этот флаг трассировки применяется только к обычным страницам памяти. Например, этот флаг трассировки не относится к заблокированным страницам или большим страницам.
Флаг трассировки 8020 может быть полезен, когда операционная система постоянно урезает рабочее множество обслуживающего SQL Server 2005 сервера, и когда сервер неспособен высвобождать память из-за большой рабочей нагрузки. В таком случае, попытки мониторинга приведут к напрасному расходованию циклов процессора. Флаг трассировки 8020 нужно использовать с осторожностью и только после глубокого тестирования на реальных рабочих нагрузках. Кроме того, использовать флаг трассировки 8020 нужно после тщательного выбора значения глобального параметра "max server memory". Неуместное использование этого флага может привести к сильным листаниям.
Включить флаг трассировки 8020 можно только при запуске сервера.

8744: Disable pre-fetching for ranges
Флаг трассировки 8744 отключает предварительную выборку для таких операторов, как "Nested Loops". Неуместное использование этого флага может спровоцировать дополнительные физические чтения, при реализации плана с оператором "Nested Loops".
Когда флаг трассировки 8744 включён при запуске сервер, он получает глобальный контекст. Когда он включен в сеансе пользователя, контекст ограничивается сеансом.

Настройка Windows Server 2008/2003 x64 для обслуживания SQL Server 2008

gladchenko — Wed, 19 Aug 2009 14:37:00 GMT

По состоянию на 2009 год

Эта статья - вольная интерпретация рекомендаций: Microsoft, IBM, HP, Dell, QLogic, LSI, EMC, ACER, Bull, Fujitsu, Hitachi, NEC и Unisys. Некоторые рекомендуемые настройки требуют отдельного, обстоятельного разговора, и потому не включены в эту статью, а найти эти рекомендации можно в моём блоге.

Материал подготовил Александр Гладченко.

Обновление установки Windows

После установки Windows Server 2008 x64, установите последний сервисный пакет обновлений (Service Pack), обязательно установите выходившие после сервисного пакета исправления безопасности и последние версии драйверов и аппаратных прошивок.

Настройка файла подкачки Windows

Размер файла подкачки Windows в случае размещения на сервере только SQL Server не играет такой важной роли, как в типовых сценариях. SQL Server старается избегать листания. Размер файла подкачки можно выбрать небольшим, чтобы его было достаточно для формирования мини-дампов. Если сервер также обслуживает приложения, которые нуждаются в файле подкачки, размер его стоит выбирать в полтора раза больше, чем размер физической памяти сервера, но не более 50Гб. В случае монопольного владения ресурсами SQL Server, размещать файл подкачки можно на том же диске, где базируется операционная система. Минимальная активность работы с файлом подкачки не будет создавать конкуренцию другим задачам. В Windows Server 2003 имеется несколько предлагаемых вариантов настройки файла подкачки, он может настраиваться жёстко, может отсутствовать вообще или его размер будет управляться операционной системой. В последнем случае, Windows создаст файл подкачки, размер которого на один Мегабайт будет превышать размер физической памяти сервера. Такой выбор обусловлен тем, что ровно столько места необходимо для создания полного дампа памяти после аварии с выдачей "синего экрана". Если вас такой дамп не интересует, можно взять управление размером файла подкачки в свои руки.

Настройка размера системных журналов

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

Настройка локальных политик Windows

Ограничьте размер системных дампов физической памяти размером 64 Гб, большой размер дампа непрактичен с точки зрения отладки. Можно оптимизировать управление памятью и существенно сократить листания с помощью установки локальной политики для пользователя, от имени которого запускается служба SQL Server. Политика называется "Lock pages in memory", располагается она в узле "User rights assignment" локальных политик. Оболочку менеджера политик можно вызвать из командной строки, набрав там gpedit.msc и нажав ввод.

Ещё одной полезной локальной пользовательской политикой является мгновенная инициализация файла данных: "Выполнение задач обслуживания тома" (Perform Volume Maintenance). Для файлов журналов эта политика неприменима. Однако, включение этой политики хоть и позволяет сделать процесс создания файла данных, его приращение или восстановление практически мгновенным, оно чревато повышением рисков безопасности, т.к. инициализация не будет сопровождаться перезаписью новых файлов или приращиваемых к ним областей нулями. Т.е. существует риск, что расположенная в этих областях информация будет доступна для чтения и к удаленной ранее информации смогут получить доступ неавторизированные участники. Дополнительную информацию по безопасности этой операции можно найти в главе SQL Server Books Online: "Инициализация файлов базы данных". Одной из альтернатив этому методу, также очень быстро позволяющей выполнять задачи создания и приращения файлов, является размещение файлов баз данных на "сырых" разделах (RAW).

Настройка Панели Управления Windows

Чтобы исключить снижение производительности в режиме энергосбережения, в оснастке "Электропитание" (Power Options) нужно выбрать режим "Высокая производительность" (High Performance). Стоит также убедиться, что сервер никогда не будет переключаться в спящий режим.

На закладке "Визуальные эффекты" можно отключить расходование процессорного времени на некритичные для работы сервера эффекты, выбрав режим "Обеспечить наилучшее быстродействие" (Adjust for best performance).

Настройка конфигурации системы

Отключите контроль учётных записей UAC. Для этого нажмите кнопку "Пуск" (Start), в списке "Программы" (All Programs) перейдите к папке "Администрирование" (Administrative Tools) и запустите утилиту "Конфигурация системы" (System Configuration). Отключить UAC можно на закладке "Сервис" (Tools), выбрав из списка средств задачу: "Отключите контроль учётных записей UAC". Для применения изменений необходима перезагрузка системы.

Настройка кэширования ввода-вывода

Для включения кэширования операционной системой операций ввода-вывода логических дисков воспользуйтесь оснасткой Disk Management или Device Manager, перейдя в ней в узел Disk Drives. Для каждого настраиваемого устройства логического диска нужно выбрать Свойства (Properties) и перейти на закладку Policies. Для индивидуальной настройки дисков лучше подходит оснастка Disk Management, там настройки выполняются из свойств дисков, которые вызываются в графической, нижней части окна оснастки. Включение чекбокса "Enable write caching on the disk" разрешает кэширование записи на диск. После пометки этого чекбокса становится доступен для пометки второй чекбокс: "Enable advanced performance". Включение обеих чекбоксов не только разрешает кэширование, но и заставляет операционную систему изымать из запросов ввода-вывода команды прямой записи на диск и сброса дискового кэша. Не рекомендуется включать эти чекбоксы если аппаратные кэши не имеют защиты от потери электропитания.

Если логический диск представлен внешним дисковым массивом, который оснащён аппаратным кэшем с батарейкой, можно не помечать второй чекбокс: "Enable advanced performance". Однако, пометка этого чекбокса может повысить производительность записи в журнал транзакций.

Для вступления в силу изменений настроек кэша дисков перезагрузки сервера не требуется.

Настройка параметров сетевых плат

В окне Local Area Connection Properties мастера Network Connections панели управления Windows можно вызвать окно параметров сетевой платы, если нажать кнопку Configure. В этом окне на закладке Advanced перечислены несколько параметров, некоторые из них могут существенно влиять на производительность обмена данными по сети.

Link Speed and Duplex

Лучшим решением зачастую является согласованный выбор полнодуплексного режима связи в свойствах сетевой платы и в свойствах порта сетевого коммутатора/концентратора. Это не означает, что выбор автоматической подстройки там и тут будет работать хуже, просто история помнит такие случаи…

Существует ещё ряд распространённых параметров, настройка которых может помочь поднять производительность сетевого интерфейса (например, Receive Buffers, Coalesce Buffers, Offload features и т.п.). Чтобы выдрать верные значения для подобных параметров, обратитесь к документации производителя сетевой платы.

Для того чтобы исключить возможность отключения сетевого интерфейса в целях оптимизации энергопотребления сервера, стоит на закладке Power Management убрать пометку чекбокса "Allow the computer to turn off this device to save power".

Отключение неиспользуемых протоколов

Для того чтобы убрать незначительную дополнительную нагрузку, создаваемую для обслуживания неиспользуемых сетевых протоколов, предлагается отключить или деинсталлировать такие протоколы. Например, первым кандидатом на отключение является TCP/IPv6, в силу пока ещё своей малой распространённости. Сделать это можно в окне Local Area Connection Properties мастера Network Connections панели управления Windows.

SQL Server также использует по умолчанию несколько протоколов, отключить лишние из них позволяет оснастка SQL Server Configuration Manager, в которой протоколы перечислены в узле "Сетевая конфигурация SQL Server".

В большинстве случаев и в свойствах сетевого интерфейса и в протоколах SQL Server достаточно ограничится использованием протокола TCP/IPv4.

Если же требуется поддерживать несколько протоколов, стоит задать приоритеты использования протоколов и их провайдеров в окне Advanced Settings мастера Network Connections.

Настройка антивирусного программного обеспечения

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

Необходимо настроить исключение сканирования файлов баз данных, журналов транзакций и резервных копий, которые типично имеют разрешения: mdf, ldf, ndf, bak и trn. Это позволит предотвратить повреждение этих файлов при попытке со стороны SQL Server их открытия, когда они уже открыты для проверки антивирусным ПО. Кроме того, необходимо принять меры для защиты каталогов полнотекстового поиска и содержащих данные Analysis Services от повреждений, связанных с активностью антивирусного программного обеспечения. Исключите также папку журналов SQL Server (MSSQL\Log), журнал ошибок открыт постоянно и в него может выводиться много событий. Если антивирусное ПО планируется использовать совместно с SQL Server работающем в кластере, нужно исключить сканирование кворум - диска и каталога: "c:\Windows\Cluster". Для получения более подробной информации о требованиях к настройкам антивирусного ПО обратитесь к статье базы знаний Майкрософт: Guidelines for choosing antivirus software to run on the computers that are running SQL Server.

Во избежание вмешательства в работу служб SQL Server и для предотвращения увеличения времени их запуска из-за сканирования антивирусным ПО, рекомендуется исключить из проверки службы SQL Server. Наиболее распространенными в использовании являются службы: sqlservr.exe, sqlagent.exe, sqlbrowser.exe и sqlwriter.exe

Дополнительные рекомендации: Рекомендации по использованию антивирусных программ для компьютеров под управлением Windows Server 2003, Windows 2000 и Windows XP.

Управление памятью в SQL Server

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

Настройка параметров глобальной конфигурации SQL Server

affinity I/O mask

Этот параметр глобальной конфигурации позволяет изолировать обслуживание ввода-вывода данного экземпляра SQL Server одним или несколькими ядрами процессоров. Привязка ввода-вывода позволяет закрепить за вводом-выводом фиксированное число планировщиков. По умолчанию и в большинстве случаев маску привязки процессоров к вводу-выводу устанавливают в ноль, разрешая SQLOS самой управлять подобной привязкой. Чаще всего это позволяет получить максимальную производительность, однако, для некоторых типов рабочей нагрузки (которым характерны высокие нормы ввода-вывода), производительность может быть выше, если жёстко привязать ввод-вывод к одному или более ядру. Например, можно выделить одно ядро для ввода-вывода, как это показано ниже, в примере сценария для 16-ти ядерного сервера:

exec sp_configure 'affinity mask',0xFFFE
exec sp_configure 'affinity I/O mask',0x0001

Не обязательно для ввода-вывода выделять самое первое по порядку ядро. Это может быть ядро из того NUMA-узла, в домене близости которого находится адаптер ввода-вывода.

network packet size

Следующим параметром глобальной конфигурации, изменение которого может в некоторых случаях способствовать повышению производительности приложений баз данных, является "network packet size (B)". Увеличение размера сетевого пакета до 8192 Байт может позволить добиться выигрыша за счёт лучшего выравнивания размера пакета с размером страницы SQL Server, которая равна 8 КБ. Однако, следует учитывать, что значение этого параметра по умолчанию (4096Б), является лучшим для большинства приложений. Только тестирование позволит выбрать для этого параметра оптимальную установку.

Настройка ключей системного реестра Windows

Важно! Эта глава содержит сведения об изменении реестра. Перед изменением реестра необходимо создать его резервную копию. Убедитесь в том, что знаете, как восстановить реестр в случае возникновения проблемы. Дополнительные сведения о создании резервной копии, восстановлении и изменении реестра см. в следующей статье базы знаний Майкрософт: Сведения о реестре Windows для опытных пользователей.

UseLargePages

Включение поддержки больших страниц может оказаться полезным для тех систем с SQL Server x64, которые оснащение большим объёмом оперативной памяти. Большие страницы способствуют повышению производительности за счёт увеличения TLB буфера процессора. Большие страницы могут использоваться для буферного пула и для кодовых страниц SQL Server. Для включения больших страниц на уровне SQL Server нужно задать флаг трассировки -T834 (это можно сделать через стартовые параметры). Кроме того, следует добавить ключ системного реестра. Содержимое reg-файла для добавления показано ниже:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Image File Execution Options\sqlservr.exe]
"UseLargePages"=dword:00000001

Для вступления изменений в силу потребуется перезагрузка системы.

LargeSystemCache/Size и IdleFrom0Delay

Установив значение LargeSystemCache в 0, тем самым устанавливается стандартный размер кэша файловой системы, который равен приблизительно 8 Мб, максимальный размер кэша файловой системы не будет превышать 512 Мб. Эта установка рекомендуется для таких программ, которые осуществляют кэширование памяти самостоятельно, и к таким программам относится SQL Server. Ниже показан reg-файла для задания такой установки:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management]
"LargeSystemCache"=dword:00000000

То, насколько агрессивно будет заниматься физическая память под задачи файлового кэша, зависит от установки следующего ключа реестра:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\lanmanserver\parameters]
"Size"=dword:00000001

Возможные значения: 1-вяло, 2-сбалансировано, 3-агресивно. Для установок с малой нагрузкой на файловый кэш, вполне достаточно 1.. Другие установки свойственны файловым серверам разного масштаба, впрочем, для тестов TPC-E часто выбирают 3.

Отключить режим экономии энергии, который тоже может замедлять некоторые операции, можно с помощью ключа IdleFrom0Delay. Сделать это можно так:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management]
"IdleFrom0Delay"=dword:00000000

IoPageLockLimit, DisablePagingExecutive и DontVerifyRandomDrivers

В одном из документов по оптимизации мне попадалась следующая формула: "IoPageLockLimit = (RAMMb - 65) * 1024". С помощью ключа IoPageLockLimit можно повлиять на то, сколько байт система будет читать или писать на логичекский диск за один раз.

Когда оперативной памяти предостаточно, с помощью установки DisablePagingExecutive можно не позволять SQL Server вытеснять в файл подкачки компоненты драйверов привилегированного и непривилегированного режимов, как и компоненты самого ядра ОС. Установка DontVerifyRandomDrivers в единицу позволяет сэкономить несколько процессорных циклов за счёт отключения отладочной проверки драйверов.

Вот как могут выглядеть значения этих ключей на практике:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management]
"DisablePagingExecutive"=dword:00000001
"DontVerifyRandomDrivers"=dword:00000001
"IoPageLockLimit"=dword:00d9bc00

CountOperations

Параметр CountOperations позволяет отключить сбор данных по некоторым счётчикам производительности, которые относятся к запросам ввода-вывода дисковой подсистемы и сетевых интерфейсов. Чтобы это сделать, нужно в ключе системного реестра "I/O System" установить значение 0 для следующего параметра:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\I/O System]
"CountOperations"=dword:00000000

Требуется перезагрузка.

NumberOfRequests и MaximumSGList

Эта пара ключей системного реестра предназначена для управления драйвером минипорта в момент инициализации последнего.

Увеличивая значение NumberOfRequests может способствовать повышению производительности обслуживания в Windows запросов дискового ввода-вывода, адресованных логическим дискам, и бывает эффективно только если эти логические диски являются аппаратными RAID-массивами, которые обладают возможностью распараллеливания запросов ввода-вывода. Рекомендованное значение можно найти в документации производителя FC-адаптера или RAID-контроллера. Увеличивать значение нужно осторожно, т.к. большое значение может привести даже к отказу системы. Например, для HBA адаптера QLogic, управляемого драйвером "QLogic Fibre Channel Miniport Driver", в документации не рекомендуется превышать значение 150. Новое значение вступает в силу после перезагрузки системы или, в некоторых случаях, достаточно перезапустить адаптер (заблокировать/разблокировать).

Ключ MaximumSGList позволяет изменять используемый по умолчанию размер пакета передачи данных по шине (64Кб), который актуален для команд интерфейса SCSI. Если установить значение 255, то размер передаваемого одной командой объёма данных будет равняться мегабайту. Современные адаптеры умеют объединять до 265 сегментов данных, каждый по 4096 байт, что в сумме может дать размер одной передачи до 1048576 байт. Этот параметр широко используется для повышения эффективности использования ленточных накопителей, а также для оптимизаций таких задач SQL Server, которые оперируют большими запросами ввода-вывода, например, резервное копирование и восстановление.

В описаниях тесов TPC-C встречается установка обоих ключей в значение 255, как это показано в примере ниже:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\ql2300\Parameters\Device]
"DriverParameter"=""
"BusType"=dword:00000006
"NumberOfRequests"=dword:000000ff
"MaximumSGList"=dword:000000ff
"CreateInitiatorLU"=dword:00000001
"DriverParameters"="UseSameNN=1;buschange=0"

IdlePrioritySupported

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

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Enum\SCSI\Disk&Ven_IBM&Prod_1726-4xx__FAStT\5&22c73432&0&000000\Device Parameters\Classpnp]
"IdlePrioritySupported"=dword:00000000

Ключ Classpnp скорее всего придётся добавить. Подобные тонкие настройки были мной замечены у IBM в тестах TPC-E.

TCPWindowSize

В Windows 2008 этот ключ больше не используется. В более ранних версиях увеличение размера окна может повысить эффективность сетевого трафика. Рекомендуемое значение (64240) для ключа TCPWindowSize представлено ниже:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters]
"TCPWindowSize"=dword:0000faf0

Подробности можно узнать в статье Базы Знаний Майкрософт: Description of Windows 2000 and Windows Server 2003 TCP Features.

Если размер окна в 64240 Байт тоже ограничивает производительность сети, существует возможность увеличения размера окна до одного Гигабайта. Ключ реестра TCP1323Opts разрешает динамическое мастабирование окна. Рекомендуемым значением для этого ключа является: 0x3.

Для дальнейшей оптимизации производительности сетевых интерфейсов попробуйте использовать следующие ключи системного реестра Windows, описание которых легко найти в Базе Знаний Майкрософт: TCPMaxConnectRetransmissions, TCPMaxDataRetransmissions, TCPTimedWaitDelay, MaxUserPort, MaxHashTableSize, NumTcbTablePartitions, TcpAckFrequency, MTU и EnablePMTUDiscovery.

MSMQ 3.0 в Кластере

gladchenko — Thu, 25 Jun 2009 09:14:00 GMT

Здесь будет идти речь о кластере 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 в качестве виртуального сервера выполните следующие действия:

Нажмите Пуск (Start), выберете пункт Программы (All Programs), пункт Администрирование (Administrative Tools), и затем запустите программу Cluster Administrator.
В пункте Open Connection to Cluster нужно выбрать соответствующее имя кластера.
В дереве кластерных групп нужно выбрать ту кластерную группу, которой должен принадлежать ресурс MSMQ.
После выбора группы, в пункте меню File нужно выбрать New, а затем щелкнуть Resource.
В мастере New Resource Wizard заполните поля Name и Description, и выберете Message Queuing в списке Resource type. Потом нажмите Next.
В поле Group укажите ту группу, которой Вы хотите, чтобы принадлежал ресурс MSMQ.
На закладке возможных владельцев, добавьте те узлы, которые смогут обслуживать ресурс MSMQ.
На странице зависимостей (Dependencies в Available resources) добавьте ресурс сетевого имени и физического диска.
Нажмите Finish.
В дереве кластерных групп нужно выбрать Resources, а затем в окне подробностей выделить ресурс MSMQ.
В меню File, выберите Bring Online.
В дереве кластерных групп нужно выбрать ту кластерную группу, которой должен принадлежит ресурс MSMQ. Нажать New, а затем щелкнуть Resource, чтобы запустился мастера создания нового ресурса.
В текстовом поле Resource Name введите название: "Computer Management".
В списке Resource Type, выберите Generic Application, и затем нажмите Next.
В поле Group укажите ту группу, которой принадлежит ресурс MSMQ.
На закладке Possible Owners, выберите возможных владельцев и нажмите Next.
На странице зависимостей добавьте ресурс сетевого имени и ресурс MSMQ, который Вы перед этим создавали. Нажмите Next.
В командной строке введите: mmc compmgmt.msc
В Current Directory введите: %windir%\system32
Нужно выбрать Use Network Name для имени компьютера, и Allow application to interact with desktop. Нажмите Next, а затем Finish.
В дереве кластерных групп нужно выбрать Resources, а затем в окне подробностей выделить ресурс Computer Management, который Вы только что создали.
В меню 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).

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

Alexander Gladchenko

Обновите свои новостные каналы лентами из SQL Server BLOGROLL 2010

Высокая доступность в репликации SQL Server 2008 с зеркалированием и доставкой журналов

Высокая доступность в репликации SQL Server 2008 с зеркалированием и доставкой журналов

Совместимость Доставки журналов с Зеркальным отображением и Репликацией

Влияние зеркалирования на работу Агента чтения журнала

Изменение поведения Агента чтения журнала при установке флага трассировки 1448

Влияние отказа зеркалирования на работу Агента чтения журнала

Как влияет нарушение зеркалирования на работу Агента чтения журнала

Настройка периода хранения Распространителя

Типы ручной синхронизации Подписчика

Примеры

Зеркальное отображение издаваемой базы данных

Шаг 1. Создание Публикации

Шаг 2. Настройка Доставки журналов для инициализации Подписчика

Шаг 3. Настройка Подписки

Шаг 4. Зеркалирование публикуемой базы данных

Шаг 5. Переключение Публикации на зеркальную копию

Выводы

Сравнения списка объектов SQL Server в Powershell на примере сравнения логинов на двух серверах

Вариант стратегии быстрого и надежного резервного копирования/восстановления VLDB по сети

HDD DEAD?!

Tips for DBA: Замена для стандартного "Back Up Database Task"

Как справиться с PAGELATCH при больших INSERT-нагрузках

Query Processor Modeling Extensions

Флаги трассировки, рекомендованные Майкрософт для эталонного теста производительности TPC-E

Флаги трассировки, рекомендованные Майкрософт для эталонного теста производительности TPC-C

Настройка Windows Server 2008/2003 x64 для обслуживания SQL Server 2008

Обновление установки Windows

Настройка файла подкачки Windows

Настройка размера системных журналов

Настройка локальных политик Windows

Настройка Панели Управления Windows

Настройка конфигурации системы

Настройка кэширования ввода-вывода

Настройка параметров сетевых плат

Отключение неиспользуемых протоколов

Настройка антивирусного программного обеспечения

Управление памятью в SQL Server

Настройка параметров глобальной конфигурации SQL Server

Настройка ключей системного реестра Windows

Рекомендуемые к отключению службы

MSMQ 3.0 в Кластере