none
EF Migrations on a legacy database RRS feed

  • Question

  • Asked on Stack Overflow, without response so I thought I'd try here:

    We have several legacy SQL Server databases that we occasionally make schema changes to. We currently have a utility written in C++ that allows users to update their DB's with these schema changes. The utility currently generates dynamic sql to create all DB objects. I am looking into redoing this and thought EF migrations might be a good way to go. I have read up a bit on the subject and I have a general idea of how it works. But I'm having a bit of a hard time figuring out how I would set it up to replace our current procedure (or if it is even possible). Currently, a client could be on any one of a number of previous versions. I'm assuming I would have to go back to the oldest possible version and create my model/initial migration from that, then generate incremental migrations for each version change in order to support updates from all versions. Is that a correct assumption? Also, currently our clients could be using sql server 2000, 2005, or 2008. Would this have any effect on how I would set things up (or if I even could)? Further, the goal is to create a utility with a (C# - probably WPF) UI that the user can use to manipulate the migrations (up or down, preferably). I've seen a lot of examples of how to manipulate migrations from command-line within package manager but not a lot of stuff on how to create a utility with a friendly UI for upgrading/downgrading DB's in production. Also, I have not seen anything that shows how to create stored procedures in a migration (our DBs rely on some stored procedures). I'm assuming that, if nothing else, I can use the Sql() method to generate a SQL query to create a SP. Is that correct? Is there a better way?

    I know my questions are a bit open-ended and I apologize for that. But I'm still in the beginning processes of learning this and I'd like to get an idea of whether or not this is a good way to go. Any guidance would be greatly appreciated.

    Thanks, Dennis

    Thursday, October 25, 2012 1:15 PM

Answers

  • Wow! Big Question.

      Let me just address one part.  With EF it is dead simple to map any accetable SQL table.  Simply add an EF model, the wizard asks for connection string, and viola, you point to the table and have a map.  It works in reverse too.  You can take any existing table or design a new one and generate the SQL needed to alter/update the DB schema!  That function alone would represent the input to your WPF application.  I'd see the project progressing like this:

    1) Map all the versions of all the tables you already have

    2) Generate the SQL schema from EF

    3) Capture and store these alter procedures in your WPF application and name them by release name.

    4) In WPF application have user pick which release to migrate

    5) Execute the appropriate SQL....

    One other idea would be to make a way for the application to allow anyone to immediately identify all the releases of the said database tables for maintainence purposes.  It should be able to accept any new definitions from a web server and act on those the same way.  By doing it this way you do not have to alter the application.  Very important when you are talking about 500 or more machines.


    JP Cowboy Coders Unite!


    • Edited by Mr. Javaman II Thursday, October 25, 2012 2:11 PM
    • Marked as answer by Dennista Thursday, October 25, 2012 5:14 PM
    Thursday, October 25, 2012 2:10 PM

All replies

  • Wow! Big Question.

      Let me just address one part.  With EF it is dead simple to map any accetable SQL table.  Simply add an EF model, the wizard asks for connection string, and viola, you point to the table and have a map.  It works in reverse too.  You can take any existing table or design a new one and generate the SQL needed to alter/update the DB schema!  That function alone would represent the input to your WPF application.  I'd see the project progressing like this:

    1) Map all the versions of all the tables you already have

    2) Generate the SQL schema from EF

    3) Capture and store these alter procedures in your WPF application and name them by release name.

    4) In WPF application have user pick which release to migrate

    5) Execute the appropriate SQL....

    One other idea would be to make a way for the application to allow anyone to immediately identify all the releases of the said database tables for maintainence purposes.  It should be able to accept any new definitions from a web server and act on those the same way.  By doing it this way you do not have to alter the application.  Very important when you are talking about 500 or more machines.


    JP Cowboy Coders Unite!


    • Edited by Mr. Javaman II Thursday, October 25, 2012 2:11 PM
    • Marked as answer by Dennista Thursday, October 25, 2012 5:14 PM
    Thursday, October 25, 2012 2:10 PM
  • Thanks for the reply! Yes, it is a big question - sorry to drop all that out there at once but I'm trying to get a big-picture look at this thing to begin with. Your reply helps with that. One thing that confuses me is how do I create the application so that the first time it is run on one of these production DBs it will know what to do. My understanding is that running upgrades (or downgrades) against a DB is easy when the Migrations History table is there. But obviously that won't be the case for these DBs the first time you try to update them. How can my application know which is the current migration on a production DB that does not currently have a Migrations History table? I am assuming that if I run the appropriate migration on a production DB it will create the Migrations History table at that point (so if I could figure out the start point then that DB would be OK going forward in the future). Is this a correct assumption?

    Thanks again!

    Dennis

    Thursday, October 25, 2012 2:39 PM
  • I'm not a DB expert but those that are know about fields in tables and schemas that indicate dates.  If the DB itself doesn't track releases, then you have to rely on date ranges.  One of the things our DBAs do sometimes is to go ahead and update tables to the latest release!  The reasoning is that if older code doesn't know about the fields, then they won't use them!  The problem of course is that the proper analysis must be done in the case of constraints changing etc.  If a new table changes field name then that must be accounted for, so there's a lot of DB analytical work that has to be done when choosing this method.

    The WPF application you invent should be able to track DB table releases at all costs.  This requires you to work out a table schema with the DBs so that both they and the developers agree to a release numbering system.  Many times application developers drive the DB changes, but somehow, somewhere there must be a collection point that assigns a numbering system to each new change.  This is a part of application lifecycle management which takes a long time to get implemeted across the board.


    JP Cowboy Coders Unite!

    Thursday, October 25, 2012 3:33 PM
  • Thanks again for your reply. If I'm correctly interpreting what you're saying in your replies, you're suggesting that I use EF to create SQL script packages that I can run from my app rather than the method of using migrations with the Migrations History table to have it determine the database version. If so, it makes sense based on my situation that I would probably have to go that route.

    You've given me a good start so I'm going to mark your first reply as the "answer" (although with this type of question I wasn't so much looking for a specific answer so much as some guidance). Thanks for your help!

    Dennis

    Thursday, October 25, 2012 5:13 PM