This month we have another great T-SQL Tuesday topic, and again, a host that I pressured into writing the invitation. Elizabeth Noble (@SQLZelda) and I were talking DevOps last year at a SQL Saturday, just as she was effecting some change at her employer. At an event later in the year, I challenged her to host, and here we are, with a great topic, Automation.
That’s this month’s invitation. Elizabeth describes the process of using automation to smooth out their deployments to SQL Servers. They slowly built a CICD pipeline and migrated projects over and over to save her time.
This month, what have we done to automate things? I have a few stories.
Automating Data Collection
One of the things that I am passionate about is the SQL Saturday events. I loved that PASS maintained a site, and made a feed of event data available, but over time, we’ve lost some data and I don’t think there is much impetus to maintain this over time.
As a result, I build an automated process to grab data from the feeds and save it as an XML file on my local machine. I have the basic code up at GitHub, though I need to improve and refactor it a bit. These files change as the organizers update them, so I need a good merge process. Right now I tend to delete all 1-2KB files periodically, as the file is built once the event is approved. However, until all the speakers and sponsors are scheduled the file size is just a few KB. Most events are > 100KB once they are set.
This is a basic way of grabbing some data, and I’m looking to build a few more data collection processes to grab data in my life and keep it around, just in case some service I use goes kaput.
Automatic Databases
I work at Redgate, and one of the things I’ve been spending more time doing is the automation of databases for development with the latest code and test data. This is a challenge, but a few of the things that I’ve done with Redgate that help are as follows:
- Use Powershell to automate the creation of SQL Clone images.
- Use PowerShell to update SQL Monitor alert values and add instances
- Use Flyway in a container to deploy changes
- Use our SQL Change Automation cmdlets to deploy changes to databases
All of these items can be automated so that a user just needs to run a script, or sometimes just click a button.
I’m slowly getting the PoSh code up on GitHub as I build demos and help customers get things done. Right now, getting organized is the biggest part of my job, because I find these questions coming over and over.
I’m a bit proponent of DevOps and automation. Use tools and computers to do the tedious, repetitive work. Keep your day focused on solving problems and building/improving scripts.