Categorias
SQL Server Troubleshooting Virtual PASS BR

Erro ao instalar Cumulative Update

Recentemente decidi atualizar meu ambiente de homologação com os Services Packs e Cumulatives Updates disponíveis para cada versão do SQL Server. Quem nunca precisou atualizar com SP ou CU seu ambiente SQL Server? E quem nunca se deparou um erro inesperado na atualização? Se você ainda não precisou ou não se deparou com um erro, não se preocupe um dia precisará e passará por isso e esse procedimento tende a ser uma rotina na sua vida.

Pois bem, no meu caso não foi diferente, depois de atualizar mais da metade do meu ambiente, o lado negro da força resolveu aparecer e começar a falhar as minhas atualizações em um determinado cluster.

Eu já havia atualizado nesse mesmo ambiente o Service Pack 2 do SQL Server 2008 R2 e estava tentando atualizar para o Cumulative Update 1 quando os erros começam a aparecer.

Analisando o log de erro gerado que por padrão fica na pasta C:Program FilesMicrosoft SQL Server100Setup BootstrapLog<Data e Hora><Instance Name> encontrei a seguinte mensagem:

Error: Action “Microsoft.SqlServer.Configuration.ScoExtension.VerifiedCopyFileAction” threw an exception during execution.

Microsoft.SqlServer.Setup.Chainer.Workflow.ActionExecutionException: File c:Program FilesMicrosoft SQL ServerMSSQL10_50. <Instance Name>MSSQLBinnDllTmp64sqlagentctr100.dll could not be copied to file path C:Windowssystem32perf-MSSQL10_50. <Instance Name>-sqlagtctr.dll. —> Microsoft.SqlServer.Configuration.ScoExtension.CopyFileException: File c:Program FilesMicrosoft SQL ServerMSSQL10_50. <Instance Name>MSSQLBinnDllTmp64sqlagentctr100.dll could not be copied to file path C:Windowssystem32perf-MSSQL10_50. <Instance Name>-sqlagtctr.dll. —> Microsoft.SqlServer.Configuration.Sco.ScoException: The process cannot access the file ‘C:Windowssystem32perf-MSSQL10_50. <Instance Name>-sqlagtctr.dll’ because it is being used by another process. —> System.IO.IOException: The process cannot access the file ‘C:Windowssystem32perf-MSSQL10_50. <Instance Name>-sqlagtctr.dll’ because it is being used by another process.

Essas duas mensagens em negrito me chamaram atenção para tentar resolver meu problema.

Por que eu não consigo copiar o arquivo? Qual é o processo que está usando essa dll? No meu cluster, primeiramente atualizo os nós passivos, não existia nenhum acesso ou serviço rodando na maquina. Tentei reiniciar o servidor, executar com outro login e nada. Então resolvi dar uma pesquisada na internet e encontrei um post (link) bem simples que resolveu meu problema

Solução é renomear o arquivo o arquivo sqlagtctr.dll dentro da pasta C:Windowssystem32 e executar novamente o setup.

Foi simples de resolver meu problemas porem ainda estou sem saber o motivo do erro acontecer em apenas um ambiente.

Categorias
SQL Server Troubleshooting Virtual PASS BR

DatabaseMail error: ‘The service queue “ExternalMailQueue” is currently disabled.’

DatabaseMail é fundamental na vida de DBA e o envio de e-mail é de grande utilidade para monitar o seu ambiente SQL Server.

Em um dos meus ambientes, onde a funcionalidade de envio de e-mail está ativada há muito tempo, surpreendentemente parou de funcionar. Problemas com o DatabaseMail não são frequentemente encontrados, porem em quando estava analisando o errorlog dessa instancia me deparei com uma massa grande de mensagens iguais a essa:

The activated proc [dbo].[sp_sysmail_activate] running on queue msdb.dbo.ExternalMailQueue output the following: ‘The service queue “ExternalMailQueue” is currently disabled.’

Existe um problema conhecido quando o SQL Server 2005 ou SQL Server 2008 está rodando sobre o Windows Server 2003 SP2 ou Windows Server 2000 SP4. http://blogs.msdn.com/b/sqlserverfaq/archive/2010/03/18/database-mail-failing-with-the-service-queue-externalmailqueue-is-currently-disabled-message.aspx

Mas esse não era meu cenario, verificando as filas de mensagens do MSDB pude constatar que a fila “ExternalMailQueue” realmente estava desabilitada, ou seja, a mensagem de erro foi direta e auto explicativa.

QueueDisable

Usei o seguinte comando para reestabelecer a fila:

USE msdb

GO

ALTER QUEUE ExternalMailQueue WITH STATUS = ON

GO

Outra maneira para habilitar a fila seria clicar com o botão direito sobre ela e escolher a opção: ENABLE QUEUE.

Apos executar o comando, eu voltei a receber e-mails normalmente e a mensagem no errorlog sumiu.

Infelizmente não pude encontrar a causa raiz ou quem desabilitou essa fila…

Isso ai.. até a próxima!!

Categorias
SQL Server

Windows Server 2008 R2 com SQL Server 2008 R2

Algumas pessoas vem me perguntando se é possivel utilizar o SQL Server 2008 R2 com o Windows Server 2003, a minha resposta é sim, porém faço sempre a recomendação para utilizarem o Windows Server 2008 R2, por varios motivos que vou explicar agora.

