SQL Server 2008 SSIS - Pipeline Scalability

No post Escolha das Transformacoes SSIS (http://sqlserver-brasil.blogspot.com/2008/06/classificando-as-transformaes-no-data.html) escrevi sobre a classificação das transformações em 3 grandes grupos: com bloqueio, sem bloqueio e semi bloqueio.

As transformações sem bloqueio consecutivas em um Data Flow formam uma única árvore de execução, utilizando o mesmo worker thread. Toda vez que em um Data Flow temos uma transformação com bloqueio ou semi bloqueio, é iniciada uma nova árvore de execução, utilizando um novo worker thread. Veja no Data Flow abaixo a divisão em árvores de execução:


Esta divisão em mais de um worker thread proporciona a execução em paralelo em máquinas com mais de um processador, melhorando o desempenho da carga.

Uma técnica utilizada no SQL Server 2005 era acrescentar ao Data Flow uma transformação semi bloqueio (exemplo UNION All) para forçar o uso de um novo worker thread, e melhorar o paralelismo.

No SQL Server 2008 o paralelismo é automático, não sendo mais necessário utilizar do artifício acima para melhorar o desempenho. Para controlar o uso de worker threads basta ajustar a propriedade do Data Flow EngineThreads,

Por outro lado isto nos força a rever os pacotes desenvolvidos no SQL Server 2005 utilizando o artifício descrito acima!

Até o próximo post,
Landry.

Novidades do SQL Server 2008 para Business Intelligence

Estou iniciando uma série de posts com as novidades do SQL Server 2008 para Business Intelligence, pois estarei participando do evento SQL Launch no Rio de Janeiro com duas palestras sobre SSIS e SSAS. Segue abaixo a relação das novas funcionalidades e melhorias que farão parte dos próximos posts:

SSIS
SSIS pipeline.
SSIS persistent lookups.
SSIS data profiling.
CDC (Change Data Capture).

SSRS
Integração com SharePoint nativo.
Novo Data Region Tablix.
Novos formatos de Rendering: rich-text, Word.
Melhorias no formato de Rendering Excel.
Entrega dos relatórios diretamente para o SharePoint.

SSAS
Personalization Extensions .
Best Practice Alerts.
Dynamic Named Sets.
Enhanced Dimension Design.
Enhanced Aggregate Design.
Novos algoritmos para Microsoft Time Series.
Subspace Computation.
MOLAP Write Back.
Scale-Out.
Backup.

Até o próximo post,
Landry

Resource Database e Tabelas de Sistema

Olá... depois de quase um mês sem fazer um post voltei... estava estudando para as provas Beta de SQL Server 2008. Estou aproveitando um intervalo de duas semanas entre as provas para escrever este post, espero que gostem!

No SQL Server 2000 era comum o acesso direto as tabelas de sistema para obter informações de metadata e até em situações extremas atualizá-las (SP_configure ‘allow updates’,1). No SQL Server 2005 todas as tabelas de sistema estão escondidas, todo o acesso ao metadata fica restrito as Stored Procedures, funções e Visões de sistema! Veja no link abaixo o diagrama das visões de sistema que podemos utilizar para acessar metadata:

SQL Server 2005 System Views Map
http://www.microsoft.com/downloads/details.aspx?FamilyID=2ec9e842-40be-4321-9b56-92fd3860fb32&displaylang=en

Para acessar as tabelas de sistema temos que abrir uma conexão DAC (Dedicated Administrator Connection), colocando ADMIN: na frente do nome da instância na conexão da janela de query no SSMS. A relação das tabelas de sistema pode ser encontrada no BOL pesquisando por “System Base Tables”.

O SQL Server 2005 introduziu um novo Banco de Dados de Sistema chamado mssqlsystemresource. Este banco contém os objetos de sistema que aparecem no schema SYS nos demais bancos de dados: Views, Stored Procedures e Funções de sistema (não contém tabelas de sistema).

O Banco de Dados mssqlsystemresource fica escondido, não aparece na lista de bancos no SSMS, porém se você navegar até a pasta abaixo verá os dois arquivos deste banco: mssqlsystemresource.mdf e mssqlsystemresource.ldf.
\Microsoft SQL Server\MSSQL.1\MSSQL\Data

Temos algumas opções para os curiosos...

1) Iniciar a Instância em modo monousuário:
- Basta parar o serviço e iniciar utilizando o comando abaixo no prompt de comando:
cd c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn
sqlservr.exe –c -m

