É possível estimar ganho de compressão de backup?

A imagem é um trocadilho

Olá,

É possível determinar o tamanho de uma backup comprimido com WITH COMPRESSION?

Observação: Recomendo a leitura do  post sobre backup compression aqui do blog além da documentação oficial, para melhor aproveitamento do assunto deste post.

 

Essa é a resposta do BOL que você pode conferir aqui:

For compressed backups, the size of the final backup file depends on how compressible the data is, and this is unknown before the backup operation finishes

Quando este questionamento foi feito a mim, o banco de dados  em questão nunca teve backup, não tinha valor algum no MSDB.DBO.BACKUPSET pra sequer dar uma ideia de qual seria o tamanho do .bak e além disso espaço em disco, nesta situação em específico, também era um problema. E ganho de compressão é aquela coisa: cada caso é um caso, não tem números mágicos pra multiplicar ou dividir e chegar nesta resposta. Quando qualquer backup com compressão já foi realizado, é possível obter o que é chamado de “Compression Rate”, dividindo o tamanho da base pelo tamanho da base comprimida. O Compression rate ajuda pra ter uma boa ideia de quanto será comprimido.  A fórmula básica é:

Mas bem, se não dá pra estimar qual será o tamanho do backup comprimido se nenhum backup foi realizado, daria certo criar um  um  backup “fake” pra obter informações na backupset? Entenda-se por backup “fake” no contexto deste post aquele backup que é realizado normalmente, logado no msdb mas nada é gravado e persistido em disco. Trata-se do backup enviado para o NUL device, assunto já comentando e que você pode conferir  neste post.

IT’S DEMO TIME!

Tudo foi testado em uma instância 2008 R2, embora possa ser testado em qualquer versão 2008+ que possua a feature de compressão de backup.

Tenho uma base chamada DB_BESTIARIO que possui 484.37 MB.

sp_helpdb na base

Trata-se de um repositório e que no momento do teste estava sem atividade alguma, o que é perfeito para este exemplo diga-se de passagem. Esse banco de dados não tem nenhum backup realizado (Veja o resultset vazio).

Não retorna nenhum resultado

Não retorna nenhum resultado

Agora, vamos tirar um backup FULL normal, sem compressão e conferir o que foi logado na backupset:

comp3

Nada demais. O tamanho do campo compressed backup size é exatamente o mesmo do backup_Size se não for utilizado compressão. Apenas com esta informação, não consigo estimar o tamanho do backup comprimido.

Então, vamos tirar um backup apontando pro NUL:

Comp4

Note que o tamanho do backup feito pra NUL é o mesmo do backup realizado fisicamente em disco. Estes tamanhos conferem com a realidade do primeiro backup tirado?

comp5

.. 151.636 KB equivale à 148MB. Agora, vamos tirar um backup COM compressão e conferir qual tamanho o SQL Server loga no backupset:

comp6

Aqui está a observação principal do teste. Fazendo um backup “fake”, dá pra estimar que o backup comprimido é três vezes menor que o tamanho do backup sem compressão. Mas nem por isso existe a certeza de que o ganho do comprimido x original será sempre 3x menor pra todo backup daquela base, embora isso seja favorável para praticamente todos os cenários. Através deste teste, é possível estimar o tamanho do backup sem efetivamente gerar algum arquivo de saída.

Só pra fechar o teste, vamos tirar um backup comprimido para o disco (ou seja, vai gerar arquivo persistido em disco) pra ver se vai dar esses 45.97MB de fato. Só pra validar se não foi viagem confiar no tamanho que o backup pro NUL mostrou.

comp7

 

 

Mais uma prova cabal do backup comprimido em disco:

comp8

IMPORTANTE (!)

– O backup é “fake” mas o processo de leitura em disco é real, ou seja, há processamento e leitura durante a criação do backup. A única coisa que muda é o destino do arquivo. Não existe essa do backup ser fictício e não consumir recursos do servidor (no caso, disco, memória e CPU).
– Use com cautela e saiba em quais situações aplicar. Por exemplo, se a base em questão tiver backups diferenciais, se você fizer um backup full desta forma sem COPY_ONLY, haverá uma modificação no DCM (Differential Change Mapping) e aquele backup será necessário se for necessário restaurar aquele diferencial que vem em seguida. No exemplo não usei COPY_ONLY pois se trata de uma base de testes. Se for para backup de log, a coisa é um pouco mais séria: mandar um backup de log quebra cadeia de backup de log, e nunca queira contar com um backup que não existe, é a pior coisa que tem quando o assunto é recovery.

RESUMO

