Azure VM Backup and Restore Testing
 Share
The version of the browser you are using is no longer supported. Please upgrade to a supported browser.Dismiss

 
View only
 
 
Still loading...
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQAR
1
Azure VM Backup/Restore Testing
2
Brent Ozar and Mike Malone, 2017/05/10. For more info, see:
https://www.brentozar.com/archive/2017/05/building-faux-paas-part-2-choosing-testing-cloud-vendor/
3
Backup Tests
Restore Tests
4
Instance - See note (1) at the bottom of this spreadsheet
Storage (2)
CrystalDiskMark, MB/sec (3)
Backing Up to NUL (4)
To local disk (5)
To UNC path (6)
From local disk
From UNC path
Sizing & Pricing (7)
5
InstanceCoresRAMProc SpeedNetwork SpeedRegionInstance cost/mo w/o SQLLocationDisk cost/moDisksTotal capacity, TBDisks Optimized ForCachingSequential ReadsSequential WritesTo NUL, 1 fileTo NUL, x4 filesTo NUL, x8 filesTo NUL, x20 filesTo local ephemeral, x4 filesTo local ephemeral, x8 filesTo local ephemeral, x20 filesTo L16s SSD, x4 filesTo L16s SSD, x8 filesTo L16s SSD, x20 filesFrom local ephemeral, 4 filesFrom local ephemeral, 8 filesFrom local ephemeral, 20 filesFrom L16s SSD, x4 filesFrom L16s SSD, x8 filesFrom L16s SSD, x20 filesMax data we can restore in RTO from Cost Drivers, in TBLesser of max restore, vs storage capacitySQL Licensing from Cost DriversCost/mo incl storage, SQL EECost per TB per month
6
DS15v2201402.4 Ghz - E52673v3Ext high
East US 2
$1,808Ephemeral$0N/A0.3N/AN/A671661638639
Discontinued tests, drive is so small it doesn't matter, just useful for TempDB or whatever
7
DS15v2201402.4 Ghz - E52673v3Ext high
East US 2
$1,808Managed$61455.1DWRead-only598543
Discontinued tests w/read caching on because performance was so bad.
8
DS15v2201402.4 Ghz - E52673v3Ext high
East US 2
$1,808Managed$61455.1DWOff9819742856827436356757066785105193605276994636564.54.5$5,555$7,977.72$1,771.10
9
DS15v2201402.4 Ghz - E52673v3Ext high
East US 2
$1,808Managed$1,2291010.0DWOff9809402607107075516136737956024523574997024606084.24.2$5,555$8,592.12$2,058.09
10
DS15v2201402.4 Ghz - E52673v3Ext highCentral$1,808Unmanaged$10255.0DWRead-only6526474906376376374864864862653354154002.72.7$5,555$7,465.72$2,718.19
11
H16161123.2 GHz - E5-2667 v3Very highWest US$2,394Ephemeral$0N/A2.0N/AN/A
Haven't done any testing on these, just using H16mr backup numbers for server costing. If we move forward with these, need to do a round of tests.
224515.42.0$4,444$6,838.51$3,419.26
12
H16m162243.2 GHz - E5-2667 v3Very highWest US$3,207Ephemeral$0N/A2.0N/AN/A292424674821472217221781067112211581996207311001710168419252200224515.42.0$4,444$7,651.70$3,825.85
13
H88563.2 GHz - E5-2667 v3Very highWest US$1,198Ephemeral$0N/A1.0N/AN/A
Haven't done any testing on these, just using H8m backup numbers for server costing. If we move forward with these, need to do a round of tests.
12538.61.0$2,222$3,420.00$3,420.00
14
H8m81123.2 GHz - E5-2667 v3Very highWest US$1,604Ephemeral$0N/A1.0N/AN/A292925803261100122512685181134119755686112538.61.0$2,222$3,826.22$3,826.22
15
H8m81123.2 GHz - E5-2667 v3Very highWest US$1,604Unmanaged$10255.0DWN/A17366
Can't attach managed storage. Unmanaged storage perf is horrible.
16
L16s161282.0 GHz - E5-2698B v3Ext high
East US 2
$1,571Ephemeral$0N/A2.8N/AN/A818818270779775748N/AN/AN/A7757817754575706824.72.8$4,444$6,015.65$2,148.45
17
L16s161282.0 GHz - E5-2698B v3Ext high
East US 2
$1,571Managed$61455.0DWRead-only6746702284764854794904854834794864862643364162163144002.72.7$4,444$6,630.05$2,413.93
18
L32s322562.0 GHz - E5-2698B v3Ext highCentral$3,143Ephemeral$0N/A5.6N/AN/A16371633267109915611540N/AN/AN/A137915341508N/AN/AN/A64588612118.35.6$8,889$12,031.30$2,148.45
19
L32s322562.0 GHz - E5-2698B v3Ext highCentral$3,143Managed$61455.0DWRead-only145510152817708109376457989136577818103495067093074716914.74.7$8,889$12,645.70$2,665.21
20
L4s432Very high
East US 2
$393Ephemeral$0N/A0.7N/AN/A208208
Discontinued tests on the L4s because the ephemeral storage just didn't cut it, nor will managed disks on this instance type.
21
L4s432Very high
East US 2
$393Unmanaged$2011.0N/ARead-only203126
Discontinued tests on the L4s because the ephemeral storage just didn't cut it, nor will managed disks on this instance type.
22
23
24
Cost Drivers
25
When you touch these, the formulas in the VM prices will change:
26
Recovery Time Objective for "oops":
2
In the event of an "oops" query across all databases, how long are we allowed to be down for? This is part of what drives how much data we can store per server.
27
Managed P30 disk $/mo, ea:
$122.88
28
Unmanaged S30 disk $/mo, ea:
$20.48
29
SQL licensing $/mo, per core:
$277.77
Monthly cost across 3 years based on $14k per 2-core pack in the first year, plus $3k in SA for two following years. $14k + $3k + $3k = $20k / 2 cores / 36 = $277 per core, per month.
30
That cost isn't the client's cost, and it's probably not yours, either. Adjust that number based on your licensing agreement.
31
32
Notes
33
1
We didn't test all instance types, just the ones that were vaguely relevant for this project based on requirements. Also threw in a couple of AWS instances for comparison.
34
VM prices are from the calculator:
https://azure.microsoft.com/en-us/pricing/calculator/preview/
35
2
For each tested instance type, we tested both local ephemeral storage, and premium managed disks. The quantity of disks was picked using the Azure portal recommendations for max throughput on that instance type.
36
3
CrystalDiskMark isn't the best or most thorough test by any means - we just did it as a quick sanity check to see, "Is there anything horribly bad before we start testing?" The L4 instances were great examples.
37
4
Backing up to NUL helps measure how fast you can read data from SQL Server's data files.
38
5
Some shops back up locally first, then sync the files somewhere else for safety. I'm not a fan of that, but I did want to see if local backups would be way faster than UNC paths.
39
6
We set up a file server with high network speed and fast local ephemeral storage. This server was not a bottleneck during the tests.
40
7
Howee, this one is complicated. See:
https://www.brentozar.com/archive/2017/05/building-faux-paas-part-2-choosing-testing-cloud-vendor/
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
Loading...
 
 
 
Test Results (Public)