When does StretchDB become cheaper than your local SAN?

This blogpost is an addendum to my previous blog “SQL 2016 – 5 (Real) reasons to upgrade, part 2” where I explain about StretchDB. We know that StretchDB is an awesome feature, but what will it cost and how does it compare with your local SAN storage?

StretchDB vs. SAN

StretchDB
For StretchDB, the answer looks simple because Microsoft provides us with clear pricing tables.

Let us focus on locally redundant storage, as this is the competing area of existing SAN solutions. For our calculations we’ll use the most expensive threshold, so let’s say we’ll only stretch 1TB into Azure, this being the most expensive ratio.

Ok, taking all these variables into account, storing 1TB in StretchDB will cost you €60.5 per month. Of course this is without taking the download costs into account. As this is archived data we’ll presume that this dataset will not be queried regularly. For arguments sake let’s say all the queries that run on this set will only download a volume of 200GB every month (20% following the 80/20 rule).The download ratio for this kind of volume is again the most expensive option costing you €0.0734/GB or €15 per month (source: https://azure.microsoft.com/en-us/pricing/details/data-transfers/ ). This means that storing a 1TB stretched table, sporadically querying it for 200GB a month will set you back for €75.5 every month.

SAN storage
Prices for SAN storage are a bit more difficult to calculate as this is an appliance and both up-front costs and service contracts will largely differ depending on the contract negotiated with your SAN vendor. For arguments sake I’ll work with the list prices as found on the internet, giving me an estimated price of ±€9,000 for an entry level SAN with 8TB of usable RAID 5 storage. One SAN appliance has an estimated life cycle of 5 years, so when including 5 years of support this will go to an approximate price of €16,500. This means that storing your (in this case partitioned) table in an archive filegroup will cost you: €16,500 / 5 years / 12 months / 8TB = ± €35 per month for one TB non-duplicated storage.

Comparing Apples with Apples

A simple conclusion could be that SAN storage is effectively cheaper, but this is only the case if you don’t duplicate this storage and don’t take any backups at all. This either means that you’re not having any High Availability features on this data, or that the database is not running in an AlwaysOn configuration. Let me go into detail a bit more.

Backup
As the dataset of our example is part of the database, we didn’t take into account any database backups. There is a major difference with StretchDB, however, as a backup of a Stretch-enabled database is a shallow backup that does not include data migrated to the remote server. On the SAN you would be able to take filegroup backups, but I rarely see this in the field unless the databases get really large. In this calculation we’re only archiving 1TB, so I don’t think the backup strategy would include filegroup backups or any differentials.

Let’s say the backup compression is used and the filegroup has a data backup ratio of 10%. In this situation it is rather common to store at least 3 full backups of your database. Transaction log backups don’t matter as this data will rarely change. This adds a total of 30% to the equation of the SAN storage. There is another possible scenario where the archive is at least three times bigger then the hot-data zone still on the server and this will give the same backup overhead.

Redundancy
Also the Azure Table storage has at least 3 copies of your data, most SAN configurations are duplicating their storage for redundancy. To be completely honest in my comparison I should thus triple the SAN’s price, but, in my experience, data is only duplicated so I’ll only duplicate the SAN’s cost. A reasonable question would be if you have to duplicate your backups as well. But in this equation I’ll presume that backups are taken across datacenters and are not duplicated. This actually doubles the initial price of the SAN storage.

Recalculating
These recalculations did not change anything on the StretchDB side, so the estimated price remains €75.5 per month. For the SAN Storage there are some important changes, the initial cost of €35 changes to: €35*(200% + 30%) = €79 per month.

Conclusion

As you can see in this example, storing your archived dataset via StretchDB, pricewise, lies very closely to SAN storage, giving us very interesting data to work with.

Scenario Criteria

• If you have a default maintenance plan, storing at least three not duplicated full backups of your database
• If you archived dataset is at least three times bigger then the remaining data set
• If the archives are only queried for 20% of their volume every month

Then StretchDB will be cheaper than on premise SAN storage, even without calculating the operational cost (energy/maintenance/work hours) of your SAN. We didn’t take into account additional cost for SAN growth (additional storage or boxes), as this is difficult to calculate. Also, we calculated the price of the SAN on a per TB level, presuming that it is 100% in use without having to extend it.

Management Summary
If you choose your stretch function wisely, and make sure that the archived zone is queried occasional and only when needed (for example by making minor adjustments to the DDL), StretchDB will rarely become more expensive than a comparable solution using on premise SAN storage. Especially because of the scenario criteria being rather favorable for the SAN solution.