SQL Server – Eventos que impedem a reutilização do log

Você já teve problemas com arquivo de log cheio ou problemas para liberar espaço em um arquivo de log no SQL Server? Se sim, provavelmente você se deparou com algum evento que impedia a reutilização do arquivo. Podemos citar alguns deles: a falta de backups de log para bases com recovery model <> SIMPLE, replicação com comandos pendentes, transações abertas, etc.
Uma forma simples de identificar o motivo que impede a reutilização do arquivo de log é consultar o valor da coluna log_reuse_wait_desc na sys.databases; “NOTHING” significa que nenhum evento está impedindo a reutilização do arquivo.

Uma situação bastante comum que impede a reutilização do log é ter uma longa transação aberta no banco. Isso faz sentido: se existe uma transação não concluída, o SQL Server não pode simplesmente ignora-la; dessa forma, o arquivo de log “segura” estes registros até que a transação seja finalizada (neste caso com um COMMIT ou um ROLLBACK), independente de quantos CHECKPOINTs ocorram durante o processo.

Aproveitando o exemplo do post anterior, podemos simplesmente adicionar uma nova linha no código: antes do “while @i<10000″ adicione um BEGIN TRAN (sem COMMIT); assim teremos uma transação aberta que impedirá a reutilização do arquivo.

Executando este código em meu ambiente tive o seguinte resultado no Perfmon:

Log não reutilizavel

O arquivo não cresceu pois estava limitado a 1MB (da mesma forma que o post anterior); só que por conta da transação aberta o SQL Server não consegue reutilizar o arquivo de log; dessa forma o espaço disponível dentro do arquivo é consumido totalmente (linha azul) e sem espaço no arquivo de log o SQL Server não pode prosseguir com as alterações. Observe que a base está com recovery model SIMPLE e que durante a execução do código o SQL Server executou CHECKPOINTs automáticos mas estas questões não surtem efeito porque temos uma transação aberta impedindo a reutilização do arquivo de log. Numa situação dessas temos o erro:

Msg 9002, Level 17, State 4, Line 44
The transaction log for database ‘databasename’ is full due to ‘ACTIVE_TRANSACTION’.

Agora reflita: qual seria a solução para este caso? Ter um arquivo de log maior? Deixar a função de auto-crescimento habilitada para o arquivo de log? Ter uma transação menor?

Até a próxima :)

Read More

SQL Server – Log, um arquivo circular

Todas alterações realizadas em um banco de dados SQL Server são efetuadas em memória e no arquivo de log; posteriormente os dados serão gravados nos arquivos de dados (existem algumas poucas exceções para este comportamento, mas falaremos sobre este tema em um outro post).

Quando as alterações são persistidas nos arquivos de dados, em geral, o SQL Server pode remover estes registros do arquivo de log e liberar espaço para que este arquivo seja reutilizado; isto explica o termo “circular” utilizado para descrever o arquivo de log.

Agora proponho um teste para comprovarmos isto.

No script abaixo criamos um banco de dados com recovery model “Simple”. Note que o arquivo de log deste banco é criado com tamanho de 1MB, sem opção de auto-crescimento:

USE master
GO
CREATE DATABASE [TesteLogFile]
  ON  PRIMARY
( NAME = N'TesteLogFile',
  FILENAME = N'C:\DataSQL\TesteLogFile.mdf' ,
  SIZE = 3072KB )
 LOG ON
( NAME = N'TesteLogFile_log',
  FILENAME = N'X:\log\TesteLogFile_log.ldf' ,
  SIZE = 1MB , FILEGROWTH = 0 )
GO

ALTER DATABASE [TesteLogFile]
  SET RECOVERY SIMPLE
GO
USE [TesteLogFile]
GO
CREATE TABLE testeCarga
  (id bigint, texto char(4000))
GO

Você poderá abrir seu Perfmon e adicionar os seguintes contadores para ter algumas evidências:

  • Buffer Manager: Checkpoint pages/sec – Monitorar eventos de checkpoints na instância
  • Databases: Log File(s) Size (KB) – Monitorar tamanho do arquivo de log do banco
  • Databases: Log File(s) Used Size (KB) – Monitorar espaço utilizado no arquivo de log

