SQL Server Database Cloud Migration Analysis

Considerations

One of the uncertainties related to moving applications, infrastructure, etc. from OnPrem to the cloud is trying to come up with an understanding of the estimated cost. For Azure SQL hosting one needs to become familiar with Database Transaction Units (DTUs), elastic Database Transaction Units (eDTUs), etc.  In this post we will go over the DTU approach and discuss EDTUs in the future. The official Microsoft definition of a DTU is as follows:

For a single Azure SQL database at a specific performance level within a service tier, Microsoft guarantees a certain level of resources for that database (independent of any other database in the Azure cloud) and providing a predictable level of performance. This amount of resources is calculated as a number of Database Transaction Units or DTUs, and is a blended measure of CPU, memory, I/O (data and transaction log I/O). The ratio amongst these resources was originally determined by an OLTP benchmark workload designed to be typical of real-world OLTP workloads. When your workload exceeds the amount of any of these resources, your throughput is throttled – resulting in slower performance and timeouts. The resources used by your workload do not impact the resources available to other SQL databases in the Azure cloud, and the resource used by other workloads do not impact the resources available to your SQL database.

Ok, so how many DTU’s would your existing or new Databases require to perform to expectations and how does a DTU effect the cost?  Not easy to decipher from the definition.  When setting up your database you must choose a service tier and performance tier. At the time of this post the following tiers are available:

Service tier Target workloads
 Basic  Best suited for a small database, supporting typically one single active operation at a given time. Examples include databases used for development or testing, or small-scale infrequently used applications.
 Standard  The go-to option for cloud applications with low to medium IO performance requirements, supporting multiple concurrent queries. Examples include workgroup or web applications.
 Premium  Designed for high transactional volume with high IO performance requirements, supporting many concurrent users. Examples are databases supporting mission critical applications.
 Premium RS  Designed for IO-intensive workloads that do not require the highest availability guarantees. Examples include testing high-performance workloads, or an analytical workload where the database is not the system of record.)

DTU Calculation

We recently tried to determine the cost for moving one of our client’s SQL Server Databases to the cloud. The first step was to try and get a rough estimate of required DTUs.  Luckily there is a DTU Calculator http://dtucalculator.azurewebsites.net/ which measures the following to calculate an estimate of required DTUs based on an hours worth of activity.

  • Processor – % Processor Time
  • Logical Disk – Disk Reads/sec
  • Logical Disk – Disk Writes/sec
  • Database – Log Bytes Flushed/sec

Once the tool ran for an hour, I uploaded the generated csv file and specified the number of cores (16 for this scenario) which returned a very detailed report recommending Service Tier: Premium with Performance Tier: Premium P11 which was based on the following three factors:

  1. Service Tier/Performance Level for CPU – Based solely on CPU utilization, we recommend you migrate your SQL Server workload to Premium – P11. This Service Tier/Performance Level should cover approximately 100.00 % of your CPU utilization.
  2. Service Tier/Performance Level for Iops – Based solely on Iops utilization, we recommend you migrate your SQL Server workload to Premium – P1. This Service Tier/Performance Level should cover approximately 98.03 % of your Iops utilization. NOTE: There is approximately 1.97 % of your workload that falls into a higher Service Tier/Performance Level. After migrating your database to Azure, you should evaluate your database’s performance using the guidance mentioned in the  information section above.
  3. Service Tier/Performance Level for Log – Based solely on Log utilization, we recommend you migrate your SQL Server workload to Basic. This Service Tier/Performance Level should cover approximately 100.00 % of your Log utilization.

As this server also runs a MongoDB instance as well as a few other cache services I believe we could probably get away with a P6 based on DTU Findings

Database Storage Requirements

The next consideration is size.  Our db is currently allocated 345 GB so technically we can stick with the P6 based on the performance levels defined below for the Premium Service Tier:

SQL Azure Performance Levels
SQL Azure Performance Levels

Analysis

The next step would be to approximate the cost of hosting the db OnPrem, items to consider would be

  • hardware, service contracts, electricity
  • licensing (For example, SQL Server 2016 would be $14,256 per core)
  • maintenance & payroll for IT management,
  • etc.

We would then compare the benefits of cloud vs. OnPrem in the context of cost to determine what is best for  solution for each particular client.

Need Help?

We are extremely excited to support cloud migrations and in particular the benefits of Azure.  Each week Azure has been introducing new exciting features making the platform exponentially more robust.

Send us an email at info@spartansoft.net if you are interested in migrating your apps, websites, databases etc. to the cloud and/or setting up an environment for a new project or start-up venture.  We offer quick turn around at very reasonable rates. Please feel free to contact us with any questions regarding cloud migrations.



Leave a Reply