Without AHB, the price goes up to USD$736.38/month and the SQL License cost is clearly shown as USD$291.90/month (USD$736.38 – USD$444.48) or US$3,502.80/year.
This means that without AHB we would pay an extra USD$3,502.80/year!
Ok lets take a step back.
To make use of AHB you required the Box product with Software Assurance (SA).
Taking a look at https://www.microsoft.com/en-us/sql-server/sql-server-2019-pricing we can see that a Standard Edition (equivalent of the Azure General Purpose Tier) 2 core pack costs USD$3,586 and therefore 2 of these, to get 4 core licenses, would cost USD$7,172.
[1] Pricing represents open no level (NL) estimated retail price.
In addition we require 2 of the SA licenses which would be USD$2,836/year (USD$1,418/year x 2).
This results in an outlay of USD$10,008 (USD$7,172 (Box) + USD$2,836 (SA)) for the 1st year to allow use of the AHB.
So based on the above, when do you break even if using AHB as opposed to not using AHB?
Just over 10 years, which is the standard life cycle of a Boxed product!
4 vCores
AHB
No AHB
Savings
Running Total
Year 1
10,008
3,502.80
6,505.20
6,505.20
Year 2
2,836
3,502.80
(666.80)
5,838.40
Year 3
2,836
3,502.80
(666.80)
5,171.60
Year 4
2,836
3,502.80
(666.80)
4,504.80
Year 5
2,836
3,502.80
(666.80)
3,838.00
Year 6
2,836
3,502.80
(666.80)
3,171.20
Year 7
2,836
3,502.80
(666.80)
2,504.40
Year 8
2,836
3,502.80
(666.80)
1,837.60
Year 9
2,836
3,502.80
(666.80)
1,170.80
Year 10
2,836
3,502.80
(666.80)
504.00
Year 11
2,836
3,502.80
(666.80)
(162.80)
Total
38,368
38,530.80
(162.80)
In summary and based on the above; if you have SA then yes use AHB and keeping renewing the SA for as long as you can. If you are starting with the need for new licenses, then using AHB doesn’t make sense.
As I’ve been working quite a bit on Azure lately, I thought I’d list a few of my findings.
Picking a Virtual Machine
At the time of this blog, there were 172 VM sizes to choose from.
172 VM sizes
The key considerations for a SQL Server VM relate to:
CPUs (Potentially impacts license costs)
Memory (The more memory the better as much faster than disk)
Disk capacity (Sufficient disks space to store your databases/backups)
Disk speed (Impacts data read/write access performance)
So with this in mind we can narrow down suitable VMs.
If the goal is to get a SQL Server 2017 Standard Edition VM with 4 vCPUs (minimum core license requirement) and 128GB RAM (Edition limit) we end up with a E16-4s_v3.
SQL Server 2017 Standard Edition VM option with 128GB Ram
The E16-4s_v3 is a constrained core size VM (see below) which has 4 vCPUs, 128GB memory and up to 25,600 IOPS.
Constrained core size VMs allow for a reduced vCPU count for licensing while keeping the RAM and MAX IOPS unchanged for the same price.
Constrained core size VMs for E16s_v3
In Microsoft’s documentation they refer to use of DS2_v2 or higher for Standard Edition and DS3_v2 or higher for Enterprise Edition.
DS VMs
If the goal is to obtain a VM with maximum disk IOPS (currently 80,000) while keeping SQL license costs down you’ll probably be best to go with a E64-16s_v3 which has 16 vCPUs and 432GB RAM.
VMs which allow up to 80,000 IOPS
VM Disks
Once you have decided on your VM, the next most important step is deciding on the disks to add to the VM.
Premium SSD disks available
The table above shows the max throughput available for the disk, but depending on your VM you may not achieve that throughput due to Microsoft setting IOPS limits on the various VMs.
I’ll demonstrate these limits with the widely used CrystalDiskMark tool to place load on various disk configurations.
Below shows CrystalDiskMark run against a very old $100 Apacer AS681 SSD 2.5 7mm SATAIII, 240GB drive which is no longer sold.
Example
All tests were run 3 times using a 1GB file to save time.
Seq Q32T1: Sequential, Queues = 32, Threads = 1
4KiB Q8T8: Random 4KiB, Queues = 8, Threads = 8
4KiB Q32T1: Random 4KiB, Queues = 32, Threads = 1
4KiB Q1T1: Random 4KiB, Queues = 1, Threads = 1
All tests made use of the recommended Storage Spaces with Simple Resiliency and the number Columns (think of it as a stripe) equaling the number of disks. The disks were formatted using best practice NTFS 64K allocation unit size and GPT partition to allow exceeding 2TB. Simple Resiliency is all that is required due to the underlying Azure infrastructure ref: “Locally redundant storage (LRS) replicates your data three times within a single data center. LRS provides at least 99.999999999% (11 nines) durability of objects over a given year.”
E16-4s_v3: Has 4 vCPUs, 128GB RAM and Max IOPS 25,600
E32-8s_v3: Has 8 vCPUs, 256GB RAM and Max IOPS 51,200
E64-16s_v3: Has 16 vCPUs, 432GB RAM and Max IOPS 80,000
Host Caching set to None
E16-4s_v3
E32-8s_v3
E64-16s_v3
Read / Write (MB/s)
Read / Write (MB/s)
Read / Write (MB/s)
1 x P30: Seq Q32T1
204 / 205
204 / 206
204 / 206
1 x P30: 4KiB Q8T8
21 / 20
20 / 20
20 / 21
1 x P30: 4KiB Q32T1
21 / 21
20 / 20
21 /21
1 x P30: 4KiB Q1T1
1 / 1
1 / 1
1 /1
Cost per month NZ$ 204.37 (Size 1TB)
2 x P30: Seq Q32T1
395 / 393
411 / 409
411 / 409
2 x P30: 4KiB Q8T8
41 / 41
41 / 41
41 / 41
2 x P30: 4KiB Q32T1
40 / 40
38 / 39
39 / 40
2 x P30: 4KiB Q1T1
1 / 1
1 / 1
1 / 1
Cost per month NZ$ 408.74 (Size 2TB)
3 x P30: Seq Q32T1
392 / 393
613 / 612
612 / 612
3 x P30: 4KiB Q8T8
61 / 62
61 / 62
61 / 62
3 x P30: 4KiB Q32T1
41 / 47
40 / 43
41 / 45
3 x P30: 4KiB Q1T1
1 / 1
1 / 1
1 / 1
Cost per month NZ$ 613.11 (Size 3TB)
4 x P30: Seq Q32T1
394 / 394
783 / 780
815 / 811
4 x P30: 4KiB Q8T8
80 / 81
80 / 81
80 / 81
4 x P30: 4KiB Q32T1
42 / 47
42 / 45
42 / 45
4 x P30: 4KiB Q1T1
1 / 1
1 / 1
1 / 1
Cost per month NZ$ 817.48 (Size 4TB)
5 x P30: Seq Q32T1
392 / 394
783 / 778
1006 / 896
5 x P30: 4KiB Q8T8
84 / 97
83 / 88
87 / 93
5 x P30: 4KiB Q32T1
42 / 47
41 / 45
42 / 45
5 x P30: 4KiB Q1T1
1 / 1
1 / 1
1 / 1
Cost per month NZ$ 1,021.85 (Size 5TB)
1 x P40: Seq Q32T1
255 / 257
257 / 256
255 / 257
1 x P40: 4KiB Q8T8
31 / 31
31 / 31
31 / 31
1 x P40: 4KiB Q32T1
31 / 31
31 / 31
31 / 31
1 x P40: 4KiB Q1T1
1 / 1
1 / 1
1 / 1
Cost per month NZ$ 391.67 (Size 2TB)
1 x P60: Seq Q32T1
392 / 394
513 / 511
510 / 511
1 x P60: 4KiB Q8T8
66 / 67
66 / 67
66 / 67
1 x P60: 4KiB Q32T1
51 / 59
46 / 51
46 / 54
1 x P60: 4KiB Q1T1
1 / 1
1 / 1
1 / 1
Cost per month NZ$ 1,430.42 (Size 8TB)
1 x P70: Seq Q32T1
393 / 394
765 / 764
766 / 764
1 x P70: 4KiB Q8T8
75 / 75
75 / 75
75 / 75
1 x P70: 4KiB Q32T1
51 / 58
46 / 52
45 / 59
1 x P70: 4KiB Q1T1
1 / 1
1 / 1
1 / 1
Cost per month NZ$ 2,724.62 (Size 16TB)
As shown above, there is clearly VM throttling in place and you could possibly be throwing money away by trying to use faster and/or more disks to get better IO performance.
Host Caching set to Read-only
E16-4s_v3
E32-8s_v3
E64-16s_v3
Read / Write (MB/s)
Read / Write (MB/s)
Read / Write (MB/s)
1 x P30: Seq Q32T1
269 / 205
538 / 205
1073 / 206
1 x P30: 4KiB Q8T8
133 / 21
268 / 21
534 / 20
1 x P30: 4KiB Q32T1
134 / 21
269 / 21
470 / 21
1 x P30: 4KiB Q1T1
19 / 1
54 / 1
58 / 1
Cost per month NZ$ 204.37 (Size 1TB)
2 x P30: Seq Q32T1
269 / 268
536 / 408
1071 / 408
2 x P30: 4KiB Q8T8
134 / 41
269 / 41
535 / 41
2 x P30: 4KiB Q32T1
134 / 38
269 / 39
489 / 40
2 x P30: 4KiB Q1T1
20 / 1
54 / 1
61 / 1
Cost per month NZ$ 408.74 (Size 2TB)
3 x P30: Seq Q32T1
269 / 268
539 / 535
1075 / 611
3 x P30: 4KiB Q8T8
134 / 61
267 / 61
535 / 62
3 x P30: 4KiB Q32T1
134 / 35
268 / 42
463 / 44
3 x P30: 4KiB Q1T1
19 / 1
54 / 1
58 / 1
Cost per month NZ$ 613.11 (Size 3TB)
4 x P30: Seq Q32T1
270 / 269
540 / 535
1073 / 812
4 x P30: 4KiB Q8T8
134 / 72
267 / 81
536 / 81
4 x P30: 4KiB Q32T1
134 / 37
268 / 44
481 / 44
4 x P30: 4KiB Q1T1
20 / 1
52 / 1
60 / 1
Cost per month NZ$ 817.48 (Size 4TB)
5 x P30: Seq Q32T1
268 / 270
538 / 535
1074 / 877
5 x P30: 4KiB Q8T8
134 / 66
269 / 89
535 / 90
5 x P30: 4KiB Q32T1
134 / 36
268 / 45
477 / 46
5 x P30: 4KiB Q1T1
18 / 1
54 / 1
60 / 1
Cost per month NZ$ 1,021.85 (Size 5TB)
1 x P40: Seq Q32T1
269 / 256
540 / 256
1075 / 256
1 x P40: 4KiB Q8T8
134 / 31
270 / 31
535 / 31
1 x P40: 4KiB Q32T1
134 / 31
268 / 31
496 / 31
1 x P40: 4KiB Q1T1
19 / 1
35 / 1
61 / 1
Cost per month NZ$ 391.67 (Size 2TB)
1 x P60: Seq Q32T1
No read cache available
1 x P60: 4KiB Q8T8
No read cache available
1 x P60: 4KiB Q32T1
No read cache available
1 x P60: 4KiB Q1T1
No read cache available
Cost per month NZ$ 1,430.42 (Size 8TB)
1 x P70: Seq Q32T1
No read cache available
1 x P70: 4KiB Q8T8
No read cache available
1 x P70: 4KiB Q32T1
No read cache available
1 x P70: 4KiB Q1T1
No read cache available
Cost per month NZ$ 2,724.62 (Size 16TB)
There are a lot of ways to interpret the data above, but key findings included:
Host caching set to None, can offer faster performance than Read-only cache.
The Larger sized disks do not offer caching.
Good option for SQL Server when a low number of disks are allocated to the VM.
Host Caching set to Read/write
E16-4s_v3
E32-8s_v3
E64-16s_v3
Read / Write (MB/s)
Read / Write (MB/s)
Read / Write (MB/s)
1 x P30: Seq Q32T1
269 / 221
538 / 451
1080 / 792
1 x P30: 4KiB Q8T8
134 / 80
268 / 156
535 / 403
1 x P30: 4KiB Q32T1
134 / 87
269 / 117
470 / 239
1 x P30: 4KiB Q1T1
18 / 26
54 / 39
58 / 51
Cost per month NZ$ 204.37 (Size 1TB)
2 x P30: Seq Q32T1
269 / 270
538 / 440
1076 / 1077
2 x P30: 4KiB Q8T8
134 / 133
269 / 194
535 / 504
2 x P30: 4KiB Q32T1
133 / 134
269 / 127
489 / 388
2 x P30: 4KiB Q1T1
19 / 36
49 / 30
61 / 31
Cost per month NZ$ 408.74 (Size 2TB)
3 x P30: Seq Q32T1
269 / 270
539 / 535
1075 / 806
3 x P30: 4KiB Q8T8
134 / 129
268 / 266
535 / 524
3 x P30: 4KiB Q32T1
133 / 66
268 / 267
463 / 386
3 x P30: 4KiB Q1T1
19 / 24
49 / 45
58 / 41
Cost per month NZ$ 613.11 (Size 3TB)
4 x P30: Seq Q32T1
268 / 250
536 / 540
1070 / 1023
4 x P30: 4KiB Q8T8
134 / 68
267 / 202
536 / 530
4 x P30: 4KiB Q32T1
134 / 123
267 / 193
481 / 370
4 x P30: 4KiB Q1T1
19 / 27
49 / 44
60 / 40
Cost per month NZ$ 817.48 (Size 4TB)
5 x P30: Seq Q32T1
269 / 261
539 / 519
1070 / 1020
5 x P30: 4KiB Q8T8
134 / 133
268 / 267
536 / 531
5 x P30: 4KiB Q32T1
134 / 134
268 / 201
481 / 374
5 x P30: 4KiB Q1T1
19 / 39
49 / 38
60 / 41
Cost per month NZ$ 1,021.85 (Size 5TB)
1 x P40: Seq Q32T1
269 / 270
539 / 246
1079 / 993
1 x P40: 4KiB Q8T8
133 / 133
270 / 231
535 / 530
1 x P40: 4KiB Q32T1
133 / 71
268 / 55
484 / 409
1 x P40: 4KiB Q1T1
19 / 26
35 / 23
59 / 57
Cost per month NZ$ 391.67 (Size 2TB)
1 x P60: Seq Q32T1
No caching available
1 x P60: 4KiB Q8T8
No caching available
1 x P60: 4KiB Q32T1
No caching available
1 x P60: 4KiB Q1T1
No caching available
Cost per month NZ$ 1,430.42 (Size 8TB)
1 x P70: Seq Q32T1
No caching available
1 x P70: 4KiB Q8T8
No caching available
1 x P70: 4KiB Q32T1
No caching available
1 x P70: 4KiB Q1T1
No caching available
Cost per month NZ$ 2,724.62 (Size 16TB)
Microsoft does not recommend used of Read/write cache for SQL Server.
VM Temporary Drive
E16-4s_v3 (256GB Size)
E32-8s_v3 (512GB Size)
E64-16s_v3 (864GB Size)
Read / Write (MB/s)
Read / Write (MB/s)
Read / Write (MB/s)
Seq Q32T1
270 / 270
538 / 535
1067 / 1074
4KiB Q8T8
134 / 134
268 / 266
476 / 489
4KiB Q32T1
133 / 133
248 / 238
277 / 271
4KiB Q1T1
15 / 22
20 / 29
21 / 30
The temporary drive throughput appears to reflect throttled premium disks with read/write cache enabled.
To summarize:
Review your VM’s temporary (D) drive performance and size to gauge where your tempdb database should be placed.
Having several striped disks does not guarantee that you will get faster throughput, rather this is dependent on the VM used.
To get faster IO performance you need a lot of disk space (even with the new Ultra disks), which you may / may not require, and remember you get charged for the whole storage (not just what you use) plus IOs (except temporary drive).
If you down scale a VM, you should ensure that the VM supports the number of drives you currently have attached and it you are using the temporary (D) drive, that the size will be sufficient for your tempdb files.
When using Storage Spaces, if you need to add additional capacity, you’ll need to add the same number of disks e.g. a 3 disk virtual drive will require an additional 3 disks. Also when the new disks are added, the Column count does not increase, so although you have increased capacity, you don’t increase the disk performance.
Depending on your SQL Server requirements, the cloud is not always cost effective.
At some stage you’ll likely want to check your SQL Server errorlog files using xp_readerrorlog or sp_readerrorlog.
If you haven’t worked on the server before and/or don’t have access to view the file sizes before running the command the below query will help obtain this information.
Why would you be interested in knowing the file sizes before running the command? Well, if the instance hasn’t been restarted in a while and sp_cycle_errorlog hasn’t been run, the log files could be really large and cause potential memory and IO performance issues when you try read them.
DECLARE @enumerrorlogs TABLE ([Log Type] varchar(5), [Archive #] int, [Date] datetime, [Log File Size (Byte)] bigint);
--get sql log file sizes
INSERT INTO @enumerrorlogs ([Archive #], [Date], [Log File Size (Byte)])
EXEC master.sys.sp_enumerrorlogs 1;
UPDATE @enumerrorlogs
SET [Log Type] = 'SQL'
WHERE [Log Type] IS NULL;
--get sql agent log file sizes
INSERT INTO @enumerrorlogs ([Archive #], [Date], [Log File Size (Byte)])
EXEC master.sys.sp_enumerrorlogs 2;
UPDATE @enumerrorlogs
SET [Log Type] = 'Agent'
WHERE [Log Type] IS NULL;
SELECT
a.[Log Type] AS log_type
,a.[Archive #] AS archive
,a.[Date] AS date_last_updated
,CAST(a.[Log File Size (Byte)]/1024/1024.0 AS decimal(20,3)) AS log_size_mb
FROM @enumerrorlogs a
ORDER BY a.[Log Type] DESC, a.[Archive #];
Yes SQL Server 2014 Mainstream Support ended this month. I didn’t notice much of a fuss. Possibly everyone is scrambling with SQL Server 2008/R2 exiting Extended Support or they are sorted.
That currently leaves SQL Server 2016 and SQL Server 2017 as the only mainstream products. SQL Server 2019 release is imminent, but uptake will likely take some time.
If you are using clustered columnstore indexes on Azure SQL databases you run the risk of not being able to access your data when you change your pricing tier.
To demonstrate, I’ll create a basic table and add a Clustered columnstore index.
Simple table with CCI.
We’ll insert 2 rows and view the data.
2 rows added.
Let’s verify the current pricing tier.
We’re working with a S3.
Cool, we’ll change the database to a S2 to save some money.
Yay it’s now a S2.
The boss wants to view some data from that table we created earlier.
Bugger!
Now you know something to watch out for with CCIs.
Backing up your databases direct to Azure can be dangerous!
If you experience a router or network connectivity issue, then you run the risk of your transaction log backups consuming all disk space and halting your business operations.
If you start experiencing high network latency, then your backups could run into your business day and start impacting performance.
One solution is to perform a local backup and then load the backups into Azure. This way backups are not impacted by network outages/latency.
Let’s go through how this can be achieved.
The approach I’m using can also be used with SQL Server Express Edition.
Big caveat – these scripts were written in 1 day! Testing and error capture is minimal! Feel free to test and any feedback is welcome.
1. Prepare Your Azure Storage
Of course you can skip this step if you’ve got it setup already.
Create a Storage account.
You’ll be needing your Storage account name for later.
Enable Blob soft delete for a get out of jail free card.
Create and you’re nearly done.
Find your Storage account.
Go to Blobs.
Let’s create a Container (folder) to hold your SQL backups.
Give it a name and OK. You’ll be needing that name later.
Right time to get your access keys!
Save your Key you’ll be needing it.
2. Get Your Favourite Ola Hallengren Backup Scripts
It just creates your standard local backups and populates the sql_backups table.
Saves backup details.
Showing other info stored.
Great so now you are secure from network issues/latency.
6. Schedule the SQL_Copy_Backups_To_Blob.ps1 Script
SQL_Copy_Backups_To_Blob.ps1 only needs to be scheduled once and typically it should run every 15 minutes.
This script checks and uploads local backups listed in the sql_backups table to Azure and will also check and remove local & blob backups which have passed your specified retention time.
Local backups that haven’t been uploaded to Azure won’t be deleted, even if their retention period has passed. Cool!
SQL_Copy_Backups_To_Blob.ps1 only has 1 parameter and it is required:
instance – This is the instance which you’ll be backing-up e.g. ‘KN01\SQL2K19CTP3_0’
What you will need to do is enter the Storage account and Key information that you created / viewed earlier. This allows the backups to be uploaded and blobs to be removed.
NOTE: This is in 2 places in the file, yeah lazy scripting.
Set you Storage connection information. Yikes it’s not encrypted! O’well at least your server is locked down!