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

Olá, Galera, Olá Comunidade Microsoft, bom dia.

Gostaria de começar esta Série de posts relacionados a Desastre e Recuperação de Dados, com algumas perguntas:

1 – Será que alguém de vocês já passou por uma situação de Perda ou Risco de Perda de Dados em seu ambiente? Mais especificamente em relação aos seus dados armazenados em suas tabelas dentro de seus respectivos Bancos de Dados?

2 – Você conhece a estrutura Física e Lógica de um Banco de Dados?

3 – Você sabe a diferença entre os Modelos de Recuperação de Banco de Dados?

4 – O que representa um Banco de Dados no Estado de Suspect ou Emergency Model?

Estas e algumas outras perguntas, vou tentar no decorrer desta série responder. Tenha certeza que boa parte dos Profissionais de TI, DBA e Administradores de Infraestrutura já devem ter passado por esta situação.

Como isso é algo muito comum e de extrema importância, decidi desenvolver um ambiente que possamos simular e entender o que pode acontecer no caso de uma falha em seu ambiente físico, que poderá gerar problemas em seus Bancos de Dados.

Sendo assim, vou começar apresentando um pouco do Conceito de Banco de Dados, por parte da Microsoft com base na Documentação Oficial existente no Books On-Line do Microsoft SQL Server.

Fundamentos de Banco de Dados

Um banco de dados no SQL Server é composto de uma coleção de tabelas que armazena um conjunto específico de dados estruturados. Uma tabela contém uma coleção de linhas, também chamada de registros ou tuplas, e colunas, também chamadas de atributos. Cada coluna da tabela é projetada para armazenar um determinado tipo de informação, por exemplo, datas, nomes, quantias em dólares e números.

As tabelas possuem diversos tipos de controles, como restrições, gatilhos, padrões e tipos de dados de usuários personalizados, que garantem a validade dos dados.

As restrições de integridade referencial declarativa (DRI) podem ser adicionadas às tabelas para certificar que os dados inter-relacionados nas diferentes tabelas permaneçam consistentes. As tabelas podem ter índices semelhantes aos dos livros que habilitam as linhas a serem localizadas rapidamente.

Um banco de dados também pode conter procedimentos que usam o código de programação Transact-SQL ou .NET Framework para efetuar operações com os dados do banco de dados. Essas operações incluem a criação de exibições que fornecem acesso personalizado aos dados da tabela ou a uma função definida pelo usuário que efetua um cálculo complexo, em um subconjunto de linhas.

Por exemplo, você cria um banco de dados nomeado MyDatabase para gerenciar os dados de sua empresa. No banco de dados MyDatabase, você cria uma tabela que é nomeada Funcionarios, para armazenar informações sobre cada funcionário. As tabela também contém colunas que são nomeadas EmpId, LastName, FirstName, Dept, e Title. Para se certificar de que dois funcionários não compartilham o mesmo EmpId e que a coluna Dept contém somente números válidos para os departamentos de sua empresa, é preciso adicionar restrições à tabela.

Como você deseja encontrar rapidamente os dados de um funcionário, com base no ID ou sobrenome do funcionário, você define os índices. Você precisará adicionar uma linha de dados à tabela Funcionarios, para cada funcionário; assim, você também precisará criar um procedimento armazenado chamado AddEmployee. Esse procedimento está personalizado para aceitar os valores de dados de um novo funcionário e efetuar a operação de adição da linha à tabela Funcionarios.

Você pode precisar de um resumo dos departamentos dos funcionários. Nesse caso, você definirá uma exibição chamada DeptEmps que combina dados das tabelas Departamentos e Funcionarios e gera a saída. Esta ilustração mostra as partes do MyDatabase criadas. A Figura 1 apresentada abaixo, ilustra a organização física de um Banco de Dados, dentro do Microsoft SQL Server:

Fundamentos-Banco de DadosFigura 1 – Estrutura física de um Banco de Dados.

Uma instância de SQL Server pode oferecer suporte a muitos bancos de dados. Cada banco de dados pode armazenar dados inter-relacionados ou não relacionados de outros bancos de dados.

Por exemplo, uma instância do SQL Server pode ter um banco de dados que armazena dados pessoais e outro banco de dados que armazena dados relacionados aos produtos. Como alternativa, um banco de dados pode armazenar dados atuais de pedidos de clientes, e outro banco de dados relacionado pode armazenar o histórico de pedidos de clientes usado para relatório anual.

O próximo passo é conhecer um pouco dos arquivos que compõem fisicamente os Bancos de Dados existentes no Microsoft SQL Server, normalmente definidos como arquivos de dados, arquivos de log e grupos de arquivos.

Compreendendo arquivos e grupos de arquivos 

Todo o banco de dados SQL Server tem, no mínimo, dois arquivos de sistema operacional: um arquivo de dados e um arquivo de log.

Os arquivos de dados contêm dados e objetos como tabelas, índices, procedimentos armazenados e exibições. Os arquivos de log contêm as informações necessárias para recuperar todas as transações no banco de dados. Os arquivos de dados podem ser agrupados em grupos de arquivos para propósitos de alocação e administração.

Arquivos de banco de dados

Os bancos de dados SQL Server possuem três tipos de arquivos, como mostrado na Tabela 1 a seguir.

Arquivo

Descrição

Primário O arquivo de dados primário contém as informações de inicialização do banco de dados e aponta para os outros arquivos no banco de dados. Dados do usuário e objetos podem ser armazenados neste arquivo ou em arquivos de dados secundários. Todo banco de dados possui um arquivo de dados primário. A extensão de nome de arquivo indicada para arquivos de dados primários é .mdf.
Secundário Os arquivos de dados secundários são opcionais, definidos pelo usuário, e armazenam dados do usuário. Arquivos secundários podem ser usados para distribuir os dados entre os diversos discos, colocando cada arquivo em uma unidade de disco diferente. Além disso, caso um banco de dados exceda o tamanho máximo em um único arquivo Windows, será possível usar arquivos de dados secundários, assim, o banco de dados continuará a crescer.

A extensão de nome de arquivo indicada para arquivos de dados secundários é .ndf.

Log de transações Os arquivos de log de transações armazenam as informações de log usadas para recuperar o banco de dados. Deve haver, no mínimo, um arquivo de log para cada banco de dados. A extensão de nome de arquivo indicada para arquivos de transação é .ldf.

Tabela 1 – Descrição das Funções dos Arquivos de Banco de Dados(Dados e Log).

Exemplo

Ao criar um simples banco de dados nomeado como Vendas que tenha um arquivo primário com todos os dados e objetos, e um arquivo de log que tenha as informações de log de transação.

Como alternativa, pode-se criar um banco de dados mais complexo nomeado como Pedidos que tenha um arquivo primário e cinco arquivos secundários. Os dados e objetos no banco de dados distribuem-se pelos seis arquivos, e os quatro arquivos de log contêm as informações do log de transação.

Por padrão, os dados e logs de transação são colocados na mesma unidade e caminho. Isto é feito para controlar os sistemas de um único disco. Porém, isto não é o ideal para ambientes de produção. Recomendamos que você coloque os dados e arquivos de log em discos separados.

Neste momento já conhecemos um pouco sobre os arquivos de bancos de dados, agora podemos avançar um pouco mais destacando os Grupos de Arquivos de Bancos de Dados.

Grupos de arquivos

Todo banco de dados possui um grupo de arquivo primário. Este grupo de arquivo contém o arquivo de dados primário e qualquer um dos arquivos secundários que não foram colocados em outros grupos de arquivos. Grupos de arquivos definidos pelo usuário podem ser criados para agrupar os arquivos de dados para fins administrativos, de alocação de dados e de posicionamento.

Por exemplo, três arquivos, Data1.ndf, Data2.ndf, e Data3.ndf, podem ser criados em três unidades de disco, respectivamente, e atribuídos ao grupo de arquivos fgroup1. Uma tabela pode ser criada especificamente no grupo de arquivos fgroup1.

As consultas para obter dados da tabela serão distribuídas pelos três discos; isso melhorará o desempenho. A mesma melhora no desenvolvimento pode acontecer, usando um único arquivo criado em um conjunto distribuído RAID (redundant array of independent disks).

Porém, arquivos e grupos de arquivos permitem que novos arquivos sejam facilmente adicionados aos novos discos.

Todos os arquivos de dados são armazenados nos grupos de arquivos listados na Tabela 2 a seguir.

Todos os arquivos de dados são armazenados nos grupos de arquivos listados na tabela a seguir.

Grupo de arquivos

Descrição

Primário O grupo de arquivos que contém o arquivo primário. Todas as tabelas do sistema são alocadas no grupo de arquivos primário.
Definido pelo usuário Qualquer grupo de arquivos que seja criado especificamente pelo usuário quando o usuário cria primeiro ou modifica depois o banco de dados.

Grupo de arquivos padrão

Quando objetos são criados no banco de dados sem especificar a qual grupo de arquivos eles pertencem, os objetos são atribuídos ao grupo de arquivos padrão. A qualquer hora, um grupo de arquivos é designado como o grupo de arquivos padrão. Os arquivos no grupo de arquivos padrão devem ser grandes o suficientes para armazenar qualquer objeto novo alocado a outros grupos de arquivo.

