2014-09-09

MS14-044 cleanup

With the roll-out of the Security Bulletin MS14-044 I found out the hard way that the DBA Repository (SQLAdmin Repository) did not have the new version numbers.

  • 10.0.5520 for SQL Server 2008 SP3.
  • 10.50.4033 for SQL Server 2008 R2 SP2.
  • 11.0.3153 for SQL Server 2012 SP1.
  • 12.0.2254 for SQL Server 2014.
When the data were inserted, the automation ran with success.
It looks like the Security Update only affect SP1 of SQL Server 2012.
Usually the SQL Server Database Engine service restart twice - one time to come up in Single-User Mode and the second time to come up in normal Multi-User Mode.
This is different on the versions, where SQL Server 2014 does the Single-User Mode restart without further logging in the SQL Server Errorlog.
On SQL Server 2012 the Database Engine service also restarts, but come up in Script Upgrade Mode with detailed logging on the execution of the upgrade scripts. The logging takes 2000+ lines in SQL Server Errorlog, which I will not trouble you with here.
The Update itself takes up to a few minutes. It depends...

Reference

Again it was a great help that the unofficial but well-known „Microsoft SQL Server Version List“ was updated.
In general I also like to keep an eye on „Update Center for Microsoft SQL Server“, which is on Microsoft TechNet and can be regarded as Microsoft official.
The abbreviations like GDR and SP are well explained at Microsoft SQL Server Version List, but you can get the Microsoft background at „An Incremental Servicing Model…“ from Microsoft Support and at the wikipedia article „Software release life cycle“.

2014-04-07

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
/FEATURES="AS"
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=11.0.0.0, 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...

2014-03-23

Test-Computer

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 {
[CmdletBinding()]
Param(
  [Parameter(Mandatory=$true, HelpMessage='Enter name of server. Use Fully Qualified Name (FQN), e.g. "SANDBOX.sqladmin.lan"')]
  [String]$ServerName
)

[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()

2014-03-15

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