Olá Pessoal!
Vou compartilhar com vocês uma alteração que fiz nos scripts do Ola Hallegren para atender a um cenário comum para quem quer conciliar esses ótimos scripts de manutenção junto a solução do Always On.
Como é de conhecimento de vocês, o Always On permite efetuar backups nas réplicas secundárias dentro de um grupo de disponibilidade, proporcionando uma ótima maneira de aliviar a carga dos backups em cima da réplica primária. Para efetuar os backups nas réplicas segundarias deve-se lembrar das restrições existentes, como por exemplo, o backup Full só pode ser feito com a opção de Copy Only e backups diferencias não são permitidos.
Levando em conta essas restrições, nem sempre é possível utilizar dessa estratégia de backup para diminuir a carga, sendo assim, nos cenários comuns a réplica primária irá concentrar a rotina de backup. Para poder adequar a rotina do Ola Hallegren fiz uma modificação para validação da réplica primária, com o objetivo de garantir que o backup sempre será feito nela, mesmo após um failover a rotina continuará funcionando de forma automatizada, pois os Jobs estarão ativos em todas as instâncias.
A função abaixo deve ser criada para através das DMVs identificar qual é a réplica principal em determinado Availability Group:
CREATE FUNCTION [dbo].[fn_hadr_group_is_primary] (@AGName sysname)
RETURNS bit
AS
BEGIN
DECLARE @PrimaryReplica sysname;
SELECT @PrimaryReplica = hags.primary_replica
FROM
sys.dm_hadr_availability_group_states hags
INNER JOIN sys.availability_groups ag ON ag.group_id = hags.group_id
WHERE
ag.name = @AGName;
IF UPPER(@PrimaryReplica) = UPPER(@@SERVERNAME)
RETURN 1; -- primary
RETURN 0; -- not primary
END;
GO
Em seguida, a consulta de validação deve ser inserida junto ao código gerado nos scripts de backup do Ola Hallegren, seguindo o exemplo abaixo:
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "IF master.dbo.fn_hadr_group_is_primary('AG_Demo') = 1 BEGIN EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'\\10.1.1.1\backup_mssql$', @BackupType = 'FULL', @Verify = 'Y', @Compress = 'Y', @CleanupTime = 24, @CheckSum = 'Y', @LogToTable = 'Y'; END ELSE BEGIN RETURN; END" -b
Dessa forma os Jobs com as rotinas de backup podem estar ativos em todas as réplicas, pois apenas a primária realmente irá executar os comandos para o backup. Claro que essa alteração também pode ser feita para os demais scripts do Ola Hallegren, como o de manutenção dos índices.
Um detalhe que deve ser tratado, é nos casos em que existe mais de um grupo de disponibilidade dentro da mesma instância, dessa forma, deve ser criado os scripts de backup específicos para cada grupo, permitindo autonomia em suas execuções e em casos de failover.
Os scripts acima foram obtidos originalmente no SQLMAG: http://sqlmag.com/blog/alwayson-availability-groups-and-sql-server-jobs-part-7-detecting-primary-replica-ownership
Até a próxima!
Bruno Silva 