O grupo de arquivos PRIMÁRIO é o grupo de arquivos padrão, a menos que seja alterado usando a instrução ALTER DATABASE. A alocação para os objetos de sistema e de tabelas permanece no grupo de arquivos PRIMÁRIO, e não no novo grupo de arquivos padrão.

Muito bem, com todo este conjunto de informações, acredito que neste momento já estamos todos familiarizados com os Fundamentos, Arquitetura dos Arquivos de Dados e Grupos de Arquivos, vamos então começar a falar um pouco do nosso ambiente para Simulação de um Desastre.

Preparando o Ambiente

A preparação do nosso ambiente é bem simples, começamos pela definição do nosso Banco de Dados, denominado MyDatabaseDesastre, sendo este fisicamente organizado:

  • Database File: MyDatabaseDesastre-Dados.Mdf;
  • Database Log: MyDatabaseDesastre-Log.Ldf.

Vamos utilizar o Código 1 apresentado abaixo para realizar a crição do nosso Banco de Dados:

Código 1 — Criando o Banco de Dados – MyDatabaseDesastre –

CREATE DATABASE MyDatabaseDesastre
ON PRIMARY
(NAME = MyDatabaseDesastre_Dados,
FILENAME = N’C:\Bancos\MyDatabaseDesastre\MyDatabaseDesastre-Dados.mdf’,
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 10%)
LOG ON
(NAME = MyDatabaseDesastre_Log,
FILENAME = N’C:\Bancos\MyDatabaseDesastre\MyDatabaseDesastre-Log.ldf’,
SIZE = 2MB,
MAXSIZE = 40MB,
FILEGROWTH = 10%)
GO

Após criarmos nosso Banco de Dados, vamos realizar os demais passos, para deixarmos o ambiente o mais próximo possível de uma cenário real. Neste caso, o próximo procedimento é definir o Modelo de Recuperação de Banco de Dados.

Modelos de Recuperação de Banco de Dados

Os modelos de recuperação são projetados para controlar a manutenção de log de transações. Os modelos possuem relação direta com as estruturas de armazenamento dos dados que formam um banco de dados.

Com base no modelo de recuperação definido para um banco, podemos sofrer alguns impactos em nosso ambiente, lógicamente estes impactos não tem a finalidade de prejudicar o funcionamento deste ambiente, ao contrário, eles existem para possibilitar outras formas de se trabalhar de acordo com o volume de informações existentes, sobre um determinado banco de dados.

Existem três modelos de recuperação: simples, completo e bulk-logged. Geralmente, um banco de dados usa o modelo de recuperação completa ou o modelo de recuperação simples. A Tabela 3 a seguir resume esses modelos de recuperação:

Modelo de recuperação Descrição Exposição à perda de trabalho Recuperação pontual?
Simples Sem backups de log

Reclama espaço de log automaticamente para manter requisitos de espaços pequenos, eliminando essencialmente a necessidade de gerenciar o espaço de log de transações.

As alterações desde o backup mais recente estão desprotegidas. No caso de um desastre, essas alterações devem ser refeitas. Só pode recuperar até o fim de um backup.
Completo Requer backups de log.

Nenhum trabalho é perdido devido a um arquivo de dados perdido ou danificado.

Pode executar uma recuperação pontual (por exemplo, antes de um erro de aplicativo ou usuário).

Geralmente nenhum.

Se a parte final do log estiver danificada, as alterações desde o backup de log mais recente deverão ser refeitas. Para obter mais informações, consulte Backups da parte final do log.

Pode executar uma recuperação pontual, supondo que seus backups estejam concluídos até aquele ponto. Para obter mais informações, consulte Restaurando um banco de dados para um ponto em um backup.
Bulk-logged Requer backups de log.

Um suplemento do modelo de recuperação completa que permite operações de cópia em massa de alto desempenho.

Reduz o uso de espaços de log usando o mínimo de registro em log para a maioria das operações em massa.

Se o log estiver danificado ou se ocorreu registro de operações em massa desde o backup de log mais recente, as alterações desde o último backup deverão ser refeitas.

Caso contrário, nenhum trabalho será perdido.

Pode recuperar até o final de qualquer backup. Não há suporte para recuperação pontual.

Tabela 3 – Modelos de Recuperação de Banco de Dados.

Como temos a necessidade de definir o ambiente o mais verídico possível, vamos trabalhar com o Modelo de Recuperação Completa(Full) que nos permite trabalhar e manter o Log de Transações de um Banco de Dados, para isso, vamos utilizar o Código 2 a seguir:

Código 2 — Alterando o Recovery Model para Full – Mantendo o Log de Transações –

ALTER DATABASE MyDatabaseDesastre
SET RECOVERY FULL
Go