A plataforma de banco de dados está intimamente relacionada com o sistema operacional. Devido a esta relação, a Microsoft criou o Windows Server 2008 R2 para fornecer uma base sólida de TI para aplicações críticas de negócios, como o SQL Server 2008 R2. A combinação dos dois produtos produz um conjunto impressionante. Com estes dois produtos, uma organização pode alcançar a máxima performance, escalabilidade, confiabilidade e disponibilidade, enquanto ao mesmo tempo reduzir o custo total de propriedade associado à sua plataforma de banco de dados

É uma das melhores práticas para alavancar Windows Server 2008 R2 como o operacional subjacente ao implantar o sistema SQL Server 2008 R2, pois os recursos novos e aprimorados do Windows Server 2008 R2 pode enriquecer a experiência de uma organização com o SQL Server 2008 R2. As novas capacidades que têm impacto direto sobre SQL Server 2008 R2 incluim:

Máxima escalabilidade: Windows Server 2008 R2 é capaz de alcançar o tamanho de carga de trabalho sem precedentes, escalabilidade dinâmica, e toda a disponibilidade e confiabilidade. Por exemplo, o Windows Server 2008 R2 suporta até 256 processadores lógicos e 2 terabytes de memória. Quando o SQL Server 2008 R2 é executado no Windows Server 2008 R2, os dois produtos juntos podem executar cargas de BI intensas em uma velocidade incrivel.

Melhorias no Hyper-V. Com base na aprovação e sucesso do original Hyper-V, Windows Server 2008 R2 oferece vários novos recursos para o plataforma Hyper-V para melhorar a experiência de virtualização do SQL Server.

Em primeiro lugar, a disponibilidade pode ser intensificada com a introdução de Live Migration, que faz possível mover máquinas virtuais com o SQL Server entre hosts Hyper-V, sem interrupção do serviço. (Isso é impressionante, a possibilidade de trocar o SQL Server para uma VM com mais recursos sem interromper o serviço e sem a percepção do usuario, faz o uso dessa ferramenta se tornar essencial)

Em segundo lugar, o Hyper-V pode fazer uso de até 64 processadores lógicos, que permite a consolidação de um maior número de VMs SQL Server em um único servidor Hyper-V.

Em terceiro lugar, Dynamic Virtual Machine Storage, um novo recurso, permite a adição de discos virtuais ou físicos a uma VM existente, sem exigir que VM seja reiniciada.

Windows Server 2008 R2 Server Manager.Server Manager foi otimizado no Windows Server 2008 R2. É normalmente usado para gerenciar e proteger várias funções do servidor através das instâncias do SQL Server que executam o Windows Server 2008 R2. O gerenciamento remoto de conexões com computadores remotos é alcançável com Server Manager. Server Manager também inclui um novo Best Practices Analyzer, que é uma ferramenta para denunciar violações de melhores práticas

Best Practices Analyzer (BPA). Essa ferramenta é um bom investimento pois ajuda a reduzir as violações de melhores práticas, que em última análise ajuda a corrigir e prevenir a deterioração no desempenho, escalabilidade e tempo de inatividade

Windows PowerShell 2.0. Windows Server 2008 R2 navega com o Windows Power Shell 2.0. Além de permitir que os DBAs executem comandos com Windows PowerShell em computadores remotos e executar comandos como os trabalhos de forma assíncrona, o Windows PowerShell 2 .0 inclue novos e melhorados cmdlets Windows Management Instrumentation (WMI), um recurso de depuração de script e um ambiente gráfico para a criação de scripts. Os DBAs podem melhorar a sua produtividade com o Windows PowerShell, simplificando, automatizando e consolidando as tarefas repetitivas e os processos de gerenciamento de servidor em um ambiente distribuído.

É isso ai pessoal, aproveitem o máximo os recursos do sistema operacional e tirem proveito disso para melhorar o SQL Server 2008 R2!

Espero que tenham gostado!

[]’s

Categorias
SQL Server

Melhorias para DBAs com o SQL Server 2008 R2 – Parte 2

Continuando com as novidades e melhorias para os DBA com o SQL Server 2008 R2, vou descrever um pouco sobre BI que é a grande aposta da Microsoft para futuro.

Na area de BI temos as seguintes melhorias:

Scalable Data Warehousing

Microsoft SQL Server 2008 R2 Parallel Data Warehouse é um data warehouse baseada na tecnoliga originalmente criada pela DATAllegro e adquirida pela Microsoft em 2008. SQL Server 2008 tem muitas características de apoiar a escalabilidade e o desempenho do Data Warehouse que Parallel Data Warehouse usa para sua vantagem. A combinação do SQL Server, utilizando a escalabilidade e desempenho, com um massively parallel processing (MPP), arquitetura de Parallel Data Warehouse, cria uma nova opção poderosa para hospedar um uma grande quantidade de dados.

Master Data Services

Master Data Services (MSD) ajuda as osempresas a padronizar dados que pessoas dependem para tomar decisões críticas de negócios. Com Master Data Services, as organizações de TI podem gerenciar os dados criticos de toda a empresa.

StreamInsight

Microsoft SQL Server StreamInsight é um processamento de eventos complexos (CEP – complex event processing). Esta tecnologia é uma nova oferta da família SQL Server, fazendo sua primeira aparição no SQL Server 2008 R2. StreamInsight é tanto um motor construído para processo de transferência de altos fluxos de dados com baixa latência quanto um Microsoft. NET Framework para o desenvolvimento de aplicações CEP.

PowerPivot

O PowerPivot é uma ferramenta que permite que você mesmo faça seu BI. o SQL Server 2008 R2 vem com dois novos recursos, SQL Server PowerPivot para o Excel 2010 e o SQL Server PowerPivot para SharePoint 2010.

