Archive

Posts Tagged ‘Virtual PASS BR’

T-SQL – Simulando o FN_SPLIT sem uso da função

Galera, boa tarde.

Para os meus amigos SQL Geeks, dedico esse post para Paula Patricia, uma super DBA SQL Server e uma grande amiga.

Hoje eu vim aqui falar de um assunto “inacabado” que apareceu em uma das turmas em que ministrava um treinamento sobre SQL Server. Falando sobre de como o SQL Server é otimizado para trabalhar com o que chamamos de “set-based” ao invés de processamento linha-a-linha.

Um aluno me disse: Thiago tem um cursor que executa um processamento “X” para um processo que processa algumas informações de estoque de forma hierárquica. A primeira sugestão que fiz foi: A alteração de um cursor para utilizar um While que lhe traria o mesmo resultado sem muita decodificação, porém, eu sabia que o resultado, poderia ser alcançado utilizando outro recurso foi que lhe pedi para que ele me trouxesse o código para analisarmos e mudar o While para uma instrução baseada em lote.

Depois de alguns minutos olhando o código cheguei à conclusão do que ele realmente precisava e de como poderíamos chegar ao resultado com uma CTE Recursiva (Para quem está lendo, irei levar em consideração que você conhece CTEs. No final desse post existem links para todos os assuntos tratados aqui).

Algum tempo de codificação por parte do aluno e… BAZINGA. A query estava retornando os valores, porem, a string que foi montada era utilizada como parâmetro de entrada para outra procedure. No que resultou em uma variável do tipo varchar(max)

Ex:

DECLARE @ListaParametrosEntrada varchar(max)= ‘18965,15239,12578,145236,7853,18965,15239,12578,145236,7853,18965,15239,12578,145236,7853,18965,15239,12578,145236,785318965,15239,12578,145236,7853,18965,15239,12578,145236,7853,18965,15239,12578,145236,785318965,15239,12578,145236,7853

Obs: Existem muitas outras soluções para resolver esse problema, para esse artigo apenas será abordado uma delas, mas, farei outros que abordam diversos resultados considerando performance.

Agora que começa a ficar interessante…

Imagine o cenário: Para cada Valor separado por vírgula dentro da variável @ListaParametrosEntrada deve ser desmembrado para que seja utilizado dentro de uma variável do tipo de dados inteiro em uma clausula WHERE com o operador de igualdade (=).

Ex:

where id_operacao_origem= @I

No código que encontrei, existia um While que verificava o seguinte: “enquanto houver valores separados por vírgula…quebra esse valor e coloque em uma variável,conforme demonstrado acima.

Então podemos avaliar que: Se eu tiver Um milhão de linhas, vou fazer isso Um milhão de vezes? A resposta é sim. Pode parecer estranho, mas, muitos programados têm uma forma de pensar serializada para resolver os problemas e essa é uma forma equivocada quando falamos de SGBD, pois, estamos nos referindo a um conjunto de dados (No fim do post tem um ótimo artigo do Fabiano Amorim sobre o assunto).

O código abaixo demonstra a lógica utilizada para o que foi visto até aqui:

DECLARE @ListaParametrosEntrada varchar(max)= ‘18965,15239,12578,145236,7853,18965,15239,12578,145236,7853,18965,15239,12578,145236,7853,18965,15239,12578,145236,785318965,15239,12578,145236,7853,18965,15239,12578,145236,7853,18965,15239,12578,145236,785318965,15239,12578,145236,7853’

DECLARE @I VARCHAR(MAX)

if right(@ListaParametrosEntrada, 1)<> ‘,’

begin

set @ListaParametrosEntrada= @ListaParametrosEntrada + ‘,’

end

while charindex(‘,’, @ListaParametrosEntrada)<> 0

BEGIN

set @I= ltrim(rtrim(substring(@ListaParametrosEntrada, 1 , charindex(‘,’, @ListaParametrosEntrada)- 1)))

set @ListaParametrosEntrada= ltrim(rtrim(right(@ListaParametrosEntrada,len(@ListaParametrosEntrada)- charindex(‘,’, @ListaParametrosEntrada))))

SELECT @I

END

Resultado:

Print2

Procurei na internet e achei ma função chamada fn_Split, que parece ser bem famosa quando estamos falando desse cenário. A função realmente atende, porém existem diversos problemas de performance relacionado a funções que não serão cobertos nesse post. Quem dizer da uma olhada de como pode ser feito com a função.

http://geekswithblogs.net/AngelEyes/archive/2007/04/12/111504.aspx

Para evitar esse processamento linha-a-linha que não é a melhor opção, o ideal seria que cada valor fosse um registro armazenado em uma coluna de uma tabela, para que possamos realizar um JOIN com a coluna id_operacao_origem.

O código abaixo atende a este requisito

DECLARE @T TABLE (Cod int NOT NULL)

DECLARE @ListaParametrosEntrada varchar(max), @Split char(1), @X xml

SELECT @ListaParametrosEntrada = ‘18965,15239,12578,145236,7853,18965,15239,12578,145236,7853,18965,15239,12578,145236,7853,18965,15239,12578,145236,7853965,15239,12578,145236,7853,18965,15239,12578,145236,7853,18965,15239,12578,145236,785318965,15239,12578,145236,7853’,

@Split= ‘,’

SELECT @X = CONVERT(xml,'<root><s>’+ REPLACE(@ListaParametrosEntrada,@Split,'</s><s>’)+ ‘</s></root>’)

insert into @T(Cod)

SELECT CAST(T.c.value(‘.’,’varchar(20)’)AS INT)

FROM @X.nodes(‘/root/s’) T(c)

SELECT * FROM @T

Print3Galera, ate o feriado eu edito algumas coisas, arrumo a formatação e vejo os erros de portugues, por enquanto o importante é que o código “funfa”.

Abs

Create Index não é Tuning II

