9

Beside being cheaper is there any problem/advantages to use SQL Azure vs to use a Virtual Machine with SQL Express?

We are looking to setup a SQL server for more than 50 websites, do you have any recommendations?

We currently have SQL Express on a dedicated server and we are looking to switch this server to Azure.

2

I have researched it and found theses differences. enter image description hereenter image description here

The last difference was Manage at scale capablilties: SQL server insode windows azure VM - Fair Windows azure SQL database - Good

I hope this helps. Here is the page which might be helpful to you. Hope it help! < http://azure.microsoft.com/blog/2012/06/26/data-series-sql-server-in-windows-azure-virtual-machine-vs-sql-database/ >

  • I still have problem understanding if is it free to install and use sql express on vm? If so it means it will have lower cost than azure sql database? – freewill Oct 7 '14 at 15:02
6

I am the author of the blog post mentioned by LouD above. I agree with those comments, that the price per unit of performance has increased quite significantly on Azure SQL DB under the new service tiers (i.e. Basic, Standard and Premium). If you have significant I/O requirements, the new tiers will likely prove expensive.

Also, just to point out however, that while there is no SQL Server License Cost for running SQL Express in a VM, remember that in a VM you do pay for the amount of I/O that you use, so lots of reading and writing increases the cost (where as Azure SQL DB is flat fee based). It is difficult to say whether the VM will be cheaper or more expensive than Azure SQL DB without knowing database size, I/O rate required, etc. But, finger in the air, I guess that for a small database, the VM may well be cheaper.

If you have multiple databases, that probably pushes things further in favour (cost wise) of the VM. At the moment there is no way to share resources across databases in Azure (e.g. you have five databases, four are quiet but one is busy, the busy one cannot use any of the resources allocated to the other four, those resources are essentially wasted). Microsoft have stated they are working on something that will make sharing resources / cost across groups of databases easier. They have committed to having this available before the end of Web/Business (which happens in September 2015) but other no details yet.

Also remember that Azure SQL DB and SQL Express in a VM don't offer the same set of features. The Database-as-a-service in Azure offers a whole lot more features, e.g. automated backup, more HA possibilities, managed servicing, etc. Also, and very important, note that Azure SQL Database supports many, but not all of the features of on-premise SQL Server. The online documentation provides a quite detailed breakdown of the on-premise features, SQL Statements, etc that are NOT supported on Azure.

Also, I would be cautious using the large table posted above by Vdub as a guide. It was taken from a 2012 blog post and much has changed since then in Azure SQL DB. The Azure SQL DB column in that table refers to the Web/Business editions that are being retired next year. Compared to the content of the table above, the new service tiers cost more (generally speaking), have additional HA features, don't support federations in the same way, have a higher uptime guarantee, etc.

Finally, just to follow on from a comment that LouD made... The new service tiers do support a limited amount of bursting. For example, see this chart: http://cbailiss.files.wordpress.com/2014/07/2014-07-04-seqsel-metrics5.png?w=800&h=401 in this post: http://cbailiss.wordpress.com/2014/07/06/microsoft-azure-sql-database-performance-tests-sequential-selects/ The start of this test (a sequential read from disk) does show a small increase / higher burst rate in the first few minutes. This is however, roughly speaking, less than that typically available in Web/Business.

  • Thanks for the answer, can you point to where MS have indicated they'll have something to deal with the multiple-databases scenario? That's a killer for us too so I'm keen to watch this space. – Rory Dec 1 '14 at 20:20
  • Oh, anks thanks for those articles of yours, they were very useful when trying to understand the new tier performance. – Rory Dec 1 '14 at 20:21
  • 1
    For the multi-database scenario, see this blog post (which includes a signup for future details on this): azure.microsoft.com/blog/2014/08/26/… – cbailiss Dec 3 '14 at 8:20
4

Beside being cheaper

Unless you have very specific use cases I doubt it's cheaper (or faster) to use Azure's SQL database service. You are comparing a FREE license of SQL Server to what amounts to a slice of an expensive, data redundant, enterprise license of SQL Server.

I am in a similar boat (using both SQLExpress and MySQL for various small projects) and have come to the conclusion I will not be moving to the hosted SQL database anytime soon. Take a look a the following link to a lengthy Microsoft thread, but the consensus is that prices have been raised significantly and performance lowered compared to past plans on Azure. Microsoft also started using a new term "Data Transfer Unit" (DTU) to confuse the issue even more rather than going with IOPs or MB/s.

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/d9af6c45-3552-4baf-8046-524c3f0a1399/new-sql-database-editions-performance

To summarize: because Microsoft switched from pricing on database size to pricing on dedicated resources, the transfer rates are very low unless you upgrade to the P1 ($465/month) database. I doubt you are paying that much for your dedicated server right now. Microsoft also removed the ability to burst, so people whose use cases need low latency or involve infrequent but high volume data access (interactive websites, large imports, database restores) are getting hit the hardest. The following link shows recent benchmarks and they are measured in MB per MINUTE, not second.

http://cbailiss.wordpress.com/2014/09/16/performance-in-new-azure-sql-database-performance-tiers/

As always, YMMV, but unless you are just slowly running background jobs consistently throughout the day and can tolerate high latency, your current VM will be much faster. Also expect to be paying more for the more redundant and highly available service. And unless your 50 websites already exist in the same physical database, you would need to either change your database and code to allow multitenancy or pay for 50 separate instances of SQL databases.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.