Task:
- Automate database deployment (SSDT/dacpac deployment with CI/CD)
- The database is a 3rd party database
- It also includes our own customized tables/SP/Fn/Views in separate schemas
- Should exclude 3rd party objects while deploying the database project(dacpac) to Production
- Thanks to Ed Elliott for the AgileSqlClub.DeploymentFilterContributor. Used the dll to filter out the schema successfully.
Problem:
- 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
- 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];
- I cannot alter 3rd party schema due to company restrictions
- There are many lines of unnamed constraint and WITH CHECK CHECK constraints generated in the script.
Question:
- 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 !!
- 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
/p:ExcludeObjectType=Defaults
or/p:DropObjectsNotInSource=False /p:DoNotDropObjectType=Defaults
etc – Santhoshkumar KB Jun 8 '20 at 7:17