PSRedgate: automating greatness

By May 11, 2018Powershell

Being an automation DBA

Most of my job is figuring out how to do something, and then doing it over and over again. The first part is great, solving complicated problems or creating value for my company is a rewarding feeling and always makes me feel accomplished. The second part, where I have to keep doing it over and over, often makes me want to smash the snooze button on my alarm in the morning.

I love automation. It’s honestly my favorite part of being a DBA. I’m a programmer by training, and came to being a Database Administrator from web development. These days I really enjoy building quality tools. I also enjoy using those tools to do things quicker and more accurately than I ever could without them. That’s what led me to create PSRedgate.


Why not just do it by hand?

Being a production database administrator means my responsibilities are very wide. I am responsible for backups and replication, tuning, monitoring, triage, on-call response, and deployment. My team of four supports around thirty developers and a business that processes millions of dollars worth of transactions a day. The databases we manage support web and mobile applications that serve hundreds of thousands of requests a day.  That doesn’t even consider the fact that we have several replicas of the production system. Environments for development, integration testing, staging, demoing, and production. All of these are back ended by SQL Servers with databases as large as nearly half a terabyte.

We’re not a big company, we’re not even big relative to the small town we’re located in, but there is a lot of pressure to do things right and do more with less. Doing things the RightWay ™ is often difficult for those involved and requires lots of code. You could double the size of my team and we’d still have plenty of work to do. The best thing I can do for my company is to allow people to do as much work as possible, with as little chance of making mistakes, with lots of documentation. Bonus points for not needing a whole lot of training to start being productive. Tools are the best way to do that.


Solving one problem at a time

Good automation starts starts by solving small problems, and then leveraging those solutions to solve even bigger problems. My first solution started with automating our 3rd party tools. Every time you want to deploy, backup, restore, or sync you have to fire up some application and press a whole bunch of buttons. What if you need to do it for 10 servers? What about 100? Not only is that not feasible, it would quickly become the largest and least enjoyable part of my job. No thanks! Fortunately my friends at Redgate are very mindful about creating developer first tools. What does that mean? For the most part, everything that you can do in their GUI (graphical user interface) applications, you can do by interacting with the CLI (command line) application. SQL Backup has a really nice interface that they built for it, but if you have used it much, you’ll notice that it’s just building a command to fire off to the server. My company has been using Redgate’s family of tools for at least as long as I’ve been here, and I’ve been doubling down over the past few years. SQL prompt alone is worth its weight in gold. We use SQL Compare to do all of our database deployments from version control to production, we also use it to promote changes to different environments, we use SQL Source Control to version our database changes, we use SQL Backup to handle all of our retention.


So what’s the solution?

I have created a powershell module that I am calling PSRedgate. If you use powershell very often, you’re probably aware of how much of a pain in the neck it can be to use windows command line tools. A lot of them are really picky about how you send them parameters, they have to be in your path, and don’t seem to handle errors very well. All of that and a little bit more is what I’m offering in this new powershell module. It will make interacting with the tools from Redgate on your machine that much easier and will hopefully enable you to do things you can’t currently do easily.

I have completely automated my replication process using this module. I have internal administration module that uses this code to create backup jobs with Redgate command embedded for my test environments, fire off adhoc database backups and restores. You can even write logic to locate all of your backup files for point in time recovery.

Getting started with PSRedgate is pretty simple, all you have to do is open up powershell and install the module from the Powershell gallery. The current implementation of this module at this time allows you to automate interactions with the following tools. SQL Compare, SQL Backup, SQL Doc, and Multiscript. An easy way to get started is to install it from the powershell gallery. I will continue pushing out new changes and will have future posts to spotlight features and different things you can do with the module.

# To install from an elevated prompt
Install-Module PSRedgate
# If you don't have admin rights, or just want to install it for your local user.
Install-Module PSRedgate -Scope CurrentUser

You can find out more about the module at PSRedgate – github. If you find things that need to be improved or bugs, open an issue. If you would like to contribute, please feel free to fork the code base and submit a pull request. I plan to continue maintaining this module for the foreseeable future. I use it every day for administering my databases and so it will continue to grow and evolve.

Leave a Reply