使用SQL Server数据工具和Visual Studio Online进行连续部署

In the previous posts

在以前的帖子中

Deployment to several databases using SQL Server Data Tools and TFS using a Custom Workflow file
Deployment to several databases using SQL Server Data Tools and Team foundation Server
Continuous integration with SQL Server Data Tools and Team Foundation Server

使用SQL Server数据工具和使用自定义工作流文件的TFS部署到多个数据库
使用SQL Server数据工具和Team Foundation Server部署到多个数据库
与SQL Server数据工具和Team Foundation Server的持续集成

I have been mostly writing about the interaction between SQL Server Data Tools and Team Foundation Server. Microsoft provides a hosted version of Team Foundation Build Service called Visual Studio Online. The configuration and functionality is mostly the same than what I have previously been writing about but there are some specifics things that we need to be aware when using Visual Studio Online Build Service.

我主要是写有关SQL Server数据工具和Team Foundation Server之间的交互的文章。 Microsoft提供了一个称为Visual Studio Online的Team Foundation Build Service的托管版本。 其配置和功能与我之前编写的内容和功能基本相同，但是使用Visual Studio Online Build Service时需要注意一些特定的事项。

什么是Visual Studio Online？ (What is Visual Studio Online? )

Visual Studio Online is a cloud-based code repository, which is based on the capabilities of Team Foundation Server with additional cloud services. It can help you getting up and running without the need for infrastructure. When you create a Visual Studio Online account it automatically comes with 5 free users. From there, if your team grows you are able to expand as needed.

Visual Studio Online是基于云的代码存储库，它基于Team Foundation Server的功能以及其他云服务。 它可以帮助您无需基础架构即可启动和运行。 创建Visual Studio Online帐户时，该帐户会自动具有5个免费用户。 从那里开始，如果您的团队不断壮大，您就可以根据需要进行扩展。

If you have Visual Studio with an MSDN subscription, you are free to join online projects and it won’t count for one of the five subscribers.

如果您拥有带有MSDN订阅的Visual Studio，则可以自由加入在线项目，并且不计入五个订阅者之一。

Visual Studio Online的局限性 (Limitations of Visual Studio Online)

Seen from a database development perspective there are some limitations inherent to the fact that Visual Studio Online is a cloud software.

从数据库开发的角度来看，Visual Studio Online是一种云软件这一事实具有固有的局限性。

The first limitation might the version of SQL Server Data Tools that its supports. Since it is a cloud-based software Microsoft have the opportunity to update the product on another basis than the releases of the on-premises products. For some time the version running in the Cloud was the equivalent version of Visual Studio Team Foundation Server 2012. Which meant:

第一个限制可能是其支持SQL Server数据工具的版本。 由于它是基于云的软件，因此Microsoft有机会在不同于本地产品发布的其他基础上更新产品。 在一段时间内，在云中运行的版本与Visual Studio Team Foundation Server 2012的等效版本相同。这意味着：

• Another default build template: thus giving some limitations to the scenarios I have been describing in the previous blog posts (like the ability to call a batch file upon post-deployment)
  另一个默认的构建模板：因此，对我在之前的博客文章中描述的场景有一些限制（例如在部署后可以调用批处理文件的功能）
• The version of SQL Server Data Tools installed on the Visual Studio Online hosted build agent might also be different than the version you are running on-premises. When a new version of SQL Server Data Tools is released it doesn’t automatically makes it to Visual Studio Online
  安装在Visual Studio Online托管的构建代理上SQL Server数据工具的版本也可能与您在本地运行的版本不同。 当发布新版本SQL Server数据工具时，它不会自动进入Visual Studio Online。
• Even if newer version of SQL Server Data Tools (mainly) maintain backward compatibility you might not be able to use new features until the right version has been deployed on the build agent.
  即使较新版本SQL Server数据工具（主要）保持了向后兼容性，您也可能无法使用新功能，除非已在构建代理上部署了正确的版本。

The most important of these limitations is the fact that – from the cloud – you are not able to use Windows Authentication to deploy to your local database instance. Furthermore, you need to use a local build agent to be able to deploy to a database on-premises.

这些限制中最重要的是-您无法从云中使用Windows身份验证来部署到本地数据库实例。 此外，您需要使用本地构建代理才能将其部署到本地数据库中。

使用托管的构建代理从Visual Studio Online进行部署 (Using a hosted build agent to deploy from Visual Studio Online)

First of all you’ll need a Visual Studio Onlne account. This is easy to register for and you can get started from there : http://www.visualstudio.com/en-us/products/what-is-visual-studio-online-vs.aspx

首先，您需要一个Visual Studio Onlne帐户。 这很容易注册，您可以从那里开始： http : //www.visualstudio.com/en-us/products/what-is-visual-studio-online-vs.aspx

With the free account, you’ll get :

使用免费帐户，您将获得：

• FREE Basic user licenses免费的基本用户许可证
• Unlimited stakeholders
  无限的利益相关者
• Unlimited eligible MSDN subscribers
  无限的合格MSDN订阅者
• Unlimited team projects and private code repos
  无限的团队项目和私人代码存储库
• FREE work item tracking for all users免费的工作项目跟踪
• FREE 60 minutes/month of build免费 60分钟构建
• FREE 20K virtual user minutes/month of load testing免费 20K虚拟用户分钟/月的负载测试
• PREVIEW application monitoring and analytics预览应用程序监视和分析

From your Visual Studio choose Team ➜ Connect to Team Foundation Server and when prompted add the URL to your newly created server. From there it is only a matter of finding the right team project.

在Visual Studio中，选择“团队➜连接到Team Foundation Server”，然后在出现提示时将URL添加到新创建的服务器。 从那里仅是找到合适的团队项目的问题。

构建模板 (The build template)

IF you have read the previous blog posts, you should be aware of the changes necessary for the build template to work. If not here they come:

如果您已阅读以前的博客文章，则应了解构建模板起作用所需的更改。 如果不是，他们会来：

1. Trigger pane choose 触发器”窗格中，选择“ Continuous Integration连续积分”
2. Source Settings pick the project you want to build 源设置”中，选择要构建的项目
3. Build Default构建设置 s is where you choose the Build Controller, and this is where it gets tricky, please read ahead.S是您选择生成器，这是它得到棘手的，请读。
4. Process pane you can specify which MSBuild arguments you need for the Continuous Integration to perform its work.过程”窗格中，可以指定持续集成执行其工作所需的MSBuild参数。

生成默认值 (The Build Defaults)

In the Build Defaults, you need to pick the Build controller that you will need to deploy your changes. By default, Visual Studio Online will pick the Hosted Build Controller but it is not the right choice since this controller runs in the Cloud which probably doesn’t have access to your on-premises database. Should you wish to deploy to an Azure SQL Database or to an Azure VM this is a different story. But for now, let us focus on deploying to on-premises resources.

在Build Defaults中，您需要选择部署更改所需要的Build控制器。 默认情况下，Visual Studio Online将选择“托管生成控制器”，但这不是正确的选择，因为该控制器在云中运行，而云可能无法访问本地数据库。 如果您希望部署到Azure SQL数据库或Azure VM，则完全不同。 但是，现在，让我们集中精力部署到本地资源。

You will need to change the Build Controller to another instance. The available instances are controlled by the Team Foundation Administration Console. This is where the troubles begin! (at least in my case).

您将需要将构建控制器更改为另一个实例。 可用实例由Team Foundation管理控制台控制。 这是麻烦开始的地方！ （至少就我而言）。

See, I have a local installation of Team Foundation Server Express 2013

请参阅，我在本地安装了Team Foundation Server Express 2013

Which does not correspond to the version of Team Foundation Server running on Visual Studio Online, which in turn means that I cannot use another Build Controller.

这与Visual Studio Online上运行的Team Foundation Server的版本不对应，这又意味着我不能使用其他构建控制器。

If we abstract from that fact for one second and suppose that I have a local version of the Build Controller that is compatible with Visual Studio Online, I would be able to pick this Build Controller.

如果我们从这一事实中抽象一秒钟，并假设我拥有与Visual Studio Online兼容的本地版本的构建控制器，则可以选择该构建控制器。

After picking this Build Controller I move on to the next step in the configuration : The Process

选择此构建控制器后，我继续进行配置的下一步： 过程

For the Process, I do not have all the possibilities that I have described in my previous posts since it is a template based on Team Foundation Server 2012 and not 2013. The figure below shows clearly what’s missing:

对于该流程 ，由于它是基于Team Foundation Server 2012而不是2013的模板，因此我没有以前的文章中描述的所有可能性。下图清楚地说明了缺少的内容：

As previously mentioned Team Foundation Server 2012 doesn’t provide the ability to work with Post-build scripts (and Pre-build scripts for that matter). So no calling SQLPackage.exe here.

如前所述，Team Foundation Server 2012不提供使用后生成脚本（以及与此相关的预生成脚本）的功能。 因此，此处无需调用SQLPackage.exe。

(A possibility would be to create a custom Process Template and add an SQLPackage step).

（一种可能是创建自定义流程模板并添加SQLPackage步骤）。

For now I want to keep it simple and uses those arguments as the MSBuild arguments:

现在，我想保持简单，并将这些参数用作MSBuild参数：

/t:Build;Publish /p:SqlPublishProfilePath=ContinuousDeploymentVSOnline.publish.xml

/ t：构建;发布/p:SqlPublishProfilePath=ContinuousDeploymentVSOnline.publish.xml

Telling MSBuild that I want to Publish using the details provided in my publish.xml file. Since I cannot use Windows Authentication I have to provide a username and password in my publish.xml file.

告诉MSBuild我想使用publish.xml文件中提供的详细信息进行发布。 由于无法使用Windows身份验证，因此必须在publish.xml文件中提供用户名和密码。

Please remember that when you create the publish.xml file and even if you specifically check the box asking you if you want to save the password, the password will not be saved.

请记住，当您创建publish.xml文件时，即使您特别选中询问您是否要保存密码的框，也不会保存密码。

You will need to explicitly edit the file in an XML editor and add the password yourself:

您将需要在XML编辑器中显式编辑文件，然后自己添加密码：

结论 (Conclusion)

In this last post in my series about Continuous Integration, we have seen how to use Visual Studio Online. It does not have feature complete than Team Foundation Server 2013 but it is a great tool for smaller projects that may or may not require CI. There are many other features to Visual Studio Online as they are many more features to Team Foundation Server. These features are not in scope for this posts about CI, but who knows, maybe in another series, I will have the opportunity to explore the more project related components of TFS online and on-premises.

在我有关持续集成的系列的最后一篇文章中，我们已经了解了如何使用Visual Studio Online。 它没有Team Foundation Server 2013完整的功能，但是对于不需要CI的小型项目来说，它是一个很好的工具。 Visual Studio Online还有许多其他功能，因为Team Foundation Server还有许多其他功能。 这些功能不在有关CI的这篇文章的讨论范围之内，但是谁知道，也许在另一个系列中，我将有机会在线和内部探讨TFS中与项目相关的更多组件。

In the meantime, happy deploying !!

同时，部署愉快！

翻译自: https://www.sqlshack.com/continuous-deployment-using-sql-server-data-tools-visual-studio-online/