SQL Server AlwaysOn Configurando Replica Somente Leitura
- 1.Configurando AlwaysOn Availability Groups – Parte 1
- 2.Configurando AlwaysOn Availability Groups – Parte 2
- 3.Configurando AlwaysOn Availability Groups – Parte 3
- 4.Monitorando AlwaysOn
- 5.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