Simulando – Desastre e Recuperação de Bancos de Dados – Microsoft SQL Server 2008 R2 e 2012 – Parte 2.

Pessoal, bom dia.

Tudo bem? Alguma novidade?

Conforme o combinado, vou dar continuidade a minha série de posts sobre Simulação de Desastre e Recuperação de Banco de Dados. Na primeira parte 1, destaquei os principais conceitos que envolvem um ambiente de banco de dados, como também, montamos o cenário que vamos trabalhar.

Nesta segunda parte, o objetivo é colocar em prática e apresentar como podemos realizar esta simulação de desastre, forçando o processo de Rompimento do Banco de Dados.

Introdução

Como destacado na primeira parte desta série, todo e qualquer banco de dados é formado por um conjunto de arquivos de dados e composto basicamente por pequenas Estruturas de Dados conhecidas como Tabelas ou Tables.

Sendo assim, quando nos referimos em forçar o rompimento de um banco de dados, estamos na teoria dizendo que as estruturas de dados que armazenam nossos dados serão afetadas, onde suas áreas de armazenamento chamadas de Páginas de Dados, Extensões, Partições e Unidades de Alocação podem ser logicamente ou fisicamente danificadas, o que poderá impossibilitar o acesso aos dados.

Antes de realizarmos os procedimentos para simular o desastre, como de costume vou destacar um pouco sobre cada conceito envolvido no armazenamento de dados.

Páginas de Dados

Considerada como a unidade fundamental de armazenamento de dados no SQL Server é a página. O espaço em disco alocado a um arquivo de dados (.mdf ou .ndf) em um banco de dados é logicamente dividido em páginas numeradas de forma contígua de 0 a n.

As operações de E/S de disco são executadas no nível de página. Ou seja, o SQL Server lê ou grava páginas de dados inteiras. As extensões são uma coleção de oito páginas fisicamente contíguas e são usadas para gerenciar as páginas de forma eficaz. Todas as páginas são armazenadas em extensões.

No SQL Server, o tamanho de página é 8 KB. Isso significa que os bancos de dados SQL Server têm 128 páginas por megabyte. Cada página começa com um cabeçalho de 96 bytes usado para armazenar informações de sistema sobre a página. Essas informações incluem o número de página, o tipo de página, a quantidade de espaço livre na página e a ID de unidade de alocação do objeto que possui a página.

A Tabela 1 a seguir mostra os tipos de página usados nos arquivos de dados de um banco de dados SQL Server.

Tipo de página

Conteúdo

Dados Linhas de dados com todos os dados, exceto os tipos de dados text, ntext, image, nvarchar(max), varchar(max), varbinary(max) e xml, quando o texto na linha é definido como ON.
Índice Entradas de índice.
Texto/Imagem Tipos de dados de objeto grande:

  • Tipos de Dados text, ntext, image, nvarchar(max), varchar(max), varbinary(max) e xml

Colunas de comprimento variável quando a linha de dados excede 8 KB:

  • varchar, nvarchar, varbinary e sql_variant
Global Allocation Map, Shared Global Allocation Map Informações sobre alocação de extensões.
Page Free Space Informações sobre alocação de página e espaço livre disponível em páginas.
Index Allocation Map Informações sobre extensões usadas por uma tabela ou índice por unidade de alocação.
Bulk Changed Map Informações sobre extensões modificadas pelas operações em massa desde a última instrução BACKUP LOG por unidade de alocação.
Differential Changed Map Informações sobre extensões modificadas desde a última instrução BACKUP DATABASE por unidade de alocação.

Tabela 1 – Relação de Tipos de Páginas de Dados que compõem um banco de dados.

As linhas de dados são colocadas em série na página, iniciando imediatamente após o cabeçalho. Uma tabela de deslocamento da linha tem início no final da página, e cada tabela de deslocamento da linha contém uma entrada para cada linha na página.

