By: Alejandro Cobar | Updated: 2021-09-28 | Comments | Related: > PowerShell
Problem
By becoming a professional SQL Server DBA, we have been (or will be) trusted with both the database servers that host the data, and the data itself. That for sure is something that should not be taken lightly and can be quite overwhelming under certain circumstances. If you are a proactive DBA, then you already know that you have to stay vigilant over a bunch of things: database backups, monitoring the health of the database servers, troubleshooting problematic queries impacting the applications and business, design and implement strategies to achieve a certain degree of fault tolerance for the business, etc.
In my experience as a professional DBA, I have always been a part of very small teams (and I don't know why to be honest). I'd like to think that it's because there are not that many people willing to take the risk or simply just don't have the required skills to pull it off (I might be wrong of course). Regardless of what I think, that's how it has been during my professional career for the past 10+ years, and I don't see that changing any time soon. With that said, it is quite obvious there's a ton of work that has to be covered and that's why I will be publishing a series of articles around PowerShell for the DBA.
Solution
During this series of articles, called PowerShell for the DBA, I'm planning to provide an overview of what PowerShell is and how it can be leveraged to assist you to cover routine tasks that, at the end of the day, consume your valuable time. At the end of the series, I hope that you have the knowledge to implement tools that allow you to automate certain aspects of your work, so that you can have more time to either focus on the hardcore stuff or simply just to enjoy life.
Initial considerations
- This content is aimed towards the DBA who has either never heard about PowerShell before, or knows its existence but has never used it for DBA related purposes.
- When you attempt to tryout any of the code samples shown throughout the series, please make sure to do it in a test environment first.
- If you, at the company where you are working right now, have access to third party solutions that help you monitor all sorts of things SQL Server related, that's fantastic! However, not everyone has the budget (or isn't willing) to afford such tools, and this content is precisely aimed to help those that are under that specific scenario.
- Most of the material that I will be presenting throughout the series is targeted against on-premises deployments of SQL Server, since the managed cloud offerings do a bunch of things for you. Regardless, you could take a thing or two from this content and apply it to your cloud managed databases.
What is PowerShell?
Let's take a look at the Wikipedia definition:
"PowerShell is a task automation and configuration management framework from Microsoft, consisting of a command-line shell and the associated scripting language. Initially a Windows component only, known as Windows PowerShell, it was made open-source and cross-platform on 18 August 2016 with the introduction of PowerShell Core.[5] The former is built on the .NET Framework, the latter on .NET Core. In PowerShell, administrative tasks are generally performed by cmdlets (pronounced command-lets), which are specialized .NET classes implementing a particular operation. These work by accessing data in different data stores, like the file system or registry, which are made available to PowerShell via providers. Third-party developers can add cmdlets and providers to PowerShell. Cmdlets may be used by scripts, which may in turn be packaged into modules."
From this definition, you can get a sense that PowerShell is this big "Frankenstein" that can be used for multiple purposes. However, the way I'm going to be approaching it is from the development and scripting perspective. Now, if you take a close look at the Wikipedia definition, it doesn't state that PowerShell is a programming language. However, from my experience and use, it definitely is!
Why do I want to use PowerShell?
From my own experience, given the fact that there's always a very small crew covering database related work, the amount of load assigned to each DBA is quite high. With that said, you can see PowerShell as that DBA, laying around, that can be used to offload those mundane and repetitive tasks that surely consume a good chunk of your time.
Since this content is born out of my own experience, here are some examples of use cases where you can take advantage of PowerShell:
Monitoring of execution of SQL Agent Jobs
Imagine that you have under your care dozens or hundreds of SQL Server instances. Wouldn't it be cool if you are able to take a look at the latest execution status of all the jobs (the enabled ones of course) across all your instances? Sometimes, due to business requirements, there are jobs that have to be running periodically and cannot be failing. I know that you can set up email alerts for each individual job, but receiving those from the entire stack of instances can be a pain. With PowerShell, you can retrieve this information from all your supported instances and digest it in a single central view.
Monitoring the backups status of all your databases
Backups are essential for every business, as they allow us to recover from an eventual disaster. With PowerShell, you can craft a script that asks each SQL Server instance for the latest backup status of all the databases, and then you can have a single view of your entire supported stack. With such information, you can immediately capture the following insights:
- Know if there are databases that have either never had a full backup or that the last full backup was more than 7 days ago.
- Know if your databases, under full or bulk-logged recovery model, have recent transaction log backups.
- Know what's your largest database and how long its full backup takes to complete.
- Know the location of all database backups.
Automating restore testing of database backups
Backups are only good if you can recover data from them, so if you are taking backups but they are corrupt, then when the time comes and you need to recover from a disaster, then you will have to deal with the consequences. That's why it is a good idea to perform regular tests of such backups, to guarantee that they are fully usable and reliable, and PowerShell can definitely help you to automate such backup tests (or perform manual tests if you have the need).
Collecting information about logins from your environments
Having visibility over you entire support stack, in regards to who has access to and the level of access to each SQL Server instance, is a very important thing to have. You definitely want to know who has elevated rights, and ask around if he/she should have such level of access, because at the end of the day anyone with elevated rights can perform a harmful action on the SQL Server instance and you are potentially the first one that's going the get all the finger pointing. However, PowerShell can help you collect such information, and ideally you'd want to automate this because new logins can be created at any time and privileges for existing ones can change over time, and you definitely need to stay on top of the game.
Collecting information about the configuration of your SQL Server instances
There's a high chance that you are providing support for SQL Server instances that were deployed by somebody else, and it would be ideal to have some sort of inventory of the entire stack that you are supporting. By having this information, you should be able to tell if there are instances with not so ideal configurations, and address each case. It can also be very helpful to keep an up-to-date list of the patching state of your instances, so that you can plan any patching activity accordingly.
In this particular scenario, PowerShell can help you not only to automate the collection of such information, but you could also build an "auditing mechanism" so that you get notified when somebody modifies a configuration parameter within an instance (e.g. modifying the max server memory amount, modifying the max dop).
Summary
These are just a few use cases that can be brought to life by integrating PowerShell into your daily operations. Of course, as with anything in life, the wide range of possibilities are only limited to the creativity of those willing to come up with solutions that can make the lives of SQL Server DBAs better.
Next Steps
- I know that this introductory article seems a bit dry, but since it is aimed to an audience who has little to no experience with PowerShell, I definitely don't want to start throwing code from the very start.
- Hopefully with the use cases I described, I can raise the interest of any SQL Server DBA at least enough to keep reading the articles that will follow this one, so that he/she can grasp the potential that PowerShell has to offer.
- Let me leave you some links to articles that I have published here at MSSQLTips.com, so that you can get an idea of things that can be accomplished with PowerShell. It doesn't matter if you don't understand the actual code that's in each one of them, the idea behind this series is that after you follow along, not only you are able to understand the scripts, but eventually craft your own and contribute to the community as well:
About the author
Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2021-09-28
