使用Visual Studio进行SQL Server源代码管理和部署

        英文原文：SQL Server Source Control and Deployment with Visual Studio

        本文将为读者你介绍 Visual Studio 2013 中 SQL Server Data Tools（SSDT）这一特性，该特性可以用于在源代码优先这一方法论中管理数据库。简单来说，对于数据库表、视图等对象的修改，都将以源代码的方式在 Visual Studio 中完成。源代码可以与生产环境的数据库进行比较，并且在编译后生成一份部署脚本。

        前提

• SQL Server 2005 或之后的版本
• Visual Studio 2013

        如果你正在使用 SQL Server 2014，请确保你已经更新了 SSDT，可以在 Visual Studio 中的“更新与扩展”菜单项中进行更新。

        对数据库进行反向工程

        通过“导入”指令，SQL Server Data Tools 可以对一个现有的数据库进行反向工程，将其转换至一个空的项目中。需要指出的是，该项目必须保证是完全空的，一旦你在这个项目中加入了任何文件，“导入”指令就无法应用于数据库上了。

        首先，创建一个全新的 SQL Server Database 项目，右键单击 Solution Explorer 中的项目，并选择“导入”，再选择“数据库”。

        数据库项目并不强制你使用任何特殊的文件夹结构。与 C# 不同，它使用文件夹结构作为默认的命名空间，而 SSDT 允许你将某个 schema 中的对象放入为另外的 schema 所创建的文件夹中。尽管如此，该导入工具仍然会为你推荐一些文件夹结构，作为项目的起点。包括以下选项：

• 空
• Schema
• 对象类型
• Schema/对象类型

        即便在小型数据库中，我也建议你使用 Schema/对象类型结构。如果你只使用 Schema 结构，不久你就会发现你会不停地打开各个文件，仅仅是为了查看该文件是什么类型的。而如果你只使用对象类型结构，那就会促使开发者们倾向于将所有对象都创 建在 dbo 这个 schema 下。

        数据库设置

        在“项目设置”这个标签页下可以找到数据库的通用设置。如果你没有找到想要的设置项，也可以通过 SQL 语句直接添加。在本文稍后的“存储”这一节中你将看到相关的示例。

        调试选项

        在我们离开项目设置这个窗口前，你还需要设置一个调试数据库。每次你的应用程序开始运行时，数据会自动部署到这个调试数据库中。如果你选项的数据库名称不存在，那么在你的项目初次运行时，会自动创建这个数据库。

        一般来说，你应该会选择“在目标数据库中删除对象，但在项目中保留”这一选项，否则你的调试数据库就会累积在开发过程中曾一度创建，而后被删除的这些对象。

        只差最后的一步，你就可以完成数据库的设置，让它支持自动部署了。你需要打开解决方案的属性设置，并将该数据库项目设置为启动项目的一个依赖项。

        存储

        如果你需要指明额外的，或者是特定的文件组，你可以使用项目内置的对象模板，它们将创建类似于以下的脚本：

