sábado, 13 de junho de 2020

SQL Server AlwaysOn Configurando Replica Somente Leitura


O AlwaysOn possibilita configurar acesso somente leitura para réplicas, com redirecionamento automático a partir do acesso via  Listener. Neste post vou mostrar como configurar as rotas de acesso somente leitura de réplicas, além da configuração necessária na aplicação cliente.

O ambiente que utilizei como exemplo possui 3 servidores:

Windows 2019 Server Domain Controler: SRVWIN2019-DC
Réplica 1 SQL Server 2019: SRV2019-SQL1
Réplica 2 SQL Server 2019: SRV2019-SQL2

O acesso é feito através do Listener SRVSQL-AGL ou o IP 192.168.1.216.




A réplica SRV2019-SQL1 é o primário e a réplica SRV2019-SQL2 o secundário somente leitura, ambos utilizando o modo síncrono.

A conexão de teste foi estabelecida através do Listener, sendo direcionada para a réplica SRV2019-SQL1 (primário).


Agora que ficando claro o ambiente, vamos iniciar a configuração da conexão somente leitura redirecionada para réplica secundária.


1) Configurando a Réplica como Leitura

O padrão é a réplica não aceitar conexão quando desempenha o papel de secundária, para aceitar conexões de consulta precisamos escolher uma das duas opções abaixo:

Read-intent only – não aceita conexão direta, apenas aquelas redirecionadas via Listener.
Yes – aceita conexão direta e redirecionada.

Este ajuste pode ser feito na interface gráfica, acessando as propriedades do Availability Group, ou por T-SQL:

ALTER AVAILABILITY GROUP [SRVSQL-AG1]
MODIFY REPLICA ON N'SRV2019-SQL2' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY))


2) URL de Redirecionamento

Cada réplica somente leitura precisa ter uma URL de redirecionamento, que pode ser definida pelo comando ALTER AVAILABILITY GROUP:

ALTER AVAILABILITY GROUP [SRVSQL-AG1] 
MODIFY REPLICA ON N'SRV2019-SQL2' WITH  
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SRV2019-SQL2:1433'))

ALTER AVAILABILITY GROUP [SRVSQL-AG1] 
MODIFY REPLICA ON N'SRV2019-SQL1' WITH  
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SRV2019-SQL1:1433'))


Veja nas propriedades do Availability Group as URLs definidas.


Podemos obter a mesma informação com a consulta abaixo.

SELECT replica_server_name as SrvReplica,
read_only_routing_url as ReplicaURL,
secondary_role_allow_connections_desc as ReplicaTipo
FROM sys.availability_replicas

ORDER BY SrvReplica



3) Criando Rotas
Terceira e última configuração necessária é definir as rotas, no caso de ser necessário um redirecionamento para conexões somente leitura. 

No comando abaixo estamos definindo o acesso somente leitura para a réplica SRV2019-SQL2 no caso de o primário ser a réplica SRV2019-SQL1.

ALTER AVAILABILITY GROUP [SRVSQL-AG1]
MODIFY REPLICA ON N'SRV2019-SQL1' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SRV2019-SQL2')))


Devemos fazer o inverso também, no caso da réplica SRV2019-SQL2 virar o primário.

ALTER AVAILABILITY GROUP [SRVSQL-AG1]
MODIFY REPLICA ON N'SRV2019-SQL2' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SRV2019-SQL1')))

Veja agora nas propriedades do Availability Group a configuração de rota feita.


Podemos obter a mesma informação com a consulta abaixo.

SELECT b.replica_server_name as OrigemProducao,
c.replica_server_name as DestinoReplica,
c.read_only_routing_url as RotaURL,
a.routing_priority as Prioridade

FROM sys.availability_read_only_routing_lists a
JOIN sys.availability_replicas b ON a.replica_id = b.replica_id
JOIN sys.availability_replicas c ON a.read_only_replica_id = c.replica_id
JOIN sys.availability_groups d ON d.group_id = b.group_id
ORDER BY OrigemProducao



Configurando o Acesso Somente Leitura no Cliente

Com a infraestrutura toda pronta no Availability Group, basta utilizar a propriedade somente leitura no cliente, vamos testar três cenários: SQLCMD, SQL Management Studio e Reporting Service.

Para ocorrer o redirecionamento a conexão deve ser feita direto em um dos bancos de dados que pertencem ao Availability Group, no meu ambiente será o banco de dados VendasDB.

SQLCMD
Abra uma janela de prompt de comando SQLCMD utilizando “-d” para definir o banco de dados de conexão e “-k readonly” para especificar o tipo de conexão somente leitura.


SQLCMD -S SRVSQL-AGL -d VendasDB -U Teste -P Teste -K ReadOnly

Repare que a conexão foi feita no Listener e mesmo sendo a réplica SRV2019-SQL1 a primária, a conexão foi direcionada para réplica secundária somente leitura SRV2019-SQL2.

Basta remover a cláusula “-d” onde selecionei um banco de dados do Availability Group, que a conexão será estabelecida no banco de sistema MASTER, e este como não faz parte do Availability Group, a conexão permanece na réplica primária.


No SQL Server Management Studio devemos utilizar a cláusula “ApplicationIntent=ReadOnly”, para utilizar a infraestrutura de redirecionamento do AlwaysOn.


Selecionando um banco do Availability Group na conexão.


Veja que apesar da réplica primária ser SRV2019-SQL1 conectamos no SRV2019-SQL2.


No Reporting Services utilizamos a cláusula “ApplicationIntent=ReadOnly” nas propriedades do Data Source.



Se o seu Reporting Service é 2014 ou anterior, terá que atualizar .Net Framework 3.5 para SP1.


Desculpe o post ser um pouco grande, muita tela para mostrar com clareza a configuração.

Saudações Tricolores,
Landry

Nenhum comentário: