在Visual Studio 2017中与SQL Server数据工具的持续集成

Do you want to make your deployments risk free? Do you want to deliver business values to your customers faster? Do you want to increase the efficiency of your Team? Are your application and database deployment separate processes? Do you encounter issues while deploying your database changes? Is your database slowing you down?

您想使部署无风险吗？ 您想更快地为客户提供商业价值吗？ 您想提高团队效率吗？ 您的应用程序和数据库部署是分开的过程吗？ 在部署数据库更改时是否遇到问题？ 您的数据库在拖慢您的速度吗？

If the answer to any of the above questions is a Yes, then this article is a good starting point for you.

如果对以上任何问题的回答为“是”，那么本文对您来说是一个很好的起点。

The objective of this article is to help you understand the problems with traditional database development, why organizations are moving towards achieving Continuous Integration, the problems it tries to solve and learn about the toolsets which will assist you in this journey towards painless database deployments.

本文的目的是帮助您了解传统数据库开发的问题，组织为何要实现持续集成，它试图解决的问题并了解工具集，这些工具集将帮助您轻松进行数据库部署。

什么是持续集成 (What is Continuous Integration)

Continuous Integration is a development practice which encourages developers to check in code changes to source control as frequently as possible. Since the code is checked in and integrated more often in smaller pieces, it is easier to identify and resolve issues at an earlier stage. Every code commit triggers an automated build in a separate CI server and executes the unit and integration tests. Code Quality is maintained since any build which has failing tests are not allowed to get checked in to source control repository.

持续集成是一种开发实践，它鼓励开发人员尽可能频繁地将代码更改签入到源代码管理中。 由于将代码检入并更频繁地集成到较小的部分中，因此更容易在早期识别和解决问题。 每次提交代码都会在单独的CI服务器中触发自动构建，并执行单元测试和集成测试。 之所以能够保持代码质量，是因为任何测试失败的构建都不允许签入源代码控制存储库。

One important thing which you need to remember is that you cannot do Continuous Integration without Source control.

您需要记住的重要一件事是，如果没有源代码控制，就无法进行持续集成。

为什么需要持续集成 (Why is Continuous Integration required)

Risk Mitigation

风险缓解

With Continuous Integration, you will commit smaller pieces of code more frequently into source control. This will clearly lead to less merge conflicts and you will be able to catch issues more easily.

使用持续集成，您将更频繁地将较小的代码片段提交到源代码管理中。 显然，这将减少合并冲突，并且您将能够更轻松地捕获问题。

Shorter Feedback Loops

较短的反馈循环

With every code commit, the automated test suite is triggered which gives an immediate feedback to the developers if there are any issues with the latest check in.

每次提交代码时，都会触发自动测试套件，如果最新签入有任何问题，该套件将立即向开发人员提供反馈。

Increasing Team Efficiency

提高团队效率

Since code commits are done more frequently, Team spends less time in resolving merge conflicts and engaging in manual activities. This results in greater Team collaboration and awareness of processes.

由于代码提交的执行频率更高，因此Team在解决合并冲突和进行手动活动上花费的时间更少。 这样可以增强团队协作和流程意识。

Faster Delivery of Business value

更快交付业务价值

The iterative model ensures that there are no last minute surprises for the customer. Since the changes are pushed to Production frequently, Business can verify the functionality more often. With shorter feedback cycles, development speed will also increase.

迭代模型可确保为客户提供最后的惊喜。 由于更改经常被推送到生产环境，因此业务部门可以更频繁地验证功能。 随着反馈周期的缩短，开发速度也将提高。

传统数据库开发有什么问题 (What are the problems with traditional Database Development)

Data is hard to manage. You can overwrite and redeploy your code from scratch every time, but the same cannot be done with data. Databases have existing data which needs to be persisted and hence it cannot be wiped off and recreated during deployments – just like the code.

数据难以管理。 您可以每次从头开始重写并重新部署代码，但是对数据则无法做到这一点。 数据库具有需要保留的现有数据，因此无法像部署代码那样在部署期间将其擦除和重新创建。

Having no Source Control for your database objects implies that there is no versioning information available for your database changes. Production is the version and system of truth. This makes it almost impossible to unit test your changes and automate your deployments.

对数据库对象没有源代码管理意味着没有版本信息可用于数据库更改。 生产是真理的版本和系统。 这样几乎就不可能对更改进行单元测试并自动进行部署。