ALTER DATABASE [$(DatabaseName)]  ADD FILEGROUP [FileGroup1]

        请注意这里对 sqlcmd 变量的使用[$(DatabaseName)]，如果你希望在同一台服务器上部署同一个数据库项目的多个拷贝，它将会很有用。

        安全

        在配置一个新数据库时，人们经常会犯的错误就是一上来就为所有人分配所有的访问权，并且假设将来某一天你会锁定这些访问权。安全设置往往是十分 微妙的，尤其在使用高级特性时，它很可能出乎你的意料。因此，更好的方式是在创建用户帐号的时候不要分配任何权限，随后在需要时为他们分配对特定对象和特 性的访问权。

        Schema

        数据库导入流程会将 schema 定义创建在 Security 文件夹中，而不是该 schema 所对应的同名文件夹中。请记住，文件夹的位置并不重要，你可以选择将 schema 的定义移动到其它文件夹中。

        登录帐号与用户

        虽然从技术角度上来说，登录帐号是一个服务器对象，而不是数据库对象，但你仍然可以将它包含在项目中。如果该登录帐号已经存在了，那么在部署时会自动忽略它。

        当你在创建登录帐号时，请不要使用“USE master”指令，部署工具会自动为你进行处理。

        在默认情况下，新创建的登录帐号和用户还不能连接到数据库服务器。这意味着你需要在脚本中加入一行“GRANT CONNECT TO [userName]”语句。

        其它类型的安全对象

        其它类型的安全对象，例如角色、非对称钥和证书等等也可以用类似的方式进行创建。

        创建数据库表

        在创建一张新表时，只需右键单击将包含该表的文件夹，并选择“添加一张表”。在这里要提供 schema 与表名，中间用.分隔。接下来，你就可以使用设计器或 SQL 编辑器开始添加各个字段了。

        索引

        索引可以作为表定义的一部分一同添加，也可以作为一个单独的命令添加在同一个文件中。理论上，你也可以为每个索引创建一个单独的文件，不过这种方式未免有些过于麻烦了。

        当你在独立的语句中创建索引时，需要注意在每个语句间使用 GO 语法作为批处理的分隔符。

        文档

        在 Visual Studio 2013 中，你再也没有借口不为表与字段添加任何注释了。表级别的描述信息需要在“属性”面板中进行设置，你也可以同样使用“属性”面板设置字段的描述，但多数人喜欢在表设计器中设置字段的注释。

        这其实并非 SQL Server 的一种新特性，早在很久之前 SQL Server 就支持为表和字段添加注释了。这一工具只是将操作替换为 sp_addextendedproperty 这一啰嗦的语法而已。

