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.
Isso é exatamente certo. O login que está sendo usado para conectar deve ser capaz de se conectar ao primário.
Ao usar
ApplicationIntent=ReadOnlyem uma cadeia de conexão que aponta para um ouvinte AG, o driver se conecta inicialmente à instância primária. Isso é para que o SQL Server possa revisar a lista de réplicas e ver se há réplicas disponíveis e legíveis online para enviar a você. Isso está documentado aqui:Intenção de aplicativo somente leitura e roteamento somente leitura
Há também um bom resumo do processo apresentado pelo Top Microsoft Man Sean Gallardy no blog da PFE aqui:
Encontrando quais conexões foram roteadas somente leitura
Em relação ao outro item que você observou na pergunta:
As permissões de nível de banco de dados serão replicadas no AG - portanto, se você remover o acesso de leitura do usuário no primário, ele também perderá o acesso de leitura no secundário.
Estou um pouco confuso sobre o objetivo de tal empreendimento, já que os dados deveriam, em teoria, ser os mesmos (ou próximos dos mesmos). Talvez você simplesmente não queira certos logins executando consultas no primário por motivos de desempenho. Expressar a intenção somente leitura e conectar-se ao ouvinte é realmente a abordagem mais direta para resolver isso.
Outras opções envolveriam hardware separado ou codificação personalizada em seu aplicativo que lida com a conexão direta à réplica correta (e lógica de repetição se ocorrer um failover e trabalhos agendados em cada réplica para desabilitar/habilitar os logins no caso de um failover , etc).
Eu sei que este é um tópico um pouco antigo, mas na verdade temos exatamente os mesmos requisitos - Permitir que os usuários se conectem com intenção somente leitura e impedir que eles voltem ao primário.
A razão é simples - Nossos bancos de dados são complexos com muitas pequenas consultas constantemente sendo executadas em nossos sistemas de produção e nossos usuários normalmente executam consultas de relatórios pesados que exigem muitas junções e bloqueios subsequentes. Não podemos, sob nenhuma circunstância, fazer com que os usuários executem esses relatórios nas réplicas primárias devido ao impacto nos sistemas de produção. É reconhecidamente extremamente improvável que eles voltem ao primário, mas é possível que isso ocorra e é isso que desejamos evitar.
Também é possível que os usuários evitem o ouvinte e se conectem diretamente à réplica primária, o que obviamente também precisamos evitar.
Acho que, por enquanto, a melhor solução é habilitar o RCSI nos bancos de dados (já pagamos o custo do versionamento de linha devido ao secundário síncrono), portanto, se um fallback de uma consulta de relatório atingir o primário, pelo menos não bloqueará ou deadlock contra consultas de produção. Não é o ideal, pois ainda temos o custo do recurso (temp db, memória, cpu, etc).
Se alguém tiver alguma outra idéia, eu adoraria ouvi-los.
Uma sugestão é o seu plano conforme descrito, mas combinado com o Administrador de Recursos para manter alguns dos outros custos de recursos sob controle.
Como alternativa, suponha que não seja muito prejudicial ao aplicativo. Nesse caso, você pode usar um gatilho de login para impedir que o relatório seja executado ou ter um trabalho executado com frequência que elimine o SPID associado ao relatório (você pode aliviar seu chefe tornando o trabalho baseado em limite).
Além disso, um gatilho de login funcionaria, mas esteja ciente do risco de se bloquear do primário. Revise completamente e teste, teste, teste, para que você não fique tentando lembrar como inicializar no modo mínimo como administrador às 3 da manhã.
Fizemos um pequeno brainstorming aqui, e não há como evitar alterações de configuração em execução no primário que conhecemos.
Eu também consideraria sair do SQL Server para este e examinar os balanceadores de carga de aplicativos. F5 se houver um orçamento, o HAProxy funcionou bem para mim no passado usando a edição da comunidade de código aberto.
Temos esta mesma exigência. Estou curioso para saber se o uso de um gatilho de logon funcionaria?
Crie uma maneira de identificar quais usos são usuários "Somente leitura": conceda a eles uma função específica para "Somente leitura"
Durante o evento de logon, se o usuário estiver nesta tabela e você estiver na reversão da réplica primária.
Como acima teste muito bem antes de tentar em produção.
Algumas abordagens que você pode tentar.
Conecte os usuários diretamente ao secundário sem usar o AG Listener. Uma variante sofisticada disso usa um endpoint IP de cluster adicional com proprietário preferencial no nó secundário. Isso permitiria que o endpoint IP fizesse failover para o primário se o secundário estivesse inativo.
Não conceda permissões SELECT em nível de banco de dados aos usuários somente leitura e conceda as seguintes permissões em nível de servidor no secundário: CONNECT ANY DATABASE e SELECT ALL USER SECURABLES.