Arquivo da tag: Log

#10 – Para que serve


O louco meu, pleno feriadão e você esta passando por aqui no meu blog……

Que legal, sensacional, fico honrado com a sua ilustre visita, seja bem – vindo mais uma vez ao meu blog, espero que você consiga encontrar o que esta procurando ou algo que possa lhe agradar.

Este é mais um post da sessão Para que serve, lançada no início de 2016 e que esta chegando ao post de número 10, isso mesmo estamos no décimo post dedicado a esta sessão que aos poucos esta conseguindo se tornar uma referência de conhecimento diferenciado no meu Blog.

É isso ai, após esta tradicional saudação, chegou a hora de falar sobre o #10 – Para que serve de hoje, tenho a certeza que você vai gostar….


Introdução

Como você já deve ter percebido os posts relacionados a esta sessão tem o objetivo de apresentar e em alguns casos demonstrar como exemplos de código, aplicativos, utilitários, entre outros elementos envolvidos a banco de dados ou gerenciadores de bancos de dados dentro eles o Microsoft SQL Server podem ser utilizados para se obter uma possível solução de um problema, como em outros casos orientar na sua forma de utilização.

Para o post de hoje vou destacar um script que utilizei recentemente e posso dizer que foi de grande ajuda, mas antes de apresentar este recurso vou destacar um pouco sobre alguns elementos relacionados a ele, dentre os quais destaco File Growth.

File Growth

E ai você já ouviu falar file growth, ou simplesmente crescimento de arquivo de dados ou log? Se você é um administrador de banco de dados, ou um profissional que já trabalha a algum tempo com o banco de dados, tenho a certeza que já deve ter ouvido falar sobre a importância de se saber como esta configurado o fator de crescimento de um banco de dados e seu arquivos de transações.

Trata-se de uma configuração que pode ser aplicada durante a criação de um banco de dados ou posteriormente, sua importância esta totalmente relacionada ao espaço de armazenamento de dados durante sua utilização, o que poderá impactar na capacidade física de uma unidade de disco em gerenciar o quanto estes arquivos podem consumir e alocar espaço em disco no decorrer do seu tempo de vida.

Ao definir a forma de crescimento ou até mesmo o quanto este arquivo poderá ou não crescer de forma ilimitado o Microsoft SQL Server vai trabalhar no processo de alocação, escrita e manipulação da estrutura física e lógica tanto para os arquivos de dados, como principalmente para os arquivos de log.

Justamente sendo estes os arquivos que normalmente consomem um grande espaço física das unidades de disco para catalogar todas as operações processadas em um banco de dados que devem ser registradas em sua estrutura.

Para este tipo de cenário os gerenciadores de banco de dados através de seu mecanismo de Storage Engine observam e monitoram o que esta sendo processado e armazenado dentro de cada arquivo, caso o mesmo tenho que crescer para alocar uma nova área é com base nas configurações de File Growth definidas para o respectivo arquivo que este crescimento poderá ser realizado em fatores de Kilobytes, Megabytes, Gigabytes ou até mesmo em valores de porcentagem.

#10 Para que serve – Obtendo informações sobre database filegrowth —

Agora que conhecemos um pouco que esta relacionada com este post, vamos então conhecer este script que poderá nos ajudar a obter todas as possíveis informações relacionadas ao fator de crescimento de nossos bancos de dados e suas respectivas estruturas de dados e log.

— Bloco de Código —

filegrowth

Muito bem, observe que este código é bastante simples, estamos basicamente fazendo uso das catalogs views existentes no Microsoft SQL Server desdes suas primeiras versões o que nos permite dizer que este bloco de código pode ser aplicado facilmente a partir da versão 2005 em qualquer nível de edição, além disso, o mesmo já foi testado e aprovado nas últimos duas edições 2014 e 2016.

Após executarmos o bloco de código apresentando anteriormente, o Management Studio deverá retornar um conjunto de colunas e valores similares ao apresentado na Figura 1 apresentada abaixo:

filegrowth1Figura 1 – Relação de bancos de dados e informações sobre o filegrowth.

Podemos notar a existência das colunas AutoGrowthStatus, GrowthValue e GrowthIncrement, são justamentes estas as colunas que nos permitem encontrar as informações relacionadas aos fatores de crescimento configurados para cada banco de dados armazenado em nosso servidor ou instância de bancos de dados Microsoft SQL Server.

Falando um pouco sobre estas três colunas é possível observar:

AutoGrowthStatus: Esta coluna apresenta o status da propriedade Auto Growth, sendo esta definida para informar e o arquivo deverá ou não crescer de forma automática.

GrowthValue: Apresenta que pode ser informado a partir de 0 (zero) que indica ao Microsoft SQL Server que o determinado banco de dados não deverá crescer. Os demais valores podem representar uma indicação de crescimento em tamanho fixo ou até mesmo em porcentagem.

GrowthIncrement: Mostra a forma de incremento do fator de crescimento do banco de dados, sendo orientado e calculado através do número de páginas de dados, se o valor apresentado for igual á 0 (zero) significa que este banco de dados não terá seu crescimento realizado, qualquer outro valor acima de 0 (zero) significa que este banco de dados será impactado em algum momento pelo valor definido nas configurações do crescimento do banco de dados. Vale ressaltar que este valor esta relacionado ao tamanho de 8Kb (Kilobytes) para cada página de dados.

Após esta análise posso dizer que fica mais fácil descobrir qual banco de dados poderá apresentar problemas de crescimento acima no normal ou simplesmente aquele banco de dados que necessita crescer além do estimado.

Referências

https://technet.microsoft.com/pt-br/library/ms181338(v=sql.110).aspx

https://msdn.microsoft.com/en-us/library/ms178534.aspx

https://msdn.microsoft.com/en-us/library/bb522469.aspx

Links

Caso você ainda não tenha acessado os posts anteriores desta sessão, fique tranquilo é fácil e rápido, basta selecionar uns dos links apresentados a seguir:

https://pedrogalvaojunior.wordpress.com/2016/08/06/07-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2016/08/06/07-para-que-serve/

Conclusão

Administrar um banco de dados não é uma tarefa das mais complicadas do mundo, mas quando se referimos em administratar um servidor de banco de dados ou conjunto de servidores de bancos de dados o cenário com certeza muda bastante.

Foi pensando neste tipo de situação que compartilhei com vocês hoje este script no #10 – Para que serve, que apresenta como podemos de maneira fácil, rápida, segura e muito prática encontrar informações relacionadas ao file growth, ou simplesmente fator de crescimento.

Considerada uma das configurações mais importantes de um qualquer banco de dados alocado em uma instância ou servidor Microsoft SQL Server.

Agradecimentos

Mais uma vez obrigado por sua visita, agradeço sua atenção, fique a vontade para enviar suas críticas, sugestões, observações e comentários.

Até mais.

Anúncios

Material de Apoio – Junho – 2014


Boa tarde,

“We are one”, opa, opa, estava pensando na música da Copa 2014, mas na verdade 2014 é a nova Versão do Microsoft SQL Server, poxa vida, parece que tem alguma relação.

Eu acredito que sim!!! Então galera, mês de Copa, com o nosso Brasil praticamente passando por um período de Ebulição com este grande evento, com certeza uma grande oportunidade para quem gosta de Futebol como eu, acompanhar este tão fantástico e fascinante esporte.

Mas voltando a realidade, como de costume e seguindo a tradição, quero compartilhar com vocês alguns dos novos Scripts e Códigos Transacti-SQL que venho utilizando recentemente, estou me referindo a mais um Material de Apoio, sessão do meu blog com maior destaque no que se diz respeito a compartilhamento de arquivo.

Na relação de hoje, você vai poder encontrar Códigos e Scripts sobre:

  • Comando While;
  • Concatenação de Linhas;
  • CTE;
  • Driver ACE.OLEDB 12.0;
  • Estatísticas com Esperas Cumulativas;
  • Função Identity no Select Into;
  • Função Rank;
  • Função Row_Numver;
  • Histórico de Execução de Jobs;
  • Numeração de Registros;
  • Operador Constant Scan;
  • Recuperação de Dados e Tabelas Excluídas;
  • Relação de Serviços e Servers; e
  • Variáveis.

