Vacation! And a few gotchas...

The blog has been slow lately due to the impending vacation. I’ll toss in a few things I stumbled on the last week though:

When dealing with a cluster, chances are that some of the disks just won’t be returned to the fold when deleted from available storage. For some reason, the cluster sometimes keeps the reservations on some disks, leading to some rather weird error messages. The solution is to us powershell to give said reservations the boot like this:

Clear-ClusterDiskReservation -disk X -force

Speaking of clusters; trying to find which node the SQL Server is running from can be a bit of a challenge from within SQL Server. Try this script that I found over at SQLMatters.com:

With ClusterActiveNode as
(
SELECT SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) as NodeName, Cast(‘Active’ as varchar(10)) as Active
),
ClusterNodes as
(
SELECT NodeName FROM sys.dm_os_cluster_nodes
)
Select b.nodename, isnull(active,‘Passive’) as NodeStatus from ClusterNodes as b left join ClusterActiveNode as a
on a.NodeName = b.nodename

I’ve also thoroughly enjoyed setting up a 2008R2 cluster on Windows 2012R2 and mount points. That’s also riddled with some interesting … features. A couple of good links are an mssqltips.com article here, a Microsoft Connect entry here and a blog post from Jacob Moran from 2009 here.

Found a nice script to set up instant file instantiation with powershell, written by Ingo Karstein:

#written by Ingo Karstein, http://ikarstein.wordpress.com

# v1.1, 10/12/2012

powershell-running-a-command-as-administrator

<— Configure here

$accountToAdd = “DOMAIN\Username”
$pathToFile1 = “c:\temp\secedit.sdb”
$pathToFile2 = “c:\temp\PerformVolumeMaintenanceTasks.txt”

—> End of Config

Get the ID and security principal of the current user account

$myWindowsID = [System.Security.Principal.WindowsIdentity]::GetCurrent();
$myWindowsPrincipal = New-Object System.Security.Principal.WindowsPrincipal($myWindowsID);

Get the security principal for the administrator role

$adminRole = [System.Security.Principal.WindowsBuiltInRole]::Administrator;

Check to see if we are currently running as an administrator

if($myWindowsPrincipal.IsInRole($adminRole))
{
    # We are running as an administrator, so change the title and background colour to indicate this
    $Host.UI.RawUI.WindowTitle = $myInvocation.MyCommand.Definition + “(Elevated)”;
    $Host.UI.RawUI.BackgroundColor = “DarkBlue”;
    Clear-Host;
}else{
    # We are not running as an administrator, so relaunch as administrator

# Create a new process object that starts PowerShell
$newProcess = New-Object System.Diagnostics.ProcessStartInfo “PowerShell”;

# Specify the current script path and name as a parameter with added scope and support for scripts with spaces in it’s path
$newProcess.Arguments = “& ‘” + $script:MyInvocation.MyCommand.Path + “’”

# Indicate that the process should be elevated
$newProcess.Verb = “runas”;

# Start the new process
[System.Diagnostics.Process]::Start($newProcess);

# Exit from the current, unelevated, process
Exit;
}

And finally a nice script from Eric Humphrey to set SQL Server trace flags with Powershell:

##############################################################################

Add-SqlServerStartupParameter

by Eric Humphrey (http://www.erichumphrey.com/category/powershell/)

##############################################################################

.SYNOPSIS

Adds an entry to the startup parameters list for all instances of SQL Server
on a computer

.EXAMPLE

PS >Add-SqlServerStartupParameter ‘-T3226’

param(
    ## The parameter you wish to add
    [Parameter(Mandatory = $true)]
    $StartupParameter
)

$hklmRootNode = “HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server”

$props = Get-ItemProperty “$hklmRootNode\Instance Names\SQL”
$instances = $props.psobject.properties | ?{$_.Value -like ‘MSSQL*’} | select Value

$instances | %{
    $inst = $_.Value;
    $regKey = “$hklmRootNode\$inst\MSSQLServer\Parameters”
    $props = Get-ItemProperty $regKey
    $params = $props.psobject.properties | ?{$_.Name -like ‘SQLArg*’} | select Name, Value
    #$params | ft -AutoSize
    $hasFlag = $false
    foreach ($param in $params) {
        if($param.Value -eq $StartupParameter) {
            $hasFlag = $true
            break;
        }
    }
    if (-not $hasFlag) {
        “Adding $StartupParameter”
        $newRegProp = “SQLArg”+($params.Count)
        Set-ItemProperty -Path $regKey -Name $newRegProp -Value $StartupParameter
    } else {
        “$StartupParameter already set”
    }
}

Alexander Arvidsson