BACKUP TO URL e o Tamanho do Backup – [1] Teoria…

4 Comments
Rodrigo Ribeiro Gomes
Post 1/1. Este post é parte da série: Backup To URL e o Tamanho do Backup
  • BACKUP TO URL e o Tamanho do Backup – [1] Teoria…
Tempo de leitura estimado: 6 minutos

Neste post vou iniciar mais uma série que nasceu de mais dia de trabalho na Power Tuning (na verdade, uns dois ou três). Cada vez mais empresas cedendo às maravilhas da Cloud, especialmente do Azure. No caso do SQL Server, ele tem uma integração fantástica e nativa: Você pode fazer o backup do seu banco direto pra uma Storage Account. Em outras palavras, seu backup já vai direto pro Azure (isto é, não fica local), e nem passa por um filesystem do seu servidor… Isso protege ainda mais contra ataques, como ransomware… É relativamente simples configurar isso… Tudo que você precisa é configurar um local no Azure e usar o comando BACKUP … TO URL!

Se você é um pai de primeira viagem, ou mesmo experiente no assunto, você pode ser pego de surpresa em suas tentativas e se deparar com o seguinte erro ao executar comando BACKUP … TO URL:

Msg 3202, Level 16, State 1, Line 4
Write on “https://ENDEREÇO-BLOB/CONTAINER/.bak” failed: 1117(The request could not be performed because of an I/O device error.)
Msg 3013, Level 16, State 1, Line 4
BACKUP DATABASE is terminating abnormally.

Analisando o error log do SQL Server, você encontra a seguinte mensagem:

BACKUP DATABASE successfully processed 547 pages in 1.408 seconds (3.032 MB/sec).
Error: 3063, Severity: 16, State: 1.
Write to backup block blob device https://thest.blob.core.windows.net/sqlbackup/BakTest.bak failed. Device has reached its limit of allowed blocks.

Error: 3063, Severity: 16, State: 1.
Write to backup block blob device https://thest.blob.core.windows.net/sqlbackup/BakTest.bak failed. Device has reached its limit of allowed blocks.

Error: 3063, Severity: 16, State: 1.
Write to backup block blob device https://thest.blob.core.windows.net/sqlbackup/BakTest.bak failed. Device has reached its limit of allowed blocks.

Error: 3063, Severity: 16, State: 1.
Write to backup block blob device https://thest.blob.core.windows.net/sqlbackup/BakTest.bak failed. Device has reached its limit of allowed blocks.

Error: 3063, Severity: 16, State: 1.
Write to backup block blob device https://thest.blob.core.windows.net/sqlbackup/BakTest.bak failed. Device has reached its limit of allowed blocks.

Error: 18210, Severity: 16, State: 1.
BackupIoRequest::ReportIoError: write failure on backup device ‘https://thest.blob.core.windows.net/sqlbackup/BakTest.bak’. Operating system error 1117(The request could not be performed because of an I/O device error.).

Error: 3041, Severity: 16, State: 1.
BACKUP failed to complete the command BACKUP DATABASE BigBakTest. Check the backup application log for detailed messages.

Com algumas pesquisas, você chega nesse excelente post do Dimitri Furman que basicamente resume como o SQL Server se integra com o Azure e como tudo isso funciona! Recomendo muito a leitura. Mas vou facilitar para você:

  • Cada arquivo que você especifica no TO URL é chamado de Block Blob (a partir daqui, irei chamar de arquivo ou block blob)
  • Um Block Blob é composto por até 50 mil blocos.
  • O SQL Server utiliza a API do Azure para escrever os dados (igual ele usa a API do Windows para escrever no disco)
    •  Cada bloco pode ter até 100MB na versão mais nova da API, e até 4MB nas versões anteiores a 2016-05-31 
    • A versão da API que o SQL utiliza é anterior a 2016-05-31, ou seja, permite até 4MB por bloco
    • Fazendo as contas: Se todos os 50 mil blocos tiverem 4MB, então, o tamanho máximo que um block blob consegue chegar é 4MB x 50.000 = ~ 195GB
  • Por padrão, o comando de BACKUP DATABASE escreve 1MB por bloco
    • Por isso você recebe o erro acima. Com os valores padrões, seu backup com 1 block blob (um TO URL), pode crescer até mais ou menos 48GB (1MB * 50.000 = ~ 48GB)
    • Você pode mudar isso usando a opção MAXTRANSFERSIZE que controla o tamanho do bloco (segundo a doc, tem que ser múltiplos de 64KB, e um valor máximo de 4194304, isto é, 4MB)

Para ilustrar melhor:

Ilustração de BACKUP DATABASE e Blobk Blobs

Ao usar o comando BACKUP DATABASE, cada “TO URL” especificado faz com que seja criado um novo “Block Blob”. Cada block blob e constituído de vários blocos (até 50 mil blocos), onde cada bloco tem um tamanho máximo de dados que podem ser inseridos nele. A opção MAXTRANSFERSIZE do comando de backup, controla o máximo que o SQL vai tentar inserir num bloco. Esse tamanho também é limitado pela API do Azure. No caso do SQL Server, ele utiliza uma versão que permite até 4MB! Ou seja, o MAXTRANSFERSIZE deve ser <= 4MB (e em múltiplis de 64k). Por padrão, MAXTRANSFERSIZE é 1MB!

 

