Dealing with schema changes between dev & prod

Question

It may be a stupid way of doing something, but this is what my company is doing ... We are making a circuit change in dev, which we must subsequently translate into production using our latest collections. Therefore, we manually open the SQL 2008 production database, using the designer to make the appropriate changes, deploy the new code, and then hope for the best. Sometimes we forget to make changes to the prod scheme, which, obviously, can lead to serious headaches. So is there a better way? Ideally, we would like some free tools to help us both identify and implement circuit changes, but I don’t know that such things exist ....

We use ASP.NET with VS2010, if that matters.

Answer 1

I stand for update scripts . Always deploy every change, no matter how small it is, as an update to the script. Then, proceeding to production, simply run the upgrade scripts from version N to version N + 1. Effectively prohibit all "visual" development / database management tools.

Various efforts are being made in this direction, first create an ORM, a GDR database project , diff based , etc. etc. Personally, I find explicitly updating the script for a much better alternative in the long run.

Answer 2

See Writing and Modifying Database Code .

Answer 3

I believe Team Foundation Server has a way to save and publish database schema changes with each assembly. In addition, I agree with Remus Rusan. All changes must be written by the script and placed in the version of the script that will be released with each version of the application.