Archive for the ‘SQLPS’ Category

XEVENT Provider in Denali

16/08/2011 Leave a comment

XEVENT provider is new sqlps provider in Denali CTP3. To admit, I was surprised when I saw it. In SQL Server 2008R2 we don’t have any GUI for XEVENT (Extended Events) written by SQL Team and now, in Denali CTP3, we have nice GUI through SSMS and sqlps provider. Nice work.

XEVENT provider is built on SMO Library (Microsoft.SqlServer.Management.XEvent Namespace). I will try to show how to work with XEVENT provider from the beginning. To start to work with XEVENT objects in sqlps, we first need to access XEVENT provider in sqlps. From PS SQLSERVER:\ type CD XEVENT. Then you need to navigate to server instance typing <server name>\<instance name>. When you are at instance level, you can see that XEVENT provider have 2 subfolders: Packages and Sessions.

From Packages subfolder we can access to metadata of all objects that are available on the instance. To be precise, all public metadata, private objects are not visible with XEVENT provider. From Sessions subfolder we can see all available sessions on the system, manage it and create new sessions. We can even generate SQL DDL script for CREATE, DROP and ALTER session.

Let’s start with Packages subfolder. (1)From Packages subfolders we can see all public packages on the system. (2)To access some package ModuleId and Name need to be specified. (3) From the Package we can get all metadata that are available in the package: events, actions, targets…

From there, to list all Events in the package we can type: dir .\EventInfoSet
The same pattern can be used for any object in the package: ActionInfo for Actions, TargetInfo for target etc.
To see Event Fields for some specific Event we can use the code:

# Get Event "sqlserver.scan_started" in $event variable
$event = dir .\EventInfoSet | Where-Object {$ -eq "scan_started"}
# Return ReadOnly Event Fields
# Return Data Event Fields
# Return Customizable Event Fields

It’s nice to have possibility to see Extended Events metadata with Powershell but I prefer to use T-SQL script or new GUI in SSMS.

For me, sessions subfolder is much more useful. (1) From Sessions subfolder we can see all available session on that instance, manage it or create new session. (2) On my server, 3 sessions are available. System_health is running and Perf1 and Perf2 are stopped. (3) We can navigate to system_health session and from there (4) we can see all events and targets that are associated to the session.

If you want to see all relevant information (actions, predicates and event fields) for specific event in the system_health session you can use:

dir .\Events | Where-Object {$ -eq "sqlserver.error_reported"} | Format-List Name, Actions,PredicateExpression, EventFields


$ev = dir .\Events | Where-Object {$ -eq "sqlserver.error_reported"}

From sessions subfolder, session can be started and stopped.

# Find system_health session
$session = Get-ChildItem | Where-Object {$ -eq "system_health"}
# Stop session
# Check is session running

If you want to create T-SQL DDL for CREATE EVENT SESSION and DROP EVENT SESSION you can use next code:


In this post I showed some basics of using XEVENT provider in Denali sqlps. I hope it will help someone.

Categories: Denali, Extended Events, SQLPS


08/08/2011 Leave a comment

I’ve tried Denali CTP3 SQLPS and noticed some great changes. Here I will describe my first experience with Denali SQLPS.
I’ve started SQLPS by right-clicking on server node in SSMS Object Explorer and selecting “Start PowerShell”. I read that sqlps is no longer a mini-shell, so I first executed Get-Module cmdlet. As a result I got 2 modules: SQLPS and SQLASCMDLETS.
Under “PS SQLSERVER:\>”  I ran dir and here is result:

I noticed 3 new Providers in Denali CTP3: XEvent, IntegrationServices and SQLAS.
Next, I looked for available Cmdlets in SQLPS and SQLASCMDLETS modules.

Get-Command -CommandType Cmdlet -Module sqlps | select name
Get-Command -CommandType Cmdlet -Module SQLASCMDLETS  | select name

Here are the results:

SQLPS Cmdlets


Add-SqlAvailabilityDatabase Add-RoleMember
Add-SqlAvailabilityGroupListenerStaticIp Backup-ASDatabase
Backup-SqlDatabase Invoke-ASCmd
Convert-UrnToPath Invoke-ProcessCube
Decode-SqlName Invoke-ProcessDimension
Disable-SqlHADRService Invoke-ProcessPartition
Enable-SqlHADRService Merge-Partition
Encode-SqlName New-RestoreFolder
Invoke-PolicyEvaluation New-RestoreLocation
Invoke-Sqlcmd Remove-RoleMember
Join-SqlAvailabilityGroup Restore-ASDatabase

There are 29 cmdlets in SQLPS module and 11 cmdlets in SQLASCMDLETS  module. All new cmdlets in SQLPS module, except Backup-SqlDatabase and Restore-SqlDatabase, are for high-availability and disaster recovery solution (HADR).
Next, I looked inside SQL, SQLPolicy and SQLRegistration Provides. It seems that they are the same as in SQL Server 2008R2. XEvent provider gives us possibility to work with Extended Events. I must admit that I like it. My next post will be about it. 🙂

I had troubles with IntegrationServices provider.

I found solution in Chad Miller’s blog post, but my first command finished with bug. I filled a bug report on Connect and here is response from Microsoft:

Similar as 5.9 Cannot Load the Data-tier Application Assembly in PowerShell in Microsoft SQL Server Code-Named “Denali” CTP3 Release Notes, here, 

The SQL Server Code-Named “Denali” CTP3 IntegrationServices assemblies are compiled using .Net Framework 4.0, and .Net Framework 4.0 assemblies are not supported in any currently available version of PowerShell. No workaround exists for this issue; IntegrationServices operations are not supported in PowerShell for SQL Server Code-Named “Denali” CTP3.

For now, I will not play with IntegrationServices.

Backup-SqlDatabase and Restore-SqlDatabase test

I decided to test Backup-SqlDatabase and Restore-SqlDatabase cmdlets. First, I extracted help about cmdlets to .txt files.
Get-Help Backup-SqlDatabase -detailed > D:\Backup-SQLDatabase.txt
Get-Help Restore-SqlDatabase -detailed > D:\Restore-SQLDatabase.txt
I tested Backup-SQLDatabase cmdlets and it seems to be working correctly. I even tried to create a compressed backup on the media with non-compressed backup.  It didn’t work as I expected.

To format disk Mediaset you need to use “FormatMedia”, “Initialize” and “SkipTapeHeader” properties during backup.

Backup-SqlDatabase -Database TestDB -BackupFile “D:\1\Test.bak” -CompressionOption on -FormatMedia -Initialize -SkipTapeHeader

Here is how I restored my database with Restore-SqlDatabase  cmdlet.

Categories: Denali, SQLPS