2009-11-13

WPF Button with click event using PowerShell

A WPF Button with a click event can basically be defined either programmatic or with XAML. The two examples below gives the same look and feel in both ways.

First the programmatic way:
function Show-pgmButton {
    $Window = New-Object System.Windows.Window
    $Window.Title="SQLAdmin"
    $Window.Height="250"
    $Window.Width="400"
   
    $Button = New-Object System.Windows.Controls.Button
    $Button.Height = 23
    $Button.Width = 100
    $Button.Margin = '12,12,0,0'
    $Button.VerticalAlignment = 'Top'
    $Button.HorizontalAlignment = 'Left'
    $Button.Content = "Don't click me!"
    $Button.add_Click({
        $Button.Content = 'Arrrgh!!!'
        Start-Process '\\Titanium\Musik'
    })
    $Grid = New-Object System.Windows.Controls.Grid
    $Grid.Children.Add( $Button )
   
    $Window.Content = $Grid
    [void]$Window.ShowDialog()
}

Then the XAML way:
function Show-xamlButton {
[xml]$XAML = @'
<Window
   xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
   xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
   Title="SQLAdmin" Height="250" Width="400">
   <Grid>
      <Button Height="22" Width="100" Name="myButton" Content="Don't click me!" VerticalAlignment="Top" HorizontalAlignment="Left" />
   </Grid>
</Window>
'@

   $Reader = New-Object System.Xml.XmlNodeReader $XAML
   $Window = [System.Windows.Markup.XamlReader]::Load( $Reader )

   $myButton = $Window.FindName('myButton')
   $myButton.Add_Click({
      $myButton.Content = 'Arrrgh!!!'
      Start-Process '\\Titanium\musik'
   })

   [void]$Window.ShowDialog()
}

Autogenerated scriptheader

In my daily tasks I often use scripts generated by other scripts. Often T-SQL scripts generated by PowerShell scripts.
The descriptive header of such autogenerated scripts I like to contain some general informations like which scripts generated it, who did it, when was it done and on which computer was it done.

In short these line give some of the wanted data.
"Filename  : $($MyInvocation.InvocationName)"
"Date      : $($(Get-Date).ToUniversalTime().ToString('s')) UTC"
"User      : $([Security.Principal.WindowsIdentity]::GetCurrent().Name)"
"Computer  : $($env:COMPUTERNAME)`(.$($env:USERDNSDOMAIN)`)"

2009-09-26

Database version

When the daily administration of several database instances is done using a standard database, it is rather nice to be able to check that all management database have the same structure.
A quick solution is to calculate a checksum of the database objects and its columns.

To hold the version number of the database and its checksum a (singleton) table is created.
CREATE TABLE [dbo].[Database_Version]
(
    [Database_Version_No] nvarchar(128) NOT NULL,
    [Database_Version_Checksum] bigint NOT NULL,
    CONSTRAINT PK_Database_Version PRIMARY KEY ([Database_Version_No])
);
The table can be initialized by a INSERT statement:
INSERT INTO [dbo].[Database_Version] ([Database_Version_No], [Database_Version_Checksum])
VALUES ('0', 42);

The table is used by two stored procedures - one to read:
CREATE PROCEDURE [dbo].[Get_Database_Version]
AS
BEGIN
    SET NOCOUNT ON;
   
    -- Check stored checksum
    --   Calculate checksum
    DECLARE @objects_checksum bigint;
    SELECT @objects_checksum = SUM(CAST(CHECKSUM(OBJECT_DEFINITION([object_id])) AS bigint))
    FROM sys.objects
    WHERE is_ms_shipped = 0;
    DECLARE @columns_checksum bigint;
    SELECT @columns_checksum = SUM(CAST(CHECKSUM(OBJECT_DEFINITION([column_id])) AS bigint))
    FROM sys.columns;
    --   Check is the checksum stored is equal to the calculated checksum
    IF (@objects_checksum + @columns_checksum) <> (
        SELECT [Database_Version_Checksum]
        FROM [dbo].[Database_Version]
    )
    BEGIN
        DECLARE @calculated_checksum nvarchar(128) = CAST((@objects_checksum + @columns_checksum) AS nvarchar(128));
        DECLARE @stored_checksum nvarchar(128) = CAST((SELECT [Database_Version_Checksum] FROM [dbo].[Database_Version]) AS nvarchar(128));
        RAISERROR(N'The calculated checksum (%s) is not equal to the stored checksum (%s).',10, 1,@calculated_checksum,@stored_checksum) WITH NOWAIT;
    END
   
    -- Get stored checksum
    SELECT [Database_Version_No], [Database_Version_Checksum]
    FROM [dbo].[Database_Version];