PowerPivot para o Excel fornece aos analistas uma maneira de integrar grandes volumes de dados fora de um data warehouse corporativo, sejam eles para criação de relatórios de apoio à tomada de decisões ou soluções de protótipos que venham a ser parte de uma implementação de BI.

Para fornecer múltiplos usuários com acesso centralizado a relatórios desenvolvidos com PowerPivot para o Excel, o pessoal de tecnologia da informação pode implementar PowerPivot para SharePoint. Este produto PowerPivot do lado do servidor fornece o necessário de infra-estrutura para gerir, proteger, atualizar e monitorar os relatórios PowerPivot eficientemente.

Pretendo em breve trazer mais detalhes sobre cada uma dessas melhorias.

Até mais!

Categorias
SQL Server

Melhorias para DBAs com o SQL Server 2008 R2 – Parte 1

Para DBAs SQL Server esse é o produto mais esperado do ano! As inovações e melhorias são impressionante e irão fazer com que qualquer pessoa tenha vontade de experimentar o SQL Server 2008 R2. O produto Microsoft SQL Server 2008 R2 é mais avançado, confiável e escalável plataforma de banco de dados. Aproveitando o sucesso do original do SQL Server 2008, o SQL Server 2008 R2 tem um impacto sobre as organizações em todo o mundo com a sua capacidade inovadora, melhorias de escalabilidade e performance, alta disponibilidade, segurança empresarial, gestão empresarial, data warehouse, self-service BI, colaboração e integração com o Microsoft Visual Studio 2010, Microsoft SharePoint 2010 e PowerPivot do SQL Server para SharePoint tornam o SQL Server 2008 R2 a melhor plataforma de banco de dados disponível. SQL Server 2008 R2 é considerado uma atualização de versão reduzida do SQL Server 2008. No entanto, para uma pequena atualização oferece uma quantidade enorme de novidades que os DBAs podem aproveitar para aumentar sua capacidade!

Vamos analizar alguns dos benefios para os DBAs, vou dividir em duas partes: Administração e BI.

Na area de administração de banco de dados temos melhorias que irão fazer realmente a diferença.

Application and Multi-Server Administration

O SQL Server tem feito investimentos consideráveis para melhorar a aplicação e gerenciamento de multi-servidor. Com o Application e Multi-Server Administration podemos gerenciar nosso ambiente atraves de um ponto centralizador minimizando esforços. Dentro do Application e Multi-Server Administration temos:

  • O SQL Server UtilityEsta é uma nova funcionalidade de gerenciamento utilizado para central monitorar e gerenciar aplicações de banco de dados e instâncias do SQL Server a partir de um único interface de gestão conhecido como um utilitário Control Point (UCP).
  • O Utility Control Point (UCP)Ponto central de raciocínio para o SQL Server Utility, o utilitário de configuração do ponto de controle e coleta informações sobre o desempenho de instâncias do SQL Server gerenciado a cada 15 minutos.
  • Data-tier application (DAC) Um Data-tier application (DAC) é uma unidade de implantação contendo todos os esquemas do banco de dados, objetos dependentes e implantação utilizados por um aplicativo.
  • Utility Explorer dashboardsOs painéis na no SQL Server Utility DBAs com informações tremendas em utilização de recursos e de instâncias estaduais de saúde gerenciada da SQL Server e aplicativos utilizando DAC em toda a empresa.
  • Consolidation managementDBAs podem reforçar seus esforços de consolidação através da sua utilização de painéis no SQL Server Utility e ver pontos, que facilmente identificam a constatada de sobreutilização dos recursos.
  •  Customization of utilization thresholds and policies Personalização dos limiares de utilização e políticas que os DBAs podem efetuar em instâncias do SQL Server gerenciado para se adequar às necessidades de seus ambientes.

Parallel Data Warehouse

É composto de hardware e software projetado para atender às necessidades dos armazéns de dados. Esta solução tem a capacidade de escala de centenas de terabytes com o uso da nova tecnologia, denominada massively parallel processing (MPP).

Integration with Microsoft SQL Azure

As ferramentas de client incluídas no SQL Server 2008 R2 permite os DBAs a se conectarem ao SQL Azure, um serviço baseado em nuvem. SQL Azure é parte da plataforma Windows Azure e oferece um banco de dados relacional flexível e totalmente solução na nuvem.

Installation of SQL Server with Sysprep

As organizações têm vindo a utilizar o Ferramenta System Preparation (Sysprep) há muitos anos para automatizar a implantação de sistemas operacionais. SQL Server 2008 R2 introduz esta tecnologia SQL Server. Instalando o SQL Server com o Sysprep envolve um processo de duas etapas que normalmente é conduzidos usando assistentes na página Avançado do Centro de Instalação.

Analysis Services integration with SharePoint

Análise de integração com o SharePoint do SQL Server 2008 R2 introduz uma nova opção para selecionar individualmente os componentes de recursos a serem instalados.O SQL Server PowerPivot para SharePoint é uma nova opção de instalação baseada em funções em que o PowerPivot para SharePoint será instalado em um servidor SharePoint 2010 novo ou existente.

Premium Editions

Premium Editions SQL Server 2008 R2 introduz duas novas edições premium para satisfazer as necessidades dos centros de dados em larga escala e data warehouses. As novas edições, são: Datacenter e Parallel Data Warehouse

Unicode Compression

Compressão Unicode do SQL Server 2008 R2 suporta compressão para tipos de dados Unicode. Os tipos de dados que suportam compressão unicode são o nchar de comprimento fixo (n) e nvarchar (n). Infelizmente, os valores armazenados em nvarchar (max) não são compactados. A taxas de compressão de até 50 por cento no espaço de armazenamento pode ser alcançada.

