That whole sql migration process…


I wanted to blog about whatever I had learned in the past week but really what came to mind was not answers, but more questions. Questions are good, they have a side effect of helping me learn. So here you go…

Migration:

What is the ideal way to push changes from your development server, to stage, and then production SQL servers with Microsoft products?

The way I am familiar with is using a third party tool, Apex Diff, to script data/schema differences add scripts to VSS, then having a custom script for any data cleanups… and push.

But Microsoft has built this all into Team system I think, I’ve never used it personally.  

Also, versioning… say I make changes to a table, “alter table add column blah blah”, what is the best way for VSS to pick up this change? I’ve always checked out the table creation script, manually added my alter script to the bottom with comments about the workorder where the new column was requested by the client, then checked in the script, hit ”f5″ to push just that portion to my database and called it a day.

But is there a way for VSS to automagically pick up these changes, and who made them… it would save me so much time to just hit “f5″ and be done.

The same goes for SSIS. How do I know what changes were made to SSIS packages before I got to them? I suppose I could compare the XML files… but I need more, I need to know why the change was made and who did it…

I guess what I’m saying is, if I only had Microsoft tools to work with, what are the best practices for pushing these changes to my databases.

All these questions are things I’ll most likely be researching and blogging about in the next few weeks.

This seems like a vital part of having a database, code change and migration process per Microsoft should be clear as a bell right?

 

3 Comments, Comment or Ping

  1. I just recently pulled an internal database into Team Data using the schema importer. It was easy, and after a little bit of tweaking, I was able to create a single-click deployment of the database for developers to use. We are now working on the sample data generation and datbase unit testing for continuous integration.

    December 19th, 2008

  2. Yeah? So far Im really liking it, but what about documenting changes to schemas?

    December 19th, 2008

  3. What do you mean documenting? I use it with TFS so you can use history or annotation to see what changes occured. Other than that just following a convention of adding a comment when a change is made to a file.

    December 19th, 2008

Reply to “That whole sql migration process…”