END
...and one to update:
CREATE PROCEDURE [dbo].[Set_Database_Version]
    @Database_Version_No nvarchar(128)
AS
BEGIN
    SET NOCOUNT ON;
   
    -- Check if the given Database Version value is different from the existing
    IF @Database_Version_No = (
        SELECT [Database_Version_No]
        FROM [dbo].[Database_Version]
    )
    RAISERROR('You have to update the Database Version to another value than the existing one.', 18, 0) WITH NOWAIT;

    -- Calculate checksum
    DECLARE @objects_checksum bigint;
    SELECT @objects_checksum = SUM(CAST(CHECKSUM(OBJECT_DEFINITION([object_id])) AS bigint))
    FROM sys.objects
    WHERE is_ms_shipped = 0;
    DECLARE @columns_checksum bigint;
    SELECT @columns_checksum = SUM(CAST(CHECKSUM(OBJECT_DEFINITION([column_id])) AS bigint))
    FROM sys.columns;
   
    -- Check is the checksum stored is equal to the calculated checksum
    IF (@objects_checksum + @columns_checksum) = (
        SELECT [Database_Version_Checksum]
        FROM [dbo].[Database_Version]
    )
    RAISERROR('The calculated checksum is equal to the stored checksum.',18, 1) WITH LOG;
   
    -- @objects_checksum + @columns_checksum
    UPDATE [dbo].[Database_Version]
    SET [dbo].[Database_Version].[Database_Version_No] = @Database_Version_No,
        [dbo].[Database_Version].Database_Version_Checksum = @objects_checksum + @columns_checksum;
END


The get procedure also checks if the stored checksum is like to actual calculated checksum. Of course the checksum inserted above is not correct, so the get procedure raise an error:
EXEC [dbo].[Get_Database_Version];
{Message} The calculated checksum (3381089942582) is not equal to the stored checksum (42).

The update procedure calculate a checksum and update the stored checksum. The version number is actually just a string, so you can make your own version standard.
EXEC [dbo].[Set_Database_Version] @Database_Version_No = '3.1';
The get procedure now executes with success.
Database_Version_No   Database_Version_Checksum
-------------------   -------------------------
3.1                   3381089942582

If a object is changes, e.g. a column is added
ALTER TABLE [dbo].[Database_Version] ADD
    [Database_Version_Description] nvarchar(MAX) NULL;

Now the get procedure raises a error
The calculated checksum (3383237426229) is not equal to the stored checksum (3381089942582).
If the column is dropped,
ALTER TABLE [dbo].[Database_Version]
    DROP COLUMN [Database_Version_Description];

the get procedure executes with success.

Discussion
The database version objects are in the schema "dbo", but you might want to keep this schema clean.
When the get procedure fails, you have no indication on what is different. You could use a comparison tool like Red Gate SQL Compare.
There is no timestamp in the solution, but you could format the version as a timestamp, e.g. "2009-09-17A".
The version checksum calculation is somewhat simple. It could also include indexes, users, files and what else you need.
A version and checksum could be calculated and stored for each database. Some changes to the version objects are needed, but it should not be a problem to the dedicated DBA.

2009-09-21

Central Registrered Servers and groups

Adding a new database group to a Central Management Server con be done by a T-SQL statement like this:
DECLARE @new_server_group_id AS int;
DECLARE @parent_server_group AS int;
SELECT @parent_server_group = server_group_id
FROM msdb.dbo.sysmanagement_shared_server_groups
WHERE [name]='DatabaseEngineServerGroup';
EXECUTE msdb.dbo.sp_sysmanagement_add_shared_server_group
  @name = N'Development'
  ,@description = N'Development SQL Server database instances.
  ,@parent_id = @parent_server_group
  ,@server_type = 0
  ,@server_group_id = @new_server_group_id OUTPUT;


