We have an Always On "cluster" that consists of 2 servers (there will be more), so one is PRIMARY and other(s) is(are) SECONDARY. The idea was to dedicate SECONDARY as a readonly replica, so it would be a somewhat search-server.
I do know how to set ConnectionString in the C# so it will use SECONDARY replica - just add
But I don't understand how to address the SECONDARY from stored procedure if I'm using distributed query. Is there a way to set some parameters to the query (like WITH statements or something), so query would use only replica and not the PRIMARY node?
The thing is, obviously from the start server1 is PRIMARY and server2 is SECONDARY, but when server1 fails then server2 is PRIMARY and server1 will be SECONDARY after it get fixed. So I cannot just use the static server2's name.
So far I've managed to get current replica's server name as a variable and use it in EXEC:
-- find first available replica DECLARE @replicaServer nvarchar(50) SELECT TOP 1 @replicaServer = RCS.replica_server_name FROM sys.availability_groups_cluster AS AGC INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS ON RCS.group_id = AGC.group_id INNER JOIN sys.dm_hadr_availability_replica_states AS ARS ON ARS.replica_id = RCS.replica_id INNER JOIN sys.availability_group_listeners AS AGL ON AGL.group_id = ARS.group_id WHERE ARS.role_desc = 'SECONDARY' AND connected_state = 1 -- and query it DECLARE @cmd nvarchar(MAX) = 'SELECT * FROM [' + @replicaServer + '].[somebase].[someschema].[sometable]' EXEC (@cmd)
But that's a shame, I guess.