I decided to write up a blog post about index maintenance in SQL Azure databases (yes, I’m gravitating towards Azure just like everyone else these days). I had everything planned, what research to do, the case I was going to use, the whole nine yards. It would have been epic, had it not been for Pieter Vanhove, who’s already done it. Worse still, he did it way better than what I had planned.

http://pietervanhove.azurewebsites.net/?p=14137

Somewhat miffed, I decided to play with it anyway (just not write a blog post about it, no, I’m not bitter :P ).

Turns out there’s quite a gotcha with this:

AzureDB does not support cross database queries, which means that, since the runbook Powershell script creates a connection to the MGMTDB, it is ONLY within that database that the queries will execute. You can check this using a simple select * from sys.databases - it will ONLY show you the user database you’re in and the system databases. Ergo, Ola’s script will only execute on the database it is installed in. Okay, you say, let’s create the scripts in the MASTER database (generally a bad idea, but hey) and run them, as if I create a connection to the MASTER database, sys.databases shows every other user database on the server. Turns out that, well, you can’t. There seems to be no way to create objects in the master database, so we’re stuck at the moment.

Ola’s solution is to create his scripts in every user database, but while this works most excellent from a performance and integrity standpoint, the amount of manual labor required to either create new runbooks for each and every database that gets created (and remove said runbook for decommissioned databases) OR create a potentially HUGE runbook that encompasses all databases (that also needs to be manually maintained, ugh) is simply staggering. The hunt for a solution is on.

*EDIT*

Aha! While attending the PASS Summit I got word from Brent Ozar that cross-database queries had arrived to Azure. I’ve yet to track down Ola and ask him about his scripts as the implementation is … maybe not quite as straight-forward as one might think, but the solution is a lot closer. Oh, and I spoke to Pieter as well - he’s updated the original blog post with his workaround. It’s a good example of what can be done with some ingenuity and a runbook. Check it out!