O script que realizará a carga segue abaixo. Inicie a execução e observe o comportamento dos contadores no Perfmon:

set nocount on 

declare @i int = 0
 while @i < 10000
 begin
   waitfor delay '00:00:00:200'
  insert into testeCarga
    values (50000, 'teste carga')
  set @i = @i + 1
 end

Observe na imagem abaixo que o tamanho do arquivo de log não é alterado durante a carga ( Log File(s) Size (KB) representado na linha verde); porém notamos na linha azul que o espaço utilizado neste arquivo tem uma variação constante (Log File(s) Used Size (KB)). Note que no meu gráfico, quando o processo inicia, o arquivo de log já estava ocupando quase 500 KB. O espaço vai sendo consumido até ocupar quase 800 KB e depois este número baixa para aproximadamente 600 KB e este comportamento se repete algumas vezes. Observe que o quando o arquivo de log está próximo de 80% de utilização o SQL Server executa um CHECKPOINT automático (linha vermelha) com o propósito de descarregar as alterações nos arquivos de dados que poderá liberar espaço no arquivo de log e possibilitar sua reutilização.

Reutilização do log

Alguns eventos podem impedir a reutilização do arquivo de log mas isso também é assunto para um próximo post :)

Até a próxima.

Read More

SQL Server – Arquivos de log

Vimos nos posts anteriores (post1 e post2) que nos arquivos de dados o SQL Server balanceia a escrita de forma proporcional ao tamanho de cada arquivo. Hoje vamos ver como isso se aplica para arquivos de log.

O cenário é o seguinte: um banco de dados com 1 arquivo de dados e 2 arquivos de log. Estes 2 arquivos de log estão configurados sem crescimento automático e tamanho limitado em 1MB.

Executaremos uma carga de alguns registros neste banco e vamos observar através do Perfmon como o SQL Server está escrevendo nestes 2 arquivos de log.

Abaixo segue o script para criação do banco e da tabela de teste; observe que criei os 2 arquivos de log em discos diferentes para facilitar a monitoria no Perfmon:

USE master
GO

CREATE DATABASE [TesteLogFile]
  ON  PRIMARY
( NAME = N'DataFile',
  FILENAME = N'C:\Temp\TesteLogFile.mdf' ,
  SIZE = 3072KB )
 LOG ON
( NAME = N'LogFile1',
  FILENAME = N'X:\log\TesteLogFile_log.ldf' ,
  SIZE = 1MB ,
  FILEGROWTH = 0),
( NAME = N'LogFile2',
  FILENAME = N'Y:\log\TesteLogFile_log2.ldf' ,
  SIZE = 1MB ,
  FILEGROWTH = 0)
GO

USE TesteLogFile
go
create table testeCarga
  (id bigint, texto char(4000))
go

Agora damos carga na tabela com o script abaixo (como a operação é muito rápida coloquei um “waitfor delay” para deixar a carga mais lenta propositalmente, assim conseguiremos visualizar melhor o comportamento do SQL Server):

use TesteLogFile
go
declare @i int = 0
begin tran
  while @i < 285
    begin
      waitfor delay '00:00:00:200'
      insert into testeCarga values (50000, 'teste carga')
      set @i = @i + 1
    end

-- commit

Resultado: observe no gráfico abaixo que ao contrário dos arquivos de dados, o SQL Server não balanceia a escrita nos arquivos de log. Veja que o SQL Server inicia a escrita no primeiro arquivo de log e quando este arquivo está preenchido o SQL Server começa a escrever no arquivo seguinte:Escrita em dois arquivos de log

A linha vermelha representa as atividades na unidade X: onde colocamos o logfile1 e na linha verde temos a unidade Y:\ onde colocamos o logfile2. Note que a imagem acima é bem diferente desta que demonstra a escrita em 2 arquivos de dados.

A explicação para este comportamento é que a escrita num arquivo de log é sequencial.

Agora… você já ouviu dizer que o arquivo de log é um arquivo circular? O que seria este “circular”? Algum palpite?

Até o próximo post :)

Read More

SQL Server – Arquivos de dados com tamanhos diferentes

