Introdução ao SQL CLR (Common Language Runtime) no SQL Server

Olá pessoal,
Bom dia!

Neste post, que será o número 100 do blog, eu gostaria de falar sobre algo que eu particularmente gosto muito no SQL Server, que é a criação de rotinas .NET dentro do banco de dados SQL Server. Sim, estamos falando a CLR (Common Language Runtime).

Introdução

Clique aqui para visualizar
Presente no banco de dados SQL Server desde a versão 2005, o CLR é o coração das linguagens da plataforma Microsoft .NET Framework. O código que é executado dentro do CLR é conhecido como código gerenciado. O CLR fornece várias funções e serviços necessários para a execução do programa, incluindo a compilação just-in-time (JIT), alocação e gerenciamento de memória, imposição de segurança de tipo, manipulação de exceção, gerenciamento de segmento e segurança..

Em outras palavras, o CLR permite que você consiga criar rotinas (stored prodecures, functions, triggers, etc) escritas em C#, F# e VB.NET, compilá-las e executá-las no banco de dados nativamente, estendendo as capacidades do SGBD, pois é possível criar uma infinidade de coisas que não seriam possíveis utilizando apenas o Transact-SQL, como manipulação de arquivos, upload e download de arquivos via FTP, funções de agregação, integração com Webservices e muito mais.

Vantagens e desvantagens

Clique aqui para visualizar
Assim como qualquer tecnologia e ferramenta, sempre existem pontos fortes e fracos, onde deve-se ou não utilizar o CLR. Vou listar alguns:

Vantagens do SQL CLR

  • Possibilidade de criação de novos recursos, que não seriam possíveis utilizando apenas T-SQL
  • Possibilidade de trabalhar com expressões regulares (RegExp) no banco de dados
  • Otimização de performance: Uma mesma função escrita em C# no CLR geralmente é executada bem mais rápido que uma função T-SQL, principalmente em casos de looping e cálculos, uma vez que o compilador do .NET é especializado pra esse tipo de operação, enquanto o do T-SQL é para trabalhar com conjuntos. No meu trabalho, já vi vários casos em que a mesma função CLR executou 5x, 10x e algumas até 60x mais rápido que a função T-SQL
  • Integração com Webservices via banco de dados
  • Segurança: Uma coisa legal que gosto no CLR, é que podemos definir um usuário fixo para a conexão com o banco de dados. Desta forma, podemos liberar acesso pra ele executar SP’s e consultar views e tabelas de sistema e criar funções e SP’s no CLR para isso. Quando um analista precisar utilizar essas SP’s de sistema, basta liberar acesso na SP/View/Function do CLR que terá acesso indireto ao objeto de sistema, sem precisar liberar acesso pra ele no objeto de origem ou criar objetos em bancos de sistema
  • Ferramentas de desenvolvimento: A ferramenta utilizada para desenvolver rotinas do CLR é o Visual Studio. O Management Studio é uma IDE muito boa para criação de rotinas Transact-SQL, principalmente como SQL Prompt instalado, mas não se compara o poderoso Visual Studio, principalmente com o ReSharper. A programação é muito mais rápida e prática.
  • Versionamento de código-fonte: Por estarmos utilizando o Visual Studio, o código-fonte pode ser facilmente controlado e gerenciado pelo TFS (Team Foundation Server), dando total controle aos códigos criados, ao contrário de Stored Procedures no banco de dados, que não possuem controles como Merge, Diff, etc
  • Substituição do xp_cmdshell: Apesar de vir por padrão, desabilitado, muitas pessoas e empresas acabam habilitando o cmdshell em suas instâncias, até mesmo em produção, em virtude que algumas operações não são possíveis de serem realizadas utilizando apenas Transact-SQL, como manipulação de arquivos, por exemplo. Esse recurso é um grande perigo, uma vez que ele simplesmente executa qualquer comando que é enviado para ele, sem qualquer filtro ou restrição. Para isso, recomendo desabilitar esse recurso e a utilização de procedures CLR destinadas exclusivamente para cada finalidade, seja ela uma cópia de arquivos ou até subir uma instância.
  • Substituição de OLE Automation: Recurso ainda bastante utilizado e que também vem desabilitado por padrão, procedures OLE Automation são bibliotecas em C++ que permitem utilizar API’s do Windows para realizar diversas operações, como manipulação de arquivos, etc. O grande problema, é que ao habilitar esse recurso, qualquer usuário pode criar qualquer coisa com isso, aliado ao fato que os comandos não são gerenciados e são executados dentro do processo do SQL Server. Em caso de falha, a instância é desligada, pois o processo do SQL Server é fechado automaticamente pelo sistema operacional (!!!!)
  • Automação: Com as procedures CLR, você pode automatizar uma infinidade de processos do dia a dia, que antes só poderiam ser automatizados utilizando o Integration Services, que é uma ótima ferramenta, mas acaba se tornando um pouco limitada diante do mundo de possibilidades do CLR, uma vez que no MSIS você conta apenas com os recursos que ferramentas lhe disponibiliza, enquanto no CLR você pode criar qualquer coisa que a plataforma .NET possibilita. Além disso, o resultado do CLR são objetos de bancos de dados, sejam Stored Prodecures, Functions, Triggers, etc, podendo ser utilizados livremente em outras SP’s, Jobs, e qualquer outro objeto do banco, enquanto os Packages só podem ser executados pela ferramenta ou por Jobs. (Obs: CLR E MSIS são ferramentas com objetivos distintos, apenas comparei pois algumas tarefas do Integration Services podem ser substituídas facilmente pelo CLR)
  • Conectividade: Possibilidade de utilizar os conectores do .NET Framework e acessar outros SGBD’s e outras instâncias com link direto, sem a necessidade de LinkedServer, que executa o comanndo remotamente

