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

Question

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 385 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.
Saraswathi Devadula 385 Reputation points Microsoft External Staff

2025-03-13T08:43:45.72+00:00

Hello Vanhoefen, Guido

We haven't heard back from you regarding the last response. Could you let us know if the provided solution resolved the issue or if you need further assistance? We are happy to help you.

Thank you.

1 answer

Your answer

Saraswathi Devadula 385 Reputation points Microsoft External Staff

2025-03-13T08:43:45.72+00:00

Hello Vanhoefen, Guido

We haven't heard back from you regarding the last response. Could you let us know if the provided solution resolved the issue or if you need further assistance? We are happy to help you.

Thank you.

Answer 1

Vanhoefen, Guido 0

Hi @Saraswathi Devadula and thanks for your answers.

As we have the Managed Instance Link up and running, I supposed to start with the Step 3 (Create the Listener). When I try to create the Listener, there is an error message "Error creating WSFC-Ressource..." Code: 5090What I do not understand is, do I have to Create WSFC Configurations on top of the up and running Managed Instance Link?

At the end, I just want a solution to let read-only users accessing the read-only replica on the Managed Instance. The only way I was able to manage an access to the read-only is to provide EntraID Users "sysadmin" rights on the Managed Instance.

If I want to assign "dbreader"-Permissions on Database level, it fails because the Database is in "read only" mode...

Thank you for your help!

Taduturu Balaji 75 Reputation points Microsoft External Staff

2025-03-14T09:53:11.02+00:00

Hi @Vanhoefen, Guido ,

SQL Managed Instance does NOT support WSFC-based AG Listeners and MI Link is based on Distributed AGs, which work differently from traditional AGs. So, you cannot create an AG Listener for SQL MI. Try by using ApplicationIntent=ReadOnly and point directly to the SQL MI instance hostname and assign db_datareader permissions on the primary SQL Server.

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

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.
Taduturu Balaji 75 Reputation points Microsoft External Staff

2025-03-18T12:33:12.4333333+00:00

Hi @Vanhoefen, Guido ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Vanhoefen, Guido 0 Reputation points

2025-03-20T13:41:13.6733333+00:00

HI @Taduturu Balaji ,

First of all, thank you for taking the time to support me.

Unfortunately, your suggested steps did not lead to a solution. It is also not clear to me how a login created on the primary SQL should work on the secondary SQL. The logins are not synchronized.

Once again: the primary SQL server runs on a standalone IaaS Azure VM and the secondary (MI Link) on a managed instance.

In all articles on MI Link it is written that it can be used for offloading workloads, I am still looking for a way to do this and am still grateful for any advice.

Guido

Share via

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

1 answer

Your answer