A question about deployment to production

A SQL Server Management Studio add-in to source control your database in Subversion or Team Foundation Server.

Moderators: Chris Auckland, David Atkinson, sherr, PhilScrace

A question about deployment to production

Postby jbenckertNT » Fri Sep 03, 2010 2:27 pm

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
jbenckertNT
 
Posts: 2
Joined: Fri Sep 03, 2010 1:42 pm

Postby David Atkinson » Fri Sep 03, 2010 3:23 pm

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
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby jbenckertNT » Fri Sep 03, 2010 3:51 pm

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

-Joe
jbenckertNT
 
Posts: 2
Joined: Fri Sep 03, 2010 1:42 pm

Postby David Atkinson » Fri Sep 03, 2010 3:55 pm

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
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby d.roberts » Mon Sep 13, 2010 5:13 pm

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.
d.roberts
 
Posts: 8
Joined: Mon Sep 13, 2010 5:05 pm


Return to SQL Source Control 1

Who is online

Users browsing this forum: No registered users and 0 guests

cron