Двунаправленная транзакционная репликация данных

«Распределение нагрузки MSSQL на 2 сервера»

Добрый день, читатели, вот решил написать про свою историю «Распределение нагрузки MSSQL на 2 сервера методом двунаправленной транзакционной репликации данных». Да не просто 2 сервера, а что бы работали они как зеркала. Кого заинтересовало, приглашаю к чтению.
Кто хочет перейти сразу к делу
Ну и конечно, же в помощь пришла репликация данных. После обзора типов репликации, пошли рассмотрения архитектуры, которая нам больше подойдет.
В нашем случае мы гнались за распределением нагрузки между 2-мя серверами. Ну и прошлись по логической цепочке «распределение нагрузки – 2 сервера зеркала – минимальные задержки — транзакционная репликация ». А так как опыта было мало совсем не было, опять отправился в msdn. А там зашел в статью, начал читать одно, прошел по парочкам пояснений к терминам и уже совсем отвлекся. В прочим как и я от главной темы…
Сначала передо мной выпал тип транзакционной репликации как «Одноранговая репликация транзакций». Не знаю как многие, но я часто сначала смотрю на топологию в виде схемы, ну а потом только вчитываюсь. В этот раз, опять, попался на своей же ошибке.

Посмотрел вот на эту топологию, ну и понравилось она мне, что слева «чтение-запись» в оба сервера, всё красиво, нагрузка распределена. В случае падения, некоторое время, один сервер справится.
Ну и на радостях принялся настраивать, а точнее вчитываться в настройку и пробовать на тестовых базах. Сначала побывал я на страничке этой и очень понравилась мне фраза «При настройке топологии активных баз данных для добавления первого и второго узлов (узел A и узел B), используется следующая процедура. Для добавления узла C и всех последующих узлов затем используется очередная процедура». В особенности эти 2 словосочетания «следующая процедура» и «очередная процедура» и далее по тексту про трехузловую одноранговую топологию.
И тут по счастливой случайности решил я вернуться на страничку с которой начал и прочитать про эту топологию и попадается опять ключевая фраза «Слева операции обновления секционируются между двумя серверами. Если база данных содержит каталог продукции, можно, например, создать пользовательское приложение, направляющее обновления названий продуктов, начинающихся с букв от «А» до «М», на узел А, а обновления продуктов, начинающихся с букв от «Н» до «Я» — на узел Б. Затем обновления реплицируются на другой узел». Так это же совсем не то что я хотел, и значит при падении сервера «А», данных будет только часть.
И опять в грусти и печали долгий сёрф по просторам ссылок, хотя решение было под носом. Натыкаюсь я на статью «Двунаправленная репликация транзакций». И снова улыбка на лице, и снова руки рвутся в бой. И когда читаю настройку этой репликации, спадает немного улыбка, и я понимаю, что ничего не понимаю в настройке репликации. После нескольких часов чтения, проб и ошибок, начинается проясняться ситуация. В настройке двунаправленной репликации, большая часть описана в настройке через процедуры, покажу свой способ настройки, по которому у меня работает такая топология.

Ну приступим. Советую сначала проделать это на тестовых базах. В основном буду кидать ссылки, ну и описывать «подводные камни» которые могут встретиться на пути

• 1. Настройка распространения. Выполнять на обоих серверах (распространитель(издатель) и подписчик). В этой части проблем особых не встретил, сервер распространения и издатель, в моем случае один и тот же. При установке папки для моментальных снимков необходимо указать локальную папку(указать ссылку на расшареную папку, к которой будет доступ для обоих серверов). Далее, так как издатель и распространитель один и тот же сервер, оставляем без изменений. И последнее это вводим пароль между распространителем и издателем (в нашем случае пароль не будем использовать дальше).
• 2. Далее добавляем публикации и статьи (на обоих серверах). «Репликация – Локальные публикации – Создать публикацию». Выбираем базу, тип публикации «Публикация транзакций», и выбираем доступные объекты, настройку агента можно пропустить, безопасность агента заполняем.
  Камушек

  Одно из условий статьи это Primary Key в таблице.
  При создании Primary key советую проверить, чтобы не было индекса кластерного по ключевому полю, а если есть то удалить его. При создании ключа этот индекс будет создан, и если не удалить то уже будет не кластерный и будет увеличена обработка данных из за присутствия 2-х одинаковых индексов.
• 3. На этапе создания подписок я в начале путался, но постараюсь объяснить доходчиво.
  Начнем последовательно:
  1. На сервере А:
     (1) Создаем подписку процедурой

     EXEC sp_addsubscription
     	@publication = 'А_ Base_repl_1', --Имя публикации на текущем сервере созданной на шаге 2.
     	@article = N'all', 
     	@subscriber = 'B', -- Имя сервера подписки
     	@destination_db = N'Base_repl_2', --Имя базы на сервере B
     	@sync_type = N'none',
     	@status = N'active', 
     	@update_mode = N'read only',
     	@loopback_detection = 'true'; -- параметр двунаправленной публикации.

     (2) Включаем агент

     
     	EXEC sp_addpushsubscription_agent 
     	@publication = 'А_ Base_repl_1',  -- Имя публикации на текущем сервере созданной на шаге 2.
     	@subscriber = 'B', --Имя сервера подписки A
     	@subscriber_db = N'Base_repl_2', --Имя базы на сервера подписки
     	@job_login = 'domainuser', --логин домена
     	@job_password = 'pass'; --пароль домена
     	

  2. На сервере B:
     (1) Создаем подписку процедурой

     
     	EXEC sp_addsubscription 
     	@publication = 'B_ Base_repl_2', --Имя публикации на текущем сервере созданной на шаге 2.
     	@article = N'all', 
     	@subscriber = 'A', -- Имя сервера подписки
     	@destination_db = N'Base_repl_1', --Имя базы на сервере B
     	@sync_type = N'none', 
     	@status = N'active', 
     	@update_mode = N'read only', 
     	@loopback_detection = 'true'; -- параметр двунаправленной публикации.

     (2) Включаем агент

     
     	EXEC sp_addpushsubscription_agent
     	@publication = 'B_ Base_repl_2',  -- Имя публикации на текущем сервере созданной на шаге 2.
     	@subscriber = 'A', --Имя сервера подписки A
     	@subscriber_db = N'Base_repl_1', --Имя базы на сервера подписки
     	@job_login = 'domainuser', --логин домена
     	@job_password = 'pass'; --пароль домена
     	

• 4. Для создания хранимых процедур используемых для статей можно воспользоваться статьей. Я считаю её не востребованной, так как для каждой таблицы создавать вручную по 3 процедуры (ins, upd, del) уж очень накладно. Поэтому я воспользовался следующей процедурой sp_scriptpublicationcustomprocs
  На сервере А:

  
  sp_scriptpublicationcustomprocs @publication ='А_ Base_repl_1' -- Имя публикации на текущем сервере
  

  далее копируем результат процедуры и выполняем его на сервере «B» в базе «Base_repl_2»
  На сервере B:

  
  sp_scriptpublicationcustomprocs @publication ='B_ Base_repl_2' -- Имя публикации на текущем сервере
  

  далее копируем результат процедуры и выполняем его на сервере «A» в базе «Base_repl_1»

Ну вот вроде и всё. Буду рад любым советам и критике.

Автор: viper4u

Источник