We have a client whose back office application is hosted in Azure and they have utilised Power BI to create an operational dashboard. The Power BI dashboard queries the Live SQL Azure Database every 2 hours. The database is set up as a Premium 2 performance tier.
The refreshes would cause 10 -15 minutes of very slow performance on the Live Database. The database. however was performing fine outside of these refreshes. We had a few ideas on how to fix the issue:
- Scale the database to a Premium 4 performance tier
- Create a secondary read-only replicated copy in another region
- Script a restore from Live 15 min before each PowerBI refresh
The client's budget unfortunately ruled out the first two options. The third option would be done at almost no extra cost as we had a SQL Azure Elastic database pool in which we store all our non-production databases. This left us with a script to write. The process that needed to be scripted was as follows:
- Restore a copy of the live database with a temporary name to ensure if the restore didn't finish in time the Power BI query would not fail.
- Delete the outdated database.
- Rename the new database with the correct name.
The logical choice was to use Azure Automation and create a script in PowerShell. We had previously set up the Azure Automation, which created the Azure "Run As" Account, using the following guide.
Azure Automation made it quite straight forward however it did have a few quirks. One of the quirks we had to cater for was the fact that you can't restore to an elastic pool that is on a different Performance tier or server to the source database. For example you can't restore a Premium 2 database to a Standard Pool. The script caters for this and restores the database and then puts it in the pool.
The scheduling component of Azure Automation leaves a lot to be desired and doesn't cater for repeatable scenarios that need to happen only during certain hours. We decided to use the Webhook function of the Azure Automation and trigger it through an Azure function using CRON expressions to trigger the required schedule.