Using Ola Hallengren's scripts on Azure SQL Databases
Ola Hallengren's SQL Server Maintenance Solution is a free set of scripts for database integrity checks, and index and statistics maintenance. It is used worldwide and has been voted as Best Free Tool in the SQL Server Magazine Awards. It is designed for on-premises SQL Server databases, but this blog post will describe how you can use it also for Azure SQL Databases.
Azure SQL vs on-premises SQL Server differences
Azure SQL databases are different than on-premises SQL Server, in that Azure SQL only gives you access to individual databases at a time. SQL Server gives you simultaneous access to all databases of the same instance.
A cross-database query such as: SELECT * FROM otherdatabase.dbo.exampletable
- Works fine on SQL Server (as long as otherdatabase belongs to the same instance as your current database)
- Gives an error on Azure SQL databases. Unless you use Azure SQL Managed Instance (which it meant for maximum compatibility with on-premises SQL Server databases).
This is an important difference, because Ola Hallengren's scripts are usually installed in only one database per instance of SQL Server. For instance the master database. They are written so that they can automatically detect which other databases you have in your SQL Server instance and include them.
Another difference is that on-premises SQL Server includes SQL Server Agent. In Azure SQL databases, you will have to schedule your scripts in another way.
Installing Ola Hallengren's scripts in Azure SQL database
Here's what you need to do. You can't run the complete installation script MaintenanceSolutions.sql. It works only on on-premises SQL Server.
Instead, download the following scripts:
- CommandLog.sql
- CommandExecute.sql
- IndexOptimize.sql
- DatabaseIntegrityCheck.sql
(the DatabaseBackup.sql script is not applicable for Azure SQL Databases)
Run the scripts in the order above. You will need to install the scripts in each database.
Test the installation by running something like:
1EXECUTE dbo.IndexOptimize
2@Databases = 'USER_DATABASES',
3@FragmentationLow = NULL,
4@FragmentationMedium = NULL,
5@FragmentationHigh = NULL,
6@UpdateStatistics = 'ALL',
7@OnlyModifiedStatistics = 'Y'
If you specify @Databases = 'USER_DATABASES', it will include your current database (while on SQL Server it would include all user databases). For more examples, see here.
Scheduling the maintenance scripts
Since Azure SQL databases don't have any SQL Server Agent (unless you use a Managed Instance), you will have to find another way of scheduling the scripts.
There are multiple options:
- Elastic jobs is a useful option, but it works only if you have an elastic pool. They are still also only in preview.
- Logic Apps is another useful option, but can be difficult/expensive to setup if your databases are on a private network.
- Timer triggered Azure Functions is another possibility, but is more work to create and setup than Logic Apps. They also are more difficult/expensive to host if your databases are on a private network.
Depending on your environment, you can probably find more ways. You may even use different scheduling solutions for different databases, depending on which network they belong to.
Good luck!