EXEC sys.sp_addextendedproperty   @name=N'MS_Description',   @value=N'The employee key, which is called empId in some older tables.',   @level0type=N'SCHEMA',  @level0name=N'dbo',   @level1type=N'TABLE',  @level1name=N'Employee',   @level2type=N'COLUMN',  @level2name=N'EmployeeKey'

        部署

        在调试时进行自动部署

        要打开自动部署功能，你需要首先打开数据库项目设置中的 Debug 标签页进行修改。一般来说，你需要修改连接字符串信息。如果该数据库比较小，而且你能够通过脚本重新加载所有数据，那么选择自动重建数据库也不是一个坏主 意。否则，你需要让它删除所有不在源代码控制系统中的对象。这样可以避免你无意中使用了某些在生产环境中并不存在的数据库对象。

        接下来，你需要将该数据库项目设置为启动项目的一个依赖项，你需要在“解决方案”设置对话框，而不是在项目属性中完成这一步骤。

        发布

        通过右键单击数据库项目，你就可以打开发布对话框。首先填入目标数据库的连接字符串，接下来你可以打开“高级”标签页进行应用程序的相关设置。 举例来说，在生产环境中，你可能会选择在发布时触发备份步骤。而在开发或预发布服务器上，将不包含在项目中的对象全部删除也是很重要的一点。请你仔细检查 这些设置，其中有许多部分需要认真考虑。

        接下来你应该保存这个发布脚本，它会生成一个 .publish.xml 文件，今后你会经常用到它。要加载这个文件时，只需在 Solution Explorer 面板中双击它就可以了。

        如果你选择部署到自己的工作机器上，可以直接单击“发布”按钮继续。而如果是部署到共享环境上，那就应当选择“生成脚本”选项。多数情况下，你 不会再去检查生成的文件，而是在 Data Tools Operations 面板中单击“检查预览”，它会列出该脚本内容的一个小结。

        如果检查后，你对这些操作觉得都没问题了，就可以通过“发布”选项重新运行这段发布脚本。

        修改部署脚本

        有些时候，你需要对发布脚本进行一些手动修改。这种情况通常发生在对某个表的操作会产生数据丢失的情况下。例如将某个字段设为不允许空值，或者是修改字段数据类型。

        在 Visual Studio 中是不能够直接修改部署脚本的，你需要将脚本拷贝到 SQL Server Management Studio 中。请确保你在“查询”菜单中打开了 SQLCMD 模式，因为它需要进行某些设置，例如数据库名称等等。

        还有一种情况下你可能需要修改部署脚本，就是将脚本分解为多个小的片段。比方说，除了某些常见的修改之外，你可能还需要为某张数据量很大的表添 加一些索引。由于创建这些索引的过程时间可能会很长，你或者会决定将创建索引的改动在几个小时之后再进行，而在那之前仍然要运行脚本的其它部分。

        重构日志

        SQL Server Data Tools 内部维护着一份重构日志，它也减少了你手动编辑部署脚本的需要。不幸的是这一特性很容易在无意之中被忽略。如果你在设计器窗口中修改了某个字段的名称，这 次改动就会记录在日志中。但如果你直接对包含这张表定义的原始 SQL 进行编辑，那么日志中就不会存在这次修改的记录。

        你或许不会立即发现这一问题，但当你开发部署数据库时，这段脚本会尝试删除某个字段，随后再添加一个新字段，而不是你所期望的对字段直接重命 名。一旦发生这种情况，你必须选择要么立即回滚你的改动，并按照“正确的方法”再做一遍，意味着你需要使用表设计器进行操作。或者你也可以选择对部署脚本 进行手动修改。

        创建视图、自定义函数和存储过程

        如果你的应用程序是基于 ORM 开发的，那么基本上可以忽略这一节内容。

        基础

        与表的创建一样，对于你将视图、自定义函数和存储过程放在哪里并没有强制的规定。如果你选择使用 Schema/对象类型这一文件结构，那么默认的设置是为这三种对象各自创建一个独立的文件夹。还有一种选择是参考 SQL Server Management Studio 的模式，将它们各自分解到细粒度的文件夹中。

        有两种方式可以将新对象加入项目中，你可以选择基于模板创建，在其中填入各种内容，例如参数列表或是视图内容。或者你也可以选择在 SQL Server Management Studio 中创建好该对象，如果在我不确定该对象的具体内容，并且预计会持续进行修改时，通常我会选择这种方式。一旦我确信了其中的内容，我就会将其保存为一个 SQL 文件，随后导入到我的项目中。

        添加安全设置

        假设这个数据库已经进行了合适的安全防护，接下来你就需要将各种对象对用户进行授权了。虽然有多种方式可以完成这一任务，但我还是建议将 GRANT 语句放在与定义视图、自定义函数和存储过程相同的文件中。这样一来，你一眼就能看到可以执行这段过程的用户是否都赋予了权限。

        部署之前与之后的脚本

        SQL Server Data Tools 允许你创建一个单独的部署前脚本，以及一个单独的部署后脚本。这些脚本并非一次性的迁移脚本，而是在每次部署时都会运行的内容。

        这两个脚本的命名分别为 Script.PreDeployment.sql 和 Script.PostDeployment.sql。它们并非真正的 SQL 脚本，而是一种称为 SQL CMD 的变体。通过 SQL CMD 可以对其它 SQL 脚本进行引用。

        认识到以上这点很重要，因为你的部署前与部署后脚本很容易变得混乱，为了避免混乱的产生，我建议你将这类脚本作为目录一样处理，将每一类的操作分别存储在不同的脚本文件中。以下是一个典型的部署前脚本的示例：

PRINT N'Enabling CLR';EXEC sp_configure 'clr enabled' , '1';RECONFIGURE;  :r ".\OneTimeScripts\Migrate records to not use customer type 6.sql"

        下面是相对应的部署后脚本：

