SQLCMD.exe -S <your availability group listener> -D <availability group database> -E -K ReadOnly
select @@servername
go
select @@servername
go
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.
No comments:
Post a Comment