Esse post é dedicado a um grande amigo que me ensinou muito sobre Tuning e Performance um dos melhores DBAs que conheci, Sergio Bonsague.

Esse artigo é continuação do último post lançado aqui no blog. A coisa mais divertida no SQL Server é saber, porque ele fez isso ou aquilo? Por que “tal” decisão foi tomada, entender um pouco desse mundo obscuro…rs. Vamos parar de tagarelar e colocar a mão na massa.

Se analisarmos o plano de execução do post print, o SQL Server escolheu utilizar um Clustered Index Seek para ler toda a tabela. Porém, fica a pergunta, Ele não poderia ter escolhido um Clustered Index Scan? Como vou ler tudo, um Scan não é melhor que um Seek? A resposta é: Depende.

Executando duas vezes a mesma query, uma com o HINT WITH(INDEX=0) que força a Não utiliza do Index da tabela e uma sem utilização de hint, para que possamos comparar o antes e depois.

 

Executando a query forçando um Clustered Index Scan Vs um Clustered Index Seek podemos ver que no plano de execução o custo de ambos é  de 50% (Atual e Estimado).

ambosPlano

 

Thiago, que dizer que é a mesma coisa? A resposta é não. Quando se está avaliando performance, o ideal também é utilizar como parâmetro os tempos de execução, para isso sempre uso o Profiler. A quantidade de leitura é muito menor quando utilizamos um Clustered Index Seek, Vejamos o resultado:

EvidenciaProfiler

 

O SQL Server realizou uma quantidade menor de leituras utilizando o Index Seek. Mas, porque?

No Seek temos duas propriedades que não aparecem no Scan que no caso é a “Direção da Leitura”, como pode ser visto utiliza o “FORWARD” e está marcada como “ORDERED” como True.

ClusteredIndexSeek

 

Já o Clustered Index Scan, essas propriedades não são apresentadas. É a única questão que acredito ser o motivo.

ClusteredIndexScan

 

 

Esperam que vocês tenham gostado.

 

Trabalhando com Credenciais e Proxy Account no SQL Server

Este post é dedicado à mulher mais importante da minha vida, que fez ser o que sou hoje com muita luta e dedicação minha mãe, Francisca de Alencar.

Após a dedicatória, vamos falar sobre SQL.

Bom dia. Galera, blz? Hoje vamos falar de alguns recursos que ganharam muita força a partir do SQL Server 2005. Segurança, no SQL Server foram incluídas diversas features, como por exemplo: TDE, Certificados, Credenciais, Proxy Account e etc.

No post , falamos de como podemos utilizar certificados para atribuir permissões especificas, sem ter que aumentar o nível de acesso de nossos logins. Nesse post vamos falar de como utilizar credenciais e proxy account. Um dos problemas que tínhamos no SQL Server 2000 era que quando era necessário executar o comando xp_cmdsheel, o login que disparava a chamada do comando precisava ser membro do server role sysadmin para poder executar com sucesso. A partir do SQL Server 2005, temos como permitir esse comportamento, sem que o login seja membro do sysadmin utilizando proxy account.

Imagine o cenário: No nosso ambiente temos diversos pacotes SSIS de integrações, porém, o login que os executa é um login do tipo SQL Server authentication que não pode fazer parte do server role sysadmin. Um dos motivos é que a publicação do pacote é feita através de file system. Thiago existe outra maneira mais segura de fazer isso? Claro, que sim meu querido blog, mas, no momento foi essa solução implementada.

Obs: Com o file system, é aberto uma brecha de segurança, pois, o login que tiver acesso a pasta onde os arquivos serão publicados, podem abrir o webconfig do pacote.

É triste dizer, mas, vi muitos DBAs que tiveram esse problema e colocaram o login dentro do server role sysadmin para “resolver” o problema. A solução que encontrei sem ter que dar a permissão “motherfucker” para o login que executa o pacote será explicada a seguir.

Vamos criar o login do tipo SQL Server dentro do server role bulkadmin.

/****** Object:  Login [PkgSSIS]    Script Date: 09/19/2012 20:00:58 ******/

IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N’PkgSSIS’)

DROP LOGIN [PkgSSIS]

GO

 

/* For security reasons the login is created disabled and with a random password. */

/****** Object:  Login [PkgSSIS]    Script Date: 09/19/2012 20:00:58 ******/

CREATE

            LOGIN [PkgSSIS] WITH PASSWORD=N’pkgssis’,

                  DEFAULT_DATABASE=[master],

                  DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

 EXEC sys.sp_addsrvrolemember @loginame = N’PkgSSIS’, @rolename = N’bulkadmin’

GO

 ALTER LOGIN [PkgSSIS] ENABLE

GO

Vamos atribuir permissão para a base de dados utilizadas nos post anteriores e para o msdb. No banco dbPermissoes o login terá que ficar dentro do database role DR_EXEC. Dentro do msdb o login ficará dentro do database role SQLAgentUserRole e SQLAgentReaderRole.

dbPermissoes:

Atribuição ao Role:

Msdb e atribuição aos roles:

 Por enquanto até aqui sem muitas surpresas e complicações. Agora queremos que esse login tenha permissão de executar pacotes, quando tentamos executar pacotes sem ser membro do server role sysadmin, a mensagem é retornada:

Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy accoutnt.

Primeiro temos que criar uma credencial, conforme script:

–Cria a credencial utilizando a conta de dominio SQLSVC e coloque o password da conta.

USE [master]

GO

CREATE CREDENTIAL [SSISCredencial] WITH IDENTITY = N’Dominio\SQLSvc’, SECRET = N’PasswordAqui’

GO

Vai ficar da seguinte forma:

Agora iremos criar um proxy account , atribuindo a essa credencial.

USE [msdb]

GO

 /****** Object:  ProxyAccount [SSISProxy]    Script Date: 09/18/2012 13:31:46 ******/

IF  EXISTS (SELECT name FROM msdb.dbo.sysproxies WHERE name = N’SSISProxy’)