The result can be viewed through msdb.dbo.sysmanagement_shared_server_groups.

After the database group is created, some database instances can be registrered in the group by a T-SQL statement like this:
DECLARE @server_group AS int;
SELECT @server_group = server_group_id FROM msdb.dbo.sysmanagement_shared_server_groups WHERE [name]='Development';
DECLARE @new_server_id AS int;
EXECUTE msdb.dbo.sp_sysmanagement_add_shared_registered_server
  @name = N'Frodo\SSDB01'
  ,@server_group_id = @server_group
  ,@server_name = N'Frodo.sqladmin.lan\SSDB01'
  ,@description = N'First database instance on Frodo.sqladmin.lan.'
  ,@server_type = 0
  ,@server_id = @new_server_id OUTPUT;
EXECUTE msdb.dbo.sp_sysmanagement_add_shared_registered_server
  @name = N'Frodo\SSDB02'
  ,@server_group_id = @server_group
  ,@server_name = N'Frodo.sqladmin.lan\SSDB02'
  ,@description = N'Second database instance on Frodo.sqladmin.lan.'
  ,@server_type = 0
  ,@server_id = @new_server_id OUTPUT;


The result can be viewed through msdb.dbo.sysmanagement_shared_registered_servers.

There is a unique constraint on the name, but the procedure is not „intelligent“ enough to handle instert or update of a server registration.

When a group is deleted, the registrered servers in the group is also deleted
DECLARE @del_server_grp AS int;
SELECT @del_server_grp = server_group_id
FROM msdb.dbo.sysmanagement_shared_server_groups
WHERE [name] = N'Development';
EXECUTE msdb.dbo.sp_sysmanagement_delete_shared_server_group @server_group_id = @del_server_grp;


In this example the two previously registered servers are deleted from the Central Management Server - without warning or additional messages.

A automated population, like from System Center Operations Manager (SCOM) will need some coding of a wrapper to handle insert or update.
Also some work is needed for handling database mirror and log shipping sets.

PowerShell

Inspired by the blog entry &bdqou;Managing Registered servers with SQL Powershell“ by Arco Scheepen I looked into using the PowerShell SQL Server provider for accessing the Management Server registrations with Set-Location and New-Item on SQLSERVER:\sqlregistration\Database Engine Server Group.
More details on this later...

This was some quickies - comments are most welcome.

History

2009-09-21 Initial blog entry
2016-10-25 Section on PowerShell added

2009-08-18

msdb.dbo.backupset

This morning I wanted to have a quick look at the backup history of a given database. This I got by this statement

SELECT [backup_start_date] AS 'Start'
,([backup_finish_date]-[backup_start_date]) AS 'Duration'
,(([backup_size]/1024)/1024)/1024 AS 'Size [GB]'
FROM [msdb].[dbo].[backupset]
WHERE [database_name]='my_database' AND [type]='D'
ORDER BY [Start] DESC;


The multiple division by 1024 is to get a clear number.

2009-08-08

UNMOUNTABLE_BOOT_VOLUME

A friend of mine have an (very) old notebook running Windows XP, and it came up with this awful message in a Blue Screen:
UNMOUNTABLE_BOOT_VOLUME

The fix was
  1. Boot the machine on a installation set (Windows XP CD-ROM)
  2. Go to the Recovery Console
  3. Run the command "chkdsk c: /r"
The contents of the disk is rescued, but the machine is still due to a replacement...

2009-06-16

Central Registrered Servers

With SQL Server 2008 came the feature "Central Management Servers". This is poorly documented by Microsoft, but the naming of the database objects are somewhat meaningful.

In the system database [msdb] there are two tables for storing the server registrations. Only the tables in msdb at the Central Management Server are used.
The two tables are
  • msdb.dbo.sysmanagement_shared_registered_servers_internal
  • msdb.dbo.sysmanagement_shared_server_groups_internal

There is no use in accessing these two tables as we have these two views for the data

  • msdb.dbo.sysmanagement_shared_registered_servers
  • msdb.dbo.sysmanagement_shared_server_groups

The first view sysmanagement_shared_registered_servers shows the registered servers by
server_id : int
server_group_id : int
name : sysname
server_name : sysname
description : nvarchar(2048)
server_type : int

