Neste momento que tenho que me dedicar às varias atividades do mestrado (aulas, leituras, artigos, seminários, reuniões, pesquisas…) e manter o mesmo desempenho no trabalho, não estarei escrevendo mais no blog, ou pelo menos não na mesma regularidade, mas continuarei respondendo as dúvidas que vocês têm enviado nos comentários ou por e-mail.

Procurei nestes três anos tratar assuntos que eu gosto, como SMO, SQL CLR, Service Broker, FOR XML e XQuery, também artigos que escrevi com o objetivo de disponibilizar tutoriais para ajudar no dia-a-dia dos novos DBAs, como habilitar SQL Authentication, habilitar conexão remota, alterar as portas de conexão do SQL Server, usuários orfãos, scripts para validar CNPJ, entender JOINs, scripts de cidades, scripts para o dicionários de dados e vários outros assuntos, que algumas vezes não estavam diretamente ligados com SQL Server, como os tutoriais de NoSQL e alguns artigos sobre gestão e carreira.

Obrigado todos aqueles que contribuíram com esta iniciativa!!!

No blog, busquei explorar de forma introdutória como trabalhar com esta feature e uma visão geral de cada objeto que é possível desenvolver. Em paralelo também tratei algumas extensibilidades, como trabalhar como F#, Web Services, referenciar projetos de terceiros nos projetos CLR e novidades no lançamento do Visual Studio 2010.

Introdução:

Este é um passo-a-passo de como iniciar com SQL CLR, desde questões mais á nível de SQL Server, até questões á nível de desenvolvimento (debug e publicação).

Integração SQL CLR no SQL Server – Uma Visão Geral [NOVO]

http://sqlfromhell.wordpress.com/2012/04/20/integracao-sql-clr-no-sql-server-visao-geral/

Habilitando CLR no SQL Server:

http://sqlfromhell.wordpress.com/2009/09/19/habilitando-clr

Criando funções CLR para o SQL Server:

http://sqlfromhell.wordpress.com/2009/09/26/funcoes-clr

Debug de funções CLR no SQL Server:

http://sqlfromhell.wordpress.com/2009/10/03/debug-clr

Publicando Assemblies e Funções CLR no SQL Server:

http://sqlfromhell.wordpress.com/2009/10/10/publicando-clr

Publicando Assemblies no SQL Server pelo Visual Studio:

http://sqlfromhell.wordpress.com/2010/05/15/publicando-assemblies-clr-no-sql-server-pelo-visual-studio

Visão Geral:

Para quem quer conhecer cada um dos objetos que permitem desenvolvimento na integração SQL CLR, elaborei uma visão geral orientada á muitos exemplos de cada um.

Stored Procedure:

http://sqlfromhell.wordpress.com/2010/07/03/clr-stored-procedure

http://sqlfromhell.wordpress.com/2010/07/13/clr-stored-procedure-2

User Defined Function Scalar:

http://sqlfromhell.wordpress.com/2010/07/25/trabalhando-com-clr-user-defined-function-scalar

Table-value User Defined Functions:

http://sqlfromhell.wordpress.com/2010/12/02/sql-clr-table-value-user-defined-functions

User Defined Aggregate Functions:

http://sqlfromhell.wordpress.com/2010/12/14/user-defined-aggregate-functions

User Defined Type:

http://sqlfromhell.wordpress.com/2010/12/22/sql-clr-user-defined-type

Triggers:

http://sqlfromhell.wordpress.com/2010/12/31/sql-clr-trigger

Estendendo:

Atendendo alguns pedidos e uma “aventura” pessoal (F#).

Web Services:

http://sqlfromhell.wordpress.com/2010/03/07/webservice-e-funes-clr-no-sql-server

F# no SQL Server:

http://sqlfromhell.wordpress.com/2009/10/17/fsharp

Referenciando uma Class Library em Projetos SQL-CLR (Draft):

http://sqlfromhell.wordpress.com/2009/09/30/crl-classlibrary

Trabalhando com SQL CLR e Spatial Data [NOVO]

http://sqlfromhell.wordpress.com/2011/07/27/trabalhando-com-sql-clr-e-spatial-data/

Novidades:

Como com cada lançamento do Visual Studio temos o suporte SQL CLR permitindo a utilização das novas .NET Framework, mostrei que no caso do Visual Studio 2010 tivemos até algumas novidades mais interessantes.

Novidades do Visual Studio 2010:

http://sqlfromhell.wordpress.com/2010/05/16/o-que-mudou-nos-projetos-sql-clr-do-visual-studio-2010

Ano passado escrevi quatro artigos que oferecem uma introdução de como utilizar XQuery no SQL Server.

Iniciando com XQuery – Lendo XML no SQL Server

Iniciando com XQuery – Gerando XML no SQL Server

Iniciando com XQuery – Modificando XML no SQL Server

Iniciando com XQuery – Namespaces têm solução!

No blog, trabalhei esta feature em três situações:

Introdução ao Service Broker – Parte 1 – Monólogo

Introdução ao Service Broker – Parte 2 – Diálogo e Permissões

Introdução ao Service Broker – Processos assíncronos

Neste artigo veremos que com um planejamento orientado por algumas perguntas e checklist é possível adotar soluções com Policy Based-Management respondendo necessidades do cenário, de forma que esta não se torne uma “feature órfã”, por permitir melhorias e acompanhar a evolução e mudanças do cenário.

Para que serve:

O planejamento na adoção do Policy Based-Management permite adequar o cenário às normas organização e às boas práticas de disponibilidade, capacidade, continuidade e segurança.

Em que situação o tema útil:

Mesmo que o Policy Based-Management seja uma feature que demande de pouca complexidade, o seu planejamento permitirá melhor entendimento do cenário, de forma que soluções com esta feature atendam adequadamente contratos de SLA, as normas organização e boas práticas de disponibilidade, capacidade, continuidade e segurança, acompanhando até mesmo a evolução do cenário.

Introdução

O Policy Based-Management (PBM) é uma feature introduzida no SQL Server a partir da versão 2008, a qual permite os administradores de banco de dados obterem o controle dos seus ambientes com a aplicação de regras e padrões sobre uma ou várias instâncias do SQL Server, além da possibilidade de reutilizar estas regras e padrões em outros cenários e versões anteriores do SQL Server (2000/2005).

Mas antes de implementar qualquer solução com esta feature, é necessário planejamento e entendimento do cenário, no qual, respostas a perguntas simples “por que, o que, como” poderão ajudar numa adequada implementação desta feature, de forma que as soluções implementadas não venham proporcionar qualquer resultado positivo ao cenário.

Este artigo vem a oferecer um conjunto de boas práticas tanto para o planejamento e adoção do PBM, como para o desenvolvimento de soluções e monitoramento das mesmas, auxiliando a responder as perguntas “por que, o que, como” desta feature.

Para um melhor entendimento, os termos disponibilidade, capacidade, continuidade e segurança utilizados neste artigo seguem algumas releituras do ITIL (Information Technology Infrastructure Library, http://en.wikipedia.org/wiki/ITIL):

Estes termos se traduzem como os objetivos da administração de banco de dados, assim como de qualquer serviço da área de TI.

No que se refere à estrutura deste artigo, ela se aproxima a algumas de práticas de resolução de problemas, como o PDCA (Plan-Do-Check-Act), adaptadas para adoção de novas soluções, onde o ciclo planejar, implementar e acompanhar (avaliar e analisar), necessitou somente de uma ênfase na estratégica por traz do planejamento, e na segurança, por questões específicas do PBM.

Por quê? Estratégia

As justificativas para adoção de qualquer feature têm que responder muitos porquês, que podem simplesmente evitar que seja implantada qualquer solução, mas respondendo estes porquês, será possível entender a importância desta feature.

Um das justificativas comuns para adoção do PBM é a importância de adequar o cenário às normas organização ou projeto e boas práticas de disponibilidade, capacidade, continuidade e segurança. Lembrando que todos os cenários existem normas, mesmo quando estas não estão documentadas, elas ainda se fazem presentes.

Também é possível encontrar a organização que se baseiam suas normas em contratos de SLA (Service Level Agreement ou Acordo de nível de serviço, http://en.wikipedia.org/wiki/Service_level_agreement), exigindo um mínimo de requisitos (disponibilidade, capacidade, continuidade e segurança) dos serviços prestados aos seus clientes, e por consequência, exigindo dos administradores de bancos de dados, o uso do PBM e outras ferramentas para monitorar várias instâncias do SQL Server e seus bancos de dados.

Quando a adoção do PBM não é justificada por normas, mudanças e problemas da organização, é interessante pensar se boas práticas de disponibilidade, capacidade, continuidade e segurança recomendadas pelo fornecedor, no caso a Microsoft, estão presentes nas normas do ambiente, pois uma simples adoção do PBM pode ser justificado por perguntas simples como:

• O último backup é recente?
• Existe espaço de disco para suficiente para os bancos de dados?

Ou até algumas perguntas um pouco mais complexas:

• Existe alguma configuração que pode estar comprometendo com o desempenho ou segurança do banco de dados e do servidor? Pelo menos, tem certeza que não existe nenhum login configurado de forma a permitir uma senha fraca?
• Existe alguma feature deprecated configurada? Para uma relação destas, é interessante acompanhar: http://msdn.microsoft.com/en-us/library/cc280407.aspx
• Existe alguma stored procedures criadas por usuários cuja nomenclatura ofereça problemas de desempenho? Sim, isso pode ocorrer com stored procedures de nomenclatura sp_*, pois o SQL Server procurará stored procedures com esta nomenclatura primeiramente no banco de dados master e depois no banco de dados na qual elas são executadas, até mesmo podem surgir alguns erros inesperados ou algumas stored procedures não serem encontradas quando executadas.

Estas perguntas podem ajudar a identificar algumas boas práticas, mas se estas boas práticas ainda estão claras, é possível encontrar no site do TechNet e MSDN, uma série de boas práticas, sendo algumas genéricas (segurança, backup…) e outras para cenários específicos (virtualização, BI…), que podem ajudar a identificar quais são úteis para cada cenário. Sempre lembrando que as organizações podem adotar práticas que até mesmo violam ou ignoram as boas práticas e recomendações pelo fornecedor, no caso a Microsoft.

Boas práticas, TechNet:
http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx

Boas práticas, MSDN:
http://msdn.microsoft.com/en-us/sqlserver/bb671432.aspx

Na pasta C:\Program Files\Microsoft SQL Server\110\Tools\Policies (em ambientes x64, C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Policies) existem uma série de arquivos XML que podem ser importados pelo PBM em forma de regras, com a finalidade de validar boas práticas para administração do SQL Server. Uma breve descrição de cada um destes arquivos é possível de ser encontrada no seguinte link:
http://msdn.microsoft.com/en-us/library/cc645723.aspx

O que? Planejamento

Ao se pensar no que será feito, é importante entender o que a feature pode oferecer e por outro lado, avaliar se não existe outra solução que responda de forma mais adequada as necessidades do cenário. Esta avaliação é importante, visto que uma feature como o Policy Based-Management ser passível de ser substituída por consultas, relatórios, jobs e triggers, outras ferramentas ou até mesmo solução de terceiros, mas o tempo, custo, flexibilidade e complexidade de desenvolver uma solução do “zero” ou adotar uma solução de terceiros são pontos a serem levados em consideração, já que muitos dos potenciais desta feature esta na praticidade de adotá-la.

Mas o que esta feature pode fazer? Basicamente, o PBM proporciona soluções de automatização com a finalidade de diminuir o tempo que o administrador de banco de dados deveria se dedicar à manutenção, que pode se estender às regras de configuração no nível de servidores até objetos do banco de dados, como tabelas e índices. O PBM oferece algumas soluções para gerenciar as regras implementadas pelo administrador de banco de dados, que ser monitoradas por meio de agendamento ou por demanda (executada pelo SQL Server Management Studio), gerar alertas ou até mesmo prevenir que as regras sejam violadas por algum usuário.

As regras do PBM são compostas por três estruturas básicas:

• Target Types, estruturas que representam os tipos de objetos manipulados pelo PBM, exemplo: Tables, Views e Databases.
• Management Facets, estruturas que representam grupos de propriedades de determinados Target Types, como por exemplo, Name, Table Options, Database Option e Surface Area, que são utilizadas para criar condições/filtros.
• Condition ou Condições/Filtros, estruturas que representam expressões lógicas por meio das Facets, a fim de aplicar filtros e validações.

As possibilidades de criar regras no PBM não estão limitadas ás suas facets, visto que há a possibilidade de customizações com consultas T-SQL e WMI. Mas é importante levar em consideração que estas customizações demandam de bom senso dos envolvidos, para evitar que sejam desenvolvidas soluções muito complexas ou que interfiram na disponibilidade e segurança do servidor.

No blog oficial de PBM, é possível obter uma relação das facets e os target types que elas manipulam:
http://blogs.msdn.com/b/sqlpbm/archive/2008/05/24/facets.aspx

A partir do momento que se entende o potencial da feature, é possível definir um checklist para determinar a solução a ser desenvolvida:

1. Justificativa: Existem normas ou boas práticas a serem adotadas em relação à disponibilidade, capacidade, continuidade e segurança do SQL Server?
2. Extensão: Qual a extensão destas normas e boas práticas (Servidores, Instâncias, Bancos de dados, Tabelas…)?
3. Ferramenta: Existem facets que atendem estas normas ou boas práticas, ou será necessária customização (comandos T-SQL ou WMI)?
4. Alternativa: Se for necessária uma customização, não existe uma alternativa (uma feature já presente no SQL Server ou feature de terceiros) que não exija esta customização? A atenção a este ponto pode evitar arrependimentos futuros.
5. Correção: O que deverá ser feito quando for encontrada uma situação que entre em desacordo com as regras estabelecidas? Em um segundo passo, defina os responsáveis e o prazo para as ações corretivas.
6. Exceções: Existem exceções? Quais? Cuidado, estas exceções podem se tornar a regra, então se a norma deve ser obedecida, exceções devem ser as mínimas possíveis e bem definidas (por que ela existe e quais os critérios para identificá-la?).

Para entender melhor como responder este checklist, segue abaixo alguns exemplos:

Cenário A:

Cenário B:

Nos cenários apresentados, todos apresentam possibilidades simples de adotar o PBM, e uma das vantagens permitidas por esta feature, é capacidade reutilizar estas regras em outros ambientes quando se fizer necessário, visto que elas podem ser exportadas e importadas por meio de arquivos XML.

Como? Implementação

Na criação de qualquer estrutura no PBM, não pode deixar de lado questões de nomenclatura e uma boa descrição das condições e regras criadas. Cenários robustos podem favorecer até mesmo na criação de regras que validam a nomenclatura e a existência de descrição das regras e condições do PBM, assim como versionamento destas regras.

Infelizmente, não existe uma solução de versionamento nativa para o PBM, mas é possível utilizar SharePoint Server, Visual Studio Team Foundation Server ou outra solução de SVN para manter um histórico dos arquivos XML das regras desenvolvidas.

Quando se pensar nas regras além de uma boa nomenclatura e descrição, é recomendado que estas sejam devidamente categorizadas, para o entendimento do que estas regras estão monitorando (servidor, banco de dados, tabelas, usuários…), saber quem será responsável por estas regras e onde/quando elas são aplicadas (verificações periódicas ou em tempo real, servidores/bancos de dados de clientes, ambientes de produção, de homologação ou de desenvolvimento).

A tabela abaixo, obtida de forma resumida do blog oficial de PBM, é possível identificar as facets que podem ser utilizadas de forma preventiva (de forma a evitar que alguma ação que burle a regra seja executada) ou que sejam “logáveis” quando sofrem alterações:

A partir dos cenários do tópico anterior e uma análise de como será implantado, é possível detalhar a implementação desta solução a partir de um checklist simples, como demonstrado abaixo:

Cenário A:

Cenário B:

Segurança

Após a implementação do PBM, algumas questões de segurança devem ser analisadas, visto que com um usuário com acesso indevido para criar/modificar regras e condições do PBM, é possível desde adulterar e burlar regras ou até mesmo criar regras e condições nocivas ao ambiente, dada a possibilidade executar comandos T-SQL por meio do PBM.

Assim, não permita que usuários indevidos se tornem membros da Database Role PolicyAdministratorRole do banco de dados msdb (role que permite a criação e edição de regras e condições do PBM), ou que sejam dadas permissões/senhas indevidas aos logins do PBM (##MS_PolicyEventProcessingLogin##, ##MS_PolicyTsqlExecutionLogin##).

Caso seja necessário executar alguma consulta que o PBM não tenha permissão, dê somente as permissões que se fizerem necessárias ao login ##MS_PolicyTsqlExecutionLogin##, que é o responsável pela execução de consultas T-SQL do PBM.

Acompanhamento

Depois de implantado o PBM, adequadas as questões de segurança, criadas as soluções de regras e condições de acordo com o que foi planejado, estabeleça com os responsáveis pelo ambiente, uma agenda para avaliar os resultados obtidos e identificar/analisar melhorias. Para orientar esta agenda, busque responder algumas perguntas, como:

• Esta solução realmente tem atendido adequadamente o que foi estabelecido e com o cenário atual? Caso não esteja atendendo adequadamente, verifique se a feature realmente respondeu as justificativas de sua implantação, pois o escopo da solução é facilmente esquecido com o tempo.
• A implementação desta feature trouxe algum problema para o cenário? Tanto processualmente e tecnicamente, a implementação do PBM pode criar problemas, como a geração de conflito com a cultura da organização ou complexidade da solução criar gargalos de desempenho ou conflitos com outras soluções.
• As regras e condições são seguidas corretamente? Caso não tenha sido acompanhado este ponto pelo responsável, é possível verificar pelo próprio PBM, alguma views ou até mesmo algumas ferramentas de terceiros (EPM Framework) se as regras e condições implementadas são seguidas corretamente e a evolução destas em determinado período. Se as regras e condições não são seguidas ou não houve correções no cenário, verifique se elas realmente estão de acordo com o cenário atual e se o processo de correção pode ser realizado de acordo com o planejado.
• Existem exceções se tornado regra? Exceções, como informado anteriormente, podem se tornar a regra, então se a norma deve ser obedecida, exceções devem ser as mínimas possíveis.
• Alguma melhoria para o cenário atual? Melhorias também devem responder as mesmas perguntas que definem a implantação da feature (“por que, o que, como”), pois adaptações mal planejadas podem tornar o cenário demasiadamente complexo em comparação ao retorno obtido e esperado, além de gerar problemas técnicos e/ou processuais em muitos casos.
• Alguma melhoria para o cenário futuro? Ao se pensar no cenário futuro, esteja atento ás estratégias da organização ou projeto, pois a escalabilidade na área de TI tem tendências mais reativas do que proativas quando se trata de estratégia, desta forma, metas da organização devem estar ao lado de métricas e metas de TI. Exemplo disso, uma organização que planeja triplicar o número de clientes em um ano, precisa que sua infraestrutura esteva preparada a este mesmo crescimento, como, servidores suportando o triplo dos dados, backups e transações que os atuais.
• Existe alguma solução ou mudança que possa justificar a substituição desta feature? Features também sofrem depreciação, como por exemplo, a feature utilizada na sua solução pode não estar presentes nas próximas versões do SQL Server ou podem surgir features que atendam de forma mais adequada o cenário. Procure deixar de lado um pouco do paternalismo para com as soluções que você e sua equipe implantou, para que se necessário sejam adotadas outras soluções que venha a substituir parcialmente ou completamente a feature atual.

Uma ferramenta muito útil para acompanhar a evolução do PBM é o EPM Framework, disponível gratuitamente no CodePlex (http://epmframework.codeplex.com), que permite relatórios bem úteis do dados históricos e correntes das regras do PBM:

Também é possível criar sua própria solução para acompanhar o PBM, utilizando as views do PBM (http://technet.microsoft.com/en-us/library/bb510742.aspx).

Em relação ao acompanhamento do PBM pela própria Microsoft, apesar de existir algumas sugestões de melhorias relacionadas a esta feature no Microsoft Connect, o CTP1 do SQL Server “Denali” (ou SQL Server 2011) não apresentou resposta a estas sugestões e também não foram divulgadas mudanças nesta feature. Mas pelo grande potencial desta feature, é até difícil pensar em melhorias que podem ser grandemente relevantes para futuras versões.

Conclusão

Este artigo tratou de permitir um entendimento de como por meio de um planejamento orientado por algumas perguntas e checklist, é possível adotar soluções com o a feature PBM, respondendo de forma adequada as necessidades do cenário, devidamente justificada, contando até mesmo com exceções e permitindo que a solução depois implantada não se torne uma feature órfã, por permitir melhorias e acompanhar a evolução e mudanças do cenário.

Com apoio de outras features nativas do SQL Server 2008+, como o Resource Governor e o Management Data Warehouse, é possível rapidamente aumentar o arsenal do administrador de banco de dados, oferecendo maior controle da disponibilidade dos recursos do SQL Server e relatórios para acompanhar da evolução dos cenários, tendo cada vez mais controle sobre o ambiente e recursos para entender como responder de forma adequada questões de disponibilidade, capacidade e continuidade dos cenários. Mas apesar da praticidade de adotar qualquer features, nunca deixe de lado o planejamento antes de adotá-las.

Este artigo permite o entendimento do potencial da integração da CLR com o SQL Server. Considerando o entendimento de como o SQL Server trabalha com a CLR para execução de rotinas desenvolvidas em C# e VB.NET, quando é recomendável e como utilizar desta integração para desenvolver rotinas, e por fim, como administrar o que foi desenvolvido.

Para que serve:

A integração CLR do SQL Server permite basicamente desenvolver funções, stored procedures, tipos e triggers com C# ou VB.NET. Sendo uma alternativa mais prática e ágil do que a linguagem T-SQL pode oferecer para o desenvolvimento. Além de contar com uma diversidade de recursos e possibilidades de novas soluções.

Em que situação o tema útil:

No momento em que as limitações da linguagem T-SQL se tornam um problema para o desenvolvimento de rotinas complexas, o .NET Framework vem permitir possibilidades, além uma diversidade de recursos que ampliam a capacidade de oferecer soluções.

Introdução:

Desde o lançamento do SQL Server 2005, se tornou possível utilizar C# ou VB.NET para criar stored procedures, functions, types e triggers. Esta possibilidade é denominada integração CLR, que consiste basicamente em utilizar bibliotecas desenvolvidas em C# ou VB.NET, para criar objetos em nível de banco de dados. Estas bibliotecas, conhecidas como assemblies, estão presentes no computador como arquivos na extensão DLL e são traduzidas em rotinas para o sistema operacional pela CLR (Common Language Runtime), um componente do .NET Framework.

Pelo fato de que estes assemblies são traduzidos, ou melhor, executados pela CLR, eles tambamém contam com todo o potencial da .NET Framework neste processo.

A .NET Framework é a principal plataforma de desenvolvimento da Microsoft, utilizada para criar aplicações em nível de sistema operacional, aplicações para internet, equipamentos móveis e outras soluções. O que faz do desenvolvimento de assemblies para o SQL Server somente uma das suas possibilidades desta plataforma.

No desenvolvimento com a .NET Framework, é possível contar com bibliotecas que permitem criar soluções que manipulam em nível de sistema operacional (manipulação de registros do sistema, diretivas de segurança, sistema de arquivos), manipular de dados em diversos formatos, trabalhar com vetores, fluxos de memória e realizar cálculos complexos, além de facilitar a interação com WebServices e protocolos em nível de rede.

Também é comum no desenvolvimento com a .NET Framework, a utilização de interfaces de desenvolvimento, como o Visual Studio, que facilita e agiliza todo o processo de desenvolvimento.

Em resumo, trabalhar com assemblies no SQL Sever não é somente de desenvolver objetos em nível de banco de dados com C# ou VB.NET ao invés de T-SQL, mas ter todo o potencial da .NET Framework para criar soluções.

Para permitir um bom entendimento do potencial desta feature, este artigo abordará como funciona a CLR e sua integração com o SQL Server, quando é recomendável utilizá-la, como desenvolver assemblies para o SQL Server e por último como administrar o que foi desenvolvido.

Como funciona a integração CLR?

No desenvolvimento de soluções com a .NET Framework, independentemente do tipo de solução, como aplicações Windows e Web, o compilador transforma o código de acordo com a linguagem utilizada (VB.NET, C#) em um assembly. Este assembly pode ser uma biblioteca (arquivo de extensão DLL) ou um arquivo executável (extensão EXE).

Conforme a Figura 1, o assembly é composto por duas estruturas básicas, sendo a primeira constituída de meta-dados (Metadata), que funcionam como um breve descritivo do código (namespaces, classes, métodos, propriedades, atributos e referências á outros assemblies), e a segunda camada, uma linguagem de alto-nível, denominada linguagem intermediária (IL ou MSIL), que é o código preparado para ser executado pela CLR.

Figura 1. Estruturas básicas que compõem um assembly.

Quando um assembly é adicionado a um banco de dados, assunto abordado nos tópicos seguintes, o SQL Server utilizará a camada de meta-dados para entender como este assembly é constituído. Neste processo de entender como o assembly é constituído, o SQL Server verificará se este assembly possui todas as dependências necessárias no banco de dados (como referências a outros assemblies) e se obedece a determinados critérios de segurança para ser executado.

Após o assembly ser adicionado ao banco de dados, será possível criar procedures, funções, triggers ou tipos a partir de referências a determinados métodos ou estruturas do assembly que possuam assinaturas compatíveis com o SQL Server. Estas assinaturas são definidas por atributos durante a fase de desenvolvimento (assunto a ser tratado nos próximos tópicos) e permitem definir quais os tipos de objetos que podem ser criados por meio destes métodos e estruturas.

Como relacionado pela Figura 2, as estruturas (structs) desenvolvidas em C# e VB.NET poderão receber assinaturas/atributos para a criação de tipos e funções de agregação, e os métodos, assinaturas/atributos para stored procedures, funções escalares ou tabulares e triggers.

Figura 2. Relação entre assinaturas/atributos do Assembly com objetos do banco de dados.

A execução de assemblies pela CLR é gerenciada pela .NET Framework em aspectos de segurança, memória, processamento e erros de execução.

Nos aspectos de segurança, um assembly poderá ser configurado como código seguro (SAFE), seguro com acesso a recursos externos (EXTERNAL_ACCESS) ou um código inseguro (UNSAFE). E a integração CLR interpretará esta configuração da seguinte forma:

SAFE: Rotinas que possuem restrição de acesso a recursos externos ao SQL Server e não referenciam assemblies com configuração de segurança diferente;

EXTERNAL_ACCESS: Rotinas que possuem restrição a códigos não-gerenciados, como objetos COM+ e assemblies UNSAFE, mas com a possibilidade de acessar recursos externos ao SQL Server, como arquivos, Web Services e serviços de e-mail SMTP;

UNSAFE: Rotinas que podem utilizar todo o potencial do .NET Framework, sem restrições de manipular até mesmo recursos e configurações do próprio sistema operacional, como registros, processamento, memória, drivers e objetos COM+.

Em relação à memória, o CLR possui uma ferramenta chamada Garbage Collector, que libera da memória as variáveis e objetos não utilizados durante a execução. Esta ação de liberar estas variáveis da memória permite que as rotinas utilizem somente a quantidade de memória necessária para serem executadas. Em contrapartida, esta ação pode exigir um pouco mais de processamento.

Diferente dos assemblies de aplicações tradicionais, os assemblies da integração CLR no SQL Server são gerenciadas pelo próprio SQL Server. Desta forma, cabe ao SQL Server controlar a demanda por recursos para execução dos assemblies para não comprometer a estabilidade do serviço de banco de dados.

Visto que se recursos utilizados por aplicações tradicionais são gerenciados pelo próprio sistema operacional, se da mesma maneira fosse com a integração CLR, a execução dos assemblies poderia concorrer por memória ou processamento com o próprio SQL Server, criando algum gargalo de desempenho e prejudicando o serviço de banco de dados.

Em relação aos erros de execução, como rotinas com laços de repetição infinitos (loops infinitos) e variáveis que recebem valores maiores que os suportados (overflow), a CLR possui total controle sobre estes erros, garantindo que, caso ocorram, seja possível identifica-los e trata-los programaticamente.

Quando é recomendável utilizar CLR ao invés de T-SQL?

Na época do SQL Server 2000, era comum usar objetos COM+ no SQL Server para acessar recursos externos, como Web Services, arquivos e pastas locais ou de rede, serviços de e-mail e informações internas do sistema operacional.

Também nesta época haviam muitas rotinas, denominadas Extended Stored Procedures, que faziam uso de código não-gerenciado (código sem a supervisão de um componente como a CLR, para ser executado). Visto que o uso de código não-gerenciado pode interferir tanto na estabilidade quanto na segurança do servidor, o surgimento do .NET Framework permitiu solucionar este problema.

Com o lançamento do .NET Framework 2.0 e a integração CLR do SQL Server 2005, muitas das Extended Stored Procedures nativas do SQL Server foram migradas para CLR, e foi recomendado que o que era desenvolvido anteriormente em código não-gerenciado fosse migrado para CLR.

Esta recomendação de utilizar CLR não foi somente válida para rotinas que fazem uso de COM+ e Extended Stored Procedures, mas também para rotinas T-SQL que possuem um alto grau de complexidade, como regras de negócio e cálculos matemáticos. Também considerando que rotinas complexas podem ser mais performáticas e manuteníveis com a utilização da CLR e linguagens já comuns ao ambiente de desenvolvimento, como VB.NET e C#.

No que se refere às regras de negócio e aos procedimentos que podem ser executados em nível de aplicação, recomenda-se que estes sejam executados em nível de aplicação. Pois mesmo que seja possível utilizar todo o potencial do .NET Framework dentro do SQL Server, não pode ser deixado de lado o papel do SQL Server como sistema de gerenciamento de banco de dados (SGBD) para torná-lo um hospedeiro para as aplicações. Considerando também que para manter a integridade e desempenho do acesso e manipulação de dados, os recursos do SQL Server serão concorridos para permitir a execução da CLR. Além do fato da CLR ser mais bem gerenciada pelo sistema operacional e por serviços dedicados para aplicação (como o IIS no caso das aplicações Web).

Quando for necessária a utilização de códigos não seguros no SQL Server, é recomendável avaliar se não é possível isolar este código em outro ambiente.

A Figura 3 demonstra um ambiente onde a utilização de códigos não seguros pode comprometer a segurança do banco de dados, dado o fato que eles exigem a criação de um assembly não seguro. Também considerando que o SQL Server pode ter sua estabilidade comprometida pelo fato de executar rotinas não seguras não gerenciadas pela CLR.

Figura 3. Exemplo de um ambiente que pode comprometer a segurança do SQL Server

Uma forma simples de isolar a execução de códigos não seguros é a utilização de Web Service, e códigos seguros com acesso a este Web Services.

A Figura 4 apresenta uma rotina que utiliza objetos COM+ intermediado por um Web Service, exigindo da integração CLR somente fazer a ponte entre o Web Service e o SQL Server.

Figura 4. Exemplo de um ambiente onde a execução de rotinas não seguras é isolada.

Também é interessante ressaltar que os Web Services não precisam ser desenvolvidos em VB.NET ou C# para ser compatíveis com a CLR, graças à diversidade de bibliotecas e recursos da .NET Framework para se comunicar com ambientes externos por meio de padrões universais, como XML, SOAP e RSS, ou padrões específicos, como ODBC, JSON e SMTP/POP3.

Na escolha entre T-SQL e CLR no desenvolvimento de novas rotinas, conforme ilustrado pela Figura 5, as abordagens com T-SQL permitem maior eficiência no acesso e manipulação de dados e recursos do próprio SQL Server. Por outro lado, a CLR permite maior eficiência no desenvolvimento de rotinas complexas ou que exijam acesso a recursos externos ao SQL Server.

Figura 5. Quando escolher uma abordagem T-SQL ou CLR.

Outro o fato a ser considerado na utilização da CLR, é que a utilização de assemblies pode ser limitada ou não permitida por alguns serviços de hospedagem de sites/banco de dados, serviços de virtualização, cloud computing e soluções de alta disponibilidade.

Como utilizar a integração CLR?

Compreendido como funciona e quando utilizar a integração CLR, o primeiro passo antes do desenvolvimento das rotinas com esta integração é verificar se algumas configurações em nível de SQL Server precisam ser realizadas.

A primeira configuração esta relacionada à integração CLR está habilitada. Para verificar se a integração CLR está habilitada por meio da stored procedure sp_configure.

A partir da execução da stored procedure sp_configure será possível obter as seguintes informações de uma determinada configuração:

name: o nome da configuração propriamente dita;

minimum e maximum: os valores mínimo e máximo possíveis desta configuração. No caso da integração CLR, o valor “0″ significa que ela está desabilitada e “1″ que ela está habilitada;

config_value: o valor da configuração antes de ser aplicado;

run_value: valor atual da configuração.

De acordo com os valores obtidos pela sp_configure, a Figura 6 demonstra uma situação onde da integração CLR está desabilitada.

Figura 6. Integração CLR desabilitada.

Um fato interessante é que mesmo com a integração CLR desabilitada, os tipos CLR nativos do SQL Server, como os tipos hierárquicos e espaciais (hierarchyid, geometry e geongraphy), funcionam sem problemas.

A Figura 7 demonstra como habilitar a integração CLR, primeiramente utilizando a função sp_configure seguida do nome da configuração e o valor desejado, alterando o valor config_value. Em um segundo passo, o comando RECONFIGURE é executado, aplicando o valor config_value sobre o run_value da configuração. Por fim, o comando sp_configure é executado novamente para verificar o valor atual da configuração – no caso, se a integração CLR foi habilitada com sucesso.

Figura 7. Habilitando a integração CLR.

Ao se pensar em códigos seguros com acesso externo ou códigos não seguros, a propriedade Trustworthy (is_trustworthy_on) do banco de dados determinará se estes códigos podem ser utilizados. A Figura 8 demonstra uma situação onde o banco de dados não permite estes tipos de código.

Figura 8. A propriedade Trustworthy desabilitada em um determinado banco de dados.

Além de utilizar códigos seguros com acesso externo ou códigos não seguros em um determinado banco de dados, a propriedade Trustworthy permitirá outras questões de segurança que não se limitam à integração CLR.

Para entender melhor os impactos de habilitar esta propriedade, e se ela está adequada ao seu ambiente, consulte o link: http://msdn.microsoft.com/en-us/library/ms187861.aspx .

A Figura 9 demonstra como habilitar a propriedade Trustworthy de um determinado banco de dados.

Figura 9. Habilitando a propriedade Trustworthy.

Terminadas as configurações no nível de banco de dados, será possível utilizar o Visual Studio como ferramenta para desenvolver, com C# ou VB.NET, assemblies para o SQL Server.

O primeiro passo para desenvolver assemblies para o SQL Server com o Visual Studio é criar um projeto a partir do template Visual C# SQL CLR Database Project, como apresenta a Figura 10.

Figura 10. Criando um projeto SQL CLR no Visual Studio 2010.

Depois de criado o projeto, aparecerá uma tela para referenciar o banco de dados onde os códigos serão instalados e executados (veja a Figura 11). Assim, adicione uma nova referência ou utilize uma já existente.

Figura 11. Adicionando uma referencia a um determinado banco de dados ao projeto.

Ao referenciar o banco de dados ao projeto, evite trabalhar diretamente com ambientes de produção, para não causar problemas de desempenho quando forem executados os testes dos códigos em desenvolvimento.

Após referenciado o banco de dados, outra tela que aparecerá, conforme a Figura 12, possibilitando habilitar a depuração do código em tempo de execução (Debug). O Debug nada mais é que permitir, durante a execução do projeto no Visual Studio, acompanhar a execução no SQL Server.

Figura 12. Habilitando o Debug no projeto.

Também não será possível utilizar o Debug se o login utilizado para referenciar o banco de dados não possuir as permissões necessários, no caso logins com permissão de “sysadmin”.

Terminadas as configurações do projeto, na janela Solution Explorer será possível ver a estrutura do projeto SQL CLR (Figura 13).

Os projetos SQL CLR possuem inicialmente scripts de PreDeploy e PostDeploy (executados antes e depois da publicação do projeto) e um script de Test, que poderá ser utilizado durante o Debug. No script de Test é possível informar alguns procedimentos para testar se as funcionalidades do assembly durante o Debug.

Figura 13. Estrutura inicial do projeto SQL CLR.

Clicando com o botão direito sobre o projeto é possível adicionar um novo item, como pode ser visto na Figura 14. Este novo item nada mais é do que um arquivo contendo alguma espécie de código, normalmente em C#, VB.NET ou T-SQL.

Figura 14. Adicionando um novo item ao projeto

Este código poderá ser criado a partir de templates, evitando a necessidade de iniciar o código de um arquivo vazio, facilitando assim o desenvolvimento. Conforme a Figura 15, os templates disponíveis para criação deste código.

Para um primeiro exemplo, será utilizado o template User-Defined Function (ou função escalar) para desenvolver uma função de pouca complexidade, no caso o cálculo de fatoriais.

Figura 15. Templates disponíveis para ao projeto SQL CLR

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString GetFatorialCLR()
    {
        // Put your code here
        return new SqlString("Hello");
    }
};

Listagem 1. Template de uma função escalar.

A Listagem 1 traz o template inicial de uma função escalar em C#. Esta função é basicamente um método que retorna ao SQL Server o texto “Hello”. O SQL Server entenderá que este método se trata de uma função escalar por causa do atributo/assinatura Microsoft.SqlServer.Server.SqlFunction e adotará o nome da função como GetFatorialCLR() por ser este nome do método.

Para entender o funcionamento desta função, clique com o botão direito sobre o projeto, e conforme a Figura 16, utilize a opção Deploy para instalar este assembly no banco de dados referenciado na criação do projeto.

Figura 16. Executando o Deploy de um projeto SQL CLR

Se a instalação do assembly for realizada com sucesso, será possível obter mensagens de sucesso na janela Output do Visual Studio, conforme a Figura 17.

Figura 17. Mensagens sucesso de uma instalação bem sucedida.

Após a instalação do assembly, abra o SQL Server Management Studio e se conecte ao banco de dados onde este assembly foi instalado. Por fim, execute a função GetFatorialCLR(), como demonstrado na Figura 18.

Figura 18. Resultado da execução da função GetFatorialCLR.

Visto que a publicação ocorreu com sucesso e a função está funcionando, será possível customizar a função ao ponto de ser possível obter o resultado desejado, no caso, o cálculo da fatorial, conforme a Listagem 2.

using System;
using System.Data.SqlTypes;
using System.Linq;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [SqlFunction(Name = "GetFatorialCLR")]
    public static SqlDouble GetFatorialCLR(SqlInt32 Value)
    {
        if (Value.IsNull || Value <= 1) return 1;

        var fatorial = Enumerable.Range(1, Value.Value)
            .Select(i => (double)i)
            .Aggregate((i, j) => i * j);

        return double.IsInfinity(fatorial) ? SqlDouble.Null : fatorial;
    }
};

Listagem 2. Código da função GetFatorialGLR() ajustado para recuperar o fatorial.

Após instalar o assembly novamente pelo Visual Studio, será possível obter como resultado da função GetFatorialCLR(), o valor de um determinado fatorial.

Figura 19. Resultado da execução da função GetFatorialCLR após os ajustes.

Como com o CLR existe maior controle sobre valores numéricos que o T-SQL, é possível tratar valores decimais que sofreram alguma forma de overflow (quando uma variável recebe um valor que ela não pode suportar).

Como exemplo de overflow, o fatorial do número 171 resulta em um valor que ultrapassa o suportado pelo tipo float do SQL Server, e por meio da CLR será possível tratar este valor antes de ser submetido ao SQL Server e gere alguma forma de erro. Conforme a Listagem 2, foi utilizado o método IsInfinity() do tipo double da CLR para verificar se houve ou não overflow, e retornar um valor fixo (como NULL), resultado demonstrado pela Figura 20.

Figura 20. Resultado da execução da função GetFatorialCLR() em uma situação de overflow

Graças às diversidades das bibliotecas do .NET Framework, principalmente às nativas da plataforma, será possível criar funcionalidades mais complexas do que as permitidas com o simples T-SQL, como criar uma função que utiliza expressões regulares (Regex).

A Listagem 3 demonstra um código que utiliza Regex para remover determinados elementos de um texto, conforme o resultado da Figura 21.

using System;
using System.Data.SqlTypes;
using System.Linq;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [SqlFunction(Name = "RegexReplaceCLR")]
    [return: SqlFacet(MaxSize = -1)]
    public static SqlString RegexReplaceCLR(
        [SqlFacet(MaxSize = -1)]SqlString Input,
        [SqlFacet(MaxSize = 50)]SqlString Pattern,
        [SqlFacet(MaxSize = 50)]SqlString Replacement)
    {
        if (Input.IsNull || Pattern.IsNull || Replacement.IsNull) return null;

        var regEx = new Regex(Pattern.Value);
        return regEx.Replace(Input.Value, Replacement.Value);
    }
};

Listagem 3. Código de uma função para substituir caracteres por meio de uma expressão regular.

Figura 21. Resultado da execução da função para substituir caracteres por meio de uma expressão regular.

Verifique que na Listagem 3 foi utilizado o atributo SqlFacet, que possui basicamente a função de definir como a integração CLR interpretará os valores recebidos e retornados do SQL Server. Sendo uma utilização comum deste atributo definir o tamanho das variáveis. Já que por padrão as variáveis do tipo SqlString resultam em um varchar(8000), com a configuração do atributo SqlFacet, estas variáveis poderão variar de um tamanho fixo á varchar(max), no caso “MaxSize=-1″.

Outra possibilidade interessante da CLR é criar funções tabulares para obter sequência de valores sem a necessidade de utilizar muitas linhas de código ou lógicas complexas com tabelas temporárias ou CTE.

O principal diferencial das funções tabulares (que retornam resultado em formato de “tabela”) e as funções escalares (que retornam um único valor), é o uso de um método auxiliar para formatação dos valores, referenciado pela propriedade FillRowMethodName (exemplo, Int32Fill) e a definição da estrutura do resultado por meio da propriedade TableDefinition.

As Listagem 4 e Figura 22, demonstram o código e a execução de uma função tabular.

using System;
using System.Collections;
using System.Data.SqlTypes;
using System.Linq;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [SqlFunction(Name = "RangeCLR"
        , FillRowMethodName = "Int32Fill"
        , TableDefinition = "Value INT")]
    public static IEnumerable RangeCLR(SqlInt32 Value)
    {
        return Value.IsNull || Value < 1
            ? null
            : Enumerable.Range(1, Value.Value);
    }

    public static void Int32Fill(object obj, out SqlInt32 value)
    {
        value = (int)obj;
    }
};

Listagem 4. Exemplo de função tabular que retorna uma sequencia de valores iniciando de 1 até um valor informado.

Figura 22. Resultado da função tabular que retorna uma sequencia de valores iniciando de 1 até um valor informado.

Também é possível desenvolver funcionalidades que podem ser desenvolvidas de maneiras mais simples com CLR, como funções de validação de CPF e CNPJ.

Outra funcionalidade que pode justificar a utilização de CLR é a possibilidade de criar funções de agregação, como recuperar o primeiro ou último registro de uma agregação ou concatenar textos.

Na Figura 23 e Listagem 5, uma função de agregação desenvolvida em CLR, que possui resultado semelhante à função COUNT nativa do SQL Server, permitindo customizações para o desenvolvimento de soluções mais específicas.

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[SqlUserDefinedAggregate(Format.Native)]
public struct CountCLR
{
    public SqlInt32 Ticks { get; set; }

    public void Init() { Ticks = 0; }

    public void Accumulate(SqlInt32 Value) { Ticks += 1; }

    public void Merge(CountCLR Group) { if (!Group.Ticks.IsNull) Ticks += Group.Ticks; }

    public SqlInt32 Terminate() { return Ticks; }
}

Listagem 5. Exemplo de função de agregação semelhante à função COUNT.

Figura 23. Execução da função COUNT e da função de agregação desenvolvida em CLR.

Tanto as funções de agregação, quanto os tipos CLR são desenvolvidos por meio de uma struct serializável. Isso quer dizer que os valores durante são armazenados em memória, e no caso dos tipos CLR, também em disco, no formado binário. Este formato binário e até mesmo a forma de serialização podem ser customizadas, ou para armazenar até o limite de um tipo varbinary(max) a partir do SQL Server 2008 (ou varbinary(8000) no SQL Server 2005) ou até mesmo, se realmente for necessário, expandir este armazenamento de forma distribuída (Web Services, objetos COM ou arquivos).

Ao término do processo de desenvolvimento de sua solução CLR, passando para a instalação do assembly em um servidor de produção, serão necessários alguns ajustes das configurações no projeto.

Então, clique com o botão direito sobre o projeto e abra as propriedades do projeto.

Conforme a Figura 24, altere a referência do banco de dados para o banco de dados de produção na aba Database, propriedade Connection String, botão Browse….

Figura 24. Propriedades de um projeto SQL CLR, aba Database.

Nesta aba, também será possível ajustar os aspectos de segurança do Assembly (SAFE, EXTERNAL e UNSAFE) e o dono (owner) deste Assembly.

Ajustadas as referências ao banco de dados de produção, altere o modo de compilação do Visual Studio para Release, conforme a Figura 25. Por fim, execute o Deploy do projeto, de acordo com a Figura 26.

Figura 25. Ajustando o modelo de compilação do Visual Studio.

Figura 26. Executando o Deploy do projeto após as configurações para o ambiente de produção

A Figura 27 demonstra que é possível encontrar no banco de dados o assembly após a instalação, assim como as stored procedures, funções, tipos e triggers desenvolvidas por meio deste.

Figura 27. Assembly e objetos CLR publicados no SQL Server.

A partir de algumas catalog views, será possível obter informações dos assemblies no SQL Server, conforme relacionadas na Tabela 1.

Tabela 1. Relação de catalog views que possuem informações sobre assemblies no SQL Server.

A Figura 28 segue com uma demonstração de como acompanhar a execução de seus assemblies.

Figura 28. Modelo de código para acompanhar a execução de assemblies de um determinado banco de dados.

Conclusão

Este artigo permitiu uma visão geral do desenvolvimento com a integração CLR. Considerando o entendimento de como o SQL Server trabalha com a CLR para execução de rotinas desenvolvidas em C# e VB.NET, quando é recomendável e como utilizar desta integração para desenvolver rotinas, e por fim, como administrar o que foi desenvolvido.

Mesmo entendendo como a integração CLR funciona e conhecendo seu potencial, é importante estar ciente de quando desenvolver funcionalidades. Mas não deixando de lado, a necessidade de realizar testes unitários e de stress para evitar problemas em ambientes de produção e conhecer os limites da utilização do que foi desenvolvido.

Por se tratar de uma funcionalidade que combina várias tecnologias em evolução, a cada versão do SQL Server, do Visual Studio e do .NET Framework, mais recursos são disponibilizados. Recursos estes, que facilitam o desenvolvimento e ampliam o leque de possibilidades para novas soluções. Sendo uma das melhorias esperadas para o SQL Server 11 “Denali”, a integração com o .NET Framework 4.

Gerando XML no SQL Server – Arte do FOR XML AUTO

Gerando XML no SQL Server – Arte do FOR XML EXPLICIT

Gerando XML no SQL Server – Arte do FOR XML RAW

Gerando XML no SQL Server – Arte do FOR XML PATH

Imagens no banco de dados 1 – ASP.NET Web Forms

Imagens no banco de dados 2 – ASP.NET MVC

Imagens no banco de dados 3 – Windows Forms

Imagens no banco de dados 4.1 – Relatórios em RDLC

Imagens no banco de dados 4.2 – Relatórios em RDLC

Imagens no banco de dados 5 – Importando arquivos por T-SQL

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

Spatial Data: STGeomFromText – Árvore de Natal

Spatial Data: Envolvendo e cercando polígonos

Spatial Data: Desmembrando Polígonos!?

Spatial Data: Uniões e Intersecções

E por último uma mescla de Spatial Data e SQL CLR:

Trabalhando com SQL CLR e Spatial Data

Primeiramente os artigos em C#:

Gerenciando o SQL Server por meio de aplicações .NET – Primeiros passos

Gerando backups do SQL Server por .NET

Gerando scripts do banco de dados por .NET – Passo 1

Gerando scripts do banco de dados por .NET – Passo 2

Criando e manipulando Tabelas e Colunas no SQL Server com .NET

Criando Constraints e Índices no SQL Server com .NET

Capturando consultas executadas no SQL Server (Trace) com .NET via SMO

Criando um arquivo de trace com .NET via SMO

Capturando eventos do SQL Server com .NET via SMO sem Triggers ou Trace

Capturando eventos de um determinado bancos de dados com .NET via SMO sem Triggers ou Trace

Monitorando Deadlocks do SQL Server com SMO

E os artigos que traduzi para PowerShell:

Gerando backups do SQL Server por PowerShell

Gerando scripts do banco de dados por PowerShell – Passo 1

Gerando scripts do banco de dados por PowerShell – Passo 2

Criando e manipulando Tabelas e Colunas no SQL Server com PowerShell

Criando Constraints e Índices no SQL Server com PowerShell

Capturando consultas executadas no SQL Server (Trace) com PowerShell via SMO

Criando um arquivo de trace com PowerShell via SMO

De uma maneira bem simples, o código abaixo monitora os eventos de Deadlock de uma determinada instancia do SQL Server, e quando ocorre uma Deadlock, ele informa os SPID que estão relacionadas a este evento, e gera um Deadlock File para obter maiores detalhes sobre o deadlock.

using System;
using System.IO;
using System.Linq;
using System.Xml.Linq;
using Microsoft.SqlServer.Management.Smo;

namespace DemoSmo
{
    static class DemoEvent3
    {
        public static void Run()
        {
            const string serverName = @".\SQLEXPRESS";

            var server = new Server(serverName);

            var eventSet = new ServerTraceEventSet(
                ServerTraceEvent.LockDeadlock,
                ServerTraceEvent.LockDeadlockChain,
                ServerTraceEvent.DeadlockGraph
                );

            server.Events.SubscribeToEvents(eventSet, OnDeadlock);

            server.Events.StartEvents();

            Console.ReadKey();

            server.Events.StopEvents();

            server.Events.UnsubscribeFromEvents(eventSet);
        }

        private static void OnDeadlock(object sender, ServerEventArgs e)
        {
            switch (e.EventType)
            {
                case EventType.DeadlockGraph:
                    DeadlockGraph(e);
                    break;
                case EventType.LockDeadlockChain:
                    LockDeadlockChain(e);
                    break;
                case EventType.LockDeadlock:
                    Console.WriteLine("Data/Hora: {0:dd/MM/yyyy HH:mm}", e.PostTime);
                    Console.WriteLine("Vítima escolhida: SPID {0}", e.Spid);
                    break;
            }

            Console.WriteLine();
        }

        private static void LockDeadlockChain(ServerEventArgs e)
        {
            var textData = e.Properties.FirstOrDefault(prop => prop.Name == "TextData");

            if (textData == null) return;

            Console.WriteLine("Data/Hora: {0:dd/MM/yyyy HH:mm}", e.PostTime);
            Console.WriteLine(((string)textData.Value).TrimEnd());
        }

        private static void DeadlockGraph(ServerEventArgs e)
        {
            var path = string.Format(@"C:\TEMP\{0}.xdl", Guid.NewGuid());

            var textData = e.Properties.FirstOrDefault(prop => prop.Name == "TextData");

            if (textData == null) return;

            var xml = XDocument.Parse(((string)textData.Value))
                .Elements("TextData")
                .Elements("deadlock-list")
                .FirstOrDefault();

            if (xml == null) return;

            using (var file = new StreamWriter(path)) file.Write(xml.ToString(SaveOptions.DisableFormatting));

            Console.WriteLine("Data/Hora: {0:dd/MM/yyyy HH:mm}", e.PostTime);
            Console.WriteLine("Arquivo gerado: {0}", path);
        }
    }
}

A execução do código:

Os arquivos gerados:

E um arquivo de deadlock file gerado pelo SMO aberto no SQL Server Management Studio, possuindo informações sobre os processos, os objetos em lock e a query que foi executada.

Assim terminamos os artigos sobre SMO, espero que tenham gostado de conhecer um pouco mais sobre esta feature.

No nível de transações de banco de dados não é muito diferente, teremos duas tabelas em nosso banco de dados:

CREATE TABLE Pagamentos (ID INT PRIMARY KEY)
GO
CREATE TABLE Contas (ID INT PRIMARY KEY)
GO

Agora, imagine que um cliente, o senhor 52, irá abrir uma transação para inserir um registro na tabela de pagamentos:

BEGIN TRAN
GO
INSERT INTO Pagamentos VALUES(1)
GO

E ao mesmo tempo, outro cliente, o senhor 55, irá inserir um registro na tabela de contas:

BEGIN TRAN
GO
INSERT INTO Contas VALUES(1)
GO

Até ai, tudo bem, as transações estão abertas, o senhor 55 e o senhor 52 estão utilizando tabelas totalmente diferentes. Mas o senhor 55 lembra que além de inserir um registro na tabela de contas, ele precisa inserir um registro na tabela de pagamentos na mesma transação, assim ele terá que esperar que o senhor 52 termine a transação dele primeiro.

INSERT INTO Pagamentos VALUES(1)
GO

Por enquanto nada crítico, é só um cliente esperando o outro terminar.

Mas o senhor 52 também inventa de querer inserir um registro na tabela de Contas:

INSERT INTO Contas VALUES(1)
GO

Dai acontece o Deadlock, o senhor 52 esperando o senhor 55 terminar a transação dele, e o senhor 55 esperando o senhor 52. Como se trata de uma Deadlock bem simples, o SQL Server vai rapidamente escolher qual dos dois clientes tem a preferência, e por consequência definirá sua vítima, que é o cliente que terá que morrer para que aquele que possui preferência consiga terminar seu processo.

E assim, no meu cenário, senhor 52 foi brutalmente assassinado (a transação sofre um ROLLBACK pelo próprio SQL Server, de forma que todos os rastros da vítima sejam apagados):

Msg 1205, Level 13, State 47, Line 4
Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

E em homenagem a todos os processos que diariamente são vítimas de Deadlock:

|| EDIT 2012-03-29
|| Via: http://pessoalex.wordpress.com/2012/03/29/exemplo-de-deadlock-no-sql-server/

Quem viu o artigo da semana passada irá perceber que não há muita diferença para capturar eventos de objetos específicos do SQL Server, pois as diferenças básicas são os objetos aos quais se associam os eventos (eventos de banco de dados, se associa a um objeto Database, eventos de instancia, a um objeto Server) e o grupo de eventos (EventSet) a ser utilizado para identificar quais os eventos que serão monitorados.

No exemplo a seguir veremos como implementar a captura de eventos de um determinado banco de dados:

using System;
using System.Linq;
using System.Xml.Linq;
using Microsoft.SqlServer.Management.Smo;

namespace DemoSmo
{
    static class DemoEvent2
    {
        public static void Run()
        {
            // Definindo um grupo de eventos a serem monitorados
            var dbEventSet = new DatabaseEventSet(DatabaseEvent.CreateTable);

            // Relacionando os eventos de um determinado banco de dados
            //   e definindo o método OnCreate para o tratamento destes eventos
            const string serverName = @".\SQLEXPRESS";

            var server = new Server(serverName);

            #region Criando um banco de dados

            var db1 = new Database(server, "NovoBanco");

            db1.Create();

            #endregion

            var database = server.Databases["NovoBanco"];

            database.Events.SubscribeToEvents(dbEventSet, OnCreate);

            // Iniciando a captura dos eventos
            database.Events.StartEvents();

            #region Criando uma tabela no banco de dados criado

            Console.ReadKey();

            var table1 = new Table(db1, "NovaTabela", "dbo");

            table1.Columns.Add(new Column(table1, "Codigo", DataType.Int) { Nullable = false });

            table1.Create();

            #endregion

            // Terminando a captura dos eventos

            Console.ReadKey();

            database.Events.StopEvents();

            database.Events.UnsubscribeFromEvents(dbEventSet);

            #region Excluindo o banco de dados criado

            db1.Drop();

            #endregion
        }

        private static void OnCreate(object sender, ServerEventArgs e)
        {
            // Exibindo o SPID e a data do evento
            Console.WriteLine("SPID: {0}", e.Spid);
            Console.WriteLine("Data: {0:dd/MM/yyyy HH:mm}", e.PostTime);

            // Exibindo informações sobre o que foi executado
            Console.WriteLine("Foi criada uma tabela!");

            // Relacionando outras propriedades que serão exibidas

            var showProperties = new[] { "DatabaseName", "LoginName" };

            var properties = e.Properties.Where(prop => showProperties.Contains(prop.Name));

            foreach (var prop in properties)
            {
                Console.WriteLine("{0}: {1}", prop.Name, prop.Value);
            }

            // Exibindo o comando que acionou o evento

            var command = e.Properties.FirstOrDefault(prop => prop.Name == "TSQLCommand");

            if (command != null)
            {
                var xml = XDocument.Parse((string)command.Value)
                    .Elements("TSQLCommand")
                    .Elements("CommandText")
                    .FirstOrDefault();

                if (xml != null)
                {
                    Console.WriteLine("CommandText: {0}", xml.Value);
                }
            }

            Console.WriteLine();
        }
    }
}

E como resultado deste exemplo:

Aqueles que gostariam de saber como funciona a implementação de eventos de notificação, podem encontrar no link abaixo referências de como implementá-los com T-SQL, no qual conhecimento de Service Broker será indispensável:
http://msdn.microsoft.com/en-us/library/ms178080.aspx

Neste exemplo, veremos como capturar/monitorar eventos de criação de banco de dados e de tabelas com o SMO:

using System;
using System.Linq;
using System.Xml.Linq;
using Microsoft.SqlServer.Management.Smo;

namespace DemoSmo
{
    static class DemoEvent1
    {
        public static void Run()
        {
            // Definindo um grupo de eventos a serem monitorados
            var eventSet = new ServerEventSet(ServerEvent.CreateDatabase, ServerEvent.CreateTable);

            // Relacionando os eventos a uma instância do SQL Server
            //   e definindo o método OnCreate para o tratamento destes eventos
            const string serverName = @".\SQLEXPRESS";

            var server = new Server(serverName);

            server.Events.SubscribeToEvents(eventSet, OnCreate);

            // Iniciando a captura dos eventos
            server.Events.StartEvents();

            Console.ReadKey();

            // Executando alguns comandos para testar se os eventos estão sendo monitorados

            const string query = @"
USE [master]
GO

CREATE DATABASE [NovoBanco]
GO

USE [NovoBanco]

CREATE TABLE [NovaTabela] (CODIGO INT)
GO

USE [master]
GO

DROP DATABASE [NovoBanco]
GO";

            server.ConnectionContext.ExecuteNonQuery(query);

            // Terminando a captura dos eventos

            Console.ReadKey();

            server.Events.StopEvents();

            server.Events.UnsubscribeFromEvents(eventSet);
        }

        private static void OnCreate(object sender, ServerEventArgs e)
        {
            // Exibindo o SPID e a data do evento
            Console.WriteLine("SPID: {0}", e.Spid);
            Console.WriteLine("Data: {0:dd/MM/yyyy HH:mm}", e.PostTime);

            // Exibindo informações sobre o que foi executado
            switch (e.EventType)
            {
                case EventType.CreateDatabase:
                    Console.WriteLine("Foi criado um banco de dados!");
                    break;
                case EventType.CreateTable:
                    Console.WriteLine("Foi criada uma tabela!");
                    break;
            }

            // Relacionando outras propriedades que serão exibidas

            var showProperties = new[] { "DatabaseName", "TableName", "LoginName" };

            var properties = e.Properties.Where(prop => showProperties.Contains(prop.Name));

            foreach (var prop in properties)
            {
                Console.WriteLine("{0}: {1}", prop.Name, prop.Value);
            }

            // Exibindo o comando que acionou o evento

            var command = e.Properties.FirstOrDefault(prop => prop.Name == "TSQLCommand");

            if (command != null)
            {
                var xml = XDocument.Parse((string)command.Value)
                    .Elements("TSQLCommand")
                    .Elements("CommandText")
                    .FirstOrDefault();

                if (xml != null)
                {
                    Console.WriteLine("CommandText: {0}", xml.Value);
                }
            }

            Console.WriteLine();
        }
    }
}

E como os eventos serão exibidos neste exemplo:

Próxima semana, eu pretendo demonstrar como capturar/monitorar eventos em um nível mais específico, e na última semana deste mês, veremos como monitorar alguns eventos que vão além de alterações estruturais do banco de dados.