Automation Ideas for T-SQL Tuesday #130

tsqltuesdayThis 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:

  1. Use Powershell to automate the creation of SQL Clone images.
  2. Use PowerShell to update SQL Monitor alert values and add instances
  3. Use Flyway in a container to deploy changes
  4. 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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.

1 Response to Automation Ideas for T-SQL Tuesday #130

  1. Pingback: T-SQL Tuesday #130 – Recap: Automate Your Stress Away – Elizabeth Noble

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.