Add AMO without SQLPS

When installing a SQL Server Analysis Services instance without a Database Engine instance to comply with the principle of Least Service the namespace Microsoft.AnalysisServices part of AMO is not available locally, and the PowerShell command
Import-Module -Name SQLPS -DisableNameChecking
fails with the error
Import-Module : The specified module 'SQLPS' was not loaded because no valid module file was found in any module directory.
It looks like the SQLPS is not part of a bare Analysis Services installation where the feature selection only is
in a command-line installation.

To use the namespace Microsoft.AnalysisServices I found out that all I had to do was to add the type Microsoft.AnalysisServices, but the PowerShell CmdLet Add-Type requires a full assembly name.
The article "Powershell Add-Type – Where’s That Assembly" by Kyle Neier gives the full name for what looks to be SQL Server 2005 with the version 9.n. I am working with SQL Server 2012 that has version 11.n, and would like to prepare for SQL Server 2014 (version 12.n) and beyond. This gives that a simple command like
Add-Type -AssemblyName 'Microsoft.AnalysisServices, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91'
is not good enough as it is version-dependant.

Kyle's article showed me where to look for the version and token, and some manual browsing showed me that the assembly is located among the combined (32/64 bit) assemblies. A little split and merge of strings gave this little script to add the type Microsoft.AnalysisServices independant of SQL Server version
$Assembly= $(Get-ChildItem 'C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.AnalysisServices.DeploymentEngine').Name.Split('_')
$AssemblyName = "Microsoft.AnalysisServices, Version=$($Assembly[1]), Culture=neutral, PublicKeyToken=$($Assembly[3])"
Add-Type -AssemblyName $AssemblyName

To get the token I take the third item in the array Assembly. The second item is a empty string as there are two underscores ('_') in the folder name between the version part and the token part of the folder name.

I think I am home-safe. That is a least until the path is changed...



I am working on an automated SQL Server installation, where we use Managed Serviece Accounts (MSA) as SQL Server service account. To create and configure a MSA some PowerShell CmdLets are given by Microsoft, but there are several steps each with it own CmdLets.
We are creating MSAs for a given computer that is used as SQL Server server, and we want to absolutely sure that the computer exists by a given name in Active Directory (AD)and DNS. That also includes that the Fully Qualified Domain Name (FQDN) is correct.
To do this check I have created a function that checks both AD and DNS. The function is constructed to a specific script, and you should probably alter something to make it suit your needs.

