0

I have a SQL Server database project (.sqlproj) which I am using as part of a CI/CD pipeline to deploy database changes. I would like to deploy the same code to two databases (Dev and Production) but each with a slightly different configuration:

In Dev, I have an Azure AD group Database-Dev-Developers:

CREATE USER [Database-Dev-Developers] FOR EXTERNAL PROVIDER;

In Production, I have an Azure AD group Database-Prod-Developers:

CREATE USER [Database-Prod-Developers] FOR EXTERNAL PROVIDER;

I can find no way to alter which scripts are build/published based on the configuration. Ideally I'd like to be able to specify the project configuration at build time (Debug/Release), which changes the output.

I have tried adding conditional expressions for the relevant files in the .sqlproj file, but this has no effect:

Condition=" '$(Configuration)' == 'Debug' "
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Nick
  • 685
  • 12
  • 27

2 Answers2

0

You should look into using a Token Replacement step in your pipeline. You can add different variable values for Dev vs Prod to replace the tokens with. Then you just need one tokenized configuration file that can be used for both Dev and Prod.

I'm not exactly sure how kosher it is to use tokens in a .sqlproj file, it depends on what configurations you're trying to replace. But I've seen it used very successfully on ...config.json files in modern .NET Core based projects.

Another thing you can look into is File Transformations. I don't have any experience using these though.

J.D.
  • 954
  • 6
  • 22
0

I have found a partial solution to this problem. One can create a publish profile, which contains instructions to ignore certain object types. See this helpful blog post which details the process, summarised below:

  1. In Visual Studio, right-click SSDT project
  2. Publish -> Advanced
  3. Select the 'drop' tab, and check 'Drop objects in target but not in source'
  4. Check 'Do not drop...' next to the object types you wish to ignore. For me this was 'Do not drop users' and 'Do not drop roles'
  5. Save the publish profile

Extra step for Azure DevOps Azure SQL Database deployment task, specify the generated publish profile xml file in the 'Publish Profile' setting.

This has the drawback that non-sensitive security settings (such as role membership) cannot be deployed, but this was a trade-off I was willing to make in my situation.

Nick
  • 685
  • 12
  • 27