My father in law is a skilled woodworker and artist who has been creating beautiful kinetic sculptures for over 30 years. If you stroll around his workshop, you’ll see many examples of odd looking contraptions built out of scrap lumber. Ask him about these, and he’ll usually readily recall a time when he needed to align two pieces of wood in a certain way, or drill two holes just so far apart. Because he builds sets of nearly identical pieces, he quickly realized that by creating and utilizing these, he could produce product at a far greater speed and with more accuracy.
In woodworking terms, these are known as jigs. They are basically tools that woodworkers use, often constructed by hand, that allow them to more easily perform routine tasks. For example, if they need to cut many pieces of wood in the exact same way, they might make one ‘master’ cut, then base all other cuts off that one. That way, they don’t have to measure each and every time (and possibly introduce error in the process). While these tasks could be done with existing tools (measuring tape and pencil), the simple creation of a jig results in a much higher rate of production along with more accurate work.
As DBAs (and really people who work with databases in general; this applies to you devs too!), we are inevitably going to encounter painful processes in our work. There are going to be tasks we must complete where our existing tools aren’t up to the job, and as a result we’re going to spend time battling through cumbersome work. Here’s an example from recent memory.
As I’ve written before, I’ve been working a lot more with Azure based solutions as of late, specifically Azure SQL Databases. There’s a lot of differences between them, not the least of which is that it’s darn near impossible to easily execute a query against all databases assigned to a particular Azure SQL server. Sure, there’s elastic database queries, but that requires a lot of pre-meditation and setup.
Can’t a guy just get some good old fashioned sp_msforeachdb style help?
So, I opened my trusty Powershell editor, and cranked out a function that basically replicates the majority of the functionality in that venerable undocumented procedure. Now, I can pass in a server name, credential information, and a query, and get back a nice result set that I can then pipe to something else or simply save as needed.
Is it perfect? Far from it! It would be fantastic to have this inside of Management Studio where I already spend most of my days. Still, it’s a huge help and a step in the right direction. And if I find I use it a lot, then I can always devote more time towards improving it.
The bigger lesson from this experience is this: the ability and motivation to create tools for ourselves is a critical skill if you are going to advance in your career as a database professional. Whether it’s Powershell, straight Transact-SQL, or even something more complex, we must put ourselves in the mindset of a craftsman. When we encounter pain, we need to create things that lessen it.
There will always be things we must repeatedly do, and some times those things may not be easy to do with the tools we have at our disposal. Learn the ability to create your own tools, and you’ll be well ahead of most of your peers in no time.
P.S. If you think the function that I wrote would be helpful to you, I’m giving it away! Enter your name and e-mail below and I’ll send it along.