The second view sysmanagement_shared_server_groups shows the registered server groups and their hieracy by
server_group_in : int NOT NULL
name : sysname NOT NULL
description : nvarchar(2048) NOT NULL
server_type : int NOT NULL
parent_id : int NULL
is_system_object : bit NULL
num_server_group_children : int NULL
num_registered_server_children : int NULL


I found the view sysmanagement_shared_registered_servers a little technical, but this query gives me a quick glance at the central registered servers

SELECT
[server_id]
,[server_groups].[name] AS 'server_group_name'
,[server_name]
,[shared_servers].[description] AS 'server_description'
,( SELECT [name]
FROM msdb.dbo.sysmanagement_shared_server_groups AS [shared_groups]
WHERE shared_groups.is_system_object = 1
AND shared_servers.server_type = shared_groups.server_type) AS 'server_type_name'
FROM msdb.dbo.sysmanagement_shared_registered_servers AS [shared_servers]
INNER JOIN msdb.dbo.sysmanagement_shared_server_groups AS [server_groups]
ON shared_servers.server_group_id = server_groups.server_group_id;

It looks like the data are handled by 11 stored procedures. I haven't tried them enough to put it in writing, but here are the procedures

  • msdb.dbo.sp_sysmanagement_verify_shared_server_type
    @server_type : int
  • msdb.dbo.sp_sysmanagement_add_shared_server_group
    @name : sysname
    @description nvarchar(2048) = N''
    @parent_id : int
    @server_type : int
    @server_group_id : int OUTPUT
  • msdb.dbo.sp_sysmanagement_add_shared_registered_server
    @name : sysname
    @server_group_it : int
    @server_name : sysname
    @description : nvarchar(2048) = N''
    @server_type : int
    @server_id : int OUTPUT
  • msdb.dbo.sp_sysmanagement_delete_shared_server_group
    @server_group_id : int
  • msdb.dbo.sp_sysmanagement_delete_shared_registered_server
    @server_id : int
  • msdb.dbo.sp_sysmanagement_move_shared_server_group
    @server_group_id : int
    @new_parent_id : int
  • msdb.dbo.sp_sysmanagement_move_shared_registered_server
    @server_id : int
    @new_parent_id : int
  • msdb.dbo.sp_sysmanagement_update_shared_server_group
    @server_group_id : int
    @description : nvarchar(2048) = NULL
  • msdb.dbo.sp_sysmanagement_update_shared_registered_server
    @server_id : int
    @server_name : sysname = NULL
    @description : nvarchar(2048) = NULL
  • msdb.dbo.sp_sysmanagement_rename_shared_server_group
    @server_group_id : int
    @new_name : sysname
  • msdb.dbo.sp_sysmanagement_rename_shared_registered_server
    @server_id : int
    @new_name : sysname

Also it loks like Central Management Servers can be handled through SQL Server Management Objects (SMO). I found seven classes in the namespace Microsoft.SqlServer.Management.Smo.RegisteredServers by the .NET component Microsoft.SqlServer.SmoExtended implemented in the assembly Microsoft.SqlServer.SmoExtended.dll. The classes are

  • RegisteredServer
  • RegisteredServerCollection
  • RegSvrCollectionBase
  • RegSvrSmoObject
  • ServerGroup
  • ServerGroupBase
  • ServerGroupCollection

Looking at the classes using Visual Studio Object Browser didn't give me that much - only I noticed that we once again have to fight with the .NET component Microsoft.SqlServer.SmoExtended :-(

About security suddenly Books Online is useful:

How to: Create a Central Management Server and Server Group

It looks like you don't have to be sysadmin to be able to use Central Management Servers. :-)

The above is the result of a quick browse in the system objects. When I have something usefull, it will surface here...

2009-05-01

PowerTab -eq CoolTool

A collegue just presented me to PowerTool, which I find rather cool:
http://thepowershellguy.com/blogs/posh/pages/powertab.aspx

After installation try this.

Get-WmiObject Win32_<TAB>

And

$a = Get-WmiObject Win32_BIOS
$a.<TAB>

2009-03-19

Dedicated Administrator Connection (DAC)

Usually the sqlcmd tool is used for automation, but I also think of it as my last access to a running database instance when the dirt hits the fan. sqlcmd is a part of a database instance installation - you don't have to install management tools.
But when the resources of a Windows Server host or a database instance is consumed by a wild running process or session, then the Dedicated Administrator Connection (DAC) might be your last option - before shutting down the box.

SQLCMD usage
Usually it is possible to use the host name, but once I've experienced a situation where the DNS server(-s?) was down. In that case you have to enter the IP address of the host. This also gives the fastest connection to the host.

Syntax
SQLCMD.EXE -S "" -A
SQLCMD.EXE -S "Admin:" -E

Example
SQLCMD.EXE -S "SANDBOX.sqladmin\SSDB42" -A
SQLCMD.EXE -S "Admin:SANDBOX.sqladmin\SSDB42" -E

Please notice that the Browser Service must be running.

Management Studio (SSMS) usage
Use the value like the SQLCMD -S parameter.

TCP port number
The TCP port number of the DAC is shown in the Registry and the SQL Server Errorlog.

Registry
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SSDB\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp\TcpDynamicPorts [REG_SZ]
ERRORLOG
2009-03-18 21:35:15.26 Server Dedicated admin connection support was established for listening locally on port 1434.

If a host has several database instances installed, each instance DAC has it own TCP port assigned. Then you have to look into several Registry branches to get the TCP port number.

