1

Task:

  1. Automate database deployment (SSDT/dacpac deployment with CI/CD)
  2. The database is a 3rd party database
  3. It also includes our own customized tables/SP/Fn/Views in separate schemas
  4. Should exclude 3rd party objects while deploying the database project(dacpac) to Production
  5. Thanks to Ed Elliott for the AgileSqlClub.DeploymentFilterContributor. Used the dll to filter out the schema successfully.

Problem:

  1. The 3rd party schema objects(Tables) are defined with unnamed constraints(default / primary key) when creating the tables. Example:
CREATE TABLE [3rdParty].[MainTable] 
(ID INT IDENTITY(1,1) NOT NULL,
CreateDate DATETIME DEFAULT(GETDATE()))  --There is no name given to default constraint
  1. When I generate the script for deployment using sqlpackage.exe, I see following statements in the generated script.

Generated the script using:

    "C:\Program Files\Microsoft SQL Server\150\DAC\bin\sqlpackage.exe" /action:script /sourcefile:C:\Users\User123\source\repos\DBProject\DBProject\bin\Debug\DBProject.dacpac /TargetConnectionString:"Data Source=MyServer; Initial Catalog=MSSQLDatabase; Trusted_Connection=True" /p:AdditionalDeploymentContributorPaths="C:\Program Files\Microsoft SQL Server\150\DAC\bin\AgileSqlClub.SqlPackageFilter.dll" /p:AdditionalDeploymentContributors=AgileSqlClub.DeploymentFilterContributor /p:AdditionalDeploymentContributorArguments="SqlPackageFilter=IgnoreSchema(3rdParty)" /outputpath:"c:\temp\script_AfterDLL.sql"

Script Output:

/*
Deployment script for MyDatabase

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/
...
...
GO
PRINT N'Dropping unnamed constraint on [3rdParty].[MainTable]...';


GO
ALTER TABLE [3rdParty].[MainTable] DROP CONSTRAINT [DF__MainTabl__Crea__59463169];

...
...
...(towards the end of the script)
ALTER TABLE [3rdParty].[MainTable_2] WITH CHECK CHECK CONSTRAINT [fk_518_t_44_t_9];
  1. I cannot alter 3rd party schema due to company restrictions
  2. There are many lines of unnamed constraint and WITH CHECK CHECK constraints generated in the script.

Question:

  1. How can I be able to remove the lines to DROP unnamed Constraint on 3rd party schemas? - Even though the dll excludes 3rd party schema, it still has these unnamed constraints scripted/deployed. Also, it is not Adding them back too !!
  2. How can I be able to skip/remove generating WITH CHECK CHECK CONSTRAINT on 3rd party schemas

Any suggestions will be greatly helpful.

EDIT:

Also, I found another issue. The deployment will not succeed due to Rows were detected. The schema update is terminating because data loss might occur

Output:

/*
The column [3rdParty].[MainTable_1].[Col1] is being dropped, data loss could occur.

The column [3rdParty].[MainTable_1].[Col2] is being dropped, data loss could occur.

The column [3rdParty].[MainTable_1].[Col3] is being dropped, data loss could occur.

The column [3rdParty].[MainTable_1].[Col4] is being dropped, data loss could occur.
*/

IF EXISTS (select top 1 1 from [3rdParty].[MainTable_1])
    RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT

GO
Santhoshkumar KB
  • 437
  • 4
  • 12
  • Basically, it ends up at the Github code [https://github.com/GoEddie/DeploymentContributorFilterer](https://github.com/GoEddie/DeploymentContributorFilterer) . Since I'm no .net expert, will create a new issue in GitHub. – Santhoshkumar KB Jun 09 '20 at 16:56
  • "Data loss might occur" issue can be fixed by enabling "Allow data loss" option in publish profile. For unnamed constraints, then it should work normally as well. As you are stating that it is only dropping, then something wrong either with your project or with your publish profile. – Dmitrij Kultasev Jun 10 '20 at 05:12
  • What I did was to add a custom script (pre-deployment script) to rename any unnamed constraint to named constraint) !! – Santhoshkumar KB Sep 04 '20 at 18:19
  • But you change schema then.... – Dmitrij Kultasev Sep 04 '20 at 18:22

1 Answers1

0

Regarding the unnamed constraints, I couldn't find any solution using sqlpackage.exe. But Redgate SQL Compare has an option to ignore them called IgnoreSystemNamedConstraintAndIndexNames that ignores system generated constraints and generates a much cleaner script.

For example when comparing 2 dacpacs:

SQLCompare /Scripts1:"\unpacked_dacpac_source_folder" /Scripts2:"\unpacked_dacpac_dest_folder" /options:IgnoreSystemNamedConstraintAndIndexNames  /scriptFile:"script_result.sql"

You can find more info here: Handling System-named Constraints in SQL Compare

Alan Macgowan
  • 481
  • 1
  • 7
  • 22