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

Need to run post-deployment script without schema change

I'm using post-deployment scripts to deploy SQL Agent jobs and other server-level objects, but the post-deployment script will not run unless there is a database schema change. What's the best way to do this? The only thing I can think of is to create a dummy database for this purpose and to deploy some kind of harmless change each time, but this seems like a hack.

Best Answer

  • Options
    tomslickerstomslickers Posts: 40 Bronze 2
    Kendra - yes we can make a small database change, especially if we move forward with the creation of a database for this purpose as you have suggested. Thanks for your help with this.


  • Options
    Hi @tomslickers ,

    I think it can often make sense to create a dedicated InstanceConfiguration database (just picked a random name) to version changes for instance level objects such as linked servers and SQL Agent jobs, for a few reasons:

    1. As you've found, sometimes you want to just deploy a change to the instance level object, and nothing to the database itself. Doing a deployment for that database is problematic from an audit trail /workflow perspective if you aren't actually changing anything in the database, but rather a related item elsewhere.

    2. On many instances, a set or family of databases is used together to support an application, so there's no obvious choice as to which database you'd manage instance-level objects from. In these cases, often SQL Agent jobs will interact with multiple databases, as well. Creating a standard database for this on each instance you manage can make it easier to identify where code for instance configuration is stored.

    3. Creating supporting objects in that database such as procedures and tables can be quite useful. Some examples:

    A team I worked on created a utility that we used so that we could control SQL Agent Job code in relatively simple configuration scripts, and then procedures in the utility would deploy the agent jobs. This level of abstraction was very nice because the definitions we stored of agent jobs were the type of step, contents of the step, etc, which made it much easier to review changes. Static data tables could be used to master the metadata. (Note: I'd love to write a simple open source utility like this someday!)

    You might want to create a process where you store some history data in tables for a short period in this database when changes to server level objects will remove the history -- such as losing job history when recreating a job.

    4. There are some scenarios where it really makes sense to decouple these instance level objects from the database code itself for deployment reasons -- for example, on an Availability Group with multiple nodes, you might need to configure SQL Agent Jobs and Linked Servers on each node, while you're deploying database code ONLY to the writable node. Deploying an InstanceConfiguration database to each node can make managing that with automation easier. (You do of course have to write the code that manages these objects in a way that's aware of the AG / properly manages job status of course.)

    Hope this helps!

  • Options
    tomslickerstomslickers Posts: 40 Bronze 2

    Thanks for your detailed and prompt reply.

    This sounds like a good solution, but I'll still need to make some kind of change to the InstanceConfiguration database so that my pre/post deployment scripts will run. Agree? I suppose this could be a static data change rather than an actual schema change.

  • Options
    From a chat in the team Slack channel, I believe that it's intended that you *should* be able to deploy pre / post scripts without a schema change, so it could be a bug. I'd like to set up a repro.

    I'm guessing that you're using SQL Source Control / State Based + SQL Change Automation? (Just checking for the repro setup as migrations approach also has pre and post scripts).
  • Options
    tomslickerstomslickers Posts: 40 Bronze 2
    Yes.  SQL Source Control / State Based + SQL Change Automation. I tried this again just now with the same result.I made a harmless change to my post-deployment script. SQL Source Control listed this on the Commit tab. I committed it to Git and pushed it. My build/deploy process (Jenkins/Octopus) kicked off, but the script artifact in Octopus contains only the following:

        Generated on 24/Apr/2019 10:24 by Redgate SQL Change Automation v3.1.16.3737
        This script is empty because the Target and Source schemas are identical.
  • Options
    Kendra_LittleKendra_Little Posts: 139 Gold 3
    edited April 25, 2019 1:38AM
    Note: striking this for other readers, as I did an incorrect step in my repro

    Thanks for clarifying!

    I did some testing. I believe the bug is with the Release Artifact -- in my repro it contains that exact text as well. However, it is misleading you-- the post deployment script is still being run in my testing.  

    For my repro, I used this script and found that the job would be recreated even when I made no state change in the database itself, and the release artifact displayed that message.

    The team has created a Jira bug to prioritize this and make sure it doesn't get forgotten. But for the time being I think the actual deployment functionality is what you want.

  • Options
    Kendra_LittleKendra_Little Posts: 139 Gold 3
    edited April 25, 2019 1:39AM
    Note for readers who come by later: this was not correct, see later in the thread please!

    ps: just confirmed from chat with the team that this is considered a bug, the desired behavior is to include the pre- and post- sql code into the Release Artifact for display (as well as executing it, which is occurring). The Jira item will help make sure we track that.
  • Options
    tomslickerstomslickers Posts: 40 Bronze 2
    I am not able to reproduce this behavior. When I commit a post-deployment script to Git, it is not deployed unless I also include a database schema change.
  • Options
    Sorry about this, Tom -- the team suggested I wasn't seeing the right behavior, and I ran through it again on the plane today. I made an error in my repro setup, and it was the *build* creating the the job that I saw, not the deployment. You're correct that it's not running it.

    Would committing a small state change as a workaround make this functional for you in the meantime? You could do a a change as small as adding / editing an extended property in the database itself where you want the post-deployment script to run.

    Sorry about the misinformation!
Sign In or Register to comment.