SQL Server major version

When I automate installation or administration I often has to use the major version of the SQL Server database installation.
Unfortunately SERVERPROPERTY('ProductVersion') return a string with the complete versionnumber, and there is no other propertyname to get only the major version.
I would like something similar to the SMO Server.VersionMajor property.

Some cutting and casting does the trick to get the left part before the first dot:
DECLARE @version_major INT = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(MAX)), CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(MAX))) - 1) AS INT);

This gives me a integer that can be compared or be part of a calculation like
... ( @version_major - 1 ) ...

Some security configuration statements are dependant on the major version, and can behandled like this
IF @version_major <= 8
  PRINT N'Not SQL Server 2005 or above.';
ELSE
  PRINT N'SQL Server 2005 or above';

In my SQL Server repository I store the version information in the table [sqladmin].[version], where the version number is in the column [version].[version_number] as NVARCHAR(128).
The major version in a SELECT statement as integer can be done with something like this:
SELECT
  N'ssdb_version_major' = CASE [version].[version_number]
    WHEN N'(unknown)' THEN 0
    ELSE CAST( LEFT([version].[version_number], ABS(CHARINDEX(N'.',[version].[version_number])-1)) AS INT)
  END
FROM
  [sqladmin_repository].[sqladmin].[version];

Please notice the ABS() on the CHARINDEX() because of the subtraction (-1). It looks like the SQL Server optimizer looks at the subtraction before CHARINDEX(). Without the ABS() the statement failed with this error:
Msg 537, Level 16, State 3, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
The case on the string „(unknown)“ is because the column is defined NOT NULL, and a unknown version number - usually because there is no connection between the repository collector and the database instance - is given the default value „(unknown)“. Even when a subset is selected by a WHERE clause, all rows are evaluated for CAST(). Without the CASE the statement failed with this error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '(' to data type int.


(This is a running update from 2011-12-21)

VLF count on SQL Server 2012

Some time ago I posted the blog entry „VLF count“ where the (undocumented) function „DBCC LogInfo“ is used to collect the VLF count of a database.
This I have implemented in my SQL Server repository, but with the first SQL Server 2012 installation, the collection failed. The collection is done on a given database with this statement:
SET NOCOUNT ON;
CREATE TABLE #stage(
 [file_id] INT
 ,[file_size] BIGINT
 ,[start_offset] BIGINT
 ,[f_seq_no] BIGINT
 ,[status] BIGINT
 ,[parity] BIGINT
 ,[create_lsn] NUMERIC(38)
);
INSERT INTO #stage EXECUTE (N'DBCC LogInfo WITH no_infomsgs');
SELECT COUNT(1) FROM #stage;
DROP TABLE #stage;

And the error message is:
Msg 213, Level 16, State 7, Line 1
Column name or number of supplied values does not match table definition.

It turns out that the resultset of DBCC LogInfo has changed with SQL Server 2012, where SQL Server 2000 to 2008 R2 has these DBCC LogInfo columns:

Name	Type
FileId	INT
FileSize	BIGINT
StartOffset	BIGINT
FSeqNo	BIGINT
Status	BIGINT
Parity	BIGINT
CreateLSN	NUMERIC(38)

And SQL Server 2012 has these DBCC LogInfo columns:

Name	Type
RecoveryUnitId	INT
FileId	INT
FileSize	BIGINT
StartOffset	BIGINT
FSeqNo	BIGINT
Status	BIGINT
Parity	BIGINT
CreateLSN	NUMERIC(38)

The column „RecoveryUnitId“ is added in the beginning of the resultset.

On SQL Server 2012 this rewritten statement gives the VLF count of the current database:
SET NOCOUNT ON;
CREATE TABLE #stage(
 [recovery_unit_id] INT
 ,[file_id] INT
 ,[file_size] BIGINT
 ,[start_offset] BIGINT
 ,[f_seq_no] BIGINT
 ,[status] BIGINT
 ,[parity] BIGINT
 ,[create_lsn] NUMERIC(38)
);
INSERT INTO #stage EXECUTE (N'DBCC LogInfo WITH no_infomsgs');
SELECT COUNT(1) FROM #stage;
DROP TABLE #stage;

