Using Redgates Sql Compare for Complex Migrations with EF’s CodeFirst

Introduction

As many of you know, I’ve been quite the fan of Entity Framework CodeFirst for quite some time.  It does a great job of reducing the number of places I have to change thngs each time I have a database schema change and also brings a ton of type safety along with it.  I’ve posted before about the benefits of the auto migrations features built into CodeFirst, however I have found when I make a lot of changes to my schema, I quickly get an error saying I will have data loss so the CodeFirst Migration will not take place.

In my realities, it is just too much time to go through with LINQ and try and make the perfect data migration.  My reality is that I likely only have one or two databases that need sql updates and I can do those much more efficiently with Redgates Sql Compare.  In this post, I’ll walk through the steps I follow.  This is by no means a perfect solution but it is quick, solves my problem in both a reliable and robust way.

 

Steps

Step 1

Backup your production database! (goes without saying but saying it anyhow)

Step 2

Run Sql Compare against your most up to date database that you want to ultimately be production (and choose compare).  Below is a screen shot of what this looks like for me.

image

The resulting comparison looks like this graphically.  I’m going to choose to do all the suggestions.

image

Step 3

Now, I press the “Deploy Wizard” and I get a screen that tells me what it will do and gives me the script to do it.  Notice that is telling me what data I will loose if any.  In my case, the data that is being lost is OK because I can simply have my background email process refetch all the email and everything will be fine.  You need to be very careful of this to make sure that it is what you want.

image

Step 4

Now, if I say “Open Script Editor” it takes me to Sql Server Enterprise Manager, connects me to the correct database and gets my script ready to go.  All I have to do is press “execute” and it will run!

image

 

Side Note:  You may have to remove your migration custom sql tables for this to work.  I did that and also used the implementation of create only when database does not exist for my code first now, not the migrations.

 

Conclusion

And it’s hard not to love the results.

image

And of course, you have to just Love Redgate’s Sql tools.  Sql Compare is just one of many.  I highly recommend them for any sqlserver professional or developer.

That’s it!  My production database is now the same as my CodeFirst local database.

About Peter Kellner

Peter is a software professional specializing in mobile and web technologies. He has also been a Microsoft MVP for the past 7 years. To read more about Peter Kellner and his experience click here. For information about how Peter Kellner might be able to help you with your project click here.

Follow me:


Comments

  1. Peter Kellner says:

    My only suggestion with a larger team is that someone has to manage the production releases. I think EF wants this to work automatically with migrations but I don’t think it’s solid enough to take a chance on a production machine, even with testing.

  2. Peter: Good post.

    This seems like a simple workflow, with one developer and one production database.

    Things seem more complex when there is a bigger team with entity framework code first, and there are several target servers: QC and PRODUCTION databases for example.

    Any suggestions on a good workflow with redgate in that case?

Your Comments

*

Protected with IP Blacklist CloudIP Blacklist Cloud

Follow

Get every new post delivered to your Inbox

Join other followers: