Introduction & Overview
The course is part of these learning paths
This course explores how to plan and implement data platform resources specifically with regards to Azure SQL offerings. In particular, we will explore the benefits and features of the SQL PaaS offerings along with billing models, service tiers, and high availability options. We'll also cover migration scenarios and provide a demo that migrates a sample database from an on-premise SQL Server to an Azure SQL managed instance using DMS.
If you have any feedback relating to this course, feel free to contact us at firstname.lastname@example.org.
- Get a general understanding of Azure SQL databases
- Learn how to deploy Azure SQL databases
- Understand business continuity and security tie in with SQL databases
- Learn how to scale, upgrade, and partition your databases
- Learn how to migrate a database from an on-premise SQL Server to an Azure SQL instance
- Anyone who wants to learn about Azure SQL Offerings
- Those preparing for Microsoft’s DP-300 exam
To get the most out of this course, you have should a general understanding of the fundamentals of Microsoft Azure. Experience using databases — especially SQL Server — would also be beneficial.
In this demonstration, I'm going to use the data migration service to migrate an on-premise SQL Server database to an Azure SQL Managed Instance. Here we are on the basics page for creating a database migration service resource. I got here by simply searching for Azure database migration under create a new resource.
I'll select an existing resource group and give the migration service the name of DMS and select West U.S as that is the location of my networking resources. I'm going to configure my pricing tier and select premium because I want to do an online migration.
Under networking, I'll choose the express route VNET I've already set up. No tags, and I'll now create that migration resource. While the service is being provisioned, let's jump over to this virtual machine I've set up, which is running SQL Server 2016. And we have the example database AdventureWorks.
We are going to migrate AdventureWorks from the default SQL Server instance to an Azure managed instance. Going back to the portal, the DMS deployment is underway, and on this other page, I have the target SQL managed instance located in the West U.S within the infrastructure VNET. The DMS deployment has finished.
If we go over and have a look at the resource, we have no projects. So we will need to create a new migration project. I'll give the project a creative name like AdventureWorks, stick with the default source server of SQL server and pick my target server type of Azure SQL Database Managed Instance.
Next I'll change the activity type from offline to online data migration. Now that I've told the migration service what the source and target databases are, we get a helpful little summary telling us what we need to do to make sure we have a successful migration.
Number one, make sure we have a Managed Instance set up, which we do. And number two, use the data migration assistant to assist our source SQL server database for compatibility. If the data migration assistant has any recommendations, we need to apply those fixes after the migration. And implement any prerequisites before configuring the online migration. Clicking Create and run activity, we'll start the wizard. But first I need to run the data migration assistant, which I previously downloaded and installed.
The first thing I need to do is create a new assessment project. I'll give the project a name. The assessment type is Database Engine, and we are going from an SQL Server to an Azure SQL Database Managed Instance. Clicking Create will take me to a wizard where I can fill in all the details about my source and target databases. I'll enter my source server name, and I'm already authenticated with Windows. So I can just connect to my source instance.
Now I can add the source instance and select the AdventureWorks database, which happens to be the only database on my server. Click Add and start the assessment. We have no feature parody problems. The only compatibility issue that we are notified about, is the full text search functionality, which has changed since SQL Server 2008. So not really an issue for us as we are going from SQL Server 2016. There are different compatibility tabs for the SQL Server versions that are newer than the source version.
Now upload the assessment to Azure Migrate by connecting to Azure and logging in with my credentials. Now that I'm authenticated, I'll select my subscription and upload the assessment. Going back to the migration was at in the portal, I can now finish filling in the source database details and save them.
The data migration service needs to be able to authenticate with the SQL Managed Instance and blob storage during the migration process. It does this by using an Azure app registration. I'll just grab the app ID and key of the SQL migration app that I've registered earlier. I'll skip the application ID contributor level, excess check, next I'll select the subscription that contains my managed instance followed by the actual managed instance itself and the credentials for the SQL login to the managed instance. I'll save that and save the selected AdventureWorks sourced database.
Now I need to specify the VMs network share location where the backups will be read from, followed by my Windows user's credentials to access that location. Finally, I need to select the storage account. So we have the backup location, the user credentials subscription, and the storage account.
An instance of the migration being run is an activity, and I'll give that a name of migration and then hit the Run migration button.
Going to have a look at the migration progress, we can see that the backup file has been uploaded and the log shipping is in progress. We've got the target and source servers at the top with their respective versions.
If I hit the refresh button, we can see that the database has been restored and the Start Cutover button is enabled. So we can click that to begin the cutover. The cutover blade gives us some information about incoming connections. So I'll confirm that and hit the Apply button to begin the cutover. If we return to our on-premise instance and the VM, I've added the SQL Managed Instance to SQL Server Management Studio, and if I expand databases, we can see the AdventureWorks database is there. Going back to the migration service, we can see that the cutover has been completed.
Course Introduction - Azure SQL Databases Overview - Deployment Options - ARM Templates Deployment - DEMO: Deploying Azure SQL Databases - Business Continuity - Security Database Services - Scale and Performance - DEMO: Scaling Azure SQL Databases - Partitioning Data - Migrating to Azure - Migration Scenarios - Upgrade Scenarios - Summary
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.