You’ve built your first Managed Instance, and now you wanna play around with real live data. You’re going to need a backup in the cloud to do it – SSMS doesn’t have a drag-and-drop option.
Big picture, here’s what we’re going to do:
- Set up a container in the cloud to hold our backups
- In our regular SQL Server, set up security so we can back up to that cloud container, and then back up a database to it
- In our Managed Instance (MI), set up security to access the cloud container, and then restore the backup from it
For steps 1 & 2, follow Steve Thompson’s excellent checklist, Backup SQL Server to an Azure Storage Account. I’ve tried several other checklists, but as of 2018, this was the only one that worked for me out of the box. (He’s @Steve_TSQL and a nice guy.)
The restore syntax in Managed Instances is a little different.
To create the security credential, the syntax is a little different:
CREATE CREDENTIAL [https://MYSTORAGEACCOUNTNAME.blob.core.windows.net/MYCONTAINERNAME]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'MYBIGLONGKEYSTRINGFROMTHEAZUREPORTAL';
The portions you have to change in there are:
Then, to restore the backup, the syntax is a little different too:
RESTORE DATABASE MYDATABASENAME
FROM URL = 'https://MYSTORAGEACCOUNTNAME.blob.core.windows.net/MYCONTAINERNAME/MYBACKUPNAME.bak'
The portions you have to change:
- MYDATABASENAME – the target, new database you’re creating
You can also restore databases asynchronously – meaning, fire off the restore so that it runs even when you’re not connected.
Want to check progress? This is about to get technical.
If you don’t care about checking progress, you can bail here. Happy testing.
You can’t use WITH parameters on the restore, which means you don’t get status messages. To check restore progress, use sp_WhoIsActive, which works beautifully:
In that screenshot, session_id 143 is me (Doctor) running the restore command, restoring StackOverflow from backup. If I scroll across to the right of sp_WhoIsActive’s output, I can normally check restore progress, but it’s null:
But it’s null.
Notice how there’s another restore running from 127 at the same time, restoring a guid-based database? With the wacko service name, and program_name RestoreService? Azure Managed Instances are the ones really doing the restore, and you can see the percent_complete is populated for their session. They’re doing more than just restoring locally, too. In the error log, while a restore runs, you’ll also see interesting messages about how Azure is setting up the replication for me to the other nodes:
And even system databases get new names – master is replicatedmaster, for example:
And they really do mean replicated – as of this writing, you can create tables in master, load them full of data, and get your instance into trouble on allocated disk space. (You get 32GB by default, and master is included in that number.)
Want to go from cloud to on-premises?
You can go in the other direction, too: since both your on-premises SQL Server and Azure SQL Managed Instances can access the same Azure Blob Storage, you can take a copy-only backup in Azure SQL Managed Instance:
BACKUP DATABASE MySoul
TO URL = 'https://mountchimborazo.blob.core.windows.net/sqlbackup/MySoul.bak'
(This syntax requires me to have already created the credential as shown at the start of this post, a SHARED ACCESS SIGNATURE credential pointing to this URL.)
Then back on-premises, just do a restore pointing to that file and…awww….
That’s fair – MIs are a newer version of SQL Server than what we have on-premises. I wouldn’t be surprised if we got access to a FeatureSwitch to enable exporting data easier at some point in the future, though – Lord knows there’s enough backup feature switches in sys.dm_hadr_fabric_config_parameters:
But that’s a story for another post, and GroupBy is about to start.
Speaking of feature switches, what trace flags are in use on Managed Instances?