Obtendo informações sobre Modelo de Recuperação, Contadores de Desempenho, Nível de Compatibilidade de Banco de Dados por Instância no Microsoft SQL Server 2005, 2008 e 2008 R2.

Pessoal, boa tarde.

Tudo bem? Quais as novidades?

No artigo de hoje, vou destacar como podemos obter algumas das principais informações de um banco de dados, como por exemplo: Modelo de Recuperação e Nível de Compatibilidade, de forma centralizada o que facilita em muito a vida do Administrador de Banco de Dados.

Como de costume, antes de apresentar a parte prática deste artigo, vou abordar um pouco sobre cada um dos conceitos que envolvem o Script utilizado para a obtenção destas informações, começando pelo Recovery Model conhecido como 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, logicamente estes impactos não têm 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 1 a seguir 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. 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 1. Resumo dos modelos de recuperação de Banco de Dados.

O modelo de recuperação apropriado para um banco de dados depende dos requisitos de recuperação e disponibilidade do banco de dados. A seguir, destacaremos um pouco sobre cada modelo de recuperação de banco de dados, suas formas de utilização, benefícios ou desvantangens.

Modelo de recuperação simples

O modelo de recuperação simples minimiza a sobrecarga administrativa do log de transações, pois não é feito backup no log de transações. O modelo de recuperação simples corre o risco de exposição à perda significativa de trabalho se o banco de dados estiver danificado.

Os dados são recuperados até o backup mais recente dos dados perdidos. Portanto, no modelo de recuperação simples, os intervalos de backup devem ser curtos o bastante para evitar a perda de quantidades significativas de dados. No entanto, os intervalos devem ser suficientemente longos para impedir que a sobrecarga de backup afete o trabalho de produção. A inclusão de backups diferenciais na estratégia de backup pode ajudar a reduzir a sobrecarga.

Geralmente, para um banco de dados de usuário, o modelo de recuperação simples é útil para bancos de dados de teste e desenvolvimento ou para bancos de dados que contém os dados principalmente somente leitura, como um data warehouse.

O modelo de recuperação simples não é adequado para sistemas de produção onde a perda de alterações recentes é inaceitável. Em tais casos, recomenda-se o uso do modelo de recuperação completa.

 

Modelo de recuperação completa

O modelo de recuperação completa fornece maior proteção aos dados do que o modelo de recuperação simples. Esse modelo de recuperação depende da execução do backup do log de transações para fornecer recuperação completa e impedir a perda de trabalho em intervalos mais amplos de cenários de falha.

Este modelo fornece o chamado modelo de manutenção de banco de dados normal.  Para os bancos de dados em que a durabilidade das transações é necessária.  Ele registra completamente todas as transações e retém os registros de log de transações depois que seus backups são feitos.

Outras características presentes neste modelo relacionam-se com a possibilidade de recuperação de um banco de dados até o ponto que ocorreu a falha. Desta forma, é possível realizar o backup do log mesmo após a ocorrência de uma falha. Com a vantagem de oferecer suporte à restauração de páginas de dados individuais.

 

Modelo de recuperação bulk-logged

Esse modelo de recuperação registra as operações em massa, realizadas com base em um determinado banco de dados. Sua única função é ser um suplemento do modelo de recuperação completa.

Os modelos de recuperação completa e bulk-logged, os backups de log são essenciais. Se você não quiser fazer backups de log, use o modelo de recuperação simples.

 

Níveis de Compatibilidade

Define certos comportamentos de banco de dados como sendo compatíveis com a versão especificada do SQL Server. Para todas as instalações do SQL Server 2008, o nível de compatibilidade padrão é 100. Bancos de dados criados no SQL Server 2008 são definidos com esse nível a não ser que o banco de dados modelo tenha um nível de compatibilidade inferior.

Quando um banco de dados for atualizado para o SQL Server 2008 a partir de qualquer versão anterior do SQL Server, o banco de dados manterá seu nível de compatibilidade se ele for pelo menos 80. Atualizar um banco de dados com um nível de compatibilidade inferior a 80 definirá o banco de dados com o nível de compatibilidade 80.

O nível de compatibilidade afeta os comportamentos apenas do banco de dados especificado e não do servidor inteiro. Ele oferece apenas compatibilidade parcial com versões anteriores do SQL Server. Use o nível de compatibilidade como um auxílio de migração provisório ao trabalhar com diferenças de versões nos comportamentos que são controlados pela definição de nível de compatibilidade relevante.

 

Log Reuse Wait Description

Esta é uma das informações existentes na Catalog View Sys.Databases introduzida no Microsoft SQL Server 2005, que oferece um conjunto amplo de informações sobre os Bancos de Dados existentes no Servidor ou Instância SQL Server.

