Introduction to the SQL Server 2012 PowerShell Provider

Introduction to the SQL Server 2012 PowerShell Provider

  • Comments 2
  • Likes

Summary: Microsoft Scripting Guy, Ed Wilson, provides an introduction to the SQL Server 2012 provider for Windows PowerShell.

Microsoft Scripting Guy, Ed Wilson, is here. One of the really cool things about SQL Server 2012 is the SQL Server provider for Windows PowerShell. By using the SQL Server provider, you gain access to a driver that permits working with SQL Server 2012 in the same way that you might work with the file system. It becomes another drive, just like the other Windows PowerShell drives.

Note   For more information about the various Windows PowerShell drives, see this series of Hey, Scripting Guy! Blog posts.

After you install SQL Server 2012, there are two additional downloads you need to gain access to the SQL Server 2012 provider for Windows PowerShell. The first is the Microsoft Windows PowerShell Extensions for Microsoft SQL Server 2012, and the second is the Microsoft SQL Server 2012 Shared Management Objects. Both of these packages come in x86 and in x64 flavors, and they are available via the Microsoft Download site.

Note   To work with the SQL Server provider, you do not have to enable script execution on your system. If you decide to enable script execution on your system, take a look at The Scripting Wife Sets the Script Execution Policy.

Creating the remote Windows PowerShell console session

I am going to create a remote Windows PowerShell console session on my computer running SQL Server. The server’s is named SQL1, and I am going to pass alternate credentials when I make the connection. I type two commands. The first creates a credential object that I store in a variable named $cred. When this command runs, a pop-up dialog box appears as shown here.

Image of dialog box

The second command uses the credential object that I just created to make a remote Windows PowerShell session on the remote computer. When I connect to the remote computer, the Windows PowerShell prompt changes to include the name of the remote computer. Here are the commands I type:

$cred = Get-Credential nwtraders\administrator

Enter-PSSession -ComputerName sql1 -Credential $cred

Note   I have SQL Server 2012 running on a machine that is also running Windows Server 2012. As such, Windows PowerShell remoting is already configured—it just works. In Windows Server 2008 or Windows Server 2008 R2, you need to run the Enable-PSRemoting command to configure Windows PowerShell remoting. I have written several Hey, Scripting Guy! Blogs about this topic.

Now that I have entered a Windows PowerShell session on the remote server running SQL Server 2012, I need to load the SQL Server module for Windows PowerShell. To do this I use the Import-Module cmdlet.

Note   A warning message appears because the SQL Server team used two unapproved cmdlet verbs. The unapproved cmdlet verbs are Encode and Decode. If you do not want to see the warning message (which does not impact performance) use the DisableNameChecking switch.

Exploring the SQL Server provider

The cool thing about the SQL Server provider for Windows PowerShell is that it exposes a SQLSERVER drive. I can use commands like dir (an alias for the Get-ChildItem cmdlet) or cd (an alias for the Set-Location cmdlet) to navigate through SQL Server. Under the covers, the SQL Server provider is masking the complexity of the SQL Server Management Objects (SMOs), and every level in the hierarchy exposes a different SMO.

To begin, I type dir. The results of this command (and my previous commands) are shown here.

Image of command output

As you can see, there are a number of different folders that provide management capabilities at different levels of the SQL Server hierarchy. For more information, see SQL Server PowerShell Provider (on Microsoft TechNet, not on MSDN). These folders and the associated capabilities are shown in the image that follows.

Navigating the SQLSERVER drive

To navigate the SQLSERVER drive, use the Get-ChildItem and the Set-Location cmdlets (or use the dir and cd aliases). I like to navigate to a directory, then use dir to produce a list of that directory, and then decide which directory to explore next. This technique is shown here.

Image of command output

To move up a level in the hierarchy, I use the cd alias with a couple of dots as shown here.

Cd ..

Join me tomorrow when I will talk about more cool Windows PowerShell and SQL Server 2012 stuff as I explore a specific SQL Server instance in the hierarchy.

Note   For more information about using Windows PowerShell with SQL Server (multiple versions) refer to this collection of Hey, Scripting Guy! Blog posts.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy 

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • SQL Server Express LocalDB is great to create and work with databases from code. Is it possible to connect the SQL Server provider for Windows PowerShell to LocalDB instances?  

  • @wf - if SQS is running then it is connected.

    dir sqlserver:\sql\$env:computername\sqlexpress\databases

    This will list all databases on the local copy of SQLExpress.