I’ve taken a few hours to try out Denali CTP 3 sqlps and noticed some welcome changes. The biggest change for sqlps is that it has been implemented as module and plain old Powershell host–It’s no longer mini-shell!
SQLPS is now regular Powershell host implemented as the familiar sqlps.exe. Prior versions of sqlps were a mini-shell, which is to say a Powershell host that implements RunsapceConfiguration with explicitly defined cmdlets and no support for add-pssnapin. The old implementation was limiting in that you couldn’t add cmdlets or providers. I’ve previously written about the SQL Server 2008 and SQL 2008 R2 sqlps so I won’t spend much time on it here, but I will say that I really like what SQL Server product team has done with the Denali version of sqlps.
There some 40 new cmdlets and 2 new “providers” over what was provided in SQL Server 2008 R2. Note: sqlps uses something called SqlServerProviderExtensions which are not like regular providers in that you can’t load them individually—so there’s really only a single “SQLServer” provider. Its interesting to see how the SQL Server product team has organized their provider so that they easily plug in these extensions. As far as I know this is unique among provider implementations.
Denali Powershell implementation also includes two modules, SQLASCMDLETS and to make things a little confusing there’s binary module called SQLPS which has the same name as the sqlps.exe host. Both modules are located under:
C:Program Files (x86)Microsoft SQL Server110ToolsPowerShellModules
get-command -CommandType cmdlet -Module sqlps,sqlascmdlets | group-object -Property verb
|3||Add||Add-RoleMember, Add-SqlAvailabilityDatabase, Add-SqlAvailabilityGroupListenerStaticIp}|
|6||Invoke||Invoke-ASCmd, Invoke-PolicyEvaluation, Invoke-ProcessCube, Invoke-ProcessDimension, Invoke-ProcessPartition, Invoke-Sqlcmd|
|6||New||New-RestoreFolder, New-RestoreLocation, New-SqlAvailabilityGroup, New-SqlAvailabilityGroupListener, New-SqlAvailabilityReplica, New-SqlHADREndpoint|
|4||Remove||Remove-RoleMember, Remove-SqlAvailabilityDatabase, Remove-SqlAvailabilityGroup, Remove-SqlAvailabilityReplica|
|4||Set||Set-SqlAvailabilityGroup, Set-SqlAvailabilityGroupListener, Set-SqlAvailabilityReplica, Set-SqlHADREndpoint|
|3||Test||Test-SqlAvailabilityGroup, Test-SqlAvailabilityReplica, Test-SqlDatabaseReplicaState|
PS SQLSERVER:> dir
|SQL||SQLSERVER:SQL||SQL Server Database Engine|
|SQLPolicy||SQLSERVER:SQLPolicy||SQL Server Policy Management|
|SQLRegistration||SQLSERVER:SQLRegistration||SQL Server Registrations|
|DataCollection||SQLSERVER:DataCollection||SQL Server Data Collection|
|XEvent||SQLSERVER:XEvent||SQL Server Extended Events|
|Utility||SQLSERVER:Utility||SQL Server Utility|
|DAC||SQLSERVER:DAC||SQL Server Data-Tier Application Component|
|IntegrationServices||SQLSERVER:IntegrationServices||SQL Server Integration Services|
|SQLAS||SQLSERVER:SQLAS||SQL Server Analysis Services|
IntegrationServices and SQLAS are new to Denali.
Just as in previous versions you launch the sqlps.exe host by right-clicking an object in SQL Server Management Studio Object Explorer and selecting “Start Powershell” or by typing sqlps.exe from the Run or command-prompt.
Alternatively if you want to load the SQLPS module in your powershell.exe host then you can run:
$env:PSModulePath = $env:PSModulePath + ";C:Program Files (x86)Microsoft SQL Server110ToolsPowerShellModules" import-module sqlps
Since I’ve implemented my own backup and restore functions I thought I’d test the Backup-SqlDatabase cmdlet:
The cmdlet seems to be fully functional and even implements a nice write-progress bar showing the overall backup progress.
Integration Services Provider
The next thing I did was try to use the IntegrationServices. I say try because there a bug in CTP3, if you navigate to the IntegrationServices provider extension you’ll see:
PS SQLSERVER:IntegrationServicesSQL11> dir WARNING: 'DEFAULT' not available: Could not load file or assembly 'Microsoft.SqlServer.Management.IntegrationServices, Version=220.127.116.11, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. --> Could not load file or assembly 'Microsoft.SqlServer.Management.IntegrationServices, Version=18.104.22.168, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
Looking at the module manifest sqlps.psd1 I could see that the Microsoft.SqlServer.Management.IntegrationServices assemlby wasn’t being loaded, so I’d I tried to load it:
add-type -Path "C:Program FilesMicrosoft SQL Server110DTSBinnMicrosoft.SqlServer.IntegrationServices.Server.dll" Add-Type : Could not load file or assembly 'file:///C:Program FilesMicrosoft SQL Server110DTSBinnMicrosoft.SqlSer ver.IntegrationServices.Server.dll' or one of its dependencies. This assembly is built by a runtime newer than the curr ently loaded runtime and cannot be loaded.
The message “runtime newer than the currently loaded runtime” means an assembly was written in higher version than the Powershell host supports. In PowerShell V2 only supports .NET 3.5.
Fixing Integration Services Provider
I filled a bug report on Connect (please vote for the the item), but rather than wait for a a fix I’ve seen this error before with other assemblies and so have few other folks. One way to fix the issue is to create a config file to tell the Powershell host to use a later version of the .NET framework. I used this post by Thomas Lee (blog|twitter) as a guide and crafted a SQLPS.exe.config file with the following contents:
<?xml version="1.0"?> <configuration> <startup uselegacyv2runtimeactivationpolicy="true"> <supportedruntime version="v4.0.30319"/> <supportedruntime version="v2.0.50727"/> </startup> </configuration>
Place the config file in the same directory as sqlps.exe (C:Program Files (x86)Microsoft SQL Server110ToolsBinn).
I uploaded a few test SSIS packages and now I’m able to use the Integration Services provider. Once you navigate to the right container the Integration Servcies provider returns PackageInfo objects.
Since the preferred storage model of SSIS packages is moving away from the file system in Denali– I can definitely see Integration Services provider extension being very useful in managing SSIS packages. I need to work with PackageInfo object in Denali some more as things have changed. As an example in 2005 to 2008 R2 I could call the Execute method on a package object, you can do the same Denali, but the method signature has changed and now expects something called an EnvironnmentReference.I haven’t figure out how execute a package in the context of the provider yet. I’ll post an update once I do or if anyone has figures this out, please post a comment.
- sqlps has been re-done as a regular Powershell host
- There are two modules, 40 new cmdlets and 2 new SqlProviderExtensions. Update: Reading Aaron Nelson’s (blog|twitter) post he also mentions the XEvent provider extension.
- The Integration Services Provider has bug in CTP3
- Overall I’m impressed with what the SQL Server product team has done