Migrating SQL Server to Managed Instances
- SQL Database Managed Instance Lab
- Overview
- Abstract and learning objectives
- Lab Registeration experience
- Exercise 1: Exploring the SQL Managed Instance and the associated resources
- Exercise 2: Accessing the pre-deployed envrionment
- Exercise 3: Configuring Shared folder in the SQL Server for Backup
- Exercise 4: Create a migration project
- Exercise 5: Verifying the successful migration of Databases
- After the hands-on lab
Managed instance is a deployment option of Azure SQL Database, providing near 100% compatibility with the latest SQL Server on-premises (Enterprise Edition) Database Engine, providing a native virtual network (VNet) implementation that addresses common security concerns, and a business model favorable for on-premises SQL Server customers. The managed instance deployment model allows existing SQL Server customers to lift and shift their on-premises applications to the cloud with minimal application and database changes. At the same time, the managed instance deployment option preserves all PaaS capabilities (automatic patching and version updates, automated backups, high-availability ), that drastically reduces management overhead and TCO.
This hands-on lab is designed to provide exposure to Azure SQL Managed Instance and migration exisitng on premises Databases to SQL Managed Instance.In this lab, you migrate the WorldWideImporters database from an on-premises instance of SQL Server to an Azure SQL Database managed instance by using the Azure Database Migration Service.
By the end of the lab, you will be able to migrate a SQL Database from a SQL Server to an Azure SQL Managed Instance using Data Migration Service.
-
Navigate to the URL provided to sign-up for the lab.
-
Enter the following details:
- First Name
- Last Name
- Work email
- Organization
- Country
After entering the details click on SUBMIT.
- After entering the details, click on the LAUNCH LAB button to proceed further.
For a Managed Instance to perfrom desirably over a virtual netwotk, there some preparations to be done such as setting up route tables, setting up the Network Security Group with the required set of rules. The Managed Instance will itself take care of all the necessary actions and preparations that are necessary for its smooth operation.To explore SQL Managed instances and the associated resources proceed as follows.
-
Navigate to the SQLMI-VNET-RG in which the Managed Instance and the associated resources are deployed.
-
Select the route table as shown below.
- Click on Routes to see all the entries to route the traffic related to the Managed Instance will be displayed.
- After reviewing the Route Table, navigate to the Network Security Group as shown below.
- In the Overview section under Settings, click on Inbound security rules view all the inbound security rules created.
- Similarly Navigate to the Outbound security rules to view the Outbound security rules.
- After reviewing the Network Security Group, navigate to the SQL Managed Instance as shown in the image below.
- In the Overview section of the SQL Managed Instance, all the information will be displayed. Copy the value of the Host as shown below. This will be needed in the further exercises.
To login in to the pre-deployed environment proceed as follows:
- From the ODL details page copy the username and password.
-
Login to Azure by using the provided credentials. To login to azure go to portal.azure.com
-
The details of the SQL Server VM is also provided in the Lab details page.
Duration: 20 mins
To configure the shared folder in the SQL Server, proceed as follows:
-
Login to the SQL Server with the credentials provided in the lab details page.
-
Create a new folder on the desktop named backup.
-
Right click on the folder named backup and click on Properties.
- In the folder properties, click on Security. Now click on Edit as shown below.
- Now click on Add.
- Type Everyone and click on Check Names. Then click on OK.
- For Everyone check the box corresponding to Full control. Now click on Apply and then OK.
-
To share the folder proceed as follows.
-
In the folder properties, click on Sharing. Now click on Share as shown below.
- Click on the Share button as shown below.
- Click on Done.
Duration: 30 mins
To create a migration project proceed as follows:
-
Login to azure using the provided credentials.
-
Click on Resource groups and select the Resource group as shown below.
- In the Overview blade of the Resource group, select the Azure Database Migration Service.
- In the Overview blade of the DMS, click on the +New Migration Project.
-
Enter the following details.
- Project name: sql-mi-01
- Source server type: SQL Server
- Target server type: Azure SQL Database Managed Instance
- Choose type of activity: Offline data migration
After entering details, click on Create and run activity
-
Now the Migration Wizard window comes up.
-
Click on Select source and enter the following details.
- Source SQL Service instance name: Enter the DNS name of the lab-sql-vm
- Authentication type: SQL Authentication
- Username: dbuser (dbuser is a pre created user which has all the required permissions to perform the migration)
- Password: demolabpassword1!
Make sure both the boxes under Connection properties are checked.
Click on Save
-
Click on Select target and enter the following details.
- Target server name: Enter the Host DNS of the Managed Instance
- Authentication type: SQL Authentication
- Username: demouser
- Password: demolabpassword1!
Click on Save.
-
Click on Select databases and check the box corresponding to WideWorldImporters. Click on Save.
- Click on Select logins and check the box near SOURCE LOGINS. Click on Save.
-
Click on Configure migration settings and enter the following details.
- Choose source backup options: I will let Azure Database Migration Service create backup files.
- Network share location that Azure Database Migration Service can take database backups to: \192.168.0.4\backup
- Windows User Azure Database Migration Service impersonates to upload files to Azure Storage: lab-sql-vm<username>
- Password: enter your password
- SAS URI for Azure Storage contianer that Azure Database Migration Service will upload the files to: Enter the SAS URI of the storage account
Click on Save.
-
Now enter the following details.
- Activity Name: migration-activiy
- Validation option: Do not validate
Now click on Run migration
-
After the migration completes, select Download report to get a report listing the details associated with the migration process.
To verify whether the migration of the databases were successfull, proceed as follows:
-
Navigate to the lab-jump-vm and start a RDP session.
-
Click on the Start button to open the start menu. Expand Microsoft SQL Server Tools 17 and open Microsoft SQL Server Management Studio.
- When the login page appears, enter the following details:
- Server Type: Database engine
- Server Name: Enter the ip address of the Managed Instance*
- Authentication: SQL Server Authentication
- Login: demouser
- Password: demolabpassword1!
After entering the details click on Connect.
- On the Object Explorer, expand Databases and you can see the database that is migrated.
Duration: 10 mins
In this exercise, you will delete any Azure resources that were created in support of the lab. You should follow all steps provided after attending the Hands-on lab to ensure your account does not continue to be charged for lab resources.
-
Using the Azure portal, navigate to the Resource group you used throughout this hands-on lab by selecting Resource groups in the left menu.
-
Search for the name of your research group, and select it from the list.
-
Select Delete in the command bar, and confirm the deletion by re-typing the Resource group name, and selecting Delete.
You should follow all steps provided after attending the Hands-on lab.