Fique a vontade para compartilhar estes arquivos, seguindo as regras do WordPress.com, estes arquivos estão renomeados com a extensão .doc:

Exemplo – Adicionando – Driver – ACE.OLEDB.12.0 – Instância – SQL Server.sql

Exemplo – Alterando – Numeração de Registros – Analisando – Condição.sql

Exemplo – Atualizando – Registros – Utilizando CTE + Função Rank.sql

Exemplo – Avançado – Recuperando – Dados – Excluídos – Através do Backup de Log.sql

Exemplo – Avançado – Recuperando – Tabela – Excluída – Através do Backup de Log.sql Exemplo – Estatísticas – Esperas Cumulativas.sql

Exemplo – Informando o Collate dentro do Select.sql

Exemplo – Obtendo – Relação – Histórico – Execução – Jobs.sql

Exemplo – Retornando – Relação – SQL Server – Serviços ou Servers.sql

Exemplo – Simulando – Operador – Constant – Scan.sql

Exemplo – Utilizando – Função – Identity no Select Into.sql

Exemplo – Utilizando – Função – Row_Number e Rank.sql

Exemplo – Utilizando Variável Table + While + Concatenando Linhas.sql

Nos encontramos em breve…

Simulando – Minimal Logging – Microsoft SQL Server 2008 R2 e 2012.


Galera, bom dia.

Tudo bem?

Gostaria de compartilhar com vocês neste artigo um conceito que coloquei em prática há alguns dias. Estou me referindo ao chamado Minimal Logging, fiquei muito surpreso ao usar esta prática, algo que me ajudou muito a entender um pouco mais sobre o processo de geração de Logs de Transações em um banco de dados.

Vou então tentar apresentar de uma forma simples esta prática, como também, os possíveis cenários de uso, cuidados que devemos ter, quais operações e operadores podem ser utilizadas.

 

Introdução

O Minimal Logging(Log Mínimo), consiste basicamente em orientar o Microsoft SQL Server, a trabalhar com um uso restrito na geração de informações específicas que envolvam e recuperação de dados sem qualquer tipo de suporte a pontos específicos de restauração.

O uso do Minimal Logging só pode ser realizado com bancos de dados que apresentem os Modelos de Recuperação Simple ou Bulk – Logged. Podemos considerar o Minimal Logging com um contra ponto ao uso de outras práticas para armazenamento do Log de Transações, sua forma de trabalho simples permite a recuperação de informações de uma maneira mais rápida.

Considerado mais eficiente que o Full Logging, reduzindo a possibilidade que operações que envolvam um volume considerável de dados, venham a encher o log de transações.

Para aqueles que não conhecem os Modelos de Recuperação, abaixo apresento um breve resumo sobre os Modelos de Recuperação existentes no Microsoft SQL Sever.

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, 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, logicamente este impacto 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: Simple, 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 1 resume os 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.

Pode executar uma recuperação pontual, supondo que seus backups estejam concluídos até aquele ponto.
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 1 – Modelos de Recuperação de Banco de Dados.

 

Operadores e operações que podem ser utilizadas com o Minimally Logged

Quando pensamos em fazer uso do Minimal Logging, temos que ter em mente que somente algumas operações ou operadores podem ser utilizados para forçar a geração mínima de logs.

A seguir destaco a lista de operações que podem ser registradas de forma mínima no log quando estamos utilizando os Modelos de Recuperação Simple ou Bulk-Logged:

 

  • Bulk import operations (BCP, BULK INSERT e INSERT..SELECT)
  • SELECT INTO;
  • TRUNCATE;
  • INSERT SELECT a partir do Microsoft SQL Server 2008, pode ser manipulada e registrada;
  • Atualizações parciais que apresentem tipos de dados considerados de grande tamanho, o que façam uso da claúsula Write;
  • CREATE INDEX;
  • ALTER INDEX REBUILD;
  • DROP TABLE;
  • Partition Switch; e
  • Merge, caso a Trace Flag 610 esteja habilitada.

 

 

