Managing SQL Server with PowerShell: Part 1 – The Tools

By now it’s pretty common knowledge that PowerShell is Microsoft’s preferred management tool and over the past few years, they have really extended the capabilities of PowerShell to where it can be used for just about every management task. There are even some things that can only be done in PowerShell. Even so, PowerShell is mostly a foreign entity to most SQL Server administrators. The vast majority of SQL Server management is done using T-SQL scripts and SQL Server Enterprise Manager.

Import the SQLPS Module

Microsoft’s recommended way of managing SQL Server with PowerShell is by using the sqlps module. Before using the sqlps module you need to import it into PowerShell using the following Import-Module cmdlet.
Import-Module “sqlps” -DisableNameChecking

The DisableNameChecking parameter is used to handle the object naming incompatibilities between SQL Server and PowerShell.

Importing sqlps is required when you are working with SQL Server from the standard PowerShell console. As you can see near the top of Figure 1, if you attempt to navigate with the SQL Server PowerShell module before its loaded you will get an error. After importing the SQL Server PowerShell module you can then use the module to navigate and perform other PowerShell commands with the SQL Server instance.

Running PowerShell from SQL Server Management Studio

You can also run PowerShell directly from SQL Server Management Studio (SSMS). If you run PowerShell,you can see launching PowerShell from SSMS in Figure 2.

When you launch the PowerShell console from SSMS the sqlps module is already installed and ready to be used. Unlike the normal blue PowerShell console, by default when you launch the PowerShell console from SSMS its black. Apart from that there’s little difference and it provides the same sqlps functionality.

Navigating Using the SQL Server PowerShell Provider

One of the ways that makes PowerShell different from other command shells like the Windows Command Shell is the fact that PowerShell can not only be used to enter commands, it can also be used for navigation into a variety of different sources. For instance, while the Windows Command Shell is capable of navigating the file system, using dir and cd commands PowerShell takes that same model and extends it. PowerShell can navigate the file system and in addition via the use of different providers it can be used to navigate, the Windows registry, Active Directory and even SQL Server. For SQL Server, you can use it to navigate through database objects, such as tables, views, and stored procedures. In the PowerShell console shown in Figure 2, you can see that the SQL Server PowerShell provider is opened by the use of the PS SQLSERVER:\SQL notation at the beginning of the prompt. The first part of the PS SQLSERVER:\SQL path is SQLSERVER:\SQL\<ComputerName>\<InstanceName> where the ComputerName is the name of the system and the InstanceName is the name of the SQL Server instance. In Figure 2, you can see the PowerShell provider uses DEFAULT for the default instance. From this root prompt, you can use the cd and dir commands to navigate through the SQL Server management tree. For instance in Figure 2, you can see how the cd and dir command are used to navigate to the PS SQLSERVER:\SQL\localhost\DEFAULT\Databases\ node which lists all of the databases. You can also continue and drill down into the database and table level. The sqlps provider provides navigation into the following folders:

• SQLSERVER:\SQL – Database object
• SQLSERVER:\SQLPolicy – Policy based management objects
• SQLSERVER:\SQLRegistration – Registered server objects
• SQLSERVER:\Utility – Utility objects
• SQLSERVER:\DAC – Data-tier application objects
• SQLSERVER:\DataCollection – Data collector objects
• SQLSERVER:\IntegrationServices – Integration Services objects and packages
• SQLSERVER:\SQLAS – Analysis Services objects

Using the Database Engine cmdlets

So far you’ve seen how you can load and use the SQL Server PowerShell provider to navigate a SQL Server instance. You can also use PowerShell to query SQL Server, run commands and more. You can use the SQL Server PowerShell cmdlets for these tasks.  There are cmdlet’s to create and manage Availability Groups, Backup, Restore and Policies. One of the most important is the Invoke-Sqlcmd cmdlet which can be used to execute T-SQL and XQuery commands and scripts. You can see an example of using Invoke-Sqlcmd in the following listing.
PS C:\Users\mikeo2> Invoke-Sqlcmd -Query “SELECT @@VERSION;”
Column1
——-
Microsoft SQL Server 2014 – 12.0.2269.0 (X64)
PS SQLSERVER:\>

Pipeline to Part 2

In part 1, I covered the basics of getting started using PowerShell with SQL Server. In part 2,I’ll continue on and dive in to many of the practical examples of performing several common SQL Server management tasks with PowerShell.