Cada entrada registra a distância do primeiro byte da linha em relação ao início da página. As entradas na tabela de deslocamento da linha estão em seqüência inversa da seqüência das linhas na página. A Figura 1 representa a Estrutura de uma página de dados, com o seu cabeçalho, Seqüência de linhas de dados e Deslocamento de Linhas.

Simulação-Figura1

Figura 1 – Estrutura Física e Lógica de uma Página de Dados no Microsoft SQL Server.

Suporte à linha grande

As linhas não podem passar de uma página para outra, no entanto, partes da linha podem ser afastadas da página da linha para que a linha possa ser realmente muito grande. A quantidade máxima de dados e sobrecarga contida em uma única linha de uma página é 8.060 bytes (8 KB). Porém, isso não inclui os dados armazenados no tipo de página de Texto/Imagem.

Essa restrição é consentida para tabelas que contêm colunas varchar, nvarchar, varbinary ou sql_variant. Quando o tamanho total da linha de todas as colunas fixas e variáveis em uma tabela exceder a limitação de 8.060 bytes, o SQL Server moverá uma ou mais colunas de comprimento variável dinamicamente para as páginas na unidade de alocação ROW_OVERFLOW_DATA, iniciando com a coluna com a maior largura. Isso é feito sempre que uma operação de inserção ou atualização aumenta o tamanho total da linha além do limite de 8.060 bytes.

Quando uma coluna é movida para uma página na unidade de alocação ROW_OVERFLOW_DATA, é mantido um ponteiro de 24 bytes na página original da unidade de alocação IN_ROW_DATA. Se uma operação subseqüente reduzir o tamanho da linha, o SQL Server moverá as colunas dinamicamente para a página de dados original.

Extensões

As extensões são denominadas, como unidade básica de armazenamento em que o espaço é gerenciado. Uma extensão tem oito páginas fisicamente contíguas ou 64 KB. Isso significa que os bancos de dados SQL Server têm 16 extensões por megabyte.

Para tornar a alocação de espaço eficiente, o SQL Server não aloca extensões inteiras a tabelas com quantidades pequenas de dados. O SQL Server tem dois tipos de extensões:

  • Extensões uniformes que pertencem a um único objeto: Todas as oito páginas na extensão podem ser usadas apenas pelo objeto proprietário. A Figura 2 apresenta a estrutura de armazenamento utilizada por Extensões Uniformes.

Simulação-Figura2

Figura 2 – Representação da Estrutura de Armazenamento de Extensões Uniforme.

  • Extensões mistas compartilhadas por até oito objetos: Cada uma das oito páginas da extensão pode pertencer a um objeto diferente. A Figura 3 apresenta a estrutura de armazenamento utilizada por Extensões Mistas.

Simulação-Figura3

Figura 3 – Representação da Estrutura de Armazenamento de Extensões Mistas.

Observações:

  1. Ao criar ma nova tabela ou um índice geralmente estes objetos são páginas alocadas de extensões mistas.
  2. Quando a tabela ou o índice cresce até adquirir oito páginas, é alternado para usar extensões uniformes para alocações subseqüentes.
  3. Se um índice for criado em uma tabela existente que tiver linhas suficientes para gerar oito páginas no índice, todas as alocações para o índice estarão em extensões uniformes.

 

Partições

As páginas de tabela e índice são contidas em uma ou mais partições. Uma partição é uma unidade definida pelo usuário da organização de dados. Por padrão, uma tabela ou um índice tem apenas uma partição que contém todas as páginas de tabela ou índice.

A partição reside em um único grupo de arquivos. Uma tabela ou um índice com uma única partição é equivalente à estrutura organizacional de tabelas e índices em versões anteriores do SQL Server.

Quando uma tabela ou índice usa várias partições, os dados são particionados horizontalmente de forma que os grupos de linhas sejam mapeados em partições individuais, com base em uma coluna especificada. As partições podem ser colocadas em um ou mais grupos de arquivos no banco de dados. A tabela ou o índice é tratado como uma única entidade lógica quando são executadas consultas ou atualizações nos dados.

