SQL Admin: Ноябрь 2008

По материалам статьи Пола Ибисона и Хилари Коттер

По материалам статьи Матузами Ананты Кумара

Дмитрий Артемов

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

Дело в том, что большой авторский коллектив с портала sql.ru готовил сборник статей для книги, посвященной репликации в SQL Server. По независящим от них причинам издать книгу в наше тяжелое (с экономической точки зрения) время не удалось, и переведенный комплект предложили нам. Естественно, отказываться от такой прекрасной подборки качественно переведенных статей было бы глупо, вот мы и не отказались.

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

Статьи охватывают все аспекты механизма репликации, реализованные в SQL Server 2000/2005, и мы уверены, будут очень полезны для разработчиков и администраторов БД, интересующихся в силу служебных обязанностей или просто для расширения кругозора, этой важной, но мало описанной темой.

Изменение столбца таблицы, участвующей в репликации

По материалам статьи Пола Ибисона (Paul Ibison)

Иногда структура участвующей в репликации таблицы нуждается в изменении. Для этого может быть несколько причин — изначально неправильный выбор типа данных, отсутствие значения по умолчанию или необходимость переименовать столбец. Попытка изменить структуру таблицы непосредственно закончится ошибкой: «Cannot alter/drop the table ‘tablename’ because it is being published for replication» (Невозможно изменить/удалить таблицу ‘имя таблицы’, так как она опубликована для репликации).

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

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

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

По материалам статьи Пола Ибисона (Paul Ibison)

Статья рассказывает о том, как настроить топологию репликации.

Здесь P1 и P2 — два разных издателя, а S — центральный подписчик. Это достаточно распространенная ситуация, например, подписчик может находиться в главном офисе, а издатели — в отдельных филиалах компании. В таких ситуациях часто требуется централизованно хранить обобщенные данные для создания отчетов.

В этой статье описывается механизм реализации приведенной топологии с использованием транзакционной репликации (transactional replication). Важно отметить, что репликация транзакций не является единственно возможной. Альтернативой ей может выступать репликация слиянием (merge replication). При этом роли серверов будут изменены, то есть S соответственно становится издателем, а P1 и P2 становятся подписчиками. Это возможно, потому что в репликации слиянием метафора «издатель/подписчик» не так сильно выражена и разница между подписчиком и издателем не так очевидна, как в репликации транзакций. Однако выбор этой альтернативы означает использование репликации слиянием там, где это в действительности не требуется, и если реплицируется много данных, то производительность может излишне пострадать. Итак, давайте рассмотрим настройку транзакционной репликации для реализации показанной выше топологии.

Обеспечение безопасности репликации SQL Server 2000

По материалам статьи Марцина Полихта (Marcin Policht)

Безопасность репликации — очень важная задача, которую нужно очень тщательно планировать. Давайте начнем с замечания о том, что процедуры создания/администрирования издателей, распространителей и подписчиков (включая публикацию базы данных для репликации) доступны только для серверной роли sysadmin. После определения участников репликации только члены роли sysadmin и члены роли db_owner публикуемой базы данных могут создавать и настраивать публикации. Мониторинг репликации доступен для членов серверной роли sysadmin и роли replmonitor базы данных distributor, которая создается автоматически, вместе с распространителем.

Ошибка, возникающая при вставке дублированных значений в ключевые поля в процессе репликации

По материалам статьи Энди Уоррена (Andy Worren)

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

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

По материалам статьи Пола Ибисона
(Paul Ibison) и Хилари Коттер (Hilary Cotter)

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

Вопрос: Какие есть «за» и «против» перезапуска агента чтения журнала?

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

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

Если остановить работу агента чтения журнала, увеличится время тиражирования изменений. Если такая остановка длится очень долго, время «жизни» тиражируемых команд может превысить установленный для публикации период задержки (retention period). Кроме того, файл журнала регистрации транзакций не будет полностью копироваться во время резервного копирования журнала транзакций (а значит и освобождаться от старых транзакций), пока агент чтения журнала не пометит эти транзакции как прочитанные.