Dando sequencia ao ultimo post, vamos falar um pouco mais sobre arquivos de dados do SQL Server.

No último post criamos um base de dados com 3 arquivos de dados, sendo que 2 deles faziam parte de um mesmo filegroup, que foi configurado como filegroup default deste banco.

Um ponto importante é que ao criar os 2 arquivos de dados em questão, o tamanho de ambos era o mesmo.

A pergunta de hoje é a seguinte: e se os tamanhos destes 2 arquivos fossem diferentes? Isso influenciaria na forma como o SQL Server escreve nestes arquivos?

Vamos dar uma olhada de perto nisso.

Os script são os mesmos do último post, no entanto na cláusula SIZE do arquivo TesteDataFile1 colocamos metade do tamanho (SIZE = 5120KB) e mantemos a configuração para o TesteDataFile2. Para monitorar a escrita optei por criar cada arquivo em um disco diferente.

Em seguida executei a inserção que da carga de 4MB no novo banco e emiti o relatório “Disk Usage”:

Tamanho dos datafiles de um banco de dados

Com o resultado acima concluímos que o SQL Server escreveu nos 2 arquivos, no entanto, como o arquivo TesteDataFile2 tem o dobro do tamanho do TesteDataFile1, o SQL Server escreveu proporcionalmente nos dois arquivos.

Monitorando os dois discos com o Perfmon temos o seguinte resultado:

Escrita nos discos lógicos A linha vermelha representa a quantidade de bytes escritos no disco que armazena o arquivo TesteDataFile2 (que é o maior arquivo) e a linha azul representa a atividade no disco do arquivo TesteDataFile1.

Se analisarmos a média de bytes escritos por segundo, teremos:

  • TesteDataFile1: 380.478
  • TesteDataFile2: 499.133

Enfim, podemos concluir que o tamanho do datafile irá influenciar na forma como o SQL Server escreve nos arquivos. O SQL Server escreverá em ambos datafiles de forma proporcional ao tamanho do arquivo.

Pra ficar no radar :)

Read More

SQL Server – Arquivos de dados

Um banco de dados no SQL Server sempre terá pelo menos 2 arquivos: 1 arquivo de dados (normalmente com extensão .mdf) e 1 arquivo de log (geralmente com extensão .ldf).

Você poderá a qualquer momento adicionar mais arquivos a um banco de dados, seja de dados ou de logs.

Quando falamos em arquivos de dados, adicionar mais arquivos pode ter inúmeras finalidades, como: facilitar o gerenciamento, alocar mais espaço no banco de dados, melhorar desempenho, etc.

É interessante notar que quando você tem um banco com mais de um arquivo de dados, o SQL Server distribui a escrita entre os arquivos. Observe este comportamento na demonstração abaixo:

Criaremos um banco de dados para nossos testes. Este banco terá 3 arquivos de dados, sendo que 2 deles farão parte de um filegroup diferente e este será o filegroup default, dessa forma, todas as tabelas que criarmos neste banco serão automaticamente criadas neste filegroup:

USE master
GO

CREATE DATABASE TesteDataFile
 ON  PRIMARY
( NAME = 'TesteDataFile',
  FILENAME = 'C:\temp\TesteDataFile\TesteDataFile.mdf' ,
  SIZE = 4096KB
),
 FILEGROUP fg_data DEFAULT
( NAME = 'TesteDataFile1',
  FILENAME = 'C:\temp\TesteDataFile\TesteDataFile1.ndf' ,
  SIZE = 10240KB
),
( NAME = 'TesteDataFile2',
  FILENAME = 'C:\temp\TesteDataFile\TesteDataFile2.ndf' ,
  SIZE = 10240KB
)
 LOG ON
( NAME = 'TesteDataFile_log',
  FILENAME = 'C:\temp\TesteDataFile\TesteDataFile_log.ldf' ,
  SIZE = 1024KB
)
GO

Depois da criação, a partir do SSMS podemos extrair o relatório “Disk Usage” deste novo banco através das seguintes opções de menu:

Como acessar relatório "Disk Usage"
Como acessar relatório “Disk Usage”

O resultado é o seguinte:

Espaço utilizado pelos datafiles
Espaço utilizado pelos datafiles

