deploy only a new schema as a separate database project without affecting existing production DB code

Question

We have a production database (SQL Server 2019) with existing code (tables, procedures, functions, synonyms) that was deployed without a source repository. Our client wants to introduce a new schema with new code, but keep all the old code and data intact.

We plan to deploy the database changes via Azure DevOps pipeline - SQL Database Project in VS (new approach for all new changes and projects).

How do we create and deploy a database project solution that contains only the new schema and its related code, without including or modifying the existing database objects?

We want to avoid touching the old schema/code to prevent any risk or downtime. The new schema should be deployed independently, coexisting with the old schema and code.

What tools handle such partial database deployments?

How do we manage this in source control and CI/CD pipelines, especially considering the old code is not in a repo?

Answer 1

We do this in our environment now and we don't have any issues. We don't use Dev Ops pipelines, but we do have all of our stuff in Visual Studio projects with source control backing them.

We have one project with the dbo schema objects, and this one lives and dies with the application code.

We have another project (actually two), with different schemas in them, one is Reporting, because it needed a different release cycle than tied directly to the application.

The only thing to be careful of is to make sure you uncheck the box "drop items that don't exist" (or whatever it's called). when deployments are made. Basically, making sure that only new or changed objects get deployed and artifacts that are in the target database don't get dropped.