Sql-server – How to Rename Table or Field With Merge Replication

merge-replicationreplicationsql server

I am using merge replication with SQL 2012. I am trying to rename a published table, and rename a published field.

These operations are not permitted in merge replication.

However I can think of an action plan if these things are needed. For instance renaming a field would include,

  1. Adding the new field
  2. Migrating the data from the old field to the new field
  3. Wait for a while, perhaps a month
  4. Delete the old field

The problem is after step 2. We can force a software upgrade to the clients so that data is written into the new field.

But some data may have been modified in the old field at one of the subscribers before they do the sync which makes the schema change. This would also be before they were forced to do the update.

The migration could be done more than once, but by the time you get to step 4 its hard to know which modifications have been made to the old field, and which have been made to the new field.

Best Answer

You could do it this way:

  1. Add the new field.
  2. Force a software upgrade that includes the logic to insert new data into the new field and to read the data from the new field if it is not null, otherwise to read that field from the old field. ( in case of update: you read the value from the old field then do an update to the new field )
  3. Wait for a while, perhaps a month till you are sure that all clients have upgraded your software.
  4. Migrate data from the old field to the new field if the new field is null.
  5. Delete the old field.
Related Question