Developers have to spend a lot of time in creating the DML scripts. They also need to account for the tedious rollback scripts, in case the overall deployment had to be rolled back. The Database Administrator has to spend a lot of time in executing scripts against multiple environments.

开发人员必须花费大量时间来创建DML脚本。 他们还需要考虑乏味的回滚脚本，以防整个部署必须回滚。 数据库管理员必须花费大量时间在多个环境下执行脚本。

Removing manual work and inefficiencies in your database development process is one of the reasons why organizations are moving towards automating their database deployments.

消除数据库开发过程中的手动工作和效率低下的原因是组织朝着使其数据库部署自动化的原因之一。

什么是SQL Server数据工具 (What is SQL Server Data Tools)

SQL Server Data Tools provides an integrated development environment inside Visual Studio with the rich set of database tools and features to complement database development. Be it a T-SQL Developer or DBA, you spend most of your time inside SQL Server Management Studio to interact with your SQL Server instance. Most of the developer capabilities and functionalities that SQL Server Management Studio provides are now available within SSDT, hence enabling developers to perform tasks like creating tables, views, stored procedures and modifying them.  

SQL Server数据工具在Visual Studio中提供了一个集成的开发环境，其中包含丰富的数据库工具和功能集，以补充数据库开发。 无论是T-SQL开发人员还是DBA，您都将大部分时间花在SQL Server Management Studio中与您SQL Server实例进行交互。 现在，SSDT内提供了SQL Server Management Studio提供的大多数开发人员功能，因此，开发人员可以执行诸如创建表，视图，存储过程和修改它们的任务。

如何在Visual Studio 2017中安装SQL Server数据工具 (How to install SQL Server Data Tools in Visual Studio 2017)

Visual Studio 2017 provides a new installation experience, where you have the option to select and install features based upon your requirements – hence ensuring that you are installing only the components which you need and not every default component which comes with Visual Studio. This helps in decreasing the install size and also making the installation process a lot faster since no unnecessary components are installed.

Visual Studio 2017提供了新的安装体验，您可以根据自己的要求选择和安装功能，从而确保仅安装所需的组件，而不安装Visual Studio随附的每个默认组件。 由于没有安装任何不必要的组件，因此这有助于减小安装尺寸，并有助于加快安装过程。

To install SQL Server Data Tools you have 2 options. You can select the Data storage and processing workload while installation:

要安装SQL Server数据工具，您有2个选项。 您可以在安装时选择数据存储和处理工作负载 ：

You can also install SQL Server Data Tools as an Individual component:

您还可以将SQL Server数据工具安装为单个组件：

SQL Server数据工具的基本功能是什么 (What are the basic capabilities of SQL Server Data Tools)

Version Control

版本控制

Using a Database Project – a special type of Visual Studio project – you can put your entire database schema under source control. All database objects like tables, stored procedures, and user-defined types are stored as individual T-SQL source files inside the project which can be searched and modified in source control just like your code files.

使用数据库项目（Visual Studio项目的一种特殊类型），您可以将整个数据库架构置于源代码控制之下。 所有数据库对象（例如表，存储过程和用户​​定义的类型）都作为单独的T-SQL源文件存储在项目内，可以像代码文件一样在源代码管理中对其进行搜索和修改。

Disconnected Database Development

断开数据库开发

SSDT allows you to maintain a local database and do all the development using that. It is important to understand that you are working with a Database Project inside Visual Project and not against the connected instance of your database. This is what is termed as ‘Offline/Disconnected support for database development’ with Database Projects. The Database projects contain all the database objects stored as separate T-SQL source files inside the project. You can merge your changes to the target database at a later point in time.

SSDT允许您维护本地数据库并使用该数据库进行所有开发。 重要的是要了解，您正在Visual Project中使用数据库项目，而不是针对数据库的连接实例。 这就是所谓的数据库项目“ 对数据库开发的脱机/断开连接支持 ”。 数据库项目包含所有数据库对象，这些对象存储为项目内单独的T-SQL源文件。 您可以在以后的某个时间将更改合并到目标数据库。

Declarative approach to Database Development

声明式数据库开发方法

