A question about deployment to production

[jbenckertNT]

A common scenario for us is data conversion. What I mean is that we'll have a need to change the database schema to support new application features and we'll write scripts that will

*Create the new schema
*Convert the data from the old to the new Schema
*Deprecate/remove/drop the old schema

How can SQL Source Control and/or SqlCompare be useful in this scenario?
_________________
Thanks.

-Joe

[David Atkinson]

That depends on how radical the changes are. If you're doing complex refactoring, you'll have to write your own conversion code. If you're making smaller schema changes, it's possible that SQL Compare can help out.

What sort of changes do you have in mind?

David Atkinson
Red Gate Software

[jbenckertNT]

I was really referring to the more complex scenarios. But thanks for the reply.
_________________
Thanks.

-Joe

[David Atkinson]

I think you'll be able to use SQL Compare to help generate much of the migration script but it sounds inevitable that you'll need to hand-craft it to some extent.

If you can think of any features that could make our tools easier to use, we'd love to hear them.

David

[d.roberts]

A typical scenario I have just had along these lines, is that I have changed a column type and name, but the new column data is initially generated from that of the old column. so steps must be:
- create new column
- run update script to populate new column
- drop old column

this is fairly typical I imagine, but I'm concerned that SQL Source Control wont be able to help me with this, or indeed SQL Compare

Idea:
perhaps every checkin could have an optional associated data modification script, tho in this case I'd still have to do this change in 2 seperate checkins, so its not ideal at all.

I'm prepared to write custom SQL, but its how this can be integerated in and performed as part of the deployment script.

if the checkin contained the deletion of the old column, and the addition of the new one, then SSC would allow me to add a data migration script to the checkin. I'd still have to communicate to SSC the relationship between these columns, and when to run th script.