Migrating SQL Server Compact Edition (SQL CE) database to SQL Server using Web Matrix

One of the things that is keeping us busy is the Web Camps we are delivering across 5 cities.  If you are a reader of this blog, and also attended one of these web camps, there is a good chance that you have seen me Smile since I was there in all the places, so far.  The topics that we cover include Visual Studio 2010 SP1, SQL CE, ASP.NET MVC & HTML5.  Whenever I talk about SQL CE, the immediate response is that, people are wow that Microsoft has shipped a FREE compact edition database, which is an embedded database that can be x-copy deployed.  If you think, well didn’t Microsoft ship SQL Express which is FREE?  The difference is that, SQL Express runs as a service in the machine (if you open SQL Configuration Manager, you can notice that SQL Express is running as a service along with your SQL Server Engine (if you have installed Smile).  This makes it that, even if you are willing to use SQL Express when you deploy your application, it needs to be installed on the production machine (hosting provider) and it needs to run as a service.  Many hosters don’t allow such services to run on their space.

SQL CE comes as a x-Copy deploy-able database with just a few DLLs required to run it on the machine and they don’t even need to be installed in GAC on the production machine.  In fact, if you have Visual Studio 2010 SP1 installed, you can use the “Add Deployable Dependencies” option in Project-Properties and it would detect that SQL CE is something you would probably want to add as a deploy-able dependency for your project.  With that, it bundles the required DLLs as a part of the “_bin_deployableAssemblies” folder.  So your project can be x-Copy deployed and just works fine.

However, SQL CE has the limit of 4GB storage space.  Real world applications often require more than just 4GB of data storage and it often turns out that people would like to use SQL CE for development/ramp up stages but would like to migrate to full fledged SQL Server after a while.  So, its only natural that the question arises “How do I move my SQL CE database to SQL Server”  And honestly, it doesn’t come across as a straight forward support. 

I was talking to Ambrish Mishra (PM in SQL CE Team, Hyderabad) since I got this question in almost all the places where we talked about SQL CE.   He was kind enough to demonstrate how this can be accomplished using Web Matrix. 

Open Web Matrix (Web Matrix can be installed for free from www.microsoft.com/web) and click on “Site from Template”

image

Click on the “Bakery” template (since by default it uses a SQL CE database and has all the required sample data) and click “Ok”.

image

In the project, you can navigate to the Database tab and will be able to find that the Bakery site uses a SQL CE database “bakery.sdf”

image

Select the “bakery.sdf” and you will be able to see the “Migrate” button on the top right

image

Once you click on the “Migrate” button, you will notice that the popup wizard opens up and by default is configured for SQL Express.  You can edit the same to point to your local SQL Server instance, or a remote server.

image

Upon filling in the Server Name, Username and Password, when you click “Ok”, couple of things happen. 

1. The database is migrated to SQL Server (local or remote – subject to permissions on remote server).   You can open up SQL Server Management Studio and connect to the server to verify that the “bakery” database exists under “Databases” node.

2. You can also notice that in Web Matrix, when you navigate to the “Files” tab and open up the web.config file, connection string now points to the SQL Server instance (yes, the Migrate button was smart enough to make this change too Smile)

And there it is, your SQL Server Compact Edition database, now migrated to SQL Server!! In a future post, I would explain the steps involved when using Visual Studio.

Cheers !!!

Print | posted on Monday, June 20, 2011 11:32 AM

Comments on this post

# re: Migrating SQL Server Compact Edition (SQL CE) database to SQL Server using Web Matrix

Requesting Gravatar...
Nice to know. Thanks for sharing.
Left by Hardik Doshi on Jun 22, 2011 10:24 AM

# re: Migrating SQL Server Compact Edition (SQL CE) database to SQL Server using Web Matrix

Requesting Gravatar...
Very nice article - today I got time to read it and I find it very useful.
Left by Pinal Dave on Jun 26, 2011 10:21 AM

# re: Migrating SQL Server Compact Edition (SQL CE) database to SQL Server using Web Matrix

Requesting Gravatar...
Wow, at first, I though I can comprehend all the contents of this blog. But no, I have things I need to know about and I need to study harder to achieve the level that I am aiming.
Left by Mario Romano on Dec 29, 2011 7:37 AM

# re: Migrating SQL Server Compact Edition (SQL CE) database to SQL Server using Web Matrix

Requesting Gravatar...
Hi, Thanks for this post.But,from the post it seems that; manually we have to do everything.
But, if I want to do this programmatically (C++/C#), how can I do this?
Please suggest!
Thanks.
Left by Jrout on Dec 30, 2011 4:11 PM

# re: Migrating SQL Server Compact Edition (SQL CE) database to SQL Server using Web Matrix

Requesting Gravatar...
Indeed quite helpful, even to intermediate developers on SQL
Left by Soham on Jul 31, 2013 2:21 PM

# re: Migrating SQL Server Compact Edition (SQL CE) database to SQL Server using Web Matrix

Requesting Gravatar...
Thank you very much for this article. This was a real life saver in helping one of our clients. Much appreciated!
Left by Tamir Orbach on Jun 27, 2014 3:36 PM

Your comment:

 (will show your gravatar)