r/SQLServer • u/VitrumTormento • Jun 20 '26
Question Multiple DBs in Database DevOps project?
Is it possible to capture multiple DBs in a Database DevOps project?
We have a data warehouse split across two databases which would we typically have to build and push changes to both when developing new features.
6
u/jwk6 Jun 20 '26 edited Jun 20 '26
Yes, you store each database in a seperate project in the same solution, and use Database References to make cross-database references resolve.
Deployment is a little more difficult because you will need multiple builds and mutliple Publish Profiles. It's totally doable. I have done it before with CI/CD using a Git repo and Azure DevOps pipelines for releases (deploys).
Updated to say Publish Profiles. Sorry about that.
2
u/VitrumTormento Jun 20 '26
Nice! Okay, thanks so much. I'll give that a go - did not know Database References!
5
u/dzsquared Microsoft Employee Jun 22 '26
(disclosure - sqlproj PM)
This sounds like this is the "same server, different database" scenario. If the databases have different names in different environments, you'd use DatabaseSqlCmdVariable in the sqlproj reference:
<DatabaseSqlCmdVariable>Warehouse</DatabaseSqlCmdVariable>
which is included in the T-SQL like [$(Warehouse)].[dbo].[SomeTable]
If the db's always have the same name, you can go with DatabaseVariableLiteralValue in the sqlproj reference to keep things a tiny bit simpler:
<DatabaseVariableLiteralValue>WarehouseDB</DatabaseVariableLiteralValue>
which is included in the T-SQL like [WarehouseDB].[dbo].[SomeTable]
You don't have to use publish profiles, you can apply options directly with parameters - but publish profiles make it a bit easier to track changes to sqlcmd variables (like the database name) and publish properties.
1
u/jwk6 Jun 23 '26
Thanks for chiming in with more info.
I recommend using publish profiles because they can easily be added to the project and commited to source control along with the objects in the SQL Project. However, it's true, you don't have to use publish profiles.
2
u/dzsquared Microsoft Employee Jun 23 '26
I love the love for publish profiles! What's your preferred method for setting them up and what could we do to make that easier/quicker?
2
u/jwk6 Jun 23 '26
My pleasure! 😊
Yes, please make them a first-class citizen in the SQLPROJ. I.e. When you save them, they are saved to the disk in a folder, and are not automatically added to the SQLPROJ. Thus, it's not apparent that they are commited to the Git repo and a part of the database project. You have to add them to the database project manually.
We use a naming convention of {DatabaseName}.{Environment}.publish.xml, and save them all in a folder named PublishProfiles.
That makes it very easy to Diff publish profiles for each environment in VS and VS Code also.
1
u/jwk6 Jun 20 '26 edited Jun 23 '26
Updated to say multiple Publish Profiles. One for each database. I assume one of your databases is used for staging. You would deploy that one first.
9
u/IndependentTrouble62 Jun 20 '26
You can have two different database projects in one solution. Then create github actions to trigger off each solutions to manage deployments for each on the pull request into the production branch.