:r .\Data\UserType.sql  :r .\Data\CustomerType.sql  :r .\Data\AccountLevel.sql

        时机

        部署前脚本的执行时机处于数据库创建之后，而在其它对象加入数据库之前。它允许你修改服务器与数据库级别的设置，而这些设置并没有直接通过 SSDT 暴露出来。

        部署后脚本执行的时机总在最后，正如其名称所暗示的一样，它是在其它所有数据库对象都更新后才运行的。

        编写一次性脚本

        编写一次性脚本并不容易。首先，SSDT 中并没有一次性脚本的概念，因为它不并清楚某段脚本是否曾经运行过，因此你所能做的就是尽量用 if 语句覆盖你的脚本，以此进行判断。

        处理一次性脚本的另一种方法是手动修改部署脚本，这种方式具有高度灵活性，但却是不可重复的，你必须对每个环境重新应用所有的改动。

        加载表数据的模式

        SSDT 中所缺少的另一项特性是表的加载，理想的情况下，所有的查找表数据都应该保存在源代码控制中，如同其它数据库对象一样。但由于这一特性的缺乏，我们不得不通过其它替代途径实现这一功能。

        其中一种途径是使用 MERGE 语句。首先，为你的目标表创建一个结构类似的临时表，随后在该临时表中加入你所需的数据。随后你就可以使用 MERGE 语句执行必要的插入、更新和删除操作。

        另外一种途径是编写一个插入或更新的存储过程，然后就可以通过调用该存储过程对目标表中的每一行进行操作了。这种方法允许你处理复杂的逻辑，但无法通过这种方式删除过期的数据。

        连接数据库

        对于大型系统来说，仅仅使用 schema 作为命名空间往往是不够的。出于多种不同的原因，例如部署周期、安全性、性能和灾备，往往会将对象分布在多个数据库、乃至多台服务器上。

        只要你对这些情况做好计划，SSDT 都能够处理。你需要注意的一点是循环引用的限制，某个由 SSDT 管理的数据库可以对另一个数据库进行引用，哪怕后者处于不同的服务器上，只要你能够建立一个清晰的编译顺序就可以实现这一点。这种情形和 .net 项目的引用是完全一样的。

        一种常见的情形是，某个新建的数据库需要引用某个遗留系统的数据库。首先要为旧数据库创建脚本，并将其导入到一个空的项目中。你并不需要使用 SQL Server Data Tools 管理这个遗留数据库，只需要它所对应的元数据就可以了。如果需要，你也可以仅仅导入那些新数据库所依赖的对象。

        接下来你需要添加一个数据库引用，在这里你需要做出一个重要的决定，是否让旧的数据库也驻留在同一台服务器上。虽然你也可以事后改变想法，但过程既麻烦又容易出错。以下是添加引用的对话框：

        其中的数据库变量以及可选的服务器变量会对你的自定义函数及存储过程的实际名称产生影响，因此请确保你使用了[$(variable)]这一模式，否则你可能会遇到编译错误。当你实际发布时，系统会要求你为这些变量填入实际名称。

        当你按下“加载值”按钮时，在项目级别所定义的默认值将作为实际的值填入这些变量中。为了在今后的部署中节约时间，你也可以将这些数值保存为发布档案的一部分。

        SQL CLR

        虽然在 SQL Server 中使用 C# 代码的做法曾经一度遭受一些非议，但在某些情况下还是非常有用的。在某些场合中，由 JIT 编译的 .NET 代码比起解释型的T-SQL 会得到更好的性能。而对于某些数据类型来说，例如几何数据与地理位置，你完全没有其它选择。

        使用 SQL CLR 的一个主要问题在于部署。通常来说，要将一个 .NET 程序集部署到 SQL Server 中，需要手动将 DLL 拷贝到某处，随后一个一个地进行手动添加。SSDT 的发布工具能够消除这一问题，它会将程序集进行编码成为一个 SQL 语句，与部署脚本的其余部分进行内联。

        外部项目与程序集

        按照约定，加入 SSDT 项目的程序集会首先添加到一个名为“Assemblies”的文件夹中。虽然这并非必须，但它会使得对 DLL 的管理更加容易。如果你决定遵循这一建议，请首先完成这一步骤。

        接下来，你可以通过常用的 .NET“添加引用”对话框添加对某个项目或是程序集的引用。

        最后一步是将你需要在 SQL 中使用的类型与函数暴露出来，你可以在“添加项目”对话框中的 SQL CLR 文件夹选项中找到对应的模板。这种做法与手动注册 SQL CLR 类型和函数没有分别。

        内部项目

        内部项目是指驻留在数据库项目中的某个 C# 项目，它将随着数据库自动构建及部署，因此你不必担心对引用及程序集的管理。虽然你无法实现 CLR 版本的静态代码分析功能，但除此之外它的行为与其它 C# 项目相比完全一致。

        内部项目的一个有用的特性在于，你不需要显式地通过 SQL 注册函数。编译器看到 SqlFunction 属性之后，会自动在项目定义的默认命名空间中创建正确的 SQL 原型。

        重编译问题

        当你应用 CLR 项目时，无论是内部项目还是外部项目，重编译都会成为问题。尤其是当你在某个计算字段中使用了某个 SQL CLR 函数的时候。

        本质上，问题在于 Visual Studio 会经常对 C# 项目进行重编译，哪怕没有任何改变。由于新的版本中使用了一个新的 hash 码，发布工具就会认为该项目是个新的版本，并且重新部署整个程序集。在之前所提到的计算字段的场景中，发布工具就需要对受影响的表中的每一行进行重新计 算。

        当这个问题出现时，可以将 SQL CLR 代码迁移到某个外部项目中，随后将编译后的项目程序集放到 Assemblies 文件夹中，就所同一个第三方类库一样。要注意的是这种方式有一个风险，那就是你很可能在进行变更的时候忘了更新该程序集。

        全文索引

        SQL Server Data Tools 对全文索引仅提供了部分支持，它对于创建与管理全文索引目录提供了良好的支持，你也可以将 FTS 索引添加到任何需要使用它的表中。你可能遇到的问题在于，SSDT 仅支持空的非索引字表（StopList），这是因为非索引字表中的内容被视为数据，而不是 schema，因此 SSDT 不知道如何将它的内容保持更新。

        有一个应对该问题的临时方案，就是使用部署后脚本加载其中的数据，正如同对于查找表数据的处理方式一样。一旦创建了非索引字表之后，发布工具将不会再修改其中的数据。

        版本号

        SQL Server 本身对于版本号并没有一个强烈的概念。在理论上，你可以在某个编译后数据层的应用（dacpac）中加入版本号，但这种方式在实践中是难以运行的。目前并没有一种非常良好的方式能够获取到这个值，你只能选择在构建后脚本中对 dacpac 版本进行自动增值处理。

        你可以考虑采取这种方式：创建一个内部项目，其中只包含一个名为 dbo.GetDatabaseVersion 的函数，该函数能够从 C# 程序集中读取版本号。如果你在程序集版本中使用了通配符，版本号就能够自动增加。

        另一个选择是创建一个名为 dbo.GetDatabaseVersion 的T-SQL 函数，它会返回一个硬编码的值，你可以在需要时手动增加它的值。

        Brett Gerhardi 的建议是，为数据库版本创建一个扩展属性。参考以下例子：

EXEC sp_addextendedproperty @name='DbVersion',   @value ='1.015',   @level0type = NULL,  @level0name = NULL,   @level1type = NULL,  @level1name = NULL,   @level2type = NULL,  @level2name = NULL

        如果不使用构造前脚本，那么你必须手动更新它的值。

        持续集成

        在理论上，你可以使用 SqlPackage.exe 自动部署数据库。Anuj Chaudhary 在博客中发布了一篇帖子，名为“SqlPackage.exe —— 自动化 SSDT 部署”，为你介绍了这一过程。但在实践中，你会发现在某些场合中如果不对更新进行手动调整，对于 SSDT 来说处理会显得过于复杂。