Pré-requisitos para uso do Minimal Logging (Log Mínimo):

Existem alguns pré-requisitos para prover e permitir o uso do Log Mínimo:

 

  • As tabelas não podem estar envolvidas com Replicação;
  • A claúsula TABLOCK deve ser declarada, especificando que um bloqueio compartilhado é usado na tabela realizada até o final da instrução, evitando possíveis locks de tabela, o que pode gerar em alguns momentos situações como: Dados Fantasmas ou Leitura Suja.

 

Comportamento do Minimal Logging

Como qualquer outro tipo de recurso e funcionalidade o Minimal Logging também apresenta um comportamento bastante diferente dependendo do ambiente, a seguir a Figura 1 apresenta um comparativo entre cenários que podem permitir uso do Minimal Logging ou Full Logging:

  • Tabelas com Índices Clusterizados ou Não – Clusterizados;
  • Tabelas que possuem Heap;
  • Tabelas com Dados existentes; e
  • Tabelas consideradas vazias.

Minimall-1

 

Figura 1 – Comparativo entre cenários de uso do Minimal Logging.

Observação: Vale ressaltar que quando uma tabela apresenta dados e faz uso pelo menos um Índice Clusterizado, por questão de boas práticas e integridades dos dados, o SQL Server por padrão vai fazer uso e forçar a escrita de log.

 

Utilizando o Minimal Logging – Cenário 1 – Table Heap

Pois bem, vamos começar a utilizar o Minimal Logging, neste Cenário 1, estaremos utilizando a seguinte configuração:

  • Banco de Dados: MinimalLogging;
  • Recovery Model: Bulk-Logged; e
  • Tabelas: TableWithHeap e TableAuxWithHeap.

 

Para montar o ambiente para o Cenário 1, devemos o utilizar o Código 1 apresentado abaixo:

 

— Código 1 – Criando o Cenário 1 –

— Criando o Banco de Dados —

Create Database MinimalLogging

Go

 

— Acessando o Banco de Dados —

Use MinimalLogging

Go

 

— Alterando o Modelo de Recuperação do Banco de Dados —

Alter Database MinimalLogging

Set Recovery Bulk_Logged;

Go

 

— Verificando a existência das Tabelas —

If Object_ID(‘TableWithPrimaryKey’) IS NOT NULL

Drop Table TableWithPrimaryKey;

 

If Object_ID(‘TableWithHeap’) IS NOT NULL

Drop Table TableWithHeap;

 

If Object_ID(‘TableAuxWithHeap’) IS NOT NULL

Drop Table TableAuxWithHeap;

Go

 

— Criando as Tabelas —

Create Table TableWithHeap

(Coluna1 INT, Coluna2 Char(6000), Coluna3 Char(2000) ) ;

 

Create Table TableAuxWithHeap

(Coluna1 INT, Coluna2 Char(6000), Coluna3 Char(2000) ) ;

Go

 

— Inserindo a Massa de Dados —

Declare @Contador Int

Set @Contador =1

 

While @Contador <=15000

Begin

Insert Into TableAuxWithHeap(Coluna1) Values (@Contador)

 

Set @Contador +=  1

End

Para ilustrar ainda mais a geração de informações para serem registradas em Log, você pode observar que utilizamos anteriormente um bloco de código com o comando While, sendo executando com base na condição de quantidade de registros.

 

Após realizarmos a inserção de registros na tabela auxiliar TableAuxWithHeap, o próximo passo é realizar a inserção desta mesma quantidade de registros na tabela TableWithHeap.

 

–Inserindo dados na Tabela TableWithHeap utilizando o Table Hint (TABLOCK) para forçar a geração do Minimal Logged—

Insert Into TableWithHeap With(TABLOCK)

Select * From TableAuxWithHeap

 

Pronto, todos inseridos e distribuídos entre nossas tables, o próximo passo é verificar o registro de informações em nosso Transact-Log através da Function Fn_DBLog, conforme o bloco de código abaixo:

