Recently Microsoft announced the Public Preview Azure Database Migration Service (Azure DMS), a new way to migrate your data from a SQL Server database or from other sources to Azure:
Thinking about it, I did this tutorial to show how can you migrate a SQL Server database On-Premises to the Azure using the Azure DMS.
The tutorial explains how establish a connection VPN Site-to-Site between the Azure and a Hyper-V (On-Premises) server until the migration of data using the Azure Database Migration Service.
- Azure subscription (PS.: A free account can be created through the site: https://azure.microsoft.com/en-us/free/?v=18.03);
- Check all Azure DMS pre-reqs on the following site: https://docs.microsoft.com/en-us/azure/dms/pre-reqs;
- Hyper-V host with Internet connection;
- ISO Windows Server 2016 (PS.: You can use an evaluation edition, see the site: https://www.microsoft.com/en-us/evalcenter/evaluate-windows-server-2016);
- ISO SQL Server 2016 or 2017 (PS.: You can use an evaluation edition, see the site: https://www.microsoft.com/en-us/sql-server/sql-server-downloads);
- Management Studio 17.x (Download: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms);
- Data Migration Assistant 3.x (Download: https://www.microsoft.com/en-us/download/details.aspx?id=53595);
- A sample database, in my case I will use a backup of the AdventureWorks2016 (Download: https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks).
1. Open the Virtual Switch Manager of the Hyper-V and create two virtual switches:
a. Name: External – Type: External (Link this with the interface that provides your Internet connection)
b. Name: Private – Type: Private
2. Create a new virtual machine with a 128 GB dynamic disk and 2 networks adapters, associate them to the switches that we created, one to the External and the other to the Private:
3. Install the Windows Server 2016 + SQL Server 2016 or newest (PS.: Add a SA account) and restore the backup from the AdventureWorks2016, if you chose this sample db like me.
4. Now, you’ll notice on the VM that one of our two network adapters will be with Internet connection, name this on the VM as External to avoid confusion. Name the other adapter as Private:
5. On the External adapter, right-click > properties, and keep only the TCP/IP marked:
6. Set the configuration of the IP and DNS as automatic:
7. Click on “Advanced...”, on the WINS tab mark “Disable NetBIOS over TCP/IP”.
8. On the Private adapter set the IPv4 and DNS mask, Gateway is not necessary, I’ll use:
IPv4 = 192.168.0.2
Subnet Mask = 255.255.255.0
DNS = 192.168.0.1
For now, this is our configuration for the Hyper-V, let’s move to the Azure.
1. Open the Azure Portal, the first thing we need to do is create a new resource group, I’ll call it SAMPLE-DMS-S2S-RG.
1. Inside the resource group, add a Virtual Network, I’ll call it SAMPLE-DMS-S2S-Vnet:
2. The address space for a virtual network is composed of one or more non-overlapping address ranges that are specified in CIDR notation. The address range you define can be public or private (RFC 1918). Whether you define the address range as public or private, the address range is reachable only from within the virtual network, from interconnected virtual networks, and from any on-premises networks that you have connected to the virtual network. In my case i am using 10.2.0.0/16.
If you need more information about it you can use the links below:
3. On subnet name I’ll use SAMPLE-DMS-S2S-Vnet-Subnet.
4. On subnet address range use 10.2.0.0/24.
5. On the resource group, select the one we created, in my case SAMPLE-DMS-S2S-RG:
Virtual Network Gateway
Now, let’s create a new Virtual Network Gateway inside our Resource Group, it will contain our second subnet.
1. Go to Virtual Networks> SAMPLE-DMS-S2S-Vnet > Subnets:
2. Click on + Gateway Subnet and on address range use 10.2.1.0/24:
Now you’ll see two Subnets:
3. Inside our Resource Group, add the Virtual Network Gateway and click on Create:
4. I’ll call the Virtual Network Gateway as SAMPLE-DMS-S2S-VnetGW.
5. Set the gateway type as VPN and the vpn type as Route-based.
6. Set the SKU as Basic.
7. For the Virtual Network select the one we created, in my case SAMPLE-DMS-S2S-Vnet.
8. On Public IP, since we don’t have one yet, we need to create one, I’ll call it as SAMPLE-DMS-S2S-VnetGW-PublicIP. Remember that this IP can take some time to be provisioned. Click on Create at the end.
PS.: This step can take some time (25min or +).
Local Network Gateway
Now that we created the Virtual Network Gateway, let’s create the Local Network Gateway.
1. On our resource group, add the Local Network Gateway and click on Create:
2. I’ll call it SAMPLE-DMS-S2S-LocalNetGW.
3. On IP address, we need to go back to our VM on Hyper-V and find out its public IP, for this, inside the VM open the command prompt and run the following command:
nslookup myip.opendns.com resolver1.opendns.com
In my case the Public IP is 18.104.22.168:
4. On Address space, add the sub-network from our Private network adapter (from the Hyper-V VM), 192.168.0.2/32, after that click on Create:
New connection - Local Network Gateway
1. Now, we need to create a new connection on the Local Network Gateway. To do this, go to your settings> connections and click on + Add:
2. I’ll call it SAMPLE-DMS-S2S-LocalNetGW-Conn.
3. Set your Virtual Network Gateway using the one we created before, in my case, SAMPLE-DMS-S2S-VnetGW.
4. Set the Shared Key (PSK) and remember to note it because we’ll use after on our Hyper-V VM, at the end click OK:
If all steps were successfully made until here we can now configure the RRAS on our Hyper-V VM.
RRAS Configuration inside the Hyper-V VM.
1. Inside our Hyper-V VM, open the Server Manager.
2. Go to Manage>Add Roles and Features.
3. On Before You Begin click Next.
4. On Selection installation type select Role-Based or Feature-based installation and click Next.
5. On Select server destination click Next.
6. On Select server roles select Remote Access and click Next.
7. On Select features click Next.
8. On Remote Access click Next.
9. On Role Services mark Direct Access and VPN (RAS):
10. Click on Add Features:
11. After that, mark Routing and click Next:
12. On Web Server Role (IIS) click Next.
12. On Select role services leave the default and click Next.
13. On Confirmation click on Install.
14. After the setup, on the Server Manager>Tools, click on Routing and Remote Access:
15. Right-click on the computer name and click on Configure and Enable Routing and Remote Access:
16. On first screen click Next.
17. On Configuration, mark Secure connection between two private networks and click Next.
18. On Demand-Dial Connections mark Yes and click Next.
19. On IP Address Assignment select Automatically and click on Next.
20. Click Finish and wait:
21. After that, a new window will open to configure the Demand-Dial Interface, click Next.
22. Set a name for it and click Next, I’ll use SAMPLE-S2S-RRAS-Azure:
23. On Connection Type select Connecting using virtual private network (VPN) and click Next.
24. On VPN type select IKEv2 and click Next:
25. On IP Address, go to our Virtual Network Gateway on Azure and copy his Public IP (Do you remember that we created it?), paste the copied address and click Next:
26. On Protocols and Security select Route IP Packets on this interface and click Next:
27. On Static Routes, we will add the route from our Azure subnet, on that case 10.2.0.0, 255.255.255.0, Metric: 24, after this click Next:
28. Check this new route on Static Routes:
29. On Dial-Out Credentials give a name that you want to and click Next and on the last screen click on Finish:
30. Now on Routing and Remote Access > Network Interfaces, right-click on the interface that we created, in my case, SAMPLE-S2S-RRAS-Azure, after this click on Properties:
31. On the Security tab, mark the option Use preshared key for authentication and inform the PSK that we used on Azure:
32. On the Options tab, mark the option Persistent Connection:
Testing the connection between On-Premises and Azure
1. Now, right-click on the interface, in my case SAMPLE-S2S-RRAS-Azure, click on Connect.
2. If everything is ok, the connection will be established and you can notice this looking the column Connection State and on Azure, look the column Status inside the Connection of the Local Network Gateway:
If for any reason the connect fail, check the ports of Azure on the Firewall and dont forget other network devices, the following link can be helpful: https://docs.microsoft.com/en-us/azure/architecture/reference-architectures/hybrid-networking/vpn.
3. Do a ping from the Hyper-V VM on the IP 10.2.0.0, it should reply with success:
DMA – Database Schema Migration from On-Premises to Azure
Now we’ll migrate the Database Schema to Azure trough the Data Migration Assistant (DMA).
If the DMA has not yet been installed, download and install it on our Hyper-V VM, use the link: https://www.microsoft.com/en-us/download/details.aspx?id=53595
1. Open the DMA, On the left, click “+”, select Migration, give a name to the Project, in my case SAMPLE-AzureDMS-Demo, on Source select SQL Server and set the target as Azure SQL Database, on migration scope select Schema Only and click on Create.
2. On connect to source inform the On-Premises instance name, select the authentication method you prefer (I’ll use Windows Authentication), mark the options Encrypt Connection and Trust Server Certificate and click on Connect.
3. Select the AdventureWorks2016 database restored before and click on Next.
4. On Connect to target server, click on Create New Azure SQL Database:
5. Open the Azure portal and create on our Resource Group a new Azure SQL Database in blank called AdventureWorks2016:
6. On Server, create a new one, I’ll name it as azuredmstestserver:
7. On the Azure SQL database we just created click on Set server firewall:
8. Create a rule with the Public IP of our Hyper-V VM, in my case 22.214.171.124, click on Save:
9. Return to the DMA and inform the server name of the Azure SQL database, authentication method, mark the options Encrypt connection and Trust Server Certificate and click on Connect:
10. Select the Azure SQL database that we created and click on Next.
11. On Select objects, mark all objects and click on Generate SQL Script:
12. After the generate of the script click on Deploy schema:
13. Wait the deploy:
14. After the deploy you can close the DMA.
Azure Database Migration Service – Data migration
Now that our schema was migrated to the Azure, let’s migrate the data using the Azure Database Migration Service.
1. On Azure Portal click on Subscriptions:
2. After select the subscription, go to Resource Providers:
3. Search for Microsoft.DataMigration and click on Register.
4. Now click on Create a resource, find the Azure Database Migration Service and click on Create:
5. On the Azure DMS configurations, give it a name (I’ll use SAMPLE-AzureDMS-Demo), select the Vnet created before, in my case SAMPLE-DMS-S2S-Vnet and click on Create:
6. After the deploy, open the resource and click on + New Migration Project:
7. Choose a name for the Project, I’ll use AzureDMSDataMigration, after this click on Create:
8. Now, on Select source, use the IP from our Hyper-V VM Private network adapter (which is also the same IP from our SQL Server), inform the user (in my case the sysadmin) and click on Save:
9. On the next step, select the AdventureWorks2016 database and click on Save:
10. Now we have to specify the connection information of our target Azure SQL database, after this click on Save:
11. The next screen contains a summary of whats is involved on our Azure DMS project, double check the information and click on Save:
12. Now, we will be back on the DMS main screen, click on the Project we just created and click on + New Activity, after this click on Run Database Migration:
13. It will be necessary re-enter the passwords of the source server and target server, after this click on Save.
14. On the step Map to target databases notice that our database created before will be automatically selected, here we also need to mark the option SET SOURCE DATABASE READ-ONLY, after this click on Save:
15. On the step Select tables, as we already migrate the schema from the on-premisses database, the tables are automatically linked (On-Premises DB x Azure SQL DB), click on Save:
16. Now, we have a summary of what was selected, give a name for the activity (in my case AzureDMSActivity), on Validation Option, I’will choose Do Not Validade because its just a demo scenario, double check the information, if everything is ok click on Run migration:
17. Now we can see our Activity inside the DMS Project with the state Running:
18. If you click on the activity it’s possible to check the migration with more details or even stop-it:
19. Wait the DMS finish the migration of data between the tables, in my case it tooks ~4 minutes:
20. You also can download a PDF report containing the migration details, just click on Download report:
21. If you click on the database line you can also check the migration details per table:
We come to the end of this tutorial, thank you!
Migrate SQL Server to Azure SQL Database
Differentiating Microsoft’s Database Migration Tools and Services
Let’s Configure Azure Site-to-Site VPN with RRAS in Azure Resource Manager!