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 ficou claro o ambiente do AlwaysOn da demonstração, 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:

• 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.

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