— Validando o Registro de Atividades no Log —

Select Top 10 operation As ‘Operation’,

Context  As ‘Contexto’,

[log record fixed length] As ‘Tamanho Fixo do Registro’,

[log record length] As ‘Tamanho do Registro de Log’,

AllocUnitId As ‘Unidade de Alocação’,

AllocUnitName As ‘Nome da Unidade de Alocação’

From fn_dblog(null, null)

Where allocunitname=’dbo.TableWithHeap’

Order By [Log Record Length] DESC;

Observe a coluna Tamanho do Registro de Log, ela apresenta o tamanho do nosso Log, conforme apresenta a Figura 2:

Minimall-2

 

Figura 2 – Coluna – Tamanho do Registro de Log.

Outra maneira de confirmarmos neste momento o tamanho do nosso Log, é consultar o local em que se encontra o nosso Banco de Dados MinimalLogging e observar o tamanho do arquivo de Log, neste caso, o MinimalLogging_Log.LDF, que apresenta 94,528 Kb, conforme ilustra a Figura 3:

Minimall-3

Figura 3 – Tamanho do Arquivo de Log em disco.

 

Espero que até aqui você tenha conseguido acompanhar e entender um pouco mais sobre o Minimall Logging, nossa próxima caminhada é com base Cenário 1, criado anteriormente, ilustrar como o Fully Logged poder ser ocasionado, para isso vamos utilizar o Código 2, apresentado abaixo:

–Inserindo dados na Tabela TableWithHeap utilizando o Table Hint (NOLOCK) para forçar a geração do Fully Logged—

 

Insert Into TableWithHeap

Select * From TableAuxWithHeap With(NOLOCK);

 

— Validando o Registro de Atividades no Log —

Select Top 10 operation As ‘Operation’,

Context  As ‘Contexto’,

[log record fixed length] As ‘Tamanho Fixo do Registro’,

[log record length] As ‘Tamanho do Registro de Log’,

AllocUnitId As ‘Unidade de Alocação’,

AllocUnitName As ‘Nome da Unidade de Alocação’

From fn_dblog(null, null)

Where allocunitname=’dbo.TableWithHeap’

Order By [Log Record Length] DESC;

Observe a coluna Tamanho do Registro de Log, ela apresenta o tamanho do nosso Log, conforme apresenta a Figura 4:

Minimall-4

 

 Figura 4 – Coluna – Tamanho do Registro de Log.

Outra maneira de confirmarmos neste momento o tamanho do nosso Log, é consultar o local em que se encontra o nosso Banco de Dados MinimalLogging e observar o tamanho do arquivo de Log, neste caso, o MinimalLogging_Log.LDF, que apresenta 138,496 KB, conforme ilustra a Figura 5:

Minimall-5

 

 Figura 5 – Tamanho do Arquivo de Log em disco.

Se você se lembra quando realizamos a primeira carga de dados inserindo 15.000 registros a coluna: Tamanho do Registro de Log e também o Tamanho Físico do Arquivo de Log, apresentavam valores bem menores. Sendo assim, podemos considerar a Table Hint NoLock utilizada em conjunto com o Select para inserção das outras 15.000 linhas de registro, aumentou de forma considerável o Tamanho do nosso Registro de Log e também do Arquivo.

A Figura 6 apresenta este comparativo e um gráfico para ilustrar estas diferenças:

Minimall-6

 

Figura 6 – Diferenças entre Minimally Logged e Fully Logged.

 

Basicamente tivemos um aumento de 68% no tamanho de nosso arquivo de MinimalLogging_Log.LDF, como também, nossos registros com tamanho fixo cresceram de forma espantosa ocupando um tamanho de 8104.

 

Utilizando o Minimal Logging – Cenário 2 – Table With Primary Key And Index Clustered

Pois bem, vamos começar a utilizar o Minimal Logging, neste Cenário 2, estaremos utilizando a seguinte configuração:

  • Banco de Dados: MinimalLogging;
  • Recovery Model: Bulk-Logged; e
  • Tabelas: TableAuxWithHeap e TableWithPrimaryKey.

 

