Migrating from AWS RDS SQL Server to Azure SQL Database Using the Azure SQL Database DTU Calculator

Last week a colleague of mine in the UK asked me for some help migrating from AWS’ RDS SQL Server to Azure SQL Database.  The details of why you’d want to do this are out of the scope of this post but needless to say, Azure SQL Database is better.  This post assumes you’re familiar with the DTU Calculator and how it works.  If not, click here to learn more.

AWS RDS provides managed services for deploying SQL Server as well as several other major DBMS platforms.  The service is single-tenant in that the RDS server is just a dedicated VM that AWS has wrapped to make it look like a platform as a service (PaaS) offering.  Unfortunately, AWS doesn’t provide access to the underlying VM so capturing the performance metrics needed by the DTU Calculator is a little bit tricky and the PowerShell script I developed to help doesn’t work.  My first thought was to capture performance metrics using SQL Server’s Dynamic Management Views (DMOs) and provide a command-line interface wrapper to generate a CSV file the DTU Calculator understands.  This is where I ran into my first problem.  Does anyone understand how to use those things?  Seriously?!?  I spent hours searching the internets trying to find something, anything, that would help.  Finally, I came across the following book by Louis Davidson and Tim Ford, Performance Tuning with SQL Server Dynamic Management Views.  After reading through a few chapters related to the performance counters I needed, I determined I would need to write the following SQL statements:

SELECT
       counter_name,
       A.cntr_value,
       B.cntr_value,
       Ratio = CASE WHEN B.cntr_value = 0 THEN 0
       ELSE A.cntr_value / B.cntr_value END
FROM sys.dm_os_performance_counters A,
       (SELECT cntr_value
       FROM sys.dm_os_performance_counters
       WHERE OBJECT_NAME LIKE 'SQLServer:Resource Pool Stats%'
             AND COUNTER_NAME LIKE 'CPU usage % base%') B
WHERE COUNTER_NAME LIKE 'CPU Usage %'
AND OBJECT_NAME LIKE 'SQLServer:Resource Pool Stats%'
AND COUNTER_NAME NOT LIKE 'CPU usage % base%'
AND COUNTER_NAME NOT LIKE 'CPU usage target%'

SELECT *, 'PERF_COUNTER_LARGE_RAWCOUNT' FROM sys.dm_os_performance_counters
WHERE COUNTER_NAME LIKE 'Disk Read Bytes/sec%'

SELECT *, 'PERF_COUNTER_LARGE_RAWCOUNT' FROM sys.dm_os_performance_counters
WHERE COUNTER_NAME LIKE 'Disk Write Bytes/sec%'

SELECT *, 'PERF_COUNTER_BULK_COUNT' FROM sys.dm_os_performance_counters
WHERE COUNTER_NAME LIKE 'Log Bytes Flushed/sec%'
AND INSTANCE_NAME LIKE '<Database>'

Reading the queries, it’s easy to see what’s going on.  First, I capture the CPU as a percentage.  Second, I get reads/writes as raw values.  Finally, I get the log which happens to be a bulk count so I’ll need to store and compare its value on each interval to find the real value.  Running the queries against a local database gave me the results I wanted but I was skeptical AWS RDS would do the same so  I fired up an RDS SQL Server instance with a database and ran the queries in SSMS.  Just as I suspected, no luck.  It seems AWS RDS will give me CPU information but I’m S.O.L. for the  other counters.  The queries run fine but the last three don’t return any values.

I spent several more hours investigating alternatives and settled on AWS CloudWatch.  AWS does provide RDS performance metrics via CloudWatch but they are aggregation metrics.  The smallest increment is 60 seconds so I’ll need to take the average of each counter for each interval.  CloudWatch provides multiple counters for RDS but the following seem to be related to what the DTU Calculator needs:  CPUUtilization, ReadThroughput, WriteThroughput.  Unfortunately, there doesn’t appear to be a way to capture the equivalent of Log Bytes Flushed/sec so I’ll need to ignore that counter and hope the others get me close enough in my calculation.

I settled on using the GetMetricStatistics action.  I won’t go into detail regarding that action and I’m definitely not an AWS CloudWatch expert so you’ll want to do some research of your own to validate my findings.  I’ve done some testing using the CloudWatch CLI and I believe something similar to the following commands will work:

mon-get-stats CPUUtilization --start-time 2015-11-15T22:00:00.000Z --end-time 2015-11-15T23:00:00.000Z --period 60 --statistics "Average" --namespace "AWS/RDS" --dimensions "DBInstanceIdentifier=[dbinstanceidentifier]"

mon-get-stats ReadThroughput --start-time 2015-11-15T22:00:00.000Z --end-time 2015-11-15T23:00:00.000Z --period 60 --statistics "Average" --namespace "AWS/RDS" --dimensions "DBInstanceIdentifier=[dbinstanceidentifier]"

