01 Jun An overview of the Azure SQL Database
The Azure SQL Database offers a lot of advantages when building a cloud based application. The database itself also has some constraints that can be overcome with complimentary Microsoft Azure features.
Enterprise feature set
The Azure SQL Database is a cloud service which means updates get rolled out much faster than on-premise updates. As such, The Azure SQL Database is already running SQL Server 2016. In addition, there is only one edition which is equivalent to the Enterprise edition in the on-premise version. This means that organisations benefit from the following features:
- Online indexing
- Column store indexes
- In memory capabilities
These features alone can provide significant performance improvements.
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 making 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
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 Database. Many of these features are using Machine Learning to analyse the 1.7 million databases that are currently hosted in Azure. This allows Microsoft to build models that can then be applied to each database individually.
This dashboard is a tool, released at the end of April, 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, the dashboard itself provides recommendations in the following areas:
- Schema errors
- Parameterisation of queries
- Creation of indexes
- Removal of indexes
With index recommendations the dashboard can implement the changes and then evaluate the effectiveness of each of the changes. It does this by implementing the following steps:
- Implement change
- Compare baselines
- Rollback change if necessary
All of the baselines are captured through another new feature of SQL Server 2016 called the Query Store and because online indexing is available, there is no interruption to the database when this process takes place.
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 an organisation has multiple client databases, then they can all be out in a group. This allows 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 level so long as they do not all spike at once.
Again, the use of Machine Learning allows the management console for the databases to provide recommendations for the use of groups, including an estimation of the saving that an organisation would make.
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 writes the data can access it, thereby preventing database developers and administrators being able to read the data. The clever point here is that reality functions are still supported queries.
Data Mining allows for content to be stored in the database but only to be returned in an un-masked format to privileged users, all other users would see the masked format of the data.
Here are a couple of features that can complement a migration to Azure SQL Database.
1) 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
2) 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.
As with any change in technology, there are always new advantages to benefit from and new constraints to work within.
Transactional consistency becomes harder to achieve; the more features are in use. Make sure that as an organisation you are fully aware of the required transactional consistency. Aphyr offer a great tool for checking this.
Again, with a growing number of features being combined, the ability to make 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.
The Azure SQL Database 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-premise database it is replacing, then there will be a reduction in the overall performance of the application using the database.
For more information, help or advice on Azure SQL Databases, and how this works in relation to your solutions, contact us today. Our team can review your existing systems and provide advice and support.