No relatório acima identificamos que os arquivos TesteDataFile1 e TesteDataFile2 possuem 10MB reservados e que deste espaço somente 64KB está sendo utilizado em cada arquivo.

Agora, criamos uma tabela e inserimos aproximadamente 4MB de dados:

use TesteDataFile
go

create table testeCarga
(id bigint, texto char(4000))
go

insert into testeCarga
values (50000, 'Teste de carga')
go 1000

Atualizando o relatório “Disk Usage” temos o seguinte resultado:

Espaço utilizado pelos datafiles
Espaço utilizado pelos datafiles

Note que o SQL Server distribui a escrita dos dados e que os dois arquivos cresceram de forma simétrica. Sendo assim, podemos concluir que os dados da tabela testeCarga estão distribuídos entre os dois arquivos: testeFileData1 e testeFileData2.

Isso é para arquivos de dados. E no arquivo de log? Isso se aplica da mesma forma?

Até +

Read More

AlwaysOn AG e backups nas réplicas secundárias

No SQL Server 2012 AlwaysOn Availability Group (AG) temos a opção de executar backups nas réplicas secundárias. Esta é uma possibilidade interessante para muitos ambientes, considerando que assim podemos aumentar nossa janela de manutenção e tirar a carga dos backups da réplica primária.

Na figura abaixo é possível entender como configurar as réplicas preferenciais para backup:

AG_properties_backup

Percebam que podemos configurar os backups das bases que participam do AG das seguintes formas: executar preferencialmente nas réplicas secundárias (Prefer secondary), somente nas réplicas secundárias (Secondary only), somente na réplica primária (Primary) ou em qualquer réplica (Any Replica).

Observe que na imagem da tela sublinhei a palavra “automated”. Porque? Porque estas configurações só são aplicáveis para backups automatizados; logo isso não se aplica para backups ad-hoc. Sendo assim, se configurarmos um AG como “Secondary only” mas precisarmos de um backup a partir da réplica primária, basta executar um backup ad-hoc na réplica primária; esta configuração nunca te impedirá de realizar backup de suas bases.

Agora, o que o AG entende como “automated backup”? Aqui compreende-se um backup feito a partir de um plano de manutenção (com a opção abaixo desmarcada) ou um comando de backup em conjunto com a função sys.fn_hadr_backup_is_preferred_replica.

AG_properties_backup_task

Por fim, lembre-se que nas réplicas secundárias somente poderão ocorrer backups FULL com COPY_ONLY e backups de log; além disso a réplica secundária deve estar com sincronia ativa com a réplica primária (SYNCHRONIZED ou SYNCHRONIZING).

Até +

Referências:
http://technet.microsoft.com/en-us/library/hh245119.aspx
http://technet.microsoft.com/en-us/library/hh710053.aspx
http://technet.microsoft.com/en-us/library/hh213235.aspx

Read More

Os 5 “noves” da alta disponibilidade

Em ambientes críticos a disponibilidade de um sistema pode ser essencial para a sobrevivência do negócio. Porém manter um sistema 100% do tempo no ar é um desafio que envolve pessoas, investimento, infraestrutura e processos bem definidos. Mas mesmo com tudo isso, ainda assim será praticamente impossível ter um sistema com 100% de disponibilidade (uptime). Porque?! Porque são inúmeros os eventos que podem afetar a disponibilidade de um ambiente, como manutenções, atualizações de hardware/software, falta de energia, acidentes, desastres naturais, etc.

100% é praticamente impossível, mas um executivo poderá lhe pedir 5 noves :)

“Give me five nines” é uma expressão conhecida no mundo da alta disponibilidade e isso é a representação de 99, 999% de uptime, o que na prática se traduz em 5 minutos de downtime em um ano!

E aí?! Pronto para este desafio?!

Se 5 noves lhe parece um número muito otimista, poderá negociar 3 noves, que na prática lhe dá uma margem de quase 9 horas de indisponibilidade (downtime) em um ano.

Como chegamos neste número? Através da seguinte fórmula:

 ((8760-X)/8760)*100

8760 é a quantidade de horas em um ano com 365 dias e X é a quantidade de horas de downtime.