Unidades de Alocação

Uma unidade de alocação é uma coleção de páginas em um heap ou árvore B usada para gerenciar dados com base no tipo da página.

Unidade de alocação IN_ROW_DATA

Para toda partição usada por uma tabela (heap ou tabela clusterizada), índice ou exibição indexada, há uma unidade de alocação IN_ROW_DATA constituída de uma coleção de páginas de dados.

Essa unidade de alocação também contém coleções adicionais de páginas para implementar cada índice XML e não clusterizado definido para a tabela ou a exibição. As coleções de páginas em cada partição de uma tabela, índice ou exibição indexada são ancoradas por ponteiros de página na exibição de sistema sys.system_internals_allocation_units.

Unidade de alocação ROW_OVERFLOW_DATA

Para toda partição usada por uma tabela (heap ou tabela clusterizada), índice ou exibição indexada, há uma unidade de alocação IN_OVERFLOW_DATA. Essa unidade de alocação contém zero (0) página até que uma linha de dados com colunas de comprimento variável (varchar, nvarchar, varbinary ou sql_variant) na unidade de alocação IN_ROW_DATA exceda o limite de tamanho de linha de 8 KB.

Quando o limite de tamanho é alcançado, o SQL Server move a coluna com a largura maior daquela linha para uma página na unidade de alocação ROW_OVERFLOW_DATA. Um ponteiro de 24 bytes para esses dados fora da linha é mantido na página original.

As páginas de Texto/Imagem na unidade de alocação ROW_OVERFLOW_DATA são gerenciadas da mesma maneira que as páginas na unidade de alocação LOB_DATA. Ou seja, as páginas de Texto/Imagem são gerenciadas por uma cadeia de páginas IAM.

Unidade de alocação LOB_DATA

Quando uma tabela ou índice tem um ou mais tipos de dados de LOB, uma unidade de alocação LOB_DATA por partição é alocada para gerenciar o armazenamento de tais dados. Os tipos de dados de LOB incluem text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max) e tipos de dado CLR definidos pelo usuário.

Acredito que com os conceitos apresentados aqui sobre os elementos responsáveis em armazenar um dado existente em uma tabela, podemos dar continuidade como nosso cenário, vamos então trabalhar com nosso ambiente.

 

Adicionando mais elementos ao nosso Ambiente

Bom, para podermos montar a simulação vamos realizar a criação de uma tabela que fará uso das Páginas de Dados, Extensões, Partições e Unidades de Alocação. Esta tabela será denominada Clientes, conforme apresenta o Código 1 apresentado abaixo:

 

 

– Código 1 – Criação da Tabela Clientes –

Use MyDatabaseDesastre

Go

Create Table Clientes

(Codigo Int Primary Key Identity(1,1),

Nome Varchar(60),

Email Varchar(100))

Go

O próximo passo consiste em realizar a inserção de uma pequena massa de dados, para isso vamos utilizar o Código 2 apresentado abaixo.

– Código 2 – Realizando a Inserção da Massa de Dados na Tabela Clientes –

Declare @Contador TinyInt

Set @Contador = 1

While @Contador <= 150

Begin

Insert Into Clientes (Nome, Email)

Values (‘Cliente ‘+Convert(Varchar(3),@Contador),                ‘Cliente’+Convert(Varchar(3),@Contador)+’@mydatabasedesastre.edu.br’)

Set @Contador += 1

End

Você pode notar que estou utilizando um pequeno Loop de Execução através do Comando While, sei que poderia fazer isso o comando Go em conjunto com um número de execução, como também, da variável de sistema @@Identity para pegar o número do Identity, mas por questões de demonstração preferi fazer isso deste tradicional recurso.

A Tabela Clientes esta criada, populada com 150 linhas de registros, como boa prática vamos realizar um backup do nosso Banco de Dados, como também, do nosso arquivo de Log de Transações, conforme apresenta o Código 3.

