Install sqlps as a PowerShell module

Most of SQL Server automation scripts using PowerShell use SMO directly. For example, one would do something like:

$MyServer = new-object (‘Microsoft.SqlServer.Management.Smo.Server’) ‘HOME’
$MyDataBase = new-object (‘Microsoft.SqlServer.Management.Smo.Database’)
($Server, “MyDataBase”)

That’s a lot of typing and looks messy and cumbersome.

Since SQL Server 2008, Microsoft provides a sqlps shell which exposes a lot of SQL Server objects and functions for easy browsing and inspection, but one couldn’t import that into an existing scripts for easy automation. With the release of SQL Server 2012 Feature Pack, it is now possible to expose sqlps functions in your scripts by running:

Import-Module sqlps

Here is how to get sqlps installed so it is available to import as a module. I’ve tested this successfully on both Windows Server 2008 R2 and Windows Server 2012. To get some idea on how to use it, perhaps some of my scripts here can get you started 🙂

1. Go to SQL Server 2012 Feature Pack site

2. Download and install the 3 items below
Microsoft System CLR Types for Microsoft SQL Server 2012
Microsoft SQL Server 2012 Shared Management Objects
Microsoft Windows PowerShell Extensions for Microsoft SQL Server 2012

Given a choice, I’d rather write automation programs on Windows with either Python and Perl, because both languages come with a comprehensive testing framework that supports TDD. With test cases and various testing harness, it is much easier to build solid, high quality software solutions.

However, if for whatever reason, it is difficult to use Python or Perl, then the natural choice on Windows is PowerShell, because it comes with the OS. Too bad PowerShell does not have a solid testing framework. I have been using PSUnit, which leaves a lot to be desired for people who are used to xUnit framework, but it is still worthwhile to use.

One thought on “Install sqlps as a PowerShell module”

  1. Though, NFC enables the devices to switch data significantly faster than Bluetooth
    and is aso considered secure because of its short-range capability, yet we simply cannot overlook the risks how
    some individuals try to exploit thee technology because of their own gains.

    Go through the terms and conditions of the rent agreement and
    punctiliously decide if you trust them or
    otherwise before you sign them on. AA AThis happens because the field
    of i . t . is ppredominantly service ased so if you’re skilled at customer care as well as an in-depth knowledge about the
    subject, you’ll bee geatly poular which enable it to enjoy luchrative opportunities as professionals.

Leave a Reply

Your email address will not be published. Required fields are marked *