Вопрос: Агент чтения журнала завершает работу по ошибке, и в хронологии его работы присутствует сообщение: «No such interface».

Ответ: Вы должны повторно зарегистрировать исполняемый файл агента чтения журнала. Попробуйте для этого выполнить команду: regsvr32 logread.exe; вам, возможно, также придется зарегистрировать все, что содержится в каталоге: C:\Program Files\Microsoft SQL Server\90\Com.

Вопрос: Запускаются не все агенты чтения журнала — что с этим можно сделать?

Ответ: Увеличьте установленное значение для max_worker_threads в таблице syssubsystems базы данных msdb.

Вопрос: (SQL Server 2000/2005) Я получаю ошибку 14100: «Specify all articles when subscribing to a publication using concurrent snapshot processing».

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

Есть два недокументированных способа решения этой проблемы: можно обойти проверку на конкурентность, задав при добавлении статьи в подписку параметр @reserve = ‘internal’, после чего агент моментальных снимков должен будет создать новый снимок, включающий новую статью. Второй способ заключается в том, что можно изменить свойство immediate_sync в таблице syspublications, задав ему значение «0» (см. sp_changepublication).

Еще одним официально рекомендуемым способом, является изменение значение свойства sync_method с «concurrent» на «database snapshot» (только для редакции Enterprise Edition) или «native» (таблица будет заблокирована на все время создания снимка). Изменение свойства sync_method приведет к принудительной инициализации всех подписок. В качестве альтернативного метода, можно просто создать другую публикацию и использовать ее вместо предыдущей публикации.

Вопрос: (SQL Server 2005) Есть настроенная репликация, которая прекрасно работает в среде серверов SQL Server 2000, но когда те же самые сценарии используются для SQL Server 2005, настройка заканчивается ошибкой «Incorrect syntax near ‘)’».

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

Эта проблема связана с изменением заданного по умолчанию поведения в SQL Server 2005, которое отличается от того, которое было в SQL Server 2000. Тиражирование столбца с типом timestamp теперь осуществляется в столбцы того же типа, а не в столбцы с типом binary(8), как это было в предыдущих версиях. В итоге, прикладная логика процедур установки репликации просто не может корректно обработать случаи, в которых столбец timestamp является частью первичного ключа и когда такой столбец копируется на подписчиков с типом timestamp. Если взглянуть на проблему шире, становится понятно, что репликация транзакций просто не может ничего сделать, если столбец timestamp задействован в первичном ключе и тиражируется как столбец timestamp. Значения timestamp будут разными на издателе и подписчиках, а репликация транзакций основана на том принципе, что значения первичных ключей должны быть одинаковы и там и тут. К счастью, проблема может быть легко разрешена, если при вызове системной процедуры sp_addarticle не задавать в устанавливаемой параметру @schema_option битовой маске бит 0x08, что автоматически приведет к тому, что столбцы timestamp будут реплицироваться как binary. Такого же результата можно достичь, если задать опцию «Convert timestamp to binary» в окне свойств статьи. Оба варианта решения проблемы позволяют вернуться к принятому в SQL Server 2000 поведению.

Вопрос: Как почистить журнал транзакций? После восстановления базы данных на другом сервере, при попытке высвобождения свободного места в журнале регистрации транзакций, выводится следующее сообщение об ошибке: «The log was not truncated because records at the beginning of the log are pending replication».

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

Ответ: Вот наш метод использования DBCC OPENTRAN: если агент чтения журнала транзакций (log reader agent) успевает обработать то, что находится в журнале транзакций, тогда порядковый номер в журнале (LSN) самой старой транзакции, которая еще не передана подписчику, будет такой: (0:0:0).

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

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

Oldest active transaction:
SPID (server process ID) : 55
UID (user ID) : 1
Name          : mytran
LSN           : (10:391:1)
Start time    : May 16 2005 10:10:28:920AM

Replicated Transaction Information:
        Oldest distributed LSN     : (10:388:4)
        Oldest non-distributed LSN : (10:390:1)

Для того чтобы определить, какая выполняется команда TSQL, нужно воспользоваться командой dbcc inputbuffer (55), а затем уже принимать решение, нужно ли прерывать данный процесс. Числа в скобках (10:388:4) — это порядковые номера журнала транзакций (LSN). Вы можете выполнить запрос:

Результатом исполнения этого запроса будет список LSN, а для получения более детальной информации, можно воспользоваться программой LogExplorer.

Вопрос: Как определить, какие команды ожидают загрузки?

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

select transaction_timestamp from subscriberdatabasename..MSreplication_subscriptions

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

Вопрос: Как понять, когда лучше использовать комбинацию sp_dropsubscription и sp_addsubscription вместо комбинации sp_dropsubscription и sp_droparticle, с последующим исполнением sp_addarticle и sp_addsubscription?

Ответ: Второй вариант лучше использовать при необходимости внесения в репликацию несложных изменений, таких, например, как изменение типа данных одного или нескольких полей. Первый вариант с использованием хранимых процедур sp_dropsubscription и sp_addsubscription применим тогда, когда вы хотите произвести изменения, но не хотите их реплицировать. Позже вы сможете пересоздать подписку и сгенерировать новый снимок. Хороший пример для использования этого варианта — удаление всех строк из таблицы, поскольку без использования этого метода, такое действие привело бы к удалению каждой отдельной строки на подписчике, а данный метод позволяет избежать этого.

Вопрос: Как избавиться от ошибки «Violation of Primary Key constraint ‘PK__@snapshot_seqnos__{UniqueNumber}’. Cannot insert duplicate key in object ‘#
{UniqueNumber}’»?

Ответ: Необходимо установить последний сервисный пакет. Имеются отзывы, что и этого не достаточно, потому что тип параметра subscription_seqno — varbinary (16), а не varchar (16). Нужно заменить тип данных столбца на varbinary (16), и тогда все будет работать правильно.

Вопрос: Как определить, какой подписчик еще не получил данные?

Ответ: Этот сценарий показывает подписчиков, у которых велико количество ожидающих передачу команд:

select agent_id from msdistribution_status
group by agent_id
having sum(undelivCmdsInDistDB) >1000
order by sum(undelivCmdsInDistDB) desc

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

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

select article_id, max(undelivcmdsindistdb)
from Msdistribution_status
group by article_id

Вопрос: Как правильно сделать резервную копию реплицируемой базы данных?

Ответ: Прочитайте в BOL статьи «Strategies for Backing Up and Restoring Transactional Replication» и «Backing Up and Restoring Replication Databases». Для стандартной репликации транзакций имеется возможность использовать опцию «sync with backup». При использовании этой опции, транзакции не попадают в базу распространителя, пока они не будут включены в резервную копию реплицируемой базы данных (то есть в системной таблице msrepl_commands не будет записей о таких транзакциях, которые не были бы уже помещены в резервную копию на издателе). Однако это ведет к увеличению задержек в передаче данных средствами репликации, поскольку при использовании отправки журналов (Log Shipping) выполнение резервной копии не может выполняться чаще, чем раз в минуту. Если вы не используете эту опцию, после восстановления из резервной копии нужно будет проигнорировать некоторые связанные с транзакциями ошибки, сделать это нужно будет вручную (используя параметр SKIPERRORS). Что же касается восстановления резервных копий подписчика, это обычно не является настолько критичным. Они должны быть восстановлены на момент времени, предшествующий восстановлению дистрибутора, и затем уже команды будут тиражироваться агентом дистрибуции — подробности можно прочитать в BOL, а также в статье «Резервное копирование в репликации SQL Server». Или в качестве альтернативы описанным выше методам, можно просто заново инициализировать подписчиков.

Вопрос: Как ежедневно добавлять таблицы в БД и реплицировать их без запуска агента создания снимка?

Ответ: Это возможно, если предполагается, что данные уже существуют на подписчике. Для этого при добавлении подписки нужно использовать системную хранимую процедуру sp_addsubscription с параметром @sync_type = ‘none’, и нужно вручную добавить на подписчике пользовательские хранимые процедуры (используя для этого процедуру sp_scriptpublicationcustomprocs). Тогда не понадобится генерировать новый снимок.

EXEC sp_addarticle
@publication = N’NorthwindOIncludeDRINonCLustered’,
@article = N’CategoriesArticle’,
@source_object = N’Categories’,
@destination_table = N’Categories’
GO
EXEC sp_addsubscription
@publication = N’NorthwindOIncludeDRINonCLustered’,
@article = N’CategoriesArticle’,
@subscriber = N’HOME-WIN2K’,
@destination_db = N’Pubs’,
@sync_type = N’none’
GO

Вопрос: Как динамически секционировать данные?

Ответ: Используйте подписку с трансформацией. В таких подписках для преобразования данных используются DTSпакеты, и для каждого из подписчиков может быть создан свой пакет. При создании публикации убедитесь, что выбраны опции ‘Show Advanced Options’ и ‘Yes, Transform the Data’.

После создания публикации создайте для нее DTSпакет. Особенностью программы Enterprise Manager является то, что эта опция доступна в папке Publication опубликованной базы данных, но недоступна в папке Publishers в оснастке Replication Monitor. Таким образом, щелкните правой кнопкой мыши на публикации и выберите «Define Transformation of Published Data». В этом мастере создается задача «Data Driven Query». Убедитесь в том, что используется сценарий с типом VBScript, и измените этот сценарий, добавив возможность фильтрации строк, как это показано в следующем примере:

if DTSSource(“RegionID”) = 1 then
Main = DTSTransformStat_OK
else
Main = DTSTransformStat_SkipRow
end if

Вопрос: Что происходит, когда при выполнении транзакции на издателе происходит ошибка, применяется ли она на подписчике?

Ответ: Результат выполнения транзакции на издателе можно отслеживать с помощью функции @@error, а затем при необходимости делать откат транзакции, но транзакция все равно применится на подписчике. Данную ситуацию можно отследить, если установить уровень изоляции транзакций Serializable.

Такую возможность важно предусмотреть заранее, потому что даже если вы получите ту же самую ошибку в транзакции на подписчике и откатите транзакцию там, ошибка все равно будет зарегистрирована, и агент распространителя (Distribution Agent) завершит работу с ошибкой. Чтобы обойти эту проблему, можно использовать параметр SkipErrors, но в идеале, транзакция, выполнение которой привело к ошибке, не должна быть послана от издателя подписчику.

Вопрос: Как в репликации транзакций прочитать транзакции не в двоичном формате?

Ответ: Транзакции записываются в системную таблицу MSrepl_commands, чтобы их посмотреть, нужно запустить процедуру sp_browsereplcmds. В случае использования службы очередей, для того чтобы прочитать данные из таблицы MSreplication_queue применяйте хранимую процедуру sp_replqueuemonitor, и системную процедуру sp_browsereplcmds для того, чтобы посмотреть входящие в транзакции команды, что бывает нужно при разрешении конфликтов.

Вопрос: Как реплицировать изменения командой Update? Когда я выполняю команду Update, на подписчике она разбивается на Delete и Insert. Почему?

Ответ: Это случается при изменении поля, которое является частью уникального индекса. Используйте флаг 8207, чтобы команда UPDATE применялась как UPDATE на подписчике, но это действительно только для случаев, когда UPDATE — одна команда в транзакции. Прочитайте статью базы знаний Майкрософт: «INF: New Trace Flag to Enable Singleton Update for Transactional Replication».

Ответ: Пользователи и другие административные объекты не могут быть реплицированы, поскольку нельзя реплицировать системные таблицы. Если это необходимо, вы можете:

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