Assim, se em um ano você tem 24 horas de downtime, no fim você terá uma disponibilidade de 99,72%, neste caso, dois noves :)

Reflexão

Qual infraestrutura, soluções e processos seriam necessário para que seu ambiente atingisse a gloriosa marca de 6 noves? Sim! Já falam em 6 noves! Prepare-se :)

Referências
http://blogs.msdn.com/b/pcb/archive/2011/01/27/going-beyond-five-nines-uptime-on-the-microsoft-platform.aspx
http://blogs.technet.com/b/uspartner_ts2team/archive/2012/01/10/what-does-five-nines-mean.aspx
http://technet.microsoft.com/pt-pt/video/hh770194.aspx

Read More

AlwaysOn AG e FCI: Qual a diferença?

O AlwaysOn vem sem aclamado em muitos eventos, fóruns e encontros ocasionais entre DBAs nerds; porém, está acontecendo uma pequena confusão no nome, isto porque o nome AlwaysOn não veio para discernir uma única solução de HA e DR, mas duas soluções: AlwaysOn Failover Cluster Instance (FCI) e AlwaysOn Availability Groups (AG).

Quer saber mais sobre HA e DR? Leia este post!

Qual a diferença entre FCI e AG?

O AlwaysOn FCI nada mais é que o clássico SQL Server instalado sobre um Windows Cluster. Porque FCI? Porque nesta solução o escopo da redundância é a Instância inteira.

Na versão 2012 esta solução veio com algumas melhorias: a tempdb pode ser armazenada localmente, os nós podem estar em subnets diferentes (sem a necessidade de utilizar VLANs), temos uma política de failover mais flexível, suporte à SMB, etc.

Agora, o AlwaysOn AG é a grande novidade de fato! No AG temos alta disponibilidade no nível de bases de dados (não mais no nível da instância), assim, podemos ter grupos de disponibilidade com 1 ou mais bases de dados. Estas bases de dados podem ser sincronizadas entre 5 instâncias (réplicas) e os arquivos destas bases não ficam armazenados num único storage, eliminando assim o risco de um único ponto de falha. A sincronização entre as réplicas pode ser síncrona ou assíncrona e temos uma réplica primária onde podemos ler e escrever; as demais réplicas podem ser configuradas para leitura e execução de backups. No caso de falha na réplica primária podemos configurar o failover automático para uma outra réplica. Com o recurso do Listener podemos criar um nome virtual que irá abstrair toda essa infraestrutura para as aplicações. Enfim, vejam quantas possibilidades esta solução oferece :)

Posso combinar FCI com AG?

Para alguns cenários a possibilidade de combinar FCI + AG pode ser muito interessante e isto é possível, porém, quando combinamos AlwaysOn FCI com AlwaysOn AG só temos failover automático no escopo do FCI; o failover entre AGs é manual.

 fci_ag

Conclusão

SQL Server AlwaysOn remete a soluções de HA e DR. Apesar de terem o mesmo prefixo é importante diferenciar AlwaysOn FCI e AlwaysOn AG pois são soluções diferentes. A grande novidade é o AlwaysOn Availability Group que traz inúmeras possibilidades para implementar soluções de HA e DR em SQL Servers que suportam ambientes críticos.

Read More

Alta Disponibilidade X Recuperação de Desastre

High Avalibility (HA) e Disaster Recovery (DR) são conceitos diferentes mas que muitas vezes são confundidos e tratados como sinônimos. Se traduzirmos estes nomes para o português teremos respectivamente “alta disponibilidade” e “recuperação de desastre”. Neste post você verá que existe uma grande diferença entre eles.

High Avalibility (HA)

Quando falamos em HA, falamos em disponibilidade do ambiente, assim, espera-se que uma solução de HA mantenha o sistema no ar, disponível, pelo maior tempo possível, oferecendo preferencialmente um modo de recuperação automático para eventos de falha, sem que usuários e aplicações notem o problema de forma evidente. Um exemplo de uma tecnologia de alta disponibilidade é o Windows Server Failover Cluster (WSFC) que permite que 2 ou mais servidores trabalhem em conjunto para evitar longas paradas no ambiente; dessa forma quando um dos servidores falha, o outro assume o trabalho automaticamente (note que durante o processo de recuperação os usuários e aplicações perdem suas conexões neste ambiente, mas em alguns segundos tudo é reestabelecido e disponível novamente).

