SQL部署自动化 [英] SQL deployment automation

问题描述

我正在尝试使用Jenkins自动化现有的sql服务器数据库部署自动化.

I am trying to automate existing sql server database deployment automation using Jenkins.

自动化的方法之一是使用Visual Studio在SQL Server数据库项目中包括SQL脚本(存储过程，视图，表创建).使用MSBuild生成项目，并使用SQLPackage.exe部署dacpac.但是，现有数据库已在存储过程/视图中引用了其他数据库，因此我已在SQL Server数据库项目中导入了其他数据库.另外，提取现有数据库的dacpac时会出现很多错误.我认为这是由于现有代码与dacpac不兼容，并且可能需要更改才能与dacpac兼容.

One of the ways to automate is to include the SQL scripts (stored procedures, views, table creation) in a SQL server Database project using Visual Studio. Use MSBuild to build the project and deploy dacpac using SQLPackage.exe. However, the existing database has reference to other databases inside Stored Procedures/Views so I have import other databases inside the SQL Server Database project. Additionally, I am getting a lot of errors when extracting dacpac of existing databases. I assume this is due to existing code is not dacpac compatible and may require changes to be dacpac compatible.

还有其他方法可以自动执行SQL Server代码部署，而不是使用SQL Server数据库项目吗?

Is there any other way to automate SQL server code deployment instead of using SQL Server database project?

推荐答案

基于SSDT模型的项目的替代方法是如

An alternative to SSDT model-based projects is a migration script approach as discussed here.

基于模型的部署需要在构建过程中验证依赖关系，否则您将在部署过程中冒犯错误的风险.不幸的是，我不知道要为不兼容dacpac的其他引用数据库创建dacpac的简单方法，而不需要为从源构建的引用数据库创建单独的项目而付出努力.您可以为引用的数据库创建数据库项目，并使用SSDT导入向导为项目源代码提取数据库的架构并清理解决方案.

Model based deployments need to validate dependencies during build, else you would run the risk of errors during deployment. Unfortunately, I know of no easy way to create a dacpac for other referenced databases that aren't dacpac-friendly without going through the effort of creating separate projects for referenced databases built from source. You could create a database project for referenced databases and use the SSDT import wizard to extract database the schema for the project source code and clean up the solution.

此任务的复杂性将根据您拥有的跨数据库和跨服务器依赖性的数量而有很大不同.为了避免循环引用，可能有必要将数据库分成单独的项目.

The complexity of this task will vary considerably depending on the number of cross-database and cross-server dependencies you have. It may be necessary to split databases into separate projects in order to avoid circular references.

这篇关于SQL部署自动化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！