Alexandre Lopes | Arquiteto de Soluções

Tenho andando meio sumido dos posts técnicos aqui no blog por uma causa bastante nobre: Além do trabalho excessivo como Microsoft Trainer e Arquiteto de Soluções, viajando inclusive para algumas cidades do Brasil, estou desenvolvendo um projeto de ECM com Sharepoint 2010. Pois bem, precisei comprar um livro para aprofundar meus conhecimentos visto que o projeto “abraçava” TODO o pacote Office e o Sharepoint. Eu não precisava de um livro para desenvolvimento, o mais importante era conhecer o produto e as suas funcionalidades e integração Office com Sharepoint.

Encontrei na Apress um excelente livro, que completou meus conhecimentos após estudá-lo no tempo recorde de 30 dias!

O conteudo do mesmo começa com uma visão geral, listas, biblioteca de documentos, usando Outlook, gerenciando listas a partir do Excel, usando Excel Services, criação de forms com InfoPah (conhece?), entre outros temas interessantes.

Lógico que o livro é muito bom e recomendo a sua utilização por todos os envolvidos com ECM e Sharepoint 2010. A sua leitura me foi extremamente útil e gratificante, servindo agora como fonte de referencia para consultas.

Segue a capa do livro que pode ser comprado em format e-book (minha escolha, assim posso levar para qualquer lugar no notebook ou até mesmo no pendrive):

Em breve vou voltar com os conteudos técnicos no blog, aguardem por novidades!

Abraços,
Alexandre Lopes

O Data Auditing atua em todas as camadas do SQL Server 2008, desde o servidor, passando por instancias, databases e tabelas. Neste post estaremos vendo o Data Auditing em ação executando o processo de auditoria em comandos DML (Select, Update, Delete e Insert) em um database específico.

Passo 1) Em Security -> Audits, clicar com o botão direito e criar um novo objeto de auditoria selecionando “New Audit”.

Passo 2) Em “Create Audit”, no campo “Audit Name” vamos digitar o nome da auditoria, neste caso, vamos auditar os eventos de select, insert, delete e update no database desejado. Portanto vamos dar o nome de “Audit select-insert-delete-update”.

Passo 3) Em “Auto Destination” vamos selecionar “Application Log” e clicar em OK.

Passo 4) O próximo passo é habilitar este objeto,clicando com o botão direito e selecionando “Enable Audit”.

Clique Close para finalizar.

Passo 5) Expandir o database desejado (que vai sofrer a auditoria de SELECT, INSERT, DELETE e UPDATE) e, em seguida, o objeto “Security” com o alvo em “Database Audit Specifications”.

Passo 6) Clique com o botão direito e selecione “New Database Audit Specification”.

Passo 7) Em “Create Database Audit Specification”, selecione um nome adequado para as tarefas que sofrerão auditoria. Podemos digitar “Database Audit Select-Insert-Delete-Update”

Passo 8) Em “Audit” selecionamos o objeto “Audit Select-Insert-Delete-Update”.

Passo 9) Em “Audit Action Type” vamos selecionar “Insert”, “Update”, "Delete" e “Select”, em seguida selecione a classe de objeto “Database” para cada um dos 4 tipos de ação.

Passo 10) Em cada classe de objeto, selecione o “Object Name” e escolha o database a ser auditado.

Passo 11) Em “Principal Name” selecione dbo, por exemplo e clique em OK para finalizar.

Passo 12) Botão direito em “Database Audit Select-Insert-Delete-Update” e selecione “Enable Database Audit Specification”.

Passo 13) Clique em CLOSE para fechar a janela “Enable Database Audit Specification”.

Passo 14) Deixe o seu database ser bastante utilizado para poder observar melhor os processos auditados. Depois de um tempo,  execute o Event Viewer e clique em “Application”. Abra cada evento e observe que todos os comandos SELECT, UPDATE, INSERT e DELETE efetuados após a habilitação da auditoria se encontram-se disponiveis no Event Viewer.

Auditoria de SELECT:

Auditoria de UPDATE:

Auditoria de INSERT:

Auditoria de DELETE:

Abraços,
Alexandre Lopes