No SQL Server temos tecnologias de HA, como exemplo podemos citar o Database Mirroring, AlwaysOn FCI e AlwaysOn AG.

Disaster Recovery (DR)

Quando falamos em DR estamos falando do pós-desastre e desastres não acontecem todos os dias, logo, é altamente recomendável ter um plano de disaster recovery devidamente documentado, enumerando todos os passos necessários para reestabelecer o(s) sistema(s). Acredite, isso poderá reduzir muito o trauma de um desastre no seu ambiente.

A pergunta inicial para começar a pensar em um plano de DR é a seguinte: “quando meu sistema cair, qual será o meu plano de recuperação?” Nestes casos você deve ser pessimista e pensar: o que fazer quando o cluster inteiro cair? O que fazer quando o datacenter for incendiado (junto com todas as fitas de backup)? O que fazer quando o storage falhar? O que fazer quando os recursos de tolerância a falha e HA se esgotarem?

Um plano de DR deve considerar as exigências do negócio relacionados ao tempo de indisponibilidade; deve se atentar aos usuários que serão impactados direta ou indiretamente; precisa envolver comunicação e é altamente recomendável que seja um plano validado e divulgado para toda organização.

No SQL Server temos soluções aplicáveis em cenários de DR, como o Log Shipping, Replicação, Database Mirroring e AlwaysOn AG.

Cenário

Abaixo temos um esboço de uma solução com HA + DR no SQL Server. No ponto “A” temos uma instância do SQL Server sendo executada sobre um cluster Windows, onde temos alta disponibilidade com failover automático entre os 2 servidores participantes. No ponto “B” temos uma instância stand-alone (não-clusterizada) que é sincronizada através da solução  log shipping. Se o ambiente que hospeda a estrutura “A” for incendiado, basta executarmos o plano de DR, comunicando os usuários da indisponilidade, envolver time de infraestrutura e DBAs para habilitarem o ponto “B” de forma que ele possa receber as conexões das aplicações; notificando o time de deploy para alterar strings de conexão e etc.

 HA_DR

Conclusão

HA e DR são conceitos diferentes. O primeiro tem o intuito de evitar desastres e manter o sistema com maior uptime possível. O segundo tem o intuito de planejar e documentar os passos necessários para reestabelecer o ambiente após um desastre.

Planos e soluções de HA e DR precisam ser testados, ensaiados e simulados periodicamente!

Garanta que sua solução funcionará quando precisar dela :)

Referências:

http://technet.microsoft.com/en-us/library/ms187103.aspx

http://technet.microsoft.com/en-us/library/ms190202.aspx

http://technet.microsoft.com/en-us/library/ms178094(v=SQL.105).aspx

Read More

SQL Server 2000 para SQL Server 2012: um voo com escala

Se você tem bancos num SQL Server 2000 não será possível migra-los diretamente para o SQL Server 2012. O artifício para esta situação é migrar seu banco para uma versão intermediária (2005, 2008 ou 2008 R2) e só então migra-lo para o SQL Server 2012.

Outro ponto importante: o nível de compatibilidade 80 foi descontinuado no SQL Server 2012. Considerando isto, o que ocorre quando você tem um banco com compatibilidade 80 num SQL Server 2005, 2008 ou 2008 R2 e deseja migra-lo para o SQL Server 2012?

Este banco será migrado normalmente, já que de fato ele está numa instância 2005 ou superior; porém o nível de compatibilidade “sobe” automaticamente para um nível que seja suportado no SQL Server 2012; neste caso, este banco passaria  a ter compatibilidade = 90.

Evidências:

Tenho aqui um banco no SQL Server 2005 com compatibilidade 80. Faço o backup deste banco para restaura-lo no SQL Server 2012.

Antes de restaurar o backup no SQL Server 2012:

restore_header_only

Depois do restore, o banco passa para compatibilidade 90 automaticamente:

restore_header_only_2

Considere estes pontos em sua migração :)

Referências: http://technet.microsoft.com/pt-br/library/bb510680.aspx

Read More