Extended Protection

Extensão da Proteção do SQL Server 2008 R2 introduz suporte para conexao do Database Engine usando Extended Protection para autenticação. A autenticação é arquivada através de canal obrigatório e serviço obrigatório para os sistemas operacionais que suportam Proteção Estendida.

High Availability and Virtualization

Microsoft SQL Server 2008 R2 oferece várias melhorias nas áreas de alta disponibilidade e a virtualização. Muitas das melhorias estam associadas com as Sistema operacional Windows Server 2008 R2 ea plataforma Hyper-V.

Essa é uma breve introdução sobre as novidades na area de Administração que irei trazer mais detalhes de cada uma delas futuramente!

Categorias
How To SQL Server

Habilitando TDE

Hoje vou demostrar como utlizar o TDE, no qual descrevi no post anterior.

Vamos apenas recordar algumas coisas. No SQL Server 2008 temos um novo recurso para encriptar dados, é o Transparent Data Encryption (TDE).

O TDEencriptografa todo o seu banco de dados, isto é todos os seus arquivos de banco de dados, de forma simples e elegante. Ele fornece um forte mecanisco para proteger informações sensíveis e cumpri muitas exigencias de orgãos regulamentares. O melhor é que você não irá precisar tomar nenhuma ação no seu aplicativo.

Gostou? Então vamos habilitar esse recurso.

Primeiro vamos criar um banco de dados:

TDE01

Agora vamos criar nossa DATABASE MASTER KEY, primeiramente eu faço uma validação para ver se já existe essa DMK na base master.

TDE02

Com a DMK criada, vamos criar a o certificado de acesso, novamente eu faço uma validação que verifica se o certificado já existe.

TDE03

Agora vamos habilitar o nosso banco de dados ‘CRIPTOGRAFIA’ para receber esse criptografia

TDE04

Eu escolhi o algoritmo AES_256, mas o SQL Server disponibiliza outros.

Lembre sempre de fazer backup da sua MASTER KEY e do seu Certificado.

Voce pode fazer uma breve consulta para verificar quais os seus bancos que estão encriptados.

clip_image002

 

Simples, facil e    agora seguro!

Categorias
SQL Server

Brincando com Segurança na Informação – TDE

TDE

Segurança na informação é uma questão amplamente discutida. Você pode tomar várias precauções para proteger suas informações, tais como firewalls, certificados e ect. No entanto, quando uma midia (HD, DVD ou Fita de backup) é roubada, a pessoa mal intencionada, pode facilmente recuperar essa informações. Como um administrador de banco de dados(DBA), a segurança dos dados é uma das áreas mais importantes a considerar quando se trata de proteger os seus bancos de dados. Uma das soluções é criptografar os dados confidenciais no banco de dados e proteger as chaves que são usadas para criptografar os dados com um certificado. Isso impede que qualquer pessoa sem as chaves usadas para criptografar, de usar esses dados. Este tipo de proteção deve ser planejado com muita cautela e antecedencia.

SQL Server 2008 introduz um novo recurso que protege o banco de dados chamado Transparent Data Encryption – TDE, que oferece proteção para o banco de dados inteiro. TDE é criptografia de nível pleno de banco de dados que não está limitado às colunas e linhas, mas protege os arquivos de dados e arquivos de log assim como os arquivos de backup e a melhor parte é que NÃO exige quaisquer alterações às aplicações existentes. Dessa forma o pessoal de desenvolvimento não precisa nem ficar sabendo!

Depois que o TDE estiver habilitado em um banco de dados, para executar o restore de um backup para outra instância do SQL Server ou o attach para outra instância do SQL Server não será permitido até que o certificado que foi usado para proteger a chave de criptografia de dados seja restaurado na nova instância SQL Server.

O recurso de criptografia da TDE é aplicada no nível da página. As páginas de um banco de dados criptografado são criptografadas antes de serem escritas no disco e descodificada quando é lido na memória. TDE não aumenta o tamanho do banco de dados criptografado. Transparent Data Encryption utiliza uma Database Encryption Key (DEK) para criptografar o banco de dados que é armazenado no registro de inicialização do banco de dados. A DEK é uma chave simétrica protegida usando um certificado armazenado no banco de dados master do servidor ou uma chave assimétrica protegida por um módulo de Extensible Key Management (EKM).

Embora o certificado pode ser garantido por uma senha, TDE requer que o certificado é garantido pela Database Master Key que é protegida pela Master Key Service que é protegido pelo Data Protection API. A TDE fornece a capacidade para cumprir muitas leis, regulamentos e diretrizes estabelecidas em diversos setores.

Obs: É importante lembrar que o canal de comunicação entre o SQL Server e o aplicativo não serão criptografados via TDE.

 

Muito Importante!

Quando TDE for habilitado, você deve imediatamente fazer o backup da MASTER KEY e do seu CERTIFICADO.

A ilustração (retirada do site da MSDN) a seguir mostra a arquitetura de criptografia TDE:

clip_image001

Os arquivos de backup da base de dados em que o TDE foi habilitado também são criptografados usando a chave de criptografia do banco de dados. Quando você restaurar esses backups, o certificado que protege a chave de criptografia de banco de dados deve estar disponível, isto significa que, além de fazer o backup do banco de dados, você tem que ter certeza que você mantenha cópias de segurança dos certificados do servidor para evitar perda de dados. A perda de dados irá ocorrer se o certificado não está mais disponível. No TDE, todos os files e filegroups do banco de dados são criptografados. Se algum algum filegroups estiver marcado como READ ONLY, a operação de criptografia do banco de dados irá falhar.

