I am a huge fan of managed database services, no matter which cloud platform they’re running on. The simple reason is that I am not a huge fan of managing the automation for the basic things like backups, patching and high availability myself anymore. There is a trade-off though when you’re using someone else’s automation to manage your environments, the price you pay is the limited visibility of what’s happening underneath the covers.
I was reminded about this the other day as I was attempting to restore a database from one Managed Instance to another, a pretty standard thing to do for certain, and was facing an issue with it. In the end the problem itself was easy to fix but difficult to figure out.
The Problem
Restoring a database in Managed Instance is super easy, it’s as simple as running a PowerShell command or clicking some stuff around in Azure Portal, whichever way you prefer to do it. I have done this quite a few times and always without any issues, however this time I was greeted by the following error message.
Restore-AzSqlInstanceDatabase: Long running operation failed with status ‘Failed’. Additional Info:’An unexpected error occured while processing the request. Tracking ID: ‘xxxxx-yyyyyy-xxxxxx-yyyyy”
Thanks a lot for this helpful errors
Admittedly, I have seen all kind of errors popping up in the cloud, but this was pretty new. I had a pretty hard time figuring this out, and when testing the restore with other databases from the same instance, they worked flawlessly. I was pretty convinced for a while that this had to do with the size of the database (it was over 2 TB compared to others that were much smaller), but turns out that it was something completely else entirely.
So yeah, the database I was restoring was partially contained. To be able to restore contained databases you’ll need to enable the configuration option to enable contained database authentication. This will allow the creation of contained databases.
So after running the sp_configure (down below), the restore would eventually run flawlessly for that database as well.
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'contained database authentication', 1
GO
RECONFIGURE
GO
sp_configure 'show advanced options', 0
GO
RECONFIGURE
Wrapping Up
For many cases it doesn’t matter what flavor of SQL Server you run in the (or any) cloud, it’s still the same engine across most of the deployment options (except the Hyperscale Azure SQL Database which is quite a different kind of beast). Platform as a Service databases also make your life easier, most of the time anyway. However, in this particular case, had I done this on an SQL Server VM I’d had gotten a much clearer error message to start with.
Leave a Reply