Connect Read Only SQL Server to Azure SQL Managed Insance

[visithag]

There is a customer requirement to migrate SQL database to Azure SQL managed instance and connect SQL server VM running on-prem to SQL MI as a read only replica. Is it possible to achieve technically? How can I implement it?

[#2 · Accepted solution]

@visithag 

Yes, it is possible to migrate a SQL Server database to Azure SQL Managed Instance and connect an on-premises SQL Server VM to the managed instance as a read-only replica.

To achieve this, you can follow these general steps:

1. Migrate the SQL Server database to Azure SQL Managed Instance using one of the available migration methods, such as using Azure Database Migration Service (DMS), Transactional Replication, or Backup and Restore.

2. Once the database is migrated, configure the Azure SQL Managed Instance to allow connections from the on-premises SQL Server VM. This can be done by creating a virtual network in Azure, configuring a VPN or ExpressRoute connection between the on-premises network and the Azure virtual network, and then configuring a firewall rule in the Azure SQL Managed Instance to allow traffic from the on-premises network.

3. Set up a read-only replica of the Azure SQL Managed Instance on the on-premises SQL Server VM. This can be done using SQL Server replication or Azure SQL Database Managed Instance read-scale replicas.

4. Configure the on-premises SQL Server VM to connect to the read-only replica of the Azure SQL Managed Instance.

5. Test the connection and ensure that the replication is working correctly.

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily.

[#3]

@visithag 

Yes, how about using read-only replicas?

Read queries on replicas - Azure SQL Database & SQL Managed Instance | Microsoft Learn

[#4]

Yes, it is possible to migrate an SQL database to Azure SQL Managed Instance (MI) and connect an SQL Server virtual machine (VM) running on-premises to the SQL MI as a read-only replica.Configure the SQL MI: Once the migration is complete, configure the necessary settings in the Azure SQL MI. Set up security, networking, and any other required configurations. Ensure that the SQL MI is accessible from the on-premises SQL Server VM.