Se o banco de dados que você pretende utilizar o TDE estiver habilitado para mirroring ou log shipping não se preocupe os dados serão criptografados quando forem enviados.

Um recurso a mais para você deixar seus dados seguros!

Até a próxima!

Categorias
SQL Server

Data Compression – Comprimindo dados…

Data Compression

O grande crescimento de dados não é um coisa fora da realidade atual, cada fez mais dados são armazenados fazendo com que o volume de disco seja maior. Mas será que apenas comprar mais discos é a solução ideal para esse problema de armazenamento? Será que não existe outra solução para se adequar ao meu volume de disco?

Não existe uma resposta precisa, tudo depende do volume de dados que são armazenados, mas o SQL Server pode te ajudar a economizar espaço em disco fazendo com que você posso armazenar mais informações.

A pergunta agora é COMO??

O SQL Server 2008 implanta uma particularidade chamada compressão de dados. A compressão está disponível somente no SQL Server 2008 Enterprise e Developer. Essa compressão suporta dois tipos: ROW e PAGE.

A compressão de dados pode ser configurada para os seguintes objetos de banco de dados:

  • Para uma tabela que é armazenada como um heap.
  • Para uma tabela que é armazenada como um índice clustered.
  • Para um índice nonclustered.
  • Para uma view indexada.
  • Para tabelas e índices particionados, a opção de compressão pode ser configurada para cada partição, e as várias partições de um objeto podem conter diferentes tem configurações de compressão.

Compressão não está disponível para objetos de sistema. A configuração de compressão não é aplicada automaticamente a índices nonclustered, por isso cada índice nonclustered deve ser configurado individual e manualmente.

Row Compression Level

Esta característica de compressão leva em conta o tipo de estruturas de dados variáveis que definem uma coluna. Row Compression Level é um nível de compressão que não utiliza nenhum algoritmo de compressão.

Como assim? O que ele faz então!?

O principal objetivo da Row Compression Level é reduzir o armazenamento de dados do tipo fixos, ou seja, quando você está permitindo Row Level Compression você está apenas mudando o formato de armazenamento físico dos dados que estão associados a um tipo de dados.

Row Level Compression estende o formato de armazenamento vardecimal por armazenar dados de todos os tipos de comprimento fixo em um formato de armazenamento de comprimento variável. Este tipo de compressão irá remover quaisquer bytes extras no tipo de dados fixo. Não há absolutamente nenhuma mudança necessária no aplicativo.

Por exemplo, temos uma coluna CHAR (100) que está usando o Row Compression Level só usará a quantidade de armazenamento definida pelos dados. Como assim? Vamos armazenar a frase “SQL Server 2008” na coluna, a frase contem apenas 15 caracteres e apenas esses 15 caracteres são armazenados ao contrario dos 100 que foram definidos pela coluna, portanto, você tem uma economia de 85% no espaço de armazenamento.

E como isso funciona na pratica!?

Vamos lá.

Primeiramente vamos criar uma tabela simples:

CREATE

 

TABLE RowCompression

(

 

[Id]

 

INTIDENTITYPRIMARYKEY

 

,[Nome] CHAR(100)

 

,[Valor] NUMERIC(18,4)

 

,[Data] DATETIME

)

GO

Vamos inserir alguns registros.

 

SET

NOCOUNTON

DECLARE

 

@min INT

, @max INT

, @rand INT

, @nome VARCHAR(100)

, @cont1 INT

, @cont INT

 

SET @cont = 0

WHILE

 

@cont < 10000000

BEGIN

SET @min = 80

SET @max = 110

SET @cont1 = 0

SET @nome =

WHILE @cont1 <CONVERT(int,RAND()* 100)

BEGIN

SET @rand =((@Max + 1) @Min)*RAND()+ @Min

SET @nome = @nome +char(@rand)

SET @cont1 = @cont1 + 1

END

INSERTINTO dbo.RowCompression

 

(

[Valor]

, [Data]

, [Nome]

)

SELECT

RAND()* 448515

,GETDATE()+Convert(Int,RAND()* 1000)

, @nome

 

SET @cont = @cont + 1

END

SETNOCOUNTOFF

Depois de inserir os registros temos o seguinte espaço utilizado pela tabela

clip_image001

 

O resultado acima indica que a tabela está armazenando 1260 MB.

Vamos aplicar o Row Compression Level agora.

clip_image002

Uau.. Notem a diferença de entre as colunas DATA e INDEX_SIZE. Depois de aplicar a compressão o armazenamento da tabela ficou em 350 MB. Deixando a tabela aproximadamente com apenas 28% do tamanho original!

Page Compression Level

Nas versões anteriores do SQL Server cada valor era armazenado na página, independentemente se o mesmo valor já havia aparecido na mesma coluna para algumas outras linhas dentro de uma página.

No SQL Server 2008, o valor redundante ou duplicado será armazenado apenas uma vez dentro da página e o será referenciado em todas as outras ocorrências, dessa forma temos o Page Compression Level.

Basicamente, o Page Compression Level é um superconjunto de compressão ROW e leva em conta os dados redundantes em uma ou mais linhas em uma determinada página. Ele também usa a compactação de prefixo e dicionário.

O método Page Compression Level é mais vital, pois permite que os dados comuns sejam compartilhados entre as linhas de uma determinada página.

Esse tipo de compressão utiliza as seguintes técnicas:

  • Row compression
  • Prefix Compression
  • Dictionary Compression

Row compression já vimos acima, agora vamos ver o resto!

Prefix Compression

 

