Gary Bargsley (b|t) is the host of this months
T-SQL Tuesday #110 – ” Automate All the Things”
The question he asks is easy:
What do you want to automate or what automation are you proud of completing?
My answer to that is simple, I’m a major contributor to the awesome Powershell library dbatools. What I’ve contributed to that library are commands that will help automate the running and processing of queries from the DMV library of Glenn Berry
At some point in the life of a DBA we’ve all come accross his scripts. For the longest time I would advise people to google “Glenn Berry DMV”, and it will be the top result.
The scripts however, come in a single file per SQL Server version and you can’t run them all in one go. You would have to select a script, run it, and paste the result from Management Studio into an Excel sheet. Glenn provides an empty sheet with tabs ready to paste the various result sets in. I’ve automated this part, hope you like it!
Let’s get started with something simple and run a single script.
First install dbatools though, and to make things more interesting for later on, install a module called importexcel while you’re at it.
All you need to do after that is open a powershell window and type something like the following:
Invoke-DbaDiagnosticQuery -SqlInstance localhost -QueryName "Configuration Values" | Select -ExpandProperty result | Format-Table -AutoSize
Want to know which scripts are available?
Invoke-DbaDiagnosticQuery -SqlInstance localhost -UseSelectionHelper
So, where is this Excel sheet you promised us?
Invoke-DbaDiagnosticQuery -SqlInstance localhost | Export-DbaDiagnosticQuery -ConvertTo Excel -Path c:\demo
(My laptop is called XPS, it shows that even though I specified “localhost” in the query)
The directory will be filled with a lot of other files besides the Excel sheets. Some of Glenn’s scripts have columns which contain the query or even the query plan. That doesn’t play nice with an Excel sheet so they will be saved in separate files.
Good to know:
- There are options available to exclude the .sql and .sqlplan file from the export
- You can include specific scripts, but also exclude specific scripts
- Want a more portable format? Pipe the result to Export-CliXml. That compresses nicely if you 7zip it and can be used as input for the Export-DbaDiagnosticQuery later on.
- The latest version of Glenn Berry’s DMV scripts are always included in dbatools, you don’t have to download them separately. (but you can certainly do that if you want to for whatever reason)
Fantastic – thanks very much
Glad you like it! Do check out the rest of the dbatools module, you’ll love it!
[…] Andrew Kamman walks through a particularly useful cmdlet in the dbatools package: […]
This is GREAT
Why Excel? We put data like this into the database (an ADMIN DB if you like …). We have a web site intranet thing, with decent query/display ability and also report writer capability, which does a good job of slice-and-dice. Excel column-filters are nice, and so on, but Excel is just String-and-Gum to me and the source of lots of time-wasting because of well meaning good intentions poorly executed : someone has finger-trouble / leans on the keyboard and alters data without realising, or stores the file in the wrong place, or the holiday-cover doesn’t know where it is, or someone runs the BATCH with the “old version”, or hangs onto a stone-age copy on their C: drive … all solved with a Intranet type solution. And keep as much historical data as you want – so allowing after-the-fact analysis of “How fast is this thing growing” and so on.
Hi Kristen, Excel is a quick and solid way to store and analyze the data. For many people this is more than enough, and way better than staring at text files. However, if you have many instances and want to collect on a daily basis and look at stats over time, then putting the data in a proper database instead of many Excel sheets is indeed a good way to go. We also do that for our systems. But it does mean that you have to figure out a proper data model and perhaps cleanup the history, and “monitor your monitoring”. At some point you might be better of buying a proper monitoring system. Long story short, I agree with you, but I do also think that there sometimes is a need for a simple Excel sheet based export.