Desvantagens do SQL CLR

  • Necessidade de conhecimento em SQL E linguagem de programação (C#, F# ou VB.net)
  • Pouca documentação e pessoas com conhecimento sobre o assunto
  • Ao publicar uma nova versão, os objetos são removidos e recriados, perdendo as permissões e deixando os objetos indisponíveis durante a publicação
  • Caso seja mal desenvolvido e implementado, pode apresentar riscos para o SGBD
  • Algumas funções podem necessitar de um alto volume de CPU para processamento
  • Não existem parâmetros opcionais para procedures. Todos devem ser preenchidos

Habilitando o CLR na sua instância SQL Server

Clique aqui para visualizar
Por padrão, o CLR vem desabilitado nas instâncias SQL Server. Para habilitar este recurso é bem simples, basta utilizar o comando sp_configure:

E teremos o seguinte resultado:
Habilitando CLR no SQL Server

Criando seu primeiro projeto SQL CLR no Visual Studio

Clique aqui para visualizar
O primeiro passo para criar o nosso projeto CLR é obviamente, baixar e instalar o Visual Studio compatível com a sua versão do SQL Server (recomendo o Visual Studio 2015 Community Edition, pois ele é compatível com o SQL Server 2005 até o 2014 e grátis para desenvolvedores).

Após isso, abra o Visual Studio e acesso o menu File > New > Project. Selecione o tipo de projeto SQL Server > SQL Server Database Project

New CLR Project

E deverá ficar assim inicialmente:
CLR novo projeto - Solution explorer

Agora vamos adicionar um novo projeto do tipo Class Library, que irá conter os nossos códigos C#. Para isso, clique com o botão direito na Solution e selecione a opção Add > New Project. Selecione a categoria Visual C# > Windows > Class Library

CLR New Class Library

Após criada, eu geralmente costumo criar diretórios por tipo de objeto na Class Library para melhor organização do código. Isso é recomendável, mas opcional. Você pode querer organizar seu código por assunto ou conforme sua necessidade.

Alguns referências podem ser removidas, pois não serão utilizadas nos exemplos. Clique com o botão direito em “References” do projeto CLR e selecione a opção “Add Reference…”. Na tela que irá abrir, selecione a categoria Projects > Solutions e marque o checkbox do projeto Class Library:

CLR Add Reference

Não se esqueça de definir a permissão do projeto que foi importado, conforme demonstrado na figura abaixo:
CLR Assembly Permission Set 2

O Solution Explorer deverá ficar como o abaixo:
CLR novo projeto com Class Library - Solution explorer

Criando uma stored procedure sem retorno

  • Clique com o botão direito no diretório “Procedures” do projeto Bibliotecas (Class Library) e selecione a opção Add > Class… Na tela que foi aberta, digite o nome do arquivo que será criado. Eu costumo colocar o mesmo nome do objeto que será criado no banco de dados. Neste exemplo, irei criar o arquivo stpCopia_Arquivo.cs
  • Copie e cole o código abaixo:

Criando uma stored procedure com retorno de um select

  • Clique com o botão direito no diretório “Procedures” do projeto Bibliotecas (Class Library) e selecione a opção Add > Class… Na tela que foi aberta, digite o nome do arquivo que será criado. Eu costumo colocar o mesmo nome do objeto que será criado no banco de dados. Neste exemplo, irei criar o arquivo stpImporta_Txt.cs
  • Copie e cole o código abaixo:

Criando uma função escalar

  • Clique com o botão direito no diretório “Functions” > “Scalar Function” do projeto Bibliotecas (Class Library) e selecione a opção Add > Class… Na tela que foi aberta, digite o nome do arquivo que será criado. Eu costumo colocar o mesmo nome do objeto que será criado no banco de dados. Neste exemplo, irei criar o arquivo fncArquivo_Existe.cs
  • Copie e cole o código abaixo:
  • Repare que, diferentemente das procedures que retornam sempre void, as functions devem retornar dados. No caso do exemplo, retornam dados do tipo SqlBoolean (bit = true/false)

Criando uma table-valued function

  • Clique com o botão direito no diretório “Procedures” do projeto Bibliotecas (Class Library) e selecione a opção Add > Class… Na tela que foi aberta, digite o nome do arquivo que será criado. Eu costumo colocar o mesmo nome do objeto que será criado no banco de dados. Neste exemplo, irei criar o arquivo fncArquivo_Ler.cs
  • Copie e cole o código abaixo:
  • Este tipo de objeto é provavelmente o mais trabalhoso de se criar no CLR, uma vez que o seu resultado é uma tabela e para preencher essa tabela, precisamos criar uma classe com os sets/gets com os dados que serão retornados, definir a saída dos dados para o banco e a programação para popular/calcular os dados.

string ou SqlString?

Clique aqui para visualizar
Como vocês devem ter reparado nos exemplos acima, em alguns casos eu utilizo string, bool, int, etc.. e em outros eu utilizo SqlString, SqlBoolean, SqlInt32, etc.. Essa diferença consiste em que os primeiros casos são tipos de dados do C#, enquanto os outros são voltados para o banco de dados em si e possuem mais controles e formas de trabalhar.

Exemplos:
o tipo SqlString possui o método de verificação IsNull, para determinar se foi passado por parâmetro um valor NULL. Essa verificação é mais rápida do que usar a string.IsNullOrEmpty. Entretanto, utilizando uma variável string como parâmetro, se a função ou SP for chamada com valor NULL, ela será executada normalmente, sendo tratada ou não futuramente no seu código-fonte. Caso você informe uma valor NULL para uma variável do tipo SqlString e você não tenha feito o tratamento utilizando o método variavel.IsNull, ao recuperar o valor informado utilizando o atributo variavel.Value ou o método variavel.ToString(), será gerada uma exceção de ERRO na sua rotina.

Outro exemplo legal pra ilustrar a diferença é entre o DateTime e o SqlDateTime. Se você passar uma data NULL para o DateTime, será gerado uma exceção de ERRO na sua rotina. Esse tipo de dados não aceita NULL. Caso você precise utilizar algo assim, deverá utilizar os método MinValue (1/1/0001 12:00:00 AM) ou MaxValue (31/12/9999 23:59:59). Já o tipo de dado SqlDateTime aceita valores nulos e possui o método variavel.Null para definir valores nulos.

Minha recomendação é utilizar sempre os tipos de dados para banco de dados (SqlString, SqlInt32, etc) e lembrar SEMPRE de realizar os tratamentos necessários (principalmente o variavel.IsNull).

Qual versão do .NET Framework devo utilizar?

Clique aqui para visualizar
Identificar e decidir a versão do .NET Framework a ser utilizado é uma tarefa bem simples, mas que já me causou muita dor de cabeça no passado devido à pouca documentação sobre o assunto. Por isso, vou tentar ajudar vocês simplificando:
– SQL Server 2005: Você só pode utilizar o .NET Framework 1.0 e 2.0
– SQL Server 2008: Suporta até o .NET Framework 3.5
– SQL Server 2012 e 2014: Suportam até o .NET Framework 4.6.1

Você pode definir a versão do .NET Framework ao criar o projeto ou clicando com o botão direito sobre o projeto do Class Library e selecionado a opção “Properties”. Você precisa fazer o mesmo procedimento para o projeto CLR também, que ainda te dá a possibilidade de definir a versão do banco de dados:

CLR - Target Framework

CLR - Target Plataform

CLR Class Library - .NET Framework Version

Compilando seu projeto e publicando no banco de dados

Clique aqui para visualizar
Uma vez que os códigos foram desenvolvidos, vamos publicar os assemblies gerados no banco de dados para começar a utilizar os objetos que serão criados. Pode-se definir que o próprio Visual Studio crie o database de destino (caso não exista) ou você pode criar o database manualmente antes (eu recomendo essa opção) e depois publicar os assemblies.

Uma vez que o database foi criado, clique com o botão direito no projeto CLR e selecione a opção “Publish…”. Os botões “Build” e “Rebuild” servem para apenas compilar o código-fonte e já validar se existem erros de sintaxe no fonte, enquanto o botão “Clean” elimina os arquivos gerados e o cache do Visual Studio.

CLR - Publish

Na tela que será aberta, você deve clicar no botão “Edit…” para digitar o servidor e a forma de conexão com o banco de dados.

CLR - Publish Database 2

Você pode criar/carregar perfis para facilitar a publicação em mais de um ambiente. O botão “Generate Scripts” irá gerar um script SQL, que deve ser executado pelo SQLCMD para publicação do CLR e o botão “Publish” irá gerar o script e já executar no banco de dados.

CLR Publish completed successfully

Nível de permissão do assembly

No SQLCLR, existem 3 níveis de permissões dos assemblies criados:

  • SAFE: Os métodos do assembly podem fazer mais que os métodos Transact-SQL com a mesma lógica e são executados com as credenciais do usuário que chamou
  • EXTERNAL ACCESS: Os métodos podem realizar operações de manipulação de arquivos e I/O pela rede. Os métodos são executados utilizando o service account do SQL Server, herdando seus privilégios do Active Directory
  • UNSAFE / UNRESTRICTED: Estende os privilégios do EXTERNAL ACCESS, permitindo o CLR a executar comandos sem qualquer restrição

Caso o assembly necessite de utilizar os níveis de permissão EXTERNAL ACCESS ou UNRESTRICTED, será necessário definir o database em que ele será publicado como TRUSTWORTY:

Permissões necessárias para compilar o CLR

Para que um usuário tenha permissão para publicar um CLR no database, ele precisará atender a um dos requisitos abaixo:

  • Membro da role sysadmin
  • Caso o nível de permissão do assembly seja SAFE, o usuário necessitará da permissão CREATE ASSEMBLY e DROP ASSEMBLY
  • Caso o nível de permissão do assembly seja EXTERNAL ACCESS, o usuário necessitará da permissão CREATE ASSEMBLY, DROP ASSEMBLY e EXTERNAL ACCESS ASSEMBLY
  • Caso o nível de permissão do assembly seja UNRESTRICTED, o usuário necessitará da permissão CREATE ASSEMBLY, DROP ASSEMBLY e UNSAFE ASSEMBLY

Resultado final:

CLR - Object Explorer

SQLCLR

Restrições em assemblies: DLL’s Suportadas e Não Suportadas

Clique aqui para visualizar
O SQL Server coloca certas restrições em código gerenciado em assemblies para verificar se eles podem ser executados de uma maneira segura e evolutiva.Isso significa que não são permitidas certas operações que podem comprometer a robustez do servidor em assemblies SAFE e EXTERNAL_ACCESS.

Atributos personalizados não permitidos
Assemblies não podem ser anotados com os seguintes atributos personalizados:
– System.ContextStaticAttribute
– System.MTAThreadAttribute
– System.Runtime.CompilerServices.MethodImplAttribute
– System.Runtime.CompilerServices.CompilationRelaxationsAttribute
– System.Runtime.Remoting.Contexts.ContextAttribute
– System.Runtime.Remoting.Contexts.SynchronizationAttribute
– System.Runtime.InteropServices.DllImportAttribute
– System.Security.Permissions.CodeAccessSecurityAttribute
– System.STAThreadAttribute
– System.ThreadStaticAttribute

Adicionalmente, não podem ser anotados assemblies SAFE e EXTERNAL_ACCESS com os seguintes atributos personalizados:
– System.Security.SuppressUnmanagedCodeSecurityAttribute
– System.Security.UnverifiableCodeAttribute

APIs não permitidas do .NET Framework
Qualquer Microsoft .NET Framework API anotada com uma das proibições HostProtectionAttributes não pode ser chamada dos assemblies SAFE e EXTERNAL_ACCESS.
– eSelfAffectingProcessMgmt
– eSelfAffectingThreading
– eSynchronization
– eSharedState
– eExternalProcessMgmt
– eExternalThreading
– eSecurityInfrastructure
– eMayLeakOnAbort
– eUI

Assemblies .NET Framework suportados
Qualquer assembly referenciado por seu assembly personalizado deve ser carregado no SQL Server usando CREATE ASSEMBLY. Os seguintes assemblies do .NET Framework já estão carregados no SQL Server e, portanto, podem ser consultados por assemblies personalizados sem ter que usar CREATE ASSEMBLY.
– CustomMarshalers.dll
– Microsoft.VisualBasic.dll
– Microsoft.VisualC.dll
– mscorlib.dll
– System.dll
– System.Configuration
– System.Core.dll (suportado a partir do SQL Server 2008)
– System.Data.dll
– System.Data.OracleClient
– System.Data.SqlXml.dll
– System.Deployment
– System.Security.dll
– System.Transactions
– System.Web.Services.dll
– System.Xml.dll
– System.Xml.Linq.dll (suportado a partir do SQL Server 2008)

Views e SP’s do Catálogo

Clique aqui para visualizar

E é isso aí, pessoal!
Até o próximo post!

CLR SQL Server SQLCLR como ativar habilitar como usar how to active enable como criar sp como criar procedure como programar como começar introdução iniciando por onde começar how to code coding programming procedures table-valued functions scalar c# csharp programação banco de dados database programming

CLR SQL Server SQLCLR como ativar habilitar como usar how to active enable como criar sp como criar procedure como programar como começar introdução iniciando por onde começar how to code coding programming procedures table-valued functions scalar c# csharp programação banco de dados database programming

SQL, sql server, sql server 2008, sql server 2008 R2, Oracle, Oracle Database, Oracle 11g, Oracle 10g, Oracle 12c, MySQL, Firebird, Consultoria, Consultor, Programador, Programação. Desenvolvedor, Analista de Sistemas, DBA, Criação de website, Criação de Sistema Web, Vitória, Vila Velha, Guarapari, Espírito Santo, ES, Consultoria SQL em VItória, Treinamento, Curso, Prestação de serviço, prestar serviço, freelancer, freela, banco de dados, consultoria em banco de dados, consultor de banco de dados

6 comentários em “Introdução ao SQL CLR (Common Language Runtime) no SQL Server

  1. Dirceu, suas explicações e exemplos tem me sido de grande utilidade, você pode, por gentileza produzir algum exemplo de FTP usando CLR? Desde já agradeço a sua boa vontade.

  2. Olá Dirceu. Segui as instruções mas na hora da publicação dava o erro abaixo, gerei o script e alterei o TRUSTWORTHY para on, Mesmo assim apenas o assembly bibilotecas foi gerado mas as procedures não, você pode me orientar em como resolver isso. Grato.
    reating [Bibliotecas]…
    (47,1): SQL72014: .Net SqlClient Data Provider: Msg 10327, Level 14, State 1, Line 1 Falha em CREATE ASSEMBLY do assembly ‘Bibliotecas’ porque o assembly ‘Bibliotecas’ não está autorizado para PERMISSION_SET = UNSAFE. O assembly é autorizado quando uma das seguintes condições é verdadeira: o proprietário do banco de dados (DBO) possui permissão UNSAFE ASSEMBLY e a propriedade TRUSTWORTHY está ativada no banco de dados; ou o assembly está assinado com uma chave assimétrica ou de certificado que possui um logon correspondente com permissão UNSAFE ASSEMBLY.
    (47,0): SQL72045: Script execution error. The executed script:
    CREATE ASSEMBLY [Bibliotecas]
    AUTHORIZATION [dbo]
    FROM 0x4D5A9000030…
    An error occurred while the batch was being executed.

Deixe uma resposta