Migrating your on-premises workload to the cloud using the Re-host scenario offers numerous advantages, such as increased scalability, flexibility, and cost savings. By following the step-by-step process outlined in this document, you can ensure a smooth and successful migration.

Remember to assess your workload, choose the right cloud provider, set up the cloud environment, establish network connectivity, replicate and migrate your workload, test and validate, and optimize your cloud environment post-migration. With careful planning and execution, your organization can unlock the full potential of the cloud.

Overview:

This post demonstrates how we can rehost a SQL database running on-prem to Azure. We will explore various migration scenarios and deep dive into how to migrate the application database to Azure SQL Managed Service.

There are various options for database re-hosting, we will explore these scenarios first and when to use which scenario.

This post is the continuation of the previous article on re-host scenario where we covered App re-host in detail.

App modernization- re-host scenario – Rajeev Singh | Coder, Blogger, YouTuber (wpcomstaging.com)

Business drivers

The business drivers remain the same as those described in the App Modernization re-host scenario.

Migration goals

In addition to migration goals, there may be additional requirements for database migration, such as

Minimal administration. Database administration tasks are to be minimized after the application is migrated.

Avoid Azure SQL Database. The client can’t use Azure SQL Database for this application and is looking for alternatives.

Solution design

It’s time to design the solution, let’s look at the current architecture and propose a new one.

Current architecture

• Contoso has one main data center, contoso-datacenter, which is located in New York City. It has a fiber-optic connection to the internet from Metro Ethernet Networks that provides 500 megabits per second. The main data center is fully virtualized by VMware products. Contoso has two VMware hosts running ESXi 6.5 that are managed by vCenter Server 6.5.
• Contoso has three branches across the United States. Each branch is connected locally to the internet by using business-class connections, and each branch is connected to the main data center via VPN with IPsec. This configuration allows Contoso’s entire network to always be connected, and it optimizes internet connectivity.
• Contoso uses DNS servers on its internal network.
• Contoso uses Active Directory for identity management.
• Contoso has an on-premises domain controller, contosodc1. The domain controllers at local branches run on physical servers. The domain controllers run on VMware VMs. The VMware environment is managed by vCenter Server 6.5, vcenter.contoso.com, running on a VM.
• SmartHotel360 is tiered across two VMs, WEBVM and SQLVM, that are located on a VMware host running ESXi version 6.5, contosohost1.contoso.com.

Proposed architecture

In this scenario, Contoso migrates its two-tier, on-premises travel application and database as follows:

• Migrate the application database, SmartHotelDB, to a SQL Managed Instance.
• Migrate the front end, WEBVM, to an Azure VM.
• Decommission the on-premises VMs in the Contoso data center after the migration is complete.

Choose the right Database solution:

Below is the scenario to select the right database migration options:

Migration Type (SQL Server To…)
SQL Server (upgrade)	If one of the following conditions applies to your business, consider moving to a SQL Server virtual machine (VM) instead: You require direct access to the operating system or file system, such as to install third-party or custom agents on the same virtual machine with SQL Server. You have a strict dependency on features that are still not supported, such as FileStream/FileTable, PolyBase, and cross-instance transactions. You need to stay at a specific version of SQL Server (2012, for example). Your compute requirements are much lower than a managed instance offers (one vCore, for example), and database consolidation is not an acceptable option.
Azure SQL (PaaS)	Limited feature, then use Azure SQL (PaaS)
Azure SQL Managed Instance	Azure SQL Managed Instance is a recommended target option for SQL Server workloads that require a fully managed service without having to manage virtual machines or their operating systems. SQL Managed Instance enables you to move your on-premises applications to Azure with minimal application or database changes. It offers complete isolation of your instances with native virtual network support.
SQL Server on Azure VM	If one of the following conditions applies to your business, consider moving to a SQL Server virtual machine (VM) instead: You require direct access to the operating system or file system, such as to install third-party or custom agents on the same virtual machine with SQL Server. You have a strict dependency on features that are still not supported, such as FileStream/FileTable, PolyBase, and cross-instance transactions. You need to stay at a specific version of SQL Server (2012, for example). Your compute requirements are much lower than a managed instance offers (one vCore, for example), and database consolidation is not an acceptable option.
Azure Synapse	Azure Synapse Analytics is a distributed system designed to perform analytics on large data If your business requires an enterprise-class data warehouse. Consider using Azure Synapse Analytics when you: •     Have one or more terabytes of data. •     Plan to run analytics on substantial amounts of data. •      Need the ability to scale compute and storage. Want to save on costs by pausing compute resources when you don’t need them

Tools and Migration Steps:

Migration Type (SQL Server To…)	Tools
SQL Server (upgrade)	Azure Database Migration Service Azure Data Factory
Azure SQL (PaaS)	Azure Database Migration Service Azure Data Factory
Azure SQL Managed Instance	Azure SQL Migration extension for Azure Data Studio Azure Database Migration Service Native backup and restore Azure Data Factory
SQL Server on Azure VM	Azure Data Explorer, AzCopy command-line utility
Azure Synapse	bcp  SqlBulkCopy API

Migration methods

You can use different migration methods to move your data between SQL Server, Azure SQL Database, and Azure SQL Managed Instance.

Some methods are Online and picking up all changes that are made on the source while you’re running the migration, while in Offline methods you need to stop your workload that is modifying data on the source while the migration is in progress.

Source	Azure SQL Database	Azure SQL Managed Instance
SQL Server (on-premises, AzureVM, Amazon RDS)	Online: Transactional Replication Offline: Data Migration Service (DMS), BACPAC file (import), BCP	Online: Managed Instance link, Log Replay Service, Azure Data Migration Service (DMS), Transactional Replication Offline: Azure Database Migration Service, Native backup/restore, BACPAC file (import), BCP, Snapshot replication
Single database	Offline: BACPAC file (import), BCP	Offline: BACPAC file (import), BCP
SQL Managed Instance	Online: Transactional Replication Offline: BACPAC file (import), BCP, Snapshot replication	Online: Database copy/move preview, Transactional Replication Offline: Cross-instance point-in-time restore (Azure PowerShell or Azure CLI), Native backup/restore, BACPAC file (import), BCP, Snapshot replication

In our scenario, let’s consider the business drivers.

• Client can’t use Azure SQL as there are few features not supported by Azure SQL (PaaS solution)
• The client doesn’t want to manage the VM and is looking for a solution.

Azure SQL-managed instances are recommended in the above scenario.

Solution review

The next step is to evaluate the proposed design by listing both pros and cons.

Consideration	Details
Pros	Contoso can move WEBVM to Azure without changes, which makes the migration simple. SQL Managed Instance supports Contoso’s technical requirements and goals. SQL Managed Instance provides 100 percent compatibility with Contoso’s current deployment while moving the company away from using SQL Server 2008 R2. Contoso can take advantage of its investment in Software Assurance and use the Azure Hybrid Benefit for SQL Server and Windows Server. SQL Managed Instance has built-in fault tolerance that Contoso doesn’t need to configure. This feature ensures that the data tier is no longer a single point of failure.
Cons	WEBVM runs Windows Server 2008 R2. Although Azure supports this operating system, it’s no longer a supported platform. The web tier remains a single point of failover with only WEBVM providing services. Contoso must continue to support the application web tier as a VM rather than moving to a managed service, such as Azure App Service. For the data tier, SQL Managed Instance might not be the best solution if Contoso wants to customize the operating system or the database server, or if the company wants to run third-party applications along with SQL Server. Running SQL Server on an IaaS VM could provide this flexibility.

Migration process

Once the review is completed, it’s time now to start the migration work for the web and data tier to Azure by completing the below steps.

1. Landing zone for migration:

Add a couple of specific components to the Azure infrastructure that Contoso previously configured (as described in Deploy a migration infrastructure). Much of the infrastructure that’s required for this migration is already in place.

Create the foundation layer to support your migration project, you can align to Azure Landing Zones and prepare the foundation layer such as establishing hybrid connectivity (VPN and connectivity), Preparing identity (Active directory and Hybrid identity), Extending domain controller, DNS, Azure Firewall changes, Routing, etc.

Refer: Cloud Adoption Framework enterprise-scale landing zones in Azure – Cloud Adoption Framework | Microsoft Learn

• Migrate the data tier by using Azure Database Migration Service. This service connects to the on-premises VM that runs SQL Server via a site-to-site VPN connection between the Contoso datacenter and Azure. The service then migrates the database.
• Migrate the web tier by using Azure Migrate. The process requires preparing the on-premises VMware environment, configuring and enabling replication, and then migrating the VMs by having them fail over to Azure.

Migration of the web is covered in detail in the post: App modernization- re-host scenario – Rajeev Singh | Coder, Blogger, YouTuber (wpcomstaging.com)

We will now cover database migration in detail here.

Prerequisites

In order to migrate a database, the following prerequisites must be met.

Database Migration Service

