← Misc

SQL Script Triage

Overview

SQL scripts allow us to enable features and correct customer issues. As of May 2019 this is still a very manual process of copying another script and modifying it for the applicable financial institution. This is a labor intensive effort so effective immediately TechOps will be running these scripts moving forward.

SQL scripts live on github.com/Banno/banno-data-one-off-scripts/ and are manually created by implementations/support. The new scripts are based off current scripts and customized to fit the need.

Expectations

  • Implementations will post pull requests(PRs) URLs for new scripts into #team-ops-prs to let others know they’re ready for review.
  • a PR needs to contain the server name & database name <- we’re working on a better solution for surfacing this information, but for now This is your best reference
  • PR reviewers/approvers will make best effort to thoroughly check scripts for accuracy and correctness. Any necessary changes should be made prior to approval and before techops merges the PR to run the script. <- this will be a work in progress as we develop better tools to help with this.
  • PR reviewers/approvers will indicate the script has been checked by adding the :white_check_mark: or :done: emote to the message.
  • The second reviewer/approver will add the :run: emote when the script is deemed ready to be run. Attaching this emote will send that message to the #org-sql-scripts channel.
  • scripts that are time sensitive should include DO NOT RUN until 00:00 at the beginning of the slack message. (it must be contiguous with the paragraph containing the URL or it will not be sent along with the link to the org-sql-scripts channel)
  • TechOps will process these in a first in first out manner unless the message includes an indication that it needs higher priority.
  • TechOps will run these throughout the day along with the deploy and release work that is happening.
  • TechOps will add :done: emote to the script in #org-sql-scripts to indicate a script has been run
  • TechOps will only provide feedback on the script if there’s an error or if specifically requested.

Caveats

Drew/Heather and the support staff have developed a comprehensive understanding of the business logic to know how features impact a customer. This knowledge was leveraged when they reviewed these scripts before running to steer clear of potential unintended consequences. Techops does not have this context so we’re asking for script authors to be aware of how their request might impact the other features currently enabled for the FI and help us steer clear of issues. If you have questions please ask.

Improvements

Improvements that we’re considering

  • a tool that can return DB server details directly in slack.
  • directing all one-off script requests to the #org-sql-scripts channel and away from org-tech-ops and org-deployments.
  • templates scripts that could be used to create new scripts.
  • script that could be fed institution id and feature and would auto create a PR with all the relevant information. This could iteratively result in a slackbot to run these.
  • sql FAQ that would allow us to apply a more stringent review during the approval process.