I’ve been presenting on Azure SQL Database to groups all over the world. A common question that I’m asked is around maintenance on Azure SQL Databases. Since backups are handled by the platform, some think that index and statistics maintenance and consistency checks are also handled by the platform. This just isn’t the case. The customer is still responsible for regular maintenance. With that said, certain safeguards are in place to help contend with physical corruption, however logical corruption is up to the consumer to check for, therefore I still recommend clients to run their own consistency checks.
Azure SQL Database doesn’t come with a SQL Server Agent like Enterprise and Standard Editions. This means you have to find another way to schedule maintenance rather than manually running scripts on a daily or weekly basis. There is some good news, we have a number of different ways to schedule jobs.
- Linked servers
- Database Maintenance Plans
- PowerShell
- Azure Services
- Elastic Jobs
Using a linked server on an existing on-premises server or an Azure VM is one of the most common methods to schedule jobs. Most of my clients use this method, they just reference the linked server in the job step to call the stored procedures.
Database Maintenance Plans are also a common method, you just reference the connection string in the step, provide login credentials and let it run.
Working with many aspects of Azure is simplified with PowerShell. Running database maintenance through PowerShell scripts isn’t much different than using T-SQL. With using PowerShell, you still need to utilize some type of scheduler to automate running the script.
Azure Services include Azure Automation, which to me is a fantastic method to create and run processes to manage your Azure platform. This service allows you to create runbooks to do everything from provisioning, monitoring, and more. There are over 100 built in runbooks that you can select from. All of this is driven by PowerShell too.
Elastic Jobs is another Azure based service that can be used with elastic pools. This option can be used to run processes against an entire pool, or custom created groups.
Regardless of your method of scheduling your tasks, for the most part, you treat Azure SQL Databases like any other database. They need regular maintenance and tuning just like your on-premises databases.
2 thoughts on “SQLskills SQL101: Database Maintenance for Azure SQL Database”
Tim,
>With that said, certain safeguards are in place to help contend with physical corruption, however logical corruption is up to the consumer to check for, therefore I still recommend clients to run their own consistency checks.
Do you stand by that statement or is it now out of date?
Hi Mark, I absolutely still encourage customers to run DBCC CHECKDB on all their environments. On-premises, IaaS, and PaaS (Azure SQL DB and Managed Instance).