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.
Agora que ficando claro o ambiente, vamos iniciar a
configuração da conexão somente leitura redirecionada para réplica secundária.
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).
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
