Galera esse é um artigo relativamente simples e bem útil para o dia a dia do DBA, quem ainda não ficou de frente com um banco de dados corrompido?
Pois é irei mostrar uma maneira de recuperar um banco de dados em modo SUSPECT, criando um novo arquivo de LOG do banco. Claro que antes de realizar este procedimento tem que olhar o log do SQL Server e ver qual motivo o banco ficou suspect, se no log tiver a mensagem que não foi possível recuperar o arquivo de log e o banco foi marcado como suspeito então este scritp resolverá seu problema.
Este scritp é válido para o SQL Server 2000, pois o comando DBCC REBUILD_LOG foi descontinuado no SQL Server 2005 e 2008.
Segue script comentado:
--VOLTA BANCO SUSPECT CRIANDO OUTRO ARQUIVO DE LOG
/*
1° Passo -> Verificar porque o banco ficou suspect, se foi porque não conseguiu fazer recovery do arquivo de log
este script resolverá o problema
*/
--EXECUTAR TODOS OS SCRIPTS SEPARADAMENTE
--Executar este primeiro comando, com este configuração é possível alterar as tabelas de sistema
sp_configure 'allow updates',1
reconfigure with override
go
--Executar e verificar se o banco e as tabelas estão acessíveis
sp_resetstatus 'NomeBancoDeDados'
go
--Caso o comando acima não funcione, executa este comando que deixará o banco em emergency mode
Update sysdatabases set status = 32768 where name ='NomeBancoDeDados'
go
--Após o comando ser executado com sucesso, executar este comando para criar novo arquivo de log para o banco,
--passa o nome do banco e o caminho onde será criado o novo arquivo
DBCC Rebuild_log ('NomeBancoDeDados','C:\CAMINHO\NomeDoArquivo.ldf')
go
-- Aqui o banco já estará on line... Mas temos que verificar se existem erros de integridade
-- Vamos colocar o banco e modo single user
Use Neo
go
sp_dboption 'NomeBancoDeDados','single_user', true
go
--Executamos o comando para reparar os erros, caso existam
use master
go
dbcc checkdb ('NomeBancoDeDados',repair_allow_data_loss)
go
--Tirar o banco do modo single user
use Neo
go
sp_dboption 'NomeBancoDeDados','single_user', false
go
--Voltar este parametro para 0
sp_configure 'allow updates',0
reconfigure with override
go
Apenas explicando o parâmetro REPAIR_ALLOW_DATA_LOSS, quando uma página está corrompida ao usar este parâmetro no DBCC CHECKDB a página anterior simplesmente muda o ponteiro e aponta para a próxima página que estiver OK, então é possível que exista perda de dados. Então em qualquer situação de banco de dados corrompido o mais adequado é recuperar o backup.
Abraços,
Rodrigo Figueiredo
figueiredo.rodrigo@hotmail.com
quinta-feira, 28 de outubro de 2010
quarta-feira, 27 de outubro de 2010
Criptografia Transparente de Dados - Parte II
Bem pessoal, como disse na primeira parte deste tema mostrei como implantar a criptografia transparente de dados em um banco de dados, agora irei passar para vocês todos os testes que realizei.
Continuo usando o SQL Server 2008 Developer Edition com SP2, build 10.0.4000.
1° Teste: Desatachar o banco de dados criptografado e tentar atachar em outro servidor SQL que não existe nenhuma chave nem certificado de criptografia
USE master
GO
sp_detach_db 'Criptografia'
GO
e em seguida tentarei atachar no outro servidor:
EXEC sp_attach_db @dbname = 'Criptografia',
@filename1 = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Criptografia.mdf',
@filename2 = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Criptografia_log.ldf'
GO
após executar este comando receberemos a seguinte mensagem do SQL Server:
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0x2400EE11A47193208473BE1225A55AC13B4405B1'.
Para conseguirmos atachar o banco de dados criptografado em outro servidor é necessário criar a Master Key com a mesma senha do servidor onde o banco foi criptogrado, levar os arquivos do certificado e da chave criptografada para este servidor e em seguinda criar um certificado passando esses arquivos.
No servidor onde queremos atachar o banco executamos os seguintes scripts:
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeysenha'
GO
USE master
GO
CREATE CERTIFICATE CertificadoCriptografia
FROM FILE = 'C:\Criptografia_certificate.cer'
WITH PRIVATE KEY (FILE = 'C:\Criptografia_certkey.pvk', DECRYPTION BY PASSWORD = 'MasterKeysenha')
GO
após criado a Master Key e o certificado vamos tentar a atachar o banco novamente:
EXEC sp_attach_db @dbname = 'Criptografia',
@filename1 = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Criptografia.mdf',
@filename2 = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Criptografia_log.ldf'
GO
e receberemos a mensagem:
Command(s) completed successfully.
2° Teste: Fazer backup do banco criptografado e tentar restaurar o backup em outro servidor que não possui nenhuma chave nem certificado de criptografia
BACKUP DATABASE Criptografia TO DISK='C:\Backup Criptografia\Criptografia.bak'
GO
agora vamos tentar restaurar este backup no outro servidor:
RESTORE DATABASE Criptografia FROM DISK='C:\Backup Criptografia\Criptografia.bak'
após tentar restaurar o SQL exibe a seguinte mensagem:
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0x2400EE11A47193208473BE1225A55AC13B4405B1'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Para conseguir restaurar o backup em outro servidor é necessário realizar o mesmo procedimento relizado no teste 1. Tem que criar a Master Key e depois criar o certificado, assim o restore é concluído com sucesso.
Com estes dois testes foi possível ver que a criptografia transparente de dados realmente funciona e é bastante interessante, pois ainda não existia nenhuma criptografia interna do SQL Server a nível dos arquivos de dados e de log.
Na próxima parte iremos realizar os teste mais interessantes, mostrarei como desabilitar a TDE de um banco de dados se é possível atachar e/ou restaurar o banco de dados em outro servidor após desabilitar a criptografia.
Abraços,
Rodrigo Figueiredo
figueiredo.rodrigo@hotmail.com
Continuo usando o SQL Server 2008 Developer Edition com SP2, build 10.0.4000.
1° Teste: Desatachar o banco de dados criptografado e tentar atachar em outro servidor SQL que não existe nenhuma chave nem certificado de criptografia
USE master
GO
sp_detach_db 'Criptografia'
GO
e em seguida tentarei atachar no outro servidor:
EXEC sp_attach_db @dbname = 'Criptografia',
@filename1 = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Criptografia.mdf',
@filename2 = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Criptografia_log.ldf'
GO
após executar este comando receberemos a seguinte mensagem do SQL Server:
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0x2400EE11A47193208473BE1225A55AC13B4405B1'.
Para conseguirmos atachar o banco de dados criptografado em outro servidor é necessário criar a Master Key com a mesma senha do servidor onde o banco foi criptogrado, levar os arquivos do certificado e da chave criptografada para este servidor e em seguinda criar um certificado passando esses arquivos.
No servidor onde queremos atachar o banco executamos os seguintes scripts:
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeysenha'
GO
USE master
GO
CREATE CERTIFICATE CertificadoCriptografia
FROM FILE = 'C:\Criptografia_certificate.cer'
WITH PRIVATE KEY (FILE = 'C:\Criptografia_certkey.pvk', DECRYPTION BY PASSWORD = 'MasterKeysenha')
GO
após criado a Master Key e o certificado vamos tentar a atachar o banco novamente:
EXEC sp_attach_db @dbname = 'Criptografia',
@filename1 = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Criptografia.mdf',
@filename2 = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Criptografia_log.ldf'
GO
e receberemos a mensagem:
Command(s) completed successfully.
2° Teste: Fazer backup do banco criptografado e tentar restaurar o backup em outro servidor que não possui nenhuma chave nem certificado de criptografia
BACKUP DATABASE Criptografia TO DISK='C:\Backup Criptografia\Criptografia.bak'
GO
agora vamos tentar restaurar este backup no outro servidor:
RESTORE DATABASE Criptografia FROM DISK='C:\Backup Criptografia\Criptografia.bak'
após tentar restaurar o SQL exibe a seguinte mensagem:
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0x2400EE11A47193208473BE1225A55AC13B4405B1'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Para conseguir restaurar o backup em outro servidor é necessário realizar o mesmo procedimento relizado no teste 1. Tem que criar a Master Key e depois criar o certificado, assim o restore é concluído com sucesso.
Com estes dois testes foi possível ver que a criptografia transparente de dados realmente funciona e é bastante interessante, pois ainda não existia nenhuma criptografia interna do SQL Server a nível dos arquivos de dados e de log.
Na próxima parte iremos realizar os teste mais interessantes, mostrarei como desabilitar a TDE de um banco de dados se é possível atachar e/ou restaurar o banco de dados em outro servidor após desabilitar a criptografia.
Abraços,
Rodrigo Figueiredo
figueiredo.rodrigo@hotmail.com
sexta-feira, 22 de outubro de 2010
Criptografia Transparente de Dados - Parte I
Passei essa semana testando a nova funcionalidade de criptografia do SQL Server 2008, a TDE(Transparent Data Encryption), comecei a ler e fiquei muito interessado pois era exatamente o que eu precisava, pois ela funciona a nível dos arquivos (.mdf e .ldf) do banco de dados.
A TDE criptografa os arquivos isso quer dizer que após o acesso ao banco de dados todas as informações estarão disponíveis, mas o problema esta justamente ai, para ter este acesso...
Nesta primeira parte veremos como implementar esta funcionalidade em um banco de dados, nos próximos post veremos os detalhes dos testes realizados.
Estou utilizando o SQL Server 2008 Developer Edition com SP2, build 10.0.4000.
Primeiramente criei um banco de dados simples com somente uma tabela:
USE Master
GO
CREATE DATABASE Criptografia
GO
USE Criptografia
GO
CREATE TABLE Pessoa(
Codigo int IDENTITY(1,1) NOT NULL PRIMARY KEY,
Nome varchar(20) NOT NULL,
Idade int NULL) ON [PRIMARY]
GO
Em seguida inserir alguns registros:
INSERT INTO Pessoa VALUES ('Rodrigo Figueiredo',25)
GO 100
Vamos começar agora a implantar a TDE no banco Criptografia:
--Primeiro passo: Criar Master Key encryption no banco master
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeysenha'
GO
/* Segundo passo: Criar um certificado no banco master.
A chave privada deste certificado será protegida pela Master Key*/
USE master
GO
CREATE CERTIFICATE CertificadoCriptografia
WITH SUBJECT = 'Certificado Criptografia'
GO
Agora que já criamos a Master Key e o Certificado podemos criptografar o banco de dados:
/* Terceiro passo: criar uma chave de criptografia para a base de dados,
usando o certificado criado anteriormente.
Podemos escolher o algoritmo de criptografia que queremos usar:
triple DES, AES 128, AES 192 ou AES 256 */
USE Criptografia
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE CertificadoCriptografia
GO
/* Este comando vai retornar um Warning,
Lembrando que devemos fazer um backup de nosso certificado imediatamente. */
USE master
GO
BACKUP CERTIFICATE CertificadoCriptografia
TO FILE = 'C:\Backup Criptografia\Criptografia_certificate.cer'
WITH PRIVATE KEY (FILE = 'C:\Backup Criptografia\Criptografia_certkey.pvk',
ENCRYPTION BY PASSWORD = 'MasterKeysenha');
GO
--Quarto passo: habilitar o TDE na nossa base
ALTER DATABASE Criptografia
SET ENCRYPTION ON
GO
Neste momento o banco Criptografia está criptografado utilizando o algoritmo AES_256. Se executarmos a consulta:
SELECT DB_NAME(e.database_id) AS DatabaseName,
e.encryption_state,
CASE e.encryption_state
WHEN 0 THEN 'No database encryption key present, no encryption'
WHEN 1 THEN 'Unencrypted'
WHEN 2 THEN 'Encryption in progress'
WHEN 3 THEN 'Encrypted'
WHEN 4 THEN 'Key change in progress'
WHEN 5 THEN 'Decryption in progress'
END AS encryption_state_desc,
key_algorithm +'_'+ convert(nvarchar,key_length) [Algoritmo],
c.name [Nome Certificado],
set_date [Data Criptografia]
FROM sys.dm_database_encryption_keys AS e
LEFT JOIN master.sys.certificates AS c
ON e.encryptor_thumbprint = c.thumbprint
Veremos que dois bancos estão criptografados usando o algoritmo AES_256, o banco Criptografia utilizando certificado criado por nós e o banco tempdb sem utilizar nenhum certificado. O banco tempdb é criptografado sempre que qualquer banco da instância for criptgrafado, isso acontece porque o tempdb é utilizado por todos os banco de dados existentes na instância.
Como sabemos toda criptogafia tem um custo de I/O e principalmente de CPU, a criptografia após aplicada no banco de dados funciona da seguinte maneira: Toda vez no momento que a informação é gravada no disco ela é criptografada e sempre que for lida do disco para a mémoria ela é descriptograda, isso gera um custo de I/O, mas praticamente imperceptível. O custo maior fica por conta do processamento, devido ao algoritmo usado, quanto mais seguro for o algoritmo maior será o custo de CPU.
Bem galera esta foi a primeira parte e mostrei apenas como implementar TDE em um banco de dados, nós próximos posts falarei dos testes feitos, de backup e restore do banco de um servidor para outro e também detach e attach entre servidores utilizando TDE.
Abraços,
Rodrigo Figueiredo
figueiredo.rodrigo@hotmail.com
A TDE criptografa os arquivos isso quer dizer que após o acesso ao banco de dados todas as informações estarão disponíveis, mas o problema esta justamente ai, para ter este acesso...
Nesta primeira parte veremos como implementar esta funcionalidade em um banco de dados, nos próximos post veremos os detalhes dos testes realizados.
Estou utilizando o SQL Server 2008 Developer Edition com SP2, build 10.0.4000.
Primeiramente criei um banco de dados simples com somente uma tabela:
USE Master
GO
CREATE DATABASE Criptografia
GO
USE Criptografia
GO
CREATE TABLE Pessoa(
Codigo int IDENTITY(1,1) NOT NULL PRIMARY KEY,
Nome varchar(20) NOT NULL,
Idade int NULL) ON [PRIMARY]
GO
Em seguida inserir alguns registros:
INSERT INTO Pessoa VALUES ('Rodrigo Figueiredo',25)
GO 100
Vamos começar agora a implantar a TDE no banco Criptografia:
--Primeiro passo: Criar Master Key encryption no banco master
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeysenha'
GO
/* Segundo passo: Criar um certificado no banco master.
A chave privada deste certificado será protegida pela Master Key*/
USE master
GO
CREATE CERTIFICATE CertificadoCriptografia
WITH SUBJECT = 'Certificado Criptografia'
GO
Agora que já criamos a Master Key e o Certificado podemos criptografar o banco de dados:
/* Terceiro passo: criar uma chave de criptografia para a base de dados,
usando o certificado criado anteriormente.
Podemos escolher o algoritmo de criptografia que queremos usar:
triple DES, AES 128, AES 192 ou AES 256 */
USE Criptografia
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE CertificadoCriptografia
GO
/* Este comando vai retornar um Warning,
Lembrando que devemos fazer um backup de nosso certificado imediatamente. */
USE master
GO
BACKUP CERTIFICATE CertificadoCriptografia
TO FILE = 'C:\Backup Criptografia\Criptografia_certificate.cer'
WITH PRIVATE KEY (FILE = 'C:\Backup Criptografia\Criptografia_certkey.pvk',
ENCRYPTION BY PASSWORD = 'MasterKeysenha');
GO
--Quarto passo: habilitar o TDE na nossa base
ALTER DATABASE Criptografia
SET ENCRYPTION ON
GO
Neste momento o banco Criptografia está criptografado utilizando o algoritmo AES_256. Se executarmos a consulta:
SELECT DB_NAME(e.database_id) AS DatabaseName,
e.encryption_state,
CASE e.encryption_state
WHEN 0 THEN 'No database encryption key present, no encryption'
WHEN 1 THEN 'Unencrypted'
WHEN 2 THEN 'Encryption in progress'
WHEN 3 THEN 'Encrypted'
WHEN 4 THEN 'Key change in progress'
WHEN 5 THEN 'Decryption in progress'
END AS encryption_state_desc,
key_algorithm +'_'+ convert(nvarchar,key_length) [Algoritmo],
c.name [Nome Certificado],
set_date [Data Criptografia]
FROM sys.dm_database_encryption_keys AS e
LEFT JOIN master.sys.certificates AS c
ON e.encryptor_thumbprint = c.thumbprint
Veremos que dois bancos estão criptografados usando o algoritmo AES_256, o banco Criptografia utilizando certificado criado por nós e o banco tempdb sem utilizar nenhum certificado. O banco tempdb é criptografado sempre que qualquer banco da instância for criptgrafado, isso acontece porque o tempdb é utilizado por todos os banco de dados existentes na instância.
Como sabemos toda criptogafia tem um custo de I/O e principalmente de CPU, a criptografia após aplicada no banco de dados funciona da seguinte maneira: Toda vez no momento que a informação é gravada no disco ela é criptografada e sempre que for lida do disco para a mémoria ela é descriptograda, isso gera um custo de I/O, mas praticamente imperceptível. O custo maior fica por conta do processamento, devido ao algoritmo usado, quanto mais seguro for o algoritmo maior será o custo de CPU.
Bem galera esta foi a primeira parte e mostrei apenas como implementar TDE em um banco de dados, nós próximos posts falarei dos testes feitos, de backup e restore do banco de um servidor para outro e também detach e attach entre servidores utilizando TDE.
Abraços,
Rodrigo Figueiredo
figueiredo.rodrigo@hotmail.com
segunda-feira, 18 de outubro de 2010
MCTS SQL Server 2008
Depois de alguns meses de estudos e já com alguma experiência nas novas features do SQL Server 2008 resolvi fazer a prova 70-432, bem galera fui aprovado e acho que o resultado foi fruto do meu estudo... Passei com 980 :DD
Fiquei muito feliz e agora já comecei os estudos para a 70-450. Essa foi minha segunda certificação Microsfot e a parti de agora irei focar bastante nestas certificações.
_1269.gif)
Fiquei muito feliz e agora já comecei os estudos para a 70-450. Essa foi minha segunda certificação Microsfot e a parti de agora irei focar bastante nestas certificações.
_1269.gif)
Abraços,
Rodrigo Figueiredo
segunda-feira, 4 de outubro de 2010
Aberta as inscrições para o Worldwide Online TechDay 2010
Galera abriu hoje as inscrições para o Worldwide Online TechDay 2010. As palestras iniciam às 08:45 do dia 30 de outubro e vão até 4h da manhã do dia 31 de outubro, horário de São Paulo.
As palestras serão ministradas em português e inglês.
Quem ainda não viu a programação com certeza vale a pena, muitas palestras bem interessantes sobre SQL Server e PowerShell.
Para inscrições e programação: http://www.online.techday.net.br/
Com certeza esse evento será um sucesso, todos os palestrantes são muito bons e o conteúdo técnico também está muito bom.
Eu já estou inscrito!!
As palestras serão ministradas em português e inglês.
Quem ainda não viu a programação com certeza vale a pena, muitas palestras bem interessantes sobre SQL Server e PowerShell.
Para inscrições e programação: http://www.online.techday.net.br/
Com certeza esse evento será um sucesso, todos os palestrantes são muito bons e o conteúdo técnico também está muito bom.
Eu já estou inscrito!!
Abraços,
Rodrigo Figueiredo
sexta-feira, 1 de outubro de 2010
Liberado SQL Server 2008 Service Pack 2
A Microsoft liberou ontem a versão final do SQL Server 2008 Service Pack 2. O Service Pack 2 atualiza qualquer instância do SQL Server 2008, do SQL Express ao SQL Enterprise e todas as ferramentas clientes. Esse service pack não serve para o SQL Server 2008 R2.
Um "detalhe" bem importante e que você deve estar totalmente certo antes de instalar o SP2, pois ele contem os hotfixes que foram incluídos nos Cumulative Update 1 até o Cumulative Update 8. Ou seja, se sua instância de SQL Server já está atualizada com o CU9 ou CU10 e se você realmente corrigiu o problema com um destes CU, pode ser que atualizando para o SP2 volte a ter o problema.
Segue link com a lista de bugs corrigidos pelo SQL Server 2008 SP2.
http://support.microsoft.com/kb/2285068
O build do SQL após ser aplicado o SP2 será 10.0.4000
Para mim as correções e atualizações mais importantes foram relacionadas a Clusterização e também que agora o SQL Server dará suporte ao PowerShell 2.0.
Sugestão: Não deixe de homologar o SP2 em seu ambiente de desenvolvimento
Um "detalhe" bem importante e que você deve estar totalmente certo antes de instalar o SP2, pois ele contem os hotfixes que foram incluídos nos Cumulative Update 1 até o Cumulative Update 8. Ou seja, se sua instância de SQL Server já está atualizada com o CU9 ou CU10 e se você realmente corrigiu o problema com um destes CU, pode ser que atualizando para o SP2 volte a ter o problema.
Segue link com a lista de bugs corrigidos pelo SQL Server 2008 SP2.
http://support.microsoft.com/kb/2285068
O build do SQL após ser aplicado o SP2 será 10.0.4000
Para mim as correções e atualizações mais importantes foram relacionadas a Clusterização e também que agora o SQL Server dará suporte ao PowerShell 2.0.
Sugestão: Não deixe de homologar o SP2 em seu ambiente de desenvolvimento
Abraços,
Rodrigo Figueiredo
Assinar:
Postagens (Atom)