Tuesday, August 15, 2017

Confirm readonly rougintg using SQLCMD

SQLCMD.exe -S <your availability group listener> -D <availability group database> -E -K ReadOnly

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