Many customers want to move their SQL Server databases into the Azure cloud. Whilst not discussed here, moving to a PaaS (Platform As A Service) database platform provides many benefits over you having to manage your on premise or IaaS (Infrastructure As A Service) SQL Server databases.
Whilst many want to, the actual process isn’t traditionally easy. There are many steps to go through, including identifying what you need to migrate, assessing compatibility with newer SQL versions, making any necessary changes, and that’s even before you attempt the migration itself! I’ve had several DBA roles in the past and the challenge is all too real.
Thankfully, there’s a handful of tools in the Azure space that can make this whole process a lot easier. In this blog I’ll touch on each of these – what to use and when, and will also provide a high level process that links them all together.
A lot of the information I’m going to talk about can be found in the excellent Azure Data Migration Guide, and is recommended reading for any migration to Azure. It also covers other sources such as MySQL and PostgreSQL as well as good old SQL Server.
Discover
In this phase you are looking to identify and inventorise your SQL Server estate so that you know what you actually have running. The MAP toolkit is a great free tool to get started, and will give a breakdown of the SQL Server Editions and the features in use.
Assess
Now we’ve documented our estate and know what we need to migrate. We need to assess these source databases and see if there are any breaking or deprecated features that would need changing before attempting to move to the latest version of SQL.
In our kit bag this time we have the Database Migration Assistant (DMA). Newly updated (October 2018) to include Azure SQL Managed Instance as a target, you simply point DMA at your source database, choose “Assessment” and it will spit out any issues with SQL Server feature parity or compatibility that you need to be aware of.
Migrate
Okay, so we’ve ran our assessment using the DMA. We fixed or amended any potential blockers to moving our source database into our target SQL database. We’re now ready to move onto the final part of the migration, the actual move itself.
There are several methods of doing this (depending on your source and target), from the good old back up, copy and restore through to more advanced methods. You can also use the DMA itself to do a migration, however the DMA is more recommended for singleton migrations or for testing before the real thing.
For production scale migrations we recommend the use of the Azure Database Migration Service (DMS), which is a fully managed database migration service and allows us to not only migrate databases from SQL Server in bulk, but also let’s us do these migrations online, meaning that, following the restore to the target database, the DMS establishes a continuous sync between source and target. This feature means that you don’t have to do the cut off straight away if you’re not ready, and make that move further down the line whilst minimizing the operational impact.
Summary
In this article I’ve barely skimmed the surface on some of the processes and tools required to migrate your database into Azure SQL. However, with the Database Migration Guide, coupled with the Database Migration Assistant and the Database Migration Service, you’ve now got an excellent kit bag to make that challenging migration just a little bit easier (and less stressful!).
Useful Reading/Links
Choosing Your Database Migration Path (Whitepaper)
SQL Of The North 