Migrating Azure SQL Database to Amazon Aurora
In a somewhat-migration from Azure SQL database to Amazon Aurora, take a look at what this developer had to do to make the migration successful.
Join the DZone community and get the full member experience.Join For Free
I recently got an opportunity to work on migrating Azure SQL Database to Amazon Aurora. Well, it was not technically a migration since our platform needed to support both Azure SQL and Amazon Aurora. The motivation behind this is perhaps a separate post in itself.
For those who have not worked on either of these database engines, here is a little introduction to both.
- Azure SQL Database is SQL-server compatible Database as Service on Microsoft’s cloud, Azure.
- Amazon Aurora is a MySQL and PostgreSQL-compatible relational database built on Amazon cloud, AWS. We chose to use MySQL flavor for Amazon Aurora.
There was an option to use Amazon RDS for SQL Server. However, there was a significant price difference between SQL Server and Aurora. We realized that the cost and effort for the rewrite would be far less than hosting on Amazon RDS for SQL Server in the longer run.
The migration was a great learning experience for someone like me who has not kept his foot outside Microsoft Technologies or Azure for most of part of his career.
Here are some of the changes we had to do to add support for Amazon Aurora.
You may also enjoy: Azure vs. AWS: Comparing Services for Your Cloud Database
The Changes Required
With Azure SQL, we used Docker image
mcr.microsoft.com/mssql/server for development since Azure SQL is compatible with SQL Server.
The SQL Server comes with SQL Server Management Studio (SSMS) from Microsoft. SSMS provided an easy way to view and manipulate tables/ queries.
For MySQL, we ended up using MySQL Workbench. One interesting issue we had while trying to write SQL queries on MySQL Workbench was that the queries MySQL Workbench by default supported version 8, and the query syntax was not compatible with
This was a little bit surprising for me since with SQL Server query syntax has mostly been backward compatible. To support the previous version of MySQL I had to update the MySQL default target version by going to
Edit -> Preferences -> Modeling -> MySQL and setting the version to
Database Setup – SSDT vs. UpDatabase
Our existing DB scripts were written using SSDT for SQL Server. With MySQL, we ended up using the
dbup-mysql Nuget package to deploy and upgrade the SQL Server database.
dbup also supports SQL Server. However, there was no motivation for us to move our existing SQL Server implementation to
dbup since SSDT works very well for us.
SQL Script Differences
Both SQL Server and MySQL are SQL. In reality, their syntax is more different than they are similar. Here are a few differences we came across while converting our queries to MySQL.
- We need to use backtick or
`in MySQL to escape keywords instead of
in SQL Server.
AUTO_INCREMENTkeyword to define
- MySQL does not support
DateTimeOffset. As a result, we need to save the date in
- MySQL and SQL Server differ in the way we define indexes. For example, MySQL does not support the filtered index.
- MySQL does not allow us to
DECLAREvariables outside of Stored Procedures
- MySQL does not have support for syntax like
WHEN MATCH, etc.
- One interesting and less obvious difference between the two is that the data type of
Int64while in SQL server it is
INT32. SQL Server
COUNT_BIGis the closest to MySQL
Note: This list is not exhaustive and it only describes a few of differences I came across during the conversion
We used Dapper ORM to talk SQL Server from code. This turned out a great choice for us as Dapper also has support for MySQL. This made the porting the queries quite easy. We were able to have a common interface that defined operations and then specific implementation for SQL Server and MySQL.
It was a bit of work but a fun learning exercise for us to add support for Amazon Aurora along with the existing Azure SQL Database.
Database Migration Challenges
From MySQL to Amazon Aurora: Different Methods of Data Migration
Published at DZone with permission of Ankit Vijay. See the original article here.
Opinions expressed by DZone contributors are their own.