Wow, that title was a mouthful.
My dev server is restoring a copy of production data daily, anonymizing it, and creating a dev-grade backup for my developers to restore locally. We're running SQL 2019. Unfortunately, my servers are all in Azure East US, but almost all of my company is based in Spain.
As of today, we had the server produce a standard backup that would be downloaded by a central server here in Spain, so devs can restore without going to the cloud. Restores on developers' laptops are taking up to 180 minutes to complete.
This is where
BACKUP ... WITH FILE_SNAPSHOT comes in. In a few words, it allows backups and restores to go super fast, by storing data directly on Azure storage accounts. There's more to it, but it's not the point here - read more here
So far so good, but...
- Restoring near the server will be fast, but will force developers to traverse the Atlantic when reading from what should be the local database.
- Restoring the snapshot from EastUs to a storage account in West Europe seems to be copying the snapshot over (which is logical), but this means there's no restore performance benefit.
- My devs are really keen on restoring daily, weekly at most to ensure they're on par with production database releases and can troubleshoot data issues that get escalated to the engineering team.
- Manually copying files from East Us to West Europe is not a good option, since it requires daily attention from someone.
What I tried
- Restoring a file_snapshot backup from our East US storage account to a temporary West Europe storage account
- Restoring a file_snapshot backup within our East Us storage account (performances were awful for large queries due to, at least, network latencies)
- Setting the storage to RA_GRS; when I tested it, it would only pair with West Us - this was to speed up normal restores, but it's still meaningful.
What I want to accomplish
I want my devs to be able to restore quickly using the
FILE_SNAPSHOT technology to the nearest storage location. I'll need them to be able to write there using SAS keys. To ensure they can restore quickly, I'm assuming the storage account must be mirrored with a copy that sits here in West Europe, but I don't know how to accomplish this.
Currently, I'm planning to either try and store the backup in a West Europe bound storage container, or try restoring to it after enabling Azure CDN. I'm doubting both of these options because
- FILE_SNAPSHOT backup takes a snapshot of the files in Azure storage, so they will have to be copied across the wire through the Atlantic
- Azure CDN is a read-only system, I'm not sure it will help me with writes in the restore process. If it has to read from the source file, I'd be back to square one.
I'm going to test these out (aka do my homework), but I'm reaching out to see if anyone here has other ideas, has already done this successfully (and wants to share their fix), or know this is not doable and I should file a UserVoice item.
Thanks a bunch
I have been testing CDNs - it does not work. When trying to restore an existing backup to URL (it does not matter if using FILE_SNAPSHOT or not) I'm getting an incorrectly formatted error. I know that the .bak is not corrupted since I can restore directly from the original blob storage. My current guess is that some header has a mismatch between the CDN URL and the actual blob storage. I also tried backing up directly to the CDN URL but that seems to not be supported, which makes sense due to my current understanding of CDNs being read-only systems.
Also tested out backing up to a different storage location. Backups are fast, but the snapshot is still taken locally to the .mdf location - this means restores are painfully slow, and reducing that lag (and the data transfer) is the whole point of this exercise. I cannot really modify the .bak pointers after the backup has been generated, so even copying that data manually across the ocean won't work.
This means I'm officially out of ideas.
I missed out on doing this for a while, but I'd better keep this thread up to date. @DavidSpillet has a few points in his reply. Here is a bit more information that might help people helping me out - you're all awesome.
- We have about 13 (SQL natively compressed) backup files, totaling around 60GB. The approximate size after restoring them is around 270GB.
- Our databases are separated into two systems. System A has 2 databases, System B has 11. Restores are done concurrently per system, so we will have at most 2 restores per developer running in parallel.
- Developers in Spain restores in the office using a wired LAN network, sitting on the same VNET (and building, and floor) the server is in.
- The server is a 16-core, 32 logical CPUs, 96GB RAM monster that was once used to host a shared SQL instance for our developers. Restores in there take consistently between 15-20 minutes.
- The server high-capacity storage (where the backups are held) is based on spinning locally attached disks - but then again, restoring locally is not a bottleneck
- Developers laptops use high-capacity NVMe SSD drives (at least, the new ones. The older model used spinning drives. I'm not minding those as they're being rolled out)
I also have a few more concerns due to the current world situation, namely
- Devs are currently working anywhere, sometimes at the office, most of the time at home. Unfortunately, our client VPN endpoint is in another country, so restoring from the office is a major pain, and I'm trying to account for that, especially considering how the country is looking like (2020-07-29).
- Some of the new laptop models arrived with lower capacity storage (256 GB) and there's nothing I can do to fix that.
- Cannot go on purchasing commercially available software that would do the trick (for example this one) because of budget constraints.