Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Chad Miller

Chad Miller is a Senior Manager of Database Administration at Raymond James Financial. Chad has worked with Microsoft SQL Server since 1999 and has been automating administration tasks using Windows Powershell since 2007. Chad is the Project Coordinator/Developer of the Powershell-based Codeplex project SQL Server PowerShell Extensions (SQLPSX). Chad leads the Tampa Powershell User Group and is a frequent speaker at users groups, SQL Saturdays and Code Camps.

Denali SQLPS First Impressions

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 Host

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.

Modules

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 Server\110\Tools\PowerShell\Modules

Cmdlets

get-command -CommandType cmdlet -Module sqlps,sqlascmdlets | group-object -Property verb
Count Verb Group
3 Add Add-RoleMember, Add-SqlAvailabilityDatabase, Add-SqlAvailabilityGroupListenerStaticIp}
2 Backup Backup-ASDatabase, Backup-SqlDatabase
1 Convert Convert-UrnToPath
1 Decode Decode-SqlName
1 Disable Disable-SqlHADRService
1 Enable Enable-SqlHADRService
1 Encode Encode-SqlName
6 Invoke Invoke-ASCmd, Invoke-PolicyEvaluation, Invoke-ProcessCube, Invoke-ProcessDimension, Invoke-ProcessPartition, Invoke-Sqlcmd
1 Join Join-SqlAvailabilityGroup
1 Merge Merge-Partition
6 New New-RestoreFolder, New-RestoreLocation, New-SqlAvailabilityGroup, New-SqlAvailabilityGroupListener, New-SqlAvailabilityReplica, New-SqlHADREndpoint
4 Remove Remove-RoleMember, Remove-SqlAvailabilityDatabase, Remove-SqlAvailabilityGroup, Remove-SqlAvailabilityReplica
2 Restore Restore-ASDatabase, Restore-SqlDatabase
1 Resume Resume-SqlAvailabilityDatabase
4 Set Set-SqlAvailabilityGroup, Set-SqlAvailabilityGroupListener, Set-SqlAvailabilityReplica, Set-SqlHADREndpoint
1 Suspend Suspend-SqlAvailabilityDatabase
1 Switch Switch-SqlAvailabilityGroup
3 Test Test-SqlAvailabilityGroup, Test-SqlAvailabilityReplica, Test-SqlDatabaseReplicaState

Providers

PS SQLSERVER:\> dir
Name Root Description
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.

Using SQLPS

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 Server\110\Tools\PowerShell\Modules"
import-module sqlps

Since I’ve implemented my own backup and restore functions I thought I’d test the Backup-SqlDatabase cmdlet:

backup-sqldatabase

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:\IntegrationServices\SQL11> dir
WARNING: 'DEFAULT' not available: Could not load file or assembly 'Microsoft.SqlServer.Management.IntegrationServices,
Version=11.0.0.0, 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=11.0.0.0, 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 Files\Microsoft SQL Server\110\DTS\Binn\Microsoft.SqlServer.IntegrationServices.Server.dll"
Add-Type : Could not load file or assembly 'file:///C:\Program Files\Microsoft SQL Server\110\DTS\Binn\Microsoft.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 Server\110\Tools\Binn).

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.

SSISProvider

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.

Summary

  • sqlps has been re-done as a regular Powershell host
  • There are two modules, 40 new cmdlets and 2 new SqlProviderExtensions
  • The Integration Services Provider has bug in CTP3
  • Overall I’m impressed with what the SQL Server product team has done

Comments

Posted by opc.three on 23 July 2011

Nice write-up Chad...I feel caught up now! Thanks for highlighting the new IS provider and for noting that the preferred storage model of SSIS packages is moving away from the file system. At all my stops file system storage was used due to xcopy deployments being viewed as easier than msdb deployments. I have heard about serious improvements to SSIS in terms of change and package management in Denali, I am hopeful that it will deliver on all points and PowerShell will certainly add to it.

Posted by cmille19 on 24 July 2011

To execute a package within IntegrationServices provider extension:

PS SQLSERVER:\IntegrationServices\SQL11\DEFAULT\Catalogs\SSISDB\Folders\SQLPSX\Projects\test\Packages> get-item sqlpsx1*

| foreach {$_.Execute($false,$null)}

Posted by cmille19 on 24 July 2011

@Orlando

I'm hopeful of the SSIS changes in Denali. Being able to execute a package in T-SQL or Powershell without all the chunkiness of dtexec and DCOM is huge.

Leave a Comment

Please register or log in to leave a comment.