Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Why did you cross-post? stackoverflow.com/questions/12591588/… –  Aaron Bertrand Sep 25 '12 at 23:22
    
Start denying access to the old column @ some point in your cut-over? msdn.microsoft.com/en-US/library/ms173724(v=sql.105).aspx –  Eric Higgins Sep 25 '12 at 23:39
    
@Aaron Bertrand. OK The stack overflow one has been closed. –  peter Oct 1 '12 at 22:22

1 Answer 1

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.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.