Implementando Database Snapshot

Fala Galera,

Hoje iremos ver como criar um snapshot de um banco de dados e de que forma utilizar esta fotografia para fazer um restore em uma base de dados onde um update foi executado de forma incorreta.

Primeiro alguns conceitos deverão ser apresentados:

Um snaptshot é uma fotografia, somente leitura,  de uma base de dados em um dado momento no tempo; O acesso a esse DB é realizado de forma transparente, como se fosse uma base normal. Algumas possíveis utilizações dessa feature seria disponibilizar uma forma pela qual os clientes acessariam um DB  de exclusivo para relatórios, evitando assim concorrência na base original ou quando alguma mudança brusca nos dados ocorrer, permitindo assim um recover mais rápido da base.

Algumas premissas devem ser levadas em consideração quando se cria um snapshot, por exemplo:

  •  A base de dados snapshot deverá, obrigatoriamente, estar na mesma instância da base original.
  • Os snapshots NÃO são substitutos para os backups normais.
  • Não é possível criar um snapshot de bases de sistema como: master, tempdb ou model
  • Os comandos restore/backup database não são permitidos.
  • O sistema de arquivos deve ser NTFS, não há suporte para FAT32.
  • Catalogos FullText não podem ser usados com o database snapshot.
  • […]

Uma lista completa das limitações do database snapshot poderá ser encontrada em:

http://technet.microsoft.com/en-us/library/ms189940(v=sql.105).aspx

Não é possível criar um snapshot por uma interface gráfica, somente através de comandos transaction SQL. O comando utilizado para se criar um database snaptshot é o já conhecido CREATE DATABASE.

As mesmas permissões concedidas para um usuário que cria uma base de dados normal servirá para criar um snapshot (CREATE ANY DATABASE e ALTER ANY DATABASE) , a menos que o DB base seja espelhado, neste caso será necessário ser membro da role sysAdmin.

Para nossos exemplos iremos primeiramente será necessário criar uma base de dados

CREATE DATABASE EX_SNAPSHOT
GO
USE EX_SNAPSHOT

Agora uma nova tabela populada com registros falsos.

