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-Help
  

Listing 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:ComputerName
  

TIP  $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     :  Core
  

Depending 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  $cred
  

Listing 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 required
  

IMPORTANT:  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-DbaService
  

That’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.