Managing database changes in a C # project

Question

I have a C # application (in VisualStudio 2010) using SqlServer 2005 accessed through TableAdapters in C #.

I did not find a good way to manage database changes. For example, in my next version I have a bunch of db schema changes. I made all the changes to the database in Sql Server Management Studio. But now I have to manually make these changes on production servers in turn after deploying new application code (slow and buggy).

In addition, if I decided to abandon my release to the previous version, I will have to manually go through and undo all changes to my db before I can deploy the old code (and now I am under time constraints because the application is not working). Again, this is also very error prone.

Oh, and let's hope that one of my mistakes will not lead to a massive destruction of the production database, otherwise I will now have to pull out the most recent backup from the storage and try again (a lot of time).

I heard about things like Migrations from Rails (and ORM like SubSonic for example). I think the new ORM style (defining your schema in C # code) helps alleviate a lot of this, but unfortunately, because I use TableAdapters, I don’t see how I could implement something like migration.

How do people deal with this?

Answer 1

Managing releases for a database usually involves migrating static data and running scripts to update / create programmable elements (sprocs, UDFs, triggers, etc.) and modify existing schema definitions. It seems to me that you lack scripts. If you make changes manually to your development database and do not create scripts that reflect these changes, you will need to repeat the same steps manually as your test / production environments, which you say are error prone and dangerous.

SQL Server Management Studio makes it easy to save scripts that reflect changes in any database objects. The toolbar should have a “Generate change script” icon, which gives you the ability to save the SQL file to disk. Then you can use this for the same change with another server. You can also manually script to save any or all saved procs, UDFs, triggers, etc., and also run them on the server (just right-click on them).

As for rollback, this is usually achieved by restoring a database backup made just before the start of the deployment process.

The whole process tends to be different for each company, but usually how it is done.

ORMs that automatically generate circuits always seemed evil to me, not to mention the fact that it’s almost impossible to use against a production box, but I think there is an option.

Answer 2

The easiest way to deal with this problem is to buy software that can detect the db schema by comparing the two changes in the database and generate a script change that can update your target database. I use Visual Studio Ultimate 2010 for this, but there is cheaper software that can do the same. This works for me in 99% of cases (the only case when it did not work for me is when I renamed the table column).

If you do not have such software, it is very important to generate SQL change scripts manually. Whenever you make changes to the database schema, keep an eye on the SQL you used to do this and add it to one large db schema change file for the next version of your software. It's a little tiring in the beginning, but you will get used to it pretty quickly.

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

• Go to the site offline
• Back up your current production database.
• Back up your current website.
• Upload the new code to the server
• Run the script database changes that you previously created (manually or using the software mentioned above).
• Take the website back online and see if it works. If this is not the case, and you cannot easily fix the problem, go back to the previous website and db version until you fix the error.

All these steps can be easily automated using batch files and SQL Server Agent or SQLCMD.

Typically, you must first deploy to an intermediate server, and then thoroughly test your site and only then go to the production server. Thus, you avoid longer downtime on your production server and minimize the risk of losing vital data.

Answer 3

Here at Red Gate Software, we are currently solving this problem. Please check out our SSMS add-in, SQL Source Control , in conjunction with SQL Compare Pro . We are also working on the Migration feature, released late this year, and let us customize migration scenarios for specific transitions by version. Since we are still in the early stages of the project, we still cannot give us feedback and help us develop a great solution. We will be happy to talk about your requirements!