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

По материалам статьи Paul Ibison: Altering a column on a Replicated Table
Перевод Маргариты Баскаковой

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

"Cannot alter/drop the table 'tablename' because it is being published for replication"."Невозможно изменить/удалить таблицу 'имя таблицы', так как она опубликована для репликации".

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

на

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

изменение подписок
```
  exec sp_dropsubscription   @publication =  'tTestFNames' 
     ,  @article =  'tEmployees' 
     ,  @subscriber =  'RSCOMPUTER'
     ,  @destination_db =  'testrep' 
  

  exec sp_droparticle  @publication =  'tTestFNames'
     ,  @article =  'tEmployees'
  

  alter table tEmployees alter column Forename varchar(100) null
  

  exec sp_addarticle  @publication =  'tTestFNames' 
     ,  @article =  'tEmployees' 
     ,  @source_table =  'tEmployees' 
  

  exec sp_addsubscription  @publication =  'tTestFNames'
     ,  @article =  'tEmployees'
     ,  @subscriber =  'RSCOMPUTER' 
     ,  @destination_db = 'testrep' 
```
Для репликации моментальных снимков (Snapshot Replication) это очевидный выбор. Мы удаляем подписку на эту статью, удаляем статью, затем вносим изменения в таблицу. Далее в обратном порядке (добавляем статью, добавляем подписку на статью). В следующий раз, когда Snapshot Agent (агент снимков) будет запущен, он соберет новую схему без всяких проблем.
Для репликации транзакций (Transactional Replication) мы тоже можем выбрать сценарий, описанный выше. Однако мы должны быть более аккуратны в этом случае. По умолчанию операции вставки, изменения и удаления, выполненные на издателе, распространяются на подписчике в форме вызова хранимой процедуры. Изменяя определение столбца, мы возможно должны изменить связанные хранимые процедуры на всех подписчиках. Дополнение значения по умолчанию сложностей не вызовет, но непосредственное изменение типа поля как описано выше будет требовать модификации аргументов хранимых процедур. Для таблицы tEmployees из примера выше, эти процедуры существуют на подписчике в виде:

sp_MSins_tEmployees, sp_MSupd_tEmployees, sp_MSdel_tEmployees.

Они могут быть сгенерированы на издателе с помощью хранимой процедуры sp_scriptpublicationcustomprocs, но для этого потребуется, чтобы система была в пассивном состоянии, то есть во время этих (быстрых) изменений не должно быть никаких других изменений данных подписчика. Затем всех подписчиков необходимо полностью синхронизировать.
Это ситуация не идеальна и влечет за собой скрытые проблемы, столкновение с которыми неизбежно. Обычно, когда вы добавляете новую статью в уже существующую публикацию транзакционного типа, при запуске Snapshot Agent (агента моментальных снимков) он создаст снимок только новой статьи. В нашем случае он создаст снимок таблицы 'tEmployees'. Таким образом, чтобы избежать всех проблем и сложностей, упоминавшихся выше, наиболее простым решением является запуск Snapshot Agent (агент моментальных снимков) немедленно после выполнения хранимой процедуры sp_addsubscription и уже затем выполнить синхронизацию.
В репликации сведением (Merge Replication) нет никакой возможности удалить подписку, основываясь на использовании сценария, описанного выше, как при репликации транзакций и репликации моментальных снимков. Если мы удалим подписку, полностью включающую все статьи (sp_dropmergesubscription), то в случае если моментальный снимок уже был создан, попытка запустить sp_dropmergearticle приведет к ошибке ("cannot drop article ' article_name' from publication ' publication_name' because a snapshot is already generated"). Поэтому мы должны установить значение @force_invalidate_snapshot в 1, сделать изменения в таблице на издателе, и вновь добавить статью. Затем добавить подписку с требованием первоначальной синхронизации данных с издателем, предварительно создав новый снимок для всех статей в этой публикации. Инициализация без первоначальной синхронизации возможна, но это может привести к существенным ограничениям будущих изменений, и я оставлю это другой статьи.
оперативное изменение таблицы

В случаях если таблицы большие, и мы не хотим запускать новый моментальный снимок - это касается как случаев с одной таблицей (репликация транзакций) так и всей публикации (репликация сведением) - существует альтернативный метод. Мы можем использовать для изменений системные хранимые процедуры sp_repladdcolumn и sp_repldropcolumn (обратите внимание, что эти процедуры ограничивают типы поддерживаемых подписчиков - только SQL Server 2000). Используя эти хранимые процедуры, мы можем добавить фиктивный столбец для временного хранения данных, удалить старый столбец, добавить новый столбец правильного типа, и переместить данные обратно. Скрипт будет выглядеть следующим образом:
```
  exec sp_repladdcolumn  @source_object =  'tEmployees'
     ,  @column =  'TempForename' 
     ,  @typetext =  'varchar(100) NULL' 
     ,  @publication_to_add =  'tTestFNames' 
  

  update tEmployees set TempForename = Forename
  

  exec sp_repldropcolumn  @source_object =  'tEmployees' 
     ,  @column =  'Forename' 
  

  exec sp_repladdcolumn  @source_object =  'tEmployees'
     ,  @column =  'Forename' 
     ,  @typetext =  'varchar(100) NULL' 
     ,  @publication_to_add =  'tTestFNames' 
  

  update tEmployees set Forename = TempForename
  

  exec sp_repldropcolumn  @source_object =  'tEmployees' 
     ,  @column = 'TempForename'
```

Хотя вышеупомянутый сценарий может использоваться для репликации транзакций или репликации сведением, внутренняя методология различна из-за отличающегося характера этих двух методов. Для репликации сведением, детали обновленных строк были бы сохранены в MSmerge_contents, и если указанная строка была изменена один раз или сто, в этой системной таблице будет только одна запись (для синхронизации/репликации), в то время как при репликации транзакций, 100 изменений строки приведут к 100 обновлениям подписчика. Это означает, что репликация сведением, имеет преимущество перед репликацией транзакций, потому что нам нужно выполнить 2 изменения каждой сроки, чтобы сделать изменение схемы.

[В начало]