Probably the biggest problem that we’re running into is when we need to refactor a table.
Say for instance I have a set of address fields in a contacts table that I want to refactor out into their table so that contacts can have multiple addresses. I want to retain the data that's currently entered for each contact by migrating it to the new structure.
What ultimately needs to happen in production:
1. Create the new structure.
2. Migrate the address data from the old fields to the new table.
3. Drop the old fields.
SSC only captures the create and the delete, there's no convenient way to inject the migration script into the works. Since this is not simply static data, but a dynamic migration script, SQL Data Compare can't be used to check in the changes.
Less than ideal workarounds:
* Save the create, migrate, delete statements into one big script. Don't check the changes into source control. Apply this script completely independently of source control to each development and production database. When it's all said and done and everyone has the change, check the change into source control.
* Create the new structure, check it into source control. Create the migration script and apply it to all development and productions databases, but only after everyone has checked out the latest version of the database from source control. At some point in the future, drop the old columns.