begin

      EXEC msdb.dbo.sp_delete_proxy @proxy_name=N’SSISProxy’

end

GO

 USE [msdb]

GO

 /****** Object:  ProxyAccount [SSISProxy]    Script Date: 09/18/2012 13:31:46 ******/

EXEC msdb.dbo.sp_add_proxy @proxy_name=N’SSISProxy’,@credential_name=N’SSISCredencial’,

            @enabled=1

GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’SSISProxy’, @subsystem_id=3 — SubSystem: CMDExec

GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’SSISProxy’, @subsystem_id=11 — SubSystem: SSIS

GO

EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N’SSISProxy’, @login_name=N’PkgSSIS’

GO

O subsystem de Id 3 e 11 são os subsystem para utilizar CmdExec e SSIS..No modo gráfico é possível verificar que eles estão “ticados”:

Clique na gui “Principals” e verifique que o login PkgSSIS está marcado como SQL Login:

Tudo isso, foi feito no script anterior. Isso está sendo mostrado para que o leitor consiga identificar o que está sendo criado e onde fica.

Calma galera! Já estamos quase lá. Agora vamos ver onde essas informações devem ser atribuídas no Job. Neste meu caso o job já foi criado, apenas colocarei os prints aonde tive que modificar.

O owner do job, que executará será o login: Pkgs:

No steps podemos ver que o job é um pacote SSIS. Temos apenas que mudar quem executa o nosso job que não será mais a conta do SQL Server Agent e sim no Proxy.

Agora iremos executar o job e “Eureca”.

O que tem dentro do pacote? Apenas uma procedure que executa um comando update sem Where dentro de uma proc. A procedure é chamado dentro de um “Execute SQL Task” do SSIS. Não existe nada de funcionalidade, pois, o post é apenas pra mostrar que é possível executar um pacote sem ter que dar permissões “absurdos” para um login especifico.

Espero que tenha ajudado.

Abs

Configurando o Operador no SQL Server

Mais uma vez, bom dia galera. Hoje esse será um post rápido, pois, ta muito tarde(Post Agendado..rs.) e está na hora de ir para a cama. Este post de hoje é dedicado ao um homem que sempre me espelhei e sempre me deus bons conselhos, meu Pai, José Adão Carlos.

Em um ambiente de banco de dados corporativo, o DBA sempre deve ter em mente uma coisa: Como automatizar meu trabalho, para que sempre possa agir com pró-atividade? A resposta é meio simples,  automatize e sempre seja avisado dos problemas antes de eles começarem aparecer. Na prática não é tão fácil, mas, tente manter isso em mente.

No post anterior eu falei sobre database email e como configurá-lo. Mas, até ai beleza, o que faço com isso? Vamos automatizar nossas rotinas!?

Imagine que você criou um job de backup, e que ele deve ser feito toda  noite. Esse job é um job critico, e no caso dele falhar você precisa ser informado. Criaremos um operador, que trará a lista dos emails que devem ser informados. Além do meu e-mail adicionarei o pessoal do suporte que fica 24×7, assim, a cobertura da solução é maior.  Com o Database e-mail configurado e o operador criador, apenas temos que mudar uma opção no JOB em questão. Let’s go.

Expanda o item SQL Server Agent e clique com o botão direito sobre Operator e em seguida em New Operator.

Aparecerá a seguinte tela:
Name: Nome do Operador.
Email name:  Lista de emails que irão receber a notificação (os emails devem ser separados por ; )

Preencha os dados e clique em OK.

Notificação

Após estes procedimentos as notificações devem ser adicionadas nos Jobs.

Clique em um job com o botão direito vá em propriedades. Em notification coloquem as seguintes configurações:

Espero ter ajudado….

Abs e até o próximo post

O que é o DTA?

Amigos da comunidade, blz? Esse post é dedicado a um outro amigo de trabalho. O DBA Felipe Melo , conhecimento SQL Geek em replicação, tunning e T-SQL. Sempre trocamos uma “figurinha” nos desafios profissionais, afinal, sempre é bom ter uma outra opinião. A certeza é: Nunca estamos totalmente certos! Mão na massa!?

O Database Engine Tuning Advisor é uma das “novas” features do SQL Server, ele analisa arquivos de carga de trabalho e propõe alterações no banco de dados, a fim de melhorar seu desempenho geral.

Para as alterações propostas, o Tuning Advisor também mostra o impacto que causará cada modificação.

Entre as suas capacidades, estão:

  • Query Optimazer, para propor índices e visões indexadas;
  • Recomendação de particões;
  • Análise de impacto das recomendações;
  • Fornecimento de informações sobre o número de consultas e o número de índices.

Opções de ajuste:

  • Quais objetos o Tuning Advisor poderá recomendar;
  • Quais partições analisará;
  • Quais estruturas serão mantidas no banco de dados;
  • Espaço máximo para recomendações;
  • Número máximo de columas por índice.

Criando analises com o DTA

Para iniciar o DTA, vá em StartAll / Programs / Microsoft SQL Server 2008 / Performance Tools / DataBase Engine Tunning Advisor.

Quando o programa for iniciado, clique em File/New Connection.

Aparecerá a tela de conexão para qual servidor você deseja criar a análise

Obs: Para o exemplo será usado um ambiente de testes.

O servidor será mostrado no canto superior direito da tela, clique com o botão direito e escolha a opção New Session.

A seguinte tela será mostrada:

Clique na guia Tunning Options e será mostrado a seguinte tela:

Physical Design Structure(PDS) to use in database: Esta opção avalia o que a Engine do DTA irá analisar referente a objetos de design na estrutura do banco(índex,índex views e etc). A opção de Índex views é desabilitada no SQL Server STD.

Partitioning strategy to employ: A opção de particionamento, verifica se existem objetos que podem ser particionados e avalia o particionamento que já existente. Disponível somente na versão Enterprise do SQL Server.