Para montar o ambiente para o Cenário 1, devemos o utilizar o Código 1 apresentado abaixo:

 

— Código 1 – Criando o Cenário 2 –

— Criando o Banco de Dados —

Create Database MinimalLogging

Go

 

— Acessando o Banco de Dados —

Use MinimalLogging

Go

 

— Alterando o Modelo de Recuperação do Banco de Dados —

Alter Database MinimalLogging

Set Recovery Bulk_Logged;

Go

 

— Verificando a existência das Tabelas —

If Object_ID(‘TableAuxWithHeap’) IS NOT NULL

Drop Table TableAuxWithHeap;

Go

 

— Criando as Tabelas —

Create Table TableAuxWithHeap

(Coluna1 INT, Coluna2 Char(6000), Coluna3 Char(2000) ) ;

 

Create Table TableWithPrimaryKey

(Coluna1 INT Primary Key, Coluna2 Char(6000), Coluna3 Char(2000));

Go

 

–Inserindo dados na Tabela TableWithPrimaryKey utilizando o Table Hint (NOLOCK) para forçar a geração do Fully Logged—

Insert Into TableWithPrimaryKey

Select * From TableAuxWithHeap With(NOLOCK);

Go

Muito bem, realizamos mais este inserção de 15.000 linhas de registros, só que agora trabalhando na Tabela TableAuxWithHeap que possui em sua estrutura um campo como chave primária e automaticamente um índice Clusterizado.

Observe o valor apresentado na coluna Tamanho do Registro de Log, conforme apresenta a Figura 7. Nesta nova operação só que agora na Tabela TableWithPrimaryKey, nos apresenta um valor bem acima dos dados apresentados nos comparativos anteriores.

Vale ressaltar que esta tabela também estava vazia, mas a mesma apresenta uma chave primária e índice clusterizado, por isso, o SQL Server vai trabalhar com a escrita de Log.

Minimall-7

 

 Figura 7 – Coluna – Tamanho do Registro de Log.

Como destaquei anteriormente toda transação realizada em uma tabela que possua Chave Primária, será armazenada em Transact – Log e desta forma, estaremos trabalhando com Fully Logged, fazendo então que o nosso arquivo de log sofra um crescimento, conforme apresenta a Figura 8 a seguir:

Minimall-8

 

Figura 8 – Tamanho do Arquivo de Log em disco.

 

Fica fácil observar e entender que quando estamos trabalhando com Minimal Logging o SQL Server vai tentar de todas formas gerar e armazenar o menos possível de informações em nosso arquivo de log, mas em alguns casos isso não é possível por questões do funcionamento padrão do SQL Server e garantia do dado que esta sendo armazenado.

Mesmo trabalhando com tabelas vazias o Minimal Logging criou um volume bem pequeno de informações para escrita no arquivo de Log.

Vou encerrar este post, deixando o último comparativo que realizei analisando o processo de inserção de registros em uma Heap Table em comparação a inserção em uma Tabela com Índice Clusterizado e Chave Primária, conforme apresenta a Figura apresentada abaixo:

Minimall-9

 

 Figura 9 – Diferenças entre Minimally Logged e Fully Logged em Empty Table.

 

Conclusão

Podemos dizer que o Minimal Logging é uma prática que possui o objetivo de gerar o menos possível de leitura e escrita nos arquivos de log, ocupando menos espaço em disco e menor tempo para backup e restauração de dados, em contra partida não nos permite realizar a restauração de dados com base em pontos específicos.

 

Espero que você tenha gostado de post.

Mais uma vez obrigado por sua atenção e visita.

Até mais.

Utilizando Backup de Filegroup no SQL Server – Parte III


Pessoal, bom dia.

Estou de volta com a minha série de artigos sobre Backup de Filegroup no SQL Server, hoje vou postar a terceira parte desta série, espero que todos possam estar gostando e que este material.

Nesta terceira parte, vamos começar a criar nosso ambiente de trabalho, posteriormente utilizado em nossos testes:

Criando o ambiente

Após conhecermos um pouco sobre os principais termos e modelos de recuperação de banco de dados relacionados á Backup de Filegroup, vamos começar a montar nosso ambiente de trabalho.

Conforme destaquei anteriormente, daremos ínicio através da criação do Banco de Dados, chamado SQL, conforme apresenta a Listagem 1. Importante destacar que esta banco de dados esta utilizando o Modelo de Recuperação Completo (Recovery Model Full) e seu log de transações será mantido mesmo após a realização dos backups.

Listagem 1. Criação do Banco de dados

 — Bloco 1 —

CREATE DATABASE SQL

ON PRIMARY

(NAME = SQL_Dados,

FILENAME = N’C:\SQL\SQL_Dados.mdf’,

SIZE = 10MB,

MAXSIZE =50MB,

FILEGROWTH =10%),

FILEGROUP Secondary

(NAME = SQL_Secondary_Dados,

FILENAME = N’C:\SQL\SQL_Secondary_Dados.ndf’,

SIZE = 10MB,

MAXSIZE = 50MB,

FILEGROWTH = 10%)

LOG ON

(NAME = SQL_Log,

FILENAME = N’C:\SQL\SQL_Log.ldf’,

SIZE = 10MB,

MAXSIZE = 50MB,

FILEGROWTH = 10%)

GO

A Figura 5 apresenta em forma gráfica a distribuição dos arquivos e seus respectivos filegroups, para obter estas informações em linha de comando, utilize as systems views: sys.sysfiles ou sys.sysfilegroups no SQL Server 2005 ou 2008.

Figura 5. Estrutura de Filegroups que compõem o Banco de Dados SQL.

 Agora vamos criar as tabelas Produtos e Armazens, posteriormente criaremos seus respectivos índices e iremos distribuí-las no Filegroup Primary, conforme apresenta a Listagem 2. Os índices criados nestas tabelas, possuem o efeito de demonstração ou mera ilustração, não estamos se preocupando com questões de performance ou estatísticas de utilização.

Listagem 2. Criação das Tabelas Produtos e Armazens

— Bloco 1 —

Create Table Produtos

(Codigo SmallInt Not Null,

Descricao VarChar(60) Not Null,

Marca VarChar(20) Not Null,

DataFabricacao DateTime Null,

DataValidade DateTime Null,

CodArmazem SmallInt Not Null ) On [Primary]

Go

— Bloco 2 —

Alter Table Produtos

Add Constraint [PK_Codigo_Produto] Primary Key Clustered (Codigo) On [Primary]

Go

— Bloco 3 —

Create NonClustered Index IND_Descricao_Produtos on Produtos(Descricao) On [Primary]

Go

— Bloco 4 —

Create Table Armazens

(Codigo TinyInt Not Null,

Descricao VarChar(20),

Situacao Char(1)) On [Primary]

Go

— Bloco 5 —

Alter Table Armazens

Add Constraint [PK_Codigo_Armazem] Primary Key Clustered (Codigo) On [Primary]

Go

— Bloco 6 —

Create Unique NonClustered Index UIND_Descricao_Armazem On Armazens(Descricao) ON [Primary]

Go

Neste momento nossas tabelas Produtos e Armazens estão criadas, através da system stored procedure SP_HELP, vamos consultar a estrutura de informações que compõem a tabela Produtos, dentre estas informações, destacamos o filegroup ao qual esta tabela esta armazenada. Sendo este o filegroup Primary, para isso utilizaremos a Listagem 3, e podemos observar o retorno dos dados, conforme apresenta a Figura 6.

Listagem 3. Consultando as informações das tabelas Produtos e Armazens.

— Bloco 1 —

SP_HELP ‘Produtos’

Figura 6. Informações sobre a tabela Produtos armazenada no filegroup Primary.

Após consultarmos as informações estas a tabela Produtos, vamos então realizar a inserção de uma pequena massa de dados em
ambas as tabelas. Esta massa de dados será utilizada posteriormente. Para realizar a inserção dos dados,  utilizaremos a Listagem 4.

