MI Link Feature implemented - How to proceed offloading read-only workloads

Vanhoefen, Guido 0

Architecture:

SQL Server 2019 on an Azure VM with the primary Replica and connected through MI Link Feature to
SQL Managed Instance (General Purpose Tier) with the secondary Replica

Now I want to point 3rd Party apps to the read-only Replica on the MI.

Question:

I was not able to find an article, how to connect to the Secondary Replica in this scenario.

If I connect using "ApplicationIntent=ReadOnly" it is not connected to the secondary Replica automatically.

Do I have to create an Availability Group Listener first? Is there a KB on how to do it within this scenario?

Thanks so much for your help!

Saraswathi Devadula 160 Reputation points Microsoft External Staff

2025-03-12T13:21:18.53+00:00
Hello Vanhoefen, Guido

I understood that you want to connect to the Azure managed instance used as a read only replica. Also, I noticed that you configured the MI link, which enables near real-time data replication between SQL Server and azure managed instance.

Please be informed that the Managed Instance link uses distributed availability groups to extend your data estate in a safe and secure manner, replicating data in near real-time from SQL Server hosted anywhere to Azure SQL Managed Instance, or from Azure SQL Managed Instance to SQL Server hosted anywhere.

Kindly request you to please create the availability group listener by following the below steps,

Step 1: Configure Windows Server Failover Clustering (WSFC)

Step 2: Enable Always on Availability Groups in SSMS by selecting the server properties and restart the server.

Step 3: Create the Availability Group for the primary replica and thereby you can create an Availability Group Listener by specifying the listener's name and IP address.

Note: If you have the existing Availability group, then you can add the listener by right clicking on the group.

Step 4: Join Secondary Replicas by connecting to the SQL MI. Right-click on the Availability Group created and select "Join."

Also, you can create the Availability group listener using T-SQL query, as shown below

ALTER AVAILABILITY GROUP [YourAvailabilityGroupName] ADD LISTENER 'YourListenerName' (WITH IP ((YourIPAddress)), PORT = 1433);

Once the listener is created, you can use it in your connection strings. The connection string would look something like this:

Server=YourListenerName;Database=your_database;User ID=your_user;Password=your_password;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;ApplicationIntent=ReadOnly;

Testing the Connection:

After setting up the listener and updating your connection strings, test the connection from your third-party applications to ensure they can connect to the secondary replica. I kindly request you to please refer the below documents for better understanding,
https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/managed-instance-link-configure-how-to-ssms?view=azuresql

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-distributed-availability-groups?view=sql-server-ver16&tabs=automatic%2Csql22

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-access-on-an-availability-replica-sql-server?view=sql-server-ver16&source=recommendations

I hope this information helps. Please do let us know if you have any further queries.

Share via

MI Link Feature implemented - How to proceed offloading read-only workloads

Your answer