Physical Design Structure(PDS) to keep in database: Avalia a estrutura fisica do banco, abalia se existem índices clustereds e nonclustereds devem ser deletados(ele não deleta os indices), ou se existe algo divergente na no modelo como um todo. O padrão é Keep All existing PDS.

Voltando a guia General, aonde:

  • Session Name: Nome da Sessão
  • WorkLoad: A origem de ondes virão os dados que serão analisados(no caso do profiler o mesmo pode ser um arquivo ou uma tabela)
  • DataBase for WorkLoad Analysis: Aonde serão gaurdadas as analises temporarias.
  • Select DataBase and tables to tune: Lista de banco de dados e de tabelas que serão parte da analise. (sempre coloque os bancos de dados que [*] foram filtrados no profiler)

Coloque o nome no profiler e escolha o caminho aonde está o arquivo de trace do profiler. Ao clicar no Radio Button File, clique no binóculo a esquerda e será aberta a tela do Windows. Escolha o profiler que deseja analisar.

Clique em abrir.

Caso o trace tenha sido guardado em uma tabela, marque o Radio Button Table, clique no binóculo a esquerda e será aberta a tela de conexão:
Escolha qual banco, schema e tabelas estão os profilers que foram armazeandos.

Obs:  O login qual abriu a sessão no DTA deve ter acesso as tabelas que estão os traces, caso contrário, uma mensagem de erro será retornada.

 

O preenchimento deve ficar parecido com a tela abaixo:

 

Clique no botão Start Analysis. Após clicar em Star Analysis, uma tela de progresso será exibida.

 

Na guia recomendações, é apresenta a porcentagem de melhoria da análise.

 

No menu Actions aparecerá a opção de aplicar as recomendações ou savá-las.
Clique em Save Recommendations

Será gerado um arquivo .sql com as recomendações.

Espero Ter ajudado

 

Atualizando a Edição do SQL Server 2008

Caros, bom dia.

Este post é dedicado ao meu amigo Lucas Souza, um DBA com quem venho aprendendo muito. Está sempre me acompanhando nos processos aqui na empresa e me instruindo.

Hoje vamos falar sobre um assunto bastante simples, porém, foi a primeira vez que tive que realizar o procedimento, atualizar a edição do SQL Server. Hoje temos aqui na empresa o ambiente de desenvolvimento, produção, testes e homologação. Junto com a equipe começamos uma iniciativa de padronizar a instalação do SQL Server, isso inclui Edição utilizada, service pack e cumulative updates. Após baixar o SP2 da versão do SQL Server 2008 R2, comecei a aplicar a instalação no ambiente de desenvolvimento, testes e homologação. Quando me deparei com um ambiente de homologação que está com a versão Standard do produto. E agora? Preciso reinstalar o SQL Serve novamente? Claro que não…

Vamos ao passo-a-passo

Inicialmente você deve copiar o instalador do SQL Server para a máquina qual deseja atualizar. De um duplo clique no setup.exe a seguinte tela será apresentada:

Clique em Yes e aparecerá o SQL Server Installation Center. No lado esquerdo clique em Maintenance.

Clique no Edition Upgrade, após isso, apenas devemos seguir os passos. Vamos fazer juntos?

O SQL Server irá fazer uma verificação, se todos os itens verificados passarem por essa verificação, prossiga. Caso algum item falhar, recomendo que seja ajustadoo item em questão e o processo seja refeito.

É altamente recomendado que todos os itens sejam aprovados no check que o SQL Server faz, só assim podemos garantir que não teremos problema futuramente.

Clique em OK.

Esse processo é bem parecido com o processo de instalação, após o OK confirmado na tela acima, aparecerá a opção da chave do produto, Clique em Next:

Marque o checkbox de “Aceito os termos” e clique em next:

Na próxima tela, podemos perceber que o instalador traz a versão atual do produto e para qual instancia será feito o “upgrade”.

Clique em next.O SQL Server fará uma verificação antes de lhe dar a opção de Update.

Clique em next. Você verá um sumario bem importante, que diz para qual versão está sendo modificado o SQL Server, qual ação está sendo tomada (No caso Edition Upgrade) e a lista de features que sofrerão com esta atualização:

Clique em Upgrade. Após isso é só aguardar o SQL Server terminar de atualizar todos os binários necessários para essa atualização. Quando isso acontecer, reinicie a máquina.

Obs: No post não foi focado cuidados que devem ser tomados no caso dessa atualização. É uma boa prática sempre realizar backup dos databases de sistema, quando for realizar alguma alteração que afeta as configurações da instancia do SQL Server

Espero ter ajudado

Abs

Trabalhando com Certificados no SQL Server 2008

Boa tarde pessoal.

Este post é dedicado a um amigo da empresa, Rogério Santini, extremamente inteligente, sempre me da os conselhos certos.No post anterior falamos sobre a importância de utilizar database roles para controlar o acesso aos servidores de banco de dados através das aplicações. Hoje iremos falar sobre um caso real que foi aplicado aqui na nossa empresa. Temos nossas aplicações executando as procedures através de database roles, porém, existe uma migração acontecendo que estamos trabalhamos com informações se sistemas legados.

Vamos partir do nosso exemplo anterior onde tínhamos uma tabela de clientes com a coluna IdCliente que tem a propriedade identity de 1,1. Imagine que essa tabela será populado pelo nosso sistema atual. A cada inserção, será atribuído um valor incremental, até ai nenhuma novidade, comportamento normal. Agora aparece uma variável em nossa implantação. O sistema legado que temos, ainda ficará em funcionamento, sendo assim, ele precisará realizar inserções em nossa tabela de clientes. Thiago não entendi!? Aonde você quer chegar? Vamos lá meu caro blog…rs.