Depois é só conectar no Banco e explorar seu conteúdo, veja na figura abaixo:

2) Utilizar os arquivos de dados e log do banco mssqlsystemresource para fazer Attach com outro nome de Banco:
- Copiar os arquivos de dados e log para outra pasta (não precisa parar o serviço).
- Executar o script abaixo (não se esqueça de alterar o caminho dos arquivos de dados e log!):

USE master
go
CREATE DATABASE mssqlsystemresource_Teste ON
( FILENAME = 'C:\mssqlsystemresource.mdf' ),
( FILENAME = 'C:\mssqlsystemresource.ldf' )
FOR ATTACH
go
if not exists (select name from master.sys.databases sd where name = N'mssqlsystemresource_Teste' AND
SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() )
EXEC mssqlsystemresource_Teste.dbo.sp_changedbowner @loginame=N'sa', @map=false
go

Mssqlsystemresource facilita a execução dos Service Packs
Em versões anteriores do SQL Server, os Service Packs tinham que executar vários scripts que alteravam os objetos de sistema em todos os banncos de dados. Dependendo do tamanho do servidor, estes scripts demoravam para rodar, aumentando o período de indisponibilidade do servidor. Centralizando o metadata no mssqlsystemresource, fica muito mais fácil para um Service Pack realizar as alterações necessárias, basta substituir os arquivos de dados e log do banco, reduzindo o tempo total de execução!

Backup do Banco de Dados Mssqlsystemresource
Como todos os objetos de sistema ficam agora no banco de dados mssqlsystemresource, ocorrendo qualquer problema nos seus arquivos o serviço do SQL Server não irá reiniciar. Veja a mensagem de erro no Event Viewer abaixo:

Sendo assim, é indicado o Backup do Banco de Dados mssqlsystemresource, juntamente com master e msdb. O problema é que o SQL Server não permite Backup no banco mssqlsystemresource, contudo é o único banco que permite a cópia dos seus arquivos de dados e log com o serviço ativo. O Restore também pode ser feito com uma cópia, porém tem que parar o serviço do SQL Server.

Existem duas instruções que retornam informações do banco mssqlsystemresource o Build e data de alteração respectivamente:
SELECT SERVERPROPERTY('ResourceVersion')
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');

Até o próximo post,
Landry.

T-SQL: CROSS APPLY

Prosseguindo na série com as instruções novas no SQL Server 2005, vou mostrar neste post o CROSS APPLY. Em uma operação de JOIN (INNER, OUTER ou CROSS) o T-SQL não aceita subquery correlacionada em tabela derivada, veja os exemplos abaixo:

select A.col, b.col
from A
cross join (select B.col from B where B.val=A.val) b
-- Erro, A.val na subquery está fora de escopo!

A instrução acima deverá ser reescrita em uma das forma abaixo, utilizando subquery como tabela derivada:

select A.col, b.col
from A
cross join (select B.col,B.val from B) b
where B.val=A.val
OU
select A.col, b.col
from A join (select B.col,B.val from B) b
on B.val=A.val

O problema persiste quando tentamos utilizar uma Função Definida pelo Usuário (UDF), porque A.val como parâmetro da função está fora de escopo!

select A.*, B.col from A
cross join dbo.UDF(A.val) B

O novo CROSS APPLY do SQL Server 2005 resolve este problema, aceitando a instrução abaixo:

select A.*, B.col from A
CROSS APPLY dbo.UDF(A.val) B

Vamos criar duas tabelas e uma função para trabalharmos em um exemplo real:

use TempDB
go
create table Cliente(ClientePK int NULL,Nome varchar(30) NULL)
create table Vendas(VendasPK int NULL,ClienteFK int NULL,Valor decimal(9,2) NULL)
go
insert Cliente values (1,'Jose')
insert Cliente values (2,'Maria')
insert Cliente values (3,'Ana')

insert Vendas values (1,1,20.00)
insert Vendas values (2,1,40.00)
insert Vendas values (3,2,15.00)
insert Vendas values (3,2,36.00)
go

create function fnu_MaiorVenda(@ClienteID int)
returns table as return(
select ClienteFK,Max(Valor) MaiorValor from Vendas
where ClienteFK = @ClienteID group by ClienteFK)
go

