What are the challenges you face when working across database platforms? Take the survey
Options

DLM Automation Release 2

We are staring to implement our CI process using VSTS + DLM plugins (Build & Release) and configured them using the documentation on your site. Upon running the Release from our Test DB to Staging DB environment, I've noticed that it dropped some of tables, stored procedures, constraints and deleted some static data in Staging that are not on our Test DB even though that the supposed change is only 1 stored procedure. We've already setup the filters using Source Control and pretty sure that the some of the objects are not source controlled. Just wondering if this is the natural behaviour of the DLM release process and if there is a way to avoid making changes on the target database that are not in the build's associated changes.

Also, I feel that the DLM automation documentation is not fully detailed and lacks content on some areas. Some of the plugin options like "Ignore static data" has no detailed purpose and when to use.
Tagged:

Comments

  • Options
    chris_godfreechris_godfree Posts: 50 Silver 1
    noelc - I had the same problem as you with a few objects and discovered that you need to include a specific filter file in the Release add-on (the Filter path field) because what you filtered in Source Control is being ignored: https://documentation.red-gate.com/display/DLMA2/Using+SQL+Compare+options+with+Addons

    Agree with you on the documentation side of things!
  • Options
    Thanks for contacting us!
    By default, DLM Automation has the IncludeDependencies option enabled. So it will deploy those dependencies even if they are Filtered out. https://documentation.red-gate.com/display/DLMA2/Using+SQL+Compare+options+with+Addons
    I would need your full build log as well as the build package to confirm that - but it seems like the most likely answer.

    You can disable it by putting a minus sign in front of "IncludeDependencies" and putting that in the Compare Options field.parkfwtj6kkr.png


    Static Data is best understood in the context of SQL Source Control and it can be stored in the Build package and deployed along with the schema scripts. If there are no data scripts in the package then no data will be deployed.

    I've also followed up via email for further troubleshooting.

    Warm Regards,
    Andrew Pierce
    Technical Sales Engineer
    Redgate Software
  • Options
    noelcnoelc Posts: 5
    edited May 14, 2017 9:16AM
    Hi chris_godfree,

    I'm quite confused, In the DLM 2 plugins in Visual Studio Online, it says it will use the included filter in the nuget package otherwise it will use filter in the path configured in the plugin.
  • Options
    chris_godfreechris_godfree Posts: 50 Silver 1
    Hi noelc,

    Check out the text in the red box in the link "If you specify SQL Compare options in SQL Source Control, these are not used by DLM Automation addons.".

    I've just run a quick test with a table:
    1) I created a table in my Dev and Live databases
    2) I specifically excluded the table from source control by adding a filter on the table name
    3) I committed the filter (the table definition is NOT in my repository due to the filter)
    4) I ran my build and release process
    -> the table was dropped from my Live database (ie the filter in my repository was not used)
    5) I added the same filter from step 2) to the filter file I use in my release process
    6) I re-created the table in my Live database
    7) I ran my build and release process
    -> the table still exists in my Live database (ie the filter defined in the release process was used)

    Hope that makes sense

    Chris
  • Options
    Hi chris_godfree,

    This is quite interesting, I would be happy to try that and confirm the behaviour you posted as soon as possible. What actually confuses me is in the VSO plugin, if you hover on the "i" icon beside the filter path it says the following:

    "The path to a .scpf filter file. Overrides any existing Filter.scpf file present in the package/source control repository, and is used when creating the update SQL. Filter files can be generated by SQL Source Control and SQL Compare."

    So, in my understanding, if there is no file specified in the filter path it will use the one included in the nuget package. I can confirm that I have a filter file in my repository and verified as well that it gets included in the nuget package created by the build.

    Thanks for giving me some explanations and pointing me to the right direction. Hope someone from redgate can verify this behaviour.


    Cheers,

    Noel
  • Options
    Andrew PAndrew P Posts: 194 Silver 3
    edited May 23, 2017 9:41PM
    Thanks for the vigorous discussion!

    I am a Technical Support Engineer over at Redgate, so I should be able to help.

    @chris_godfree The Source Control Options are not passed to DLM Automation, but Filters are not Options - Filters are passed from SQL Source Control and should be used throughout the pipeline.

    @noelc As I mentioned before - the only thing that would override the Filter file is the IncludeDependencies option which is enabled by default (see my previous post) - unless this was a bug with the version of DLM Automation you are using.

    So I would need a copy of your Release package (a package created by the "Create Database Release" step) to fully understand what occurred.

    We have a ticket open as well - so you would be able to send in the Release package there.

    Kind Regards,
    Andrew Pierce
    Technical Sales Engineer
    Redgate Software
  • Options
    chris_godfreechris_godfree Posts: 50 Silver 1
    Hi Andrew, I'm not sure that's the functionality I have been seeing - from my test above, I have used a filter (to exclude a table) in SQL Source Control but that filter is not then picked up in my release pipeline. Unless I'm missing something somewhere?

    Chris
  • Options
    @Andrew P

    Thanks for the reply, I'm sure i'm using a very recent (probably latest) version of DLMAutomation. As with regards to the IncludeDependencies, I don't think it will help on my case as on my previous tests it drops tables not related to the stored procedures that I modified. I have a feeling that I'm using the filters in the source control wrongly even though I managed exclude the tables/SP that I don't wan't to be source controlled.

    @chris_godfree

    Tried to specify the filter using the Filter path, some of my tables were dropped still. I think i need to revisit my filters and explicitly include object that I want to include.

    Cheers

  • Options
    Andrew PAndrew P Posts: 194 Silver 3
    edited June 22, 2018 4:51AM
    We solved Noel's issue by creating the Filter using SQL Compare (to confirm exactly what it included/excluded).

    We also discussed the IncludeDependencies option that is on by default (and it may be the source of his issue), but it can be disabled by putting a minus sign in front of the option like
    -IncludeDependencies,OtherOption

    The includeAdditional option works similarly to how in Compare it suggests that you deploy dependencies along with the objects that you are deploying, so you can do some tests with that to make sure that the filters and options are working the way you intend.
    Andrew Pierce
    Technical Sales Engineer
    Redgate Software
  • Options
    RamunasRamunas Posts: 4 New member
    Hi,

    I have very similar issue. I'm trying to implement CI/CD with VSTS release and build plugins. I'm successfully building nupkg, but when I'm trying to release that it creates excessive script with objects that were not changed. This is mostly tables and it seems that it is trying to rebuild those tables, but it is weird as those table were not changed and I cannot understand why rebuild is required. I tried to add -IncludeDependencies option but it did not helped. I see you guys are trying to solve this by creating filter, could you give more details about this? Although I not sure how it would help in my situation as per checking the changes.html report it founds only few objects, but still creates enormous update script.
  • Options
    Hey Ramunas,

    Thanks for posting!
    Off the top of my head, I'm not sure why it would do that.

    If you are still experiencing this - could you send in an email to Support@red-gate.com along with the full deployment log, the Release package (which contains the Changes.html file as well as the Source and Target scripts folders) that is created by the Create Database Release step, and a screenshot of your Create Database Release step definition.

    Kind Regards,
    Andrew Pierce
    Technical Sales Engineer
    Redgate Software
  • Options
    RamunasRamunas Posts: 4 New member
    Hi Andrew,

    I had a conversation with Sergio Rocha through Support@red-gate.com, he tried to replicate my issue but was not able to. I added additional information you suggested to our conversation. If you could have a glance I would be graceful as I'm stuck on this for a few weeks now I cannot think of any decent workaround.

    Thank you,
    Ramunas
Sign In or Register to comment.