O sistema legado já existe os seus IdClientes, com uma sequencia própria dele, neste caso, as procedures farão uso da opção SET IDENTITY_INSERT. E dai? Como já tenho permissão de execução de procedures no sistema, nada muda certo? ERRADO. Para realizar a operação de SET IDENTITY_INSERT o usuário deve ter permissão de DDL_ADMIN. No momento que liberar essa permissão, o login da aplicação pode criar qualquer objeto dentro do banco de dados e esse é um risco muito grande. Thiago então qual a solução? Vamos lá bloguinho. A partir do SQL Server 2005, aprimorou-se as features de segurança do produto. Uma das “novidades” é o uso de certificados e máster key. Podemos realizar o uso dessas features, para fazer com que o login que chamou a procedure possa executar a mesma com o comando set identity_insert sem ter permissão de DDL_ADMIN. Vamos ver como isso funciona?

Vamos se conectar ao banco de dados dbPermissoes e alterar a procedure stp_ins_cliente que usamos no exemplo anterior para pode adicionar o comando SET_IDENTITY_INSERT.

alter  PROCEDURE dbo.stp_ins_cliente(@Id int, @Nome VARCHAR(50), @Cpf CHAR(11))

AS

begin

       SET IDENTITY_INSERT dbo.Clientes ON

            INSERT INTO dbo.Clientes(IdCliente, NomeCliente,CpfCliente) VALUES(@id, @Nome, @Cpf)

      SET IDENTITY_INSERT dbo.Clientes OFF

 END

go

Executando a procedure após de alterada, temos a seguinte mensagem de erro:

EXECUTE AS LOGIN = ‘AplSistema’

go

EXEC dbo.stp_ins_cliente @id = 8 ,@Nome = ‘Alencar’,@Cpf =’133xxx63215′

GO

REVERT

Msg 1088, Level 16, State 11, Procedure stp_ins_cliente, Line 5

Cannot find the object “dbo.Clientes” because it does not exist or you do not have permissions.

A mensagem acima é clara, ou não tenho permissão ou o objeto não existe. Segundo que a hipótese da permissão é verdadeira. Vamos dar permissão de DDL_ADMIN para o login e testar a execução, para garantir que é isso mesmo.

USE [dbPermissoes]

GO

EXEC sp_addrolemember N’db_ddladmin’, N’AplSistema’

GO

Executando novamente:

EXECUTE AS LOGIN = ‘AplSistema’

go

EXEC dbo.stp_ins_cliente @id = 8 ,@Nome = ‘Alencar’,@Cpf =’133xxx63215′

GO

REVERT

Vejamos o resultado e o ID 8 foi inserido.

Iremos remover o login do database role do DD_ALMIN e faremos a solução “Elegante”.

Criaremos o database máster key dentro da base dbPermissoes:

use dbPermissao

go
CREATE MASTER KEY ENCRYPTION BY password = ‘MkE2012@#’

Criamos o certificado:

CREATE CERTIFICATE CrtPermissoes WITH SUBJECT = ‘Schema user Certificate’

Agora precisamos criar um usuário para a base de dados em questão. Esse usuário é o que chamamos de “without login”. Quando criamos um usuário no banco de dados que não está atribuído a nenhum login, a sintax é completamente simples. Na criação do usuário, atribuo ele aio certificado:

CREATE USER UsrCert FROM CERTIFICATE CrtPermissoes

No momento da mágica, apenas realizamos o comando abaixo:

GRANT ALTER ANY SCHEMA TO UsrCert ADD SIGNATURE TO dbo.stp_ins_cliente BY CERTIFICATE CrtPermissoes

GO

Esse comando da permissão de “ANY SCHEMA” para o usuario UsrCert que criamos, e essa permissão é atribuida pelo certificado CrtPermissoes. Como o Usuario AplSistema tem permissão de execução de procedures a solução torna-se viável e “clean”.  Iremos remover o login do DDL_ADMIN e realizar uma execução de insert, inserindo o IdCliente 666.

USE [dbPermissoes]

GO

EXEC sp_droprolemember N’db_ddladmin’, N’AplSistema’

GO

Executamos a procedure:

EXECUTE AS LOGIN = ‘AplSistema’

go

EXEC dbo.stp_ins_cliente @id = 666 ,@Nome = ‘Alencar’,@Cpf =’133xxx63215′

GO

REVERT

E BAZINGA!!!!!

Permissão – Database Role

Galera, boa tarde

O post de hoje é dedicado para o meu Amigo Silas Mendes, um ótimo DBA e um tremendo SQL Geek. O cara esté me pentelhando para eu instalar um plugin que deixa os códigos coloridos,mas, infelizmente ficará para o próximo  post. Nesse ficará tudo em AZUL o que for script.

Hoje vamos falar sobre uma coisa que “teoricamente”, não deveria acontecer no desenvolvimento de softwares, porém, é bem comum acontecer quando a aplicação está sendo desenvolvida, o desenvolvedor ou o mesmo DBA coloca o login da aplicação como owner do database, assim os problemas de permissão serão “resolvidos” mais rápidos.

O  login da aplicação que acessa o servidor de banco de dados deve ter as permissões mais restritivas possiveis. Na maioria das vezes o usuário deve estar dentro de um database role, e esse database role deve apenas ter permissão de execução nas procedures.

Quando o cenário acima é verdadeiro, nem o próprio login tem acesso de realizar qualquer operação diretamente nas tabelas, só através das procedures, isso é uma das vantagens da utilização de stored procedures, isolamento. As vezes acontece do usuário estar dentro dos roles: db_datareader e db_datawriter. Mas, se todas as interações entre banco de dados e aplicação for via stored procedures isso não deveria acontecer, o login apenas deve estar no role de execução.

Vamos colocar a mão na massa.

–Cria o banco de dados dbPermissoes

CREATE DATABASE dbPermissoes

USE dbPermissoes

go

–Cria o login AplSistema

CREATE LOGIN AplSistema WITH PASSWORD =’$@123456′ ,CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

go

–Cria o role que receberá as permissões de execução

CREATE ROLE [DR_EXEC]

go

