I've had an issue with our database deployments for a while now. We need to be able to deploy our database, ignoring certain tables because they're modified by the application on the fly and also ignoring any additional objects (customisations) that may have been created in the database for that specific customer. We know the names (or at least the pattern) of tables to ignore for updates. We don't know the name or schemas of any additional objects ahead of time.

Our database is built by TeamCity using Redgate's DLM Automation tools and this outputs our database as a lovely bundle of NuGet'y goodness. From there, the NuGet package is pushed to Octopus and available to be deployed. In Octopus we use a (slightly modified) Deploy From Package step template to deploy the database. The modification allows us to pass the -IgnoreAdditional switch to the DLM Automation cmdlets.

[caption id="attachment_41" align="alignnone" width="1064"]DLmAutomationIssue The error you'll receive if you combine the -IgnoreAdditional and -FilterPath switches[/caption]

 

After a little experimentation, some mild frustration and a support call, I found that you cannot combine the -IgnoreAdditional and -FilterPath switches when using the New-DlmDatabaseRelease cmdlet... From Redgate's help site:

-IgnoreAdditional <System.Management.Automation.SwitchParameter>

Specifies whether to ignore additional objects in the target database. Enabling this prevents new and unexpected objects in the target being dropped - note that the checks and tests applied to this database may not have taken those objects into account and may therefore not be valid.

This parameter can't be used simultaneously with filter files.

https://documentation.red-gate.com/dlma2/cmdlet-reference/new-dlmdatabaserelease

The bold and italic is a relatively recent update to the help site following our support call mentioned above; unfortunately, it looks like it's not going to be supported in the near future.

What I've got working is as follows:

  1. Create a new, empty database
  2. Deploy the database, using the filter file, to the database created in step 1 - using the Deploy From Package step template
  3. Deploy from the database in step 1, to the final target database, ignoring additional objects - using the Deploy From Database step template
  4. Drop the database from step 1

I haven't yet experimented with any of the other cmdlets to see if this could be achieved without an intermediate database. If anyone out there has managed to, please let me know; I'd be interested to hear how you solved the problem. Of course one of the downsides of this approach is that you're doing two full comparisons, so it takes almost twice as long.

NB. This is my first ever SQL blog post so if you've got any comments or suggestions, please let me know :)