A função fnu_MaiorVenda faz um GROUP BY na tabela Vendas, retornando o valor da maior venda de um cliente identificado pelo parâmetro de entrada @ClienteID. Ao tentar executar um JOIN entre a tabela Cliente e a função fnu_MaiorVenda, recebemos o erro 4104:

select c.Nome, v.MaiorValor
from Cliente c JOIN fnu_MaiorVenda(c.ClientePK) v
on c.ClientePK = v.ClienteFK
-- Msg 4104, Level 16, State 1, Line 1
-- The multi-part identifier "c.ClientePK" could not be bound.

O CROSS APPLY resolve o problema...

select c.Nome, v.MaiorValor
from Cliente c CROSS APPLY fnu_MaiorVenda(c.ClientePK) v

Até o próximo post.
Landry.

Trigger de Login

No último curso de Design de Segurança do SQL Server 2005 (curso 2790), um aluno com experiência em Oracle, perguntou se o SQL Server tinha Trigger de Login. Respondi que não tinha, porém poderíamos simular esta funcionalidade com Event Notification... o problema é que ele bloqueava o login em algumas situações, dependendo do usuário e da aplicação utilizada no login, o que seria impossível já que Event Notification é assíncrono! Uma rápida consulta na Internet mostrou uma nova funcionalidade incluída no Service Pack 2 do SQL Server 2005: Trigger de Login!

A Trigger de Login foi incluída para atender a uma certificação de segurança chamada Common Criteria (CC), resultante da união de três outras certificações: ITSEC (padrão Europeu), CTCPEC (padrão Canadense) e TCSEC (Departamento de Defesa Norte Americano). A certificação CC é reconhecida por mais de 24 países e possui 7 níveis para produtos de informática, de EAL1 a EAL7.

O SQL Server até SP1 foi classificado em EAL1, já com SP2 recebeu a classificação EAL4+ (o + indica atendimento parcial, já que a Microsoft irá melhorar o suporte em atualizações futuras). A Trigger de Login foi criada para atender os seguintes requisitos que constam no EAL4+:
Restringir a quantidade máxima de conexões concorrentes de um mesmo usuário.
Definir uma quantidade máxima default de conexões por usuário.
Negar a conexão com base no usuário, grupo, dia da semana, etc.

Dentro de uma Trigger de Login você pode utilizar a função EVENTDATA() para obter informações da conexão que originou o disparo da trigger, veja o documento XML gerado abaixo:



Outras fontes de informações que podem ser utilizadas dentro da trigger:
- sys.dm_exec_sessions – View dinâmica com informações das sessões abertas.
- sys.dm_exec_connections – View dinâmica com informações das conexões abertas.
- app_name() – nome da aplicação utilizada para realizar a conexão corrente
- CURRENT_USER – usuário de banco de dados da conexão corrente

Exemplo:
-- Retorna Informações da conexão corrente
select s.*,c.*
from sys.dm_exec_sessions s
join sys.dm_exec_connections c
on s.session_id = c.session_id
where s.session_id = @@spid

O exemplo abaixo cria uma Trigger de Login que restringe o acesso ao servidor a partir do Management Studio apenas para o Administrador, além de registrar em uma tabela de auditoria os logins com sucesso.

-- Tabela de Auditoria na MSDB
create table msdb.dbo.AutitLogin (
idPK int not null identity,
Data datetime null,
ProcID int null,
LoginID varchar(128) null,
NomeHost varchar(128) null,
App varchar(128) null,
SchemaAutenticacao varchar(128) null,
Protocolo varchar(128) null,
IPcliente varchar(30) null,
IPservidor varchar(30) null,
xmlConectInfo xml)
go

-- Trigger de Login
create trigger AuditLogin on all server
for logon
as
IF CURRENT_USER <> 'dbo' and
app_name() like 'Microsoft SQL Server Management Studio%'
rollback
else
-- Login sucesso
insert msdb.dbo.AutitLogin
select getdate(),@@spid,s.login_name,s.[host_name],
s.program_name,c.auth_scheme,c.net_transport,
c.client_net_address,c.local_net_address,eventdata()
from sys.dm_exec_sessions s join sys.dm_exec_connections c
on s.session_id = c.session_id
where s.session_id = @@spid
go

Se um usuário comum tentar abrir uma conexão no SQL Server a partir do Managemente Studio irá receber a mensagem de erro abaixo:



A mensagem de erro poderia ser melhor, sem expor o motivo da falha da conexão: “trigger execution”! Você pode votar no site Microsoft Connect para alterar a mensagem de erro no link abaixo:
https://connect.microsoft.com/SQLServer/feedback/Vote.aspx?FeedbackID=237008

Gostaria de agradecer a Diego Cerqueira (o aluno especializado em Oracle) por ter enriquecido a aula com sues questionamentos, dando origem a este post.

Até o próximo post,
Landry.

T-SQL: OUTPUT

Olá... neste post veremos outra novidade no Transact-SQL do SQL Server 2005, a cláusula OUTPUT. Esta cláusula pode ser acrescentada a uma instrução de atualização para retornar os dados que acabaram de ser atualizados. Vamos criar uma tabela no Banco de Dados TEMPDB para utilizar nos exemplos:

use tempdb
go
create table TesteOutput (ColPK int IDENTITY NOT NULL, Nome varchar(50), Tel varchar(20))

Repare que a tabela TesteOutput possui a propriedade IDENTITY (auto numeração) na primeira coluna (ColPK)! Uma necessidade comum é retornar o valor atribuído pelo SQL Server a coluna com propriedade IDENTITY durante um INSERT, sendo necessário executar um SELECT após o INSERT.

insert TesteOutput values ('Ana Lucia','1111-1111')
select SCOPE_IDENTITY()

Valor_ColPK
-------------------
1

O SQL Server 2005 pode simplificar a operação acima em um comando apenas, veja:

insert TesteOutput OUTPUT inserted.ColPK
values ('Maria Clara','2222-2222')

ColPK
-----------
2

Se você quiser pode até retornar todas as colunas da tabela:

insert TesteOutput OUTPUT inserted.*
values ('Ana Paula','3333-3333')

Veja no DELETE:

delete TesteOutput OUTPUT deleted.ColPK,deleted.Nome
where ColPK = 1

Agora um exemplo mais interessante.... você precisa manter uma tabela de auditoria registrando o usuário, operação e a data que ocorreu a atualização! No SQL Server 2000 existem duas opções: TRIGGER ou executar duas operações, veja no SQL 2005:

create table TesteHist (
Nome varchar(50),
Operacao varchar(10),
Data datetime,
Usuario varchar(256))
go

delete TesteOutput OUTPUT
deleted.Nome,'DETELE',getdate(),suser_sname()
into TesteHist
where ColPK = 2
go

select * from TesteHist



Até o próximo post.
Landry.

T-SQL: INTERSECT e EXCEPT

Quarta... terminei a consultoria mais cedo (as 16h), tenho duas horas até começar a próxima turma do curso Microsoft 2793 (Reporting Service) as 18h... Tempo suficiente para escrever mais um post e fazer um pequeno lanche (a turma da noite detona o coffee break)!

Uma situação muito comum para quem trabalha com banco de dados é comparar o conteúdo de duas tabelas, retornando as linhas em comum ou as linhas que existem em uma tabela e não existem na outra (minus da álgebra relacional). Até o SQL Server 2000 a solução para estes dois problemas era utilizar JOIN ou SUBQUERY, veja no exemplo abaixo:

-- Criando duas tabelas
create table Teste1 (Coluna1 varchar(20))
create table Teste2 (Coluna1 varchar(20))
go

insert Teste1 values('Rio de Janeiro')
insert Teste1 values('Sao Paulo')
insert Teste1 values('Salvador')
insert Teste1 values('Sao Luiz')

insert Teste2 values('Rio de Janeiro')
insert Teste2 values('Sao Paulo')
insert Teste2 values('Salvador')
insert Teste2 values('Brasilia')
go

-- Obtendo linhas em comum
select Coluna1 from Teste1 where exists
(select * from Teste2 where Teste2.Coluna1 = Teste1.Coluna1)

-- Obtendo as linhas que existem na tabela Teste1 e
-- não existem na tabela Teste2
select Coluna1 from Teste1 where not exists
(select * from Teste2 where Teste2.Coluna1 = Teste1.Coluna1)

No SQL Server 2005 ficou muito mais fácil utilizando os operadores INTERSECT e EXCEPT, veja abaixo:

-- Obtendo linhas em comum
select Coluna1 from Teste1
INTERSECT
select Coluna1 from Teste2

-- Obtendo as linhas que existem na tabela Teste1 e
-- não existem na tabela Teste2
select Coluna1 from Teste1
EXCEPT
select Coluna1 from Teste2

Até o próximo post.
Landry.