从Azure DevOps管道运行模式比较以更新SQL数据库项目。

Question

我们有一个SQL数据库项目，用于将更改部署到我们的测试和生产环境。在白天，开发人员直接在DEV数据库上进行更改（不使用数据库项目）。定期，在数据库项目内进行模式比较，以收集开发人员在DEV数据库中进行的所有更改，以便通过Azure发布流水线将其应用于TEST环境。

这一切都可以在Azure Pipelines中完美运行，但我们希望创建一个流水线，自动在DEV数据库之间运行模式比较，并将更改应用于数据库项目（我们必须手动执行此操作）。多年前，我研究过这个问题，当时命令行不支持将数据库项目作为模式比较的目标。现在是否有可能实现这种工作流程呢？

- Geekn

---

白天开发人员直接在DEV数据库上进行更改（不使用数据库项目）。停止这样做。您的持续交付流程应该在所有环境中完全相同，包括开发环境。如果开发人员需要开发数据库模式更改，则应在本地副本中执行此操作，并在提交其代码之前更新数据库项目。 - Daniel Mann

Answer 1

正如Daniel所提到的，没有数据库项目的开发环境并不是一个好主意。然而，由于沙箱价格、测试数据或其他限制，有时我们不得不在共同的数据库中工作。在这种情况下，检查你的DEV数据库何时准备好同步更改变得过于复杂，因为它可能包含原始更改。考虑使用以下过程：你的开发人员在源代码控制下向数据库项目添加更改。他们可以直接更改DEV数据库，但每个开发人员只提交其工作范围到源代码控制。在这种情况下，您可以在源代码控制中查看每个用户故事和错误的更改，并在需要时将它们链接到相应的工作项。此外，您还可以添加一个集成数据库来检查新更改的一致性： DEV (手动+提交到SC) -> INT (管道CI/CD) -> TEST -> PROD 其他想法包括：

1. 您可以尝试创建一个Coded UI测试或Selenim测试，在VS上按按钮并更新您的数据库项目。然后，您可以将其添加到管道中，并每晚更新您的SC。
2. SqlPackage.exe 生成一个更新脚本 Database->DacPac文件。然而，没有办法将其应用于数据库项目。

- Shamrai Aleksander

---

谢谢。我同意尽可能避免这种做法。在这种情况下，我们无法指示客户开发团队如何进行开发工作。听起来没有自动化运行模式比较数据库项目的目标，所以这基本上回答了问题。感谢您的见解。当我有更多时间时，我可能会考虑使用编码 UI 测试方法。 - Geekn

Answer 2

我同意Daniel的观点，应该避免直接在DEV数据库中运行数据库更改。您可以尝试使用sql脚本作为这个案例的示例：

set nocount on;
-- Set the two variables newmodel and oldmodel to the appropriate database names and execute the script

declare @newmodel varchar(50), @oldmodel varchar(50);

Set @newmodel = '[NewModel to Compare]';
set @oldmodel = '[OldModel to Compare]';


Declare @Temp table (TABLE_SCHEMA varchar(40), TABLE_NAME varchar(40), COLUMN_NAME varchar(50), ORDINAL_POSITION int, IS_NULLABLE varchar(5), NullChange varchar(5), Comment varchar(50));

Declare @script varchar(5000);


set @script = '
Select nc.TABLE_SCHEMA, nc.TABLE_NAME, nc.COLUMN_NAME, nc.ORDINAL_POSITION, nc.IS_NULLABLE, IIF(nc.IS_NULLABLE <> oc.IS_NULLABLE, ''Yes'', ''No''), 
        IIF(oc.COLUMN_NAME IS NULL, convert(varchar(20), ''ADDED COLUMN''), convert(varchar(20), ''--'')) as Comment
    from {NEW}.INFORMATION_SCHEMA.COLUMNS nc
        LEFT join {OLD}.INFORMATION_SCHEMA.COLUMNS oc 
            on nc.TABLE_NAME = oc.TABLE_NAME and nc.COLUMN_NAME = oc.COLUMN_NAME
UNION ALL
    Select oc.TABLE_SCHEMA, oc.TABLE_NAME, oc.COLUMN_NAME, oc.ORDINAL_POSITION, oc.IS_NULLABLE, ''No'', ''DELETED COLUMN'' as Comment
    from {OLD}.INFORMATION_SCHEMA.COLUMNS oc
    where CONCAT(oc.TABLE_NAME, ''.'', oc.COLUMN_NAME) 
        not in (Select CONCAT(TABLE_NAME, ''.'', COLUMN_NAME) from {NEW}.INFORMATION_SCHEMA.COLUMNS)
';


Set @script = replace(@script, '{OLD}', @oldmodel);
Set @script = replace(@script, '{NEW}', @newmodel);

--print @script

Insert into @Temp
    exec(@script);

Select * from @Temp where Comment <> '--'
order by TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME;
go

- Cece Dong - MSFT