How to query readonly replica in Always On cluster
After some help and hints (especially thanks to Andriy M) I've managed to understand what I actually want and how to do it. This article from Microsoft helped as well: https://technet.microsoft.com/en-us/library/hh403414.aspx
First I had to create a read only listener. And then at the other server add linked server like this (borrowed from: https://stackoverflow.com/a/19585304):
USE [master] GO EXEC master.dbo.sp_addlinkedserver @server = N'cluster-readonly', -- just a name @srvproduct = N'SQL', -- anything except 'SQL Server' @provider = N'SQLNCLI11', @datasrc = N'ClusterListener', -- name of the readonly listener @provstr = N'ApplicationIntent=ReadOnly', @catalog = N'AnyActualDataBase' -- system database like 'master' is no good GO
How do I check, where am I:
SELECT * FROM OPENQUERY([cluster-readonly], 'SELECT @@SERVERNAME')
Here's some trick - if I don't specify @catalog
, then connection will be to the PRIMARY node for some reasons.
SQL Server Management Studio supports adding items to the connection string when connecting to a server through the "Connect to Server" dialog box:
Adding ApplicationIntent=ReadOnly;
to the 'Additional Connection Parameters' box (as shown in the above image) should accomplish what you need.
You may also need to add Database='YourDatabase';
if your default database is not part of an Availability Group. If you don't add the Database='YourDatabase';
part, you may still connect to the primary host, since the default master database is not part of your Availability Group.