如何使用 SQL Server 数据库项目 [英] How to use SQL Server Database Project

问题描述

我正在运行 SQL Server 2012 和 VS 2010，并安装了 SSDT(SQL Server Data Tools).我的开发数据库使用存储过程、函数、CLR 对象等.它有大约 500GB 的生产数据快照.

I am running SQL Server 2012 and VS 2010 with SSDT (SQL Server Data Tools) installed. My dev DB uses stored procs, functions, CLR objects, etc. It has a snapshot of prod data of about 500GB.

我创建了 SQL Server 数据库项目，然后导入了数据库.这在模式名称下创建了所有表、视图、过程和函数文件.很棒的东西——现在我可以像在其他 VS 项目中一样进行版本控制、创建部署等.到目前为止，一切都很好.

I created SQL Server Database Project and then imported the database. This created all tables, views, procs and functions files under schema names. Great stuff -- now I can do a version control just like in other VS projects, create deployments, etc. So far, so good.

但是，我对在 SQL Server 数据库项目下更改/添加过程/表的开发过程应该是什么感到困惑.看来我所做的任何更改都应用于某些 LocalDb/Projects 数据库，而不是我的开发数据库.

But, I am confused as to what my development process should be for changing/adding procs/tables under SQL Server Database Project. It appears that any changes I make are applied to some LocalDb/Projects database and NOT to my dev database.

我是否想在该 LocalDb 中创作我的所有对象，然后通过发布构建并部署到我的开发数据库?我担心开发数据库中的现有表，因为如果发布过程删除并重新创建表，我将丢失生产数据快照.

Am I suppose to author all my objects in that LocalDb, then Build and deploy to my dev database via Publish? I am worried about my existing tables in the dev DB since if the publish process drops and recreates tables, I will loose my prod data snapshot.

在 SQL Server 数据库项目中应遵循的正确开发流程是什么?

What is the right development process to follow in SQL Server Database Project?

推荐答案

将源数据库(在您的情况下，您的数据库项目)视为部署后的待定"状态.启动部署时，可执行文件 (SqlPackage.exe) 将源与目标进行比较，并生成差异/增量脚本以使目标看起来像源.这就是为什么我们不再需要指定 CREATE 或 ALTER；该工具计算出来.要回答有关持续开发的问题，您可以采用任何一种方式进行开发.您可以在项目文件中进行开发并将它们发布到公共 Dev 数据库(例如，如果您在一个团队中)，或者您可以使用 SQL Server Management Studio (SSMS) 等工具在数据库中进行开发并与项目文件同步与模式比较(我使用后一种技术，因为我喜欢 SSMS).

Think of the source database (in your case, your database project) as being the "to be" state after deployment. When a deployment is initiated, the executable (SqlPackage.exe) compares the source with the target and generates a difference/delta script to make the target look like the source. This is why we no longer have to specify CREATE or ALTER; the tool figures it out. To answer your question about ongoing development, you can develop either way. You can develop in the project files and publish them to a common Dev database (say, if you're on a team), or you can develop in the database with tools like SQL Server Management Studio (SSMS) and synchronize with the project files with a schema compare (I use the latter technique because I like SSMS).

对于部署，您必须在执行部署的计算机上安装 SSDT(SSDT 随 SQL Server 2012 及更高版本一起提供；我不了解 SQL Server 2008).您可以创建脚本来简化部署.您实际上将使用操作和源调用 SqlPackage.exe(它位于 x:\Program Files (x86)\Microsoft SQL Server\nnn\DAC\bin).我也使用发布配置文件来处理大多数命令属性.因此，示例部署可能如下所示:

For deployment, you'll have to have SSDT installed on the machine from which you execute the deployment (SSDT ships with SQL Server 2012 and later; I don't know about SQL Server 2008). You can create scripts to simplify deployment. You'll essentially call SqlPackage.exe (it lives in x:\Program Files (x86)\Microsoft SQL Server\nnn\DAC\bin) with an action and a source. I use Publish Profiles as well to take care of most command properties. So an example deployment might look like this:

SqlPackage.exe /Action:Publish /SourceFile:MyDatabase.dacpac /Profile:MyProfile.publish.xml

欲了解更多信息:SQL Server 数据工具文档http://msdn.microsoft.com/en-us/library/hh272686(v=vs.103).aspx

For more information: SQL Server Data Tools Documentation http://msdn.microsoft.com/en-us/library/hh272686(v=vs.103).aspx

SqlPackage.exe 文档http://msdn.microsoft.com/en-us/library/hh550080(v=vs.103).aspx

SqlPackage.exe Documentation http://msdn.microsoft.com/en-us/library/hh550080(v=vs.103).aspx

这篇关于如何使用 SQL Server 数据库项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！