Introduction
2015 has been a massive year for Azure SQL Database. This year has seen the product mature from "an interesting idea" and "something that we should have a look at" to a genuine piece of enterprise software and something that most IT departments are putting onto their future roadmap. Azure SQL Database has assumed the pole position in terms of cloud databases by continuing to enhance their product as well as making it faster, more reliable and easier to use. Here is my “Top 10” list of enhancements for Azure SQL Database for 2015.
Dynamic data Masking
One of the best thing about Azure SQL Database this year was the fact that Azure SQL Database has started to get functionality before the on-premise version of SQL Server. One of these features was Dynamic Data Masking, which isn't slated for release until SQL Server 2016. Dynamic Data Masking allows sensitive database information, such as credit card or phone numbers, to be stored as plain text in the database, but to be easily obfuscated to all but privileged users. For more information about Dynamic Data Masking, check out my previous post on the topic (don't worry, there will be more shameless blog self-promotion to come dear readers!!!).
Row Level Security
Another feature that Azure SQL Database got before on premise SQL Server this year was Row Level Security (which is slated for SQL Server 2016). Row Level security allows data from a table to be filtered depending on who is reading the table. Row Level security can also be used to determine who can perform add, update and delete operations on the table. This level of security is put in place "by a security predicate defined as an inline table-valued function". Microsoft has put out a great article with everything that you need to know about Row Level Security here.
Transparent Data Encryption
Azure SQL Database also released a lot of features that brought it closer to parity with the features that are available in on-premise versions of SQL Server. Transparent Data Encryption (TDE) has been available since SQL Server 2008 and allows a database to be encrypted while at rest. With a non-TDE database, if someone were to steal the physical media that contained a particular database, the person could then just attach that database to another SQL Server and browse the data in the databases. TDE gets around this problem by encrypting the database using a database encryption key (DEK). Databases protected with TDE can only be used on server with their DEK. You can read a bit more about it on a post that I wrote here.
Full Text Search
Another feature that was released for Azure SQL Database this year that is a staple for on-premise versions of SQL Server is Full Text Search. It is available in all its glory so that you can create Full Text Catalogues, Full Text Indexes and use Dynamic Management Views to monitor the population status of your catalogues. The only limitation at the moment is that the Azure SQL version does not support filters. This means that you cannot create Full Text Indexes on Microsoft Word or PDF documents that you have stored in Azure SQL Database. A great overview or everything that is possible is available in this document brought to you by the good people at Microsoft.
Graphical Runbooks
The development environment surrounding Azure SQL Database has also matured greatly in 2015. Runbooks are one of the ways that you get things done in the cloud. Runbooks allow you to create PowerShell scripts to perform maintenance, move around data and perform most of the daily tasks that you would use SQL Server Agent to do in on-premise versions of SQL Server. Previously, all you had to create Runbooks was a text editor. The Azure Preview Portal has now been enhanced so that the Runbooks are now a lot more graphical in nature, and it is a great improvement. The Runbooks are still written in PowerShell, but now those PowerShell scripts are enclosed in blocks enabling you to implement control flow logic in between your blocks, much in the same way that you would do in SQL Server Integration Services. You can also add PowerShell commands directly to the design surface if you just want to do something simple, such as send an email when your Runbook has finished. You fill in the properties for the command through the GUI, and all of the PowerShell code is written for you. Finally, Azure implemented Webhooks, which are a way to start your Runbooks via an http: request. Personally, I really like Runbooks (and PowerShell for that matter), and these are some of my favourite enhancements to come out this year. Read up on these and some other enhancements on the post I wrote about it here.
Azure Data Factory
Azure SQL Server also introduced a new means of moving data around in the cloud called the Azure Data Factory. In much the same way as you would use SQL Server Integration Services to move data around in on-premise versions of SQL Server, you use the Azure Data Factory to move data around in the cloud. Using Data Pipelines, you can create and schedule data movement between various Data Stores. One of the Azure Data Factory's strengths is that it also attaches to other cloud based Data Stores such as Hadoop. I'm currently in the middle of a multi-part series on the Azure Data Factory. You can read part 1 of the series here.
Elastic Scale
One thing that other NoSQL database platforms do extremely well is Database Sharding. Database Sharding allows data to be stored across multiple databases to cope with spikes in demand. These types of spikes in demand are typical of internet databases where demand for database resources may be extremely high during a particular time frame (such as during an "after Christmas sale") and then more constant during the rest of the year. Until recently, this sort of concept could only be created using a custom solution in SQL Server. This year, Azure SQL Database introduced Elastic Scale which provides a development framework to implement Sharding. I've covered Elastic Scale in a 3 part series of posts here, here and here. It contains lots of code samples and everything you would need to implement one of these solutions in Azure.
Elastic Database
Someone in the Microsoft Marketing Department must have lost their thesaurus because another new feature that was also launched this year is Elastic Database. This new feature allows you to group your databases into Elastic Pools, and then share your database resources across all databases in the pool instead of having to set your performance resource for each database individually. Azure also launched a set of tools that make managing large numbers of databases easier including the hugely exciting Elastic Database Jobs (which is essentially TSQL Jobs for Azure). Unfortunately, the pricing model only makes using Elastic Database feasible if you have a large number of databases so we in Azure must wait for TSQL jobs for our "normal" Azure SQL Databases (although you can vote for this Feature Request that I started that will hopefully speed up the process).
Stretch Databases
Although Stretch Databases won't be completely available until the release of SQL Server 2016, I thought that I'd mention it here because it is such an exciting feature. Stretch Database allows you to migrate a table (or tables) from your on-premise SQL Server into Azure SQL Database. Once the tables have been migrated to Azure, they can still be queried as if they are a part of the original database. This feature is great if you have a lot of historical data that you need to keep around for compliance reasons, but the data isn't accessed very often. Everything you need to know about this feature can be found in this article here.
Azure Data Warehouse
Azure Data Warehouse is a purpose built Azure SQL Database specifically designed for data warehouse database applications. Azure Data Warehouse uses Microsoft's Massive Parallel Processing (MPP) architecture to ensure that querying and loading the data is as fast as possible. Azure Data Warehouse also allows you to scale size and compute power so that you only have to pay for what you use when you use it. Find out more about Azure Data Warehouse by reading this article from Microsoft.
Wrap Up
This year has seen a lot of enhancements to Azure SQL Database. As a whole, the platform has pretty much achieved parity with the on-premise version of SQL Server while leveraging the strengths of being a cloud platform. Azure SQL Database is more robust, faster and easier to use than it has ever been, and things will continue to get even better. If you haven't had a chance to play around with Azure yet, make this be the year that you start to embrace the cloud.
