How to perform index maintenance on Azure SQL Database – Reviewed

A few months, I wrote a blog post on how to perform index maintenance on Azure SQL Database.
I thought my solution with Ola Hallengren maintenance scripts worked fine, however, I got some comments on the post and on Twitter that there was an issue with my solution.

After analyzing and retesting it again, it turns out that there was indeed an issue. During the setup, I tried to create the IndexOptimize stored procedure in the master database but that is not allow.
So, I created a management database and created the stored procedures in that DB. Now I’ve noticed that when you do a select * from sys.databases in the user database, it only returns the database itself and the master…
It’s also documented here https://msdn.microsoft.com/en-us/library/ms178534.aspx#Anchor_1

This was an issue because the index optimize stored procedure is also using sys.databases to determine the databases. So, the runbook was working fine, except it was only doing an index optimize in my management DB and nothing more…

I’ve contacted Ola on this and he told me that sys.databases is not the only issue. The problem is also that Azure SQL Database does not support three-part-naming (DatabaseName.SchemaName.ObjectName). That means that it is not possible to have a stored procedure in one database, and do something in another database.

Workaround

After investigating, I’ve found a possible workaround for this issue.
The first step that you have to do is creating the IndexOptimize stored procedure in every user database.
Secondly, I’ve modified my runbook so that the selection of the user databases is not done with the stored procedure but inside the runbook.

I’m first going to connect to the master database to retrieve a list of all the online user databases.
Once I’ve got it, I connect to each of the user databases separately in a loop, and execute the index maintenance procedure.

This solution seems to be working so far. I know it is not (yet) as flexible as Ola’s scripts but it is certainly a good step in the right direction.
You can find the new script on the original post.

Good luck with it and if you have any issues with it, please let me know.

Pieter

How to view Azure SQL Database Audit Logs?

When you enable auditing on you Azure SQL Database, it will capture database events and it will write them to an audit log on your Azure Storage Account.
In fact, the audit is actually an Azure Table which will keep the data according to the retention period that you have specified. More information about auditing on Azure SQL Database can be found here.
The only “issue” with auditing is that you cannot read the Azure Store Table directly from the portal. You’ll have to use different tools to analyze your audit logs.

The Azure Portal Dashboard

The Azure Portal Dashboard gives you an overview and summary of the events that have happened on your database.
You can see the number of schema changes, login failures, data changes, stored procedures, …
This dashboard is only available in the new portal.

 

Azure Storage Explorer

The Azure Storage Explorer is a free tool that you can download from Codeplex which allows you to read the Azure audit log file in detail. With the Storage Explorer, you can see and configure all your storage account components.
Once you have installed the tool, open it and click on add account. Specify the storage account and the access key where the audit table is stored.


When you’re connected, you’ll see a folder Tables, where you can find your audit log. By default, the name of the audit log always has the same structure. It begins with “SQLDBAuditLogs” + “the name of your server” + “the name of your database” + “the date the audit was activated”.

When you click on the table, the audit log will open and you can see all the tracked events that have been logged.
Be aware that the eventtime might be different than the time where you are located. You also have to take into account that it can take a sometime to open the audit log if many events are tracked.
You also have the possibility to download the logs locally. Next to the eventtime you also see the server and database name, the application that executed the query, the type of the event and even the SQL statement or stored procedure that have been executed.
You can really see who did what and when on your database and can be useful to troubleshoot your database.

In the same audit log, you can also see the number of AffectedRows, ResponseRows and how many time it took for the server to execute the query.

Excel and Power Query

A template can be downloaded from the Azure Portal to help you analyze the audit logs. The Excel file allows you to connect to your Storage Account and use Power Query to import the log information.

I won’t explain how you can use the Excel file because Microsoft has already a detailed explanation in the Excel file itself

Power BI

Microsoft also released the Power BI Azure Audit Logs content pack which can help you easily analyze and visualize the wealth of information contained in these logs.
You just need to connect to your storage account through the content pack and a nice dashboard will be created for you automatically.

More information can be found on this MSDN blog post.

If you should have other ways to connect to the audit logs, please do share it with me. I’m always interested in it.

Pieter