Por padrão, um backup de um banco com cerca de 6MB, vai ficar assim em um Azure Block Blob:

1 Blobk Blob com 6 blocos de 1MB

Com as opções padrões, um backup com total de 6MB se pareceria com isso lá Azure Block Blob

Em suma, o erro que o SQL disparou é muito claro: Você atingiu o limite desses blocos em alguns dos Blocks Blobs usados! E para resolver isso, você tem algumas opções:

  • ajustar o MAXTRANSFERSIZE para o máximo possível, permitindo que você use o máximo de espaço possível em um Block Blob. No caso do SQL Server, o máximo é 4194304 (4MB). Com 50 mil blocos de limite em cada block blob, o máximo que o SQL Server consegue escrever em um único Block Blob do Azure são cerca de 195GB ( 4MB x 50 MIL)… E é por isso que, se você precisa mais do que isso, é necessário adicionar mais block blobs, adicionando mais TO URL.

  • adicionando mais TO URL
    Se você chegou no limite de um único block blob, então o que resta é aumentar a quantidade de block blobs. Você faz isso dividindo o seu backup em mais arquivos. O SQL suporta esse stripping do arquivo de backup desde  a cláusula “TO DISK” e “TO TAPE”, e com o TO URL não é diferente. Então, não é uma novidade isso. Fazendo o SQL usar mais de um único block blob, é uma maneira de contornar esse limite do Azure. Por exemplo, um backup cujo tamanho total é de 400GB (independente de ser comprimido ou não) não cabe um um único Block Blob, e por isso você tem que especificar dois TO URL (e por segurança, três), se estiver usando um MAXTRANSFERSIZE de 4MB. O máximo que você consegue especificar no comando BACKUP DATABASE são 64 “TO URL”! Ou seja, considerando o máximo de 4MB por bloco, o seu backup pode chegar até: 4MB * 50 mil blocos * 64 block blobs = ~12TB (até o SQL Server 2019 CU4, época em que este post foi escrito, ele ainda usava a API que permite até 4MB)

Na teoria é lindo…

Pois é… Num caso recente, eu e o Diego nos deparamos com esta mensagem, e mesmo fazendo todos as contas e todos os ajustes, continuamos recebendo esta mensagem. O Banco tinha 1TB de tamanho, desses 1TB, apenas 448GB usados. A edição dele não permitia o uso de compressão (era um 2019 Web Edition). Então esse era o tamanho do backup necessário: 448GB! Fazendo a conta:

  • 1 block blob = 195GB (com blocos de 4MB)
  • 2 blocks blobs = 195*2 = 390GB… Ainda não cabe os 440GB
  • Então, com 3 = 585GB! Opa, na teoria, três arquivos são suficientes e ainda sobraria!

E então fiz o backup to URL com três:

BACKUP DATABASE Banco448GB TO
	 URL  = 'https://STORAGEACCOUNT.blob.core.windows.net/CONTAINER/Banco448GB.part1.bak'
	,URL  = 'https://STORAGEACCOUNT.blob.core.windows.net/CONTAINER/Banco448GB.part2.bak'
	,URL  = 'https://STORAGEACCOUNT.blob.core.windows.net/CONTAINER/Banco448GB.part3.bak'
WITH MAXTRANSFERSIZE = 4194304

E mesmo usando os três arquivos, voltei a receber o mesmo erro do início deste post! Então, fomos aumentando. Tentamos com 20 arquivos e nada. Com 30… Sempre que aumentávamos o número de arquivos, o backup demorava mais pra falhar, mas falhava pela mesma razão: atingiu o limite de blocos em dos block blobs. Então, o Diego sugeriu tentamos com o máximo de 64 block blobs. E finalmente deu certo. Cada blob ficou 7GB! Fazenda a conta: 7*64 = 448GB!

Eu achei muito estranho, pois em outro cliente, um backup com um total de 500GB foi feito normalmente com apenas 8 blobs! Então, diante desses fatos eu resolvi investigar mais a fundo… E não encontrei nada que explicasse isso… Depois de algumas investigações, cheguei a resposta… E eu vou contar ao longo dessa série, pois tem muitas coisas legais que aprendi nesse caminho e serão bem úteis para você entender mais o Backup pro Azure e o como o comando de BACKUP funciona!

Até a próxima!

Compartilhe este post!

Posts Relacionados:

Tutorial: Criando um Block Blob no Azure para um BACKUP TO URL Azure ARM Templates: Como eu monto uma infra pra um AlwaysON em minutos? Azure File Share no SQL Server – [2] Conceitos do Azure files Azure File Share no SQL Server – [5] Tudo junto! Recuperando dados deletados do SQL Server sem Backup Full – Parte 1

Comments ( 4 )

  1. / ReplyRenato Siqueira
    Que doidera lol Curioso pelos próximos posts.
  2. / ReplyLuiz Vitor França Lima
    TOP Rodrigo! CASE bacana esse ai e excelente explicação sobre o tamanho dos blocos dos backups! Aguardando os proximos post também =) Abraço, Luiz Vitor
  3. / ReplyConfigurando o Backup do seu SQL Server para salvar direto no Azure - Gustavo Larocca
    […] https://thesqltimes.com/blog/2020/06/21/backup-to-url-tamanho-backup-1/ […]

Leave a reply

Your email address will not be published.