WP_Cloud Analytics - Mapping Requirements to Technology

W H I T E P A P E R

www.persistent.com

5.2 Decision points

We illustrate hereunder the thought process that the PSL analytics team in charge of the customer’s product

went through, following the decision steps introduced previously in

5.2.1 Type of cloud

Early on, the customer had decided they wanted to have a public cloud deployment. When the PSL team arrived,

they had already decided to go with Azure VM infrastructure, given their technology stack which is based around

Microsoft SQL Server.

With respect to the degree of influence of dimensions on the decision, the following can be said:

1. The dimensions that influenced most this decision was the pricing model (“pay as you go”) and the low

cost associated with Microsoft’s public cloud.

2. Performance requirements seemed attainable (on the analytic workload defined by the customer, most

reports should return in 1 second, some in more).

3. The same was thought of security and compliance: SQL Server provides workable options to encrypt

personal identifiable information, and internal tables can be used to store metadata to control the data

access per business rules and policies relevant to the customer’s product tenants (schools).

5.2.2 Cloud database management level

The initial decision was to go with SQL Server managed directly on Azure VMs. The overall volume to manage is

too large to a single SQL Server instance (over 200 TB). The solution adopted was to partition the data space in

“deployment units” (DUs) managed through IaaS with an SMP database on each.

Each school tenant stores about 1 GB of OLTP data per year, and 6 years of history are required. This gives 6

GB per tenant. The working scale factor for data warehousing data expansion has been found out to be 2, which

gives 12 GB per tenant. The customer expected number of tenants is about 22,000, so the total data warehouse

size comes out to be 256 TB. SQL Server SMP architecture allows it to scale to the small tens of terabyte

For

this reason, the current design considers DUs of 1000 tenants each, storing 12 TB of data per DU. Each DU has

been sized at 4 servers, each with 16 cores and 250 GB of RAM. There would be 22 DUs over time.

Of course, the customer will not be able to run a query needing data from more than one DU without extra-

merging work; this need is nevertheless planned for future releases.

With SQL Server on Azure VMs, customers have the full administrative rights over a dedicated SQL Server

instance and a cloud-based VM. This implies that the customer must have IT resources available

• To use the services managing the VMs and other infrastructure (storage and networks), and

• To manage SQL Server: the traditional management of SQL Server done on-premises needs to be done

now on the cloud.

A TPC-H world record was just broken by MSFT SQL Server on an SMP machine on the 10 TB category.