如何使用 Visual Studio 的 Git 源代码控制提供程序对 SQL Server 数据库进行版本控制

How to version control SQL Server database with Visual Studio's Git Source Control Provider

提问by Souper

I have Git Source Control Providersetup and running well.

我已经安装了Git 源代码控制提供程序并且运行良好。

For Visual Studio projects, that is.

对于 Visual Studio 项目，即。

The problem is that each such project is tightly tiedto a SQL Server database.

问题是每个这样的项目都与SQL Server 数据库紧密相关。

I know how to version control a databasein its own .gitrepository but this is neither convenient nor truly robust because ideally I would want the same ADD, COMMIT, TAG and BRANCH commands to operate on both directory trees simultaneously, in a synchronized manner.

我知道如何在自己的.git存储库中对数据库进行版本控制，但这既不方便也不真正强大，因为理想情况下我希望相同的 ADD、COMMIT、TAG 和 BRANCH 命令以同步方式同时对两个目录树进行操作。

Is there a way to Git SQL Server database with Visual Studio's Git Source Control Provider in the manner I described?

有没有办法以我描述的方式使用 Visual Studio 的 Git 源代码控制提供程序来 Git SQL Server 数据库？

采纳答案by Not So Sharp

You can install the SQL Server Data Toolsif you want to, but you don't have to: You can Use the Database Publishing Wizard to script your table dataright from Visual Studiointo the solution's folder, then Git it just like you do with the other project files in that folder.

SQL Server Data Tools如果您愿意，您可以安装它，但您不必：您可以使用数据库发布向导将您的表数据直接从 Visual Studio编写到解决方案的文件夹中，然后像处理其他项目文件一样使用 Git在那个文件夹中。

回答by Uri

Being in the database version control space for 5 years (as director of product management at DBmaestro) and having worked as a DBA for over two decades, I can tell you the simple fact that you cannot treat the database objects as you treat your Java, C# or other files and save the changes in simple DDL scripts.

在数据库版本控制领域工作了 5 年（在DBmaestro担任产品管理总监）并作为 DBA 工作了二十多年，我可以告诉你一个简单的事实，即你不能像对待 Java 那样对待数据库对象， C# 或其他文件并将更改保存在简单的 DDL 脚本中。

There are many reasons and I'll name a few:

原因有很多，我只列举几个：

• Files are stored locally on the developer's PC and the change s/he makes do not affect other developers. Likewise, the developer is not affected by changes made by her colleague. In database this is (usually) not the case and developers share the same database environment, so any change that were committed to the database affect others.
• Publishing code changes is done using the Check-In / Submit Changes / etc. (depending on which source control tool you use). At that point, the code from the local directory of the developer is inserted into the source control repository. Developer who wants to get the latest code need to request it from the source control tool. In database the change already exists and impacts other data even if it was not checked-in into the repository.
• During the file check-in, the source control tool performs a conflict check to see if the same file was modified and checked-in by another developer during the time you modified your local copy. Again there is no check for this in the database. If you alter a procedure from your local PC and at the same time I modify the same procedure with code form my local PC then we override each other's changes.
• The build process of code is done by getting the label / latest version of the code to an empty directory and then perform a build – compile. The output are binaries in which we copy & replace the existing. We don't care what was before. In database we cannot recreate the database as we need to maintain the data! Also the deployment executes SQL scripts which were generated in the build process.
• When executing the SQL scripts (with the DDL, DCL, DML (for static content) commands) you assume the current structure of the environment match the structure when you create the scripts. If not, then your scripts can fail as you are trying to add new column which already exists.
• Treating SQL scripts as code and manually generating them will cause syntax errors, database dependencies errors, scripts that are not reusable which complicate the task of developing, maintaining, testing those scripts. In addition, those scripts may run on an environment which is different from the one you though it would run on.
• Sometimes the script in the version control repository does not match the structure of the object that was tested and then errors will happen in production!

• 文件本地存储在开发人员的 PC 上，他/她所做的更改不会影响其他开发人员。同样，开发人员不受她同事所做更改的影响。在数据库中（通常）情况并非如此，开发人员共享相同的数据库环境，因此提交给数据库的任何更改都会影响其他人。
• 发布代码更改是使用 Check-In / Submit Changes / 等完成的（取决于您使用的源代码控制工具）。此时，来自开发人员本地目录的代码被插入到源代码控制存储库中。想要获取最新代码的开发人员需要从源代码控制工具中请求。在数据库中，更改已经存在并影响其他数据，即使它没有签入到存储库中。
• 在文件签入期间，源代码控制工具会执行冲突检查，以查看在您修改本地副本期间同一文件是否被其他开发人员修改和签入。同样，数据库中没有对此进行检查。如果您从本地 PC 更改程序，同时我使用来自本地 PC 的代码修改相同程序，那么我们将覆盖彼此的更改。
• 代码的构建过程是通过将代码的标签/最新版本放到一个空目录中，然后执行构建-编译来完成的。输出是我们复制和替换现有的二进制文件。我们不在乎以前是什么。在数据库中我们无法重新创建数据库，因为我们需要维护数据！此外，部署执行在构建过程中生成的 SQL 脚本。
• 在执行 SQL 脚本（使用 DDL、DCL、DML（用于静态内容）命令）时，您假定环境的当前结构与创建脚本时的结构相匹配。如果没有，那么您的脚本可能会在您尝试添加已存在的新列时失败。
• 将 SQL 脚本视为代码并手动生成它们会导致语法错误、数据库依赖错误、不可重用的脚本，这会使开发、维护和测试这些脚本的任务复杂化。此外，这些脚本可能运行在与您想运行的环境不同的环境中。
• 有时版本控制存储库中的脚本与测试对象的结构不匹配，然后在生产中会发生错误！

There are many more, but I think you got the picture.

还有更多，但我想你已经明白了。

What I found that works is the following:

我发现有效的是以下内容：

1. Use an enforced version control system that enforces check-out/check-in operations on the database objects. This will make sure the version control repository matches the code that was checked-in as it reads the metadata of the object in the check-in operation and not as a separated step done manually. This also allow several developers to work in parallel on the same database while preventing them to accidently override each other code.
2. Use an impact analysis that utilize baselines as part of the comparison to identify conflicts and identify if a difference (when comparing the object's structure between the source control repository and the database) is a real change that origin from development or a difference that was origin from a different path and then it should be skipped, such as different branch or an emergency fix.
3. Use a solution that knows how to perform Impact Analysis for many schemas at once, using UI or using API in order to eventually automate the build & deploy process.

1. 使用强制版本控制系统对数据库对象强制签出/签入操作。这将确保版本控制存储库与签入的代码匹配，因为它在签入操作中读取对象的元数据，而不是作为手动完成的单独步骤。这也允许多个开发人员在同一个数据库上并行工作，同时防止他们意外覆盖彼此的代码。
2. 使用利用基线作为比较的一部分的影响分析来识别冲突并确定差异（在比较源控制存储库和数据库之间的对象结构时）是源自开发的真正更改还是源自开发的差异不同的路径，然后应该跳过它，例如不同的分支或紧急修复。
3. 使用知道如何同时对多个模式执行影响分析的解决方案，使用 UI 或使用 API 以最终自动化构建和部署过程。

An article I wrote on this was published here, you are welcome to read it.

我这个写了一篇文章发表在这里，欢迎您阅读。

回答by Piotr Sobiegraj

You can store your database schema as Visual studio project using SQL Server Data Toolsand then version control this project using Git.

您可以使用SQL Server Data Tools将数据库架构存储为 Visual Studio 项目，然后使用 Git 对该项目进行版本控制。