SQL Server versions and builds

This is a very nice and usefull list of SQL Server versions and builds:
http://sqlserverbuilds.blogspot.com/

if operator (not) in PowerShell

I do miss the if operator (?:) that I have in C++, C#, JavaScript and likewise but not in PowerShell.

A quick workaround is the subexpression ($()) in a string surrounded by ".

$ref = 'blue'
"The color is $(if ($ref -eq 'blue'){''} else{'not '})blue."
The output is
The color is blue.

If the $ref value is changed
$ref = 'yellow'
The output is
The color is not blue.

Reference

wikipedia: ?:

Automatic SPN registration

First read the blog entry „What SPN do I use and how does it get there?“ by the CSS SQL Server Engineers.
Then read the Knowledge Base article „How to use Kerberos authentication in SQL Server“ (KB319723).

All configuration I have done on a Domain Controller. It might be possible to use another server.
You have to be domain Administrator to do this.
Before configuration you can check by the commandline statement
setspn -l sqlaccount01
Which should give a output like
Registered ServicePrincipalNames for CN=SQLACCOUNT01,OU=DBA,DC=SQLADMIN,DC=LAN:

Then configure the SPN registration by „Step 3: Configure the SQL Server service to create SPNs dynamically“ in KB319723.
Restart the SQL Server database service.

Check the configuration by the command line statement
setspn -l sqlaccount01
This should give a output like
Registered ServicePrincipalNames for CN=SQLACCOUNT01,OU=DBA,DC=SQLADMIN,DC=LAN:
MSSQLSvc/SANDY.SQLADMIN.LAN:1433
MSSQLSvc/SANDY.SQLADMIN.LAN
When the database service is restarted two entries are made in the SQL Error Log
2010-09-08 07:06:05.04 Server The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/SANDY.SQLADMIN.LAN ] for the SQL Server service.
2010-09-08 07:06:05.04 Server The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/SANDY.SQLADMIN.LAN:1433 ] for the SQL Server service.

This is a manual process that should be automated...

SQL PowerShell output

There are several CmdLets for output in PowerShell. I got some by the statements
man write-*
and
man out-*

Putting the CmdLets in a script file
$DebugPreference = "Continue"

":: (default)"
":: Out-Null" | Out-Null
":: Out-Default" | Out-Default
":: Out-Host" | Out-Host
#":: Out-File"
#":: Out-Printer"
":: Out-String" | Out-String

Write-Host ":: Write-Host"
#Write-Progress ":: Write-Progress"
Write-Debug ":: Write-Debug"
Write-Verbose ":: Write-Verbose"
Write-Warning ":: Write-Warning"
Write-Error ":: Write-Error"
Write-Output ":: Write-Output"
I have disabled the calls of the CmdLets Out-File, Out-Printer and Write-Progress as I find them irrelevant to this little experiment.

Executing the script file gives in PowerShell (powershell.exe)
:: (default)
:: Out-Default
:: Out-Host
:: Out-String

:: Write-Host
DEBUG: :: Write-Debug
WARNING: :: Write-Warning
H:\Script Library\Output.ps1 : :: Write-Error
At line:1 char:13
+ .\Output.ps1 <<<<
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Output.ps1

:: Write-Output

and in SQL PowerShell (sqlps.exe)
:: (default)
:: Out-Default
:: Out-Host
:: Out-String

DEBUG: :: Write-Debug
WARNING: :: Write-Warning
H:\Script Library\Output.ps1 : :: Write-Error
At line:1 char:13
+ .\Output.ps1 <<<<
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Output.ps1

:: Write-Output
In the script for SQL PowerShell I removed the call to Write-Host as is fails with
A job step received an error at line 18 in a PowerShell script. The corresponding line is 'Write-Host ":: Write-Host"'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot invoke this function because the current host does not implement it.

When I execute the script in a PowerShell SQL Agent job step using the Invoke operator (& "H:\Script Library\Output.ps1"), the output file contains
:: (default)
:: Out-String

:: Write-Output

The job script encountered the following errors. These errors did not stop the script:
A job step received an error at line 1 in a PowerShell script. The corresponding line is '& "H:\Script Library\Output.ps1"'. Correct the script and reschedule the job. The error information returned by PowerShell is: ':: Write-Error
'

I was a little surprised to see that the Debug Preference and the CmdLets Out-Default and Out-Host were ignored.
A quick conclusion is that the implicit pipe and the CmdLet Write-Output is general usable.

SQL Server TCP port

A SQL Server database instance has dynamic TCP port by default, and when installing a instance it is "Best Practice" to set a static TCP port.
Getting the actual TCP port configuration is usual done quickly and manual by SQL Server Configuration Manager, but when I have several installations it is rather cumbersome to logon to each Windows Server to check.
The PowerShell snippet below gets the basic information using SQL Server Management Objects (SMO).

$ComputerName = 'sandbox.sqladmin.lan'
$SsdbName = 'sandy'

[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement')

$smoComputer = New-Object 'Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer' $ComputerName
if ($SsdbName -eq '.') { # Default database instance
  $smoTcp = $smoComputer.ServerInstances['MSSQLSERVER'].ServerProtocols['Tcp']
}
else {  # Named database instance
  $smoTcp = $smoComputer.ServerInstances[$SsdbName].ServerProtocols['Tcp']
}
if ($smoTcp.IPAddresses['IPAll'].IPAddressProperties['TcpDynamicPorts'].Value -eq '') {
  [bool]$smoTcpIsDynamic = $false
  [int]$smoTcpPortNumber = $smoTcp.IPAddresses['IPAll'].IPAddressProperties['TcpPort'].Value
}
else {
  [bool]$smoTcpIsDynamic = $true
  [int]$smoTcpPortNumber = $smoTcp.IPAddresses['IPAll'].IPAddressProperties['TcpDynamicPorts'].Value
}

"ssdb_tcp_port = $smoTcpPortNumber; ssdb_tcp_is_dynamic = $smoTcpIsDynamic"

The variable $ComputerName hold the name of the computer... and the variable $SsdbName holds the name of the database instance. If the instance is default I use the value '.' (dot).
If the script invoked on a named instance with static TCP port, the output is like
ssdb_tcp_port = 54321; ssdb_tcp_is_dynamic = False
and from a named instance with dynamic TCP port the output is like
ssdb_tcp_port = 1930; ssdb_tcp_is_dynamic = True

Reference
Allen White: „Accessing WMI Information Using SMO“