- 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.
- 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"
/* 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.
- 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.
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
/* 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