Is SQL Azure suitable for Desktop client applications

Question

I have a client that runs a small business. They need a custom database solution and I'm looking into various options. My experience is limited to .NET using local SQL Servers (no ASP.NET), however, this client is non-technical and would benefit from being able to outsource the DBA tasks. I'm a complete Azure noob, I just scanned the tutorials and they all appear targeted at developing MVC ASP solutions. The client doesn't need a browser based solution. A fat desktop client used from different geographical offices would be the least expensive option I can deliver. I'm just trying to save some time going through all the tutorials and docs only to find out that this isn't what SQL Azure is intended to do. In effect my questions boil down to:

1. Can I develop a C#/.NET WPF desktop application using Entity Framework 4 and have it hit SQL Azure instead of a local SQL Server?

2. Are there any known gotchas with EF4 and SQL Azure?

3. Are there other hidden development costs/complications in using SQL-Azure instead of a local SQL Server.

4. Is the basic tool support the same? One specific example I can think of; do I get a SQL profiler tool for troubleshooting?

5. The final question is security related and I'm not sophisticated enough to ask a good question, but is hitting a SQL Azure db this way considered a security no no?

Answer 1

1. Yes, you can, but a more suitable approach would be to use WCF Data Services or another form of web services (asmx or WCF) as a services layer for your application. I like this approach for line of business applications. I hate web apps for line of business and by using a services connected WPF desktop application, you get the benefits of running in the cloud and having a cloud offering without the necessity to be HTML based.

2. SQL Azure has full support for EF these days. In the past there were some issues, but I have not encountered any these days.

3. In terms of development costs and complications - the Azure desktop hosted environment is a bit of a PITA from a development perspective, but I haven't had major problems. You lose the ability to share a local DEV SQL Server unless you use a hosted instance....of course there's a development cost in that because you have to pay for usage.

4. Good point! SQL Azure does not provide SQL Profiler support at present. I personally use the built in EF tracing support for this functionality.

5. Exposing a SQL Azure DB directly isn't a good idea from a security perspective. That's why I suggest hitting a WCF Data Services (or other web services) endpoint in point 1.

Answer 2

You can develop a desktop or on-premise application that uses SQL Azure for your database.

You need to take the standard Azure precautions - assume that connection failures will occur and ensure that your application has retry logic to restore operation. Also note that SQL Azure will terminate any operations that take longer than a minute, to preserve the service for other users. If you have lots of data and some nasty queries, that might be relevant.

EF works fine with SQL Azure. There are some limitations to SQL Azure, itself, which you can read about from the documentation on Microsoft's web site. If you design you database for Azure, it'll work fine on SQL Server or SQL Express (but not necessarily the other way around).

In addition to the monthly charge for the database, you will pay for data that leaves the data centre. Design your application carefully to minimise the amount of data that is retrieved from the database. You no longer have to pay for data going into the data centre, which helps.

You can still use SQL Management Studio and Data Connections within Visual Studio. No SQL Profiler, though. There are a few irritating things you can't do with Management Studio, but nothing insurmountable.

You will have to open up firewall rules for access to the database, but hopefully, they'll be limited. Authentication is by SQL Server credentials, not integrated authentication.

I wouldn't tend to do it this way, but it works.