Microsoft SQL Server Brasil: junho 2020

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.