maintaining different schemas across environments work seamlessly in the SQL database dacpac deployment, particularly for managing environment-specific configurations?

Question

I have quick question on dacpac deplpyment in Azure sql database deployment will maintaining different schemas across environments work seamlessly in the SQL database, particularly for managing environment-specific configurations?

One of the requirement involves keeping certain schemas and tables in the DEV environment without propagating them to other environments(qua and prd).

As understand Roles+permissions seamlessly works across environments with the help of dacpac deployment but is it possible to have different schema across the environment and not propagating it to elevated environments using the cicd pipeline(since we are using cicd pipeline for deploymeing roles and permission,tables,sample schema(sampleschema) into the Azure sql database

Answer 1

Hello Sandeep Kumar,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

Regarding your explanation and question, I understand that you are concerned about specific consideration in maintaining different schemas across environments in SQL database DACPAC deployment.

This can work seamlessly, particularly for managing environment-specific configurations. Based on your key points to consider:

• You can maintain different schemas across environments by using pre-deployment and post-deployment scripts in your DACPAC. Example of a script is here for your perusal - https://www.kenmuse.com/blog/devops-sql-server-dacpac-docker
• You can use SQLCMD variables in your DACPAC project to handle environment-specific configurations and configure your CI/CD pipeline to include steps that check the target environment and apply the appropriate DACPAC with the correct schema configurations - https://github.com/lluppesms/sql.dacpac.deploy
• The roles and permissions can be managed seamlessly across environments using DACPAC deployment, only need to include scripts to handle role and permission assignments, which can be applied consistently across all environments in your DACPAC Project - https://www.red-gate.com/hub/product-learning/flyway/using-ssdt-and-dacpacs-with-flyway-a-demonstration

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful