ARTICLE
Getting to Know dbatools
From Learn dbatools in a Month of Lunches by Chrissy LeMaire and Rob Sewell
Interested in dbatools?
Here’s a quick introduction to some dbatools commands and common dbatools parameters.
__________________________________________________________________
Take 37% off Learn dbatools in a Month of Lunches by entering fcclemaire into the discount code box at checkout at manning.com.
__________________________________________________________________
Something simple to start with, i.e. a gentle introduction to dbatools commands
As DBAs, we know that DBAs are cautious folk. It’s in our nature to be wary and to want to understand what something is going to do before we let it anywhere near our production environments. You’re reading this article to learn how to make use of dbatools. You’ll be introduced to some dbatools commands and common dbatools parameters.
This article contains no commands which change your SQL Server instance. Ultimately, you‘re responsible for the code which you run and there is no guarantee of the effect on any system. We recommend, as with all code that you run against your SQL instances, that you test all code before running it on any production instances.
Checking the SQL connection
Dbatools, and any PowerShell command which you run against an SQL Server, can only accomplish what is available to the user account running the PowerShell, both at the operating system level, as well as at the SQL Server level. Checking that the user account running the PowerShell process can connect and that the SQL Server is running and accessible to that user is recommended before running other commands. The command which you use to accomplish this is shown in listing 1.
Listing 1. Check a connection
Test-DbaConnection
To find out how to use it, you need to use Get-HelpListing 2 shows what happens when you use Get-Help.
Listing 2. Getting help for Test-DbaConnection
Get-Help Test-DbaConnection
NAME
Test-DbaConnection
SYNOPSIS
Tests the connection to a single instance.
SYNTAX
Test-DbaConnection [[-SqlInstance] <DbaInstanceParameter[]>] [[-Credential] <PSCredential>] [[-SqlCredential] <PSCredential>] [-EnableException]
[<CommonParameters>]
DESCRIPTION
Tests the ability to connect to an SQL Server instance outputting information about the server and instance.
PARAMETERS
-SqlInstance <DbaInstanceParameter[]>
The SQL Server Instance to test connection
-Credential <PSCredential>
Credential object used to connect to the Computer as a different user
-SqlCredential <PSCredential>
Login to the target instance using alternative credentials. Windows and SQL Authentication supported. Accepts credential objects (Get-Credential)
-EnableException [<SwitchParameter>]
By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but it inconvenient because it disables advanced scripting. Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.You can test the connection to a local instance as shown in listing 3.
Listing 3. Check a connection to a local instance
Test-DbaConnection -SqlInstance $ENV:ComputerNameTIP: $Env:ComputerName is a PowerShelldefault environment variable containing the name of current machine
This shows you an output similar to the below. Notice that it returns much more than whether there was a successful connection.
ComputerName : ROB-XPS (1)
InstanceName : (2)
SqlInstance : ROB-XPS (3)
SqlVersion : 14.0.2002 (4)
ConnectingAsUser : Beard\Rob (5)
ConnectSuccess : True (6)
AuthType : Windows Authentication (7)
AuthScheme : NTLM (8)
TcpPort : 1433 (9)
IPAddress : 172.16.11.162 (10)
NetBiosName : ROB-XPS (11)
IsPingable : True (12)
PSRemotingAccessible : True (13)
DomainName : Beard.local (14)
LocalWindows : 10.0.17134.0 (15)
LocalPowerShell : 6.1.0 (16)
LocalCLR : (17)
LocalSMOVersion : 15.1.18068.0 (18)
LocalDomainUser : True (19)
LocalRunAsAdmin : True (20)
LocalEdition : Core (21)1 The Windows name of the machine
2 The SQL Instance — The default instance is blank
3 The machine name returned from SQL
4 The SQL Server build version
5 The user account which is connecting to SQL
6 Was there a Successful SQL connection
7 The SQL authentication type used
8 The SQL authentication scheme used
9 The SQL TCP port of the instance
10 The IP address of the SQL instance
11 The Netbios name of the SQL instance
12 The result of ICMP echo request to the SQL instance
13 The PowerShell Remoting status of the SQL host
14 The domain name or workgroup which the SQL instance is joined to
15 The operating system version number of the machine running the dbatools command
16 The PowerShellversion used to run the dbatools command
17 The version of the common language runtime
18 The version of the SQL Server Management Objects used
19 Is the local user account running the dbatools command a domain user
20 Is the process running the dbatools command running in an elevated session
21 The PowerShell edition of the process running the dbatools command
The SqlInstance parameter
One of the first common default dbatools parameter is SqlInstance. This is used in all dbatools commands which connect to a SqlInstance! (Yes, there are some that don’t!). To associate this to something which you’re familiar with, it’s, as you may expect, the same information that you would enter into SSMS or put into a connection string.
For a default instance, the name of the host is required although HOST(XXX HostName? XXXX)\MSSQLSERVER also works. If you’ve a named instance then you use HOST\InstanceName.
The SqlInstance parameter isn’t limited to a single instance. You can run dbatools commands against multiple instances in a number of ways.
Listing 4. Multiple instances to the SQLInstance parameter
T
est-DbaConnection -SqlInstance SQL01,SQL02 (1)
$Instances = 'SQL01','SQL02'
Test-DbaConnection -SqlInstance $Instances (2)
SQL01,SQL02 | Test-DbaConnection (3)
$Instances = 'SQL01','SQL02'
$Instances | Test-DbaConnection (4)
#Get Instance Names from database
$Instances = (Invoke-DbaQuery -SqlInstance ConfigInstance -Database Instances -Query "SELECT InstanceName FROM Config").InstanceName $Instances | Test-DbaConnection (5)1 Specify a comma delimited list of instances
2 Use a variable with an array of instances
3 Pipe a comma delimited list of servers to the command
4 Use a variable with an array of instances and pipe it to the command
5 Gather the instance names from a database and use them in a variable piped to the command.
NOTE: Placing a command in braces () and referencing a property which is returned removes the column heading from the output as seen in listing 4 above.
The SqlCredential parameter
You may not have permissions on the SQL instance that you wish to connect to for your Windows or domain user account and need to connect with SQL authentication. dbatools commands that connect to SQL always have a SqlCredential parameter to enable this.
To use a dbatools command with SQL authentication you can provide the username with the SqlCredential parameter and you’ll be prompted for the password.
Listing 5. Check a connection to a local instance with SQL authentication
Test-DbaConnection -SqlInstance . -SqlCredential TheBeardRules
Sun 23:18 [22.964ms] dbatools-MoL:\> Test-DbaConnection -SqlInstance . -SqlCredential TheBeardRules
PowerShell credential request Enter your credentials.
Password for user TheBeardRules:
You can also pass a PSCredential object to the SqlCredential parameter. One way of doing this is to use the Get-Credential command
$Cred = Get-Credential
Test-DbaConnection -SqlInstance $ENV:ComputerName -SqlCredential $cred
Sun 23:32 [7.300ms] dbatools-MoL:\> $cred = Get-Credential
PowerShell credential request Enter your credentials.
User: TheBeardRules
Password for user TheBeardRules: **********
Sun 23:32[8.211s] dbatools-MoL:\> Test-DbaConnection -SqlInstance $ENV:ComputerName -SqlCredential $cred
ComputerName : ROB-XPS
InstanceName :
SqlInstance : ROB-XPS
SqlVersion : 14.0.2002
ConnectingAsUser : TheBeardRules
ConnectSuccess : True
AuthType : SQL Authentication
AuthScheme : SQL
TcpPort : Can't connect to TCP:ROB-XPS: System.ComponentModel.Win32Exception (10061): No connection could be made because the target machine actively refused it
IPAddress : 172.16.11.162
NetBiosName : ROB-XPS
IsPingable : True
PSRemotingAccessible : True
DomainName : WORKGROUP
LocalWindows : 10.0.17134.0
LocalPowerShell : 6.1.0
LocalCLR :
LocalSMOVersion : 15.1.18068.0
LocalDomainUser : False
LocalRunAsAdmin : True
LocalEdition : CoreDepending on how you store your credentials, you may be able to access them programmatically. If this is the case then you’ll be able to use those credentials in dbatools commands as long as you can convert them into the following:
[PSCredential] object
$Query = "EXEC GetPasswordFromPasswordStore @UserName='Beard\dbatools'"
$SecurePassword = Invoke-DbaQuery -SqlInstance VerySecure -Database NoPasswordsHere -Query $Query
$SecurePassword = ConvertTo-SecureString $SecurePassword -AsPlainText -Force
$cred = New-Object System.Management.Automation.PSCredential ("Beard\dbatools", $SecurePassword)
Test-DbaConnection -SqlInstance $ENV:ComputerName -SqlCredential $credListing SQL Services
Test-DbaConnection enables you to check the Database Engine, to be able to find other SQL Server features which are installed on a computer you can use Get-DbaService. As is commonplace when exploring PowerShell commands use Get-Help to understand the function of the command and the syntax.
Get-Help Get-DbaService
Synopsis
Gets the SQL Server related services on a computer.
Description
Gets the SQL Server related services on one or more computers.
Requires Local Admin rights on destination computer(s).
Syntax
Get-DbaService [[-ComputerName] <DbaInstanceParameter[]>] [-InstanceName <String[]>] [-Credential <PSCredential>] [-Type <String[]>] [-AdvancedProperties] [-EnableException] [<Co
Get-DbaService [[-ComputerName] <DbaInstanceParameter[]>] [-Credential <PSCredential>] [-ServiceName <String[]>] [-AdvancedProperties] [-EnableException] [<CommonParameters>]
LocalAdmin permissions requiredIMPORTANT: The account running the Get-DbaService command or provided to the Credential parameter must have Local Admin permissions on the remote computer
You can get the services on a computer by running the following:
Listing 6. Listing the SQL Services on a server
Get-DbaService -ComputerName Beard
Which provides a response like
Sun 17:01 [90.001ms] dbatools-MoL:\> Get-DbaService -ComputerName Beard
ComputerName : Beard
ServiceName : MsDtsServer140 ServiceType : SSIS InstanceName :
DisplayName : SQL Server Integration Services 14.0
StartName : NT Service\MsDtsServer140
State : Stopped
StartMode : Manual
ComputerName : Beard
ServiceName : MSSQLSERVER
ServiceType : Engine
InstanceName : MSSQLSERVER
DisplayName : SQL Server (MSSQLSERVER)
StartName : NT Service\MSSQLSERVER
State : Stopped
StartMode : Manual
ComputerName : Beard
ServiceName : SQLBrowser ServiceType : Browser InstanceName :
DisplayName : SQL Server Browser
StartName : NT AUTHORITY\LOCALSERVICE
State : Stopped
StartMode : Manual
ComputerName : Beard (1)
ServiceName : SQLSERVERAGENT (2)
ServiceType : Agent (3)
InstanceName : MSSQLSERVER (4)
DisplayName : SQL Server Agent (MSSQLSERVER) (5)
StartName : NT Service\SQLSERVERAGENT (6)
State : Stopped (7) StartMode : Manual (8)This returns the following information.
1 The computer name
2 The name of the service
3 The type of service — This can be one of Agent,Browser, Engine, FullText, SSAS, SSIS, SSRS, PolyBase
4 The name of the SQL Instance (if applicable)
5 The display name of the service
6 The state of the service
7 The start mode of the service
TIP: When running this command locally there’s no requirement to use the ComputerName parameter.
The ComputerName parameter
dbatools commands use the ComputerName parameter by default. This is used in all dbatools commands which connect to a server. To associate this to something you’re familiar with, it’s, as you may expect, the same information that you’d enter into a Remote Desktop Connection.
You can pass one or multiple servers to the ComputerName parameter in the same way as you can with the SqlInstance parameter. This means that you can list the SQL Services on multiple servers, as shown in listing 7.
Listing 7. Methods of listing the SQL services on multiple servers
Get-DbaService -ComputerName SQL01, SQL02
SQL01, SQL02 | Get-DbaService
$Servers = 'SQL01', 'SQL02'
Get-DbaService -ComputerName $Servers
$Servers = 'SQL01', 'SQL02'
$Servers | Get-DbaServiceThat’s all for now.
If you want to learn more about the book, check it out on our browser-based liveBook reader here and see this slide deck.
