CODEWITHSUNDEEP
gitversion-controlazure-devopssql-server-data-tools
Knightrider
Knightrider

Reputation: 15

Commit a sql server database to source control

I have two databases named 'xyz'(for example) on two different sql server instances. Both the xyz databases on the sql server instances have some database objects in common while some of them differ on both the instances.

I want to have a single copy of the xyz database stored on azure devops git repository .This copy should include all the objects from both of the sql server instances.

I then want to implement CI/CD using azure devops pipelines using the AzDo Git repository and deploy ONLY the database objects that are specific to a sql server instance- meaning even though the AzDo repo contains objects from the 'xyz' database from 2 different instances, the xyz database on each of the sql server instances should maintain its customized version when the build is triggered.

I am using the SQL Database Project in SSDT 2017 for scripting out the database objects.

I have tried the following to have a single copy of 2 different versions of a database.

1) create a solution in ssdt 2017 and create a sql database project. 2) right click the solution and click on schema compare. choose the first sql server instance having the xyz database as the source and the empty solution file as the target and click compare. The comparison shows that none of the structures are present in the solution file . Click the 'Update' button to update the solution file.

3) Next, I compared the xyz database from the second sql server instance as the source and the updated solution file as the target. Then i updated the objects from the 2nd sql server instance to the solution file.

Now the sql database project in ssdt has the objects from the xyz database from both the sql server instances.

Now, I have a single source of truth in AzDo Git Repository. But I do not know how to utilise this to deploy the respective objects to the respective sql server instances.(P.s - i do not want to have all the objects of xyz database on both the sql server instances. each database on each of the instance must maintain its customized version)

Please let me know if anything is unclear. Any help is appreciated.

Upvotes: 0

Views: 436

Answers (1)

Ed Elliott
Ed Elliott

Reputation: 6856

Create one project with the common code, then create two more projects and put the custom code in each.

From the two projects create a “same database” reference to the common code and when you deploy, deploy the custom code projects but specify the option to include composite projects.

Upvotes: 1

Related Questions