PowerShell is a powerful cross-platform framework designed to assist in task automation and configuration management. It is built on top of the .NET Common Language Runtime (CLR) and accepts and returns .NET objects adding enhanced functionality and providing new ways to automate administrative activities. Objects can be passed as parameters in complex and dynamically created command strings.
Becoming familiar with the capabilities of PowerShell is recommended for any busy DBA who values their time. Fortunately, this does not mean learning the intricacies of a new programming language just to get the benefits of PowerShell. You only need to be able to execute a command, and most DBAs have demonstrated that ability.
Commands in PowerShell are known as cmdlets and can be used singly or in combination with other cmdlets to perform complicated tasks. PowerShell can interact directly with console input and format display output. Its use of a pipeline to concatenate commands adds substantially to the functionality of PowerShell commands. Objects rather than text are piped and enable results to be passed to subsequent commands for further processing.
Using the dbatools Library
The use of PowerShell is not restricted to SQL Server database administrators. The command family is extensible and there are many open-source libraries or modules that have been developed to address the needs of specific user groups. For SQL Server DBAs, the dbatools library provides a wealth of valuable and time-saving commands that can be executed against one or multiple systems simultaneously.
The dbatools library began as a project to develop a SQL Server migration tool. It has grown to the point where it now has over 500 best practice commands for SQL Server administration, development, and migration. The open-source library has over 140 contributors on GitHub who bring hands-on experience and an understanding of the challenges faced by a DBA. Users can take advantage of the commands as they exist or delve into customization if they desire.
Many of the tasks and activities for which DBAs are responsible can be accomplished quickly and easily using PowerShell and dbatools. Complex code is hidden behind straightforward commands that execute under your Windows login. You can use dbatools on SQL Server versions from 2000 on up, with some reduced functionality on older systems. The library only needs to be installed on the machine executing the commands which can be run against as many remote SQL Servers as you wish.
Some of the activities that can be accomplished with PowerShell and dbatools include:
- Automating many regularly scheduled processes;
- Backing up and restoring databases;
- Running DBCC CHECKDB;
- Checking drive space;
- Identifying who is active on a system;
- Interacting with the SQL Agent;
Flexible filtering enables you to execute commands against multiple servers or selected groups of database instances. The Pipeline provides enhanced parameter passing so commands can be run sequentially with shared objects. A really nice feature is the “What if” parameter that lets you test a command to see what would happen without actually executing it. Taking advantage of the functionality of PowerShell and the dbatools library gives a database team new tools to help streamline the complexities of SQL Server administration.
Identifying Targets for PowerShell Commands
An IDERA Geek Sync Webcast investigates some of the ways that PowerShell can be used by SQL Server DBAs to save time and maximize productivity. It includes many hands-on demonstrations that illustrate the power and flexibility of PowerShell and dbatools. DBAs who take the time to view the webcast will be repaid handsomely by learning techniques that will save exponentially more time than that spent watching it.
SQL Diagnostic Manager for SQL Server is a versatile software tool that enables a database team to monitor their complete SQL Server environment from a centralized location. The application provides deep dive diagnostics related to all components of your SQL Server environment. It fully supports cloud and virtual instances, making it an excellent choice for hybrid cloud implementations. Quickly identify problem queries or display resource utilization to proactively avoid insufficient capacity issues.
Low overhead allows you to run the tool with minimal performance impacts on your systems. The customizable alerts generated by SQL Diagnostic Manager can trigger PowerShell commands to address various conditions that may arise in your SQL Server environment. Combining the insights garnered from SQL Diagnostic Manager with PowerShell commands that make use of dbatools enables a database team to automate many responses that will contribute to a high-performing SQL Server environment.