Rajendra Gupta
DBATools documentation

DBATools PowerShell Module for SQL Server

March 12, 2019 by

This article will provide an overview and introduction to DBAtools, a powerful open source library of automation scripts.

It is an essential task for DBA to automate routine activities. DBAs should always be exploring opportunities for automation in their environment. It gives you enough room to focus on other critical tasks. We can use the combination of t-SQL, SSIS and SSRS to fetch required data and present in various format automatically. We can use PowerShell scripts to automate things without much complexity. SQL Server provides two PowerShell modules SQLPS and SQLServer. We can use SQLServer PowerShell module to use new cmdlets supporting various SQL Server features and latest features.

In this article, we will discuss automation of DBA tasks with the open-source and community driven PowerShell module DBATools.

PowerShell integrates deeply and seamlessly with SQL Server and OS and helps us perform various tasks programmatically with a single language. DBATools is a useful repository for the PowerShell commands for SQL Server tasks, which contains more than 500 commands to do tasks such as backup/restore, monitoring, administration tasks with these simple to use commands.

DBATools supports almost all features, editions in SQL Servers such as followings.

  • SQL Server versions SQL Server 2000 to SQL Server 2017
  • Clustered and AG instances
  • All SQL Server editions
  • Windows and SQL authentication

You can visit the URL for the documentation.

DBATools documentation

Installation

We can install DBATools using various methods. If you have an active internet connection, open the Windows PowerShell with administrative rights. You will receive the following error message without administrative rights.

DBATools installation

Right click on Windows PowerShell and Run as administrator.

Windows PowerShell with administrative rights

Run the following command to install DBATools module. In PowerShell.

DBATools automatically downloads the dependencies and installs them. We need to give permission by entering Y to import the NuGet provider as shown here.

DBATools installation

We also need to accept the module from an untrusted repository. Enter Y to accept and go ahead with the installation.

DBATools installation

We get the warning in the following screenshot because DBATools is already installed in my system. In my environment, I have version 0.8.693 however latest version is 0.9.781.

DBATools installation and upgrade

Run the following command to upgrade the version.

It downloads the required package and installs it in PowerShell module.

DBATools installation and upgrade

Once the DBATools PowerShell Module is installed, run the following command to enable remote execution of PowerShell scripts.

enable remote execution of PowerShell scripts

PowerShell Module in Azure Data Studio

Azure Data Studio is a cross-platform integrated tool for SQL Server. It also has an in-built terminal for PowerShell, SSH. We can use this to run the SQL queries as well as PowerShell Command. We will use Azure Data Studio to run the commands using DBATools PowerShell Module.

Launch Azure Data Studio. Go to View -> Command Palette and Terminal: Create new Integrated Terminal.

PowerShell Module in Azure Data Studio

In the PowerShell terminal, run the following command to get a list of commands containing word Login.

Get-help command in DBATools

Similarly, in the following image, you can see the filtered results for keyword *Copy-SQLServe*

Showing filtered results for keyword *Copy-SQLServe*

In this next section, we will look on a few commands in DBATools PowerShell module.

Test-DbaDatabaseOwner

: We can check database owners for a database.

We can get information about this command using the following query.

It gives information about this command along with brief information, description, and related links.

Let us run the command on SQL named instance SQL2019CTP.

Get-help command ifor Test-DbaDatabaseOwner DBATools

It gives information about each database in the instance. We did not specify any login name; therefore, it checks for SA by default.

Use cases of command Test-DbaDatabaseOwner in DBATools

In the output, we get the row for each property in the database. If we have many databases, it is difficult to read details. We can format the result in a tabular format using the following query. It represents the output in an excellent tabular representation.

In this output, we can see that OwnerMatch is False for highlighted databases as their owner is not SA. You can check this in column TargetOwner.

Use cases of command Test-DbaDatabaseOwner in DBATools

Let us change database owner for SQLShackDemo_ADR to Rajendra using following command.

Let us rerun the command and check for the login ‘rajendra’. In the output, we can see OwnerMatch is True for database SQLShackDemo_ADR.

Checking the database owner name in the output

We can use -SqlCredential parameter to login with alternate credentials in SQL Server instance. We can use both Windows and SQL authentication.

Suppose we want to login to localhost\SQL2019CTP using SQL authentication user rajendra and want to check database where targetlogin does not match ‘kashish\Test’.

Run the below command in terminal.

It opens a pop-up window to provide a password for the user specified in -SqlCredential.