A Log Reuse Wait Description apresenta a forma de reutilização do espaço ocupado pelo arquivo de log de transações no Banco de Dados corrente, as possíveis formas de reutilização são:

  • NOTHING;
  • CHECKPOINT;
  • LOG_BACKUP;
  • ACTIVE_BACKUP_OR_RESTORE;
  • ACTIVE_TRANSACTION;
  • DATABASE_MIRRORING;
  • REPLICATION;
  • DATABASE_SNAPSHOT_CREATION;
  • LOG_SCAN; e
  • OTHER_TRANSIENT.

 

Page Verify Option Desc

Também presente na Catalog View Sys.Databases, a Page Verify Option Desc, retorna o conjunto de dados que descrevem a opção de verificação das páginas de dados que compõem o Banco de Dados. O conjunto de valores que podem ser apresentados nesta coluna, é limitado e consiste basicamente em:

  • NONE;
  • TORN_PAGE_DETECTION; e
  • CHECKSUM.

State_Desc

Outra importante informação presente na Sys.Databases, que possui a finalidade de retornar a descrição do Status atualmente do Banco de Dados. Dentre elas destaco:

  • ONLINE;
  • RESTORING;
  • RECOVERING;
  • RECOVERY_PENDING;
  • SUSPECT;
  • EMERGENCY; e
  • OFFLINE.

 

Informações complementares

As informações Log Size Kb, Log Used Kb e Log Size Percent, consistem basicamente no retorno de informações referentes aos Contadores de Desempenho existente em cada banco de dados.

Vale ressaltar que estas três colunas são criadas em tempo de execução, com base, no conjunto de valores existentes na Dynamic Management View (Visão de Gerenciamento Dinâmico) sys.dm_os_performance_counters, introduzida a partir do Microsoft SQL Server 2005.

A sys.dm_os_performance_counters apresenta as linhas referentes aos contadores de performance mantidos pelo Servidor ou Instância SQL Server.

 

Muito bem, agora que já conhecemos um pouco sobre cada um dos conceitos e informações que serão retornadas após o processamento do Script, podemos então apresentar a parte prática deste artigo.

 

Vou começar inicialmente apresentando o Script 1, que consiste na utilização do comando Select fazendo acesso a Catalog View Sys.Database, retornando algumas informações, conforme apresento abaixo:

— Script 1 – Consultando informações através da Catalog View Sys.Databases —

SELECT name,

recovery_model_desc,

log_reuse_wait_desc,

compatibility_level,

page_verify_option_desc,

State_Desc

FROM   sys.databases

 

Podemos notar que serão retornados pelo Script 1, informações do Nome, Descrição de Recovery Model, Nível de Compatibilidade, conforme apresenta a Figura 1.

DatabaseInfo-1

Figura 1 – Retorno de dados apresentados com a execução do Script 1.

 

No Script 2, vamos utilizar a sys.dm_os_performance_counters, para retornar os contadores de desempenhos existentes para cada objeto e banco de dados controlados e calculados pelo SQL Server. Além disso, estamos fazendo uma junção com a Sys.Databases para retornar somentes os contadores de Banco de Dados, conforme ilustra a Figura 2.

 

— Script 2 – Consultando a relação de contadores existentes na DMV sys.dm_os_performance_counters –

Select Sc.Instance_Name,

Sc.Counter_Name,

Sc.cntr_value,

Sc.cntr_type

From Sys.dm_os_performance_counters sc Inner Join Sys.Databases sd

on sc.instance_name = sd.name

Order By Sc.Instance_Name

DatabaseInfo-2

 

Figura 2 – Dados retornados pela DMV Sys.dm_os_performance_counters.

Neste momento, já podemos notar as formas de retorno dos dados por parte do SQL Server. Para facilitar a forma de consulta destes dados poderíamos automatizar de diversas maneiras processamento, através de Stored Procedure, Views e CTE, sendo assim, optei por automatizar este código tornando-o uma CTE (Common Table Expression), recurso que também foi introduzido a partir do SQL Server 2005.

Nesta parte do código, estarei criando duas CTEs, denominados:

  • DatabaseInfo: Conjunto de Dados retornados com base na View Sys.Databases;
  • DatabasePerfCounters: Conjunto de Dados retornados através da junção da DMV Sys.dm_os_performance_counters e View Sys.Databases.

Vou denominar esta parte de código como Script 3, sendo que, o mesmo será dividido em duas partes:

— Script 3 – Parte 1 – Criação das CTES – DatabaseInfo e DatabasePerfCounters —

;With DatabaseInfo (DatabaseName, RecoveryModel, LogReuseWaitDescription, CompatibilityLevel, PageVerifyOption, state_desc)

