In this demo, we will migrate a couple of users and databases from an on-premises SQL Server to an Azure managed instance using the Data migration Service in the Azure portal. Before we can create our Data Migration resource, I need to check that the appropriate resource provider is registered within my subscription. From the portal home page, click on the subscriptions golden key, and once in subscriptions, select the one you're working with. In my case, and I guess for many of you, there'll only be one subscription. Once in the subscription, go down to the settings section in the left-hand menu and click Resource Providers. We're looking for Microsoft.DataMigration, so either scroll down until you find it or use the filter by name search box at the top to narrow the selection. Once located, make sure it is registered. If it isn't, just click the Register button at the top.

With the resource provider registered, we can go ahead and create the Database Migration service resource. That's Azure Database Migration, not Azure Data Migration, just so we're clear. Creating the resource through the portal is much like creating any other resource. I already have a resource group called DbMigrate that contains my SQL Managed Instance and virtual network. I'll put the migration service in the same West US 2 region as the other DB migrate resources and give it the name of dbmigration-service. The service mode will be Azure, and I'll stick with the standard one vCore pricing tier.

When I created the SQL managed instance and the associated virtual network, I created an additional subnet called migrate subnet, remembering that the managed instance needs to be in a subnet by itself. I also have a subnet called gateway for my point to site VPN gateway. That's all I need to do. I can now hit the create button to deploy the migration service. Once the migration service is up and running, we can create a new migration project. I'll call my project mihorsemigrate. Mi, for managed instance and horse is the name of the database I'm migrating. The source server is an on-premises SQL instance, and the target server is an Azure SQL managed instance. The migration activity type will be an offline data migration. The next few steps of the migration project wizard involve setting up our data source and target. I'll connect to the source database server with SQL authentication and using my sa login. There's a couple of things to note here. I'm using a point-to-site VPN connection, as opposed to a site-to-site, and don't have an Azure domain linked with my local domain, which means I have to use the IP address of my source server as it appears to the VPN. I also need to tell the data migration service to trust my server certificate. Once successfully connected, I can select the databases that I want to migrate to my managed instance. In this case, it will be two small databases, howhorse and lorrylog.

As you can see, I got a little bit too excited and forgot to enter the target server name going with the default placeholder. I'll quickly fix that up with lower case m my SQL managed instance name and try that validation again. The summary page confirms our source and target servers and the fact that we are migrating two of 21 databases on the source server. Right, I'll save the project now. Back at the migration project homepage, we now need to create a new activity to perform the migration. I'll select offline data migration. This does seem a bit clunky to me as we have two re-enter our source and target database credentials, as well as confirm the database selection. After the databases, I'll select logins from my source instance that I would like to migrate to the managed instance. Now to configure the migration settings, which is where things get interesting. I'll need to set up a share on my local PC that the migration service will backup the selected databases to. We also need to provide a local user with full control privilege on the backup folder I've just shared.

Migration service will back up the databases to the shared folder and then use the local credential to copy those backups to an Azure storage container. I've already created that storage container, and I'll paste in the shared access signature URI and validate the settings. You can see here that it is telling me I have insufficient permissions on the blob container DB backup. You don't set the correct permissions through the portal. You set them with the Azure Storage Explorer app. Microsoft Azure storage explorer is a free application you can download from the Microsoft website, and once authenticated, it provides an easy-to-use storage navigation GUI. I need to navigate to my DB backup container, right-click on it, and select get shared access signature. I'll tick all the permissions, extend the expiry time and grab the new shared access signature URI and paste it in. 

Having successfully validated the settings, we are now ready to start the migration. Before I hit the start button, I'll open up SQL Server management studio and log into my managed instance and have a look at the databases node, which shows there are no existing user databases. Next, I'll give my migration activity a name, start the migration, and quickly bring up the shared backup folder. There we go. We have two databases in the process of being migrated and two backup files in our shared backup folder. Switching over to SQL server management studio and refreshing the managed instances databases node, we can see the two newly migrated databases sitting there. In the portal, we can see the databases change from in progress to completed and the logins migration starting. 

Expanding the howhorse database reveals the migrated tables and stored procedures.