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?
First of all, just is a four letter word. Second 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 4 support a development staff of around 30 and a business that processes millions of dollars worth of transactions a day. We also support web and mobile applications that serve hundreds of thousands of requests a day. We have several environments for development, integration testing, staging, demoing, and production. All of these are backed up 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.
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 it’s 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. You could say we’re pretty invested.
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.
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.
# From an elevated prompt for the whole machine. Install-Module PSRedgate # Or just for the current 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.