Nosso banco de dados, foi definido com o Modelo de Recuperação Completa, sua estrutura esta totalmente criada e configurada para nosso cenário, o próximo passo é confirmar as configurações do nosso banco de dados MyDatabaseDesastre, onde nos depararemos com mais um conjunto de informações sobre banco de dados, os chamados Estados de Banco de Dados.

Antes de apresentar o Código 3, quero destacar os Estados existentes para qualquer Banco de Dados armazenado no Microsoft SQL Server, através da Tabela 4, apresentada abaixo:

Estados de banco de dados

ONLINE O banco de dados está disponível para acesso. O grupo de arquivos primário está on-line, embora a fase desfazer de recuperação pode não ter sido completada.
OFFLINE O banco de dados está indisponível. Um banco de dados se torna off-line por ação explícita do usuário e permanece off-line até que uma ação adicional do usuário seja executada. Por exemplo, o banco de dados pode ser ficar off-line para que um arquivo seja movido para um novo disco. O banco de dados é, então, colocado on-line novamente, após a mudança ter sido concluída.
RESTORING Um ou mais arquivos do grupo de arquivos primário está sendo restaurado ou um ou mais arquivos secundários está sendo restaurado off-line. O banco de dados está indisponível.
RECOVERING O banco de dados está sendo recuperado. O processo de recuperação é um estado transitório, o banco de dados ficará on-line automaticamente se a recuperação for bem-sucedida. Se a recuperação falhar, o banco de dados se tornará suspeito. O banco de dados está indisponível.
RECOVERY_PENDING SQL Server encontrou um erro relacionado a recurso durante a recuperação. O banco de dados não está danificado, mas arquivos podem ter sido perdidos ou limitações de recursos do sistema podem estar impedindo sua inicialização. O banco de dados está indisponível. Uma ação adicional é exigida do usuário para resolver o erro e permitir que o processo de recuperação seja concluído.
SUSPECT Pelo menos o grupo de arquivos primário é suspeito e pode estar danificado. O banco de dados não pode ser recuperado durante a inicialização de SQL Server. O banco de dados está indisponível. Ação adicional pelo usuário é exigida para resolver o problema.
EMERGENCY O usuário alterou o banco de dados e definiu o estado como EMERGENCY. O banco de dados está em modo de usuário único e pode ser reparado ou restaurado. O banco de dados está marcado como READ_ONLY, o log está desabilitado e o acesso é limitado aos membros da função de servidor fixa sysadmin. EMERGENCY é usado principalmente para fins de solução de problemas. Por exemplo, um banco de dados marcado como o suspeito pode ser definido como o estado EMERGENCY. Isso permitiria o acesso somente leitura do administrador de sistema ao banco de dados. Apenas membros da função de servidor fixa sysadmin podem definir um banco de dados como o estado EMERGENCY.

Tabela 4 – Relação de Estados de Bancos de Dados.

Com todos os estados de bancos de dados apresentados anteriormente, vamos agora utilizar o Código 3, para obter as informações no nosso Banco de Dados MyDatabaseDesastre, dentre elas:

  • Status Database – Estados de Banco de Dados;
  • Recovery Model – Modelo de Recuperação;
  • UserAccess – Forma de Acesso; e
  • Version – Versão do Banco de Dados.

Código 3 – Utilizando a System Function DATABASEPROPERTYEX() –

SELECT DATABASEPROPERTYEX(‘MyDatabaseDesastre’, ‘Status’) As Status,
DATABASEPROPERTYEX(‘MyDatabaseDesastre’, ‘Recovery’) As ‘Modelo de Recuperação’,
DATABASEPROPERTYEX(‘MyDatabaseDesastre’, ‘UserAccess’) As ‘Forma de Acesso’,
DATABASEPROPERTYEX(‘MyDatabaseDesastre’, ‘Version’) As ‘Versão’
Go

Observe que estamos utilizando uma Função de Sistema chamada DatabasePropertyEx, sendo ela, responsável em coletar e apresentar informações sobre as Configurações e Opções relacionadas a Banco de Dados.

Pois bem, com o ambiente criado e definido para nossas necessidades, a Parte 1 desta série já esta concluída, podemos avançar para Parte 2, que consiste realmente na Simulação da Falha em nosso Banco de Dados MyDatabaseDesastre.

Por enquanto o meu muito obrigado, acredito a sua visita e atenção, na próxima semana eu volto com a segunda parte desta série.

Até mais.

About these ads

7 comentários sobre “Simulando – Desastre e Recuperação de Bancos de Dados – Microsoft SQL Server 2008 R2 e 2012 – Parte 1.

  1. Olá Junior,
    Estava montando nesses dias um plano de Disaster recovery, e certamente, todas essas suas informações irão auxiliar bastante.

    Parabéns pelos posts, sempre ajudando a comunidade.

    Abraços.

Deixe um comentário

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