SQL Server: Replicando usuários e permissões no AlwaysOn Availibity Group (AG)

Fala galera, nesse post venho Escrever um pouco sobre usuários e permissões envolvendo a alta disponibilidade conhecida como AlwaysOn Availibity Group (AG) no SQL Server.

Para continuar o artigo você já precisa ter um pouco de conceito formado sobre alta disponibilidade, porém vou tentar explicar uma pequena base.

Para quem não conhece, a alta disponibilidade no SQL Server faz com que seu serviço de banco de dados continue operante mesmo após eventuais problemas que possam vir deixar o seu serviço inoperante, exemplo: Disco, memória, CPU e etc. Esse tipo de arquitetura é muito utilizado para garantir que qualquer indisponibilidade possa ser mínima afim de evitar prejuízos nos negócios.

O SQL Server oferece boas arquiteturas de alta disponibilidade e redundâncias como AlwaysOn Failover Cluster Instances, AlwaysOn Availability group, Database Mirroring e Log Shipping. Irei contribuir com  um artigo sobre a arquitetura de cada um futuramente. Entretanto, hoje venho falar um pouco sobre o funcionamento dos usuários e permissões no AlwaysOn Availability group.

Resumidamente, o Availability group permite que os bancos que você deseja sincronizar no “grupo de disponibilidade” sejam “replicados” a todo momento para um outro servidor de banco de dados SQL Server , fazendo com que os dados e os bancos de dados fiquem o tempo todo disponíveis em um eventual problema no cluster, ou seja, você possui dois ou mais servidores dentro de um grupo de disponibilidade e um listener que aponta para o nó primário.

Você pode continuar entendendo melhor esse assunto nos seguintes links:

• Documentação oficial – Microsoft
• Artigo sobre Arquitetura de AlwaysOn AG.

Cenário:

Voltando ao assunto original, essa arquitetura tem um pequeno impasse em relação aos usuários e suas devidas permissões. O cenário é o seguinte, você tem um grupo de disponibilidade com dois servidores de banco de dados por exemplo:

Listener AlwaysOn AG

Percebam na imagem acima que o nó primário é o servidor: [SQLSERVER1] e o secundário: [SQLSERVER2].

Quando você cria um usuário novo e dá permissão em algum banco de dados, você está fazendo isso no servidor primário. Infelizmente o SQL Server não consegue replicar esses usuários e permissões para o segundo nó, ou seja, o servidor secundário.

Tudo bem, até ai nenhum problema não é mesmo? Mas o que acontece se ocorrer o failover no seu ambiente de alta disponibilidade? Consequentemente, os serviços passarão para o segundo servidor que agora se tornará primário.

E agora? Cadê os usuários e permissões por exemplo? Ele acabou não replicando os usuários e nem suas permissões. Suponhamos que há 30 usuários, você irá criar todos um por um? Imagina ter que fazer tudo manualmente.

O que fazer?

Eu Tenho uma ideia para evitar todo esse trabalho manual. Todos os usuários no SQL Server possuem um endereço, chamado “SID”, conforme imagem abaixo:

SID do usuário no SQL Server

Qual a solução em primeiro momento? Criar esse mesmo usuário no servidor secundário com o mesmo “SID” e assim ele herdará as mesmas permissões do usuário que foi criado no servidor primário. Legal né? Porém, ficar fazendo isso de forma manual é meio complicado com tanta coisa pra gerenciar.

Será que existe uma forma de automatizar esse processo?

Solução:

Depois de pesquisar muito e ver diversas soluções espalhadas por ai, finalmente encontrei algo que poderia melhorar e facilitar minha rotina diária em relação aos usuários e suas devidas permissões.

Encontrei uma procedure  que ajudou muito, você pode verificar nesse link:

• https://github.com/strdco/ag-sync

Após realizar o download da procedure SyncLogins vamos aos passos:

1. Abra a procedure SyncLogins e se conecte ao nó secundário, no meu caso: [SQLSERVER2\SQLSERVER2] e execute.

Conectando no segundo nó AG.

2. No servidor secundário, é necessário criar um linked server  para que a rotina funcione da forma correta, se você não sabe como criar um linked server, veja esse link:  Como Criar um linked server.

3. Após criar o linked server , crie a procedure no servidor secundário [SQLSERVER2\SQLSERVER2].

Linkedserver servidor secundário

4. Após criação da procedure, execute no servidor secundário com os seguintes parâmetros:

EXECUTE dbo.SyncLogins
@primary_replica, -- {name of SOURCE linked server}
@allow_drop_logins, -- 1= Drop login caso já exista, e recria novamente.
@print_only, -- 1= Exibe antes no resultados o que script que será executado, mas não aplica.
@check_policy -- 1= Checa se a política de força da senha é atendida.
@exclude_logins -- 1= Exceções de login para não ser sincronizado. Example: 'abc,def,DOMAIN\EsseLoginNao'

5. A Procedure SyncLogins  sempre será executada do servidor secundário [SQLSERVER2\SQLSERVER2]

Laboratório:

Após criar a procedure, configurar o linked server, vamos aplicar e ver se realmente vai funcionar.

1.  No servidor primário, crie um login qualquer e dê permissão em qualquer banco. No meu caso irei criar um usuário que é sysadmin e que também tem permissão de owner em uma base de dados chamado teste2, vejamos:

Criação de usuário nó primário

2. No servidor secundário, podemos perceber que esse usuário não foi criado.

Usuários do segundo nó, com rotina ainda não executada.

3. Voltemos ao servidor secundário e vamos executar a procedure SyncLogins e ver a mágica acontecer.

Procedure SyncLogins

Perceba na imagem acima que com o parâmetro @print_only = 1 conseguimos ver antes o que será executado. No Script percebemos que a procedure percorreu o servidor primário e conseguiu pegar o mesmo SID do usuário de forma automática.

5. Executando a SyncLogins com o parâmetro @print_only = 0

Execução SyncLogins

De fato agora, foi executado. Perceba que após um refresh, o usuário já foi criado no segundo nó.

6. Após isso, podemos ver que o usuário foi replicado com o mesmo SID e permissões, igualmente ao primeiro nó.

Usuário replicado no segundo nó com mesmas permissões após execução da procedure.

Conclusão:

Podemos agendar a execução da procedure SyncLogins em um JOB e pronto, rotina implementada.

Para dúvidas, sugestões ou informações, comente.