4 A gentle introduction to dbatools commands
Now you should be all set up and ready to start working with dbatools. In this chapter, you will learn about a few new commands and four of the common dbatools parameters: -SqlInstance, -SqlCredential, -ComputerName, and -Credential.
These parameters are particularly useful because they’re used in nearly every command to connect to both local and remote servers. The primary goal of this chapter is to get you comfortable with these common parameters. You will see them used throughout this book and in all of the dbatools that you will write in the future. Having consistent parameters throughout the entire module was a high priority for the 1.0 release of dbatools.
4.1 Getting started
As DBAs, it is in our nature to be wary; we want to understand what a tool is going to do before we let it anywhere near our production environment. For this reason, we’ll start with commands that are read-only.
The first command that we have chosen to use is one that will help you check that you are able to connect to the SQL Server instances. We figure that this is a good place to start because if you can’t connect to the SQL Server instance, then you will not be able to use any of the dbatools commands.
We will also show you how to list the services on the host for SQL Server and how to list the databases on an instance using dbatools. We have chosen these as our starting point not only because they are common scenarios that you will want to use, but also because they do not perform any changes.
4.2 Checking the SQL connection
dbatools, like SQL Server Management Studio (SSMS) and any PowerShell command that you run against a SQL Server, will be able to accomplish only what is available to the user account running the PowerShell command. There is no magic involved here. At both the operating system level and the SQL Server level, you will be able to perform only the actions that your user account has permissions to.
Before we start doing anything, it is a good idea to check that the user account running the PowerShell process can connect to the SQL Server instance and that the SQL Server instance is running before you start running other commands, as shown in figure 4.1. This is a bit like using the connect dialogue in SSMS.
When that connects, you know that, at the very least, SQL Server is running on that instance, and the account being used has CONNECT permissions.
Now we will translate that into a dbatools command. Right now, we are using this command to teach you some of the common dbatools parameters. As you progress with your dbatools and PowerShell learning, you will want to use this command to check that you have a working connection prior to running any further commands so that your results are not full of Failed to Connect errors.
The dbatools command that you will use to accomplish a test connection is appropriately named Connect-DbaInstance. We’ll also explore Test-DbaConnection, which not only connects to the database engine but performs a few other tests as well.
4.3 First, getting help
In our experience, some PowerShell users may not know that help is available not just on Stack Overflow or in Slack but also within PowerShell itself. Because of this, we’ll touch on Get-Help just once more.
As you learned in chapter 2, you can use the Get-Help command to learn how to use any PowerShell command, and we recommend that you remember to use Get-Help every time you want to use a PowerShell command that is new to you.
Although we won’t show the Get-Help example for every command throughout the book, it’s still a good idea to use it for each new command that you run. Even though we’ve been using PowerShell for years, Get-Help is our go-to command anytime we run a new command. Get-Help -Examples is a particular favorite. Let’s find out how to use Get-Help and Test-DbaConnection together, as shown in the next code sample.
Listing 4.1 Getting help for Test-DbaConnection
When you run the command in listing 4.1, you will see output similar to the text in figure 4.2.
In the synopsis, you can see that this command is for testing the SQL Server connection. The Parameters section shows the parameters available to this command.
As mentioned in chapter 2, dbatools, like SSMS, should be installed on as few servers as possible. This is to avoid performance impacts, reduce your attack surface, and increase maintainability. Logging in to remote servers via Remote Desktop (RDP) is actually considered unsafe by security professionals (see dbatools.io/secure). Not only is it more convenient to connect remotely to servers, it’s more secure. dbatools enables you to easily manage your entire estate from a centralized location.
4.4 Running your first dbatools command
Now we’re going to test the connection to your local SQL Server instance using Test-DbaConnection, as shown in the next code listing. This command will also check the connection for PowerShell remoting, which helps run commands targeted at the operating system, such as Get-DbaDiskSpace.
Listing 4.2 Testing SQL engine and PowerShell remote connectivity
Note that when our commands reference $Env:ComputerName or localhost, it is expected that each of these commands will be run against a test instance on localhost.
Tip $Env:ComputerName is a PowerShell default environment variable containing the name of the current machine.
Now you will see output similar to the output in listing 4.3. If you have a successful connection with the account running PowerShell to your local instance, then the ConnectSuccess property will be true. Notice that the output returns much more than just whether there was a successful connection.
Listing 4.3 Example output of Test-DbaConnection
ComputerName : DEVSQL ① InstanceName : MSSQLSERVER ② SqlInstance : DEVSQL ③ SqlVersion : 14.0.2002 ④ ConnectingAsUser : ADwdurkin ⑤ ConnectSuccess : True ⑥ AuthType : Windows Authentication ⑦ AuthScheme : NTLM ⑧ TcpPort : 1433 ⑨ IPAddress : 172.16.11.162 ⑩ NetBiosName : DEVSQL ⑪ IsPingable : True ⑫ PSRemotingAccessible : True ⑬ DomainName : AD.local ⑭ LocalWindows : 10.0.17134.0 ⑮ LocalPowerShell : 6.1.0 ⑯ LocalCLR : ⑰ LocalSMOVersion : 15.1.18068.0 ⑱ LocalDomainUser : True ⑲ LocalRunAsAdmin : True ⑳ LocalEdition : Core ㉑
① The Windows name of the machine
② The SQL Server instance—MSSQLSERVER is a default instance.
③ The machine name returned from SQL
④ The SQL Server build version
⑤ The user account connecting to SQL
⑥ Was there a successful SQL connection?
⑦ The SQL authentication type used
⑧ The SQL authentication scheme used
⑨ The SQL TCP port of the instance
⑩ The IP address of the SQL Server instance
⑪ The NetBIOS name of the SQL Server instance
⑫ The result of ICMP echo request to the SQL Server instance
⑬ The PowerShell Remoting status of the SQL host operating system
⑭ The domain name or workgroup the SQL Server instance is joined to
⑮ The OS version number of the machine running the dbatools command
⑯ The PowerShell version used to run the dbatools command
⑰ The version of the common language runtime
⑱ The version of the SQL Server Management Objects used
⑲ Is the local user account running the dbatools command a domain user?
⑳ Is the process running the dbatools command running in an elevated session?
㉑ The PowerShell edition of the process running the dbatools command
If you do not have a successful connection, then you will see something similar to the error message in the next code snippet.
Listing 4.4 SQL Server connection error, example failure
PS> Get-DbaDatabase -SqlInstance SQLDEV01 WARNING: [00:02:07][Get-DbaDatabase] Error occurred while establishing connection to SQLDEV01 | A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
This is exactly the same result you would have if you tried to connect in SSMS and were unable to get a successful connection, as seen in figure 4.3.
Now that you have learned how to test the connection to your local default instance, you might think that all you need to do is install dbatools on every machine with a SQL Server instance and then use $Env:ComputerName. Although that would work, one of the advantages of using PowerShell and dbatools is the ability to run commands against multiple instances with a single command. Returning to the SSMS analogy, you can connect to multiple remote SQL Server instances from a single laptop. How do you do the same with dbatools? Let’s explore the -SqlInstance parameter.
4.5 The -SqlInstance parameter
You have learned your first common default dbatools parameter: -SqlInstance. A PowerShell parameter follows the command name and is defined by the fact that it starts with a hyphen. The parameter enables the user running the command to provide input or to select options.
Every dbatools command that needs to connect to a SQL Server instance has a parameter of -SqlInstance (yes, there are some that don’t, which we will also learn about). To associate this to something that you are familiar with, it is, as you may expect, the same information that you would enter into SSMS or put into a connection string. In this section, we will show how to use this parameter with multiple instances and nondefault instances.
4.5.1 Single instances
Let’s begin with connecting to a single instance.
Checking a connection to a single remote default instance
When you connect to a remote default instance in SSMS or Azure Data Studio (ADS), just the name of the remote host is required. When connecting to a remote default instance with dbatools, you only need to add the name of the remote host following the -SqlInstance, as shown in the next code listing.
Listing 4.5 Connecting to a remote instance
Checking a connection to a single remote named instance
If you want to test the connection to a named instance, then you can use the format HOSTNAMEINSTANCENAME in exactly the same way as you would use for SSMS or ADS, as shown next.
Listing 4.6 Connecting to a remote named instance
Checking a connection to a single local default instance
DBAs are used to using the . or localhost to represent the local hostname. When you are running Test-DbaConnection against the local default instance, you can use the following values for the -SqlInstance parameter:
Checking a connection to a single local named instance
For a local named instance, you can use the following:
You can even force the protocol you’d like to use by using it in the connection string, just like you would in SSMS. For example, to force TCP to be used to connect to SQLPROD01, you would use TCP:SQLPROD01SHAREPOINT.
4.5.2 Multiple instances
We have covered local and remote instances and named instances, but only for a single instance. The -SqlInstance parameter is not limited to a single instance. You can run dbatools commands against multiple instances in a number of ways. Let’s look at some of the different methods that you can use, and will see us use, throughout this book and in the reading you will do online.
Tip Nearly every dbatools command works against multiple instances. Which one you will choose to use in the PowerShell that you write will sometimes depend purely on personal preference. Other times, it is dictated by the task that you are about to perform.
Multiple instances passed as an array
If you just need to run a single command against a number of instances, perhaps because someone has walked to your desk and asked, “Are the three PRODSQL Server Instances working okay?,” you can just list the instance names separated by a comma, as shown in the following code.
Listing 4.7 Connecting to multiple instances
Your preference might be to pipe the instances to the command, as shown in the following code snippet.
Instances stored in a variable
To avoid repeating yourself, if you are going to run a number of dbatools commands against the same instances, you can define a variable as a list of instances and then provide that variable to the dbatools command, as shown in the following listing.
Listing 4.9 Storing values in a variable
PS> $instances = "PRODSQL01", "PRODSQL02", "PRODSQL03ShoeFactory" PS> Connect-DbaInstance -SqlInstance $instances
Alternatively, your preference may be to pipe the variable to the command, like so.
Listing 4.10 Piping values from a variable
PS> $instances = "PRODSQL01", "PRODSQL02", "PRODSQL03ShoeFactory" PS> $instances | Connect-DbaInstance
Instances from a separate source
If you have a list of instances in a database, you might want to use that to be able to gather the instances for a dbatools command. For example, if you are frequently asked by a project manager, “Are all of my instances running?” you know the instances are stored in a database with a reference to the project manager, and you know the query to gather the instance names.
You could copy and paste those instance names one by one into an SSMS connection window or add them to a folder in Central Management Server (CMS) and run a query. To achieve the same result with dbatools, you can use the code in the following listing.
Listing 4.11 Piping to the -SqlInstance parameter
# Get Instance Names from database PS> $instances = (Invoke-DbaQuery -SqlInstance ConfigInstance ➥ -Database DbaConfig -Query "SELECT InstanceName FROM ➥ Config.Instances C JOIN Project.People P ON C.InstanceID = ➥ P.InstanceID WHERE P.Name = 'Shawn Melton'").InstanceName PS> $instances | Connect-DbaInstance
You may notice that the instances variable is the result of some code being wrapped in parentheses. Placing a command in parentheses and referencing a property that is returned will remove the column heading from the output. This is used in listing 4.11 to ease readability. Another way to do this would be to remove the parentheses and instead pipe the results and parse with the -ExpandProperty parameter in Select-Object, as shown next.
Listing 4.12 Using -ExpandProperty
PS> $instances = Invoke-DbaQuery -SqlInstance ConfigInstance ➥ -Database DbaConfig -Query "SELECT InstanceName FROM ➥ Config.Instances C JOIN Project.People P ON C.InstanceID = ➥ P.InstanceID WHERE P.Name = 'Shawn Melton'" | Select-Object ➥ -ExpandProperty InstanceName PS> $instances | Connect-DbaInstance
Each approach is valid, but we recommend that you choose one way within your coding style and stick with it. This will help keep consistency throughout your project.
Instances using a nondefault port number
If you connect to your SQL Server instance using a port number, then this is provided to the -SqlInstance parameter in the same way as you provide it to the SSMS Connection dialog box, as shown next.
Listing 4.13 Connecting to an instance using a nondefault port
This is useful when the SQL browser service isn’t enabled, and your instance is on a nondefault port. Pay particular attention here to the use of quotes, because they tell PowerShell that the comma is part of the SQL Server instance name and not an array. If you use Linux or macOS and are used to the host:port syntax, we support that syntax as well, as shown in the next code snippet.
Listing 4.14 Connecting to an instance using a nondefault port with a colon
Behind the scenes, we just translate sqldev04:57689 to Microsoft’s required syntax, sqldev04,57689.
4.6 The -SqlCredential parameter
Before discussing the -SqlCredential parameter in depth, we’d like to highlight the difference between -SqlCredential and -Credential. Back in the early days of dbatools, we agreed as a team that -SqlCredential would be used to connect to a SQL Server instance whereas -Credential would be used to connect to the operating system. To outline this, we’ll borrow from table 2.3, minus the port column, as shown in table 4.1.
Table 4.1 -SqlCredential or -Credential
A little over 20 commands in dbatools use both -SqlCredential and -Credential because they connect to both the SQL Database Engine and an OS component, such as the Windows Registry or a shared drive. One such command is Test-DbaMaxMemory, which uses -SqlCredential to get the maximum memory setting and -Credential to calculate how many instances exist in total on the host server.
4.6.1 Connecting to instances with SQL Server Authentication
As previously mentioned, the -SqlCredential parameter is used to connect to the database engine using alternative credentials, including SQL Server Authentication or even multifactor authentication (MFA).
In the next example, we will show how to connect to a SQL Server instance in dbatools using SQL Server Authentication. This is similar to providing a username and password in the SSMS Connection dialog box and choosing SQL Server Authentication, as seen in figure 4.4.
You can do this with dbatools as well. You will need to use the -SqlCredential parameter. dbatools commands that connect to the SQL database engine will always have a -SqlCredential parameter.
This is especially helpful when some of the instances in your estate are not joined to a domain or they are not joined to a domain that has trust with your primary domain. It’s also useful testing the connection for applications that support only SQL Server Authentication. In this case, you can test by providing the username for the -SqlCredential parameter of the Connect-DbaInstance command, as shown in the next code sample.
Listing 4.15 Using an alternative credential
If you are using PowerShell 6+ or VS Code, you will be prompted for the password as shown next.
Listing 4.16 Using an alternative credential in PowerShell 6+
PS> Connect-DbaInstance -SqlInstance CORPSQL01 -SqlCredential devadmin PowerShell credential request Enter your credentials. Password for user devadmin:
Otherwise, it will look similar to the classic credential prompt as seen in figure 4.5.
4.6.2 Saving the credential to use SQL Server Authentication with multiple commands
More often, you will be running more than one command against your SQL Server instances. You don’t want to be entering the password for every command.
In the same way as you saved the instances as a variable earlier, you can save your credential in memory as a variable. You do this by passing a PSCredential object to the -SqlCredential parameter. The most common way of doing this is to use the Get-Credential command, illustrated in the next listing.
Listing 4.17 Assigning -Credential to a variable
# Get the credential and set it to a variable PS> $cred = Get-Credential # Connect to the local machine using the credential PS> Connect-DbaInstance -SqlInstance $Env:ComputerName -SqlCredential $cred
This should result in output similar to the following code.
Listing 4.18 Using Connect-DbaInstance and Get-Credential
PS> $cred = Get-Credential PowerShell credential request Enter your credentials. User: devadmin Password for user devadmin: ********** PS> Connect-DbaInstance -SqlInstance $Env:ComputerName -SqlCredential $cred Name Product Version Platform IsAzure IsClustered ConnectedAs ① ---- ------- ------- -------- ------- ----------- --------- SQLDEV Microsoft SQL Server 14.0.2027 NT x64 False False devadmin
You can see in the results that ConnectedAs ① is shown as the devadmin user that we provided to the -SqlCredential parameter.
4.6.3 Other methods of using credentials for SQL Server Authentication
Depending on how you store your credentials, you may be able to access them programmatically. You can store credentials locally and securely using built-in PowerShell commands like Export-CliXml or using community modules like Joel Bennett’s BetterCredentials. For more information on stored credentials, visit dbatools.io/ credentials.
If you do choose to use stored credentials, then you will be able to use those credentials in dbatools commands as long as you can convert them into a PSCredential object. The way you achieve that will depend on the product you use to secure your credentials, so providing a good example is troublesome. One method we have seen in the wild enables you to return a credential from a database using a stored procedure. The example in the next listing shows how that could be used with the -SqlCredential parameter.
Listing 4.19 Converting a password to a credential
PS> $query = "EXEC GetPasswordFromPasswordStore @UserName='ADdbatools'" PS> $securepassword = ConvertTo-SecureString (Invoke-DbaQuery -SqlInstance ➥ VerySecure -Database NoPasswordsHere -Query $query) -AsPlainText -Force PS> $cred = New-Object System.Management.Automation.PSCredential ( ➥ "ADdbatools", $securepassword) PS> Test-DbaConnection -SqlInstance $Env:ComputerName -SqlCredential $cred
Note that the -Force parameter is required by ConvertTo-SecureString when converting plain text to a SecureString. This is because passwords being transmitted as plain text is frowned on and should be avoided if at all possible.
4.6.4 Connecting to instances with a different Windows account
dbatools also allows you to connect by using an alternative Windows account. To do this, you can use -SqlCredential to specify the alternative account’s credentials, as shown next.
Listing 4.20 Connecting using an alternative Windows or Active Directory account
This even works with Azure Active Directory (AAD) and Azure SQL Database, as shown here.
Listing 4.21 Connecting using AAD
# Create a server connection PS> $server = Connect-DbaInstance -SqlInstance dbatools.database.windows ➥ .net -SqlCredential [email protected] -Database inventory # Use server connection to query the database using our query command, ➥ Invoke-DbaQuery PS> Invoke-DbaQuery -SqlInstance $server -Database inventory -Query ➥ "select name from instances"
And in dbatools 1.0, we even added support for multifactor authentication (MFA), as shown in the next two code samples!
Listing 4.22 Connecting using MFA
# username is the application id, password is client secret PS> Connect-DbaInstance -SqlInstance dbatools.database.windows.net ➥ -SqlCredential 52c1fbca-24ed-4353-bbf1-6dd52f535027 -Tenant ➥ ec46e088-2707-4b0a-ab0d-dee0b52fc5c8 -Database inventory Name Product Version Platform IsAzure IsClustered ConnectedAs ---- ------- ------- -------- ------- ----------- ----------- tcp:dbatools.database.windows.net 12.0.1600 True 52c1fbca-etc@ec46e088-etc
Listing 4.23 Performing a query using MFA
# Username is the application id, password is client secret PS> $appcred = Get-Credential 52c1fbca-24ed-4353-bbf1-6dd52f535027 # Establish a connection PS> $server = Connect-DbaInstance -SqlInstance dbatools.database.windows ➥ .net -Database inventory -SqlCredential $appcred -Tenant ➥ 6b73c0ef-114d-43ad-94c9-85a4a82cde8b # Now that the connection is established, use it to perform a query PS> Invoke-DbaQuery -SqlInstance $server -Database dbatools -Query ➥ "SELECT Name FROM sys.objects" Name ---- sysrscols sysrowsets sysclones sysallocunits sysfiles1 sysseobjvalues syspriorities sysdbfrag sysfgfrag ...
Alternatively, you can run the entire PowerShell process as another user. It is good practice to log in to your workstation with a user account with minimal privileges and in to programs with an account with elevated privileges (your alternative admin account).
To run PowerShell as a different user, right-click the PowerShell icon in the task bar, hold Shift and right-click the PowerShell icon, and choose Run as Different User, as illustrated in figure 4.6. For more information on alternative credentials, including an in-depth discussion of Azure MFA, please visit dbatools.io/credentials.
4.7 The ComputerName parameter
All dbatools commands that connect to a server use the -ComputerName parameter by default. To associate this to something that you are familiar with, it is, as you may expect, the same information that you would enter into a Remote Desktop connection. You can enter hostnames, fully qualified names, and IP addresses.
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 Server Services on multiple servers.
Connect-DbaInstance enables you to check the database engine. When another admin asks you which SQL Server features are installed on a host, you can use Get-DbaService. As will become commonplace when exploring PowerShell commands, use Get-Help to understand the function of the command and the syntax, as shown next.
Listing 4.24 Get-Help for Get-DbaService
PS> 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] [<CommonParameters>] Get-DbaService [[-ComputerName] <DbaInstanceParameter[]>] [-Credential <PSCredential>] [-ServiceName <String[]>] [-AdvancedProperties] [-EnableException] [<CommonParameters>]
Note that the description provides additional information about required privileges.
LocalAdmin permissions required The account running the Get-DbaService command or provided to the -Credential parameter must have local admin permissions on the remote computer.
To find SQL-related services on a remote server, use the -ComputerName parameter, as shown in the next listing.
Listing 4.25 Listing the SQL services on a remote server
PS> Get-DbaService -ComputerName CORPSQL ComputerName : CORPSQL ServiceName : MsDtsServer140 ServiceType : SSIS InstanceName : DisplayName : SQL Server Integration Services 14.0 StartName : NT ServiceMsDtsServer140 State : Stopped StartMode : Manual ComputerName : CORPSQL ServiceName : MSSQLSERVER ServiceType : Engine InstanceName : MSSQLSERVER DisplayName : SQL Server (MSSQLSERVER) StartName : NT ServiceMSSQLSERVER State : Stopped StartMode : Manual ComputerName : CORPSQL ServiceName : SQLBrowser ServiceType : Browser InstanceName : DisplayName : SQL Server Browser StartName : NT AUTHORITYLOCALSERVICE State : Stopped StartMode : Manual ComputerName : CORPSQL ① ServiceName : SQLSERVERAGENT ② ServiceType : Agent ③ InstanceName : MSSQLSERVER ④ DisplayName : SQL Server Agent (MSSQLSERVER) ⑤ StartName : NT ServiceSQLSERVERAGENT ⑥ State : Stopped ⑦ StartMode : Manual ⑧
③ The type of service: Agent, Browser, Engine, FullText, SSAS, SSIS, SSRS, or PolyBase
④ The name of the SQL Server instance (if applicable)
⑤ The display name of the service
⑧ The start mode of the service
Note that when running this command locally, there is no requirement to use the -ComputerName parameter, but it is required if it’s not local.
4.7.1 Methods of listing the SQL services on multiple servers
Your DBA manager asks you to identify all of the SQL Server features on a number of hosts in your test cluster. You can pass hostnames to the -ComputerName parameter using the same methods that you learned for the -SqlInstance parameter, as shown here.
Listing 4.26 Listing SQL services on multiple servers
# Computer Names as an array PS> Get-DbaService -ComputerName SQL01, SQL02 # Computer Names piped to a command PS> "SQL01", "SQL02" | Get-DbaService # Computer Names stored in a variable PS> $servers = "SQL01", "SQL02" PS> Get-DbaService -ComputerName $servers # Computer Names stored in a variable and piped to a command PS> $servers = "SQL01", "SQL02" PS> $servers | Get-DbaService
4.8 The -Credential parameter
You may want to pass alternative credentials for connecting to the server as a different user than the one that is running the PowerShell process. dbatools commands that have a -ComputerName parameter will always have a -Credential parameter to enable this.
4.8.1 Listing services on a server using a different account at the command line
In the same way as you learned with -SqlCredential, you can provide the username with the -Credential parameter, and you will be prompted for the password, as shown in the next code sample.
Listing 4.27 Listing services on a server using a different user
PS> Get-DbaService -ComputerName CORPSQL -Credential ADwdurkin PowerShell credential request Enter your credentials. Password for user ADwdurkin:
4.8.2 Listing services on a server using a different account with a credential variable
When you use multiple commands, you do not want to keep typing the password. You can also pass a PSCredential object to the -Credential parameter. One way of doing this is to use the Get-Credential command, shown here.
Listing 4.28 Listing services on a server using a variable with a different user
PS> $cred = Get-Credential PowerShell credential request Enter your credentials. User: ADwdurkin Password for user ADwdurkin: ********** PS> Get-DbaService -ComputerName CORPSQL -Credential $cred
The in-console password prompt is a feature of PowerShell 6+. In earlier versions of PowerShell, expect the classic credential prompt as seen in figure 4.7.
4.8.3 Listing SQL services by type
You can accomplish further tasks using Get-DbaService. To list all of the SQL services of a certain type, you can use the -Type parameter, as shown in the next listing. This can help you to answer questions such as, are all of the instances on that server using the same service account (StartName) for the database engine?
Listing 4.29 Listing the database engine services on a remote server
PS> Get-DbaService -ComputerName CORPSQL -Type Engine ComputerName : CORPSQL ServiceName : MSSQL$BOLTON ServiceType : Engine InstanceName : BOLTON DisplayName : SQL Server (BOLTON) StartName : NT ServiceMSSQL$BOLTON State : Stopped StartMode : Manual ComputerName : CORPSQL ServiceName : MSSQL$LONDON ServiceType : Engine InstanceName : LONDON DisplayName : SQL Server (LONDON) StartName : NT ServiceMSSQL$LONDON State : Stopped StartMode : Manual ComputerName : CORPSQL ServiceName : MSSQL$SQL2016 ServiceType : Engine InstanceName : SQL2016 DisplayName : SQL Server (SQL2016) StartName : NT ServiceMSSQL$SQL2016 State : Stopped StartMode : Manual ComputerName : CORPSQL ServiceName : MSSQLSERVER ServiceType : Engine InstanceName : MSSQLSERVER DisplayName : SQL Server (MSSQLSERVER) StartName : NT ServiceMSSQLSERVER State : Stopped StartMode : Manual
You can also use Get-DbaService to get the services for a single instance if you have multi-instance SQL Servers. You do this using the -InstanceName parameter, shown next.
Listing 4.30 Listing the services for a specific instance
PS> Get-DbaService -ComputerName CORPSQL -InstanceName BOLTON ComputerName : CORPSQL ServiceName : MSSQL$BOLTON ServiceType : Engine InstanceName : BOLTON DisplayName : SQL Server (BOLTON) StartName : NT ServiceMSSQL$BOLTON State : Stopped StartMode : Manual ComputerName : CORPSQL ServiceName : SQLAgent$BOLTON ServiceType : Agent InstanceName : BOLTON DisplayName : SQL Server Agent (BOLTON) StartName : NT ServiceSQLAgent$BOLTON State : Stopped StartMode : Manual
4.9 Bonus parameter: EnableException
All of our commands except for one include the parameter -EnableException. This is because, by default, “sea of red” PowerShell exceptions are disabled in favor of useful and more attractive error messages. If you’re wondering about the single command that does not support -EnableException, it is Connect-DbaInstance, which supports -DisableException instead.
Exception handling is bit of an advanced topic, so we won’t cover it in this gentle introduction. But in the event that you are an advanced programmer, we wanted to make you aware of the way we handle exceptions. For more information about this topic, please visit dbatools.io/exceptions.
Now that you’ve learned how to run a few dbatools commands and you’ve learned about four of our common parameters, let’s find all of the SQL Server instances on your network.
4.10 Hands-on lab
Let’s use what you have read about in this chapter to get comfortable with dbatools commands. Try the following tasks:
-
Identify the user account that is running the SQL agent service.
-
Write a command to return the databases without a log backup.
-
Write a command to return the databases without a log backup in the last 30 minutes.
-
Find any databases without a full backup on your test instance.