AlwaysOn – Como configurar leitura, backup e checkdb na réplica secundária
Hey guys tudo certo?
No post de hoje vou compartilhar um pouco mais sobre o AlwaysOn.
Nos dois últimos posts mostrei como realizar a configuração do AlwaysOn no SQL Server e como podemos utilizar o Snapshot para fazer leitura na réplica.
Só que no post anterior “Leitura na Réplica do AlwaysOn com SQL Standard? Isso é possível? – Parte II”, a configuração foi realizada em um SQL Server Standard. Agora vamos fazer em um SQL Server Developer, que tem as mesmas configurações do SQL Server Enterprise, ou seja, não temos limitações das features. Então, agora podemos criar um grupo de disponibilidade com mais de uma base, realizar leitura na réplica, executar o checkdb e backups.
IMPORTANTE – LICENCIAMENTO!!!
Lembrando que dependendo do seu contrato você necessita licenciar os 2 nós para poder utilizar essas funcionalidades na réplica. CUIDADO!!!
Configurando o AlwaysOn no SQL Server Enterprise
A parte de configuração do Cluster é a mesma, nada muda, então você pode ver como fazer a base da configuração no post “Leitura na Réplica do AlwaysOn com SQL Standard? Isso é possível? – Parte I”. O que muda é somente a configuração do AlwaysOn.
Embora não seja obrigatório, eu gosto de fazer o restore das bases antes na réplica e dar somente o join no AlwaysOn, mas isso fica a gosto do freguês.
Normalmente, eu faço da seguinte forma:
- Bases Pequenas: Backups Full + Log. Nesse caso, o Backup Full é rápido.
- Bases Grandes: Backups Full + Diferencial + Log. Nesse caso, são bases maiores. Com isso, podemos deixar o Backup Full sendo restaurado com mais antecedência e no momento da configuração restauramos o Diferencial + Log e conseguimos agilizar o processo.
Entretanto, isso fica a gosto do freguês. =)
Vamos aos passos!
Antes de começar, vamos identificar os servidores:
- SQL22 será o servidor primário;
- SQL23 será o servidor secundário.
- O primeiro passo é realizar um backup full dos bancos no servidor primário para realizar o restore no servidor secundário.
backup database AdventureWorks
to disk = '\\sql22\BKP\AdventureWorks_dados.bak'
with format,stats=1
backup database Stackoverflow
to disk = '\\sql22\BKP\Stackoverflow_dados.bak'
with format,stats=1
2) Após o backup ser realizado, vamos executar o restore das bases no servidor secundário.
IMPORTANTE – Devemos utilizar a opção NORECOVERY para conseguir restaurar os outros backups posteriormente.
restore database AdventureWorks
from disk = '\\sql22\BKP\FULL\AdventureWorks_dados.bak'
with move 'AdventureWorks2017' to 'C:\MSSQLSERVER\DATA\AdventureWorks2017.mdf',
move 'AdventureWorks2017_log' to 'C:\MSSQLSERVER\LOG\AdventureWorks2017_log.ldf',
NORECOVERY
restore database Stackoverflow
from disk = '\\sql22\BKP\Stackoverflow_dados.bak'
with move 'SUPERUSER' to 'C:\MSSQLSERVER\Data\Stackoverflow.mdf',
move 'SUPERUSER_log' to 'C:\MSSQLSERVER\Log\Stackoverflow_log.ldf',
NORECOVERY
3) Agora vamos fazer a configuração do AlwaysOn. Se você tiver muitas transações no momento que for realizar a configuração, pode ser que você necessite fazer um backup de log no servidor primário e fazer o restore no servidor secundário.
Para realizar a configuração do AlwaysOn, no servidor primário vamos na guia “Always On Availability” à “New Availability Group”
4) Vamos ter que dar um nome ao grupo de disponibilidade, no caso vou utilizar o “AG2017”. Logo após vamos informar quais databases irão fazer parte do grupo de disponibilidade e informar qual servidor será configurado a réplica, no caso vou utilizar o servidor secundário “SQL23”.
5) Agora vamos informar se vamos realizar leitura na réplica. Para fazer isso, na opção “Readable Secondary” devemos setar o valor “Read-intent only”.
Feito isso, o nosso Grupo de Disponibilidade está configurado! WOW!!!
Para realizar a leitura na réplica, precisamos adicionar o parâmetro “ApplicationIntent=ReadOnly” no “SQL Server Management Studio” e na string de conexão das aplicações.
Após isso, é possível fazer a leitura na réplica secundária!!!
No teste abaixo, vou executar uma consulta na réplica secundária. Fiz uns “cross apply” para fazer uma consulta um pouco mais demorada. Dessa forma, vou conseguir tirar um print com a sp_whoisactive para fazer a demonstração.
select *
from AdventureWorks.HumanResources.Employee
cross apply AdventureWorks.HumanResources.Department
cross apply sys.objects
“Ok Tiago, mas tem algo a mais que podemos fazer na réplica secundária?”
Também podemos utilizar a réplica secundária para realizar outras atividades, por exemplo: CHECKDB e BACKUP.
Para configurar o SQL Server para fazer o BACKUP na réplica secundária, você tem que alterar a configuração do AlwaysOn.
- Prefer Secondary – O Backup sempre que possível será realizado na réplica secundária, mas se ela não estiver disponível o backup será realizado na réplica primaria.
- Secondary Only – O backup será sempre realizado a réplica secundária.
- Primary – O backup será sempre realizado na réplica primária.
- Any Replica – O backup será realizado em qualquer uma das réplicas.
“Tiago posso fazer todos os meus backups na réplica secundária?”
A resposta é NÃO! Você não consegue fazer um backup diferencial na réplica, pelo menos até a data de hoje (20/05/2020). Vamos ver mais sobre isso daqui a pouco.
“Tipos de backups suportados na réplica secundária”
Na réplica secundária podemos fazer basicamente somente o backup de log, pois não é suportado fazer o backup full na réplica secundária, ou melhor, você até pode fazer, mas seria o backup full com a opção COPY_ONLY.
Não é possível fazer backup diferencial na réplica secundária.
É possível fazer somente o backup de log na réplica secundária.
Então podemos criar nossa política de backup utilizando tanto a réplica primária para fazer os backups full e diferencial e utilizar a réplica secundária para fazer os backups de log.
Pontos de atenção:
Novamente fica como ponto de atenção a questão de LICENCIAMENTO.
Se você utiliza o Plano de Manutenção para fazer os backups do seu banco de dados, se atente para algumas pegadinhas:
- Se você configurou que a preferência de backup é somente na réplica secundária, o SQL Server ao executar o job de Backup FULL não vai efetuar o backup, a não ser que você marque a opção no plano de manutenção para ignorar a preferência.
Veja no exemplo abaixo:
Como já foi dito no início do post, a minha réplica primaria é o servidor SQL22.
E configurei o meu AG para fazer o backup somente na réplica secundária.
Ao criar um Plano de Manutenção para fazer um Backup FULL, é necessário se atentar em marcar a seguinte opção:
“For availability databases, ignore replica priority for backup and backup on primary settings”,
Se você NÃO habilitar essa opção, o JOB será executado com sucesso, porém o backup NÃO será realizado.
Uma vez marcada a opção, o SQL Server irá ignorar a prioridade e efetuará o backup full na réplica primária.
Este problema ocorre somente quando utilizamos plano de manutenção. Se você faz backup utilizando scripts, você não terá esse problema. =)
Para o backup de log, se você for realizá-lo na réplica secundária você vai ter que configurar um job para executar na réplica secundária (no caso do post o SQL23).
Para mais informações sobre backup no AlwaysOn:
CHECKDB na réplica secundária
Como foi dito anteriormente, podemos executar a rotina de CHECKDB na réplica secundária sem nenhum problema. Além disso, ela nem precisa estar com a opção do readonly habilitada.
dbcc checkdb('AdventureWorks')
Bom pessoal é isso, com esse post finalizamos a série de como fazer a Leitura na Réplica do AlwaysOn.
Recomendo também que façam o CURSO GRATUITO “Módulo 01: HADR – Início da Jornada” da Power Tuning com instrutores feras demais nesse assunto!
https://cursos.powertuning.com.br/course?courseid=mdulo-01-hadr-incio-da-jornada
Espero que tenham gostado e aprendido algo também! =)
Um abraço,
Tiago Neves
About The Author
