Managing Your SQL Servers

Using Windows PowerShell for SQL Server Management

The graphical management tools provide just about everything you need to work with SQL Server. Still, there are many times when you might want to work from the command line, such as when you are working on a Windows Server 2008 R2 Core installation. To help with all your command-line needs, SQL Server 2012 includes the SQL Server provider for Windows PowerShell (also known as “SQL Server PowerShell”). To work with SQL Server via Windows PowerShell, you must first open a Command Prompt window or Windows PowerShell prompt and then start SQL Server PowerShell by typing sqlps at the command line.

Windows PowerShell introduces the concept of a cmdlet (pronounced “commandlet”). A cmdlet is the smallest unit of functionality in Windows PowerShell. Cmdlet names are not case-sensitive. SQL Server PowerShell cmdlets include the following:

• Backup-SQLDatabase. Performs backup operations on SQL Server databases.

• Convert-UrnToPath. Converts a SQL Server Management Object Uniform Resource Name (URN) to a SQL Server provider path. The URN indicates a management object’s location within the SQL Server object hierarchy. If the URN path has characters not supported by Windows PowerShell, the characters are encoded automatically.

• Decode-SQLName. Returns an unencoded SQL Server identifier when given an identifier that has been encoded.

• Encode-SQLName. Encodes special characters in SQL Server identifiers and name paths to formats that are usable in Windows PowerShell paths. The characters encoded by this cmdlet include \:/%<>*?[]|. If you don’t encode these characters, you must escape them by using the single quotation mark (‘) character.

• Invoke-PolicyEvaluation. Evaluates management policies applied to SQL Server instances. By default, this cmdlet reports compliance but does not enforce compliance. To enforce compliance, set–AdHocPolicyEvaluationMode to Configure.

• Invoke-Sqlcmd. Runs a Transact-SQL (T-SQL) or XQuery script containing commands supported by the SQLCMD utility. By default, this cmdlet doesn’t set any SQLCMD variables or return message output; only a subset of SQLCMD commands can be used.

• Restore-SQLDatabase. Performs restore operations on SQL Server databases.

To get detailed information about a cmdlet, type get-help cmdletname–detailed, where cmdletname is the name of the cmdlet you want to examine. To get detailed information about the SQL Server provider, which provides SQL Server functionality for Windows PowerShell, type get-help sqlserver | more.

Import-Module "sqlps" –DisableNameChecking

You can work with cmdlets by executing commands directly at the shell prompt or by running commands from scripts. You can enter any command or cmdlet that you can run at the Windows PowerShell command prompt into a script by copying the related command text to a file and saving the file with the .ps1 extension. You can then run the script in the same way that you would any other command or cmdlet. However, when you are working with Windows PowerShell, the current directory might not be part of the environment path. For this reason, you might need to use the ./ notation when you run a script in the current directory, such as the following:

./runtasks

The current execution policy for SQL Server PowerShell controls whether and how you can run scripts. Although the default configuration depends on which operating system and edition you’ve installed, you can quickly determine the execution policy by entering get-executionpolicy at the Windows PowerShell prompt.

To set the execution policy to require that all scripts have a trusted signature to execute, enter the following command:

set-executionpolicy allsigned

To set the execution policy so that scripts downloaded from the web execute only if they are signed by a trusted source, enter:

set-executionpolicy remotesigned

To set the execution policy to run scripts regardless of whether they have a digital signature and work in an unrestricted environment, you can enter the following command:

set-executionpolicy unrestricted

For administration at the Windows PowerShell prompt, you use Invoke-Sqlcmd to run T-SQL or XQuery scripts containing commands supported by the SQLCMD utility. Invoke-Sqlcmd fully supports T-SQL and the XQuery syntax supported by the Database Engine, but it does not set any scripting variables by default. Invoke-Sqlcmd also accepts the SQLCMD commands listed in Table 1-3, later in this chapter. By default, results are formatted as a table, with the first result set displayed automatically and subsequent result sets displayed only if they have the same column list as the first result set.

The basic syntax you use most often with Invoke-Sqlcmd follows:

Invoke-Sqlcmd [-ServerInstance ServerStringOrObject]
[-Database DatabaseName] [-EncryptConnection ]
[-Username UserName] [-Password Password] [[-Query] QueryString]
[-DedicatedAdministratorConnection]

[-InputFile FilePath] [ | Out-File –filepath FilePath]

The command’s parameters are used as follows:

• –Database. Specifies the name of the database that you want to work with. If you don’t use this parameter, the database that is used depends on whether the current path specifies both the SQLSERVER:\SQL folder and a database name. If both are specified, Invoke-Sqlcmd connects to the database that is specified in the path. Otherwise, Invoke-Sqlcmd connects to the default database for the current login ID.

  NOTE

  Use–IgnoreProviderContext to force a connection to the database that is defined as the default for the current login ID.

• –DedicatedAdministratorConnection. Ensures that a dedicated administrator connection (DAC) is used to force a connection when one might not be possible otherwise.

• –EncryptConnection. Enables Secure Sockets Layer (SSL) encryption for the connection.

• –InputFile. Provides the full path to a file that should be used as the query input. The file can contain T-SQL statements, XQuery statements, SQLCMD commands, and scripting variables. Spaces are not allowed in the file path or file name.

• –Password. Sets the password for the SQL Server Authentication login ID that is specified in –Username.

