Temos uma situação única em que queremos permitir que os usuários consultem uma réplica secundária legível de um banco de dados com SSMS para relatórios ad hoc, mas não permitir que eles leiam da réplica primária. Configuramos o roteamento somente leitura para fazer isso. Isso também está tudo no SQL 2016.
Meu pensamento inicial foi criar o logon nas réplicas primária e secundária e conceder acesso de leitura ao banco de dados em questão. Em seguida, DENY CONNECT ou desabilitamos o login na réplica primária atual. No SSMS, os usuários podem se conectar ao Ouvinte com ApplicationIntent=ReadOnly e ser roteados para a réplica secundária sem nunca tocar na primária.
Configuramos um trabalho simples nos servidores de réplica primário e secundário com lógica básica: IF current server = primary then disable login; se o servidor atual = secundário, ative o login.
O problema é que estou recebendo falhas de login ao conectar ao listener com intenção somente leitura quando o login está desabilitado no servidor primário. Quando eu reabilito o logon na réplica primária, ele funciona bem e a conexão é roteada corretamente para o secundário legível.
Eu configuro um rastreamento no servidor primário e, com certeza, posso ver o login conectar e executar algumas consultas de tipo de sistema em master e msdb na réplica primária - mesmo que eu esteja conectando com ApplicationIntent=ReadOnly no SSMS. Não tenho certeza se isso é algo que o SSMS faz nos bastidores ou se é o comportamento padrão de um logon passando pelo processo de roteamento somente leitura.
Aqui estão as consultas que capturei com o rastreamento do criador de perfil rápido no primário:
--msdb query
select
case
when object_id('dbo.sysdac_instances') is not null then 1
else
0
end
--master query
SELECT
dtb.name AS [Name],
dtb.database_id AS [ID],
CAST(has_dbaccess(dtb.name) AS bit) AS [IsAccessible]
FROM
master.sys.databases AS dtb
ORDER BY
[Name] ASC
Alguém já teve que lidar com essa situação antes? Parece que basicamente precisamos permitir uma permissão de conexão de login na réplica primária enquanto negamos acesso de leitura ao banco de dados no AG no primário, mas concedemos a essa permissão de login para ler o banco de dados na réplica secundária legível.
A outra alternativa é criar uma entrada DNS que aponte diretamente para a réplica secundária, mas não podemos garantir que a réplica SEMPRE seja a secundária, pois pode ocorrer um failover.