mon-get-stats WriteThroughput --start-time 2015-11-15T22:00:00.000Z --end-time 2015-11-15T23:00:00.000Z --period 60 --statistics "Average" --namespace "AWS/RDS" --dimensions "DBInstanceIdentifier=[dbinstanceidentifier]"

Take the output from those three commands and copy the results into a CSV file.  Use the following headers:  ‘% Processor Time’, ‘Disk Reads/sec’, ‘Disk Writes/sec’, ‘Log Bytes Flushed/sec.’  Since you don’t have metrics for the ‘Log Bytes Flushed/sec’ column, fill that column with zeros.  Once you’ve built the CSV file, you’re all set to upload the results into the DTU Calculator.  If your RDS SQL Server is running more than a single database, select the ‘Elastic Databases’ option to calculate the equivalent pool size.

As I mentioned above, calculating the Azure SQL Database equivalent for an AWS RDS SQL Server is a bit tricky and by no means perfect.  However, my hope is this post gives you some indication of how to gather performance metrics from RDS using AWS CloudWatch and hopefully provides a baseline calculation for migrating to Azure SQL Database.  As always, I’d love to hear your feedback.

Advertisements

Analyzing the Azure SQL Database DTU Calculator Results

The following is the third in a series of posts about the Azure SQL Database DTU Calculator.  The first post in this series described the need for the calculator and the process of creating it.  The second post described using the calculator.  This post will focus on analyzing the results.

Single Azure SQL Database

Assuming you’ve captured your SQL Server’s performance and uploaded it in the calculator, you will now see the results page.  The example below is for a single database so your results may vary slightly if you’re using Elastic Database, but in general, the results page is divided into three basic sections:  ‘Service Tier and Performance Level’, ‘DTUs Over Time’, and ‘View More Details’.  Note that the ‘View More Details’ section is collapsed by default so you’ll need to expand it in order to analyze CPU, IOPS, and Log recommendations individually.

Service Tier/Performance Level

The ‘Service Tier/Performance Level’ section provides an overall recommendation for migrating to Azure SQL Database.  This recommendation is calculated by taking the maximum number of DTUs for either CPU, IOPS, or Log at each interval and using that maximum as the basis for the recommendation.  Since the calculator recommends the Service Tier/Performance Level which maximizes performance while minimizing costs, it’s important to note the recommendation may not cover 100% of your workload.  There may be some portion of your workload that falls into a higher Service Tier and Performance Level so you’ll want to validate your database’s performance post migration.

Note:  You can gain additional insights by hovering your mouse over each section of the chart to see what percentage of your workload is covered by a specific Service Tier/Performance Level as well as how much that Service Tier/Performance Level covers the entire workload.

Service Tier/Performance Level

DTUs Over Time

The ‘DTUs Over Time’ section is similar to the ‘Service Tier/Performance Level’ recommendation in that it uses the maximum DTU for CPU, IOPS, and Log.  The difference is the ‘DTUs Over Time’ chart displays the DTU value at each interval.  This type of visualization is useful since it shows how DTUs relate to performance.  In general, you would hope your performance is somewhat consistent but having this chart allows you to identify performance spikes that may cause problems with your Azure SQL Database deployment and do some additional investigation to identify and resolve those performance issues before migrating to Azure SQL Database.

DTUs Over Time

View More Details

The ‘View More Details’ section is broken down into four subsections.  The first three sections show individual recommendation for CPU, IOPS, and Log.  Having individual recommendation allows you to quickly identify how your workload is bound.  For example, the following shows recommendations for CPU and IOPS.  Notice the recommendation for CPU is Standard – S1 and the recommendation for IOPS is Standard – S0.  This means your workload is CPU bound as your workload requires more CPU than disk.

Service Tier/Performance Level for CPU 

Service Tier/Performance Level for IOPS

The last chart shows how much of your workload’s CPU, IOPS, and Log is covered by a Service Tier/Performance Level.  By utilizing this chart you can visualize how your workload changes across Service Tiers and Performance Levels.  For example, the following chart shows that 30% of CPU, 78% of IOPS, and 100% of Log are immediately covered by the Basic Service Tier and it isn’t until Standard – S1 that all three metrics have over 90% coverage.

Service Tier/Performance Level for CPU, IOPS, and Log

Elastic Databases

The Elastic Databases recommendation is almost identical to the recommendation for a single database with two major differences.  The first difference is that elastic database pools don’t have a concept of Performance Levels so the recommendation only provides a Service Tier (Basic, Standard, or Premium).  The second difference is the Elastic Databases recommendation shows the total eDTUs required for the pool.  The total eDTUs required for the pool is important because of the way Elastic Databases are billed.  You pay for the total eDTUs required for all databases in the pool.  See the SQL Database Pricing page for more information.