Introducing Azure SQL Database v12

Those of you who are already hooked into Azure SQL Database know that v12 was in Public Preview in December of 2014 and has been available in most regions since February of this year, 2015. Despite Microsoft making a really big announcement about this release not that many people are aware of it. They really should be.

Prior to the v12 release of Azure SQL Database, many people were not enamored of the concept of using a SQL Server database on a platform as opposed to using one on a server (in Azure or earthed) because of the limitations - both in size and the footprint of standard SQL Server functionality. With all the functionality available now, many people need to reexamine their decisions to avoid the use of this platform-as-a-service-means of managing databases.

Considering all the functionality added over the last several months as well as the improvements to performance on the Premium service tier, this platform is like a fully functional Death Star, and I think it’ll surprise you if you get started with it.

Enhancements

Some of the functionality has been a long time coming and now that it’s here it’s pretty exciting. For example, you can have users contained within databases. You no longer have to have a user in both the master database and your local database. This means you have enhanced mobility on these databases. You can move them from server-to-server or even-region-to region as you desire much more easily.

Now you can finally run most of the standard DBCC commands to understand the status of your database through CHECKDB or CHECKTABLE. (Heck, if you don’t want to use the DMVs for querying the system you can even use DBCC INPUTBUFFER now.) I really like access to DBCC because while I’m willing to let Microsoft management platform, I’m still a paranoid DBA and I want to be able to check on the status of the database.

Extended Events is fully available (it’s been there in the background for about a year) within Azure SQL Database.  You can track query execution, deadlocks and other events the same way you would within the standard product. This one is just huge if you’re a performance tuning nut like me!

Microsoft is allowing heap tables now within Azure SQL Database. I see this as something of a mixed bag since I think that most tables should have a clustered index, I kind of liked the requirement, but it sure made SELECT/INTO hard… well, impossible.

V12 now supports some windowing functions through the use of the OVER clause. This opens up tons of query opportunities that previously weren’t available.

XML indexes are available as well and in case you didn’t know, spatial indexes have been available for a while now.

We have Columnstore indexes and table partitioning. You can do online index rebuilds with indexes that contain BLOB columns. That means that your indexes are still available to the people querying the database while you rebuild the index.

If you go for the Premium Tier, you get even more added functionality. With Premium you now have parallel queries. Parallelism is not something to be avoided in queries, just managed and used appropriately. This is one more tool in the toolbox. 

Also at the Premium Tier they’ve directly enhanced performance going after less latency in I/O as well as a general throughput increase in IOPS. If you need performance in Azure SQL Database, you’re going to be able to get it.

There are even some really interesting enhancements that are still in preview that are worth checking out. And worth noting, these are vNext functions that you just can’t do in SQL Server 2014. But if you want to know what’s coming in the next version, check out Azure SQL Database to try dynamic data masking. You are going to be able to specify an algorithm that hides data from data sets as they’re accessed depending on the security level. That’s pretty cool. I’m going to want to test out the performance implications myself. There’s also a new row level security mechanism. Again, performance implications are very interesting, but so is the added functionality.

For a complete listing of all the enhancements, I’d suggest checking out the What’s New in v12 list and the Azure SQL Database T-SQL Reference.

Why Aren't You Trying This?

I know that there are some businesses out there that have legal limitations on where their data can go (although Microsoft is working on those, and there are already a lot fewer limitations than you think). Some of you may not have the connectivity that moving your data management to the cloud is going to require to make it a viable option. Still more of you need to have the sheer power that local iron is going provide and you’ll be using earthed SQL Server instances for quite some time to come. But, for a very large percentage of you, the excuses are gone.  The majority of all functionality of the SQL Server service is available to you within Azure SQL Database. The list of unsupported functions and functionality are almost all directly related to stuff that is primarily about server and OS management, not things you worry about with PaaS (Platform as a Service). The sizes supported are well within the size of the majority of your databases. The performance is going to support you. Plus you get all the benefits of a managed platform. It’s time to start exploring the use of Azure SQL Database as a part of your complete data platform.

Over the next several months, I’ll highlight in more detail some of the functionality available within Azure SQL Database and show you how it works.