ARTICLE

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

Listing 2 shows what happens when you use Get-Help.

Listing 2. Getting help for Test-DbaConnection

You can test the connection to a local instance as shown in listing 3.

Listing 3. Check a connection to a local instance

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.

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

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

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:

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.

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

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

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.

Follow Manning Publications on Medium for free content and exclusive discounts.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store