Advertisements
Home > Powershell, SQL Server > PowerShell and SQL Server Works Together

PowerShell and SQL Server Works Together

Before I write anything about PowerShell and MS SQL Server, we need to understand few important concepts and terminology which will help us to identify that PowerShell and MS SQL Server works together.

  • PSDrive

In PowerShell there is cmdlet called PSDrive for SQL Server, which can be used for access, manipulate and navigate SQL Server Objects. Get-PSDrive lists all drives in current session which can be used in PowerShell Script. Basically this cmdlet lists all Windows logical drives, Session-specified temporary drives, persistent mapped network drives, drives exposed by windows PowerShell providers (such as the Certificate:, Function:, and Alias: drives) and the HKLM: and HKCU: drives that are exposed by the Windows PowerShell Registry provider.

When we execute Get-PSDrive cmdlet we can see something similar to the following:

  • PSProvider

Get-PSProvider is PowerShell cmdlet which gets the windows PowerShell providers in the current session. PowerShell allows different data stores to be accessed as if they are regular files and folders. PSProvider is similar to an adapter, which allows these data stores to be seen as drives.

When we execute Get-PSProvider cmdlet we can see something similar to the following:

  • Modules and Snap-Ins

Modules and snap-ins are ways to extend PowerShell. Both modules and snap-ins can add cmdlets and providers to your current session. Modules can additionally load functions, variables, aliases, and other tools to your current session. Snap-ins are Dynamically Linked Libraries (DLL), and need to be registered before they can be used. Snap-ins are available in V1, V2, and V3. For example:

Add-PSSnapin SqlServerCmdletSnapin100

Modules, on the other hand, are more like your regular PowerShell *.ps1 script files. Modules are available in V2 and V3. You do not need to register a module to use it, you just need to import:

Import-Module SQLPS
  • Execution Policy

By default, PowerShell will stand by the current execution policy to determine what kind of scripts can be run. Let’s say, we are going to assume that you will run PowerShell as the administrator on your test environment. You will also need to set the execution policy to RemoteSigned:

Set-ExecutionPolicy RemoteSigned

This setting will allow PowerShell to run digitally-signed scripts, or local unsigned scripts.

One of the strongest points for PowerShell is that it simplifies automation and integration between different Microsoft ecosystems. As most products have support for PowerShell, getting one system to talk to another is just a matter of discovering what cmdlets, functions, or modules need to be pulled into the script. Even if the product does not have support yet for PowerShell, it most likely has .NET or COM support, which PowerShell can easily use.

Sandip

Advertisements
  1. March 9, 2013 at 10:39 am

    Great blog post thanks for posting

  2. vps
    March 14, 2013 at 8:58 pm

    I think your blog is amazing. You write about very interesting things. Thanks for all the tips and information.

  3. vps
    March 14, 2013 at 10:11 pm

    This blog is amazing. I realy love it!

  4. johnny
    March 15, 2013 at 4:44 pm

    This post is great. I realy love it!

  5. September 2, 2015 at 5:47 am

    Excellent read

    Roberta Nasser, CIR
    home office / fax: 206.285.8477
    mobile: 206.854.9215
    linkedin.com/in/robertanasser
    twitter.com/robertanasser
    about.me/robertanasser

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: