image
SQL Server Migration Assistant Demo
Start course
Difficulty
Intermediate
Duration
49m
Students
639
Ratings
5/5
Description

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.

 

Transcript

I will use SQL Server Migration Assistant for MySql to migrate an on-premise MySql database to an SQL Server instance running on an Azure virtual machine. I've got MySql workbench open, and I've done a select count from the tables in the employees database that I'm going to migrate. There is a reasonable amount of data here, with two tables having several hundred thousand rows and one table having 2.8 million rows. Switching over to SQL Server management studio, I've connected to the server instance on the virtual machine using a point-to-site VPN connection I set up earlier. I'll create an empty employees database on the virtual machine going with the default settings. Coming back to my desktop, I'll open up SQL Server migration Assistant and create a new project. We can see in the new project dialogue there are several options in the migrate to drop-down. There is SQL Server starting at 2012 up to the current version plus Azure SQL database, and Azure SQL managed instance. If you are migrating from an on-premise SQL Server to a VM-hosted instance, be mindful of the target server version. You can't go backward, so if you are running server 2019 on-premises and create one of the canned SQL Server VM's from the portal running server 2017, you may run into problems. In the case of a source MySql database, this is not an issue.

Connecting to MySql on-premise server, I'm getting a warning about the ODBC driver version and how SQL Server migration Assistant has not been tested with the 8.0 Unicode driver I'm using. I have a new installation of MySql, and this is a recent download of SQL Server Migration Assistant, so I'm going to trust that nothing too much has changed and there is good backward compatibility. In the MySql metadata explorer, I'll tick the employees database with its six tables and two views. Next, I'll log into the SQL Server using the private IP address of my point-to-site VPN with SQL Server authentication. Now that I'm all connected, I need to make one modification to the target schema because I don't want to have an employees database with an employees schema. I'll modify the target schema to be the default dbo. Next, click the convert schema button, which will parse the MySql definitions into SQL Server DDL statements. Before we can migrate the data, we need to apply the schema definition to our target database. We do that by right-clicking on the target database and select synchronize with database. The output shows that we have an issue with the dept_emp_latest_date view. When I open it up, we can see there is a large number specifying how many rows we would like returned as part of the TOP clause. I'll change that to 1 million, click save and re-synchronize the database. Having fixed that issue, I can now migrate the data by clicking the migrate data button, funnily enough. And annoyingly enough, I have to re-authenticate with both the source and target servers. The migration assistant output window is telling us the number of rows migrated for each table. If we go over to the target server, we can see all the tables have been created, plus two extra ones created by SQL Server migration Assistant to manage the migration process. This is not an insignificant amount of data to transfer, and the point-to-site VPN connection does a relatively good job compared to using a public IP address over the internet. That is, it is quite fast and stable. Once the migration has finished, we get a summary detailing how many records of each table were successfully migrated.

About the Author
Students
21236
Courses
72
Learning Paths
14

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.