Listagem 4. Inserção de dados na tabela Armazens

— Bloco 1 —

USE SQL

Go

— Bloco 2 —

Insert Into Armazens (Codigo, Descricao, Situacao) Values(1,’Matriz A’, ‘A’)

Insert Into Armazens (Codigo, Descricao, Situacao) Values(2,’Matriz B’, ‘A’)

Insert Into Armazens (Codigo, Descricao, Situacao) Values(3,’Filial 1′, ‘A’)

Insert Into Armazens (Codigo, Descricao, Situacao) Values(4,’Filial 2′, ‘A’)

Go

Inserimos as 4 linhas de dados na tabela Armazens, vamos agora inserir 2000 (duas mil) linhas de registros na tabela Produtos,
conforme apresenta a Listagem 5.

Listagem 5. Inserção de dados na tabela Produtos

— Bloco 1 —

Declare @Codigo Int,  @CodArmazem SmallInt

Set @Codigo=1

While @Codigo <=2000

Begin

If @Codigo <=500

Set @CodArmazem=1

Else If (@Codigo >500 And @Codigo <=1000)

Set @CodArmazem=2

Else If (@Codigo >1000 And @Codigo <=1500)

Set @CodArmazem=3

Else Set @CodArmazem=4

Insert Into Produtos(Codigo, Descricao, Marca, DataFabricacao, DataValidade, CodArmazem)

Values(@Codigo,’Produto nº:’+CONVERT(VarChar(4),@Codigo), ‘SQL’,GETDATE(),GETDATE()+@Codigo, @CodArmazem)

Set @Codigo=@Codigo+1

End

Go

Pronto acabamos de inserir 2000(duas mil) linhas de registros na tabela Produtos, nossa primeira parte do ambiente esta pronta. Podemos criar as outras duas tabelas Estocagem e Saldos, que serão armazenadas na filegroup Secondary conforme apresenta a Listagem 6.

Listagem 6. Criando as tabelas Estocagem e Saldos

— Bloco 1 —

Create Table Estocagem

(CodEstocagem Int Identity(1,1),

CodProduto SmallInt Not Null,

CodArmazem TinyInt Not Null,

Quantidade Float Not Null) On [Secondary]

Go

— Bloco 2 —

Alter Table Estocagem Add Constraint [PK_CodEstocagem_Estocagem] Primary Key Clustered (CodEstocagem) On [Secondary]

Go

— Bloco 3 —

Create NonClustered Index IND_CodProduto_Estocagem  On Estocagem(CodProduto) On [Secondary]

Go

— Bloco 4 —

Create Table Saldos

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

CodProduto SmallInt Not Null,

SaldoProduto Float) On  [Secondary]

Go

Desta forma, começamos a entender um pouco mais sobre o função e finalidade em se trabalhar com mais de um filegroup, neste momento, nosso ambiente esta dividir fisicamente, onde as tabelas Produtos e Armazens estão armazenadas no filegroup Primary e as tabelas Estocagem e Saldos estão armazenadas no filegroup Secondary.

Caso um dos filegroups apresente alguma falha, as tabelas armazenadas no outro filegroup não sofreram nenhum impacto. Agora todas as tabelas estão criadas e armazenadas em seus respectivos filegroups, iremos executar o mesmo
procedimento realizado anteriormente, através da system stored procedure SP_HELP, consultaremos a estrutura de informações da tabela Saldos, podendo confirmar que esta tabela armazenada e vínculada fisicamente ao filegroup Secondary, conforme apresenta a Figura 7.

Figura 7. Informações sobre a tabela Produtos armazenada no filegroup Secondary.

Bom pessoal, vou encerrar esta terceira sessão aqui, com o nosso ambiente pronto e preparado para começarmos os testes. Na próxima parte vamos iniciar a aplicação e execução dos Backups de Filegroup, demonstrando o que é necessário para realizar este tipo de procedimento.

Agradeço a sua visita, nos encontramos em breve, um bom feriado a todos.

Até mais.