function Test-Computer {
  [Parameter(Mandatory=$true, HelpMessage='Enter name of server. Use Fully Qualified Name (FQN), e.g. "SANDBOX.sqladmin.lan"')]

[String]$ComputerName = $ServerName.Split('.')[0]

"{0:s}Z Testing if the computer '$ComputerName' exists in DNS..." -f $([System.DateTime]::UtcNow) | Write-Verbose
try {
  [System.Net.IPHostEntry]$IpHost = [System.Net.Dns]::GetHostByName($ComputerName)
catch [System.Management.Automation.MethodInvocationException] {
  "'$Computername' does not exist in DNS as FQDN!"
  return $false
"{0:s}Z Testing if the FQDN of '$ServerName'..." -f $([System.DateTime]::UtcNow) | Write-Verbose
if ($IpHost.HostName -ieq $ServerName) {
  "{0:s}Z FQDN '$ServerName' is OK." -f $([System.DateTime]::UtcNow) | Write-Verbose
else {
  "The computer name '$ServerName' does not match the FQDN '$($IpHost.HostName)'." | Write-Error
  return $false

"{0:s}Z Testing if the computer '$ComputerName' exists in Active Directory..." -f $([System.DateTime]::UtcNow) | Write-Verbose
try {
  [Microsoft.ActiveDirectory.Management.ADComputer]$Computer = $null
  $Computer = Get-ADComputer -Identity $ComputerName
catch [Microsoft.ActiveDirectory.Management.ADIdentityNotFoundException] {
  $ComputerError = $Error[0]
if ($Computer) {
  "{0:s}Z The computer '$ServerName' exists in Active Directory." -f $([System.DateTime]::UtcNow) | Write-Verbose
  return $true
else {
  "The computer '$ServerName' does not exist in Active Directory." | Write-Error
  return $false

} # Test-Computer()


PowerShell MessageBox

Sometimes I need a tool made in PowerShell to require extra attention from the user. When I did the scripting in Windows Script Host (WSH) I had the MsgBox function in VBScript. It is possible to create a WSH COM object in PowerShell like shown in „VBScript-to-Windows PowerShell Conversion Guide“ on Microsoft TechNet, but I prefer a .NET solution when it is available. To be fair to TechNet, the guide is written for PowerShell v1 that did not have the same interation with .NET as the present version of PowerShell.

A solution that I find easy to use is the static method Show() from the MessageBox class.
[System.Windows.Forms.MessageBox]::('Hello world!', 'Hello', 'OK' ,'Information')
This class is in the .NET namespace System.Windows.Forms.

If the script is executed in PowerShell consloe (powershell.exe) you have to load the assembly System.Windows.Forms
Add-Type -AssemblyName System.Windows.Forms
Is the script running in PowerShell ISE (powershell_ise.exe) the graphics are loaded already.

If you do not want the 'OK' written on the PowerShell console when OK in the Message Box is clicked, then you can stream it to the CmdLet Out-Null
[System.Windows.Forms.MessageBox]::Show('Hello world!', 'Hello', 'OK' ,'Information') | Out-Null

The output is generated by the MessageBoxButtons defined in the MessageBox object and what button the user pressed. In the example above ther is only one possibility („OK“) but it is possible to present other buttons to the user.
The possible MessageBoxButtons are dokumented with the MessageBoxButtons Enumeration.
The answer is from the enumration System.Windows.Forms.DialogResult, and if the answer is piped to the console it is implicit converted to a String by PowerShell.
The answer can be grabbed in a PowerShell variable and used for a more complex reaction to the DialogResult.
$Answer = [System.Windows.Forms.MessageBox]::Show('Hello world!', 'Hello', 'OkCancel' ,'Information')
switch ($Answer) {
  'OK' { 'Cheers' }
  'Cancel' { 'Having a bad day?' }

There are several possibilities for an icon. This is described in the documentation of the MessageBoxIcon Enumeration. In the examples above I have used the MessageBoxIcon member „Information“.

One of the nice things with the .NET Windows Forms is that it takes descriptive parameters like "OkCancel" instead of the numeric parameters to MsgBox. This is the reason to the many constant definitions in VBScript files.

The message part can be created in a more complex proces outside the Show() command.
On one occation I wanted the message to be formatted in lines that I added up during the script execution.
To keep track of each message line I added the line to an array, and before showing the message in the MessageBox, I added a NewLine (`n) in the end of each line.
[String[]]$Msg = @()
$Msg += 'Keep Calm'
$Msg += 'And'
$Msg += 'Trust a DBA'
[String]$MsgTxt = ''
$Msg | ForEach-Object { $MsgTxt += $_ + "`n" }
Add-Type -AssemblyName System.Windows.Forms
[System.Windows.Forms.MessageBox]::Show($MsgTxt, 'SQLAdmin Message', 'OK', 'Information') | Out-Null

The MessageBox is then
Keep Calm And Trust a DBA


Database Engine performance counters

This is some notes on a basic performance baseline. The baseline is created by some Performance Counters.
In general I am interested in these three key resources
  • CPU
  • Memory
  • Storage
These three resouces I look at from both Windows Server and SQL Server Database Engine.

Data Collector

A User Defined Data Collector Set can be created in the Performance Monitor by a right-click on
Data Collector Sets \ User Defined
and select to create the data collector set manually.

Performance Counters

LogicalDisk \ Avg. Disk Queue Length : ( data; translog; tempdb )
LogicalDisk \ Avg. Disk sec/Read ( data; translog; tempdb )
LogicalDisk \ Avg. Disk sec/Write ( data; translog; tempdb )
Memory \ Available Mbytes
Memory \ Pages/sec
PhysicalDisk \ Avg. Disk Queue Length ( data; translog; tempdb )
PhysicalDisk \ Avg. Disk sec/Read ( data; translog; tempdb )
PhysicalDisk \ Avg. Disk sec/Write ( data; translog; tempdb )
PhysicalDisk \ Disk Reads/sec ( data; translog; tempdb )
PhysicalDisk \ Disk Writes/sec ( data; translog; tempdb )
Process(sqlservr) \ % Processor Time
Process(sqlservr) \ Page Faults/sec
Processor() \ % Processor Time
Processor() \ % Privileged Time
MSSQL : Access Methods \ Forwarded Records/sec
MSSQL : Access Methods \ Full Scans/sec
MSSQL : Access Methods \ Index Searches/sec
MSSQL : Access Methods \ Page Splits/sec
MSSQL : Buffer Manager \ Buffer cache hit ratio
MSSQL : Buffer Manager \ Page life expectancy
MSSQL : Buffer Manager \ Page reads/sec
MSSQL : Buffer Manager \ Page writes/sec
MSSQL : General Statistics \ User Connections
MSSQL : Latches \ Average Latch Wait Time (ms)
MSSQL : Locks(_Total) \ Average Wait Time (ms)
MSSQL : Locks(_Total) \ Lock Timeouts/sec
MSSQL : Locks(_Total) \ Number of Deadlocks/sec
MSSQL : Locks(_Total) \ Locks Waits/sec
MSSQL : Memory Manager \ Total Server Memory (KB)
MSSQL : Memory Manager \ Target Server Memory (KB)

MSSQL : Plan Cache \ Cache Hit Ratio
MSSQL : SQL Errors(_Total) \ *

MSSQL : SQL Statistics \ SQL Batch Requests/sec
MSSQL : SQL Statistics \ SQL Compilations/sec
MSSQL : SQL Statistics \ SQL Re-Compilations/sec
MSSQL : Wait Statistics(Average wait time (ms)) \ Network IO waits
MSSQL : Wait Statistics(Average wait time (ms)) \ Page IO latch waits

Network Interface \ Output Queue Length ( <physical interface> )
Paging File(_Total) \ % Usage
System \ Processor Queue Length

The sample interval is set to 5 seconds and the data are logged in a binary (.blg) file on a local drive.

Never do a performance measure remote as you will create more preasure by the measure and then get a blurred result.
Also logging direct in a Performance Monitor graph might give you a performance penalty. Even if it is done local on the server.

Disk performance counters are on individual drives where user data (.mdf & .ndf), transaction log (.ldf) and tempdb data (.mdf & .ndf) are placed. If there are more drives installed for one of theese places all these drives should be included in the measure. 


A quick view on one baseline measure series is easy done with a User Defined Report in Performance.
When you want to do some statistics a spreadsheet is a common tool.
To export the data from Performance Monitor to a spreadsheet you open the logged data in Performance Monitor and right-click in the graph area. Then you click Save Data As and save the data as tabulator seperated values (.tsv).
To import the data in a Excel spreadsheet you create a new sheet and select the tab Data.Click the item From Text in the panel, and do the proper selections. In Denmark where I live we use comma as decimal seperator and dot as thusand seperator. This is one of the details that could trick you. The first time you do a import you might not get it right, but I am sure you will get it right after a few tries.

When you have more sheets from several measure series you can do some delta compares and produce some nice diagrams for management. Most diagrams I create as X-Y diagrams, but the type depends on what you are looking for or trying to show.

Some basic statistics that are usefull are
The calculation I usually add in rows at the top of the sheet.


Be aware when
  • Disk latency (sec/Read & sec/Write) is higher than 20 ms. If the value is less than 8 ms it is excellent.
  • Page life expectancy is less than 500 in intervals. If the value is less than 300 in regular spikes this should be investigated.
(To Be Continued...)


Jonathan Kehayias: „The Accidental DBA (Day 21 of 30): Essential PerfMon counters“.