Big Data, Data Analytics and Data Science | BI-BIGDATA.com

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