Tag: SQL

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

Posted on by Justin Henriksen

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.

0