• –Query. Defines one or more queries to be run. The queries can be T-SQL queries, XQuery statements, or SQLCMD commands. Separate multiple queries with semicolons.

  TIP

  You do not need to use the SQLCMD GO command. Escape any double quotation marks included in the string and consider using bracketed identifiers such as [EmpTable] instead of quoted identifiers such as “EmpTable”. To ensure that the message output is returned, add the –Verbose parameter. –Verbose is a parameter common to all cmdlets.

• –ServerInstance. Specifies the name of an instance of the Database Engine that you want to work with. For default instances, specify only the computer name, such as DbServer23. For named instances, use the format “ComputerName\InstanceName”, such as DbServer23\EmployeeDb.

• –Username. Sets the login ID for making a SQL Server authentication connection to an instance of the Database Engine. You also must set the password for the login ID.

With this in mind, you could replace the following T-SQL statements:

USE OrderSystem;
GO
SELECT * FROM Inventory.Product
ORDER BY Name ASC
GO

with the following Windows PowerShell command:

Invoke-Sqlcmd -Query "SELECT * FROM Inventory.Product; ORDER BY Name ASC"
-ServerInstance "DbServer23\OrderSystem"

You also could read the commands from a script, as shown in Sample 1-1.

Sample 1-1 Example SQL Command Script.

Contents of SqlCmd.sql Script.

SELECT * FROM Inventory.Product
ORDER BY Name ASC

Command to Run the Script

Invoke-Sqlcmd -InputFile "C:\Scripts\SqlCmd.sql"

When you work with Windows PowerShell, don’t overlook the importance of SQL Server support being implemented through a provider. The data that providers expose appears as a drive that you can browse. One way to browse is to get or set the location with respect to the SqlServer: provider drive. The top of the hierarchy exposed is represented by the SQL folder, then there is a folder for the machine name, and finally, there is a folder for the instance name. Following this, you could navigate to the top-level folder for the default instance by entering

Set-Location SQLSERVER:\SQL\DbServer23\Default

You could then determine the available database structures by entering Get-ChildItem (or one of its aliases, such as ls or dir). To navigate logins, triggers, endpoints, databases, and any other structures, you set the location to the name of the related folder. For example, you could use Set-Location Databases and then enter Get-ChildItem to list available databases for the selected instance. Of course, if you know the full path you want to work with in the first place, you also can access it directly, as shown in the following example:

Set-Location SQLSERVER:\SQL\DbServer23\Default\Databases\OrderSystem

Here, you navigate to the structures for the OrderSystem database on DbServer23’s default instance. If you then want to determine what tables are available for this database, you could enter:

Get-ChildItem Tables

Or you could enter:

Set-location Tables
Get-ChildItem

To manage SQL Server 2012 from a computer that isn’t running SQL Server, you need to install the management tools. In the SQL Server Installation Center, select Installation, and then click the New Installation Or Add Features To An Existing Installation option. When the wizard starts, follow the prompts. On the Feature Selection page, select the Management Tools—Basic option to install Management Studio, SQLCMD, and the SQL Server provider for Windows PowerShell.

For remote management via Windows PowerShell, you need to ensure that Windows Remote Management (WinRM) and Windows PowerShell are both installed and made available by using the Add Features Wizard. You also need to enable remote commands on both your management computer and the server running SQL Server.

You can verify the availability of WinRM and configure Windows PowerShell for remoting by following these steps:

1. Click Start, All Programs, Accessories, and Windows PowerShell. Then start Windows PowerShell as an administrator by right-clicking the Windows PowerShell shortcut and selecting Run As Administrator.

2. The WinRM service is configured for manual startup by default. You must change the startup type to Automatic and start the service on each computer you want to work with. At the PowerShell prompt, you can verify that the WinRM service is running by using the following command:

   get-service winrm

   As shown in the following example, the value of the Status property in the output should be Running:

   Status Name DisplayName ------ ---- ----------- Running WinRM Windows Remote Management

   If the service is stopped, enter the following command to start the service and configure it to start automatically in the future:

   set-service –name winrm –startuptype automatic –status running

3. To configure Windows PowerShell for remoting, type the following command:

   Enable-PSRemoting –force

   You can enable remoting only when your computer is connected to a domain or private network. If your computer is connected to a public network, you need to disconnect from the public network and connect to a domain or private network and then repeat this step. If one or more of your computer’s connections has the Public connection type but you are actually connected to a domain or private network, you need to change the network connection type in the Network And Sharing Center and then repeat this step.

In many cases, you can work with remote computers in other domains. However, if the remote computer is not in a trusted domain, the remote computer might not be able to authenticate your credentials. To enable authentication, you need to add the remote computer to the list of trusted hosts for the local computer in WinRM. To do so, type the following:

winrm s winrm/config/client '@{TrustedHosts="RemoteComputer"}'

where RemoteComputer is the name of the remote computer, such as

winrm s winrm/config/client '@{TrustedHosts="DbServer23"}'

When you are working with computers in workgroups or homegroups, you must use HTTPS as the transport or add the remote machine to the TrustedHosts configuration settings. If you cannot connect to a remote host, you can verify that the service on the remote host is running and is accepting requests by running the following command on the remote host:

winrm quickconfig

This command analyzes and configures the WinRM service. If the WinRM service is set up correctly, you see output similar to the following:

WinRM already is set up to receive requests on this machine.
WinRM already is set up for remote management on this machine.

If the WinRM service is not set up correctly, you see errors and need to respond affirmatively to several prompts that allow you to configure remote management automatically. When this process is complete, WinRM should be set up correctly. Don’t forget that you need to enable remote management on the database server as well as your management computer.