Não é possível afirmar com precisão qual será o tamanho do backup com compressão a não ser que você tire um. Então a resposta para a pergunta “É possível estimar o tamanho de um backup comprimido” continua a mesma do BOL (Não, só tendo algum backup . O que apresento aqui é uma forma alternativa de obter essa informação.  Como Backup to NUL não é salvo em disco. Significa na prática que você pode estimar espaço de um backup daquela base de 2TB sem ter problemas com espaço em disco por causa do arquivo de backup. Foi o que usei para resolver essa questão, especificamente.

Obrigado pela sua leitura, e…I’ll be back!


Referências

Backup Compression – https://technet.microsoft.com/en-US/library/bb964719.aspx

BACKUP TO NUL – Enviando seus backups pro além

Puro void!

Puro void!

Olá,

Em vários locais (livros, blogs, etc) você pode encontrar backups que são feitos para o dispositivo NUL conforme o exemplo abaixo:

Que caminho estranho… O que é esse NUL? E o mais importante: o que aconteceu com esse backup?

A explicação curta e grossa: Você fez um backup só que não.
A explicação convencional e correta: Você acabou de fazer um backup mas jogou ele pro “nada”.

Sobre o NUL

Em poucas palavras:

– É um device bastante antigo do Sistema Operacional (device no contexto de SO em geral assume característica de arquivos especiais, então você também pode chamar NUL de arquivo) e se algo é gravado nele, o destino final é o limbo 🙂

É interessante pontuar que NUL device é equivalente ao dispositivo /dev/nul do UNIX e que é NUL e não NULL. . É um arquivo especial com nome reservado. Maiores informações nas referências do post.

Você pode confirmar que mesmo o SQL Server escrevendo no NUL, a operação de backup é realizada sem erros e é inclusive logada  nas tabelas do msdb.dbo.backupset e similares (backupfile, etc).

Qual a utilidade disto?

– Realizar benchmarking de backup (cujo alguns parâmetros são configuráveis e você pode ler sobre o assunto neste post do Edvaldo Castro), mensurar performance de leitura do mdf, etc;

– Você está testando algo que gere porém não precise de backups;

– Simular o antigo comando WITH TRUNCATE ONLY  que  foi descontinuado no SQL Server 2008 ou superior com um comportamento parecido: a maior diferença é que o backup pro limbo não quebra explicitamente a cadeia de log em teoria, já que tecnicamente o backup foi feito pelo SQL Server e entregue em algum device. Mas na prática, aquele backup enviado pro limbo furou a cadeira de log e se algum restore for realizado e precisar daquele intervalo furado, já era. E aí é reiniciar a cadeira com um backup FULL (ou Diff).

Conclusão

O uso do NUL como caminho de backup deve ser compreendido pois, como o arquivo não é persistido, não sem tem backup no final das contas. Também reforço ser de extrema importância compreender a aplicabilidade desta opção de caminho assim como suas vantagens e consequências.

[]’s


 

Referências

  1. Devices, Arquivos Especiais e outros padrões do Windows – https://msdn.microsoft.com/en-us/library/aa365247(VS.85).aspx
  2. /dev/null – http://pt.wikipedia.org/?title=/dev/null]

AlwaysOn no SQL Server 2014 e novidades lunares que talvez te interessem!

Imagem

“Esse cara pode ser um san admin futuramente” – Fonte: NASA

Boa noite!
Tá vendo aquela lua que brilha lá no céu?
A lua tecnicamente não brilha por si só, apenas reflete a luz do sol. Legal, né?
Quero compartilhar duas notícias (na verdade mais importantes do que realmente parecem) que sairam recentemente e vi na internet. Links do Olhar Digital:

Fantástico!

E o que isso tem relação com o SQL Server 2014?

Conheça o Logical Backup Device

Logical Device

Olá,

O assunto de hoje é Logical Backup Device. Para maiores informações sobre o termo “Device” (Dispositivo) de backup no SQL Server ou sobre o termo que intitula o post, dê uma olhada nas referências.

Logical Backup Device (Dispositivo Lógico de Backup), que ao longo do post vou chamar de LBD, nada mais é do que um alias (ou abstração, para os mais teóricos) de um dispositivo físico (pode ser uma fita ou disco, geralmente este último). Significa que podemos referenciar um local físico passando apenas o nome do dispositivo lógico como destino.

Como seria um backup referenciando um LBD?

Observações:

– O nome do seu Logical Backup Device deve ser único (óbvio, lol)
– View de sistema: sys.backup_devices (consulte aqui todos os LBD’s criados)
– Pra criar um logical device, é necessário pertencer à role diskadmin (ou sysadmin).

Como criar

Aquela beleza de poder escolher N formas para cumprir  determinado objetivo.

Duas formas de criar o LBD (o modo hardcore, aka Powershell, não será abordado aqui);

a – Usando a GUI do Management Studio (Modo fácil);

Criando um LBD visualmente

Vale lembrar que o arquivo informado em File não é criado em tempo de execução. Você precisa criá-lo (lembre-se: é um arquivo binário).

b – Usando T-SQL (Modo normal)

Com as permissões necessárias, basta utilizar a procedure sp_addumpdevice.

O nome não é intuitivo e pode causar um certo estranhamento, pois antigamente, o termo dump era bastante utilizado em alguns cenários de tecnologias (e ainda é, no MySQL) por ser sinônimo de backup e agora nem tanto (sp_addbackupdevice seria muito mais amigável não só de nome como também combinaria com a view e tal…)

Confira as sintaxes úteis, que são a criação, a deleção e a consulta de informações em view de sistema relacionadas aos LBDs :

Modo TSQL

– name: Nome do seu dispositivo. É o nome que vai ser apontado no destino de backup;

– type: Tape = 1 e Disk = 2

– type_desc = descrição dos tipos acima

– physical_name = O caminho FÍSICO do dispositivo

Vale lembrar (de novo) que o arquivo informado em File não é criado em tempo de execução. Você precisa criá-lo (lembre-se: é um arquivo binário).

Mas qual a utilidade disto?

O fato de  se ter uma abstração física te dá a vantagem de alterar o caminho de um dispositivo em determinado ponto (na configuração do próprio lbd), e não em scripts que o referenciem. Vamos supor que você tenha um job de backup, de uma base em específica. Se o script contiver apenas o nome do LBD e não um caminho de disco local ou SAN, se você precisar alterar o caminho físico, basta alterar o LBD.

E como usar um LBD? Mais fácil enxergar na prática:

BKP E RST

Apenas um exemplo (tirado da vida real)

Uma aplicação X frequentemente fazia backup e restore da sua base e utilizava esse recurso do LBD. O fornecedor optou por utilizar apenas um arquivo pra fazer esse processo todo de backup e restore continuamente, sempre sobrescrevendo o mesmo em cada execução. A definição do (mais adequado possível) caminho físico fica por responsabilidade do DBA.

Também é interessante utilizar esse método onde você desenvolva algum script que precise de uma localização física para realização de backup e restore e você não pode garantir um caminho definitivo (seja de rede ou local). Adivinha qual a solução? LBD é uma boa. Já vi também implementações  de LBD para estratégias de backup bem excepcionais (Backup dividido). Ou seja, é uma solução que não atende um cenário apenas, embora não sejam cenários comuns.

Qual a vantagem disto?

O comando de Backup e Restore recebem um alias, uma abstração de um caminho físico. Logo, por exemplo, se meu caminho real tiver no disco D, e eu precisar retirar esse disco, posso mapear o LBD para algum outro local, por exemplo disco E, sem prejuízo para a aplicação. Também é útil em manobras emergenciais.

Por exemplo, manobras que envolvam diferentes discos (o que inclui troca de caminhos físicos com frequência) e/ou que sofram de falta de espaço podem fazer bom uso dessa abstração.

É útil para rotinas de backup convencionais? Não considero e não recomendo de jeito nenhum. O nível de organização que se tem em realizar um backup de banco por arquivo, o que inclui também convenção de nome bem definida (20140226_AdventureWorks_Full.bak por exemplo) é muito mais interessante  para uma rotina diária do que manter mais de um backup por arquivo (e isso pode pesar MUITO quando você precisar usar um device que tenha mais de uma base, e pior, bases diferentes).

Observação importante 

Uma recomendação da própria MS e que atualmente é uma boa prática (por razões óbvias): não deixe seus backups no mesmo array de disco que seus arquivos de dados e de log (note que eu disse array de disco e não discos e isso vale também para LBDs, pois, você pode ter duas letras/discos diferentes (D: e E: por exemplo) porém são partições/discos do mesmo disco/array. Quando se pensa em desastres, pense na pior hipótese sempre, algo como: o array todo pode falhar, e se seus arquivos de backup estiverem lá, você aprenderá da pior forma possível que backup no mesmo local físico onde seus dados estão não é backup. Isso é um ponto que irei sempre voltar ao longo das postagens.

 

Qualquer dúvida ou comentário adicional, fique à vontade para comentar.

 

Referências

Backup Devices: http://technet.microsoft.com/en-us/library/ms179313.aspx

Logical Backup Device  : http://technet.microsoft.com/en-us/library/ms189109.aspx