Use cases of command Test-DbaDatabaseOwner in DBATools

We login to the instance using rajendra and then checks for database owner that does not match targetowner.

Use cases of command Test-DbaDatabaseOwner in DBATools

We can further customize the output in a grid view using parameter Out-GridView. In a Grid view, we get an interactive result window. In this interactive result window, we can filter out the results as well.

Run the following code to check databases with target login ‘Kashish\Test’ in a grid output window.

Use cases of command Test-DbaDatabaseOwner in DBATools

In the Add Criteria, you get a list of all columns present in the output. Suppose we want to filter based on the database name. Select Database from the drop-down and mention the database name for which we want to filter the results. It is an interactive filtering of results. We get the filtered result as soon as we start typing on the filter box.

In the following image, you can see the filtered results for the database name contains SQLShackDemo.

Use cases of command Test-DbaDatabaseOwner in DBATools - Grid view

We can filter the result set for a particular database using –Database parameter. In the following code, we filtered out the result for SQLShackDemo database.

We get only result for a particular database in this case.

Use cases of command Test-DbaDatabaseOwner in DBATools

Get-DbaSpConfigure

DBA need to configure various parameters in SQL Server such as maximum memory, minimum memory, dedicated administration connection, allow remote access etc. In SQL Server, we d do most of the configurations using the sp_configure command. DBATools contains PowerShell command Get-DbaSpConfigure to configure these setting easily and effectively.

First, let us get the information about Get-DbaSpConfigure using the get-help command. It lists out the synopsis, description, syntax for this DBATool command.

Use cases of command Get-DbaSpConfigure in DBATools

The Get-DbaSpConfigure command returns all server level configuration information in SQL Server. In the following query, we specified the SQL instance name to get the all supported configuration using this command. We want the result in tabular format, therefore, specified Format-Table.

We can see it return the default value, run value and description for each configuration.

Use cases of command Get-DbaSpConfigure in DBATools

It is quite a long list to go through. You can check the few configurations output in the following screenshot as well.

Use cases of command Get-DbaSpConfigure in DBATools

We do not need information about all system configurations most of the time. Suppose we want to check whether default backup compression is enabled or not in our SQL instance. We need to pass particular configuration in –Name parameter. Execute following DBATool command to get detailed information about this configuration.

In the output, you can see we get excellent information such as MinValue, MaxValue, ConfiguredValue, and RunningValue. It also gives a column to show whether in the specified instance, we are running with the default value for this parameter or not.

Use cases of command Get-DbaSpConfigure in DBATools

Similarly, following DBATool command list the configuration details for Cost threshold of Parallelism.

Use cases of command Get-DbaSpConfigure in DBATools

In the following query, we are checking whether the dedicated administrator connection is enabled on SQL instance or not.

Use cases of command Get-DbaSpConfigure in DBATools

Once we get the information of any particular parameter, we can use DBATool command Set-DbaSpConfigure to change the configuration value. Suppose we want to enable the dedicated administrator connection in SQL instance. Previously, we checked that this configuration is not enabled for specified SQL instance. We can enable it using the following code by specifying configuration value 1 for ‘RemoteDacConnectionsEnabled.’

Use cases of command Get-DbaSpConfigure in DBATools

Similarly, in the following code, we are changing the Cost threshold of parallelism value to 100.

In the output, we get the Previous and New value for the configuration setting.

Use cases of command Get-DbaSpConfigure in DBATools

Let us verify this change using SSMS. Connect to SQL instance. Open Server properties and go to Advanced.

In the following screenshot, we can see the Cost Threshold for Parallelism value is 100. It is the value we changed using DBATools configuration.

Verify configuration change from DBATools with SSMS

Conclusion

In this article, we explored the use of PowerShell module DBATools to perform various DBA tasks. It contains many such important commands. I will continue exploring useful command and share in future articles.

Table of contents

DBATools PowerShell Module for SQL Server
PowerShell SQL Server Validation Utility – DBAChecks
SQL Database Backups using PowerShell Module – DBATools
IDENTITY columns threshold using PowerShell SQL Server DBATools
DBATools PowerShell SQL Server Database Backups commands
SQL Restore Database using DBATools
Validate backups with SQL restore database operations using DBATools
Fix Orphan users in SQL Server using DBATools PowerShell
Creating a SQL Server Database using DBATools
Get SQL Database details using DBATools
Get-DbaHelpIndex command in DBATools
Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views