Data Auditing é um recurso que, ao ser implementado, mantêm a conformidade com as Normas de Segurança existentes na empresa. Antes do lançamento do SQL Server 2008, a auditoria era realizada através do SQL Server Traces e Profiler. Agora Data Auditing é um objeto integrante do SQL Server.

Uma auditoria é a combinação de vários elementos em um único pacote para um grupo específico de ações ou ações de servidor de banco de dados.

O Data Auditing atua em todas as camadas do SQL Server 2008, desde o servidor, passando por instancias, databases e tabelas.

Auditoria de uma instância do SQL Server ou um banco de dados  envolve o acompanhamento e registro de eventos que ocorrem no sistema. Com base nas informações acumuladas, é possível rastrear as alterações, acessos e outras atividades no banco de dados e/ou no servidor.

Os componentes do SQL Server Audit se combinam para produzir uma saída que é chamado de “auditoria”, assim como uma definição de relatório que é uma combinação de elementos gráficos e de dados.

Enquanto estamos trabalhando com auditoria no SQL Server 2008 precisamos manter quatro objetos em mente:

SQL Server Audit: Este objeto de auditoria recolhe uma única instância do servidor ou banco de dados para monitorar. A auditoria está no nível de instância do SQL Server. Você pode ter múltiplas auditorias por instância. Quando você define uma auditoria, você especifica o local para a saída dos resultados. Este é o destino da auditoria. A auditoria é criada com status desabilitado.

Server Audit Specification: Pode-se criar uma Server Audit Specification por auditoria, porque ambos são criadas no escopo da instância do SQL Server.

Database Audit Specification: O Database Audit Specification também pertence a uma auditoria do SQL Server. Você pode criar uma especificação de auditoria por banco de dados SQL Server.

Target: Os resultados de uma auditoria são enviados para um destino, que pode ser um arquivo, o log de eventos de segurança, ou o log de eventos Windows Application. (A gravação no log de segurança não está disponível no Windows XP.)

Logs: Devem ser analisados e arquivados periodicamente para se certificar de que o destino tem espaço suficiente para gravar mais registros.

Com as informações acima, podemos começar a trabalhar com auditoria de dados no SQL Server 2008. No exemplo, estarei usando o SQL Server 2008 R2:

Passo 1) Expandir Security -> Audits;

Passo 2) Clique com o botão direito em Audits e selecione a opção "New Audit"

Passo 3) Em "Create Audit" digite em “Audit Name” um nome que faça referencia clara ao objetivo desta auditoria. Nesta demonstração estaremos trabalhando com auditoria a nivel de servidor, portanto o nome será “Audit-Backup” pois estaremos auditando rotinas de backup no servidor corrente.

Passo 4) Em “Audit Destination” selecionar “Application Log” e clicar OK.

Passo 5) Como pode-se observar, a Auditoria encontra Disabilitada, portanto clique com o botão direito em “Audit-Backup” e em “Enable Audit”.

Passo 6) Em “Enable Audit” clicar em “Close”  após o Status ficar em “Success”.

Passo 7) Abrir em Administrative Tools o Event Viewer, em seguida limpar o log de Aplicações clicando com o botão direito em selecionando “Clear Log…”, em seguinda clicar em “Clear”.

Passo 8) Selecionar “Server Audit Specifications” no SSMS, clicar com o botão direito e selectionar “New Server Audit Specification”.

Passo 9) Em “Create Server Audit Specification” digite um nome referente ao escopo da auditoria, pode ser “Server Audit Backup”.

Passo 10) Em “Audit” selecione o objeto de auditoria criado, ou seja, “Audit-Backup”.

Passo 11) Em “Actions” selecione “Audit Action Type” e escolha a opção “BACKUP_RESTORE_GROUP”. Em seguida clique em OK.

Passo 12) Em “Server Audit Backup” clique com o botão direito e selecione “Enable Server Audit Specification”.

Passo 13) Em “Enable Server Audit Specification” quando o Status estiver “Success” clique em “Close”.

Passo 14) Realizar backup de um database com sucesso e, em seguida, visualizar o processo de backup no log de aplicações do Event Viewer.

e também aqui…

Pronto! Todos os backups serão auditados a partir de agora utilizando a funcionalidade “Data Auditing”.