–Crio usuario no banco de dados dbPermissoes. Ao fazer isso, estou atribuindo o usuario ao login que foi criado anteriormente

CREATE USER AplSistema FOR LOGIN AplSistema

–Adiciono o usuario AplSistema ao Role: DR_EXEC

EXEC sp_addrolemember N’DR_EXEC’, N’AplSistema’

No script acima criamos um banco de dados chamado:dbPermissoes, com um usuário e um database role. Vendo pelo modo gráfico, ficou da seguinte maneira:

Atribuição ao banco de dados:

Roles:

Agora criaremos uma tabela de clientes e realizaremos algumas inserções.

USE dbPermissoes

go

 IF EXISTS(SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID(‘dbo.Clientes’))

BEGIN

      DROP TABLE db.Clientes

END

go

CREATE TABLE dbo.Clientes

(

      IdCliente INT IDENTITY(1,1) NOT NULL

      ,NomeCliente VARCHAR (50) NOT NULL

      ,CpfCliente CHAR(11) NOT NULL

)

INSERT INTO dbo.Clientes (NomeCliente,CpfCliente) VALUES(‘Sheldon Cooper’,’12345685961′),(‘Leonard Hofstadter’,’98632156978′),(‘Penny’,’85236974112′)

Após nossa tabela ser criada, iremos criar duas procedures: Uma de inserção e outra de seleção. No código dessas procedures, iremos atribuir permissão de execute para o database role DR_EXEC que criamos no banco de dados em questão.

USE dbPermissoes

go

IF EXISTS(SELECT 1 FROM sys.procedures WHERE object_id= OBJECT_ID(‘dbo.STP_INS_Cliente’))

BEGIN

      DROP PROCEDURE dbo.stp_ins_cliente

END

go

CREATE PROCEDURE dbo.stp_ins_cliente(@Nome VARCHAR(50), @Cpf CHAR(11))

AS

begin

      INSERT INTO dbo.Clientes(NomeCliente,CpfCliente) VALUES(@Nome, @Cpf)

 END

GO

GRANT EXECUTE ON  dbo.stp_ins_cliente TO [DR_EXEC]

go

 IF EXISTS(SELECT 1 FROM sys.procedures WHERE object_id= OBJECT_ID(‘dbo.STP_SEL_Cliente’))

BEGIN

      DROP PROCEDURE dbo.STP_SEL_Cliente

END

go

CREATE PROCEDURE dbo.STP_SEL_Cliente

AS

begin

       SELECT IdCliente AS Id,  NomeCliente AS Nome ,CpfCliente AS CPF

            FROM dbo.Clientes

END

GO

GRANT EXECUTE ON  dbo.STP_SEL_Cliente TO [DR_EXEC]

go

Agora executaremos a instrução utilizando o login que foi criado no inicio do artigo. No nosso exemplo, não precisamos nem conectar na instancia, pois, podemos simular a execução de um outro usuário, utilizando a opção: Execute as Login.

Faremos os seguintes testes:

1º -Inserção na tabela de Clientes com o login AplSistema
2º -Inserção na tabela Clientes atraves da execução da procedure
3º -Select  na tabela de Clientes com o login AplSistema
4º -Select na tabela Clientes atraves da execução da procedure

Ao executar o teste 1º e 2º, veja o que acontece:

EXECUTE AS LOGIN = ‘AplSistema’

go

INSERT INTO dbo.Clientes(NomeCliente,CpfCliente) VALUES(‘Alencar’,’133xxx63215′)

GO

REVERT

Erro:

Msg 229, Level 14, State 5, Line 1

The INSERT permission was denied on the object ‘Clientes’, database ‘dbPermissoes’, schema ‘dbo’.

Com a procedure o registro é inserido:

EXECUTE AS LOGIN = ‘AplSistema’

go

EXEC dbo.stp_ins_cliente @Nome = ‘Alencar’,@Cpf =’133xxx63215′

GO

REVERT

Teste 2º e 3º:

EXECUTE AS LOGIN = ‘AplSistema’

go

SELECT IdCliente AS Id,  NomeCliente AS Nome ,CpfCliente AS CPF

            FROM dbo.Clientes

GO

REVERT

Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object ‘Clientes’, database ‘dbPermissoes’, schema ‘dbo’.

 EXECUTE AS LOGIN = ‘AplSistema’

go

EXEC dbo.STP_SEL_Cliente

GO

REVERT

Como podemos ver utilizar roles para restringir o acesso das nossas aplicações à recursos dos nossos banco de dados, pode aumentar drasticamente o esquema da segurança. Lembrando que a aplicação só pode realizar comandos DMLs e Execução de procedures. No conceito do mundo perfeito a aplicação não tem direito de criar objetos no banco de dados, e é disso que iremos falar no próximo post.

 

Restore Parcial no SQL Server

Galera, boa tarde.

Como de costume, sempre dedico meu post as pessoas próximas a mim, pois, são elas que nos moldam e nos incentivam a sempre dar um passo a frente. Este post é dedicado ao líder que tive, um verdadeiro líder, Eduardo G. Pinho.

Muito tempo que não posto nada, mesmo tendo os posts no meu note não estou arrumando tendo de publicar, espero que todo mundo entenda. Hoje gostaria de dar uma pequena pausa na seção de índices e falar sobre um assunto que não vejo muito na net. Restore de banco de dados por file groups. Vamos lá!?

O leitor deve esta se perguntando quando devo realmente usar esse cenário? Uma coisa interessante que percebi nas empresas onde passei é que os DBAs anteriores não seguiam realmente as boas práticas à risca. Esse cenário, seria mais adequado para ambientes que existam tabelas realmente gigantescas e que não podem sofrer expurgo. Quando o banco de dados é criado do zero a tarefa torna-se mais simples. É possível dimensionar todos os filegroups com a sua real necessidade. Uma vez que o desenho do banco não seguiu as boas práticas, fica realmente difícil ajustá-lo depois que o mesmo está sendo utilizado em produção.

