Administering Azure SQL Database

by | Jul 17, 2018

photo by Gellinger

Azure has a Platform as a Service solution called Azure SQL Database, it’s when you have a database in a cloud and never worry about the infrastructure or anything else, almost like magic. But does it mean that database administrators have nothing to do anymore? Let’s have a look at the main functions DBAs used to do with SQL Server before the Cloud and see how it works now.

Server upgrades and patching

As there is no infrastructure visible to Azure SQL Database users, they don’t need to worry about system upgrades and patching, it’s done automatically and unnoticeable to users.
Similar to SQL Server, when a new version is released every few years, Azure SQL Database does the same. Some organisations tend to stay on the previous version of a product for different reasons. In Azure, they would want you to migrate to the new version as soon as possible as there is no fun in supporting multiple versions. Last time when the new major version became available, there were two upgrade options: create a new instance and migrate your current database there, or upgrade existing database to newer version. The latter sounds more risky, however, Azure guarantees no failures and even no noticeable database interruption for users.

Database provisioning and size management

Creating a database is still a thing that require some skills. There is an option to click very few buttons on the Azure portal to create a SQL database. There was such option in on-premise SQL Server as well, you could use all the defaults when creating a database, almost everything could be changed later if needed. Some of the options have disappeared in Azure SQL database. No need to think about database files and recovery model, it is all handled by Azure. Most of the options, like switching “ANSI NULLs” and “concatenation with NULL returns NULL” on and off are available. However, Azure supposes that there is only one database option that matters, the pricing tier, it defines maximum database size and transaction load.

Security and user permissions

Not much changes in this area, security and access is still a complex matter and an organisation still needs someone to manage that. Microsoft has kept a concept of logins, users and roles from on-premise SQL Server. Also, similar to SQL Server, in SQL Database security could be bound with Azure Active Directory. So, there is still a need for an admin to make sure right people have right level of access in SQL databases.
Advanced threat protection option provides all the security you need to protect your Azure databases from the unauthorised connection. This could be set up on a server-level, it works for all databases bound to logical server altogether once set up. It is quite cheap, so it totally worth enabling.
You can also choose to collect events on the database level or the Server level at the cost of storage. Once it’s enabled, you have full visibility of every event happened in the database, every query that run. That creates quite a lot of log records, so it actually only makes sense if you know what type of information you are after.

Data encryption

The same as the previous point, this is about making sure the right people get an access to right things. Database file encryption could be switched on and off by clicking one button in Azure database, there is no need to deal with encryption certificates.
Similarly, the Always encryption feature starts encrypting after a few buttons are clicked. This is a feature for individual columns encryption; only those people who have the key are able to see the value of these columns. A key could be stored in Windows certificate storage or Azure Key Vault. You probably need someone to know how it works.

Backing up and replication

Azure SQL Database backs up automatically, you don’t even need to switch anything on. That includes full, differential and even transaction log backup; the Recovery point objective is 5-10 minutes. Backup files retention period depends on the pricing tier, but no less than 7 days. There is an extra option for storing weekly, monthly and yearly backups for a longer time at the cost of storage.
In Azure SQL Database things are organised differently, including system databases. There is only one system database left and it is automatically backed up, so you probably don’t even need to know about it.
If your database is too important and you can’t afford the database being offline for the time it takes to restore it from a backup, you probably need a database replication enabled. Geo replication, i.e. replication to another part of the world, could be enabled in a few clicks. As this is another database, it will cost you the same money as the original one.

Database restore

Making a copy of the database is easy, but restoration is always harder. It is easy to do a Point-in-Time database restore from the Azure portal. It always creates another copy of the database rather than replacing an existing one. In case if you have restored database because something has caused a data loss, you may want a replacement to happen. This operation doesn’t have a simple button on the Azure portal (yet?), it require writing and executing a PowerShell or T-SQL script.
In case of swapping primary and secondary between geo-replicated databases, it’s just one click.

Performance tuning

Azure analyses the data usage and generates suggestions which could be found on the portal. For example, it would suggest to create an index on the table to improve performance based on the usage patterns. You can visit recommendations page from time to time and choose if you want to follow them or not. Also you can choose automatic tuning, so Azure will create the index it found missing automatically. The good news is that it suggests to drop indexes as well if they are not used or not effective, so you won’t end up with hundreds of indexes on a table that slow down the performance.
There is an option to create alerts to send an email if DTU usage is high or database size is approaching a threshold. At that point you should probably start thinking on upgrading the tier.
Do you remember Query Store feature in on-premise SQL Server? It tracks all the queries executed on the database and stores query statistics and execution plan. It is available in Azure SQL Database as well, and its interface is more user-friendly; you can find it under Query Performance Insight menu on the Azure portal. This should be understandable to developers, so they can take actions on high resource consuming queries, e.g. try to optimise them.
Summarising all above, Microsoft put a lot of effort into creating a platform that could be fully managed by machines after you have agreed with that and clicked a few buttons. However, all of these settings are not easily understandable to a person with no SQL Server database administration background. It will either require a part-time DBA to be available for an organisation, or database developers and users should extend their skillset to understand Azure portal features and settings. Knowing how fast it changes and new features are introduced, this is not a trivial task.
Data masseuse

Kate blogs about the details that make the Data Warehouses work.
Read more from Kate.

Submit a Comment

Your email address will not be published. Required fields are marked *