I am rewriting (refactoring) my script to depend on the major version number.

BTW – Please take a look on the Microsoft Connect item „Document DBCC LOGINFO() or create new DMVs to view VLF info“.

Check NetBackup file backup

I have been looking in ways to ensure that a SQL Server backup file is backed up by NetBackup, so that the backup file can be deleted from the local disk. I have not found a API for the local NetBackup client, but there are a lot of command-line tools.
In this case our backup vendor has pointed out the tool "bplist" (bplist.exe). Usually bplist is located in the folder „%ProgramFiles\Veritea\NetBackup\bin\“.
A bplist answer example looks like this:
-rwx------ root;SQL1 root;Doma      485888 Jul 29 20:00:01 E:\MSSQL\Backup\ReportServer_backup_2012_07_28_221142_2861771.bak
if bplist is called with the parameters „-l -b -Listseconds“.

WARNING!!!

No year on NetBackup backup.
This is also discussed in the forum thread "bplist does not display the year of a backup?".
It looks like there is an issue on the age of the backup, if it is more than six months old or not.
Also when I look in the documentation, there are some differences on the answer from the NetBackup server if it is Windows or Linux. Please notice that it is the NetBackup server, not the client operating system that gives the answer.

bplist can be executed in PowerShell with the invoke operator (&):
& "<NetBackup folder>\bplist.exe" -l -b "X:\MSSQL\Backup\sqladmin_repository_backup_2012_12_24_172249_7006410.bak"
The full path must be provided for the file to examine in NetBackup.

To look at how to handle bplist and its answer in a automated way, I made this PowerShell spike script:
param (
  [Parameter()]
  [ValidateScript({Get-ChildItem -Path $_})]
  [string]$NetBackup_Folder = 'C:\Program Files\Veritas\NetBackup\bin'
)

Set-StrictMode -Version 2.0

function Get-NetBackup_bplist {
[CmdletBinding()]
param(
  [Parameter(Mandatory=$true,ValueFromPipeline=$true)]
  [System.IO.FileInfo]$File
)
  BEGIN { Write-Verbose "OS Version = $([System.Environment]::OSVersion.VersionString)" }
  PROCESS {
    Write-Verbose "File name = '$($File.FullName)'."

    $BpList = New-Object -TypeName PSObject
    $BpList = $File
    $BpList.PSObject.TypeNames.Insert(0,'SqlAdmin.NetBackup.BpList')

    # Get file backup status from NetBackup
    $bplist_answer = $(& "$NetBackup_Folder\bplist.exe" -l -b -Listseconds "$($File.FullName)") 2>&1 # Redirect bplist.exe error to $bplist_answer
    if ($bplist_answer.GetType().IsArray) { # Multiple backups in NetBackup
      Write-Verbose " $($bplist_answer.Length) backups found in NetBackup. Will continue on last backup."
      $_bplist = $bplist_answer[0] # Get last backup in NetBackup
    }
    else {
      $_bplist = $bplist_answer
    }
    Write-Verbose " $($_bplist)"

    # Evaluate file backup status from NetBackup. Add -PassThru to last added member.
    if ($_bplist.ToString() -ceq 'EXIT STATUS 227: no entity was found') { # File not in NetBackup
      Write-Verbose " --- NO backup in NetBackup ($($File.Name))."
      Add-Member -InputObject $BpList -MemberType NoteProperty -Name HasBackup -Value $false
      Add-Member -InputObject $BpList -MemberType NoteProperty -Name BackupLength -Value $null
      Add-Member -InputObject $BpList -MemberType NoteProperty -Name BackupTime -Value $null
    }
    else {
      Write-Verbose " +++ Backup is in NetBackup ($($File.Name))."
      Add-Member -InputObject $BpList -MemberType NoteProperty -Name HasBackup -Value $true

      # Get backup details
      $regex = [regex]"\w+"
      $Backup = $($regex.matches($_bplist)) # Returns [System.Text.RegularExpressions.Match]

      if ($Backup[5].Value.SubString($Backup[5].Length-1) -eq 'K') { # Is the most right char 'K'?
        $Backup_Size = [int]$Backup[5].Value.SubString(0,$Backup[5].Length-1) * 1024 # Convert from KB to Bytes
      }
      else {
        $Backup_Size = [int]$Backup[5].Value
      }
      Add-Member -InputObject $BpList -MemberType NoteProperty -Name BackupLength -Value $Backup_Size
      $DateParse = "$($Backup[7].Value) $($Backup[6].Value) $([System.DateTime]::Now.Year) $($Backup[8].Value):$($Backup[9].Value):$($Backup[10].Value)"
      Add-Member -InputObject $BpList -MemberType NoteProperty -Name BackupTime -Value $([System.DateTime]::Parse($DateParse))
    }
    Write-Output $BpList
  }
  END {}
}


