The course is part of this learning path
Once you've decided to take advantage of cloud technology, resources, and scale, and have set up an Azure infrastructure, there is the small matter of transferring your existing data to the cloud. A migration is successful when the target production system is up and running correctly with the minimum impact on the business' operation. Achieving this requires knowing what data issues you will have in the new environment and minimizing system downtime while the data is transferred. This course looks at strategies and tools to achieve an optimal migration.
Learning Objectives
- Understand the underlying concepts of migrating relational databases to Azure
- Explore the factors in determining the optimal migration scenario as well as those affecting the physical movement of data from on-premises to Azure
- Learn the different tools and services to aid in discovery, assessment, and migration
- Learn how to migrate an on-premises SQL Server DB to Azure SQL Managed Instance using the Data Migration Service
- Use Data Migration Assistant to migrate on-Premises SQL Server databases to Azure SQL DB
- Migrate MySQL to Azure MySQL using MySQL Workbench
- Migrate a MySQL database to Azure SQL Server using SQL Server Migration Assistant
Intended Audience
This course is ideal for anyone looking to migrate their existing on-premise database environment to an Azure SQL database solution.
Prerequisites
To get the most out of this course, you should be familiar with general database and cloud concepts.
As we've seen, Azure is not all about SQL Server and now natively supports several well-known database engines outside the virtual machine environment. In this demonstration, I'll migrate a MySQL database from on-premises to an Azure MySQL flexible server instance using MySQL Workbench. Workbench is the MySql equivalent of SQL Server management studio and has a data migration wizard function that I'll use for the migration. It's pretty standard stuff. We create the TCP connections to our source and target servers. The source server will be localhost on port 3306, and I'll use the fully qualified name for the Azure target server, which is also on MySQL's default port of 3306. You would be using either a site-to-site VPN or an ExpressRoute connection and not transferring data over the public internet in a production environment as I am here. The target server username is the one I set up when I created the MySql server instance, so that will be azureuser. The next step is to validate the connections to source and target and then retrieve the schema data from the source server. I'm going to migrate the employees database, which has six tables. I'm not going to migrate the views through the wizard as current_dept_emp is dependent on dept_empt_latest_date, and the object selection won't allow me to change the creation order. Views are easy enough to create after the migration, so not a big deal. Like all good wizards, it's just a case of hitting next while MySql workbench generates the DDL statements to run on the target server. There are no problems and zero warnings, which is good but not unexpected as we're doing a greenfields migration, and I've left out the problem-causing views. Next, to apply the schema SQL to the target server, and all tables have been successfully created. I'll select an online copy of the data to Azure and click next to kick off the data transfer. I have sped this up significantly, and it still takes quite a while, even with a 1Gbps internet connection. This highlights the importance of quality connectivity and that there really is no substitute. Having said that, a mysqldump into a compressed file copied to Azure storage would be quicker and more reliable for large databases. Now that's done, I'll do a quick table count check of the source and target to make sure they match. That all looks in order, so next, I'll create the two views to complete the database migration.
Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.