T-SQL Tuesday #135 – Tools of the Trade

T-SQL Tuesday logo.

Big thanks to Mikey Bronowski (B|T) for this edition of T-SQL Tuesday about our chosen tools of the trade. This got me thinking about what my top three tools which I use that make a significant difference to the way I manage database systems.

So, here goes.

Search Engine – Google, Bing, etc.

I’m the first to admit that I do not, and cannot remember everything I need in order to do my job. However, I do know that the information is out there and have taught myself how to find it again repeatably.

One thing to know is that different search engines will return different results for the same search terms. My search engine of choice is Google because it seems to be the one which gives me better results for the way I form my search terms. I suggest that you test out multiple engines and see how you fare and pick the one which works for you.

One of my common searches is to look up T-SQL syntax and examples, most of which are in the Microsoft docs. To focus our search into a single site where I know what I want is then we can prefix our search with “site: <website> <search term>”. Great for finding things like T-SQL, DbaTools commands, or GitHub repositories. Other useful things include using double quotes to search for exact terms and the * for wildcards. This is really useful when searching for errors and exceptions thrown by SQL, PowerShell, etc.

Animated Gif to show how to use the Site search for google.

Finally, when it comes to defining search terms I have found that focusing on key terms, error numbers, and elements that make a search string unique help get me the results I want. For things like error messages which can be quite verbose I find that focusing on the error number and key words and, if we have it, the key word which failed helps. Also, omitting the environment specific things like column or table names yields good results.

On key thing to remember whenever you are searching for anything is to take an objective review of the results you get back. Read a couple of them and see where the line up and where they do not. It is possible to get a lot of bad information on the internet as well as the good stuff, make sure that you can work on identifying reliable and trustworthy sources.

Terraform – Infrastructure as Code

I first started using Terraform just over three years ago when working on deploying Azure resources for work. From there I started to present about it and use it for standing up my lab and demo environments as well as customer systems in Azure and AWS.

What do I like about it, and why use it in preference to ARM, PowerShell, or the AZ CLI?

First and foremost I find it easy to pick up and put down again because of the common syntax (Hashicorp Configuration Language or HCL for short). I find ARM overly complex for casual use, if I were using it all day every day it would probably be another story. With PowerShell and the CLI it is a constantly moving target with modules and versions and refreshing all my scripts can be somewhat cumbersome.

One of the key features I like is the way that it allows for implicit and explicit dependencies to be formed between resources. This then allow Terraform to decide when things should be done in parallel Vs. serial for deployment. This also means that I don’t have to remember to write my code in any particular order for execution which is the case for PowerShell or CLI. This combined with other really nice features like being able to output a digraph in DOT language which allows us to visualise our infrastructure as code in tools like Graphviz and WebGraphviz which I find really useful.

Graph of Azure infrastructure from Terraform digraph output.

One thing I will say is that as much as I think Terraform is a great tool that makes things a lot easier for me to deploy cloud resources it is not perfect. There are still resources which it cannot deploy so there is a need to combine it with ARM, PowerShell, and CLI for little bits. But it is a lot easier to take those out and convert to HCL as that coverage expands.

If you are working with cloud systems, or even on premises with VMware which has a provider then check it out and see if it works for you.

DbaTools PowerShell Module

Last, but by no means least, is the stalwart that is the DbaTools PowerShell module. I use different elements of this module every day at work and quite frankly I would be lost without it.

Over the last year I have been working on in-sourcing a database infrastructure from a third party and this has been invaluable. The estate is not huge but at the same time it is certainly not small, and it has its complexities. DbaTools has allowed the team and I to gather information and start managing the estate at large.

I am regularly using $Instances = Find-DbaInstance -ComputerName <Hostname> | Connect-DbaInstance to pull back information from servers and then drilling in further. This helps me avoid needing to go to the CMDB and other systems of record when dealing with servers.

The joy of DbaTools is that it allows us to get the key information we need very quickly to then go onto the more complex elements of managing the estate. If we want to make an update to an sp_configure option on 400+ servers it can be done with a couple of lines of code and a CSV. And while I am an advocate of PowerShell DSC, there will always be a need for DbaTools as a daily driver for the DBA.

Animated gif of DbaTools PowerShell module getting a list of databases.

If you are not already using DbaTools or you are and want to dive deeper then I strongly recommend you check out the blogs by Chrissy LeMaire (B|T), Jess Pomfret (B|T), Rob Sewell (B|T), and Stuart Moore (B|T). These are the people who set me on my journey and who’s content I regularly refer to when I need to get more information on how to get the most of DbaTools.

Summary

So here we have three of the things that I use on a regular basis to do what I do when I’m managing database systems. Is this exhaustive, not by a long way, make sure you check out all the other posts on this topic by other authors and see what tips and trick they have to share.

Keep on learning and sharing 🙂

2 thoughts on “T-SQL Tuesday #135 – Tools of the Trade

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.