Working With Azure AD in AWS and Moving from Azure SQL to RDS SQL Server

For the last week, I worked on a pretty intense migration of a fairly sizable Azure SQL instance that moved into AWS’ RDS service (running SQL Server). It was an intense project due to the timeline and size of the database. Of course, this involved access to both services, using both web consoles, CLI and native database interfaces. The client was controlling access to AWS and Azure using Azure AD, so I had to figure out federated access to the AWS API/CLI (since we built out their new environment using Terraform). A little bit of googling turned up this gem: https://github.com/dtjohnson/aws-azure-login

Follow the directions in the README, and you’re good to go. One thing to note, if you get an error trying to use timeouts longer than 1 hour, check your federated access settings in AWS if everything looks copacetic in Azure.

I’ve updated my Curated List of Favorite DevOps Tools to include this under the IAM section as well.

A few other interesting takeaways:

• Microsoft has made many of their CLI-based SQL administration tools available to macOS users.
• If you’re using DMS to replicate out of Azure to RDS, start with an empty database (just run a CREATE DATABASE whatevs; on the target first, so your target database is available to DMS).
• I could never get DMS’ drop tables to work (thankfully it’d just fail outright instead of getting 4 or 5 hours into a copy and then tanking).
• pymssql and some decent Python and database understanding will take you a long way.
• RedGate makes some amazing tooling.
• IF EXISTS doesn’t work in older versions of SQL Server/Azure SQL.
• Reported disk usage of a database in Azure doesn’t seem to include indexing. In my recent experience, this actually ended up doubling the reported disk usage of the database in RDS over what was reported in Azure (e.g. Azure console vs AWS console/CloudWatch). I need to dig into this one a bit deeper.
• (At least) double the amount of storage of the database is currently using for the storage on your DMS replication instance.
• Don’t forget that DMS doesn’t do indexes, primary keys, foreign keys, constraints, or anything like stored procs, UDFs, etc. (You don’t want these there when you’re copying the data anyways, but don’t forget you’ll need some mechanism to copy them over later).
• DMS seems to use a very rudimentary DDL when creating target tables. This required recreating tables on the target after they were migrated as pretty every table had an IDENTITY column. Make sure you account for this in your timelines.