Imagine o cenário que o sistema foi feito por módulos e as tabelas do módulo de clientes estão em um Filegroup e as tabelas de Produtos em outro filegroup, você pode voltar o módulo de clientes ONLINE enquanto vai fazendo restore parcial dos outros módulos (se os mesmos foram criados em filegroups distintos).

Para este post iremos criar um banco de dados com a seguinte estrutura:

Um arquivo de dados com a extensão .mdf em um filegroup primário , um segundo arquivos de dados com a extensão .ndf no filegroup FG_USER_SECUNDARIO e um arquivo de log.

CREATE DATABASE [VariosGruposDeArquivos]

ON  PRIMARY

(

      NAME = N’VariosGruposDeArquivos_Primary’,

      FILENAME = N’C:\T\VariosGruposDeArquivos_Primary.mdf’ , SIZE = 4096KB , FILEGROWTH = 10%

),  FILEGROUP [FG_USER_SECUNDARIO]

(

      NAME = N’VariosGruposDeArquivos_Secondary’,

      FILENAME = N’C:\T\VariosGruposDeArquivos_Secondary.ndf’ , SIZE = 4096KB , FILEGROWTH = 10%

)

LOG ON

(

      NAME = N’VariosGruposDeArquivos_log’,

      FILENAME = N’C:\T\VariosGruposDeArquivos_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%

)

GO

EXEC dbo.sp_dbcmptlevel @dbname=N’VariosGruposDeArquivos’, @new_cmptlevel=90

GO

Obs: Os arquivos estão sendo criados no C: apenas pra propósitos de testes, em um ambiente real esse cenário NUNCA deve ser real.

Após o banco ser criado, colocaremos o seu recovery model como full.

ALTER DATABASE [VariosGruposDeArquivos] SET RECOVERY FULL

Agora criaremos duas tabelas no nosso banco de dados, sendo que a tabela de Cliente será criado no filegroup PRIMARY e a tabela Produto será criada no filegroup FG_USER_SECUNDARIO.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Cliente](

      [ClienteID] [int] IDENTITY(1,1) NOT NULL,

      [ClienteCadastro] [datetime] NULL,

 CONSTRAINT [PK_Cliente] PRIMARY KEY CLUSTERED

(

      [ClienteID] ASC

) ON [PRIMARY]

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Produto](

      [ProdutoID] [int] IDENTITY(1,1) NOT NULL,

      [ProdutoCadastro] [datetime] NULL,

 CONSTRAINT [PK_Produto] PRIMARY KEY CLUSTERED

(

      [ProdutoID] ASC

) ON [FG_USER_SECUNDARIO]

) ON [FG_USER_SECUNDARIO]

GO

Obs: Como boa prática, sempre crie um arquivo secundário e atribua-o a um filegroup de usuário. Coloque o filegroup de usuário como padrão. O filegroup PRIMARY deve apenas ter os metadados do banco de dados em si, e nunca nenhuma informação de usuário. O estado do filegroup PRIMARY define o estado do database.

Agora iremos inserir informação nas duas tabelas.

INSERT INTO dbo.Cliente(ClienteCadastro) VALUES (GETDATE())

INSERT INTO dbo.Produto(ProdutoCadastro) VALUES (GETDATE())

INSERT INTO dbo.Cliente(ClienteCadastro) VALUES (GETDATE())

INSERT INTO dbo.Produto(ProdutoCadastro) VALUES (GETDATE())

Agora realizaremos um backup full do nosso banco de dados.

BACKUP DATABASE [VariosGruposDeArquivos] TO  DISK = N’C:\T\VariosGruposDeArquivos.bak’ WITH NOFORMAT, INIT, 

      NAME = N’VariosGruposDeArquivos-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

Se fizermos um select nas tabelas, veremos que cada uma delas possuem duas linhas.

Agora o que iremos fazer é o seguinte: Vamos “dropar” esse database e aplicar os restores com os arquivos de backups que já criamos anteriormente. Abaixo segue o script:

use master

go

drop database VariosGruposDeArquivos

Imagine o cenário que o banco de dados está corrompido e será necessário realizar o restore do seu banco parcial e liberando os módulos para os usuários conforme o restore vai acontecendo. O script abaixo traz o script de restore do banco de dados. Nesse script o “pulo do gato” está em duas clausulas: FILE e PARTIAL. Na clausula file você pode passa qual o datafile que você que realizar o restore e a clausula partial informa que o restore será feito em “pedaços”.

RESTORE DATABASE [VariosGruposDeArquivos]

FILE = N’VariosGruposDeArquivos_Primary’

FROM  DISK = N’C:\T\VariosGruposDeArquivos.bak’

WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10, PARTIAL

GO

Se fizermos um select na tabela de clientes os dados serão retornados, conforme o print:

Ao fazer um select na tabela de produtos que está em um outro datafile, um erro será retornado:

Msg 8653, Level 16, State 1, Line 1

The query processor is unable to produce a plan for the table or view ‘Produto’ because the table resides in a filegroup which is not online.

Vamos deixar o outro filegrouo online, aplicaremos novamente o comando de restore. O arquivo do backup será o mesmo, o que mudará dessa vez é o valor do parâmetro “FILE”. Agora nos iremos informar o nome do segundo datafile.

RESTORE DATABASE [VariosGruposDeArquivos]

FILE = N’VariosGruposDeArquivos_Secondary’

FROM  DISK = N’C:\T\VariosGruposDeArquivos.bak’

WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10

GO

Como podemos ver a tabela foi restaurada:

Ate a próxima KIDS

Conhecendo Indices Step-by-Step V

Fala Galera, blz? Bem vindos ao quinto artigo da série “Conhecendo Indices Step-By-Step”. Esse post é dedicado a Bianca Almeida de Oliveira, que esta sempre ao meu lado , minha incrível esposa. Hoje iremos ver como o SQL Server acessa os dados de um índice nonclustered em uma tabela clusterizada. Na seção três vimos como o SQL Server usa o RID para encontrar um registro especifico, conhecido como RID Lookup. A técnica aplicado para o caso de hoje é o que chamamos de KeyLookup. Vamos a prática:

Neste exemplo usaremos uma tabela chamada TB_CLIENTES que possui apenas um índice clustered na coluna ID_CLIENTE. Fazendo a query a seguir, o SQL Server optou por usar um Clustered Index Scan, vejamos:

SET STATISTICS IO ON

SELECT ID_Cliente, Nome, Col1

FROM TB_CLIENTES

WHERE Nome = ‘Colin B9A7D004’

SET STATISTICS IO OFF

Abaixo o resultado e o plano de execução:

Plano de execução:

Voltando as posts iniciais desta série é correto afirmar que os índices são construídos em estruturas B-Tree. No nosso plano o que foi apresentado foi um clustered index scan, mas, será que não seria interessante criarmos um nonclustered index por nome? Vamos avaliar?

CREATE NONCLUSTERED INDEX IXNC_Nome ON TB_CLIENTES(Nome)

Ao criar o índice, podemos afirmar que o SQL Server utilizará o índice para buscar os nomes, porém, como foi dito nos posts anteriores: O SQL server cria uma estrutura B-Tree pra cada índice e neste caso é o nome. Pare um pouco e pense caro leitor, a query apenas solicita o nome? NÃO. Ela ainda precisa retornar para o cliente a coluna Col1 e o Id_cliente, como o SQL Server busca essa informação? Ao executar a mesma query do inicio do artigo, podemos perceber que o SQL Server apenas efetuou seis leituras lógicas e fez o que chamamos de KeyLookup:

O KeyLookup pode ser visto como um dos responsáveis por alto custo no plano de execução:

Thiago! Não entendi, como o SQL Server fez isso? Vamos simular a leitura dos índices para entender esse comportamento.

Obs: Lembrando que o conceito, é muito parecido com os posts anteriores.

Tendo como base o comando DBCC PAGE gerado, vamos começar a leitura.

SELECT dbo.fn_HexaToDBCCPAGE(Root), *

FROM sys.sysindexes

WHERE name = ‘IXNC_Nome’

AND id = OBJECT_ID (‘TB_CLIENTES’)

DBCC TRACEON (3604)

DBCC PAGE (31,3,303,3) — 1º Leitura

Baseando-se no resultado do print, foram retornados dois registros. A pergunta é: A letra “C” vem antes ou depois da letra “J”? Resposta fácil: Que dizer que nossa próxima leitura é na ChildPageId 26528.

DBCC PAGE (31,3,26528,3) — 2º Leitura

Na nossa segunda leitura podemos ver que o cliente Colin B9A7D004, pode estar dentro da página 26661 ou 26662. Mas qual será a página certa? Posso afirmar com certeza que é a página 26661, pois, o cliente Colin que procuramos começa com B e em uma ordenação, os números sempre veem antes das letras. Que dizer que o cliente Colin 90026186 está antes do Colin B9A7D004. Vamos para a terceira leitura:

DBCC PAGE (31,3,26661,3) — 3º Leitura

EURECA! Encontramos nosso cara! Se você que está lendo, vem acompanhando todos os artigos da série, você deve se lembrar que quando fizemos essa mesma quantidade de leitura para uma Heap, encontramos uma coluna de nome “Heap RID (Key)” com valores em hexadecimais. Como estamos falando de uma tabela Clusterizada, temos a referência do nosso índice clustered (Coluna id_cliente(Key)) dentro do índice nonclustered. Thiago! Que dizer que todos nonclustered indexes possui o clustered index? CORRETO.

Obs: Muito cuidado ao escolher seus clustereds index. Caso você tenha uma tabela com um índice clustered composto por três colunas. E nesta tabela você tenha dois nonclustered indexes…as três colunas do índice clustered irá aparecer no índice nonclustered. Isso é um grande vilão de performance. Fique sempre atento: PERFORMANCE COMEÇA NA MODELAGEM.

Voltando ao assunto do post: A partir daqui fizemos três leituras, mas, o SQL Server apenas sabe qual o nome do cliente que ele está buscando no índice e automaticamente seu id_cliente que é 77939. Mais uma pergunta! Então, até agora apenas navegamos na estrutura B-Tree do índice nonclustered Thiago? Sim. Agora o que o SQL Server faz é pegar a “chave” e navegar na estrutura do índice clustered. Vamos então a quarta leitura? Mas, primeiro precisamos retornar o DBCC PAGE do índice clustered:

SELECT dbo.fn_HexaToDBCCPAGE(Root), *

FROM sys.sysindexes

WHERE name = ‘PK’

AND id = OBJECT_ID (‘TB_CLIENTES’)

DBCC PAGE (31,3,295,3) — 4º Leitura

Novamente a pergunta que o SQL Server faz. O valor de id_cliente 77939 é maior que o id_clente 40899? A resposta é sim. Vamos a quinta leitura, tendo como base a ChildPageID 19328.

DBCC PAGE (31,3,19328,3) — 5º Leitura

Como podemos ver, o cliente de id 77939 encontra-se dentro da página 20241 onde o id_cliente 77913 é menor que o id_cliente 77939. Vamos para a sexta e última leitura:

DBCC PAGE (31,3,20241,3) – 6º Leitura Encontramos o ID_Cliente = 77939

Pronto! Simulamos as seis leituras lógicas do SQL Server. Thiago uma pergunta! Como faço pra evitar que o SQL Server faça as últimas três leituras adicionais? Não seria mais performático pra ele achar a Col1 quando chegar no nível folha do índice nonclustered? A resposta é: Sim, seria mais fácil. No próximo post sobre índices, veremos uma das features que surgiu a partir do SQL Server 2005, o Covered Index que particularmente acho incrível.

Espero ter ajudado

Thiago Carlos [TC] de Alencar