|
From Learn dbatools in a Month of Lunches by Chrissy LeMaire, Rob Sewell, Jess Pomfret, Cláudio Silva In this article you’ll learn about a few commands and four of the common dbatools parameters:
|
Take 40% off Learn dbatools in a Month of Lunches by entering fcclemaire into the discount code box at checkout at manning.com.
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 article is to get you comfortable with these common parameters. Having consistent parameters throughout the entire module was a high priority for the 1.0 release of dbatools.
Getting started
As DBAs it’s 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 is one that helps you to check if you can connect to the SQL Server instances. We figure that this was a good place to start because if you can’t connect to the SQL Server instance, then you can’t use any of the dbatools commands.
We’ll 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 chose these as our starting point as the‘are common scenarios that don’t perform any changes.
Checking the SQL connection
dbatools, like SQL Server Management Studio (SSMS) and any PowerShell command that you run against a SQL Server, can only accomplish what’s available to the user account which is running the PowerShell command.
No magic is involved here; at both the operating system level and the SQL Server level, you can only perform the actions which your user account has permission.
Before we start doing anything, it’s a good idea to check that the user account which is 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. This is a bit like using the connect dialogue in SSMS.
Figure 1. SSMS’s familiar connection dialog box
When this connects, you know at the least that SQL Server is running on that instance and that the account being used has CONNECT permissions.
Now we translate that into a dbatools command. Right now, we’re using this command to teach you some of the common dbatools parameters. As you progress with your dbatools and PowerShell learning, you’ll want to use this command to check if you’ve a working connection prior to running any further commands to get results which aren’t full of failed to connect errors.
The dbatools command that you 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.
First, getting help
In our experience, some PowerShell users may not know that help is available not only on StackOverflow or in Slack, but within PowerShell as well. Because of this, we’ll touch on Get-Help.
You can use the Get-Help
command to find out 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 which is new to you.
Although we won’t show the Get-Help
example for every command, 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.
Listing 1. Getting help for Test-DbaConnection
PS C:\> Get-Help Test-DbaConnection –Detailed
When you run the command in Listing 1, you can see output similar to the text in Figure 2.
Figure 2. Getting Help
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.
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 into remote servers via remote desktop (RDP) is considered unsafe by security professionals. 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.
Now, run your first dbatools command
Now we’re going to test the connection to your local SQL Server instance using Test-DbaConnection
. This command also checks the connection for PowerShell remoting, which helps run commands targeted at the operating system such as Get-DbaDiskSpace
.
Listing 2. Test SQL Engine and PowerShell remoting connectivity to a local instance
PS C:\> Test-DbaConnection -SqlInstance $Env:ComputerName
Note that when our commands reference $Env:ComputerName
or localhost
, it’s expected that each of these commands runs against a test instance on localhost.
$Env:ComputerName
is a PowerShell default environment variable containing the name of the current machine.
Now you can see output similar to the output in Listing 3. If you’ve a successful connection with the account running PowerShell to your local instance then the ConnectSuccess
property is true.
Notice that the output returns much more than whether there was a successful connection.
Listing 3. Example output of Test-DbaConnection
ComputerName : DEVSQL ❶ InstanceName : MSSQLSERVER ❷ SqlInstance : DEVSQL ❸ SqlVersion : 14.0.2002 ❹ ConnectingAsUser : AD\wdurkin ❺ 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 which is 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 operating system 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 don’t have a successful connection then you can see something similar to the error message in Listing 4.
Listing 4. SQL Server connection error, example failure
PS C:\> 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 as you get if you try to connect in SSMS and are unable to get a successful connection, as seen in Figure 3.
Figure 3. Connection failure in SSMS
Now that you know 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 works, 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.
The SqlInstance parameter
You learned your first common default dbatools parameter SqlInstance
. A PowerShell parameter follows the command name and it’s 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 Instance has a parameter of SqlInstance
(yes, there are some that don’t) to associate this to something that you’re familiar with, it’s, as you may expect, the same information that you enter into SSMS or put into a connection string. In this section we show how to use this parameter with multiple instances and non-default instances.
Single Instances
Let’s begin with connecting to a single instance.
Check a connection to a single remote default instance
When you connect to a remote default instance in SQL Server Management Studio (SSMS) or Azure Data Studio (ADS) 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
.
Connect-DbaInstance -SqlInstance PRODSQL01
Check 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 HOSTNAME\INSTANCENAME in exactly the same way as you use for SSMS or ADS.
PS C:\> Connect-DbaInstance -SqlInstance PRODSQL01\SHAREPOINT
Check a connection to a single local default instance
DBA is are used to using the . or localhost to represent the local hostname. When you run Test-DbaConnection
against the local default instance you can use the following values for the -SqlInstance
parameter:
$Env:ComputerName
as seen in the previous example- the name of the machine (
DEVSQL
in the prior example), localhost
Check a connection to a single local named instance
And for a local named instance you can use
- $Env:ComputerName\INSTANCENAME
- MACHINENAME\INSTANCENAME
- localhost\INSTANCENAME
You can even force the protocol you’d like to use by using it in the connection string, as you do in SSMS. For example, to force TCP to be used to connect to SQLPROD01, you do
Multiple instances
We covered local and remote instances and named instances, but only for a single instance. The SqlInstance
parameter isn’t limited to a single instance. You can run dbatools commands against multiple instances in a number of ways.
Nearly every dbatools command works against multiple instances.
Which one you choose to use in the PowerShell that you write sometimes depends purely on personal preference, and other times it’s dictated by the task you’re about to perform.
Multiple instances passed as an array
If you need to run a single command against a number of instances, perhaps because someone has walked to your desk and asked “Are the 3 PRODSQL Instances working ok?”, you can list the instance names separated by a comma.
PS C:\> Connect-DbaInstance -SqlInstance PRODSQL01, PRODSQL02, PRODSQL03\ShoeFactory
Piping in instance names
Your preference might be to pipe the Instances to the command.
PS C:\> "PRODSQL01", "PRODSQL02", "PRODSQL03\ShoeFactory" | Connect-DbaInstance
Instances stored in a variable
To save repeating yourself, if you 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
PS C:\> $instances = "PRODSQL01", "PRODSQL02", "PRODSQL03\ShoeFactory" PS C:\> Connect-DbaInstance -SqlInstance $instances
Alternatively, your preference may be to pipe the variable to the command.
PS C:\> $instances = "PRODSQL01", "PRODSQL02", "PRODSQL03\ShoeFactory" PS C:\> $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. If you frequently get asked by a Project Manager “Are all of my instances running?” 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 a SSMS connection window or add them to a folder in Central Management Server and run a query. To achieve the same result with dbatools you can use the code in Listing 5.
Listing 5. Get a list of instances from a database and set to a variable and pipe to the SqlInstance parameter
# Get Instance Names from database PS C:\> $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 C:\> $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 which is returned removes the column heading from the output. This is used in Listing 5 to ease readability.
Another way to do this is to remove the parentheses and instead pipe the results and parse with the ExpandProperty
parameter in Select-Object
.
PS C:\> $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 C:\> $instances | Connect-DbaInstance
Each approach is valid, but we recommend that you choose one way within your coding style and stick with it. This helps keep consistency throughout your project.
Instances using a non-default port number
If you connect to your SQL Server instance using a port number then this is provided to the SqlInstanc
e parameter in the same way that you provide it to the SSMS Connection box.
PS C:\> Connect-DbaInstance -SqlInstance "sqldev04,57689"
This is useful when the SQL Browser service isn’t enabled and your instance is on a non-default port.
Pay particular attention here to the use of quotes, as they tell PowerShell that the comma is part of the SQL 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.
PS C:\> Connect-DbaInstance -SqlInstance sqldev04:57689
Behind the scenes, we translate sqldev04:57689
to Microsoft’s required syntax, sqldev04,57689
.
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
is used to connect to a SQL Server instance and Credential
is used to connect to the operating system.
Table 1. SqlCredential or Credential |
|||
Protocol |
Sample Command |
Percentage of Commands |
SqlCredential or Credential |
SQL Database Engine |
Get-DbaDatabase |
62% |
SqlCredential |
WS-Management |
New-DbaClientAlias |
25% |
Credential |
SQL WMI |
Enable-DbaAgHadr |
4% |
Credential |
SMB over IP |
Get-DbaPfDataCollectorCounterSample |
<1% |
Credential |
A little over twenty commands in dbatools use both SqlCredential
and Credential
. This is 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.
Connecting to Instances with SQL Authentication
As previously mentioned, the SqlCredential
parameter is used to connect to the database engine using alternative credentials, including SQL authentication or even multi-factor authentication (MFA).
In the next example, we show how to connect to a SQL instance in dbatools using SQL Authentication. This is similar to providing a username and password in the SSMS connection box and choosing SQL Authentication, as seen in Figure 4.
Figure 4. SQL Authentication box in SSMS
You can do this with dbatools as well. You need to use the SqlCredential
parameter. dbatools commands that connect to the SQL Database engines always have a SqlCredential
parameter.
This is helpful when some of the instances in your estate aren’t joined to a domain or they aren’t joined to a domain which has trust with your primary domain.
It’s also useful testing the connection for applications that only support SQL authentication. In this case, you can test by providing the username for the SqlCredential
parameter of the Connect-DbaInstance
command.
PS C:\> Connect-DbaInstance -SqlInstance CORPSQL01 -SqlCredential devadmin
If you use PowerShell 6+ or VS Code, you will be prompted for the password as shown in Listing 6.
Listing 6. Use an alternative credential
PS C:\> Connect-DbaInstance -SqlInstance CORPSQL01 -SqlCredential devadmin PowerShell credential request Enter your credentials. Password for user devadmin:
Otherwise, it looks similar to the classic credential prompt as seen in Figure 5.
Figure 5. Classic credential prompt
Saving the credential to use SQL Authentication with multiple commands
More often, you run 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.
Listing 7. Assigning Credential to a variable
# Get the credential and set it to a variable PS C:\> $cred = Get-Credential # Connect to the local machine using the credential PS C:\> Connect-DbaInstance -SqlInstance $Env:ComputerName -SqlCredential $cred
This should result in output similar to Listing 8.
Listing 8. Using Connect-DbaInstance and Get-Credential in PowerShell 6+ and VS Code
PS C:\> $cred = Get-Credential PowerShell credential request Enter your credentials. User: devadmin Password for user devadmin: ********** PS C:\> Connect-DbaInstance -SqlInstance $Env:ComputerName -SqlCredential $cred Name Product Version Platform IsAzure IsClustered ConnectedAs (1) ---- ------- ------- -------- ------- ----------- ----------- SQLDEV Microsoft SQL Server 14.0.2027 NT x64 False False devadmin
You can see in the results that ConnectingAs (1) is shown as the devadmin user that we provided to the SqlCredential
parameter.
Other methods of using credentials for SQL 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 choose to use stored credentials, then you can use those credentials in dbatools commands as long as you can convert them into a [PSCredential]
object. The way to achieve this point depends on the product you use to secure your credentials. One method we’ve seen in the wild enables you to return a credential from a database using a stored procedure.
The example in Listing 9 shows how that could be used with the SqlCredential
parameter.
Listing 9. Convert password to credential
PS C:\> $query = "EXEC GetPasswordFromPasswordStore @UserName='AD\dbatools'" PS C:\> $securepassword = ConvertTo-SecureString (Invoke-DbaQuery -SqlInstance VerySecure -Database NoPasswordsHere -Query $query) -AsPlainText -Force PS C:\> $cred = New-Object System.Management.Automation.PSCredential ("AD\dbatools", $securepassword) PS C:\> 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 upon and should be avoided if at all possible.
Connecting to Instances with a different Windows Account
dbatools also allows you to connect to using an alternative Windows account. To do this, you can use SqlCredential
to specify the alternative account’s credentials.
Listing 10. Connect using an alternative Windows or Active Directory account
PS C:\> Connect-DbaInstance -SqlInstance SQLDEV01 -SqlCredential ad\sander.stad
This even works with Azure Active Directory (AAD) and Azure SQL Database.
Listing 11. Connect using AAD
# Create a server connection PS C:\> $server = Connect-DbaInstance -SqlInstance dbatools.database.windows.net -SqlCredential dbatools@mycorp.onmicrosoft.com -Database inventory # Use server connection to query the database using our query command, Invoke-DbaQuery PS C:\> Invoke-DbaQuery -SqlInstance $server -Database inventory -Query "select name from instances"
And in dbatools 1.0, we even added support for multi-factor authentication (MFA)!
Listing 12. Connect using MFA
# username is the application id, password is client secret PS C:\> 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 13. Perform a query using MFA
# Username is the application id, password is client secret PS C:\> $appcred = Get-Credential 52c1fbca-24ed-4353-bbf1-6dd52f535027 # Establish a connection PS C:\> $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 C:\> 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’s good practice to log into your workstation with a user account with minimal privileges and programs with an account with elevated privileges (your alternative admin account).
To run PowerShell as a different user, right click on the PowerShell icon in the task bar, hold shift and right-click the PowerShell icon and choose Run as different user
.
Figure 6. Running PowerShell as a different user
For more information on alternative credentials, including an in-depth discussion of Azure MFA, please visit dbatools.io/credentials.
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 that you’re familiar with, it’s, as you may expect, the same information that you 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 Services on multiple servers
Connect-DbaInstance
enables you to check the Database Engine. When another admin comes and asks you what SQL Server features are installed on a host you can use Get-DbaService
. This becomes commonplace when exploring PowerShell commands use Get-Help
to understand the function of the command and the syntax.
PS C:\> 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.
Listing 14. Listing the SQL Services on a remote server
PS C:\> Get-DbaService -ComputerName CORPSQL ComputerName : CORPSQL ServiceName : MsDtsServer140 ServiceType : SSIS InstanceName : DisplayName : SQL Server Integration Services 14.0 StartName : NT Service\MsDtsServer140 State : Stopped StartMode : Manual ComputerName : CORPSQL ServiceName : MSSQLSERVER ServiceType : Engine InstanceName : MSSQLSERVER DisplayName : SQL Server (MSSQLSERVER) StartName : NT Service\MSSQLSERVER State : Stopped StartMode : Manual ComputerName : CORPSQL ServiceName : SQLBrowser ServiceType : Browser InstanceName : DisplayName : SQL Server Browser StartName : NT AUTHORITY\LOCALSERVICE State : Stopped StartMode : Manual ComputerName : CORPSQL ❶ ServiceName : SQLSERVERAGENT ❷ ServiceType : Agent ❸ InstanceName : MSSQLSERVER ❹ DisplayName : SQL Server Agent (MSSQLSERVER) ❺ StartName : NT Service\SQLSERVERAGENT ❻ State : Stopped ❼ StartMode : Manual ❽
Results for both local and remote computers include:
The computer name ❶ The name of the service ❷ 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 service account ❻ The state 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’s required if it’s not local.
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.
# Computer Names as an array PS C:\> Get-DbaService -ComputerName SQL01, SQL02 # Computer Names piped to a command PS C:\> "SQL01", "SQL02" | Get-DbaService # Computer Names stored in a variable PS C:\> $servers = "SQL01", "SQL02" PS C:\> Get-DbaService -ComputerName $servers # Computer Names stored in a variable and piped to a command PS C:\> $servers = "SQL01", "SQL02" PS C:\> $servers | Get-DbaService
The Credential parameter
You may want to pass alternative credentials for connecting to the server as a different user to the one that runs the PowerShell process. dbatools commands that have a ComputerName
parameter always have a Credential
parameter to enable this.
List 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
Listing 15. Listing Services on a server using a different user
PS C:\> Get-DbaService -ComputerName CORPSQL -Credential AD\wdurkin PowerShell credential request Enter your credentials. Password for user AD\wdurkin:
List Services on a server using a different account with a credential variable
When you’re using multiple commands, you don’t 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.
Listing 16. Listing Services on a server using a different user
PS C:\> $cred = Get-Credential PowerShell credential request Enter your credentials. User: AD\wdurkin Password for user AD\wdurkin: ********** PS C:\> 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 5.
Listing SQL Services by type
Further tasks can be accomplished using Get-DbaService
. To list all of the SQL Services of a certain type you can use the Type
parameter. 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?
Let’s find out.
Listing 17. Listing the database engine services on a remote server
PS C:\> Get-DbaService -ComputerName CORPSQL -Type Engine ComputerName : CORPSQL ServiceName : MSSQL$BOLTON ServiceType : Engine InstanceName : BOLTON DisplayName : SQL Server (BOLTON) StartName : NT Service\MSSQL$BOLTON State : Stopped StartMode : Manual ComputerName : CORPSQL ServiceName : MSSQL$LONDON ServiceType : Engine InstanceName : LONDON DisplayName : SQL Server (LONDON) StartName : NT Service\MSSQL$LONDON State : Stopped StartMode : Manual ComputerName : CORPSQL ServiceName : MSSQL$SQL2016 ServiceType : Engine InstanceName : SQL2016 DisplayName : SQL Server (SQL2016) StartName : NT Service\MSSQL$SQL2016 State : Stopped StartMode : Manual ComputerName : CORPSQL ServiceName : MSSQLSERVER ServiceType : Engine InstanceName : MSSQLSERVER DisplayName : SQL Server (MSSQLSERVER) StartName : NT Service\MSSQLSERVER 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.
Listing 18. Listing the services for a specific instance
PS C:\> Get-DbaService -ComputerName CORPSQL -InstanceName BOLTON ComputerName : CORPSQL ServiceName : MSSQL$BOLTON ServiceType : Engine InstanceName : BOLTON DisplayName : SQL Server (BOLTON) StartName : NT Service\MSSQL$BOLTON State : Stopped StartMode : Manual ComputerName : CORPSQL ServiceName : SQLAgent$BOLTON ServiceType : Agent InstanceName : BOLTON DisplayName : SQL Server Agent (BOLTON) StartName : NT Service\SQLAgent$BOLTON State : Stopped StartMode : Manual
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 doesn’t support EnableException
, it’s Connect-DbaInstance
, which supports DisableException
instead.
Exception handling is bit of an advanced topic and it won’t be covered in this gentle introduction, but in the event that you’re an advanced programmer, we want 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, you’re ready to check out the rest of the goodies in the book here.