Azure SQL Databases - An overview

Azure SQL Database offers a lot of advantages when building a cloud based application, it also has some constraints that can be overcome with complimentary Azure features.

Enterprise feature set

Azure DB is a cloud service which means updates get rolled out much faster than on premises updates. As such, Azure DB is already running SQL Server 2016. In addition, there is only one edition which is equivalent to the Enterprise edition in the on premises version. This means that you benefit from the following features:

• Online indexing
• Partitioning
• Compression
• Column store indexes
• In memory capabilities

These features alone can provide significant performance improvements

Temporal tables

Temporal tables are a new feature of SQL Server 2016, and are already available in Azure DB.

They allow the database engine to take the heavy lifting out of tracking when data changes. By feeding a couple of special columns related to the time when changes take place, and the name of a history table, the database engine will work out when a record should be added to the history table.

Retrieving data from the table is done in the same way as any other table. When you want to make use of the temporal element of the table some new keywords can be used:

• AS OF - returns the values on a particular date
• BETWEEN - returns all values that occurred between two dates

This makes recording the data very easy and the retrieval of the data just as easy.

Management

Microsoft are working towards the concept of DBaaS (Database as a Service). To achieve this they have introduced many features to help the management of Azure DB. Many of these features are using Machine Learning to analyse the 1.7 million databases that are currently hosted in Azure. This allows them to build models that can then be applied to each database individually.

Performance dashboard

This is a tool released last week which allows the visualisation of the performance of the database. It can show slow running queries and make recommendations for improving the performance. At present it provides recommendations in the following areas:

• Schema errors
• Parameterisation of queries
• Creation of indexes
• Removal of indexes

With the index recommendations it can implement the changes and evaluate the effectiveness of the changes. It does this by implementing the following steps:

• Baseline
• Implement change
• Baseline
• Compare baselines
• Rollback change if necessary

All of the baselines are captured through another new feature of SQL Server 2016 called the Query Store.

Because online indexing is available there is no interruption to the database when this process takes place.

Elastic groups

A benefit of being a cloud service is the ease with which the size capacity of a database can be changed. A database can be scaled up for an intensive process and scaled back down again after the process has completed. This is a very useful feature for a single database.

If you have multiple client databases then they can all be out in a group allowing for them to flex without having each of them having committed resources, e.g. ten client databases can reside in the same group with a finite amount of resources allocated and each of them can spike to a certain Levi so long as they do not ask spike at once.

Again, the use of Machine Learning allows for the management console for the databases to provide recommendations for the use of groups, including an estimation of the saving that you’d make.

Security

As with all aspects of data, and cloud, security is paramount. SQL Server 2016 brings some new features that will protect data in transit, at rest and in applications.

All content is encrypted at storage level so the data can only be accessed via Azure DB.

Always Encrypted allows specific columns to be encrypted so that only the application that write the data can access it, thereby preventing database developers out administrators being able to read the data. The clever point here is that reality functions are still supported queries.

Data Making allows for content to be stored in the database but only to be returned in an unmasked format to privileged users, all other users would see the masked format of the data.

Complimentary features

Here are a couple of features that can complement a migration to Azure SQL DB.

Azure search

Azure search is built on Lucene and Elasticsearch. Microsoft have enhanced the scalability of the solution and provided it as a feature designed to search databases out documents. This means that the SQL full text search need not be used. Some of the advantages of Azure search include:

• Content highlighting
• Facets
• Suggestions

Redis cache

For those repeated calls to retrieve a set of items a Redis will provide a cache to minimise the number of calls to the database.

Warnings

As with any change in technology, there are always new advantages to benefit from and new constraints to work within.

Transactional consistency

Transactional consistency becomes harder to achieve; the more features are in use. Make sure that you are fully aware of the required transactional consistency.

A tool for checking this can be found at http://aphyr.com/tags/jepson

Backup

Again, with a growing number of features being combined, the ability to take a backup that retains the integrity of the data becomes harder. This should be carefully considered as different features are combined.

Corporate requirements for b backup retention should also be taken into account as Azure will store automated backups for a period of time but if your organisation is obliged to store backups to comply with legislation then this should be considered.

Performance

Azure DB is in the cloud, that is, all access has to get to the Microsoft data centre and back again, navigating firewalls and the internet. This journey will mean that if the performance of the database is identical to the on premises database it is replacing, then there will be a reduction in the overall performance of the application using the database.

Sources

This information was all presented during SQL Bits XV

• Andre Kamman - RE-architecting your old school database into Azure
• Raoul Illyes - Operational insights with Azure temporal tables and PowerBI
• Vladimir Ivanovic - Intelligent Database as a Service
• Ron Matchoro - Azure SQL Database Security

References

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-v12-whats-new/

https://azure.microsoft.com/en-gb/documentation/services/sql-database/