Migrating from SQL Server to Amazon AWS Aurora

Is Microsoft’s licensing scheme getting you down? It’s 2020 and there are now plenty of data platforms that are good for running your enterprise data workloads. Amazon’s Aurora PaaS service runs either MySQL or PostgreSQL.

I’ve been supporting SQL Server for nearly 22 years and I’ve seen just about everything when it comes to bugs or performance problems and am quite comfortable with SQL Server as a data platform; so, why migrate to something new?

Amazon’s Aurora has quite a bit to offer and they are constantly improving the product. Since there’s no license costs its operating expenditures are much more reasonable. Let’s take a quick look to compare a 64 core Business Critical Azure Managed Instance with a 64 core instance of Aurora MySQL.

image

What about Aurora?

image

Two nodes of Aurora MySQL are less than half the cost of Azure SQL Server Managed Instances. I’ve added two nodes here because Aurora by default is durable but doesn’t have any H/A. Using two nodes in a DB Cluster should not only add H/A but also provide a readable secondary, which the Azure Business Critical tier has by default.

Azure Managed Instances only support 100 databases and only have 5.1 GB of RAM per vCPU. With 64 vCPUs in the above example there’s only 326.4 GB of RAM for the Azure instance compared to the 512 GB in the Aurora Instance. It’s also worth noting that Microsoft has added newer instance types such as the M series which is memory optimized and allocates 29 GB RAM per vCPU; but, I haven’t found a way to choose this in the Azure calculator. The M series is more than twice the cost of your typical Gen5 for Azure SQL DB and I’m assuming it’s a similar increase for Managed Instances too.

This post wasn’t intended to be about the “Why” of migrating; so, let’s talk about the “How”.

Migration at the high level takes two steps.

  1. Schema Conversion
  2. Data Migration

Schema Conversion is made simple with AWS SCT (Schema Conversion Tool).

Walking through a simple conversion. Note that the JDBC drivers for SQL Server are required.

image

You can’t use “.” for a local host, which is a little annoying but typing the servername is easy enough.

image

image

image

image

image

The dark blue items in the graph represent complex actions, such as converting triggers, since triggers aren’t a concept used in MySQL they aren’t a simple 1:1 conversion.

Migrating to Aurora from SQL Server can be simple with AWS SCT and a cost saving move that also modernizes your data platform. Next we’ll look at AWS DMS (Data Migration Service).

Thanks to the engineers at AWS, migrating to Aurora PostgreSQL is even easier. Recently Babelfish for Aurora PostgreSQL was announced, which is a product that allows SQL Server’s T-SQL code to run on PostgreSQL.