3

I am working on a project which uses a relational database (SQL Server 2008). The local (on-premises) application both reads and writes to the database. I am working on a different front end for Azure (MVC2 Web Role), which will use the same data, but in a read only fashion. If I was deploying a traditional web app, I would use SQL Express to act as the local database, and deploy changes with updates to the application (the data changes very slowly) or via some sync system.

With Azure, the picture is a little cloudy (sorry, I had to). I can't seem to find any information to indicate if SQL Express will work inside of Web Roles, and if so, how to do it. Does anyone know if using SQL Express in an Azure web role is possible?

Other options I could do if forced: SQL CE or use SQL Azure. Both have a number of downsides, and are definitely less than perfect.

Thanks, Erick

Edit I think my scenario may not have been clear enough.

This data won't change between deployments, and is only accessed from within the Web Role; it is basically a static cache. The on-premises part is kind of a red herring, as it doesn't impact the data on the web role (aside from being its source). Basically, what I want to do is have a local data store/cache that I use existing T-SQL/DAL code with.

While I could use SQL Azure, it doesn't add anything, and if anything only adds additional overhead and failure points. I could also use a VM Role, but that is way too costly/complex.

In a perfect world, I would package the MDF into the cspkg (so it gets deployed with the app) and then use it locally from within the role. If there is no way to do this, then that is ok and I need to figure out the pros and cons of other solutions. We don't live in a perfect world. :)

Erick T
  • 7,009
  • 9
  • 50
  • 85

5 Answers5

2

You might be able to run SQL Express using a custom VHD but you won't be able to rely on any data every being present on that VHD. The VMs are completely reset when they reboot - there is no physical persistence across reboots.

If you wanted to, you might be able to locate your entire SQL Server installation in Azure blob storage.

However, in doing all of this, you'll only be able to have one worker/web role that can use that database. Remember: a SQL Server database can only be attached to one SQL Server at a time. If you want to scale out, you'll have to create new SQL Server instances for every web/worker role.

Outside of cost concerns, I can't think of anything that is in SQL Express that should be a show stopper for 99.9% of applications out there.

  • Jeremaih - Thanks for the response. I was not thinking of having the SQL Server files on blob storage (the worst of both worlds). I could use SQL Azure, but this is readonly data local to the web role instance. Using SQL Azure would only slow it down and add additional points of failure. Plus it is overkill, and more expensive. – Erick T Mar 14 '11 at 04:49
  • So, you're proposing to pre-load your data into SQL Server on a local VM and never modify that? If that's the case, you should be fine. It really sounds like SQLite or SQL Server CE would serve you better, though. –  Mar 14 '11 at 14:29
  • Jeremiah - that is exactly what I am trying to do. I could have boiled down my post to be more clear. :) The problem is that it looks like you can't get SQL Express on an Azure Web Role. I will have to rewrite all my T-SQL into something that CE can understand. C'est la vie. – Erick T Mar 14 '11 at 15:33
  • Sadly, the web and worker roles are pretty limited, that's why I mentioned the VM role/custom VHD. I don't know what the price is on those, but it's always a possibility. –  Mar 14 '11 at 17:02
2

Adding to Jeremiah's answer: SQL Azure should give you nearly everything SQL Express does today, and you can use the Sync service to synchronize on-premise SQL Server with SQL Azure.

If you installed SQL Express into a VM role, you'd be consuming around $90 monthly just for that instance, plus blob storage (you'd want a Cloud Drive for durability). By definition, a VM Role (or any role) must support scale-out; if you were to scale to 2 instances for whatever reason, both instances would need their own copy of the database, so you'd need to create a blob snapshot for each instance.

Keep in mind, though, if you choose to install SQL Express in a VM: once you're at 2 instances, along with, say, 20GB per instance of blob storage, you're nearing $200 monthly and you're maintaining your VM's OS patches, SQL Express configuration and updates, failure recovery procedures, etc. In contrast, SQL Azure at 20GB, while costing the same $200, will offer better performance and works with the sync service, while completely removing any OS or database server management tasks from you.

David Makogon
  • 69,407
  • 21
  • 141
  • 189
  • Thanks David. I should have clarified that running a VM is completely out, for the reasons that you mention. While SQL Azure would work for this scenario, it really doesn't fit. The data is local to the web role instance, read only, and doesn't need to be durable (e.g., having it part of the cspkg would be fine). – Erick T Mar 14 '11 at 04:51
1

To add to the already existing answers and for anyone wondering if its a good idea to run SQL Express in the cloud: it does makes sense as a temporary storage area. Consider this architectural approach: say you're spinning up nodes to run jobs. Storing a gazillion of calculation results might be a good idea inside a local SQL Express for each node, and provide the aggregated responses immediately when the job finishes on the node. Transfer of the no longer hot results to off-prem SQL server for future reporting/etc can be done afterwords. SQL Azure may not be optimal from the volume/latency/cost perspective to store gazillion of results and ATS will not always fit the bill, especially when relational data, performance or existing code are involved.

Igorek
  • 15,716
  • 3
  • 54
  • 92
  • Thanks for the comment. This is one scenario where I can see it being useful. It seems like there are a number of places where this would be nice, but it looks like it will be a non-starter. – Erick T Mar 14 '11 at 15:34
1

To expand on what David mentioned you can register for SQL Azure Data Sync CTP2 that would allow sync from SQL Server to SQL Azure here: http://www.microsoft.com/en-us/SQLAzure/datasync.aspx

Make sure to use CTP2 though since CTP1 did not support SQL Server.

Voclare
  • 241
  • 2
  • 4
0

If it's a read only local cache - SQL CE 4 or SQLite.

Both have Entity Framework providers.

If you're writing to it - SQL Azure

Doobi
  • 4,844
  • 1
  • 22
  • 17