SQL Server Data Tools follow a State based approach – which means that the source of truth is the database schema inside source control and not the live database. As a developer, you don’t have to deal with writing cumbersome ALTER scripts. All you need to do is to mention the state you want your database to be, and SSDT takes care of the rest. It is intelligent enough to compare the modified state of the database in source control with the target database and generate difference scripts to get both the environments in sync.

SQL Server数据工具遵循基于状态的方法 -这意味着真实来源是源代码控制内部的数据库架构，而不是实时数据库。 作为开发人员，您不必处理编写繁琐的ALTER脚本。 您所需要做的就是提及您希望数据库成为的状态，SSDT负责其余的工作。 它足够智能，可以将源代码管理中数据库的修改状态与目标数据库进行比较，并生成差异脚本以使两个环境同步。

DACPAC Deployments

DACPAC部署

DACPAC is a self-contained deployment file which is used for deploying SQL Server objects to an instance of SQL Server. You can also think of DACPAC to be like a database snapshot file, which can serve as the in-memory representation of database objects and can be stored for maintaining version history.

DACPAC是一个自包含的部署文件，用于将SQL Server对象部署到SQL Server实例。 您也可以将DACPAC视为数据库快照文件，它可以用作数据库对象的内存表示形式，并且可以存储以维护版本历史记录。

When the DACPAC is deployed, it uses the information in the DACPAC file as the source database schema. It compares this with the defined target to generate an appropriate change script, which is then executed against the target to sync up both the environments.

部署DACPAC时，它将DACPAC文件中的信息用作源数据库架构。 它将其与定义的目标进行比较以生成适当的更改脚本，然后针对目标执行该脚本以同步两个环境。

Enhanced Code Editing Experience

增强的代码编辑体验

SQL Server Object Explorer is a new window provided inside Visual Studio IDE, which provides developers with the same user experience as SQL Server Management Studio. The new Table Designer enables developers to see the design view and code view of your database objects in the same screen, which is very helpful. You have the option to either modify inside the code view or the design view, and irrespective of which view you make the change, the other view gets updated immediately.

SQL Server对象资源管理器是Visual Studio IDE中提供的一个新窗口，它为开发人员提供了与SQL Server Management Studio相同的用户体验。 新的表设计器使开发人员可以在同一屏幕上查看数据库对象的设计视图和代码视图，这非常有帮助。 您可以选择在代码视图中还是在设计视图中进行修改，并且无论您进行更改的是哪个视图，另一个视图都会立即更新。

Schema Comparison

模式比较

SSDT provides the ability to identify differences between any 2 database models – which can be a combination of Offline Database Projects, Connected Database or Database Snapshot file.

SSDT能够识别任何两种数据库模型之间的差异，这些模型可以是脱机数据库项目，连接的数据库或数据库快照文件的组合 。

SSDT generates a difference script which can be executed against the target so that it matches the state of the source database. As part of the Schema Comparison functionality, it displays the list of objects which are different between the source and target database, and you have the option to include/exclude the objects before generating the change script.

SSDT生成一个差异脚本，可以针对目标服务器执行该脚本，以使其与源数据库的状态匹配。 作为模式比较功能的一部分，它显示源数据库和目标数据库之间不同的对象列表，并且您可以选择在生成更改脚本之前包括/排除对象。

Data Comparison

数据比较

SSDT lets you analyze the data differences between the source and target databases and generate a change script to update the target database to match with the source.

SSDT使您可以分析源数据库和目标数据库之间的数据差异，并生成更改脚本以更新目标数据库以与源数据库匹配。

This functionality is very helpful when you want to sync up reference/ look up tables between different database environments.

当您要在不同数据库环境之间同步参考/查找表时，此功能非常有用。

结论 (Conclusion)

Continuous Integration has become an important practice for modern application and database development projects. SQL Server Data Tools helps to transform traditional database development into a modern declarative model inside Visual Studio environment. Using SQL Server Data Tools you can put your database schema into source control. And once you do that, you can rest assured that you have taken that very important first step towards the DevOps journey.

持续集成已成为现代应用程序和数据库开发项目的重要实践。 SQL Server数据工具有助于在Visual Studio环境中将传统的数据库开发转换为现代的声明式模型。 使用SQL Server数据工具，可以将数据库架构放入源代码管理中。 这样做之后，您可以放心，您已迈出了非常重要的第一步，迈向DevOps旅程。

翻译自: https://www.sqlshack.com/continuous-integration-sql-server-data-tools-visual-studio-2017/