In part 1 of this series, we covered the case for Managed Instance. In part 2, we covered how to provision Managed Instance and connect to it using SSMS via a point-to-site VPN. In this post, I’ll show you how to migrate an existing database using Azure Database Migration Service.
Now that we have our Managed Instance, it’s time to put it to work! Other than creating a new database from scratch, there’s a number of ways to get data into our Managed Instance including transactional replication, bulk loads, partner offerings such as Attunity Replicate and Native Restore from URL, which uses Azure Blob Storage for the .bak file containing a backup of the source database. This technique, which involves downtime, is covered here. In our case, we want to minimize downtime during migration, so we’ll focus on using Azure Database Migration Service (DMS).
There’s a couple of excellent resources which walk through this process including the Azure Database Migration Guide, which provides tailored guidance based on source-target pairs, e.g.; SQL Server to Managed Instance, and tutorials in the online documentation.
Before we launch into the migration details, let’s call out a few important pre-migration tasks.
Discovery and Inventory
As covered in the first post, the sweet spot for Managed Instance is bulk migration of existing SQL Server instances. As such, having an inventory of your existing SQL Server landscape is an important planning step, and there’s a number of Microsoft and partner tools for this purpose including the MAP toolkit, Azure Migrate and partner offerings such as Cloudamize.
Target Optimization and TCO Calculations
One of the benefits of PaaS services such as Managed Instance is the ability to scale the performance to obtain the best operating cost, rather than through upfront capital investment in on-prem hardware and software. However, having a good understanding of the likely costs is a required step for budget approvals, and there’s a number of excellent offerings to enable you to scan your current workloads over a period of time (including peak usage times) to determine the best configuration.
Managed Instance can currently scale from 8 to 80 cores, and while you scale it after creation, partner offerings such as Cloud Atlas help you determine the optimal price-performance configuration as early as possible. And also be sure to checkout the Azure TCO calculator to quantify the cost benefits of PaaS migration.
While the focus of this post is Managed Instance, it may not be the best target state for your migration. Be sure to checkout our Data Workload Assessment Model and Tool which uses an application focused Q&A approach to determine the best target state platform (including Azure PaaS services such as Managed Instance, SQL Data Warehouse and Virtual Machines), the likely level of remediation required and the estimated DWUs required, if migrating to SQL Data Warehouse.
Database and Performance Assessment
One of the great things about Managed Instance is that we can migrate databases from all the way back to SQL Server 2005, with database compatibility level settings keeping the application experience the same. Having said that, it’s best practice to perform an assessment on the schema to detect potential compatibility issues that can impact database functionality in your target state environment. The Data Migration Assistant tool is used for this purpose, with support for Managed Instance as a target state.
Equally as important as schema assessment is performance assessment to determine the likely performance outcome of current workloads in the target state environment. The Database Experimentation Assistant is used for this purpose, providing an A/B testing solution for SQL Server upgrades, allowing for the capture and replay of production workloads on Azure SQL Database, Azure SQL Managed Instance and SQL Server on Linux.
Let’s get to it! As covered in this article, there’s a number of important pre-migration steps to complete, including;
- Configuring your Windows Firewall for source database engine access. In my case, I allowed traffic in on ports 1433 and 1434 so that the Database Migration Service can connect to my source database
- Creating a network share for the source database backups (full and transactional) that the account running the SQL Server service has access to
- Creating a Windows User to provide to the Database Migration Service which has access to the network share in step 2. It will impersonate this user to obtain the backup files from the network share
- Creating a Storage Account in the same region as the Database Migration Service and Managed Instance, and obtaining the SAS URI, which is described here
- Register the Microsoft.DataMigration resource provider, described here
vNets, Subnets and Network Security Groups
As covered in this article, there’s a number of network configurations we can use to perform the migration from our on-prem database to our Managed Instance. In this post, I’ll use a single vNet for both Managed Instance and the Database Migration Service, with each in their own subnet, as shown below.
An important note on the subnets: Before you create the Database Migration Service, create a new Subnet in the Managed Instance vNet. I called my Subnet “DMS”. When you create the Database Migration Service, it will ask you what vNet to create the service in. Be sure to choose the dedicated subnet you’ve created. In the example below, I chose the vnet-rodcolmanagedinstance/DMS option. The other option listed contains the ManagedInstance itself, and shouldn’t be selected for the Database Migration Service.
The other important step is to create a Network Security Group (NSG) and apply it to the subnet you created for the Database Migration Service. The rules to apply to the NSG are contained at the bottom of this article. The process to create an NSG and associate it to the subnet is described in this article.
Once you’ve covered these pre-migration steps, you can go ahead and create the Migration Service in the Azure Portal.
Azure Database Migration Service
As shown below, there’s only a few bits of information to provide, but the ones I want to focus on are the vNet and Pricing tier.
The pricing tier options comes in Standard and Performance. Standard lets you choose from 1-4 cores, and Premium is 4 cores by default. Importantly, only the Premium option enables you to perform minimal downtime migrations, which we’ll cover later.
As covered above, when selecting the vNet and Subnet, make sure to choose the dedicated subset created for the Database Migration Service with the NSG applied.
Once the Database Migration Service is provisioned, we can go ahead and create a migration project.
Firstly, make sure the source database to migrate is in Full recovery mode, and you take a full backup with CHECKSUM, storing the backup file in the network share you created above e.g.;
BACKUP DATABASE [AdventureWorksDW]
TO DISK = ‘C:\DMS\Aworks.bak’
From the home screen of your Database Migration Service, click + New Migration Project
Per below, select your source and target server types. In my example, I’m migrating from an On-Prem SQL Server database to Azure SQL Database Managed Instance. I’ve also selected the option for an Online data migration. This is the option to choose to minimise the migration downtime. On the destination server, it will restore a full backup of the source, and remain in recovery mode, trickling in new transaction log backups as they’re taken. This is very similar to transaction log shipping. We’ll cover the cutover process below.
For step 1, enter the source SQL Server details, as per the below example. In my case, I entered the IP address of my laptop running a local SQL Server installation. Note; if you’re doing the same as me, ensure you’re running the point-to-site VPN that we created in the previous post, and use the IP Address from the VPN adapter, available by running ipconfig in a command window. I also clicked “Trust Server Certificate”. Alternatively, follow this link.
Step 2 requires an Azure Application Registration. This process is covered here. Note, when creating the app registration, the Sign-on URL is irrelevant – you can use anything, as it’s not required by the Migration service. Enter your App ID and Key, select the target Managed Instance, and supply the Managed Instance User name and password.
For step 3, select the source database
For step 4, enter the network share, along with a Windows account and password that has access to the share. This account will be impersonated by the Migration Service to obtain the backup files. Finally, select the storage account you created earlier. This is where the Migration service will copy the backup files to restore to the destination, in my case, my Managed Instance.
The final step lets you review and save the migration summary before executing it.
The migration service will copy the full backup file from the source network share to Azure storage, restore the backup to the destination server, and because we’ve chosen the Online Data Migration option, will restore in recovering mode, awaiting more transaction log backups. You can check the status of the migration by refreshing the screen shown below. When the full backup is restored, the status will change to “Log files uploading”
Assuming this was a live production database, data would continue to be changed along with the usual scheduled transaction log backups (making sure you use the CHECKSUM option, a requirement for the migration service). If you click the database name shown in the above screen shot, you can see a history of transaction log backup files that have loaded to the migration service.
When you’re ready to cutover, click the Start Cutover button shown above. Per the below screen shot, the service recommends you stop all transactions on the source database, take a final transaction log backup and click Confirm and Apply.
And we’re done! At this point we have a replica of our source database on our destination server, with the only downtime being the time to copy, load and restore the final transaction log backup.
Hopefully this series of blog posts have give you a good feel for both Managed Instance and the Database Migration Service.