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.
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.
Right click on Windows PowerShell and Run as administrator.
Run the following command to install DBATools module. In PowerShell.
|
1 |
>Install-Module -Name DBATools |
DBATools automatically downloads the dependencies and installs them. We need to give permission by entering Y to import the NuGet provider as shown here.
We also need to accept the module from an untrusted repository. Enter Y to accept and go ahead with the 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.
Run the following command to upgrade the version.
|
1 |
>Install-Module -Name DBATools -Force |
It downloads the required package and installs it in PowerShell module.
Once the DBATools PowerShell Module is installed, run the following command to enable remote execution of PowerShell scripts.
|
1 |
> set-executionpolicy remotesigned |
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.
In the PowerShell terminal, run the following command to get a list of commands containing word Login.
|
1 |
>Get-Command -Name *login* |
Similarly, in the following image, you can see the 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.
|
1 |
> get-help Test-DbaDatabaseOwner |
It gives information about this command along with brief information, description, and related links.
Let us run the command on SQL named instance SQL2019CTP.
|
1 |
> Test-DbaDbOwner -SqlInstance localhost\SQL2019CTP |
It gives information about each database in the instance. We did not specify any login name; therefore, it checks for SA by default.
|
1 |
>> Test-DbaDbOwner -SqlInstance localhost\SQL2019CTP |
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.
|
1 |
>> Test-DbaDbOwner -SqlInstance localhost\SQL2019CTP | Format-Table |
Let us change database owner for SQLShackDemo_ADR to Rajendra using following command.
|
1 |
sp_changedbowner 'rajendra' |
Let us rerun the command and check for the login ‘rajendra’. In the output, we can see OwnerMatch is True for database SQLShackDemo_ADR.
|
1 |
>Test-DbaDbOwner -SqlInstance localhost\SQL2019CTP -TargetLogin 'rajendra' | Format-Table |
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.
|
1 |
>Test-DbaDbOwner -SqlInstance localhost\SQL2019CTP -SqlCredential rajendra -TargetLogin 'kashish\Test' | Format-Table |
It opens a pop-up window to provide a password for the user specified in -SqlCredential.
We login to the instance using rajendra and then checks for database owner that does not match targetowner.
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.
|
1 |
>Test-DbaDbOwner -SqlInstance localhost\SQL2019CTP -SqlCredential rajendra -TargetLogin 'kashish\Test' | Out-GridView |
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.
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.
|
1 |
>Test-DbaDbOwner -SqlInstance localhost\SQL2019CTP -TargetLogin 'kashish\Test' -Database SQLShackDemo |
We get only result for a particular database in this case.
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.
|
1 |
> get-help Get-DbaSpConfigure |
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.
|
1 |
>Get-DbaSpConfigure -SqlInstance localhost\SQL2019CTP | Format-Table |
We can see it return the default value, run value and description for each configuration.
It is quite a long list to go through. You can check the few configurations output in the following screenshot as well.
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.
|
1 |
>Get-DbaSpConfigure -SqlInstance localhost\SQL2019CTP -Name 'DefaultBackupCompression' |
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.
Similarly, following DBATool command list the configuration details for Cost threshold of Parallelism.
|
1 |
Get-DbaSpConfigure -SqlInstance localhost\SQL2019CTP -Name 'CostThresholdForParallelism' |
In the following query, we are checking whether the dedicated administrator connection is enabled on SQL instance or not.
|
1 |
>Get-DbaSpConfigure -SqlInstance localhost\SQL2019CTP -Name 'RemoteDacConnectionsEnabled' |
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.’
|
1 |
>Set-DbaSpConfigure -SqlInstance localhost\SQL2019CTP -Name 'RemoteDacConnectionsEnabled' -Value 1 |
Similarly, in the following code, we are changing the Cost threshold of parallelism value to 100.
|
1 |
Set-DbaSpConfigure -SqlInstance localhost\SQL2019CTP -Name 'CostThresholdForParallelism' -Value 100 |
In the output, we get the Previous and New value for the configuration setting.
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.
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
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023