This article is more than 1 year old
Microsoft improves Azure SQL Server cloud service, simultaneously makes it worse
SQL Server compatibility better, reporting as a service chopped
Microsoft has announced improvements to its Azure SQL Database service, which offers a managed SQL Server database with patching and operating system maintenance handled automatically by the cloud service.
At the Professional Association for SQL Server (PASS) summit currently under way in Seattle, Microsoft Data Platform VP T.K. "Ranga" Rengarajan said that the service will be enhanced for greater compatibility with the full SQL Server product. Currently, Azure SQL Database supports only a subset of the features in the standalone version.
Coming soon in preview are a larger maximum database size (the current limit is 500GB), parallel queries (using multiple threads for query optimization), Common Language Runtime support (enabling .NET code to run within queries) and XML indexing for XML column types.
There will also be support for in-memory columnstore queries in the premium version of Azure SQL database. Columnstore indexes are a type of in-memory database, enabling much faster queries at the expense of easy updating. They are therefore suitable for large read-only datasets, such as those used by business analytics.
The announcement is good news for SQL Server developers puzzled by the differences between Azure SQL and plain old ordinary SQL Server, which can make migrating a database between the two a painful experience.
Here is an odd thing though. A few days ago, on 31 October, Microsoft discontinued its SQL Reporting service. This service used to offer SQL Server reporting as a managed service along the lines of Azure SQL. Now that it is withdrawn, the only way to use SQL Server Reporting Services (SSRS) on Azure is to install SQL Server on a virtual machine (VM) and manage it yourself.
The official notes on why managing SSRS yourself is preferable are telling:
SQL Server Reporting Services (SSRS) running on Azure Virtual Machines provides the full native mode feature set including: scheduled reporting, data driven subscriptions, email subscriptions, code segments and custom extensions, domain joining capability, and windows authentication integration. Additional benefits include superior data resiliency and the ability to suspend virtual machines when not in use, so you pay only for the hours that you use.
Overall it is a confusing story. Should you use Microsoft’s database as a service, or simply use Azure infrastructure to run SQL Server on VMs?
What to choose: SQL Server or SQL Database?
If performance is critical, the VM option is likely to be better, especially with recently introduced “D” series options that include up to 800GB of local SSD disk. SQL Server 2014 has a feature called Buffer Pool Extensions that uses SSD to optimize performance.
The cost of running SQL Server on a VM is higher though, and not only in terms of extra time spent on maintenance. The SQL Server licensing costs can be more than that for the VM itself, so for example a D14 VM with 16 CPU cores, 112GB of RAM and 800GB SD is currently £1,236.53 per month plus a mere £60.46 for the low-end SQL Web edition, but an additional £2,840.89 per month if you need SQL Server Enterprise.
Even if you only want Reporting Services, you have to pay for the whole of SQL Server, as the licence cannot be broken down.
If Microsoft were to evolve Azure SQL Database to full parity with SQL Server, Reporting Services included, the company would miss out on these hefty licensing fees. Figures like these illustrate how much of the old Microsoft approach to enterprise computing remains, even in its cloud incarnation. ®