The DAC TCP port number is unfortunately not available through SMO or the SQL Server WMI provider :-(

Remote DAC
By default the DAC is available only locally on the host, but it is possible to enable remote DAC.
At SQL Server 2005 it was possible to enable remote DAC with the Surface Area Configuration point-and-click tool. But this tool is not part of SQL Server 2008 where you have to enable Remote DAC by
sp_configure 'remote admin connections', 1;
GO
RECONFIGURE;
GO

Reference

MSDN library: Using a Dedicated Administrator Connection

Kalen Delaney et al: "Microsoft SQL Server 2008 Internals", pp 27.

2009-03-16

Executing a T-SQL script file

I would like to execute a developers T-SQL script file automated, so that I can deploy without having to press the button every single time.
To do this I'm working on a PowerShell function, that uses both the Windows Shell (cmd.exe) end the SQL Server sqlcmd (SQLCMD.EXE) utility.

function Invoke-SqlCmd_cmd {
param(
 [string]$WindowsName = $( throw "Windows host name required as parameter." ),

 [string]$SqlInstanceName = $( throw "SQL Server database instance name required as parameter." ),

 [string]$DatabaseName = $( throw "Database name required as parameter." ),

 [string]$SqlScriptFileName = $( throw "T-SQL script file name required as parameter." ) )


 if( $SqlInstanceName -eq '.' )

 { $DbInstanceName = $WindowsName }

 else

 { $DbInstanceName = $WindowsName + '\' + $SqlInstanceName }


 & cmd.exe /c "SQLCMD.EXE -E -S `"$DbInstanceName`" -d `"$DatabaseName`" -i `"$SqlScriptFileName`" -u -b -m-1"

}


The function is used like this:
Invoke-SqlCmd_cmd 'Sandbox' '.' 'master' 'C:\SQLAdmin\SqlCmd.sql'

I use '.' (dot) to identify a default database instance in my repository, but any other identification can be motivated.

I haven't found a way round the Windows Shell, which I think is a indicator that PowerShell is still some way from complete. Maybe the PowerShell V2 CmdLet Invoke-Command does a better job...

The documentation is at MSDN Library on sqlcmd and at TechNet Library on Windows Shell (cmd.exe).

2009-03-10

SQL Server Native Client (SNAC)

SNAC is not documentet in Books Online (BOL), but in the learning section of MSDN:
Data Platform Developer Center > Learn > Microsoft SQL Server Native Client

Also there is a blog about SNAC. The reference to the blog is at the page referenced above.

The installed version can be found in the Registry:
HKLM\SOFTWARE\Microsoft SQL Native Client\CurrentVersion\Version : [REG_SZ]
or
HKLM\SOFTWARE\Microsoft\SNAC\InstalledVersion : [REG_SZ]
Using PowerShell the value can be accessed like this
PS > $SnacVersion = get-itemproperty 'hklm:\software\microsoft\microsoft sql native client\currentversion' 'Version'
PS > $SnacVersion.Version
and the result could be
9.00.4035.00
The SNAC version for SQL Server 2008 can be found in the Registry at
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server Native Client 10.0\CurrentVersion\Version : [REG_SZ]

To get the SNAC version on a remote host, I have used WMI and the StdReg provider:
function Get-SnacVersion {
Param( [string]$ServerName = '.' ) # Default local host
 $Reg = [WMIClass]"\\$ServerName\root\default:StdRegProv" # Remote host
 $HKLM = 2147483650
 $RegBranch = 'software\microsoft\microsoft sql native client\currentversion'
 $RegItem = 'Version'
 $Reg.GetStringValue($HKLM, $RegBranch, $RegItem).sValue
}

The function is called like this for the local host:
Get-SnacVersion
Getting the SNAC version on a remote host the function is called like this:
Get-SnacVersion 'Sandbox.sqladmin.dk'
The result is equal the result above from the Registry.

The existance of SNAC can be determined by either the existance of the Registry braches above or if the file "sqlncli.dll" exists in the folder "%SYSTEM%" (e.i. "C:\WINDOWS\system32\").
The SQL Server 2008 SNAC is implemented in the file "sqlncli10.dll".

Installation of SNAC itself can be done with the Feature Pack for Microsoft SQL Server.
A reference to this can be found at the page referenced in the beginning of this entry.

Why Microsoft (again) has a seperate installation and path for SQL Server 2008 I don't know. I'm not impressed as is will only make automated maintenance much more complex.

The book "Windows PowerSehll: TFM" (2nd Edition) har a whole chapter on managing the Registry with PowerShell (chapter 30).

2009-01-17

WPF Menu using XAML and PowerShell

Success - Inspired by a blog entry from The PowerShell GUY (link) I managed to get a solution on the last WPF example, but this time using XAML:

Add-Type –assemblyName PresentationFramework

[xml]$XAML = @'
<window
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Title="SQLAdmin" Height="250" Width="400">
<Grid>
<Menu Height="22" Name="MainMenu" VerticalAlignment="Top" HorizontalAlignment="Stretch">
<MenuItem Header="_Systems" Name="SystemsMenuItem">
<MenuItem Header="_Tools">
<MenuItem Header="Management Studio" Name="MgmtStudMenuItem">
</menuitem>
</menu>
<Canvas Name="ContentCanvas" Margin="0,22,0,0">
</grid>
</window>
'@
$Reader = New-Object System.Xml.XmlNodeReader $XAML
$Form = [Windows.Markup.XamlReader]::Load( $Reader )

$Label = New-Object Windows.Controls.Label
$Label.Content = "This initial laben contain several lines`nof text."

$ContentCanvas = $Form.FindName("ContentCanvas")
$ContentCanvas.Children.Add($Label)

$SystemsMenuItem = $Form.FindName("SystemsMenuItem")
$SystemsMenuItem.add_click({ $Label.Content = 'SQLAdmin' })

$MgmtStudMenuItem = $Form.FindName("MgmtStudMenuItem")
$MgmtStudMenuItem.Add_Click({ $Label.Content = 'Starting SQL Server Management Studio...' })

$Form.ShowDialog() | out-null


The result is similar to that of the former WPF MenuItem example.

2009-01-13

WPF Menu using PowerShell

Usually I use HTA for a quick application, but with PowerShell V2 it's possible to use Windows Presentation Foundation (WPF) with a lot of nice features I don't have to code from the bottom.

This little script is a investigation into the MenuItem class and click events:

$Window = New-Object Windows.Window
$Window.Title="SQLAdmin"
$Window.Height="250"
$Window.Width="400"

$SystemMenuItem = New-Object Windows.Controls.MenuItem
$SystemMenuItem.Header = "_Systems"
$SystemMenuItem.add_Click({ $Label.Content = 'SQLAdmin' })

$ManStudToolMenuItem = New-Object Windows.Controls.MenuItem
$ManStudToolMenuItem.Header = "_Management Studio"
$ManStudToolMenuItem.add_Click({ $Label.Content = 'Starting SQL Server Management Studio...' })

$ToolMenuItem = New-Object Windows.Controls.MenuItem
$ToolMenuItem.Header = "_Tools"
$ToolMenuItem.Items.Add($ManStudToolMenuItem)

$Menu = New-Object Windows.Controls.Menu
$Menu.Height="22"
$Menu.VerticalAlignment="Top"
$Menu.HorizontalAlignment="Stretch"
$Menu.Items.Add($SystemMenuItem)
$Menu.Items.Add($ToolMenuItem)

$Label = New-Object Windows.Controls.Label
$Label.Content = "This initial laben contain several lines`nof text."

$ContentCanvas = New-Object Windows.Controls.Canvas
$ContentCanvas.Margin="0,22,0,0"
$ContentCanvas.Children.Add($Label)

$Grid = New-Object Windows.Controls.Grid
$Grid.Children.Add($Menu)
$Grid.Children.Add($ContentCanvas)

$Window.Content = $Grid

[void]$Window.ShowDialog()


When the script starts, this window is shown:



Clicking the menu item "Systems" changes the contents of the white area:


The menu item "Tools" has a sub-item "Management Studio":

Clicking the menu item "Management Studio" changes the contents of the white area (again...):

Well - not exactly Rocket Science, but I found out how to use WPF MenuItem :-)
Still the formatting is done programatically. I would like to know how to use XAML for formatting and PowerShell for functionality.
References
MSDN Library > Windows Presentation Foundation
Windows PowerShell Blog : WPF & PowerShell -- Part 3 (Handling Events)
Huddled Masses : WPF From PowerShell - Select-Grid
Charles Petzold : Applications = Code + Markup (ISBN-13: 978-0-7356-1957-9)

2009-01-07

Selectivity

After a breif discussion with a collegue about selectivity this script was created to have something concrete to work with.

-- Single key
SELECT TOP(10)
 SalesOrderID,
 (CAST(COUNT(*) AS float) / CAST((SELECT COUNT(*) FROM Sales.SalesOrderDetail) AS float))*100 AS [percent]
FROM Sales.SalesOrderDetail WITH (NOLOCK)
GROUP BY SalesOrderID
ORDER BY [percent] DESC

-- Combined key
SELECT TOP(10)
 SalesOrderID,
 SalesOrderDetailID,
 (CAST(COUNT(*) AS float) / CAST((SELECT COUNT(*) FROM Sales.SalesOrderDetail) AS float))*100 AS [percent]
FROM Sales.SalesOrderDetail WITH (NOLOCK)
GROUP BY SalesOrderID, SalesOrderDetailID
ORDER BY [percent] DESC


On SQL Server 2005 the database is [AdventureWorks] and on SQL Server 2008 the database is [Adventureworks2008].

Still I'm (a little) uncertain if the % calculation is correct - right now the percentage is low (good), but should it be high (good)? I'm looking for a more precise calculation definition.

2009-01-06

Differential database backup using SMO

A differential database backup of a single database can be done like this using SMO and PowerShell:

[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
$SsdbName = 'Sandbox\Ssdb0'
$BackupPath = 'Z:\Backup\Ssdb0'
$Server = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $SsdbName
$Database = $Server.Databases['msdb']
$Backup = New-Object 'Microsoft.SqlServer.Management.Smo.Backup'
$Backup.Checksum = $true
$Backup.Database = $Database.Name
$Backup.Incremental = $true
$Backup.BackupSetDescription = 'Differential backup of the database [' + $Database.Name + '].'
$BackupFileName = $Database.Name + '_Diff.bak'
$BackupDevice = New-Object 'Microsoft.SqlServer.Management.Smo.BackupDeviceItem'
$BackupDevice.DeviceType = 'File'
$BackupDevice.Name = [System.IO.Path]::Combine($BackupPath, $BackupFileName)
$Backup.Devices.Add($BackupDevice)
$Backup.BackupSetName = $Database.Name + ' ' + $BackupType + ' backup'
$Backup.SqlBackup($Server)
$Backup.Wait()
"[{0}] is backed up to the file '{1}'." -f $Database.Name, $BackupDevice.Name


The generated backup file is named "msdb_Diff.bak". A more unique filename is preferred, but this is just a simple example.

The output of the script is:
[msdb] is backed up to the file 'T:\Backup\Ssdb0\msdb_Diff.bak'.

A more detailed description of SMO backup using PowerShell is done by Muthusamy Anantha Kumar in a article at Database Journal (link).