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’)
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:
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.