### INVOCATION ###
switch -casesensitive ($ComputerName) {
  'TRACY.SQLADMIN.LAN' {
  Get-ChildItem -Path 'D:\MSSQL_Backup' |
  Sort-Object -Property Length -Descending |
  Sort-Object -Property LastWriteTime -Descending |
  Select-Object -First 100 |
  Get-NetBackup_bplist | #-Verbose |
  Where-Object { $_.HasBackup -eq $true } |
  #Format-Table Name,Length,HasBackup,BackupTime,BackupLength -AutoSize
  ForEach-Object { Remove-Item $_.FullName -Confirm:$true }
  break
  }
  default {
  Get-ChildItem -Path 'X:\MSSQL\Backup' |
  Sort-Object -Property Length -Descending |
  Select-Object -First 20 |
  Get-NetBackup_bplist |
  #Format-Table Name,Length,HasBackup,BackupTime -AutoSize
  ForEach-Object { Remove-Item $_.FullName -Confirm:$true }
  break
  }
}

Again – a spike…

The general motivation to look into this is to ensure that the restore chain is complete, also in the secondary backup on NetBackup.
The complete restore chain is necessary to ensure complete recovery.

(This is a running update on a post from 2012-08-27)

SQL Server 2008 on virtual Windows 8

As preparation for a study group at work for MCTS 70-448 „Microsoft SQL Server 2008 – Business Intelligence Development and Maintenance“ I install SQL Server 2008 R2 Developer Edition on a virtual Windows 8 Enterprise Edition.
The virtualizer is VMware Player. Not because I have evaluated the possibilities on other products, but because I am used to VMware products.

I choose not to use Easy Install

I once tried to map the ISO file in the wizard but even the wizard recognizes Windows 8 and I copied the license code in the VMware Player Easy Install, I get this error message from Windows Setup several times:

I guess that the issue is that Windows 8 is validating licence key against a licence server, and I don't have such one in my study.

The guest is defined with a larger virtual disk than default and in one file on host.

Also the guest is given 5120 MB (5 GB) of guest memory.

After the guest is defined the guest CD drive is mapped to Windows 8 ISO file and the installation when the guest is started.

The Windows 8 Action Center tells me that the installation it must be activated, but this fails with a error message about a DNS name:

The real issue is that I do not have a Key Management Server (KMS) in my study. This is described in KB929826, where some solutions also are given. In my case method 1 works fine.
Actually I started a PowerShell as administrator, but still it works.
Microsoft is talking a lot about PowerShell, but slmgr is a vbs-file executed by Windows Script Host (WSH). This is indicated by the success message, where the title of message box is „Windows Script Host“.

From then it is a normal SQL Server 2008 installation with the components described in the MCTS 70-448 Training Kit.