Fala galera!
Tudo certo com vocês?
No post de hoje gostaria de compartilhar um novo recurso do SQL Server 2017 que é o Smart Backup e depois também foi disponibilizado no Service Pack 2 do SQL Server 2016.
Basicamente o que temos de novo é uma coluna na DMV (Dynamic Management View) sys.dm_db_file_space_usage chamada “modified_extent_page_count”. Essa coluna traz o número de páginas que foram alteradas desde o seu último backup full. Verificando essas contagens de páginas podemos deixar as nossas rotinas de backup mais inteligentes, pois o número de páginas modificadas pode ser usado para rastrear as alterações do banco de dados desde o último backup full e com isso decidir se o backup diferencial poderia ser o mais indicado nesse caso.
Normalmente utilizamos uma política de fazer um backup full no sábado ou domingo e backups diferenciais durante a semana. Utilizando essa estratégia tradicional, muitas vezes acontece de o backup diferencial de sexta-feira já estar quase do tamanho do backup full e tem casos que até maior, o que leva a um tempo maior em caso de necessidade de se fazer um restore.
Com essa nova feature podemos deixar as nossas rotinas de backup mais inteligentes validando se o percentual de páginas alteradas é 75% do total de páginas do bando de dados. Nesse caos, já valeria a pena fazer um backup full ao invés de um backup diferencial!
Vejamos na pratica como funciona:
Antes vamos realizar um backup full da nossa database para zerar o percentual de páginas alteradas.
Backup Database Northwind To disk = 'D:\SQLSERVER\BKP\NorthWind_Dados_Full.bak' with compression, format, stats=5
SELECT DB_NAME(database_id) DB_Name,
total_page_count TotalPaginas,
modified_extent_page_count TotalExtent_Modificado,
CAST((modified_extent_page_count + mixed_extent_page_count) * 1.00 / allocated_extent_page_count AS NUMERIC(18, 2)) [%_PagModificadas]
FROM sys.dm_db_file_space_usage;
Como podemos observar o %_PagModificadas está zerado.
O script abaixo faz uma validação se o percentual de páginas alteradas é maior que 75% e se o dia da semana está entre segunda-feira e quinta-feira ou se é domingo. Se essa cláusula for verdadeira, a rotina vai executar um backup full, caso contrário, executa o backup diferencial.
DECLARE @threshold_bkp_full NUMERIC(18,2), @dia_semena int
SELECT @threshold_bkp_full = CAST((modified_extent_page_count + mixed_extent_page_count) * 1.00 / allocated_extent_page_count AS NUMERIC(18, 2))
FROM sys.dm_db_file_space_usage;
SELECT @dia_semena = DATEPART(WEEKDAY,GETDATE())
-- 1 - Domingo
-- 2 - Segunda-feira
-- 3 - Terça-feira
-- 4 - Quarta-feira
-- 5 - Quinta-feira
-- 6 - Sexta-feira
-- 7 - Sábado
IF (@threshold_bkp_full > '0.75' AND @dia_semena in (2,3,4,5)) OR @dia_semena = 1
BEGIN
BACKUP DATABASE Northwind
TO DISK = 'D:\SQLSERVER\BKP\NorthWind_Dados_Full.bak'
WITH COMPRESSION,
FORMAT,
STATS = 5
PRINT 'Backup FULL'
END
ELSE
BEGIN
BACKUP DATABASE Northwind
TO DISK = 'D:\SQLSERVER\BKP\NorthWind_Dados_Dif.bak'
WITH COMPRESSION,
FORMAT,
DIFFERENTIAL,
STATS = 5
PRINT 'Backup Diferencial'
END
Sugerindo que esse backup full foi realizado em um Domingo, durante a Segunda-feira a empresa trabalhou normalmente e algumas páginas foram alteradas.
Podemos observar que na segunda-feira apenas 3% das páginas de dados foram alteradas. Com isso, a nossa rotina de backup vai realizar o backup diferencial, como é esperado.
Agora vamos simular que chegamos na Quinta-feira e já tivemos bastante alterações no banco, milhares de registros novos e milhares de update aconteceram. Com isso, ultrapassamos o nosso threshold de 75% de páginas modificadas.
Como podemos ver na Quinta-feira o nosso percentual de páginas alteradas chegou a 83%, com isso a nossa rotina vai executar um backup full.
Como foi dito na introdução do post, essa nova feature nos ajuda a otimizar a nossa estratégia de backups. O script de validação do % de páginas modificadas e dia da semana pode ser melhor customizados, no exemplo do post coloquei algo bem simples mesmo.
Bom pessoal a ideia desse post foi apresentar essa nova feature apesar de não ser novidade, pois o SQL Server 2019 já está saindo do forno e muitos ainda não conheciam.
Espero que tenham gostado.
Abraços,
Tiago Neves
Curta a minha página no Facebook.