– Código 3 – Realizando o Backup do Banco de Dados e do Log de Transações –

Backup Database MyDatabaseDesastre

To Disk = ‘C:\Bancos\MyDatabaseDesastre\MyDatabaseDesastre-Backup.bak’

With Init, NoFormat,

Description = ‘Backup Database MyDatabaseDesastre’,

Stats=10

Go

Backup Log MyDatabaseDesastre

To Disk = ‘C:\Bancos\MyDatabaseDesastre\MyDatabaseDesastre-Backup-Log.bak’

With Init, NoFormat,

Description = ‘Backup Log MyDatabaseDesastre’,

Stats=10

Go

Pronto, pronto, Backup de Banco de Dados e Backup de Log criados, já temos uma forma mais segura para recuperar o nosso ambiente caso não seja possível através de outras formas. Em nosso cenário de simulação, conforme destacado, iremos forçar o Rompimento da Estrutura Física e Lógica do nosso Banco de Dados MyDatabaseDesastre, como se tivesse ocorrido uma falha de energia elétrica ou desligamento forçado do servidor.

Para darmos inicio ao processo de quebra do banco de dados, vamos criar um bloco de Transação chamado Tran1, dentro deste bloco executaremos um Insert de 15000 linhas de dados em nossa Tabela Clientes, conforme apresenta o Código 4 a seguir:

– Código 4 – Realizando o Rompimento da Integridade Física e Lógica do Banco de Dados –

Begin Transaction Tran1

Declare @Contador SmallInt

Set @Contador = 1

While @Contador <= 15000

Begin

Insert Into Clientes (Nome, Email)

Values (‘Cliente ‘+Convert(Varchar(6),@Contador),                ‘Cliente’+Convert(Varchar(6),@Contador)+’@mydatabasedesastre.edu.br’)

Set @Contador += 1

End

Realizamos o processo de Insert, mas como estamos trabalhando com um Bloco de Transação Explícita nossos dados ainda não foram confirmados por parte do SQL Server, para isso poderíamos utilizar os Comando Commit Transaction e Checkpoint, em nosso cenário não vamos utilizar nenhum destes dois comandos, muito pelo contrário, vamos forçar o desligamento do nosso SQL Server através do comando Shutdown.

 

Para isso realize a abertura de duas novas querys e execute em sequência os dois comandos Shutdown apresentados no Código 5:

– Código 5 – Forçando o Desligamento da Instância SQL Server –

SHUTDOWN

 

SHUTDOWN WITH NOWAIT

 

Observe que após a execução deste comando o SQL Server começar a realizar o processo de encerramento de todas as transações e entre no estado de encerramento dos processos, sem garantir a conclusão da nossa Tran1, pois o Log de Transações esta incompleto devido a falha na confirmação dos dados.

Este tipo de situação é facilmente contornando através da reinicialização do serviço Database Engine, numa situação normal, ao ser inicializado, o SQL Server realizará o ROLLBACK das transações que ficaram em aberto, fazendo uso das informações contidas no Arquivo de Log.

Mas isso é algo que poderá demandar um tempo de processamento por parte do SQL Server após o processo de Start do Database Engine, fazendo com o Banco de Dados, seja apresentado no Estado de In Recovery, conforme apresenta a Figura 4 abaixo:

Simulação-Figura4

Figura 4 – MyDatabaseDesastre apresentando o Status de In Recovery, após a simulação de desligamento forçado.

Muito bem, o próximo passo da nossa Jornada é realizar a Simulação de Desastre corrompendo o Arquivo de Log de Transações, mas isso faremos na próxima parte desta série.

Mais uma vez obrigado, nos encontramos em breve, agradeço a sua visita.

Até mais.

About these ads

Um comentário sobre “Simulando – Desastre e Recuperação de Bancos de Dados – Microsoft SQL Server 2008 R2 e 2012 – Parte 2.

  1. Pingback: Entendendo e Melhorando seus backups (SQL Server) | Alex Souza

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s