19 / 54
W H I T E P A P E R
www.persistent.com
© 2017 Persistent Systems Ltd. All rights reserved.
On the cost side, besides the IT resources costs for administering the database, SQL Server on Azure VMs is
sold with an included license that customers pay per-minute and includes Windows Server and VM disk storage
costs. Existing SQL Server licenses can also be brought in; in this case, customers will be charged for Windows
Server and storage costs only. Pricing per month for the 22 DUs in this case comes out to be in the order of
$250.000 to $350.000 per month (using VM configurations for the sizes presented above); this between 5 to 7
times more expensive than Azure SQL DW (see below), and does not include the cost of managing SQL Server.
Appendix 2provides more detail on the management of the infrastructure layer and the tools available to IT
administrators to manage SQL server on virtual machines in Azure.
Study on data warehouse as a service
The customer was open to go beyond the raw IaaS management level and wanted PSL to conduct a study to
determine if Azure SQL DW, Microsoft’s MPP cloud database service, made sense for their product.
The notion of deployment units, developed assuming a SQL Server implementation, would need to be revisited:
indeed, a single Azure SQL DW could store the data of the entire set of school tenants, as it allows to store 240
TB compressed on disk; this compression allows the database to grow to approximately 1 PB when all tables are
clustered with
columnstore
type, which is the default table type.
On the positive side, for the reasons we have explained in
section 4, definite gains are expected on the
performance and scalability, due to the MPP architecture and the elastic nature of the Azure SQL DW product.
Cost should also be favorable, we talk about this aspect below.
On the negative side:
a. As of last year, there were unsupported features such as PK/FK constraints, more general constraints,
unique indexes, UDFs, identity columns (required for DW surrogate key generation), triggers, indexed
views, as well as certain T-SQL constructs.
b. Furthermore, certain limits such as 1024 active connections and maximum 32 concurrent users executing
queries may be limiting, as it is expected that 100 concurrent users would be executing simple reports.
However, Azure SQL DW can scale out and deliver results for small reports in fractions of a second, so
this limit does not necessarily mean that the requested throughput can’t be attained (this would need to
be verified experimentally through a PoC, which was not part of the initial study).
Finally, the cost per 100 DW units (a measure of underlying resources like CPU, memory, and IOPS) at 1.21
USD/hour comes to be about $52,000 per month for 6000 DW units, the maximum in Azure SQL DW, which
seems adapted to the size and load of this custome
r 16. In comparison, this is about half of Amazon’s Redshift
on-demand pricing which, at a price of $5000 USD/TB/Year, comes to be $106,600 USD/month for the total 256
T
B 17. As it is a managed service, the price includes the devOps cost of managing the database. Thus, when
compared to the cost levels we saw above for SQL Server on Azure VMs, multi-tenant is indeed less expensive
than single-tenant IaaS deployments.
The first version in production will be running on SQL Server on Azure VM (an IaaS option), the main roadblock
being the unsupported features needed to migrate the current application to Azure SQL DW. In later releases, as
Azure SQL DW matures and features needed by the OLTP and the reporting/analytics application are supported,
this choice might be reconsidered.
16
Given the expected concurrent workload, 6000 DWUs would give 240 concurrency slots. Each query consumes one or more concurrency slots, dependent on the resource
class of the query. Simple queries consume one concurrency slot. Queries running in a higher resource class consume more concurrency slots. This number of slots could
probably accommodate the customer’s 10 medium size queries and 5 complex queries, in addition to 100 simple queries.
17
However, AWS allows you to pay upfront for 1 or even for 3 years, for steady-state production workloads, which offers anywhere from 25% up to 75% discounts over on-
demand pricing depending the machine sizes and upfront payments.
19