Azure SQL Now Supports JSON Storage and Query

Microsoft has just released into General Availability support for storage and retrieval of JSON data.  JSON is a convenient data format used by web sites, JavaScript, REST APIs, etc.  Using the new JSON functions, you can run a query against an Azure SQL database and return a JSON object or you can store JSON entities as values within Azure SQL tables.

The ability to store and retrieve JSON also means you can combine traditional relational data with no-SQL JSON data and use SQL to query both at the same time and to link relational data records and semi-structured JSON data together through queries.

Read More

Azure SQL Always Encrypted Now Generally Available

Microsoft has just announced that their “Always Encrypted” feature for Azure SQL has now been released to General Availability.

Always Encrypted allows you to consistently store columns of data within SQL tables as encrypted data.  The encryption/decryption happens at the .NET calling layer so that the underlying data at rest is always encrypted.  In order to encrypt/decrypt the calling application has to be registered, have sufficient permissions and access to the encryption keys.

By leveraging Always Encrypted, any DBA level access has no ability to decrypt the data without going through the application tier.  Any direct SQL call will result in encrypted data being returned.

Another advantage to this approach is selective column encryption – only columns specified by the schema are encrypted such as personal information, credit card numbers, etc.

Always in Encrypted in Azure SQL Database

Read More

Azure SQL Now Supports Full Text Search

Azure SQL now supports Full Text Search as part of the V12 platform.

Full-Text Search is now generally available in Azure SQL Database V12. For applications that manage and store character-based data in Azure SQL Database, Full-Text Search provides fast, rich search functionality integrated into the database platform. Full-Text Search is available in all service tiers in Azure SQL Database V12 through familiar TSQL syntax and Microsoft SQL Server tools. In addition to preview capabilities, the feature supports .xml doc types and self-service diagnostic experiences through XEvents. Users can easily troubleshoot and have visibility into issues such as indexing errors or unsupported doc types. This optimizes the search experience and brings it closer to the on-premises capabilities of SQL Server.

Full text search allows for indexing of text content with a SQL database, stored in traditional database records.  Columns such as char, varchar, nchar, nvarchar, text, ntext, image, xml, or varbinary(max) are supported for full text searching.  Once the text is indexing, searches can be performed using TSQL commands.

There are some limitations compared to the full SQL 2014 version:

  • No support for installation or use of third party filters, including Office and .pdf.
  • Customers cannot manage service settings for fdhost, all configurations are being managed by the service.
  • Semantic search, thesaurus and search property lists syntax is not yet enabled.

Read More

Azure SQL Now Supports Azure AD Authentication in Preview

Microsoft has just released a new feature for Azure SQL that allows you to manage your users access via Azure Active Directory.  Previously, authentication was limited to SQL authentication.

This provides an enterprise class method for managing users and providing them access to your Azure SQL database.

aad auth diagram

The details for setting this up can be found here.

Read More

New Elastic Database Pools in Azure SQL Targeted to SaaS Developers

Microsoft launched in preview this week a new Elastic Database Pool model specifically designed to support SaaS developers building massively multi-tenant applications.  In these scenarios, SaaS application vendors provision a database per client and with thousands of clients, each having their own dedicated Azure SQL database.

The challenge in this scenario is that each database is charged on a per unit basis with its own pool of resources.  In many cases, you can have databases that are not using up their allotted resources and therefore the SaaS developer is forced to over provision and therefore be over charged for the databases for each of their clients. 


With the Elastic Database Pool model, the SaaS Developer now can buy a pool of resources that spans across thousands of databases.  In a similar way to packing virtual machines on a host to optimize resources, the Elastic Database Pool allows you to pack databases into a shared pool of database resources by average the peaks and valleys across many databases.  The efficiency gain is dependent on the variability of the database and the number of databases in the pool.  The ideal target for the Elastic Database Pool model are applications where are hundreds of databases provisioned but they are not constantly used and have lots of variability in usage patterns.

While paying more per performance unit than a dedicated database, this can result in significant savings in scenarios where there are a large number of database instances that are not under utilized as the pool averages out the usages across all the instances.  If the Elastic Pool runs out of resources, the SaaS can easy scale up the entire pool.

Read More

With Azure SQL Data Warehouse, You Pay Only for Active Compute Use

One of the key principles of cloud economics is the ability to only for active use of a service.  However, many cloud services don’t actually work that way in reality – for example, Office 365 and Active Directory Premium both are priced per user and not per use.  If your end users only upload one document to SharePoint in a year, you pay the same price as the end user who uses it every five minutes.

Azure SQL is priced on a constant use basis – there is no mechanism for “pausing” an Azure SQL Database.  At a minimum, your Basic database will cost ~$5 US / month.  If you scale up your database to S1 (for example, if you have a database larger than 2 GB) the S1 database will cost ~$15 US / month.  Storage is bundled into the price at its fixed at 250 GB for Standard and 500 GB for Premium.

Azure Data Warehouse is priced differently – it is priced per hour based on how many performance units you use at $0.70 US / hr.  On a monthly basis, this is about ~$521 per month.  In addition, the storage price is independent and not bundled into this price.  You can pause your Azure Data Warehouse and only use it for when you need it.  In addition, you can scale up your warehouse to massive processing power for an hour and then scale it back down or pause the service.

Azure Data Warehouse uses RA-GRS storage which is $0.061 / GB for the first terabyte and then cheaper as your storage increases.  If you have a 10 TB warehouse, the cost of storage is ~$599 US / month. 

Read More

Row Level Security and Data Masking Available in Azure SQL in Preview

Row Level Security

imageAt the end of January, Microsoft launched a new Row Level Security implementation in preview for Azure SQL.

Row Level Security in Azure SQL allows you to set policies using SQL statements which filter data based on the users identity.  This can be done based on the logged in user connected directly to the database or through an application. 

The basic idea of Row Level Security is the ability to filter queries based on user credentials either supplied directly when a user connects directly to the database or passed in by an application passing in a user identity.

The implementation details can be found in this article.

Data Masking

Another new feature available in Azure SQL in preview is Data Masking.  The concept of data masking is that based on policies, you can prescribe that when a user selects data from a table the data can be returned as masked data, e.g. XXXXXXX instead of the original value.  The policy you set allows you to specify:

  • Who receives masked data and who receives original data
  • Which tables and columns are masked
  • Whether to mask based on the source table name / column name or the alias provided in the query
  • Whether to restrict developers directly connected to the database
  • Format of masking based on a set of masking functions

The masking function allows you to mask common types of sensitive information.  For example:

  • Credit Card: XXXX-XXXX-XXXX-1234
  • Social Security Number: XXX-XX-XX12
  • Email Address:

You can also use your own masking patterns using the Custom Text function.

Unlike Row Level Security, Data Masking is set up either through the Azure Portal or through a REST API, not through SQL.

Navigation pane

Read More