Para cada coluna em uma página os prefixos duplicados são identificados. Estes prefixos são armazenados nos cabeçalhos de Compressão de Informação (CI), que residem após o cabeçalho da página. Um número de referência é atribuído a esses prefixos e esse número de referência é utilizado onde quer que esses prefixos estejam sendo usados.

  • Para cada página que está sendo comprimido, o Prefix Compression utiliza os seguintes passos:
  • Para cada coluna, um valor que é identificado pode ser usado para reduzir o espaço de armazenamento para os valores em cada coluna.
  • A linha que representa os valores de prefixo para cada coluna é criada e armazenada na CI, estrutura que segue imediatamente o cabeçalho da página.
  • Os valores repetidos na coluna são substituídos por uma referência ao prefixo correspondente. Se o valor em uma linha não corresponder exatamente ao valor do prefixo selecionado, uma correspondência parcial ainda pode ser indicada.

A ilustração abaixo, retirada do site da MSDN, mostra uma página de exemplo de uma tabela antes da utilização do Prefix Compression

clip_image003

A ilustração abaixo, também retirada do site da MSDN, mostra a mesma página após a utilização do Prefix Compression.

O prefixo é movido para o cabeçalho e os valores da coluna são alterados as referências ao prefixo

clip_image004

Na primeira coluna da primeira linha, o valor 4b indica que os quatro primeiros caracteres do prefixo (AAAB) estão presentes para essa linha, e também o caráter b. Isso torna o aaabb valor resultante, que é o valor original.

Dictionary Compression

Pesquisas por valores duplicados por fora da página e as armazena no CI. A principal diferença entre o Prefix Compression e Dictionary Compression é que o Prefix se restringe apenas a uma coluna enquanto Dictionary é aplicável para a página completa.

Depois que a Prefix Compression foi concluída, a Dictionary Compression é aplicada e pesquisas valores repetidos em qualquer lugar da página e os armazena na área de CI. Ao contrário da Prefix Compression, a Dictionary Compression não está restrita a uma coluna e pode substituir valores repetidos, que ocorrem em qualquer lugar em uma página.

A ilustração abaixo mostra a mesma página após a compressão dicionário

clip_image005

Observe que o valor 4b foi referenciado a partir de diferentes colunas da página.

Legal, como ficaria nossa tabela com esse nível de compressão?

Simples, primeiro vamos voltar à tabela ao seu estado original, sem compressão.

clip_image006

Agora aplicamos o Page Compression Level

clip_image007

 

Mais uma vez notem a diferença de entre as colunas DATA e INDEX_SIZE.

Depois de aplicar a compressão o armazenamento da tabela ficou em 338 MB.

Deixando a tabela aproximadamente com apenas 26% do tamanho original, tivemos um ganho de compressão em relação ao Row Compression Level.

Comparação entre o Row Compression Level e o Page Compression Level

Row Level

Page Level

Nenhum algoritmo de compressão é utilizado

Algoritmo de compressão é utilizado

Menor taxa de compressão em relação ao nível de página

Maior taxa de compressão em relação ao nível de linha

Este método tenta converter tipo de dados fixo para tipo de dados variáveis para a realização de compressão

Este método usa o algoritmo baseado em Prefix e Dictionary para atingir compressão

Usa menos recursos (processador, memória, etc.)

Usa mais recursos (processador, memória, etc.)

Taxa de compressão depende da distribuição dos dados e do esquema.

Taxa de compressão depende da distribuição dos dados.

Como estimar de economia de espaço

No SQL Server 2008 existem duas maneiras de estimar a economia de espaço para armazenamento de tabelas e índices. O primeiro método é usar uma SP de sistema chamada sp_estimate_data_compression_savings e o segundo método é usar o Assistente de Compactação de Dados.

Primeiro vamos usar a sp_estimate_data_compression_savings onde:

  • 1º parâmetro é o nome do schema;
  • 2º parâmetro é o nome do objeto;
  • 3º parâmetro é o ID do índice;
  • 4º parâmetro é o ID da Partição
  • 5º parâmetro é o tipo de compressão;

clip_image009

Notem as colunas size_with_current_compression_setting(KB) e size_with_requested_compression_setting(KB), essas colunas mostras o valor atual e o valor após a compressão. Dessa forma podemos saber quanto de espaço iremos ganhar com a aplicação da compressão.

Agora vamos utilizar o assistente.

Clique com o botão direito em cima da tabela escolha a opção Storege -> Manage Compression

clip_image011

O Assistente irá aparecer.

 

clip_image013

Na próxima pagina você pode escolher o tipo de compressão e antes de aplicar você pode calcular o espaço para verificar qual é melhor!

clip_image015

 

Eu escolhi o tipo de compressão PAGE, e cliquei no botão Calculate. O assistente irá trazer as informações abaixo.

clip_image017

Seguindo com o assitente, escolhi uma das opções, e em seguida irá gerar uma lista com todos os itens a serem executados.

clip_image019

Após executar irá aparecer uma tela de confirmação.

clip_image021

 

Você pode verificar todos os seus objetos e quais níveis de compressão eles estão utilizando através do comando:

clip_image022

 

Ou você pode clicar com o botão direito em cima do objeto, na guia Storage, a opção Compression informa o tipo de compressão.

clip_image024

 

Dessa maneira é mais fácil de manter banco de dados compactado e ajuda a otimizar e reduzir tempo do seu plano de manutenção.

É isso ai, até a próxima!

Categorias
SQL Server

Entendendo a Criptografia

No mundo corporativo de hoje, a segurança com os dados e informações é primordial.
O SQL Server se preocupa com isso e implementa alguns artificios em relação a criptografia e segurança
de dados.

