Managing DB Changes in a C# Project

Question

I have a C# app (in VisualStudio 2010) that used SqlServer 2005 accessed through TableAdapters in C#.

I haven't discovered a good way to manage DB changes. For example, in my next release I have a bunch of db schema changes. I made all of my DB changes in Sql Server Management Studio. But now I have to manually make these changes on the production servers in turn after I deploy the new application code (slow and buggy).

Furthermore, if I decide to roll back my release to a previous version, I have to manually go through and undo all my db changes before I can deploy the old code (and now I am under time constraints because the app is down). Again, this is also very error prone.

Oh, and lets hope that one of my errors doesn't cause massive destruction to the production DB, otherwise I now have to pull the most recent backup out of storage and try again (very time consuming).

I have heard of things like Migrations from Rails (and ORMs like SubSonic). I think that the new ORM style (define your schema in c# code) helps alleviate a lot of this, but unfortunately, as I am using TableAdapters, I don't see how I could implement something like migrations.

How do people deal with this?

Answer 1

Release management for DBs usually involves migrations of static data and running of scripts to update/create programmability elements (sprocs, UDFs, triggers, etc) and modify existing schema definitions. Looks to me like you're missing the scripts. If you're making changes manually to your development DB and not creating scripts that mirror those changes, you will need to repeat the same manual steps against your test/production environments, which as you say is error prone and dangerous.

SQL Server Management Studio makes it easy to save scripts that reflect changes to any database objects. In the toolbar there should be an icon called "Generate change script", which gives you the option to save the SQL file to disk. You can then use this to perform the same change against another server. You can also manually script any or all stored procs, UDFs, triggers and so on, and run those against a server as well (just right-click on them).

As to rollback, that's normally achieved by restoring a backup of the database made just before the deployment process begins.

This whole process tends to be different for each company, but that's generally how it's done.

ORMs that auto-generate schemas have always seemed evil to me, not to mention pretty much impossible to use against a production box, but I guess there's also an option.

Answer 2

The easiest way to deal with this problem is to buy software that can detect db schema by comparing two databases changes and generate a change script that can update your target database. I am using Visual Studio Ultimate 2010 for that, but there's also cheaper software that can do the same. This works for me 99% of the time (the only instance where this did not work properly for me is when I renamed a table column).

If you don't have such a piece of software, it is crucial to generate your SQL change scripts by hand. Whenever you do a change to the database schema, keep track of the SQL you used for that changed and add it to one big file of db schema changes for the next version of your software. It's a bit tedious at the beginning, but you'll get used to it pretty quickly.

Then when you are ready to deploy the software, proceed as follows:

1. Take the website offline
2. Make a backup of your current production database.
3. Make a backup of your current production website.
4. Upload your new code to the server
5. Run the DB changes script you previously created (either by hand or with the software mentioned above)
6. Take the website back online and see if it works. If it doesn't and you can't easily fix the problem, revert to the previous website and db version until you have fixed the bug.

All of these steps can be easily automated using batch files and the SQL server agent or SQLCMD.

Generally you should deploy to a staging server first, then test your website very thoroughly and only then move on to the production server. This way you avoid longer downtimes on your production server and minimize the risk of losing any vital data.

Answer 3

Here at Red Gate Software we're currently tackling this exact issue. Please take a look at our SSMS add-in, SQL Source Control, in combination with SQL Compare Pro. We're also working on a 'Migrations' feature, due out later this year, allowing custom migrations scripts to be defined for specific version transitions. As we're still in the early stages of the project, there's still time to give us feedback and help us design a great solution. We'd love to speak to your further about your requirements!