Abraços,
Alexandre Lopes

É chato administrar diversos servidores SQL Server e, em cada um deles, estar configurado como default um local diferente para backup. Daí voce quer realizar o backup através de código Transact-SQL e não sabe para onde enviar.

Pensando nisso, quando atuava com DBA resolvi escrever um código Transact-SQL que acessava o registry e pegava a variavel que continha o diretorio default. Depois bastava copiar e colar no Transact-SQL para fazer backup e pronto.

Com o tempo, o que era um simples código Transact-SQL se transformou em User-Defined Function (UDF) e acabou virando um conjunto de UDF + Stored Procedure que sempre criava no database master ou no model.

Recentemente um amigo me perguntou se eu tinha essa rotina ainda, e resolvi procurar para testar e ver se havia alguma possibilidade de acrescentar algo novo pois criei esse processo na época do SQL Server 2005.

No inicio do ano procurei essa rotina para incluir na palestra “scripts que não podem faltar no pendrive de um DBA” mas não encontrei. Assim sendo, resolvi testar novamente todo o processo, validar e colocar aqui no blog.

Tudo começou com um código Transact-SQL que vai direto no Registry para “pegar” o diretório default de backup configurado no servidor corrente:

Reparem que utilizo a Xp_Instance_Regread que é uma eXtended-stored Procedure não documentada pela Microsoft no SQL Server 2008. Pode procurar no BOL (Books OnLine que voce não vai achar).

Até o momento apenas temos o local aonde fica armazenado, por default, os backups desta instância, correto? Mas ficar digitando código Transact-SQL repetidamente acaba cansando e sendo pouco produtivo, podemos colocar em uma User-Defined Function (UDF – Função Definida pelo Usuario). E o foi que fiz aqui embaixo:

O que mudou? Agora tenho uma função e recebo em sua saida o local aonde fica armazenado o backup. Simples, não?

Próximo passo: Preciso automatizar TODO o processo, retornando apenas o caminho, preciso ainda concatenar com código Transact-SQL. Como a função já estava disseminada por várias instâncias e, inclusive, já estava sendo também distribuida pelo database MODEL cada vez que um database era criado, resolvi manter a função e criar uma stored procedure (segue o código):

Podemos observar que utilizo a UDF e faço a concatenação do caminho completo + database + a extensão .BAK e guardo em uma variavel (BackupFinal). Em seguida utilizo o comando BACKUP DATABASE com a variavel que contém o nome do database (BackupName) e gravo no disco com as informações da variavel (BackupFinal). Não esqueço de mandar uma mensagem que o processo finalizou com sucesso (vou mexer outro dia para colocar BEGIN…TRY e BEGIN..CATCH).

O passo final é executar a Stored Procedure passando o nome do database desejado (passei através de uma variavel) e ver o resultado (no exemplo abaixo utilizo também um GETDATE() apenas para ter certeza que o backup foi feito com sucesso MESMO!

Finalizando, vamos ver se foi realmente realizado de forma correta o backup que deve estar em E:\Backup:

Observando data e hora temos certeza que TUDO funcionou. Agora fica facil migrar essa rotina para outras instancias, servidores ou database MODEL bastando criar a UDF e Stored Procedure e incluir a “chamada” para a Stored Procedure em uma tarefa agendada.

Abraços,
Alexandre Lopes

A integração do SQL Server com o .NET framework é uma das principais características implementadas no SQL Server para ajudar os desenvolvedores que podem mover seus códigos .NET para mais perto do banco de dados usando CLR – CLR significa Common Language Runtime.

O CLR proporciona um ambiente otimizado para o processamento de tarefas de uso intenso que podem ser executados na camada de SQL Server da arquitetura do seu software.

Os DBAs possuem a necessidade de um forte conhecimento de CLR para auxiliá-los na tomada de decisões no momento do desenvolvimento da solução. Mas se o DBA ignorar o CLR, vai estar perdendo uma parte significante do potencial que o SQL Server pode oferecer aos seus projetos, limitando assim a sua eficácia com o produto.

CLR é um tópico muito debatido nas comunidades técnicas, mas também é frequentemente mal interpretado, pois os desenvolvedores costumam falar que o CLR é uma caixa preta e adiciona mais trabalho na escrita de códigos.

Inquestionavelmente, haverá trabalho adicional para escrever código CLR, mas esse trabalho será recompensado com um acréscimo nas opções de desenvolvimento.

O que é SQLCLR?

SQLCLR é um recurso presente no SQL Server desde a versão 2005 e que permite inserir lógica escrita em C# e Vb.Net para ser utilizado pelo Transact-SQL em objetos como stored procedures, funções, triggers, agregações e tipos definidos pelo usuario. As aplicações-cliente interagem com essas rotinas como se elas estivessem escritas em SQL nativo.

Internamente, o código como seqüência de manipulações e cálculos complexos tornam-se mais fácil de programar, porque você não está restrito a usar somente Transact-SQL e agora têm acesso a estruturada .Net para poder expandir seus códigos.

Externamente, a lógica de criar é envolta em protótipos Transact-SQL para que o aplicativo cliente não tenha conhecimento dos detalhes da aplicação. Isto é vantajoso, pois você pode empregar CLR onde você precisa de verdade, sem refazer seu código de cliente existente. Com o CLR voce também está livre da restrição da lógica que se aplica somente dentro do contexto do banco de dados; no código Transact-SQL.

Você pode escrever, desde que tenha as permissões adequadas, a lógica para ler e gravar em sistemas de arquivos, lógica de utilização confinada em objetos COM ou DLLs, ou mesmo um resultado de processos de Web Services.

Uma vantagem que algumas empresas de desenvolvimento de software utilizam, é após criar o assembly no SQL Server, apagar a DLL original pois não se faz mais necessário a sua existencia no servidor SQL Server. Ou seja, após a criação do assembly a DLL se torna inútil para todo o processo do CLR ser executado no SQL Server. Esta é uma grande vantagem em relação à sistemas legados que utilizavam o acesso à DLL para executar seus processos (principalmente para validar se a instalação estava dentro do contrato de licenciamento, por exemplo).

Quais são os objetivos do CLR?

Os arquitetos do time de integração do CLR com o SQL Server tiveram alguns objetivos para esta funcionalidade:

1) Confiabilidade: O código gerenciado escrito por um desenvolvedor não deve ser capaz de comprometer o servidor SQL Server que está hospedando o codigo.

2) Escalabilidade: O código gerenciado não deve parar o SQL Server a partir do suporte de milhares de sessôes concorrentes do usuário, o qual foi projetado para suportar a demanda.

3) Segurança: O mesmo código gerenciado deve aderir as práticas de segurança padrão do SQL Server e também as suas permissões. Os administradores deverão ser capaz de controlar os tipos de recursos que os Assemblies CLR podem acessar.

4) Performance: O código gerenciado a ser hospedado no SQL Server deve ser executado tão rapidamente como se ele estivesse sendo executado através de uma aplicação fora do SQL Server.

Objetos suportados pelo CLR no SQL Server:

Um desenvolvedor pode criar código CLR para manipular os seguintes objetos do SQL Server:

1) Stored procedures

2) Triggers

3) User-defined Functions (UDF)

4) User-defined aggregates (UDA)

5) User-defined types (UDT)

Modelo de Segurança:

O modelo de segurança do SQLCLR é chamado de segurança de acesso ao código ou CAS do ingles “code access security”. O principal  objetivo do  CAS é evitar  que código não-autenticado realize tarefas que devem exigir autenticação prévia. O CLR identifica código e seus grupos de código relacionado como “workloads” em tempo de execução. O grupo de códigos é a entidade que auxilia o CLR em associar um assembly com o conjunto de permissões. Este conjunto de permissões detemina quais ações são permitidas para serem executadas no código.

Conjunto de permissões são atribuidas pelo administrador do servidor em que o SQL Server está sendo executado.Vamos ver, rapidamente, o conjunto de permissões CAS:

Quando o desenvolvedor cria os assemblies no SQL Server, ele deve atribuir um conjunto de permissões. Essas permissões determinam quais ações correspondentes o assembly pode realizar. Existem 3 permissões-padrão que voce pode atribuir aos seus assemblies quando está carregando-os no SQL Server:

SAFE – Indica que o acesso local é permitido e seguro.

EXTERNAL_ACCESS – Esta permissão vai herdar todas as permissões atribuidas pelo SAFE mais as permissões para acessar arquivos no file system, acesso as redes e ao registro além das variaveis de ambiente. Ou seja, além do acesso interno ao SQL Server, com esta permissão o codigo poderá fazer acesso ao registry ou file system, por exemplo.

UNSAFE – é o mesmo que o EXTERNAL_ACCESS sem algumas das suas restrições e inclui a capacidade para executar código não gerenciado. Ou seja, é o menos seguro das 3 permissões.

Qual a melhor opção: Usar CLR ou Transact-SQL?

Uma pergunta bastante frequente: Quando devo implementar CLR ou Transact-SQL? O CLR não é um substituto para o Transact-SQL; ele o complementa.

Algumas diferenças básicas entre os dois modelos: o código CLR é compilado no modelo SQLCLR e interpretado no Transact-SQL. Uma grande vantagem quando se utiliza o CLR é que este acessa diretamente as bibliotecas de classes do .NET Framework. Sendo assim, em determinadas situações do seu projeto, se voce deseja alta performance em calculos complexos, a melhor opção provavelmente será usar CLR.

Usando CLR ou eXtended-Stored Procedures?

As eXended-stored Procedures (ou XPs) são escritas em C/C++, e a partir daí são geradas APIs que produzem as DLLs que o SQL Server pode carregar, executar e descarregar em tempo de execução.  As eXtended-stored Procedures são notórias por causar vasamentos de memória e comprometer a integridade dos processos do SQL Server. Além disso, todos já ouviram falar que a Microsoft pretende em versões futuras do SQL Server não suportar mais as eXtended-stored Procedures. Com isso, o CLR fornece uma alternativa segura de substituição as eXtended-stored Procedures desenvolvidas.

E o CLR na vida de um DBA? É uma boa opção? Vale a pena?

O DBA é normalmente muito cauteloso ao dar aos desenvolvedores a flexibilidade necessária para que estes realizem suas tarefas de desenvolvimento no banco de dados. Um pedaço de código mal escrito pode comprometer toda a instancia do SQL Server no qual ele esteja sendo executado.

Como mencionei anteriormente, uma eXtended-stored Procedure pode ser a causadora de problemas na instancia em que está sendo executada no SQL Server, e isto reforça a politica do DBA em dar permissões bastante restritivas aos Desenvolvedores; afinal o DBA é o responsavel por manter a boa performance e a segurança no SQL Server.

O CLR é desabilitado por padrão após a instalação do SQL Server, isto devido a estratégia da Microsoft chamada “secure by default”, ou traduzindo, segurança por padrão. Óbvio que manter o CLR desabilitado não é necessariamente uma opção ruim, mas caso seja necessário habilita-lo o DBA pode aplicar aos desenvolvedores as permissões de acesso via CAS que considero a melhor solução.

A partir do momento que o DBA aceita executar CLR em seu ambiente SQL Server, os desenvolvedores terão um ambiente seguro para desenvolver código CLR integrado com Transact-SQL e o mais importante, com as permissões corretas.

Passo-a-passo para criação e entrega de rotinas CLR:

Os procedimentos para criação, entrega e uso de uma rotina baseada em CLR no SQL Server é extremamente simples e segue uma ordem, vamos observar a figura abaixo e ir identificado cada etapa do processo:

1) Criar a classe publica usando linguagem .NET tendo pelo menos um método estatico e um ponto de entrada;

2) Compilar a classe gerando um arquivo .DLL;

3) Carregar o arquivo .DLL dentro do SQL Server de destino usando o comando CREATE ASSEMBLY;

4) No objeto do SQL Server que pode ser stored procedure, function, trigger, deve ser feita referencia ao assembly criado;

5) Feito isto, basta utilizar o objeto criado no SQL Server;

6) Opcionalmente, voce pode apagar a DLL pois a mesma não será mais utilizada pelo SQL Server.

Na essencia, um exemplo do funcionamento de uma estrutura com SQL CLR poderia ser a seguinte:

1) O desenvolvedor cria suas DLLs cujo conteudo podem ser a logica de negócios, exemplo validação do CPF e validação de e-mail. Essas DLLs são criadas em VB.NET ou Visual C# por exemplo;

2) O DBA cria o assembly especifico no banco de dados desejado;

3) O desenvolvedor, tendo as permissões adequadas, cria um stored procedure em T-SQL que fará a chamada a DLL especifica que contém a regra de negócios;

4) O DBA gerencia as permissões tanto para os desenvolvedores quanto para os usuários que estarão fazendo chamadas a stored procedure criada pelo desenvolvedor;

5) A aplicação executa a stored procedure armazenada no servidor SQL Server.

6) Para o usuário final todo o processo é transparente, não sabendo identificar identificar se o codigo encontra-se em stored procedure escrita em formato padrão do SQL Server (Transact-SQL) ou a Stored Procedure faz uma chamada ao CLR.

Vamos ver na prática todos os passos, desde a criação no Visual Studio 2008 até a execucação no SQL Server. Esta demonstração apresenta o uso de CLR através de um código VB.NET e tem como objetivo apagar backups do SQL Server anteriores a um determinado numero de dias. Primeiro, vamos observar na figura abaixo que existem diversos backups do SQL Server que foram realizados (12 no total):

No Visual Studio…

1) O nosso primeiro passo, é no Visual Studio 2008 criar um novo projeto, Selecionar em “Project Types” -> Visual Basic;

2) clicar em “Class Library” para criar uma DLL, sem esquecer de selecionar o framework a ser utilizado (NET Framework 3.5);

3) Em “name” digitar “CLRapagabackups” e Ok;

4) Em “Solution Explorer” clicar em “CLRapagabackups” com o botão direito. Em seguida clicar em “Adicionar” e “Item Existente”. Vamos em “e:\projetos\CLR”, selecionar “CLRapagabackups.vb”. Agora basta clicar em “Adicionar”;

5) No “Solution Explorer” clicar com o botão direito em “class1.vb” e “Delete” + “Ok”;

6) Duplo clique em “CLRapagabackups”;

7) Observando o codigo, notamos uma classe publica "CLRFunctions" e uma função chamada "DeleteFiles" que recebe como parametros o caminho, quantos dias para trás devemos apagar os arquivos de backup e qual a extensão do arquivo de backup (ver figura abaixo):

8) No meio do código existe um For Each que fica em loop procurando os arquivos que satisfaçam os parametros que foram passados e aplica um delete nos arquivos;

9) Clicar em Save ALL;

10) Neste ponto vamos precisar gerar a DLL, clicando em “Build” + "Solution", podemos observar que na saida não tivemos nenhum erro… e a DLL foi gerada com sucesso;

11) Nosso trabalho com o Visual Studio acabou, vamos para o SQL Server….

No SQL Server Management Studio…

Com o SSMS aberto e estando no database "master", vamos precisar configurar o "master" com ALTER DATABASE model SET TRUSTWORTHY ON para que Os módulos de banco de dados (por exemplo, UDF ou Stored Procedures) que usam um contexto de representação possam acessar recursos fora do banco de dados.

Aproveitamos para verificar se está habilitado o CLR no servidor (o default é 0 – zero).

O valor encontra-se em 0, ou seja, está desabilitado… Para habilitar basta digitar a linha abaixo:

Agora, vamos criar o Assembly…

Criado o vinculo, vamos agora criar uma função:

Criada a função, vamos executa-la passando como parametros o diretorio aonde estão os arquivos de backup com mais de 5 dias de existencia e a extensão do arquivo de backup. Ou seja, no exemplo abaixo, ele irá manter os backups realizados nas ultimas 120 horas independente do numero de backups diarios existentes e apagará somente arquivos com extensão .BAK (default do SQL Server para arquivos de backup).

Resultado: 7 arquivos de backup com extensão .BAK foram apagados, vamos notar que outros arquivos mais antigos mas com extensão diferente foram mantidos, ou seja a regra “arquivos com extensão BAK com mais de 120 horas” foram excluidos; os demais mantidos intactos.

Abraços,
Alexandre Lopes