SQL Server criptografa dados com uma criptografia hierárquica e infra-estrutura de gerenciamento de chaves. Cada camada criptografa a camada abaixo dela, usando uma combinação de certificados, chaves assimétricas, e as chaves simétricas. As chaves assimétricas e chaves simétricas podem ser armazenados fora do SQL Server em um módulo chamado Extensible Key Management (EKM).

A imagem baixo, retirada do site da TechNet, mostra a hierarquia de criptografia.

 

 

O acesso ao início da hierarquia é geralmente protegido por uma senha.

A figura mostra que tudo começa com a criptografia do Windows através da API Data Protection que proteje a Service Master Key (SMK) onde começa nossa aventura no mundo do SQL Server.

Service Master Key

Service Master Key forma a base para a hierarquia da criptografia no SQL Server e é necessária para que você possa criar categorias de certificados ou chaves assimétricas. A Master Key é gerada automaticamente na primeira vez que a instância é iniciada. Por padrão, Service Master Key é criptografada usando a API de proteção de dados do Windows e usa a chave da máquina local. Service Master Key só pode ser aberta pela conta de serviço do Windows em que ela foi criada, ou por uma entidade com acesso tanto ao nome da conta de serviço e sua senha. A Service Master Key é usada para criptografar qualquer database master key que é criado dentro da instância.

Regenerar ou recuperar a Service Master Key envolve descriptografar e re-criptografar a hierarquia de criptografia completa.

Database Master Key

Cada banco de dados tem uma Master Key diferentes, garantindo que um usuário com acesso a descodificar os dados em um database também não pode decifrar dados em outro database sem ser concedido permissão para ele executar essa tarefa.

A Database Master Key é usado para proteger todos os certificado, chave simétrica, ou chave assimétrica que são armazenadas detro de um database e é criptografada com o Triple DES e a senha fornecida pelo usuário. Uma cópia da Database Master Key também é criptografada usando a Service Master Key de tal forma que a descriptografia automática pode ser realizado dentro da instância.

Vamos demostrar a criação de uma Database Master Key, criei um database chamado CRIPTOGRAFIA”

USE [master]

GO

CREATE DATABASE [CRIPTOGRAFIA]

GO

A Database Master Key deve ser gerada explicitamente usando o seguinte comando:

USE [CRIPTOGRAFIA]

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘passwordDatabase’

GO

Para verifcar informações sobre chaves de criptografia, você pode consultar uma DMV (Dynamic Management View)

USE [master]

GO

SELECT * FROM sys.symmetric_keys

GO

 

Quando você faz uma solicitação para descriptografar dados, a Master Key é usada para descriptografar a Database Master Key, que é usada para descriptografar um certificado, chave simétrica, ou chave assimétrica, e por sua vez é usada para descriptografar os dados.

A razão dessa hierarquia é importante e que você deve ter cuidado quando for preciso mover backups contendo os dados criptografados entre instâncias do SQL Server. Para restaurar e ser capaz de decifrar os dados com sucesso, você também deve fazer backup da Database Master Key e depois gerar a Database Master Key em outra instância.

Certificados

Os certificados são chaves baseado no padrão X.509 que são utilizados para autenticar as credenciais da entidade. Você pode criar certificados públicos ou privados. Um certificado público é essencialmente um arquivo que é fornecido por uma autoridade de certificado que valida a entidade, através do certificado. Os certificados privados são gerados e utilizados para proteger os dados dentro de uma organização.

Para criar um certificado assinado no SQL Server, você deve usar o seguinte comando:

USE [CRIPTOGRAFIA]

GO

CREATE CERTIFICATE CertificadoBlog

ENCRYPTION BY PASSWORD = ‘passwordCertificado’

WITH SUBJECT = ‘Certificado Blog’,

EXPIRY_DATE = ’12/31/2010′;

GO

Assinaturas

Assinaturas permitem elevar a permissão de um usuário, mas para proporcionar isso o usuário deve estar executando um pedaço especificações de código ou seja, você so pode adicionar uma assinatura digital para um módulo de stored procedure, function, trigger e assemblies.

O processo para assinar digitalmente um código para gerenciar as permissões é o seguinte:

  • Criar uma Database Master Key.

    (repetindo o código acima)

USE [CRIPTOGRAFIA]

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘passwordDatabase’

GO

  • Criar um certificado no database

    (repetindo o código acima)

USE [CRIPTOGRAFIA]

GO

CREATE CERTIFICATE CertificadoBlog

ENCRYPTION BY PASSWORD = ‘passwordCertificado’

WITH SUBJECT = ‘Certificado Blog’,

EXPIRY_DATE = ’12/31/2010′;

GO

Criar um usuário mapeado para o certificado.

USE [CRIPTOGRAFIA]

GO

CREATE USER UserBlog FOR CERTIFICATE CertificadoBlog;

GO

  • Atribuir permissões em um objeto ou objetos para o usuário.

   Vou criar uma serie de objetos no database para demonstrar a assinatura.

USE [CRIPTOGRAFIA]

GO

CREATE TABLE dbo.Secretaria(

idSec int IDENTITY(1,1),

nomeSec nvarchar(300) NOT NULL,

responsavelSec nvarchar(300) NOT NULL,

CONSTRAINT Secretaria_PK PRIMARY KEY CLUSTERED (idSec) )

GO

USE [CRIPTOGRAFIA]

GO

CREATE PROCEDURE dbo.USP_LISTA_SECRETARIA AS

SELECT * FROM dbo.Secretaria

Depois de criar os objetos vou dar permissão ao usuario UserBlog que criamos para o certificado.