CREATE TABLE REGISTROS(
ID INT IDENTITY(1,1) PRIMARY KEY,
   TEXTO VARCHAR(max),
   DATA DATETIME DEFAULT (GETDATE()
)
GO
INSERT INTO REGISTROS (TEXTO)VALUES
('TEXTO 1'),
('TEXTO 2'),
('TEXTO 3'),
('TEXTO 4'),
('TEXTO 5'),
('TEXTO 6'),
('TEXTO 7'),
('TEXTO 8'),
('TEXTO 9'),
('TEXTO 10')

Estes são os dados contidos na nossa base de dados normal.

Imagem

Agora iremos criar um snaptshot baseado no banco de dados EX_SNAPSHOT, lembrando que a propriedade “NAME” deverá ser o nome lógico da base origial, esta informação poderá ser obtida através da DMV:

select NAME from sys.databases where database_id = DB_ID('EX_SNAPSHOT')

Script para criação do snapshot.

CREATE DATABASE DB_SNAPSHOT_3 ON(
NAME = 'EX_SNAPSHOT',
      FILENAME = 'D:\DB_SNAPTSHOT3.MDF'
) AS SNAPSHOT OF EX_SNAPSHOT 

Imagem

Conforme já foi dito, um database snapshot é read-only, ou seja, não são permitidas escritas ou atualizações nos dados lá contidos; O próximo comando irá simular um update no snapshot para que possamos observar o erro retornado.

USE DB_SNAPSHOT
GO
UPDATE REGISTROS SET [TEXTO].WRITE('123 ',0,0)

Imagem

Sabendo disso agora iremos fazer o mesmo update na base de dados original e após isso um select em cada uma das bases para que possamos constatar que existem diferenças entre as tabelas, e que um update na base original não afeta de nenhuma maneira seu snapshot.

USE EX_SNAPSHOT
GO
UPDATE REGISTROS SET TEXTO.Write('123 ',0,0)

SELECT * FROM DB_SNAPSHOT.DBO.REGISTROS
SELECT * FROM EX_SNAPSHOT.DBO.REGISTROS

Imagem

Suponhamos que você tenha feito um update de forma incorreta e deseja, através do snapshot, recuperar a base de dados original para que ela volte no estado que estava quando o snapshot foi tirado; Há apenas um porém, se você possuir mais um database snapshot eles deverão ser removidos, apenas um único pode existir, caso contrário o seguinte erro é apresentado:

Imagem

Agora iremos mudar o contexto do banco de dados para o Master e realizar o restore baseado no único snapshot que foi mantido.

USE master
GO
RESTORE DATABASE EX_SNAPSHOT FROM DATABASE_SNAPSHOT = 'DB_SNAPSHOT

Simples e fácil, basta fazer um select na tabla do DB ex_Snapshot e veremos que os registros retornaram para o estado que estavam no momento que o snapshot foi tirado.

Imagem

Bom é isso pessoal, espero que vocês tenham conseguido entender, pelo menos do básico, de como e onde utilizar essa feature do MS SQL Server.

[]’s

Criando e Executando um assembly pelo SQL Server

Fala Galera,

Todos sabemos que a linguagem T-SQL possui limitações, alguma vez você já precisou fazer algum processo muito elaborado e acabou esbarrando em alguma delas?

Pois é, saiba que o SQL Server possuí uma integração com linguagens de programação, Visual Basic e C#, o que nos permite utilizar todas suas vantagens.

Vamos inciar criando uma nova base de dados para os exemplos:


CREATE DATABASE EX_ASSEMBLY

GO

USE EX_ASSEMBLY

GO

O Common Runtime Language, por default, não vem habilitada na instancia, portanto será necessário faze-lo antes de iniciarmos.


EXEC sp_configure 'ADVANCED OPTIONS',1

RECONFIGURE WITH OVERRIDE

GO

EXEC sp_configure 'clr enabled',1

RECONFIGURE WITH OVERRIDE

Também habilitaremos o CMDShell para que possamos compilar a dll de dentro do SQL (poderia ser realizado pelo prompt de comando)


EXEC sp_configure 'xp_cmdshell',1

RECONFIGURE WITH OVERRIDE

Agora iremos criar uma função em C# bem simples, que irá concatenar uma string passada por parâmetro, a criação poderá ser realizada no notepad e salva com a extensão .cs .


using System;

using System.Data;

using System.Data.Sql;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class TesteBlogPiroto{

[SqlProcedure]

public static void ConcatenandoTexto(String parametro1){

String parametro = string.Concat("Blog do:", parametro1);

SqlContext.Pipe.Send(parametro);

}

};

Após criarmos o arquivo, que no meu exemplo está na raíz do diretório D:\ , nós precisaremos transforma-lo em uma DLL, para isso será necessário utilizar o comando xp_cmdshell, que nos permite executar comandos shell. Para compilar, precisamos do diretório que o nosso .net framework está instalado (no meu caso a versão usada é a 2).


declare @comando varchar(2000)

set @comando =
'C:\Windows\Microsoft.NET\Framework\v2.0.30319\csc
/target:library
/out:d:\BlogPiroto.dll d:\artigo.cs'

exec xp_cmdshell @comando

Depois de criarmos a dll será necessário importar seu conteúdo para o SQL Server e para isso iremos utilizar a instrução ‘create assembly’:


Create Assembly exBlogPiroto from  'D:\BlogPiroto.dll'
WITH PERMISSION_SET = SAFE

É de extrema importância atentarmos ao permissionamento concedido a um assembly criado, existem três opções:

Safe: O mais restritivo, além de ser a opção default e recomendado pela MS, restringe o acesso do assembly a somente o SQL Server.

UnSafe: Tem acesso irrestrito a todos os recursos da maquina, registro, rede, variaveis do sistema etc.

External_Access: Tem Acesso restrito aos recursos da maquina, pode usar código não gerenciado.

Nao esqueça de ser uma versão suportada pelo SQL, caso contrário o seguinte erro será apresentado

1

Depois de importar o assembly iremos criar uma procedure que fará referência ao método contido na dll.


CREATE PROCEDURE SP_CHAMAASSEMBLY

 (@PARAMETRO NVARCHAR(MAX))

AS EXTERNAL NAME
 EXBLOGPIROTO.TESTEBLOGPIROTO.CONCATENANDOTEXTO

O external name é formado da seguinte forma>  [Nome do Assembly].[Nome da Classe].[Nome do Método]

Agora faremos uma chamada a nossa recém criada procedure passando como parametro o texto a ser concatenado.


EXEC SP_CHAMAASSEMBLY ' DHIEGOPIROTO@GMAIL.COM '

Retorno:

2

Por hoje é isso pessoal!

[]’s

Dicionario de dados diretamente pelo SQL Server.

Fala Galera,

Imaginem um mundo ideal, onde todas as tabelas e campos que criamos são bem documentados e estão sempre a mão…ok! podem acordar…sei que são poucos os ambientes onde isso ocorre.

Onde trabalho atualmente, uma grande empresa de marketing em SP, estamos implantando este tipo de documentação, e digo com propriedade que este  processo ajuda muito na hora de realizar uma manutenção no sistema.

Bem, vamos documentar o que cada campo de uma dada tabela deverá armazenar através da system procedure : sp_addextendedproperty

Primeiramente devemos criar uma procedure que irá nos dará mais agilidade na hora da documentação.


create procedure addDescricaoCampo(@Tabela as varchar(100) , @Coluna as varchar(100) , @Descricao as Varchar(1000) )
as begin
EXEC sys.sp_addextendedproperty
@name= 'Caption',
@value= @Descricao ,
@level0type='SCHEMA',
@level0name='dbo',
@level1type='TABLE',
@level1name= @Tabela,
@level2type='COLUMN',
@level2name= @coluna
end --procedure

CREATE TABLE EdaComplementoMailing(
CelularClaroCOmvc_VD varchar(11),
CNPJ_PDV varchar(14),
CelularClaroComvc_Vpdv varchar(11)
)

Agora, utilizando a nossa proc, vamos incluir as informações de nossos campos.


--Popula dicionário de Dados (adiciona descrição aos campos)
exec addDescricaoCampo 'EdaComplementoMailing','CNPJ_PDV','Campo que ir armarzenar o nmero do CPF ou CNPJ do Ponto de Venda '
go
exec addDescricaoCampo 'EdaComplementoMailing','CelularClaroCOmvc_VD','Campo que ir armarzenar o nmero do telefone celular da campanha ClaroComVc do Vendedor do Distribuidor'
go
exec addDescricaoCampo 'EdaComplementoMailing','CelularClaroComvc_Vpdv','Campo que ir armarzenar o nmero do telefone celular da campanha ClaroComVc do Vendedor do Ponto de Venda'

 

 

Muito bom! Mas e agora? Como vamos consultar nossa documentação?

Bem, através de algumas tabelas de sistema o SQL Server nos permite fazer isto.


--–Consulta Dicionário de Dados
Select c.Object_id,Objetos.Name,c.Name,p.Value,t.Name,c.Precision,c.Scale,
Case c.Is_Nullable When 0 then 'No' else 'Sim' End as Nulo,
Case c.Is_Computed When 0 then 'No' else 'Sim' End as Computado,
Case c.Is_Identity When 0 then 'No' else 'Sim' End as 'Identity', c.Collation_name,
Case c.Is_Replicated When 0 then 'No' else 'Sim' End as 'Replicado'
from Sys.All_Columns C
Join Sys.Types T On t.System_Type_Id=c.System_Type_id
Left Outer Join Sys.SysComments Cc On cc.Id=c.Default_Object_Id
Left Outer Join Sys.Extended_Properties P On p.Major_id=c.Object_id and Minor_id=c.Column_Id
Join Sys.All_Objects Objetos On Objetos.Object_id=c.Object_Id
where c.object_id = object_id('EdaComplementoMailing')

UPDATE:
Um dia desses acabei ajudando uma leitora do blog (Amanda) e criei o seguinte script
que gera metade do código necessário para criar seu dicionário de dados:

</pre>
<pre>/*O select abaixo vai gerar um resultset com todas as colunas de todas as suas tabelas já no
padrão de criação do dicionário de dados.
Basca voce copiar o resultado, adicionar a info de cada coluna, executar e depois conultar (ultimo passo)*/
SELECT 
 ' exec addDescricaoCampo ''' + t.NAME + ''','''+ c.NAME + ''',''[DESCRIÇÃO CAMPO]'''
FROM sys.objects t INNER JOIN sys.columns c
ON t.OBJECT_ID = c.OBJECT_ID WHERE t.TYPE = 'U' ORDER BY t.NAME, c.NAME

Por hora é isso.

Abraços.

Linked Server entre o mySQL e o SQL Server

Fala Galera, esta semana precisei acessar uma base de dados mySQL e importar alguns registros para uma base SQL Server; achei que seria interessante compartilhar os passos que segui para realizar a tarefa. Optei por fazer a comunicação entre as duas bases através de um linked server e utilizando o provider disponibilizado pelo pela própria comunidade mySQL.

Antes que possamos continuar, certifique-se que você possua o Provider de acesso ao mySQL cadastrado no windows, para isso será necessário fazer a verificação no ODBC Data Source Administrator (para mais informações consulte: http://msdn.microsoft.com/en-us/library/ms188691.aspx). Caso você ainda não possua o provider instalado poderá fazer o download através do: http://dev.mysql.com/downloads/connector/odbc/ .

Agora que o provider do mySQL já está instalado na maquina, vamos vincula-lo a uma base de dados. Clique em “ADD” e preencha o formulário para cadastrar a base.

Preenche adequadamente o formulário e faça o teste de comunicação.

Muito bem! Já temos nossa base de dados mySQL cadastrada. Agora abra o Microsoft SQL Server Management Studio (SSMS), pois vamos criar o linked server que possibilitará o acesso aos dados da outra base.

Mas antes vamos alterar algumas propriedades do provider MSDASQL, evitando assim alguns erros de comunicação com o linkedserver. Acesse sua instância do SQL Server, vá em “server objects”, depois “providers” e dê um duplo clique no “MSDASQL”. Selecione as opçoões conforme a imagem abaixo:

Feito isto vá em “server objects” e clique com o botão direito em “New LinkedServer”. Basta fazer o preenchimento dos campos DataSource e ProductName, conforme a imagem a seguir:

Clique em “OK” e pronto! Nosso linked Server está criado e pronto para acessar os dados contidos na base de dados mySQL.

Que tal testarmos nosso linkedServer? A query abaixo seleciona do ID UM da tabela usuarios (esta tabela está no servidor mySQL)

SELECT * FROM OPENQUERY(MYSQLPROVIDER,’SELECT * FROM usuario WHERE ID = 1′)

Bom gente, por hoje é isso.

Qualquer dúvida estou a disposição.

Abraços.

Alex Souza

Banco de dados, Business Intelligence, Big Data, Governança de TI ... /* Um eterno aprendiz... */

Blog - Thiago Carlos de Alencar

Aprendendo SQL Server !

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert

Vitor Fava

SELECT (CrazyIdeas*2), (InsaneIdeas*100), MyExperience FROM MyBigHead WHERE InsaneLevel > 1000

Think Think SQL

DBCC DumpMemory 'TECH','ALL'

Gustavo Maia Aguiar

Artigos, dicas e algumas reflexões sobre o SQL Server

Kimberly L. Tripp

DBCC DumpMemory 'TECH','ALL'

Thiago Zavaschi R2

www.zavaschi.com

Blog do Luti

DBCC DumpMemory 'TECH','ALL'

Luan.Moreno a.k.a [SQL.Soul]

Lead Database Consultant at Pythian

Blog do Leka

let's make things better