Finalmente voltei... depois de um longo período sem publicar nada no blog devido a problemas particulares, estou voltando com força total!
Vou escrever hoje sobre uma novidade na dependência de objetos no SQL Server 2008. Até o SQL Server 2005 toda a dependência de objetos era armazenada utilizando os IDs dos objetos, gerando informações incompletas ao se consultar o metadata com sp_depends ou sql_dependencies. Para entender porque veja o script abaixo:
use tempdb
go
create table Ref1 (col1 int)
create table Ref2 (col1 int)
go
create proc spu
as
select * from dbo.Ref1
select * from dbo.Ref2
select * from dbo.Ref3
go
A Stored Procedure SPU acessa três tabelas, porém uma delas (Ref3) não existe no banco de dados, “Deferred Name Resolution” (Resolução de Nomes Tardia). Tanto sp_depends quanto sql_dependencies retornam apenas Ref1 e Ref2 como objetos referenciados pela Stored Procedure SPU, observe no script abaixo:
exec sp_depends 'dbo.spu'
select OBJECT_NAME([object_id]) ObjAnalisado,
OBJECT_NAME(referenced_major_id) ObjReferenciado
from sys.sql_dependencies
where [object_id] = object_id('dbo.spu')
O SQL Server 2008 trouxe novidades, uma View (sql_expression_dependencies) e duas funções (dm_sql_referenced_entities e dm_sql_referencing_entities), que retornam a dependência dos objetos através do nome, resolvendo o problema descrito acima. Veja no script abaixo:
select OBJECT_NAME(referencing_id) ObjAnalisado, referenced_entity_name, referenced_id
from sys.sql_expression_dependencies
where referencing_id = object_id('dbo.spu')
select * from sys.dm_sql_referenced_entities('dbo.spu','OBJECT')
select * from sys.dm_sql_referencing_entities('dbo.spu','OBJECT')
Até o próximo post,
Landry.
terça-feira, 2 de junho de 2009
quinta-feira, 12 de fevereiro de 2009
Diferença na ocupação do Transaction Log após término de Transação
Olá... Resolvi escrever outro post sobre Transaction Log devido a grande quantidade de dúvidas que recebo dos alunos, principalmente quanto ao crescimento deste arquivo. Vale a penas recordar os 3 artigos sobre a estrutura do Transaction Log no link http://sqlserver-brasil.blogspot.com/search/label/Transation%20Log.
Uma dúvida freqüente dos meus alunos e clientes é a diferença entre o tamanho alocado no Transaction Log durante uma transação e ao seu final. Vamos visualizar esta diferença utilizando o script abaixo:
Primeiro crie um banco de dados para teste chamado Fluminense com o Recovery Model FULL.
-- Criar pasta c:\TesteLog
USE master
go
CREATE DATABASE Fluminense ON
(NAME = 'Fluminense',
FILENAME = 'C:\TesteLog\Fluminense.mdf',
SIZE = 512MB,FILEGROWTH = 100MB)
LOG ON
(NAME = 'Fluminense_log',
FILENAME = 'C:\TesteLog\Fluminense_log.ldf',
SIZE = 1MB,FILEGROWTH = 1MB)
go
ALTER DATABASE Fluminense SET RECOVERY FULL
Agora vamos verificar a ocupação do Transaction Log utilizando o comando abaixo:
DBCC SQLPERF (LOGSPACE)
-- Resultado:
-- Log Size (MB): 0.9921875
-- Log Space Used (%): 34.69488
Veja que o Transaction Log está com 34% de ocupação, o que corresponde a 0,34 MB. Agora vamos gerar atividade neste banco para observarmos a escrita no Transaction Log, rode o script abaixo:
USE Fluminense
go
CREATE TABLE Teste (c1 INT IDENTITY,
C2 CHAR (8000) DEFAULT (REPLICATE ('F', 8000)));
go
BEGIN TRAN
DECLARE @i int
set @i = 0
WHILE (@i < 50500)
BEGIN
INSERT INTO Teste DEFAULT VALUES
SELECT @i = @i + 1
END
Execute o DBCC SQLPERF (LOGSPACE) para verificar o crescimento:
DBCC SQLPERF (LOGSPACE)
-- Resultado:
-- Log Size (MB): 555,9922
-- Log Space Used (%):99,89989
O Transaction Log passou de 0,34 MB para 555,43 MB. Agora vamos executar um ROLLBACK e verificar a ocupação do Transaction Log novamente:
ROLLBACK TRAN;
GO
DBCC SQLPERF (LOGSPACE)
-- Resultado:
-- Log Size (MB): 555,9922
-- Log Space Used (%):99,89989
O Transaction Log passou a ocupar 473,42 MB, liberando 82,01 MB de espaço interno! Que comportamento estranho... Porque o ROLLBACK liberou espaço?
Bem, a resposta é simples... Enquanto uma transação está em curso o SQL Server já aloca espaço no Transaction Log para realizar a operação de RollBack, sendo liberado este espaço ao final da transação. Se não fosse reservado este espaço no Transaction Log, ao esgotar o espaço em disco durante uma transação, o banco de dados entraria em estado SUSPECT, devido a inviabilidade de realizar o RollBack com conseqüente perda de integridade.
Até o próximo post,
Landry.
Uma dúvida freqüente dos meus alunos e clientes é a diferença entre o tamanho alocado no Transaction Log durante uma transação e ao seu final. Vamos visualizar esta diferença utilizando o script abaixo:
Primeiro crie um banco de dados para teste chamado Fluminense com o Recovery Model FULL.
-- Criar pasta c:\TesteLog
USE master
go
CREATE DATABASE Fluminense ON
(NAME = 'Fluminense',
FILENAME = 'C:\TesteLog\Fluminense.mdf',
SIZE = 512MB,FILEGROWTH = 100MB)
LOG ON
(NAME = 'Fluminense_log',
FILENAME = 'C:\TesteLog\Fluminense_log.ldf',
SIZE = 1MB,FILEGROWTH = 1MB)
go
ALTER DATABASE Fluminense SET RECOVERY FULL
Agora vamos verificar a ocupação do Transaction Log utilizando o comando abaixo:
DBCC SQLPERF (LOGSPACE)
-- Resultado:
-- Log Size (MB): 0.9921875
-- Log Space Used (%): 34.69488
Veja que o Transaction Log está com 34% de ocupação, o que corresponde a 0,34 MB. Agora vamos gerar atividade neste banco para observarmos a escrita no Transaction Log, rode o script abaixo:
USE Fluminense
go
CREATE TABLE Teste (c1 INT IDENTITY,
C2 CHAR (8000) DEFAULT (REPLICATE ('F', 8000)));
go
BEGIN TRAN
DECLARE @i int
set @i = 0
WHILE (@i < 50500)
BEGIN
INSERT INTO Teste DEFAULT VALUES
SELECT @i = @i + 1
END
Execute o DBCC SQLPERF (LOGSPACE) para verificar o crescimento:
DBCC SQLPERF (LOGSPACE)
-- Resultado:
-- Log Size (MB): 555,9922
-- Log Space Used (%):99,89989
O Transaction Log passou de 0,34 MB para 555,43 MB. Agora vamos executar um ROLLBACK e verificar a ocupação do Transaction Log novamente:
ROLLBACK TRAN;
GO
DBCC SQLPERF (LOGSPACE)
-- Resultado:
-- Log Size (MB): 555,9922
-- Log Space Used (%):99,89989
O Transaction Log passou a ocupar 473,42 MB, liberando 82,01 MB de espaço interno! Que comportamento estranho... Porque o ROLLBACK liberou espaço?
Bem, a resposta é simples... Enquanto uma transação está em curso o SQL Server já aloca espaço no Transaction Log para realizar a operação de RollBack, sendo liberado este espaço ao final da transação. Se não fosse reservado este espaço no Transaction Log, ao esgotar o espaço em disco durante uma transação, o banco de dados entraria em estado SUSPECT, devido a inviabilidade de realizar o RollBack com conseqüente perda de integridade.
Até o próximo post,
Landry.
quinta-feira, 15 de janeiro de 2009
Recuperando Banco de Dados no SQL Server 2005 e 2008
Desculpem o silêncio neste final de 2008.... após as férias de Dezembro, fiquei atolado de trabalho nestas duas semanas iniciais de Janeiro! Vamos iniciar 2009 com um artigo sobre recuperação de banco de dados...
Os scripts deste artigo funcionam tanto no SQL Server 2005 quanto no SQL Server 2008!
Primeiro vamos criar um banco de dados para teste, o script abaixo cria o banco TesteDB contendo uma tabela de Clientes:
USE master
go
CREATE DATABASE TesteDB
go
USE TesteDB
go
CREATE TABLE Clientes (
ClientesID int not null,
Nome varchar(40) not null,
Telefone varchar(20) null)
go
INSERT Clientes VALUES (1,'Jose','(21) 2521-2252')
INSERT Clientes VALUES (2,'Maria','(21) 2521-1151')
go
Agora para corromper o banco TesteDB, basta simular uma queda de energia:
1) Na mesma conexão utilizada no script anterior, execute o código abaixo e mantenha a conexão aberta:
-- Abre a transacao e atualiza tabela
BEGIN TRAN
UPDATE Clientes SET Telefone = '(21) 2521-1151'
WHERE ClientesID = 2
go
CHECKPOINT
go
2) Abra uma nova conexão e execute o comando “SHUTDOWN WITH NOWAIT”.
O SQL Server para o serviço com o arquivo de dados atualizado pela transação que ficou em aberto, mantendo no arquivo de log o registro desta transação incompleta. Em uma situação normal, O SQL Server resolveria o problema ao iniciar, executando um ROLLBACK da transação que ficou em aberto, utilizando as informações do arquivo de log.
Para corromper o arquivo de log basta abri-lo no NOTEPAD, apagar alguns caracteres no seu início e salvar. Após corromper o arquivo de log inicie o SQL Server e tente entrar no banco TesteDB, teremos a mensagem de erro abaixo:
USE TesteDB
Resultado:
Msg 945, Level 14, State 2, Line 1
Database 'TesteDB' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Se você pesquisar no log do serviço (Management Studio – Object Explorer – Management – SQl Server Logs) veremos as mensagens abaixo:
- File activation failure. The physical file name "F:\sql2005\TesteDB_log.LDF" may be incorrect.
- The header for file 'F:\sql2005\TesteDB_log.LDF' is not a valid database file header. The PageAudit property is incorrect.
Para verificar o status do banco TesteDB:
SELECT databasepropertyex ('TesteDB', 'STATUS');
Resultado: SUSPECT
Vamos tentar resolver o problema com Detach e depois Attach:
EXEC sp_detach_db 'TesteDB'
Resultado:
Msg 947, Level 16, State 1, Line 1 Error while closing database 'TesteDB'. Check for previous additional errors and retry the operation.
O Detach ocorreu, apesar do erro alertando o estado inconsistente do banco de dados. Verifique com o comando abaixo:
SELECT * FROM sys.databases WHERE NAME = 'TesteDB'
Se você tentar realizar um Attach utilizando sp_attach_db a operação irá falhar, pois o arquivo de log foi corrompido.
EXEC sp_attach_db @dbname = 'TesteDB',
@filename1 = 'F:\sql2005\TesteDB.mdf',
@filename2 = 'F:\sql2005\TesteDB_log.LDF'
Resultado:
Msg 5172, Level 16, State 15, Line 1 The header for file 'F:\sql2005\TesteDB_log.LDF' is not a valid database file header. The FILE SIZE property is incorrect.
Outra opção, que também não irá funcionar, seria tentar criar um novo arquivo de log utilizando a opção ATTACH_REBUILD_LOG do comando CREATE DATABASE.
CREATE DATABASE TesteDB ON
(NAME = TesteDB, FILENAME = 'F:\sql2005\TesteDB.mdf')
FOR ATTACH_REBUILD_LOG
Resultado:
File activation failure. The physical file name "F:\sql2005\TesteDB_log.LDF" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Msg 1813, Level 16, State 2, Line 1 Could not open new database 'TesteDB'. CREATE DATABASE is aborted.
Para resolver o problema, basta seguir os passos abaixo:
1) Mover (copiar para outra pasta e apagar) os arquivos de dados e log do banco TesteDB para outro local.
2) Criar um novo banco com o mesmo nome (TesteDB) e parar o serviço do SQL Server.
CREATE DATABASE TesteDB
SHUTDOWN
3) Apagar o arquivo de log e copiar o arquivo de dados que você salvou no item 1, por cima do arquivo novo criado no item 2 acima.
4) Executar o script abaixo.
ALTER DATABASE TesteDB SET EMERGENCY
ALTER DATABASE TesteDB SET SINGLE_USER
go
DBCC CHECKDB (TesteDB, REPAIR_ALLOW_DATA_LOSS)
WITH NO_INFOMSGS, ALL_ERRORMSGS
go
ALTER DATABASE TesteDB SET read_write
ALTER DATABASE TesteDB SET multi_user
go
Repare que foi gerado um novo arquivo de log e o banco de dados foi recuperado, verifique rodando o script abaixo:
USE TesteDB
go
select * from TesteDB.dbo.Clientes
Até o próximo post e feliz 2009,
Landry.
Os scripts deste artigo funcionam tanto no SQL Server 2005 quanto no SQL Server 2008!
Primeiro vamos criar um banco de dados para teste, o script abaixo cria o banco TesteDB contendo uma tabela de Clientes:
USE master
go
CREATE DATABASE TesteDB
go
USE TesteDB
go
CREATE TABLE Clientes (
ClientesID int not null,
Nome varchar(40) not null,
Telefone varchar(20) null)
go
INSERT Clientes VALUES (1,'Jose','(21) 2521-2252')
INSERT Clientes VALUES (2,'Maria','(21) 2521-1151')
go
Agora para corromper o banco TesteDB, basta simular uma queda de energia:
1) Na mesma conexão utilizada no script anterior, execute o código abaixo e mantenha a conexão aberta:
-- Abre a transacao e atualiza tabela
BEGIN TRAN
UPDATE Clientes SET Telefone = '(21) 2521-1151'
WHERE ClientesID = 2
go
CHECKPOINT
go
2) Abra uma nova conexão e execute o comando “SHUTDOWN WITH NOWAIT”.
O SQL Server para o serviço com o arquivo de dados atualizado pela transação que ficou em aberto, mantendo no arquivo de log o registro desta transação incompleta. Em uma situação normal, O SQL Server resolveria o problema ao iniciar, executando um ROLLBACK da transação que ficou em aberto, utilizando as informações do arquivo de log.
Para corromper o arquivo de log basta abri-lo no NOTEPAD, apagar alguns caracteres no seu início e salvar. Após corromper o arquivo de log inicie o SQL Server e tente entrar no banco TesteDB, teremos a mensagem de erro abaixo:
USE TesteDB
Resultado:
Msg 945, Level 14, State 2, Line 1
Database 'TesteDB' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Se você pesquisar no log do serviço (Management Studio – Object Explorer – Management – SQl Server Logs) veremos as mensagens abaixo:
- File activation failure. The physical file name "F:\sql2005\TesteDB_log.LDF" may be incorrect.
- The header for file 'F:\sql2005\TesteDB_log.LDF' is not a valid database file header. The PageAudit property is incorrect.
Para verificar o status do banco TesteDB:
SELECT databasepropertyex ('TesteDB', 'STATUS');
Resultado: SUSPECT
Vamos tentar resolver o problema com Detach e depois Attach:
EXEC sp_detach_db 'TesteDB'
Resultado:
Msg 947, Level 16, State 1, Line 1 Error while closing database 'TesteDB'. Check for previous additional errors and retry the operation.
O Detach ocorreu, apesar do erro alertando o estado inconsistente do banco de dados. Verifique com o comando abaixo:
SELECT * FROM sys.databases WHERE NAME = 'TesteDB'
Se você tentar realizar um Attach utilizando sp_attach_db a operação irá falhar, pois o arquivo de log foi corrompido.
EXEC sp_attach_db @dbname = 'TesteDB',
@filename1 = 'F:\sql2005\TesteDB.mdf',
@filename2 = 'F:\sql2005\TesteDB_log.LDF'
Resultado:
Msg 5172, Level 16, State 15, Line 1 The header for file 'F:\sql2005\TesteDB_log.LDF' is not a valid database file header. The FILE SIZE property is incorrect.
Outra opção, que também não irá funcionar, seria tentar criar um novo arquivo de log utilizando a opção ATTACH_REBUILD_LOG do comando CREATE DATABASE.
CREATE DATABASE TesteDB ON
(NAME = TesteDB, FILENAME = 'F:\sql2005\TesteDB.mdf')
FOR ATTACH_REBUILD_LOG
Resultado:
File activation failure. The physical file name "F:\sql2005\TesteDB_log.LDF" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Msg 1813, Level 16, State 2, Line 1 Could not open new database 'TesteDB'. CREATE DATABASE is aborted.
Para resolver o problema, basta seguir os passos abaixo:
1) Mover (copiar para outra pasta e apagar) os arquivos de dados e log do banco TesteDB para outro local.
2) Criar um novo banco com o mesmo nome (TesteDB) e parar o serviço do SQL Server.
CREATE DATABASE TesteDB
SHUTDOWN
3) Apagar o arquivo de log e copiar o arquivo de dados que você salvou no item 1, por cima do arquivo novo criado no item 2 acima.
4) Executar o script abaixo.
ALTER DATABASE TesteDB SET EMERGENCY
ALTER DATABASE TesteDB SET SINGLE_USER
go
DBCC CHECKDB (TesteDB, REPAIR_ALLOW_DATA_LOSS)
WITH NO_INFOMSGS, ALL_ERRORMSGS
go
ALTER DATABASE TesteDB SET read_write
ALTER DATABASE TesteDB SET multi_user
go
Repare que foi gerado um novo arquivo de log e o banco de dados foi recuperado, verifique rodando o script abaixo:
USE TesteDB
go
select * from TesteDB.dbo.Clientes
Até o próximo post e feliz 2009,
Landry.
quarta-feira, 12 de novembro de 2008
SQL Server 2008 SSIS – Change Data Capture (CDC) Parte 2
CDC e Integration Service (SSIS)
No ultimo post escrevi sobre uma nova funcionalidade do SQL Server 2008 chamada Change Data Capture (CDC), onde é criada uma infra-instrutora para registrar as alterações que ocorrem em uma tabela, ficando simples identificar as alterações nos dados durante a carga com o Integration Service (SSIS).
Para maiores detalhes veja a primeira parte em http://sqlserver-brasil.blogspot.com/2008/11/sql-server-2008-ssis-change-data.html.
Neste post vamos ver como utilizar o CDC dentro do SSIS, identificando facilmente as alterações nos dados durante a carga.
No post anterior mostrei o conteúdo da tabela cdc.dbo_Cliente_CT que contém o registro das operações que ocorreram na tabela Cliente, veja na imagem abaixo:
Select * from cdc.dbo_Cliente_CT
No ultimo post escrevi sobre uma nova funcionalidade do SQL Server 2008 chamada Change Data Capture (CDC), onde é criada uma infra-instrutora para registrar as alterações que ocorrem em uma tabela, ficando simples identificar as alterações nos dados durante a carga com o Integration Service (SSIS).
Para maiores detalhes veja a primeira parte em http://sqlserver-brasil.blogspot.com/2008/11/sql-server-2008-ssis-change-data.html.
Neste post vamos ver como utilizar o CDC dentro do SSIS, identificando facilmente as alterações nos dados durante a carga.
No post anterior mostrei o conteúdo da tabela cdc.dbo_Cliente_CT que contém o registro das operações que ocorreram na tabela Cliente, veja na imagem abaixo:
Select * from cdc.dbo_Cliente_CT
As linhas identificadas no resultado acima representam:
Azul INSERT (_$operation = 2)
Vermelho UPDATE antes da alteração (_$operation = 3)
Vermelho UPDATE após a alteração (_$operation = 4)
Verde DELETE (_$operation = 1)
O primeiro problema que temos é identificar as alterações que ocorreram em um determinado período (entre duas cargas de dados), porque na tabela acima não existe uma coluna com data e hora! A chave para resolver este problema está na coluna _$start_lsn, que contém uma numeração em Hexa seqüencial (LSN – Log Sequence Number). A função fn_cdc_map_time_to_lsn converte uma data e hora no LSN mais próximo! O script abaixo identifica o intervalo de LSN a ser utilizado na consulta na tabela acima:
DECLARE @Data_INI datetime, @Data_FIM datetime,
@lsn_INI binary(10), @lsn_FIM binary(10);
SET @Data_INI = GETDATE() -1
SET @Data_FIM = GETDATE();
SET @lsn_INI = sys.fn_cdc_map_time_to_lsn
('smallest greater than or equal', @Data_INI);
SET @lsn_FIM = sys.fn_cdc_map_time_to_lsn
('largest less than or equal', @Data_FIM);
O próximo problema é identificar as operações que devem ser realizadas durante a carga, utilizando as entradas na tabela cdc.dbo_Cliente_CT, dentro do período especificado acima. No exemplo da figura acima temos seis linhas, onde as três primeiras são inclusões, a quarta e quinta uma atualização e a última exclusão.
1) A cliente Maria foi incluída e depois não sofreu alteração, por isso esta deverá ser inserida no destino.
2) O cliente Jose foi incluído e depois alterado, sendo assim este cliente deverá ser inserido no destino com o telefone 2211-1111, que foi modificado na alteração registrada nas linhas quatro e cinco.
3) O cliente Antonio foi incluído e depois excluído (linhas 3 e 6 respectivamente), por isso este não deve fazer parte da carga.
Para facilitar o SQL Server 2008 disponibiliza a função cdc.fn_cdc_get_net_changes_dbo_cliente (em azul a porção variável do nome) que realiza a análise acima em um intervalo de LSN e retorna apenas as operações que devem ser feitas no destino. No exemplo acima o retorno da função seria: inclusão da Maria e Jose com o telefone novo! Na Stored Procedure abaixo temos o procedimento completo, ficando fácil utilizar este recurso no Integration Service:
CREATE PROC dbo.cdc_Cliente
@Data_INI datetime, @Data_FIM datetime
as
SET @Data_INI = GETDATE() -1
SET @Data_FIM = GETDATE();
DECLARE @lsn_INI binary(10), @lsn_FIM binary(10);
SET @lsn_INI = sys.fn_cdc_map_time_to_lsn
('smallest greater than or equal', @Data_INI);
SET @lsn_FIM = sys.fn_cdc_map_time_to_lsn
('largest less than or equal', @Data_FIM);
SELECT *
FROM cdc.fn_cdc_get_net_changes_dbo_cliente
(@lsn_INI, @lsn_FIM, 'all');
go
No próximo post veremos um exemplo de pacote SSIS utilizando os recursos deste post, até lá.
Landry.
Azul INSERT (_$operation = 2)
Vermelho UPDATE antes da alteração (_$operation = 3)
Vermelho UPDATE após a alteração (_$operation = 4)
Verde DELETE (_$operation = 1)
O primeiro problema que temos é identificar as alterações que ocorreram em um determinado período (entre duas cargas de dados), porque na tabela acima não existe uma coluna com data e hora! A chave para resolver este problema está na coluna _$start_lsn, que contém uma numeração em Hexa seqüencial (LSN – Log Sequence Number). A função fn_cdc_map_time_to_lsn converte uma data e hora no LSN mais próximo! O script abaixo identifica o intervalo de LSN a ser utilizado na consulta na tabela acima:
DECLARE @Data_INI datetime, @Data_FIM datetime,
@lsn_INI binary(10), @lsn_FIM binary(10);
SET @Data_INI = GETDATE() -1
SET @Data_FIM = GETDATE();
SET @lsn_INI = sys.fn_cdc_map_time_to_lsn
('smallest greater than or equal', @Data_INI);
SET @lsn_FIM = sys.fn_cdc_map_time_to_lsn
('largest less than or equal', @Data_FIM);
O próximo problema é identificar as operações que devem ser realizadas durante a carga, utilizando as entradas na tabela cdc.dbo_Cliente_CT, dentro do período especificado acima. No exemplo da figura acima temos seis linhas, onde as três primeiras são inclusões, a quarta e quinta uma atualização e a última exclusão.
1) A cliente Maria foi incluída e depois não sofreu alteração, por isso esta deverá ser inserida no destino.
2) O cliente Jose foi incluído e depois alterado, sendo assim este cliente deverá ser inserido no destino com o telefone 2211-1111, que foi modificado na alteração registrada nas linhas quatro e cinco.
3) O cliente Antonio foi incluído e depois excluído (linhas 3 e 6 respectivamente), por isso este não deve fazer parte da carga.
Para facilitar o SQL Server 2008 disponibiliza a função cdc.fn_cdc_get_net_changes_dbo_cliente (em azul a porção variável do nome) que realiza a análise acima em um intervalo de LSN e retorna apenas as operações que devem ser feitas no destino. No exemplo acima o retorno da função seria: inclusão da Maria e Jose com o telefone novo! Na Stored Procedure abaixo temos o procedimento completo, ficando fácil utilizar este recurso no Integration Service:
CREATE PROC dbo.cdc_Cliente
@Data_INI datetime, @Data_FIM datetime
as
SET @Data_INI = GETDATE() -1
SET @Data_FIM = GETDATE();
DECLARE @lsn_INI binary(10), @lsn_FIM binary(10);
SET @lsn_INI = sys.fn_cdc_map_time_to_lsn
('smallest greater than or equal', @Data_INI);
SET @lsn_FIM = sys.fn_cdc_map_time_to_lsn
('largest less than or equal', @Data_FIM);
SELECT *
FROM cdc.fn_cdc_get_net_changes_dbo_cliente
(@lsn_INI, @lsn_FIM, 'all');
go
No próximo post veremos um exemplo de pacote SSIS utilizando os recursos deste post, até lá.
Landry.
domingo, 9 de novembro de 2008
Palestras SQL Server Landry
Olá,
Segue abaixo as minhas palestras para download (PPTs e Demonstrações):
- SQL Server 2005
Atualizando para Microsoft SQL Server 2005
Local: Instituto Infnet (Rio de Janeiro)
Data: 04/06/2007
Link: http://www.4shared.com/file/70459690/a009ce7b/SQL2005_Upgrade.html
Microsoft SQL Express 2005
Local: Intituto Infnet (Rio de Janeiro)
Data: 06/06/2007
Link: http://www.4shared.com/file/70460708/447aa26d/SQL2005_Express.html
- SQL Server 2008
Novidades no Integration Service (SSIS) do SQL Server 2008
Local: Allen
Data: 08/11/2008
Link: http://www.4shared.com/file/70458232/b5d8864/SQL2008_NovidadesSSIS.html
Espero que gostem!
Landry
Segue abaixo as minhas palestras para download (PPTs e Demonstrações):
- SQL Server 2005
Atualizando para Microsoft SQL Server 2005
Local: Instituto Infnet (Rio de Janeiro)
Data: 04/06/2007
Link: http://www.4shared.com/file/70459690/a009ce7b/SQL2005_Upgrade.html
Microsoft SQL Express 2005
Local: Intituto Infnet (Rio de Janeiro)
Data: 06/06/2007
Link: http://www.4shared.com/file/70460708/447aa26d/SQL2005_Express.html
- SQL Server 2008
Novidades no Integration Service (SSIS) do SQL Server 2008
Local: Allen
Data: 08/11/2008
Link: http://www.4shared.com/file/70458232/b5d8864/SQL2008_NovidadesSSIS.html
Espero que gostem!
Landry
quinta-feira, 6 de novembro de 2008
SQL Server 2008 SSIS – Change Data Capture (CDC) Parte 1
SQL Server 2008 SSIS – Change Data Capture (CDC) Parte 1
Estou no final da elaboração da minha palestra no SQL Server 2008 Community Lauch aqui no Rio de Janeiro, faltam apenas dois assuntos CDC e Script... Neste post vou escrever sobre o Change Data Capture (CDC).
O CDC tem como objetivo registrar todas as alterações que ocorrem em uma tabela facilitando o processo de exportação. Primeiro vamos aprender a habilitar e configurar esta funcionalidade, depois como fazer uso deste log no SSIS.
Habilitando e Configurando o CDC
Vamos criar um banco de dados para utilizar neste post como exemplo, execute o script abaixo:
USE master
go
IF EXISTS (SELECT name FROM sys.databases WHERE name = 'TesteCDC')
DROP DATABASE TesteCDC
go
CREATE DATABASE TesteCDC
go
Para habilitar o CDC no banco criado acima utilizamos a Stored Procedure sp_cdc_enable_db:
USE TesteDCD
go
EXEC sys.sp_cdc_enable_db
Podemos verificar se um banco de dados está habilitado para o CDC com a query abaixo:
SELECT [name] as Banco, is_cdc_enabled FROM sys.databases
O SQL Server 2008 cria alguns objetos quando um banco é habilitado para o CDC:
- Usuário: cdc
- Schema: cdc
- Tabelas de Sistema: capture_columns, change_tables, ddl_history, índex_columns, lsn_time_mapping e dbo.systranschemas.
Agora vamos criar uma tabela para ver o CDC em ação, execute o script abaixo:
USE TesteCDC
go
CREATE TABLE Cliente (
ClienteID int not null primary key,
Nome varchar(30) not null,
Telefone varchar(30) null)
go
Para habilitar o CDC na tabela criada acima basta utilizar a Stored Procedure sp_cdc_enable_table, veja o exemplo abaixo:
EXEC sp_cdc_enable_table 'dbo', 'Cliente',
@role_name = NULL, @supports_net_changes =1
A Stored Procedure pc_cdc_enable_table cria alguns objetos no SQL Server 2008: uma tabela de sistema (cdc.dbo_Cliente_CT) e dois Jobs do SQL Server Agent (cdc.TesteCDC_capture, cdc.TesteCDC_cleanup).
Para obter a lista de tabelas habilitadas para CDC execute a consulta abaixo:
Select [name], is_tracked_by_cdc from sys.tables
Agora vamos testar incluindo, excluindo e alterando linhas na tabela Cliente:
-- Insert
insert dbo.Cliente values (1,'Jose','2236-1111')
insert dbo.Cliente values (2,'Maria','2236-2222')
insert dbo.Cliente values (3,'Antonio','2236-3333')
go
-- Update
update dbo.Cliente set Telefone = '2211-1111'
where ClienteID = 1
go
-- Delete
delete dbo.Cliente where ClienteID = 3
go
Na figura abaixo temos o resultado da consulta ao log de operações gerado na tabela cdc.dbo_Cliente_CT:
Select * from cdc.dbo_Cliente_CT
As linhas identificadas no resultado acima representam:
Azul - INSERT (_$operation = 2)
Vermelho - UPDATE antes da alteração (_$operation = 3)
Vermelho - UPDATE após a alteração (_$operation = 4)
Verde - DELETE (_$operation = 1)
No próximo post vamos ver como utilizar este recurso no SSIS, até lá!
Landry.
Estou no final da elaboração da minha palestra no SQL Server 2008 Community Lauch aqui no Rio de Janeiro, faltam apenas dois assuntos CDC e Script... Neste post vou escrever sobre o Change Data Capture (CDC).
O CDC tem como objetivo registrar todas as alterações que ocorrem em uma tabela facilitando o processo de exportação. Primeiro vamos aprender a habilitar e configurar esta funcionalidade, depois como fazer uso deste log no SSIS.
Habilitando e Configurando o CDC
Vamos criar um banco de dados para utilizar neste post como exemplo, execute o script abaixo:
USE master
go
IF EXISTS (SELECT name FROM sys.databases WHERE name = 'TesteCDC')
DROP DATABASE TesteCDC
go
CREATE DATABASE TesteCDC
go
Para habilitar o CDC no banco criado acima utilizamos a Stored Procedure sp_cdc_enable_db:
USE TesteDCD
go
EXEC sys.sp_cdc_enable_db
Podemos verificar se um banco de dados está habilitado para o CDC com a query abaixo:
SELECT [name] as Banco, is_cdc_enabled FROM sys.databases
O SQL Server 2008 cria alguns objetos quando um banco é habilitado para o CDC:
- Usuário: cdc
- Schema: cdc
- Tabelas de Sistema: capture_columns, change_tables, ddl_history, índex_columns, lsn_time_mapping e dbo.systranschemas.
Agora vamos criar uma tabela para ver o CDC em ação, execute o script abaixo:
USE TesteCDC
go
CREATE TABLE Cliente (
ClienteID int not null primary key,
Nome varchar(30) not null,
Telefone varchar(30) null)
go
Para habilitar o CDC na tabela criada acima basta utilizar a Stored Procedure sp_cdc_enable_table, veja o exemplo abaixo:
EXEC sp_cdc_enable_table 'dbo', 'Cliente',
@role_name = NULL, @supports_net_changes =1
A Stored Procedure pc_cdc_enable_table cria alguns objetos no SQL Server 2008: uma tabela de sistema (cdc.dbo_Cliente_CT) e dois Jobs do SQL Server Agent (cdc.TesteCDC_capture, cdc.TesteCDC_cleanup).
Para obter a lista de tabelas habilitadas para CDC execute a consulta abaixo:
Select [name], is_tracked_by_cdc from sys.tables
Agora vamos testar incluindo, excluindo e alterando linhas na tabela Cliente:
-- Insert
insert dbo.Cliente values (1,'Jose','2236-1111')
insert dbo.Cliente values (2,'Maria','2236-2222')
insert dbo.Cliente values (3,'Antonio','2236-3333')
go
-- Update
update dbo.Cliente set Telefone = '2211-1111'
where ClienteID = 1
go
-- Delete
delete dbo.Cliente where ClienteID = 3
go
Na figura abaixo temos o resultado da consulta ao log de operações gerado na tabela cdc.dbo_Cliente_CT:
Select * from cdc.dbo_Cliente_CT
As linhas identificadas no resultado acima representam:
Azul - INSERT (_$operation = 2)
Vermelho - UPDATE antes da alteração (_$operation = 3)
Vermelho - UPDATE após a alteração (_$operation = 4)
Verde - DELETE (_$operation = 1)
No próximo post vamos ver como utilizar este recurso no SSIS, até lá!
Landry.
segunda-feira, 3 de novembro de 2008
SQL Server 2008 SSIS – Data Profile
O SQL Server 2008 trás uma nova tarefa no Control Flow para analisar os dados durante a carga. O Data Profile pode fazer até 8 análises nos dados gerando um relatório em XML, que pode ser visualizado em um novo aplicativo chamado Data Profile Viewer.
As 8 análises disponibilizadas pelo Data Profile podem ser divididas em dois grupos: analise do relacionamento entre colunas e analise individua de colunas.
Análise do relacionamento entre colunas:
Candidate Key – analisa se uma ou mais colunas podem ser utilizadas como chave, ou analisa se existem valores duplicados onde se espera unicidade.
Functional Dependency – analisa a dependência funcional entre colunas, por exemplo ocorrência de cidades em estados.
Value Inclusion – verifica relacionamento tipo Foreign Key entra colunas de tabelas diferentes, exemplo de ocorrência de código de cliente nas tabelas de vendas e cliente.
Análise individual das colunas:
Column Length Distribution – relação dos tamanhos distintos de string em uma coluna e o percentual de linhas de cada tamanho. Útil para identificar possíveis erros nos dados.
Column Null Ratio – retorna o percentual de ocorrências de NULL em uma coluna.
Column Pattern – retorna um conjunto de expressões regulares identificadas e o percentual de linhas de cada ocorrência.
Column Statistics – só aplicável a colunas numéricas, mostra: Min, Max, Média e Desvio Padrão.
Column Value Distribution – retorna todos os valores distintos de uma coluna e o percentual de linhas de cada ocorrência.
Tipos de dados indicados:
Você poderá combinar várias análises em uma única tarefa Data Profile, veja na imagem abaixo:
O arquivo XML gerado após a análise poderá ser visualizado com o utilitário Data Profile Viewer localizado no grupo de programas do SQL Server 2008 no submenu Integration Services. O exemplo abaixo mostra uma análise de distribuição de valores (Column Value Distribution Profile):
Até o próximo Post,
Landry.
As 8 análises disponibilizadas pelo Data Profile podem ser divididas em dois grupos: analise do relacionamento entre colunas e analise individua de colunas.
Análise do relacionamento entre colunas:
Candidate Key – analisa se uma ou mais colunas podem ser utilizadas como chave, ou analisa se existem valores duplicados onde se espera unicidade.
Functional Dependency – analisa a dependência funcional entre colunas, por exemplo ocorrência de cidades em estados.
Value Inclusion – verifica relacionamento tipo Foreign Key entra colunas de tabelas diferentes, exemplo de ocorrência de código de cliente nas tabelas de vendas e cliente.
Análise individual das colunas:
Column Length Distribution – relação dos tamanhos distintos de string em uma coluna e o percentual de linhas de cada tamanho. Útil para identificar possíveis erros nos dados.
Column Null Ratio – retorna o percentual de ocorrências de NULL em uma coluna.
Column Pattern – retorna um conjunto de expressões regulares identificadas e o percentual de linhas de cada ocorrência.
Column Statistics – só aplicável a colunas numéricas, mostra: Min, Max, Média e Desvio Padrão.
Column Value Distribution – retorna todos os valores distintos de uma coluna e o percentual de linhas de cada ocorrência.
Tipos de dados indicados:
Você poderá combinar várias análises em uma única tarefa Data Profile, veja na imagem abaixo:
O arquivo XML gerado após a análise poderá ser visualizado com o utilitário Data Profile Viewer localizado no grupo de programas do SQL Server 2008 no submenu Integration Services. O exemplo abaixo mostra uma análise de distribuição de valores (Column Value Distribution Profile):
Até o próximo Post,
Landry.
Assinar:
Postagens (Atom)