SQL Server 2012: building failover cluster

Just a note to self after tonights exam preparation on how to create a SQL Failover Cluster.

Every database server that hosts critical system should be created as high-availability solutions. This can be done by creating “Failover Cluster” or “AlwaysOn Availability Groups”. This post only includes notes on important points for Failover cluster.

  1. Configure shared storage. Make sure servers are in the OU for SQL Servers. Apply firewall rules for ports TCP/135, TCP/3260, in addition to UDP/138 and any SAN target software. Bring SAN volumes online and create disk partitions. Add DNS record for cluster name.
  2. Configure Windows Server R2 failover cluster. Apply .NET Framework 3.5.1 on all nodes. Configure cluster servers (nodes). Set cluster name and IP.
  3. Run “Advanced Cluster Preparation” on each node.  Each node in cluster need to perform this preparation task with node reboot. Installation includes db engine, replication, Mgmnt tools on default instans.
  4. Run “Advanced Cluster Completion” on node with shared storage.  Verify SAN disks. Set server network name, resource group, cluster IP and subnet-mask, including online status verfication.
  5. Perform a cluster failover. This can be done by powershell command “Move-ClusterGroup <resourcegroup> <node>”

Powershell – validate domain user with password

Another common problem yesterday: how to validate a domain user with a password. I had a service account that I suscpected had an invalid password. After some googling and trying different solutions, I came accross a post by Shay Levy that fitted my purpose.

I wrote a function based on the Levy post, and the following function CheckCredentials will validate username on format “domain\user” and password againt the supplied domain.

Add-Type -AssemblyName System.DirectoryServices.AccountManagement 