As

(SELECT db.name,

db.recovery_model_desc,

db.log_reuse_wait_desc,

db.compatibility_level,

db.page_verify_option_desc,

db.State_Desc

FROM    sys.databases AS db),

DatabasePerfCounters

As

(SELECT  db.DatabaseName As InstanceName,

ls.cntr_value As LogSizeKB,

lu.cntr_value As LogUsedKb,

CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) AS DECIMAL(18,2)) * 100 As LogSizePercent

FROM  sys.dm_os_performance_counters AS lu Inner Join DatabaseInfo db

ON lu.instance_name = db.DatabaseName

INNER JOIN sys.dm_os_performance_counters AS ls

ON ls.instance_name = db.DatabaseName

WHERE   lu.counter_name LIKE ‘Log File(s) Used Size (KB)%’

AND ls.counter_name LIKE ‘Log File(s) Size (KB)%’)

 

— Script 3 – Parte 2 – Execução das CTEs – DatabaseInfo e DatabasePerfCounters —

Select DatabaseName,

RecoveryModel,

LogReuseWaitDescription,

CompatibilityLevel,

PageVerifyOption,

State_Desc,

LogSizeKb,

LogUsedKb,

LogSizePercent

from DatabaseInfo db Inner Join DatabasePerfCounters DPC

On db.DatabaseName = DPC.InstanceName

 

Observe que as duas CTEs são criadas individualmente, mas existe um relacionamento entre elas para limitar o retorno de dados por parte dos Contadores de Desempenho obtidos na sys.dm_os_performance_counters, conforme apresenta a Figura 3.

DatabaseInfo-3

Figura 3 – Retorno de Dados após o processamento das CTES DatabaseInfo e DatabasePerfCounters.

 

Observando a Figura 3, temos a possibilidade de entender o retorna de dados que o SQL Server apresenta, trazendo de forma unificada o RecoveryModel, CompatibilityLevel, PageVerifyOption, Log Size, Log Used, etc.

Com isso, vou encerrando mais um artigo, espero que você possa ter gostado, e que as informações disponíveis neste material possam ser úteis e ajudar no seu dia – á – dia.

Mais uma vez obrigado por sua visita.

Nos encontramos nos próximos artigos.

Até mais.

Anúncios

Autor: Junior Galvão - MVP

Profissional com vasta experiência na área de Tecnologia da Informação e soluções Microsoft. Graduado no Curso Superior em Gestão da Tecnologia de Sistemas de Informação. Pós-Graduado no Curso de Gestão e Engenharia de Processos para Desenvolvimento de Software com RUP na Faculdade FIAP - Faculdade de Informática e Administração Paulista de São Paulo. Pós-Graduado em Gestão da Tecnologia da Informação Faculdade - ESAMC Sorocaba. Formação MCDBA Microsoft, autor de artigos acadêmicos e profissionais postados em Revistas, Instituições de Ensino e WebSistes. Meu primeiro contato com tecnologia ocorreu em 1995 após meus pais comprarem nosso primeiro computador, ano em que as portas para este fantástico mundo se abriram. Neste mesmo ano, comecei o de Processamento de Dados, naquele momento a palavra TI não existia, na verdade a Tecnologia da Informação era conhecida como Computação ou Informática, foi assim que tudo começou e desde então não parei mais, continuando nesta longa estrada até hoje. Desde 2001 tenho atuado como Database Administrator - Administrador de Banco de Dados - SQL Server em tarefas de Administração, Gerenciamento, Migração de Servidores e Bancos de Dados, Estratégias de Backup/Restauração, Replicação, LogShipping, Implantação de ERPs que utilizam bancos SQL Server, Desenvolvimento de Funções, Stored Procedure, Triggers. Experiência na Coordenação de Projetos de Alta Disponibilidade de Dados, utilizando Database Mirroring, Replicação Transacional e Merge, Log Shipping. Atualmente trabalho como Administrador de Banco de Dados no FIT - Instituto de Tecnologia da Flextronics, como também, Consultor em Projetos de Tunnig e Performance para clientes, bem como, Professor Titular na Fatec São Roque. Desde 2008 exerço a função de Professor Universitário, para as disciplinas de Banco de Dados, Administração, Modelagem de Banco de Dados, Programação em Banco de Dados, Sistemas Operacionais, Análise e Projetos de Sistemas, entre outras. Possuo titulação Oficial Microsoft MVP - SQL Server renovada desde 2007.

Um comentário em “Obtendo informações sobre Modelo de Recuperação, Contadores de Desempenho, Nível de Compatibilidade de Banco de Dados por Instância no Microsoft SQL Server 2005, 2008 e 2008 R2.”

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