Need to run post-deployment script without schema change
tomslickers
Posts: 40 Bronze 2
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.
Tagged:
Best Answer
-
tomslickers
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.
Answers
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!
Kendra
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.
Tom
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).
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.
Kendra
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.
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!