Introduction to the SQL Server 2012 PowerShell Provider
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.
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.
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.
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
Light
Dark
0 comments