Run Schema Compare from Azure DevOps pipeline to update SQL database project

user_7596217 0
Viewed 2187

We have a SQL database project that we use to deploy changes into our TEST and PROD environments. During the day developers make changes on the DEV database directly (do not use database project). Periodically, a schema compare is done within the database project to collect all the changes made by developers in the DEV database so they can be applied to TEST environment via the Azure release pipeline.

This all works great from Azure Pipelines, but we would like to create a pipeline that automatically runs the schema compare between DEV database and applies the changes to the database project (we have to do this manually). Years ago I looked into this and the command line did not support having the database project be the target of the schema compare. Does anyone know if this type of workflow is possible these days?

sql-server-data-tools azure-pipelines azure-devops
2 Answers

As Daniel mentioned, it`s not a good idea to use dev environments without database project. However, there are cases when we have to work in a common database because of sandbox prices, test data, or any other constraints. In this case too complex to check when your DEV database is ready to sync changes because it may contain raw changes. Consider using the process: your developers add changes to the database project under source control. They can change DEV database directly but each developer commits only its work scope to the source control. In this case, you can see changes for each user story and bug in the source control and link them to corresponding work items if needed. Additionally, you can add an integration database to check the consistency of new changes:

DEV (manually + commit to SC) -> INT (pipelines CI/CD) -> TEST -> PROD

As additional ideas:

  1. You may try to create a Coded UI test or Selenim test to press buttons on VS and update your database project. Then you can add it to a pipeline and update your SC every night.
  2. SqlPackage.exe generates an update script Database->DacPac file. However, there are no ways to apply it to a database project.
user_82359710

I agree with Daniel, running database changes directly in DEV database should be avoided. You could try using sql script as the sample in this case:

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
user_54258250
Related