function CheckCredentials( [String]$username,  [String]$password)
    # find seperator character '\' in username string
    $sepidx = $username.IndexOf('\') ;

    # pick domain from username string
    $domain = $username.Substring(0, $sepidx);
    # pick user from username string
    $user = $username.Substring($sepidx+1, ($username.Length - $sepidx)-1)

    # create instance for domian principle context for input user
    $ct = [System.DirectoryServices.AccountManagement.ContextType]::Domain
    $pc = New-Object System.DirectoryServices.AccountManagement.PrincipalContext `

    # validate user credential for user with password against domain
    $res = $pc.ValidateCredentials($user,$password)

    return $res; #true: ok, false: invalid username and passwrod

The script will pick the domain from the supplied username string based on the ‘\’ seperator character. The function returns true of the username is successfully validated, otherwise false.

Enabling Poweshell ISE for Windows Server 2008 R2

Working with Powershell scripts the last few weeks, I have discovered that the Powershell ISE (Integrated Scripting Environment) isn’t installed by default on many servers. This can easily be added, either by “Add Feature” or by powershell itself. As  old-fashion, hard-core developer, I’m using the command shell 🙂

  1. Open Powershell command shell
  2. Execute following cmdlets.
Import-Module ServerManager 
Add-WindowsFeature PowerShell-ISE

Check eventlog status in powershell

When we monitor servers, we need to get notified if there are new errors and/or warnings in the Windows Eventlog.. Normally, it is the Application event log we check for these errors and warnings. The required functionality is that the script should check for errors and warnings on the current machine and if the number of event exceeds a maximum, the script should send an email to administrator to notify that some further investigation is needed on the server.

Here is the second powershell script I wrote today:


param([String]$logname="Application", [Int]$maxEventCount=10)

$since = ((get-date).addDays(-1));

$errwarnlist =  Get-Eventlog -logname $logname -after $since 
			-EntryType Error, Warning
$linecount = $errwarnlist | Measure-Object –Line

$msg = $env:COMPUTERNAME + " " + $logname + " eventlog has " 
       + $linecount.Lines  
       + " errors/warnings since " + $since.ToString("dd.MM.yyy HH:mm") 
       + ". Limit = " + $maxEventCount;

if ($linecount.Lines -gt $maxEventCount)
    $xto = john.doe@company.com
    $xfrom = "no-reply@company.com"
    $xsub = $env:COMPUTERNAME + ": " + $logname 
            + " eventlog exceeds limit for errors/warnings";

    Write-host $msg

    Send-MailMessage -To $xto -From $xfrom  
                 -Subject $xsub  -Body $msg 
                 -SmtpServer smtp.company.com
    Write-host $msg

Check service status in powershell

This week have been dedicated to baselining and monitoring server state. Our department is mainly monitoring SQL Server, Sharepoint  and Dynamic CRM servers for our customers. Therefore, it is very important to check if services for are running at all time. If they are not running we have to restart them, otherwise out customers will have downtime for the particular service. The powershell script below checks if a service exist and running. If it is not running the script is trying to restart the service. The script will send an email if the servcie is not installed on the server or can not be restarted.


if ($svcname.Trim().Length -eq 0)
    Write-Host "Invalid service name" ;
    return ;

$svc = get-service $svcname -ErrorAction SilentlyContinue;
if ($svc -eq $null)
    $xto = "jon.doe@company.com"
    $xfrom = "no-reply@company.com"
    $xsub = $env:COMPUTERNAME + ": " + $svcname + " service error";
    $msg = $env:COMPUTERNAME + ": " + $svcname + " service does not exist";
    Write-host $msg
    Send-MailMessage -To $xto -From $xfrom  -Subject $xsub  
                     -Body $msg -SmtpServer smtp.company.com

$sts = $svc | Select Status;
if ($sts.Status.ToString() -eq "Stopped")
    Write-Host "Service " $svcname  " is STOPPED ==> trying to restart";
    Restart-Service $svcname ;
    $svc = get-service $svcname -ErrorAction SilentlyContinue;
    if ($svc -ne $null)
        $sts = $svc | Select Status ;
        if ($sts.Status.ToString() -eq "Stopped")
            $xto = "jon.doe@company.com"
            $xfrom = "no-reply@company.com"
            $xsub = $env:COMPUTERNAME + ": " + $svcname + " service error";
            $msg = $env:COMPUTERNAME + ": " + $svcname 
                          + " service could not be restarted";
            Write-host $msg
            Send-MailMessage -To $xto -From $xfrom  -Subject $xsub  
                             -Body $msg -SmtpServer smtp.company.com

        Write-Host $svcname " status = " $sts.Status.ToString();
    Write-Host "Service " $svcname " is still RUNNING";

Calculate total and iteration elapsed time in Powershell

When you are executing long-running scripts you probably want to know how long time each part of the script is taking to execute, and the total run time of the script. The demo script below shows how to use the Get-Date and Get-Random CmdLets.

When calculating elapsed time the substraction will result in a TimeSpan database like the one in C#.

$script:startTime = Get-Date

function GetElapsedTime([datetime]$starttime) 
    $runtime = $(get-date) - $starttime
    $retStr = [string]::format("{0} sec(s)", $runtime.TotalSeconds)

write-host "Script Started at $script:startTime"
for ($i=1; $i -lt 10; $i++) 
    $iterTime = Get-Date ;
    get-process | out-null
    $waitsecs = Get-Random -minimum 1 -maximum 5
    sleep $waitsecs
    $elapsed = GetElapsedTime $iterTime ;
    write-host "   Iteration Time: " $elapsed ;

write-host "Script Ended at $(get-date)"
$elapsed = GetElapsedTime $script:startTime
write-host "Total Elapsed Time: " $elapsed;

Happy coding…

SQL Server – sending email tsql + powershell

Today, I wanted to send an email when a long-running script completed successfully. The SQL server was already configured correctly with the Database Mail Configuration Wizard, so I just had to add similar code as code below to send the mail:

EXEC msdb.dbo.sp_send_dbmail
    @recipients = 'john.doe@someplace.com',
    @body = 'The custom script was successfully completed',
    @subject = 'Automated Success Message' ;

In addition, yesterday I had to send email from a powershell script to notify owner of Sharepoint sites. Here is my send_mail function used from powershell:

function send_email ([string]$emailTo, [string]$emailFrom, 
			[string]$emailSubject, [string]$emailBody)
    $emailSmtpServer = "smtp.company.com"
    $emailSmtpServerPort = "25"
    $emailSmtpUser = "COMPANY\johndoe"
    $emailSmtpPass = "johndoe"

    $emailMessage = New-Object System.Net.Mail.MailMessage
			( $emailFrom , $emailTo )
    $emailMessage.IsBodyHtml = $true
    $emailMessage.Subject = $emailSubject
    $emailMessage.Body = $emailBody 

    $SMTPClient = New-Object System.Net.Mail.SmtpClient
			( $emailSmtpServer , $emailSmtpServerPort )
    $SMTPClient.Credentials = New-Object System.Net.NetworkCredential
			( $emailSmtpUser , $emailSmtpPass );

    $SMTPClient.Send( $emailMessage )

Off course, I need to rework the security here and not hard code the password, but for testing purpose, this worked smoothly yesterday.

Happy emailing 🙂