Support for SQL Server Compact 4.0 on Azure

14
votes

My website is a .net 4.5 mvc site built with VS 2012. I have a SQL Server Compact DB in my app_data folder. All works great on my localhost. I deploy to Azure website and get this error?

Unable to find the requested .Net Framework Data Provider. It may not be installed.

I've googled it and get lots of possible answers, but none specific to Azure and SQL server compact. So my question is simple. Does a windows Azure Website which is deployed to an Azure shared hosting server support SQL Server Compact? If so, what do I need to do to eliminate the aforementioned error and make it work?

3
azure
Could you clarify if you are you using azure websites or web roles? Is there any reason for not using SQL Azure? – Simon Opelt

3 Answers

2
votes

Second Wenchao Zeng of Microsoft the Azure don't support Sql Compact.

I could use once but this is not recommended because the Windows Azure work with replication of the data (this is the cloud, remember?) and the Sql CE does not support this functionality. If you can to put a Sql CE in Azure sometimes will not get access or users see data outdated or broken because the async.

The best way is you to use the Azure Sql ou BLOB storage.

19
votes

It certainly is possible to use SQL Server Compact on Windows Azure. However, you need to ask yourself:

Will I change anything in database?
This includes adding, updating and removing data. If you do, you should not use SQL Server Compact on Windows Azure. Because, if you use SQL Server Compact on Windows Azure and change anything in the database, you'll run into two problems:

  1. Data is not replicated.
  2. You risk losing data.

Data is not replicated
If you change anything in the database, the changes stays local. This means that if you have multiple instances running your web application, then each web application will have their own database, and if you change something in one of them, the change won't be replicated to other instances, which will result in unpredictable behaviour in your application.

You risk losing data
If you change anything in the database after deployment, you risk losing data, because Windows Azure might decide to redeploy your instance to another virtual or physical machine. When this happens, the new instance will be setup with the deployment package you originally uploaded to Windows Azure, and this package does not contain any changes you make to the database. And this redeployment can happen at any time for several reasons.

Conclusion
So, SQL Compact on Azure? Sure, no problem if your data is read-only. However, note that you won't get the performance you would with SQL Azure. But if your need changes down the road, you can always migrate your application from SQL Server Compact to SQL Azure.

If you want to use SQL Server Compact with Windows Azure, you can use the "private deployment" method, as explained on "ErikEJ"'s blog.

Edit: Microsoft has announced that they now provide 1 free 20 MB SQL Azure Database for every Azure subscription. This means you can create a subscription and create a free website, with a free SQL Azure Database. (As long as it's less than 20 MB) You can also have multiple Web Sites associated with the same SQL Azure Database for free. So, if you're considering SQL Server Compact on Windows Azure because your database will be very small and you don't want to pay for a 100 MB database, you can consider this option. In most cases, it will be a better solution. Your website will perform better, your database performance will be better and you have the ability to change your data and it will be replicated.

5
votes

It's possible for SQL Server Compact 4.0 on Azure!!

I write an an article for it

And this is the English version I found

By few step:

Below is the solution after my research:

Step by Step like this:

  1. Install two nuget: EntityFrame.SqlServerCompact & Microsoft SQL Server Compact Edition

  2. put your SQL database file(.sdf/.mdf) in APP_Data folder (Put the connection string like this in order to use it:

    <add name ="DefaultConnection" connectionString ="Data Source=|DataDirectory|CompactDB.sdf" providerName ="System.Data.SqlServerCe.4.0" />)

  3. Publish full project include above SQL database file to AzureWebsites.

  4. you can find it can work well and is totally free.