Change Data Capture – CDC

Olá pessoas!

Uma feature muito interessante para quem deseja fazer “log” de dados históricos do banco de dados, de forma a não prejudicar o desempenho do banco ou ter que criar um monte de triggers e tabelas de log, é o Change Data Capture, ou simplesmente CDC. Esta feature presente no SQL Server 2008 nas edições Developer e Enterprise.

O CDC permite de forma simplificada monitorar e manter histórico das alterações de determinadas tabelas no banco de dados. Isso quer dizer que, não será necessário desenvolver toda uma estratégia complexas com triggers para salvar a execução de INSERT, DELETE e UPDATE, pois com esta feature permite isso de forma descomplicada e performática.

Ok, para que preciso manter histórico das alterar das minhas tabelas no banco e dados? A motivação mais comum é a possibilidade de utilizar estes históricos para sincronizar informações de aplicações OFF-LINE ou soluções de Data Warehouse, que receberam dados de um determinado período e em algum momento terá que ser atualizado com os novos dados.

Outra motivação comum é manter histórico das alterações de determinadas tabelas para uma auditoria periódica, como auditorias de alterações salariais e movimentações logísticas e financeiras.

O primeiro passo para configurar o CDC, é habilitar esta feature em nível de banco de dados, a partir da stored procedure sys.sp_cdc_enable_db:

Em seguida, utilizar a stored procedure sys.sp_cdc_enable_table para configurar a tabela a ser monitorada:

Diferente das triggers, que são disparadas na mesma transação que é executado INSERTs, DELETEs e/ou UPDATEs (e que até mesmo por algum erro de programação podem prejudicar toda a transação), o CDC utiliza um Job para capturar os registros alterados, excluídos ou inseridos diretamente de arquivos de logs do banco de dados a cada 5 segundos:

Obs.: O recovery model do banco de dados não intefere no CDC.

E um segundo Job executado todos os dias às 2h00 AM, elimina os registros armazenados a mais de 3 dias dos repositórios de registros históricos do CDC, considerando que estes registros históricos ficam armazenados no próprio banco de dados.

Após a captura dos dados pelo CDC, a forma mais simples de recuperar estes dados históricos é por meio da tabela que armazena estes dados no CDC, que normalmente segue a seguinte nomenclatura:

cdc.(schema)_(tabela)_CT

Exemplo:

Existem outras formas mais otimizadas para recuperar estes dados, mas isso será tratado em um próximo artigo.

Para aqueles que estão familiarizados com Transactional Replication, podem estar se questionando pelo fato desta forma de replicação também fazer leitura diretamente do arquivo de logs do banco de dados, geraria alguma forma concorrência com o CDC. Nesta situação, podem ficar tranquilos, pois o Transactional Replication e o CDC são compatíveis, e quando o Log Reader do Transactional Replication estiver varrendo o log do banco de dados para replicar as alterações, o CDC esperará este processo ser concluído para ser executado.

E no caso de Mirroring, algum problema em relação ao CDC? Não, nenhum, pois até mesmo os dados armazenados pelo CDC serão espelhados para o banco de dados espelho.

Mais informações:
http://msdn.microsoft.com/en-us/library/bb522489

Spatial Data: STGeomFromText

Boa noite pessoas!

Prosseguindo os artigos sobre dados espaciais, vou demonstrar um pouco a criação de pontos, linhas e polígonos por meio de texto. A partir deste momento, os códigos vão parecer estranhos para quem nunca trabalhou com estes dados geométricos em outro banco de dados.

