How do I test locally against SQL Azure?

Question

Looks like Azure Storage Emulator omits SQL Azure. Does it hold true? Then how do I test my application that uses SQL Azure without deploying it onto live cloud?

Answer 1

For local testing, you can use a local SQL Server (full, express or even CE) within your app - just use local connectionstring's in place of the SQL Azure ones in your .cscfg or .config files - then the local compute instance will connect just like any other local process would.

For the most part local SQL and SQL Azure are compatible and interchangeable. Microsoft claim that SQL Azure shares much of its codebase with SQL Server 2008 R2. As a result you can use SQL Server 2008 R2 or SQL Server 2008 Express locally in order to test your application. Indeed, for "simple" applications you should also be able to use the new file-based SQL Server CE.

---

For "advanced" applications, then there are some limitations with SQL Azure - see limitations on http://msdn.microsoft.com/en-us/library/ee336245.aspx - but for many ASP.Net applications, you should be able to just migrate from SQL express to SQL Azure.

For more details on SQL vs SQL Azure, see http://social.technet.microsoft.com/wiki/contents/articles/comparing-sql-server-with-sql-azure.aspx

For migrating data from local SQL to SQL Azure, see the SQL Migration Wizard on Codeplex - http://sqlazuremw.codeplex.com/

Answer 2

This reply needs to be updated.

Since this year you can now emulate Azure SQL Database locally:

https://learn.microsoft.com/en-us/azure/azure-sql/database/local-dev-experience-set-up-dev-environment?view=azuresql&tabs=vscode

https://www.youtube.com/watch?v=3XgepwpBJP8

https://www.youtube.com/watch?v=rf0FvDMm0f0

Answer 3

i suggest you use SQL server 2008 express for onpremise development. you can migrate that to cloud once you are ready. But you need to be aware of contemporary limitations and also some architectural considerations while using SQL server.

And the best part is that you just need to change the connection string to connect to SQL Azure after the migration.

Answer 4

No, there is no local SQL Azure emulator. As Stuart mentioned, a local DB is the best option for local development.

With SQL Server 2012, you have the option of "Local DB". It is a very lightweight flavor of SQL Server 2012 Express. More details here. It only runs in "user mode", meaning

1. You don't have to install SQL Server locally
2. You don't have to run SQL Server as a service in the background full time

After you install it, there's now a project type in VS 2012. Similar to CE, it creates an MDF and LDF file. You connect to it with a connection string like this:

"Server=(LocalDB)\v11.0; Integrated Security=true ;AttachDbFileName=D:\Data\MyDB1.mdf"

This has worked well for me; the lighter download size is nice (33 MB vs 133 MB). It takes load off of my dev machine not to run a database server at all times.

Note: When you create the MDF/LDF files, it will probably be included in your solution. In my opinion, you should leave these files out of your VCS. I like to use scripts to create and update DB objects, then check those scripts in with my other source files.