Search code examples
c#databaseentity-frameworkasp.net-mvc-3data-loss

How to evolve SQL Server database of MVC3 sites in the future and avoid data lost

I'm getting ready to develop a MVC 3 website with C#, Entity Framework and SQL Server.

This website is built for critical jobs and data lost is something absolutely not allowed ! In my knowledge I had no experience of evolving database, but I know this project should be able to evolve while using incremental development methodology. May I know is there any guideline to follow and how do I evolve it without any single error? In term of database initial design or anything. Just, 0 Data Lost is highest priority requirement.

I need answer for this 2 question and hope some experience could guide me in this issue

  1. How to update database include table, column without affect other data in the same table
  2. How to update remote database (for example C# window apps and database is not with me)

For the 1. question the database is located at my web server but question 2 the database is staying with user end.

Solution

  • The answer is: it is your duty to design upgrade process in the way your requirements are met. There is no auto magic which will do this for you.

    The process usually involves creation of upgrade SQL script which will modify database structure and if needed it can also move data to temporary tables while structure of main tables are changed so that data are not lost. You can also maintain database version in some special table and check it before you run the update so that you ensure that update script is run on expected old version.

    There are tools like RedGate SQL Compare and Visual Studio Database tools (only Premium and Ultimate version) which are able to take old database, a new database and create difference script for you so that old database schema can be upgraded to a newer one. This works for most scenarios but you must always very carefully test result in your testing environment. It is best to test in on backup of your production database if possible.

    How to avoid data loss if anything goes wrong? There is only one very simple way BACKUP THE DATABASE before you do any changes and restore the old database if anything goes wrong. Backup can be even scripted with SQL. Without successful backup never touch your production database.

    How to upgrade client side database? You will use the same process but you will wrap it all in some installation package (.msi) for example created with WiX.