Вопрос: Как предотвратить удаления, приходящие от подписчика?

Ответ: При настройке издателя это сделать довольно просто — нужно только в свойствах статьи изменить правило для этой команды на NONE. Если ваш издатель уже настроен, это вызовет повторную инициализацию всех подписчиков. Если это только временная мера и вы не хотите повторно инициализировать подписчиков, можно изменить хранимую процедуру, связанную с удалением данных на подписчиках, чтобы предотвратить удаление, хотя, очевидно, это приведет к потере синхронизации данных.

Вопрос: При создании подписчиков с немедленным обновлением выдается ошибка «Login failed for ‘sa’». Почему?

Ответ: При подключении к издателю, подписчики с немедленным обновлением используют удаленный вызов процедур (RPC). По умолчанию, для таких подключений используется учетная запись пользователя «sa», с пустым паролем, что часто становится причиной ошибок. Чтобы избавиться от этой ошибки, используйте системную хранимую процедуру sp_link_publication с параметром @security_mode = 2. Другие параметры этой процедуры описаны в BOL.

Вопрос: С чем связана эта ошибка: «Could not find stored procedure ‘sp_MSupd_Region’»?

Ответ: Вы можете получить эту ошибку, если в процедуре sp_addsubscription используете параметр @sync_type = none (или выбираете аналогичную опцию в мастере создания публикации), а в окне инициализации подписчиков выбираете «no, the subscriber already has the schema and data». При использовании этой опции, системные таблицы репликации на подписчике будут созданы в любом случае, но вам придется вручную создать на подписчике хранимые процедуры. Иными словами, вы можете получить эту ошибку при изменении записей на издателе. Используйте sp_scriptpublicationcustomprocs (предполагается, что у вас установлен sp1) для создания сценариев этих процедур и выполните эти сценарии на подписчике.

Вопрос: Я использую стандартную репликацию транзакций на промышленном сервере. Когда я тестирую ее работу, поле identity не работает. Почему?

Ответ: Если при инициализации у вас используется опция «nosync», может получиться, что поле имеет атрибут «Identity — Yes» (not for replication), но значение identity не увеличивается в процессе репликации и DBCC CHECKIDENT не всегда работает для таких полей. Это может стать одной из причин, чтобы рассмотреть возможность использования обновляемых подписчиков с использованием очередей.

Вопрос: Какие изменения в схеме допустимы на издателе?

1. Изменение размера поля недопустимо, используется повторная инициализация или использование комбинации процедур sp_repladdcolumn и sp_repldropcolumn.

2. Допустимо удаление ограничения участвующей в репликации таблицы. Ограничение будет удалено для таблицы издателя, но эти изменения не реплицируются подписчикам.

3. Создание нового ограничения на таблицу допустимо, но оно не будет реплицировано.

4. Удаление индекса опубликованной таблицы допустимо, но не реплицируется.

5. Создание нового индекса для опубликованной таблицы допустимо, но не реплицируется.

Для тех действий, которые допустимы на издателе, но не реплицируются на подписчиков, можно использовать механизм «OnDemand Script Execution» (или посредством связанных серверов и т. п.).

По материалам статьи Матузами Ананты Кумара)

В среде OLTP (OnLine Transaction Processing) вы часто встречаете пакеты заданий (batch jobs), которые перемещают хронологию данных в архивные таблицы. Кроме того, часто встречаются пакетные задания, которые выполняют очистку OLTP таблиц от устаревших данных.

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

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

Репликация столбцов Identity в SQL Server — Настройка репликации

По материалам статьи Матузами Ананты Кумара (Muthusamy Anantha Kumar)

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

Содержание номера за Ноябрь 2008 год

SQL Server
для администраторов

Ноябрь 2008
№ 11 (29)

Содержание номера за Ноябрь 2008 год

SQL Server для администраторов

Ноябрь 2008 № 11 (29)

SQL Server
для администраторов

Ноябрь 2008
№ 11 (29)