Antes que critiquem a forma do SQL Server trabalhar com estes dados, tenho que informar ele segue as especificações do OGC (Open Geospatial Consortium http://en.wikipedia.org/wiki/Simple_Features), permitindo compatibilidade com outros SGDB que também seguem (IBM DB2, IBM Informix, MySQL, Oracle e PostgreSQL).

A geração de dados espaciais por meio de texto é a forma mais simples para geração destes dados (também existe a possibilidade gerar estes dados por meio de XML e tipos binários).

O primeiro exemplo é a criação de um ponto por meio de texto:

SELECT geometry::STPointFromText('POINT(1 1)', 0)

O resultado será o mesmo da utilização do método “sem texto”:

SELECT geometry::Point(1, 1, 0)

Para a geração de uma linha, é utilizado método STLineFromText e o texto possui a declaração do tipo a ser criado com a ordem dos pontos correspondentes a linhas. Exemplo:

SELECT geometry::STLineFromText('LINESTRING(0 0, 1 1)', 0)

Uma vantagem desta forma de geração é a possibilidade de criar uma seqüência de linhas, sem a necessidade de concatenar com outras linhas, somente adicionando mais pontos. Exemplo:

SELECT geometry::STLineFromText('LINESTRING(1 1, 2 2, 2 3, 1 0)', 0)

E o fato de linhas, pontos e polígonos serem do mesmo tipo, permite o uso dos mesmos métodos, exemplo:

SELECT geometry::STLineFromText('LINESTRING(1 1, 2 2, 2 3, 1 0)', 0).STBuffer(0.1)

Para a criação de polígonos é necessário especificar pelo menos três pontos e por fim novamente o primeiro. Exemplo:

SELECT geometry::STPolyFromText('POLYGON((1 1, 2 2, 2 3, 1 1))', 0)

Um forma genérica de criar estes tipos é utilizando o método “STGeomFromText”, lembrando que métodos genéricos sempre geram um custo para identificar o que será executado.

Exemplo do uso do método “STGeomFromText”:

SELECT geometry::STGeomFromText('POLYGON((1 1, 3 2, 2 2, 1 1))', 0)

Para terminar, segue um exemplo utilizando todos os métodos de criação de dados geométricos que foram demonstrados até agora:

SELECT geometry::STGeomFromText('POLYGON((1 1, 5 1, 3 4.7, 1 1))', 0)
UNION ALL
SELECT geometry::STPolyFromText('POLYGON((2.5 1, 3.5 1, 3.5 0, 2.5 0, 2.5 1))', 0)
UNION ALL
SELECT geometry::STGeomFromText('LINESTRING(2.5 5, 3.5 5, 3 5.8, 2.5 5)', 0).STBuffer(0.02)
UNION ALL
SELECT geometry::STLineFromText('LINESTRING(2.5 5.5, 3.5 5.5, 3 4.7, 2.5 5.5)', 0).STBuffer(0.02)
UNION ALL
SELECT geometry::STGeomFromText('POINT(1 1)', 0).STBuffer(0.25)
UNION ALL
SELECT geometry::STPointFromText('POINT(2 1)', 0).STBuffer(0.25)
UNION ALL
SELECT geometry::Point(3, 1, 0).STBuffer(0.25)
UNION ALL
SELECT geometry::Point(4, 1, 0).STBuffer(0.25)
UNION ALL
SELECT geometry::Point(5, 1, 0).STBuffer(0.25)

Acredito que a partir destes exemplos, seja possível ter uma boa noção de como criar tipos geométricos. Lembrando que eles não nasceram para ficar “desenhando” no SQL Server. Para o próximo artigo estou validando a possibilidade de utilizar alguns algoritmos mais complexos para demonstrar os vários métodos que estes tipos possuem, então até o próximo artigo!

Se eu não voltar a escrever ainda neste ano: Chanukah Sameach, Feliz Natal, Bonne Noël, Feliz Ano Novo!

Spatial Data: Introdução aos tipos geométricos

Boa tarde pessoas!

Algumas funcionalidades para tratamentos de “dados espaciais” foram introduzidos no SQL Server 2008 (e no 2005 sem muito suporte, por meio de um assembly CLR opcional). Mas o que são estes tipos de dados? Bem, eles são tipos CLR nativos da engine de banco de dados do SQL Server 2008. O suporte a estes tipos de dados possibilita inserir dentro da engine tratamento a dados geométricos e geográficos. Basicamente, agora você pode armazenar pontos, linhas e polígonos como um único tipo de dado dentro do banco de dados, indexar, executar cálculos dos simples aos mais complexos.

Bom, creio que eu já tenha vendido a idéia, então vamos ao nosso primeiro exemplo:

SELECT geometry::Point(1,1,0)

E temos como resultado:

0x00000000010C000000000000F03F000000000000F03F

Interessante, não é? Bem antes que me chamem de louco, no resultado desta consulta é possível verificar que existirá uma aba “Spatial results”:

Nesta aba, você poderá acompanhar os resultados de suas consultas com tipos geométricos.

Geometry presente na sintaxe é a “classe” que define os tipos geométricos e Point é um “método estático” que permite criar por meio de coordenadas a posição do ponto, como no exemplo “1, 1”. Já “0” é um “número mágico” (como disse o Javier Loria na palestra de Spatial Data no TechEd de 2009), pois por enquanto não há muita utilidade falarmos sobre ele.

Visto que um ponto não possui uma ‘área visível’, vamos utilizar o método STBuffer para determinar uma área, exemplo:

SELECT geometry::Point(1,1,0).STBuffer(1)

Resultado:

Uma forma de exibir vários resultados de tipos geométricos juntos é utilizando UNION ALL, exemplo:

SELECT geometry::Point(10,10,0).STBuffer(1)
UNION ALL
SELECT geometry::Point(20,20,0).STBuffer(1)
UNION ALL
SELECT geometry::Point(25,10,0).STBuffer(1)

Resultado:

Para trabalhar com variáveis ou criar colunas do tipo geométrico, você pode declarar simplesmente como “geometry”, exemplo:

DECLARE @Ponto geometry
SET @Ponto = geometry::Point(10,10,0)
SELECT @Ponto.STBuffer(1)

Uma dica importante: STBuffer cria poligono sobre o tipo geométrico, criando um dado bem maior que o original (como é possível ver na aba Results dos exemplos anteriores), desta forma, se você deseja salvar um ponto, linha ou polígono no banco de dados, não salve-o com o método STBuffer, ou você estará salvando um dado muito maior e mais complexo do que é dado realmente é. Limite o uso do método STBuffer para cálculos e exibição de consultas.

Caso você possua alguma identificação para seus dados geométricos, por exemplo, um nome, você pode visualizar este nome no resultado de suas consultas:

Exemplo:

SELECT geometry::Point(10,10,0).STBuffer(1), 'Meu Ponto' AS Titulo

Por enquanto é uma introdução, nos próximos artigos estarei trabalhando melhor outras funcionalidades presentes no SQL Server para estes tipos de dados.

Caso você deseje conhecer um pouco mais:
http://msdn.microsoft.com/en-us/library/bb933876.aspx

Trabalhando com datas – Extraindo informações

Boa noite pessoal!

Continuando o tópico “trabalhando com datas”, continuarei a relacionar algumas dificuldades com extrair informações de tipos DATETIME.

Função responsável por obter a data e hora atual:

GETDATE()
Ou
CURRENT_TIMESTAMP

Outras funções utilizadas para obter de pequenos fragmentos de datas como ano, hora, dia:

Função	Exemplo
DAY	SELECT DAY(GETDATE())
MONTH	SELECT MONTH(GETDATE())
YEAR	SELECT YEAR(GETDATE())

Obs.: As três funções acima não funcionam em SQL CE.

Para obter informações sobre dia de semana, hora, minuto, segundo e outras, não é possível pelas três funções acima, sendo necessário utilizar a função DATEPART, exemplo:

SELECT DATEPART(YEAR, GETDATE())
SELECT DATEPART(MONTH, GETDATE())
SELECT DATEPART(WEEKDAY, GETDATE())
SELECT DATEPART(DAY, GETDATE())
SELECT DATEPART(HOUR, GETDATE())
SELECT DATEPART(MINUTE, GETDATE())
SELECT DATEPART(SECOND, GETDATE())

Na biblioteca do MSDN, também existe uma relação de todas as dateparts possíveis:
http://msdn.microsoft.com/en-us/library/ms174420.aspx

Também existe a função DATENAME, que retorna, quando possível, o nome do mês ou do dia da semana, exemplo:

SELECT DATENAME(MONTH, GETDATE())
SELECT DATENAME(WEEKDAY, GETDATE())

Pequeno detalhe, se o servidor estiver no idioma inglês será necessário utilizar a seguinte instrução:

SET LANGUAGE 'Brazilian'
Ou
SET LANGUAGE 'Portuguese'

Obs.: No SQL Server, o idioma Brasileiro existe e funciona melhor que o português, exemplo:

SET LANGUAGE 'Portuguese'
SELECT DATENAME(MONTH, GETDATE()), DATENAME(WEEKDAY, GETDATE())
Ou
SET LANGUAGE 'Brazilian'
SELECT DATENAME(MONTH, GETDATE()), DATENAME(WEEKDAY, GETDATE())

A relação dos idiomas possíveis pode ser conseguida pela view:

SELECT * FROM syslanguages

Somente para completar o post anterior, abaixo as funções para pegar dia/mês/ano e hora:minuto:segundo:

SELECT CONVERT(VARCHAR(8),GETDATE(),103)
SELECT CONVERT(VARCHAR(8),GETDATE(),108)

E o diferencial de trabalhar com SQL Server 2008, onde existem os tipos DATE e TIME, sendo possível obter estas informações até mesmo por um simples cast: 

SELECT CAST(GETDATE() AS DATE)
SELECT CAST(GETDATE() AS TIME)

(at. http://blog.sqlauthority.com/2009/08/06/sql-server-get-time-in-hourminute-format-from-a-datetime-get-date-part-only-from-datetime/ )

Pessoal, então finalizando este post, agradeço as visitas nestes quatro primeiros meses de blog que têm me animado bastante a continuar escrevendo, principalmente os emails e comentários que tenho recebido.

Até o próximo post!

Innovation Day Curitiba (Cobertura do evento)

Como não fui, eu não posso falar nada do evento, mas segue os links com a cobertura do evento:

Cobertura By DevBR:
http://devbr.blogspot.com/2009/07/innovation-day.html

Cobertura By Zavaschi (Ensinando como eliminar o Acre por SQL):
http://thiagozavaschi.spaces.live.com/blog/cns!8DE5A8EFC1819ECA!335.entry

Cobertura By Ordine (Vídeos):
http://eordine.blogspot.com/2009/07/cobertura-do-evento-microsoft.html

 

Zavaschi e Márcio Gomes no Innovation Day Curitiba

O Márcio Gomes (MCT) do DevBR disse que irá disponibilizar alguns vídeos do evento, mas ainda sem data para publicação.

Se alguem fez alguma cobertura do evento e não vi, me avisem!

Valeu Cleibson por ajudar divulgar o evento!

Pessoal, até o próximo post!

Innovation Day Curitiba (04/07/2009)

Pessoal,

Evento (gratuito) muito bom neste sábado. Mas só pelo nome dos caras já dá para perceber que vai ser muito bom! Indico a palestra do Márcio Gomes e Thiago Zavaschi sobre “Virtual Earth + Silverlight + SQL Server 2008”, também a do Marcelo D’Ávila de Pauli sobre “Linguagens Dinâmicas”!

Abaixo o convite:

SQL Server 2008 como fonte de dados no Visio

No último post relacionado à modelagem com Visio, não entrei em detalhes sobre como eu criei uma fonte de dados a partir do SQL Server 2008. Então dedicarei este post para específicar este processo.

Um detalhe interessante é que para iniciar este processo, tive que é necessário realizar o download do SQL Server 2008 Management Studio Express ( http://www.microsoft.com/downloads/details.aspx?FamilyID=08e52ac2-1d62-45f6-9a4a-4b76a8564a2b&displaylang=en ) ou instalar qualquer versão do SQL Server 2008 ( http://www.microsoft.com/downloads/details.aspx?familyid=7522A683-4CB2-454E-B908-E805E9BD4E28&displaylang=en ), pois o driver utilizado nas versões 6.5 até 2005 não são compatíveis com a versão atual, mas isso é sinal de maior compatibilidade com as novas funcionalidades.