如何将 SQL CLR 存储过程部署到多台服务器

Question

我继承了一个 SQL CLR 项目，作为我正在为客户开发的代码维护项目的一部分。诚然，我对 SQL CLR 还很陌生，所以我试图弄清楚它是如何工作的。

我注意到数据库连接字符串存储在项目的属性中，因此我知道如何在需要时更改它。我的一个问题是：是否可以设置多个连接字符串以部署到多个 SQL Server 实例？就我而言，我有一台本地开发计算机、一台临时服务器和一台生产服务器（每台服务器上都有目标数据库的单独副本）。我希望能够将 SQL CLR 程序集部署到所有 3 个程序集，而无需更改连接字符串并为每个程序集重新构建。

Answer 1

除了通过 Visual Studio 进行开发之外，您不应部署到任何地方，因此项目中的连接字符串应始终指向您的开发环境。

在开发服务器中测试代码后，您可以通过右键单击相关程序集并执行“将程序集编写为...”然后“创建到...”然后“新建”来在 SSMS 中编写程序集脚本。查询窗口”。这将为您提供用于部署到 QA、暂存和生产的基本脚本。

一般格式是：

USE [DBName]
GO

CREATE ASSEMBLY [AssemblyName]
AUTHORIZATION [dbo]
FROM 0x0000...
WITH PERMISSION_SET = SAFE

您实际上并不需要将程序集文件传播到其他环境，但如果您愿意的话也没有什么坏处。

如果您想自动执行此操作，一旦您拥有了基本脚本，您始终可以通过以下方式获取更新的汇编代码（上面标记为 0x0000）：

SELECT Content FROM sys.assembly_files WHERE name = 'AssemblyName'

编辑：
为了完整起见，正如 Jeremy 在下面的评论中提到的，上述信息仅描述了程序集本身的部署，而不是用于访问程序集内代码的包装器对象的部署。完整的部署过程将：

1. 删除现有的包装器对象（存储过程、函数、触发器、类型和聚合）
2. 删除程序集
3. 创建新的程序
4. 集 创建包装器对象

Answer 2

将代码部署到开发服务器时，Visual Studio 会在 bin/Release 文件夹中创建一个 .sql 文件。

这对于部署很有用，但需要一些清理。

这是一个 perl 脚本，我用来从 VS 创建的脚本中获取部署脚本。

它与我的需求和文件格式密切相关（我正在使用 VS 2010 SP1、SQL 2008 R2、cygwin 中的 perl），将此视为一个示例，它可能无法自动适合每个人。

use strict;
use warnings;

use Text::Unidecode 'unidecode'; # http://search.cpan.org/dist/Text-Unidecode/

sub ProcessBlock($)
{
    my $lines = $_[0];

    if ($lines =~ "Deployment script for") { return 0; }
    if ($lines =~ "^SET ") { return 0; }
    if ($lines =~ "^:") { return 0; }
    if ($lines =~ "^USE ") { return 0; }
    if ($lines =~ "^BEGIN TRANSACTION") { return 0; }
    if ($lines =~ "extendedproperty") { return 0; }
    if ($lines =~ "^PRINT ") { return 0; }
    if ($lines =~ "#tmpErrors") { return 0; }
    if ($lines =~ "^IF \@\@TRANCOUNT") { return 0; }

    my $drop = $lines;
    if ($drop =~ m/^DROP (FUNCTION|PROCEDURE) ([^ ]+);/m)
    { 
        printf("if OBJECT_ID('$2') IS NOT NULL\n");
    }
    elsif ($drop =~ m/^DROP ASSEMBLY \[([^ ]+)\];/m)
    { 
        printf("IF EXISTS (SELECT 1 FROM sys.assemblies WHERE name = '$1')\n");
    }

    printf($lines);
    printf("GO\n");

    my $create = $lines;
    if ($create =~ m/^CREATE PROCEDURE (\[[^]]+\])\.(\[[^]]+\])/m)
    {
        printf("GRANT EXECUTE ON $1.$2 TO PUBLIC\nGO\n");
    }
    elsif ($create =~ m/^CREATE FUNCTION (\[[^]]+\])\.(\[[^]]+\]).*RETURNS .* TABLE /ms)
    {
        printf("GRANT SELECT ON $1.$2 TO PUBLIC\nGO\n");
    }
    elsif ($create =~ m/^CREATE FUNCTION (\[[^]]+\])\.(\[[^]]+\])/m)
    {
        printf("GRANT EXECUTE ON $1.$2 TO PUBLIC\nGO\n");
    }
}



my $block="";

while (<>)
{
    my $line = $_;
    $line = unidecode($line);
    if ($line =~ "^GO")
    {
        ProcessBlock($block);
        $block = "";
    }
    else
    {
        $block .= $line;
    }
}

用法：

perl FixDeploy.pl < YourAssembly.sql > YourAssembly.Deploy.sql

Answer 3

看这里：SQLCLR 中连接字符串的区别 我认为如果可能的话，您应该使用上下文连接。这样您就不必重新配置。

如果您需要不同的凭据或其他内容，您可以查询保存这些设置的设置表。使用上下文连接进行连接，查询设置表以获取登录详细信息，然后使用它们再次连接。

另外：连接字符串位于属性中，但据我了解，settings.xml 不会部署，因此您始终会将默认值硬编码到设置类中。