Requirements	Details
Compatible VPN	For Azure Database Migration Service, you need a compatible on-premises VPN device. You must be able to configure the on-premises VPN device. It must have an externally facing public IPv4 address. The address can’t be located behind a NAT device.
Access on-prem SQL database	Make sure that you can access your on-premises SQL Server database.
Firewall rules to access the source database	Windows Firewall should be able to access the source database engine. If there’s a firewall in front of your database machine, add rules to allow access to the database and files via SMB port 445.
SQL server credentials/access	The credentials for connecting to the source instance of SQL Server and for the targeted SQL Managed Instance must be members of the sysadmin server role.
Network share	You need a network share in your on-premises database that Azure Database Migration Service can use to back up the source database.   Make sure that the service account that runs the source instance of SQL Server has write permissions on the network share. Make a note of a Windows user and password that has full-control permissions on the network share. Azure Database Migration Service impersonates these user credentials to upload back-up files to the Azure Storage container.
TCP/IP protocol	The installation process for SQL Server Express sets the TCP/IP protocol to Disabled by default. Make sure that it’s set to Enabled.

Scenario steps

Once pre-requisites are completed, it’s time now to start the database migration work.

Step 1: Prepare a SQL-managed instance. 

Prepare and create SQL Managed instances.

• Create Subnet
• Set up virtual network
• Set up routing
• Create a managed instance

Step 2: Prepare Azure Database Migration Service. 

• Register the database migration provider, and create an instance.

• Set up a shared access signature (SAS) uniform resource identifier (URI) for the instance of Database Migration Service.

A SAS URI provides delegated access to resources in Contoso’s storage account so that Contoso can grant limited permissions to storage objects. Contoso sets up a SAS URI so that Azure Database Migration Service can access the storage account container to which the service uploads the SQL Server backup files.

• Create an Azure Database Migration Service project.

Step 3: Prepare Azure for the Migration and modernization tool. 

• Create a virtual network for the Azure VMs that are created during migration.
• Provision the Migration and modernization tool, part of Azure Migrate.

Step 4: Prepare on-premises VMware for the Migration and modernization tool. 

Prepare accounts for VM discovery and prepare to connect to Azure VMs after migration.

• A virtual network for the Azure VMs when they’re created during migration.
• The Azure Migrate appliance, is provisioned and configured.

Step 5: Replicate the on-premises VMs. 

• Sets up replication and starts replicating VMs to Azure Storage.

Step 6: Migrate the database via Azure Database Migration Service.

• Create a Database Migration Service project.

• Migrate the database

Step 7: Migrate the VMs with the Migration and Modernization tool. 

• Run a test migration to make sure everything’s working fine.
• Run a full migration to move the VM to Azure.

Clean up after migration

With the migration finished, an Azure VM runs the SmartHotel360 application and SQL Managed Instance hosts the SmartHotel360 database.

Now, perform these cleanup tasks:

• Remove the WEBVM machine from the vCenter Server inventory.
• Remove the SQLVM machine from the vCenter Server inventory.
• Remove WEBVM and SQLVM from local backup jobs.
• Update internal documentation to show the new location and IP address for WEBVM.
• Remove SQLVM from internal documentation. Alternatively, Contoso can revise the documentation to show SQLVM as deleted and no longer in the VM inventory.
• Review any resources that interact with the decommissioned VMs. Update any relevant settings or documentation to reflect the new configuration.

Review the deployment.

Now that the application is running, it must fully operationalize and be secured in Azure.

Security

Check the Azure VMs and the SQL-managed instance for any security issues in the implementation:

• Review the network security groups that control access for the VM. Network security groups help ensure that only traffic that’s allowed to the application can pass.
• Consider securing the data on the disk by using Azure Disk Encryption and Azure Key Vault.
• Enable threat detection on the managed instance. In the event of a threat, threat detection sends an alert to Contoso’s security team/service desk system to open a ticket.

Business continuity and disaster recovery

For business continuity and disaster recovery, take the following actions:

• Keep data safe. Backs up the data on the VMs by using the Azure Backup service.
• Keep applications up and running. Replicates the application VMs in Azure to a secondary region by using Site Recovery.

Licensing and cost optimization

• If Contoso has existing licensing for, take advantage of pricing with Azure Hybrid Benefit, Contoso converts the existing Azure VM.
• Contoso uses Azure Cost Management + Billing to ensure that the company stays within the budgets that IT leadership established.

Conclusion

In this article, we covered details on how we can plan and migrate the on-premises database to a SQL-managed instance by using Azure Database Migration Service.

In this post, we covered the migration approach and execution for the re-host scenario, migrating on-premises databases to Azure SQL-managed instances using Azure Database Migration Service.

Azure Database Migration Service offers a reliable and efficient approach for organizations looking to leverage the benefits of the cloud. By following the steps outlined in this article, organizations can successfully plan and execute the migration, thereby ensuring a seamless transition to the scalable and robust Azure SQL-managed instance environment.

References:

Rehost an on-premises application by migrating to Azure VMs and Azure SQL Managed Instance – Cloud Adoption Framework | Microsoft Learn