GRANT EXECUTE ON dbo.USP_LISTA_SECRETARIA TO UserBlog

  • Assinar digitalmente um módulo para o certificado.

      USE [CRIPTOGRAFIA]

GO

ADD SIGNATURE TO dbo.USP_LISTA_SECRETARIA BY CERTIFICATE CertificadoBlog WITH PASSWORD = ‘passwordCertificado’

Symmetric Keys

As chaves simétricas utilizam uma única chave de criptografia e descriptografia. Como apenas uma única chave é necessária para criptografar e descriptografar dados, a criptografia de chave simétrica não é tão forte como os certificado ou chave assimétrica baseada em criptografia. No entanto, as chaves simétricas proporcionar o melhor desempenho possível para o uso rotineiro de dados criptografados.

Legal, e agora? O que isso muda?

Vamos lá… vou mostrar o que isso muda e como faz diferença.

Vou aproveitar que já criamos uma Master Key e um Certificado e vou apenas criar uma Symmetric Keys

USE [CRIPTOGRAFIA]

GO

CREATE SYMMETRIC KEY EncriptaTabela

WITH ALGORITHM = TRIPLE_DES

ENCRYPTION BY CERTIFICATE CertificadoBlog

Vamos inserir alguns dados na tabela “Secretaria” que tambem já criamos utilizando a chave simetrica.

OPEN SYMMETRIC KEY EncriptaTabela

DECRYPTION BY CERTIFICATE CertificadoBlog WITH PASSWORD = ‘passwordCertificado’

insert into CRIPTOGRAFIA.dbo.secretaria

( nomeSec

,responsavelSec

)

values

( ENCRYPTBYKEY(KEY_GUID(‘EncriptaTabela’),‘Urbanismo’)

,ENCRYPTBYKEY(KEY_GUID(‘EncriptaTabela’),‘Fernado B.’)

)

insert into CRIPTOGRAFIA.dbo.secretaria

( nomeSec

,responsavelSec

)

values

( ENCRYPTBYKEY(KEY_GUID(‘EncriptaTabela’),‘Meio Ambiente’)

,ENCRYPTBYKEY(KEY_GUID(‘EncriptaTabela’),‘Ricardo G.’)

)

insert into CRIPTOGRAFIA.dbo.secretaria

( nomeSec

,responsavelSec

)

values

( ENCRYPTBYKEY(KEY_GUID(‘EncriptaTabela’),‘Educacao’)

,ENCRYPTBYKEY(KEY_GUID(‘EncriptaTabela’),‘Aline A.’)

)


Dessa forma meus dados estão todos criptografados. Como mostra a figura abaixo.

 

Ok.. você pode perguntar.. ele não usou a função para descriptografar. Tudo bem, vamos lá.

Hum.. e agora? Como eu recupero meus dados? Será que eles estão guardados?

Calma, seus dados estão lá. Para recuperar nos precisamos usar a mesma função para inserir dados.

 

Um detalhe importante. Não esqueça de fechar a chave, isso garante a segurança na hora de leitura dos dados!

Backup Master key e Certificates

Porque devemos fazer backup da Master Key e de Certificados?

A resposta é simples! São eles que permitem a criptografia dos seus dados e apenas eles podem descriptografar os dados.

Não adianta nada voce criptografar os dados se qualquer um pudesse abri-los em qualquer lugar.

A sintaxe genérica para fazer backup de um DMK é:

USE [CRIPTOGRAFIA]

GO

BACKUP SERVICE MASTER KEY TO FILE = ‘D:Bancos de DadosMasterKey’ ENCRYPTION BY PASSWORD = ‘password’;

Database Master Key (DMKs) são criadas antes de um certificado, uma chave simétrica, ou chave assimétrica. A DMK é a raiz da hierarquia de criptografia em um banco de dados. Para garantir que você pode acessar certificados, chaves assimétricas, e as chaves simétricas dentro de um banco de dados, você precisa ter uma cópia de segurança da DMK.

Antes que você pode fazer backup de um DMK, ela deve ser aberta. Por padrão, um DMK é criptografada com a chave mestra de serviço. Você deve abrir a DMK usando o seguinte comando:

USE [CRIPTOGRAFIA]

GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘passwordDatabase’;

Os certificados são usados para criptografar dados, bem como assinaturas digitais. Apesar de você poder criar um novo certifcado para substituir a assinatura digital, no caso da perda de um
certificado, você deve ter o certificado original para acessar todos os dados que foram criptografados com
ele. Você pode fazer backup somente da chave pública, usando o seguinte comando:

BACKUP CERTIFICATE CertificadoBlog TO FILE = ‘D:Bancos de DadosCertificado1’

CLOSE MASTER KEY

No entanto, se você restaurar um backup de um certificado, contendo apenas a chave pública, o SQL Server gera uma nova chave privada. Infelizmente, a chave privada é um componente importante de um certificado que é usado para criptografar e descriptografar dados no SQL Server. Portanto, é preciso assegurar que o backup contenha tanto chaves públicas e privadas para um certificado. Assim como Master Key, você deve fazer backup de um certificado imediatamente após a criação, utilizando o seguinte comando:

USE [CRIPTOGRAFIA]

GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘passwordDatabase’;

BACKUP CERTIFICATE CertificadoBlog TO FILE = ‘D:Bancos de DadosCertificado2’

WITH PRIVATE KEY ( DECRYPTION BY PASSWORD = ‘passwordCertificado’ ,

        FILE = ‘D:Bancos de DadosPrivateKey1’ ,

ENCRYPTION BY PASSWORD = ‘passwordBackup’ )

)

CLOSE MASTER KEY