Azure Storage for SQL Server

imageTo deploy a SQL Server instance on an Azure Virtual Machine, you will require the use of the Azure Storage service for your storage requirements. There are a few nuances of the Azure storage which requires prior knowledge so that your SQL Server instance runs without any glitches.

Before I start babbling about IOPs, data disks and regions, let’s first get a quick glimpse at what Azure storage offers. Azure storage offers blobs, tables and queues. The data disks that your database files would use are stored as blobs in Azure. One or more data disks are grouped into a single Azure Storage container. It is recommended that you store of all your data disks for a particular virtual machine in a single container. Each storage account has a limit of 500TB!

 

Here is the Cheat Sheet that we had shared during our session at the Microsoft India Teched 2014:

A maximum of 20,000 IOPs per storage account – Each storage account has a limit of 20k IOPs. It is recommended that your mission or business critical or virtual machines with high storage throughput requirements have their data disks residing in a single storage account. Additionally, this storage account should not be shared with another virtual machine or service. For the Standard tier, do not place more than 40 highly used VHDs in a storage account

• A maximum of 500 IOPs for each data disk – Each persistent data disk supports a maximum of 500 IOPs for the Standard Tier. It is advisable to

64-KB allocation unit size for data and log files as well as tempdb

• Use Storage Spaces on Windows Server 2012 and above – This allows you to group your data disks and leverage throughput greater than 500IOPs for the storage pool. For Windows 2008 R2 or earlier, you can use dynamic disks (OS striped volumes) and the stripe size is always 64 KB.

Do not store data on the temporary drive unless for tempdb and buffer pool extension on SSD drives (D-series VMs)

Separate data disks for data and log files – It is very important to determine your storage throughput requirements. As a best practice, having separate storage pools or data disks for your data and log files could go a long way in ensuring that you maintain optimal performance.

Caching policy = NONE – This needs to be set for all the data disks being used by the SQL Server instance

Backup to BLOB storage – When performing backups for SQL Server running in Azure virtual machines, you can use SQL Server Backup to URL. This feature is available starting with SQL Server SP1 CU2 and recommended for backing up to the attached data disks. Prior to SQL Server 2012, you can use SQL Server Backup to Azure Tool. This tool can help to increase backup throughput using multiple backup stripe targets.

Disable GEO-replication on storage account – It is advisable to use a locally redundant storage account for your SQL Server instance. The other benefit that you would get from a locally redundant storage is that the ingress and outgress limits (data moving in and out of the storage account) are higher.

In the next part of this blog post, I am going to use PowerShell magic to determine if the SQL Server instance that you have configured is following some of the best practices mentioned above.

Checking for geo-replication

The following PowerShell script used the Get-AzureStorageAccount cmdlet to get details of the storage account which hosts the virtual disks for the virtual machine. The script and the output is available below.

$StorageAccount = Get-AzureStorageAccount -StorageAccountName "<storage account name>"
if ($StorageAccount.GeoReplicationEnabled -eq $false)
{
    Write-Host "PASSED: Geo-replication is DISABLED" -ForegroundColor Green
}
else
{
   Write-Host "ISSUE: Geo-replication is ENABLED!" -ForegroundColor Green
}

image

Checking the caching policy

The PowerShell script below determines if the caching policy is enabled for the data disks using the Get-AzureVM cmdlet. This cmdlet fetches information about the virtual machine which is used to get the list of the data disks using the Get-AzureDataDisk cmdlet.

$DataDisks = Get-AzureVM -ServiceName "<service name>" -Name "<VM name>" | Get-AzureDataDisk
foreach ($disk in $DataDisks)
{
    if ($disk.HostCaching -eq "None")
    {
        Write-Host "PASSED: Disk caching is DISABLED for data disk: " $disk.DiskName "(" $disk.MediaLink ")" -ForegroundColor Green
    }
    else
    {
        Write-Host "ISSUE: Disk caching is ENABLED for data disk " $disk.DiskName "(" $disk.MediaLink ")" -ForegroundColor Red
    }
}

 

I will get a few more PowerShell scripts uploaded for automating the best practices checks for Azure VMs running SQL Server workloads.

References

Azure Storage
http://azure.microsoft.com/en-us/services/storage/

Azure Subscription and Service Limits, Quotas, and Constraints
http://azure.microsoft.com/en-us/documentation/articles/azure-subscription-service-limits/#storagelimits

* This blog post has been written based on the service details available on 9th November, 2014.

A little bit of POSH for document conversion

This is a completely non-SQL Server post. I had to recently convert a large number of word documents into Web Archive (.MHT) format. I would not mind doing that for one or two documents but when I have over 50 documents to perform this exercise on, it could get cumbersome and monotonous! This is where PowerShell came to the rescue.

The most common example available on the web is to convert Word documents to PDF. What I needed for the work that I was doing was a way to convert the Word documents to the Web Archive format. After a few Bing searches, I was able to determine that the Web Archive format enumeration number was 9. The following link has information about all the enumeration values: http://msdn.microsoft.com/en-us/library/office/bb238158(v=office.12).aspx

The PowerShell script below allows you to traverse a folder, pick all the word documents in the folders recursively and then convert each of those word documents in a .MHT file with the same name in the same location.

The script can be downloaded from OneDrive also.


<#

#################################################################################
    
Script Name: ConvertToWord                        
    Author: Amit Banerjee                            
    Date: April 28, 2014                            
    
Description:                                 
    This script takes a folder as an input and then converts the docx files present in the folder to web archive documents        
#################################################################################

This Sample Code is provided for the purpose of illustration only and is not 
intended to be used in a production environment. THIS SAMPLE CODE AND ANY 
RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER 
EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF 
MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. We grant You a 
nonexclusive, royalty-free right to use and modify the Sample Code and to 
reproduce and distribute the object code form of the Sample Code, provided that 
You agree: (i) to not use Our name, logo, or trademarks to market Your software 
product in which the Sample Code is embedded; (ii) to include a valid copyright 
notice on Your software product in which the Sample Code is embedded; and (iii) 
to indemnify, hold harmless, and defend Us and Our suppliers from and against 
any claims or lawsuits, including attorneys fees, that arise or result from the 
use or distribution of the Sample Code.

    The enumeration for the various document types that you can save as from Microsoft Word when you use the SaveAs option
    Const wdFormatDocument                    =  0
    Const wdFormatDocument97                  =  0
    Const wdFormatDocumentDefault             = 16
    Const wdFormatDOSText                     =  4
    Const wdFormatDOSTextLineBreaks           =  5
    Const wdFormatEncodedText                 =  7
    Const wdFormatFilteredHTML                = 10
    Const wdFormatFlatXML                     = 19
    Const wdFormatFlatXMLMacroEnabled         = 20
    Const wdFormatFlatXMLTemplate             = 21
    Const wdFormatFlatXMLTemplateMacroEnabled = 22
    Const wdFormatHTML                        =  8
    Const wdFormatPDF                         = 17
    Const wdFormatRTF                         =  6
    Const wdFormatTemplate                    =  1
    Const wdFormatTemplate97                  =  1
    Const wdFormatText                        =  2
    Const wdFormatTextLineBreaks              =  3
    Const wdFormatUnicodeText                 =  7
    Const wdFormatWebArchive                  =  9
    Const wdFormatXML                         = 11
    Const wdFormatXMLDocument                 = 12
    Const wdFormatXMLDocumentMacroEnabled     = 13
    Const wdFormatXMLTemplate                 = 14
    Const wdFormatXMLTemplateMacroEnabled     = 15
    Const wdFormatXPS                         = 18

#>

# Replace with the correct folder path
# Remove the -recurse option if you only want to convert the documents in the first level folders
# Retrieve the list of documents
$Files = Get-ChildItem "C:\Windows\*.docx" -recurse 

foreach ($File in $Files)
{
    # Create the name of the new document
    $Name = $File.FullName.replace(“docx”,”mht”)
    
    if (Test-Path $Name)
    {
        # Check if the file already exists
        # If it does then do not do anything
        Write-Host "Skipping conversion for " $Name  
    }
    else 
    {
        # Save the file as a web archive if it does not exist
        Write-Host "Creating file " $Name
        ConvertToMHT $File.FullName
    }
}

# Function to convert the file
function ConvertToMHT ($FileName)
{        
    # Create a word document object
    $Word=NEW-OBJECT –COMOBJECT WORD.APPLICATION
    # Open the word document
    $Doc=$Word.Documents.Open($FileName)
            
    # Replace with appropriate document format type using the enumeration provided above in the comments
    # Save the document in the required format in the same location
    [ref]$SaveFormat = "System.Object" -as [type]
    $Doc.saveas([ref] (($FileName).replace(“docx”,”mht”)),  [ref]9)
    # Quit word after closing the document
    $Doc.close()
    $Word.Application.Quit()
}

Reference:
http://blogs.technet.com/b/heyscriptingguy/archive/2013/03/24/weekend-scripter-convert-word-documents-to-pdf-files-with-powershell.aspx

Powershell for the perfmon files

Recently, I was working on a requirement to convert a number of BLG files into CSV and then changing the CSV files into Excel files.

So, the script below does the following:

1. Picks the BLG files from a folder and then creates a CSV file from the BLG file using RELOG.EXE

2. Then the same CSV file is converted to a XLS file

As always, this is a Powershell script which can be adapted in any way possible. Happy perfmon analysis!

The script can be downloaded from OneDrive as well.


#################################################################################
#                                        #
#                                        #
#                                        #
#                                        #
#    Script Name: Relog                            #
#    Author: Amit Banerjee                            #
#    Date: May 10, 2014                            #
#    Description:                                 #
#    The script uses relog to create CSV files from BLG files         #
#    It then converts the CSV files to XLS files                 #
#                                        #
#                                        #
#                                        #
#                                        #
#                                        #
#################################################################################

# This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits, including attorneys fees, that arise or result from the use or distribution of the Sample Code.

# Enumerates BLG files on disk and starts running relog on them
$path = "C:\PerfmonFiles\" # Replace with correct path
$files = Get-ChildItem $path -Filter *.blg
foreach ($file in $files)
{
    # Create the CSV filename
    $filename = $file.FullName.Split(".")[0] + ".csv"
    # Run relog with the correct arguments
    $AllArgs =  @($file.FullName,  '-f', 'csv', '-o',  $filename)
    & 'relog.exe' $AllArgs
}

# Enumerates CSV files on disk and starts converting them to Excel
$files = Get-ChildItem $path -Filter *.csv 
foreach ($file in $files)
{
    # Launch Excel
    $xls = new-object -comobject excel.application
    $xls.visible = $true
    # Open the CSV file
    $Workbook = $xls.workbooks.open($file.FullName)
    $Worksheets = $Workbooks.worksheets
    $filename = $file.FullName.Split(".")[0] + ".xls"
    # Save the file as Excel
    # Depending on your compatibility settings you might have to accept a prompt to save the file
    $Workbook.SaveAs($filename,1)
    $Workbook.Saved = $True
    # Close Excel
    $xls.Quit()
    # Delete the CSV file
    Remove-Item $file.FullName
}

T-SQL Tuesday #50: Database Role Membership

imageFirst of all, a Happy New Year to everyone!

This month’s T-SQL Tuesday topic on Automation provides a perfect opportunity for me to blog about an automation that can be achieved around database roles and it’s members.

A big thank you to Hemanth [T | B] for hosting this year’s first T-SQL Tuesday and to Adam Machanic [B|T] as his initiative just reached a milestone! 50 months filled with awesome SQL related blog posts on a Tuesday!

The ask was to create a database role matrix for each and every user database on the SQL Server instance. Now the catch here was to find out memberships in custom database roles as well.

So here is how to go about this.

First task would be to identify each and every fixed database role that a particular database user is part of. This is a fairly simple task as there are system catalogs to fetch this information.

The next task is to find out each and every custom database role that exists in each user database. This can be achieved using the T-SQL script below.

<span style="font-size: small;">
/*
# This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code.
# Author: Amit Banerjee
# Purpose: T-SQL script to fetch all the custom roles in a database
*/
DECLARE @ctr int = 1, @maxctr int = 1, @dbname sysname, @rsql nvarchar(4000)

CREATE TABLE #tblCustomRoles (rolename varchar(255))

SELECT identity(int,1,1) as databaseid, name
INTO #dbs
FROM sys.databases
WHERE database_id NOT IN (1,2,3,4)
AND state_desc = 'ONLINE'

SELECT @maxctr = COUNT(*) FROM #dbs
WHILE @ctr <= @maxctr
BEGIN
SELECT @dbname = name FROM #dbs WHERE databaseid = @ctr
SET @rsql = 'SELECT name FROM ' + @dbname +'.sys.database_principals where type_desc = ''DATABASE_ROLE'' and is_fixed_role = 0 and name <> ''public'''
INSERT INTO #tblCustomRoles
EXEC sp_executesql @rsql
SET @ctr += 1
END

SELECT DISTINCT ', CASE IS_MEMBER ('''+rolename+''') WHEN 1 THEN ''YES'' ELSE ''NO'' END as ' + quotename(rolename) as sqlstring
FROM #tblCustomRoles

DROP TABLE #tblCustomRoles
DROP TABLE #dbs


Now that you have list of custom database roles, you need to create a T-SQL query with the custom database role list and the fixed database role list. This query when executed against each user database will give you the database role matrix that you are looking for.

And this is where an automation scripter’s best friend, Powershell steps in. The script below:

1. Fetches the custom database roles from each database

2. Creates the T-SQL query for fetching the database role members

3. Executes the query against each database except for master, msdb, model and tempdb

4. Appends the output from each database into a CSV file


# This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code.
# Author: Amit Banerjee
# Purpose: Powershell script to fetch role membership for each database user in all databases in a single SQL Server instance
# Usage: .\GetPerms.ps1 -vFileName "<Output file name with fully qualified path>" -vServer "<SQL Server Instance Name>" -vScriptFile "<Location of GetCustomRoles.sql>"

Param ([string] $vFileName, [string] $vServer, [string] $vScriptFile)

# Declaring variables
$VCustomRoles = ""

# Fetch the names of the databases
$vDatabases = Invoke-Sqlcmd -ServerInstance $vServer -Database "master" -Query "select name from sys.databases where database_id not in (1,2,3,4) and state_desc = 'ONLINE'"

# Find non-system database roles
$vRoles = Invoke-Sqlcmd -ServerInstance $vServer -Database "master" -InputFile $vScriptFile

# Construct the SQL query to be executed
$vQuery = "SELECT
db_name() as DatabaseName
, prin.name as DBUserName
, srvprin.name as LoginName
, srvprin.create_date
, CASE IS_MEMBER ('db_owner') WHEN 1 THEN 'YES' ELSE 'NO' END as [db_owner]
, CASE IS_MEMBER ('db_accessadmin') WHEN 1 THEN 'YES' ELSE 'NO' END as [db_accessadmin]
, CASE IS_MEMBER ('db_securityadmin')  WHEN 1 THEN 'YES' ELSE 'NO' END as [db_securityadmin]
, CASE IS_MEMBER ('db_ddladmin')  WHEN 1 THEN 'YES' ELSE 'NO' END as [db_ddladmin]
, CASE IS_MEMBER ('db_backupoperator')  WHEN 1 THEN 'YES' ELSE 'NO' END as [db_backupoperator]
, CASE IS_MEMBER ('db_datareader')  WHEN 1 THEN 'YES' ELSE 'NO' END as [db_datareader]
, CASE IS_MEMBER ('db_datawriter')  WHEN 1 THEN 'YES' ELSE 'NO' END as [db_datawriter]
, CASE IS_MEMBER ('db_denydatareader')  WHEN 1 THEN 'YES' ELSE 'NO' END as [db_denydatareader]
, CASE IS_MEMBER ('db_denydatawriter')  WHEN 1 THEN 'YES' ELSE 'NO' END as [db_denydatawriter]
, CASE IS_MEMBER ('public')  WHEN 1 THEN 'YES' ELSE 'NO' END as [public]
"

# Foreach loop to identify each custom role in all the databases on the instance
foreach ($vRole in $vRoles)
{
$vQuery = $vQuery + $vRole.sqlstring + "`n"
}

# Complete the T-SQL query to be executed
$vQuery = $vQuery + "FROM sys.database_role_members mem
INNER JOIN sys.database_principals prin on mem.member_principal_id = prin.principal_id
INNER JOIN sys.database_principals rol on rol.principal_id = mem.role_principal_id
LEFT OUTER JOIN sys.server_principals srvprin on srvprin.sid = prin.sid
WHERE prin.is_fixed_role = 0 and srvprin.type in ('U','S')"

# Foreach loop to fetch the role membership of each user and append to a CSV file
foreach ($db in $vDatabases)
{
"Fetching database role matrix for database: " + $db.name
# NoTypeInformation prevents the #TYPE System.Data.DataRow from being the first row in the CSV file
# Use Invoke-SQLCMD to get the database role matrix for each database and append it to an output CSV file
Invoke-Sqlcmd -ServerInstance $vServer -Database $db.name -Query $vQuery | Export-Csv -Path $vFilename -Append -NoClobber -Encoding UTF8 -ErrorAction Stop -NoTypeInformation
}

The CSV output file shows a neat matrix as seen in the screenshot below:

image

Example command:

.\GetPerms.ps1 -vFilename G:\Perms.CSV -vServer MYSERVER -vScriptFile “G:\GetCustomRoles.sql”

Happy Automation!

CPU Hyperthreading: Is it ON or OFF

Sometime back a customer had asked a question on how to find out the different between hyperthreaded CPUs and multi-core CPUs using Powershell. There are multiple utilities available on the web which provide this information readily along with code samples i.e. if you have an affinity for coding.

However, my requirement was to get this information without the use of an executable. Such an endeavor seemed worthwhile for me since such scripts can be used for auditing and inventory related purposes.

The powershell script below can help you identify if hyperthreading is enabled on the server or not and gives you information about the number of logical and physical processors on the server/machine. The powershell script below makes use of Win32_Processor WMI class. The script can be easily adapted to using VBScript as well.

Continue reading

What IP are you listening on SQL

This is probably the easiest question to answer for any SQL Server DBA. And this is one of those scenarios where the adage “There are multiple ways to skin a cat” holds true. You could look into the SQL Server Errorlog and quickly look up the phrase “is listening on” and find lines of the following nature:

Server is listening on [ ‘any’ <ipv6> 1433].
Server is listening on [ ‘any’ <ipv4> 1433].

imageOr you could even use SQL Server Configuration Manager (SQL Server 2005 and above) to figure out the TCP/IP settings of the SQL instance. Now there is a catch here! If you see the screenshot, you will notice that the Listen All property is set to YES. This means that the SQL Server instance will listen on all the IP Addresses that the server/machine is configured to listen on. If this property was set to NO, then you moved over the IP Addresses tab (see screenshot), you would have see one of the IPs listed there which was Active and Enabled. (IP Address hidden in screenshot). All this seems simple enough, right? Then why am I taking the time to mention all these facts that you are already know and are probably questioning my sanity about writing such a post. Well…. If you had a let’s say a few hundred SQL Server instances from which you needed to fetch this information, then I can imagine how much you would relish the manual task of fetch this information. Both the options mentioned above, Errorlog and Configuration Manager, will not help you in this endeavor. Which is why I decided to leverage two of my best friends in the scripting world…. WMI and Powershell.

image

Using the namespace for SQL Server 2008: root\Microsoft\SqlServer\ComputerManagement10 and the class ServerNetworkProtocolProperty, you can fetch the TCP/IP settings for any SQL instance. The script illustrated below can be used to fetch the IP Address(es) that one SQL instance is listening on. You could write an outer loop to fetch the SQL Server instance names from a database or XML file and then use the script below to get the relevant data. If you want to SQL Server 2005 specific code, then the namespace to be used is root\Microsoft\SqlServer\ComputerManagement. Unfortunately for SQL Server 2000 and below, there are no WMI namespaces available to retrieve this information. In case you have SQL Server 2000 instances in your shop, then you would have to write Powershell code to fetch this information from the instance specific registry keys or using Select-String cmdlet to traverse the SQL Errorlog to retrieve this information. Note that the Errorlog approach has caveats in environments where the Errorlog is regularly recycled.

Addition: January 30, 2012: If you have specific IPs that a SQL Server failover cluster is listening on with the Listen All property set to TRUE, then the script can be modified such that only the Active and Enabled IP Addresses are reported by the script below. The part of the script which reports the IP Config output for the server can be omitted.

# This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code.
# Author: Amit Banerjee
# Description: Powershell code to fetch the IP Addresses that the SQL instance is listening on.
# Notes:
# root\Microsoft\SqlServer\ComputerManagement10 - This namespace is applicable for SQL Server 2008 and above.
# For SQL Server 2005 the namespace is root\Microsoft\SqlServer\ComputerManagement
# There is no equivalent WMI namespace for SQL Server 2000 instance

# Provide the computer name that you want to query
$vComputerName = "."
# Provide the SQL instance name that you want the information for
# MSSQLSERVER for default instance
$vInstanceName = "MSSQLSERVER"

Write-Host "IP Address(es) that the SQL instance " $vComputerName "\" $vInstanceName " is listening on are listed below: "

$vListenAll = 0
$vTCPProps = get-WMIObject ServerNetworkProtocolProperty -ComputerName $vComputerName -NameSpace "root\Microsoft\SqlServer\ComputerManagement10" | Where-Object {$_.PropertyName  -eq "ListenOnAllIPs" -and $_.InstanceName -eq $vInstanceName}
foreach ($vTCPProp in $vTCPProps)
{
$vListenAll = $vTCPProp.PropertyNumVal
}

if($vListenAll -eq 1)
{
Write-Host "Is instance configured to listen on All IPs (Listen All property): TRUE"
# Get Networking Adapter Configuration
$vIPconfig = Get-WmiObject Win32_NetworkAdapterConfiguration -ComputerName $vComputerName

# Iterate and get IP address on which the SQL is listening
foreach ($vIP in $vIPconfig)
{
if ($vIP.IPaddress)
{
foreach ($vAddr in $vIP.Ipaddress)
{
$vAddr
}
}
}
}
else
{
# If SQL is configured to listen for specific IP addresses (eg. SQL clusters), then this else block will fetch those IP Addresses
# The Sort-Object ensures that the for-each loop below doesn't break while reporting the active and enabled IPs
$vIPProps = get-WMIObject ServerNetworkProtocolProperty -ComputerName $vComputerName -NameSpace "root\Microsoft\SqlServer\ComputerManagement10" | Where-Object {$_.InstanceName -eq $vInstanceName -and $_.ProtocolName  -eq "Tcp"} | Sort-Object IPAddressName,PropertyName
$vActive = 0
$vEnabled = 0

Write-Host "Is instance configured to listen on All IPs (Listen All property): FALSE"

foreach ($vIPProp in $vIPProps)
{
# Check if the IP is active
if ($vIPProp.Name -ne "IPAll" -and ($vIPProp.PropertyName -eq "Active"))
{
$vActive =  $vIPProp.PropertyNumVal
}
# Check if the IP is enabled
if ($vIPProp.Name -ne "IPAll" -and ($vIPProp.PropertyName -eq "Enabled"))
{
$vEnabled = $vIPProp.PropertyNumVal
}
# Report the IP if active and enabled
if ($vIPProp.Name -ne "IPAll" -and $vIPProp.PropertyName -eq "IPAddress" -and $vEnabled -eq 1 -and $vActive -eq 1)
{
# Get the IP addresses that SQL is configured to listen on
$vTCPProp.PropertyStrVal
}
}
}
Technorati Tags: ,,

Deadlocked Schedulers and event notifications with a bit of Powershell

I had written 2 posts recently about Deadlocked Schedulers debugging (Part 1 | Part 2) which basically walked through a 17784 and 17888 condition and the steps that you could take to debug such an issue. A quote from the both these articles:

So if you have a monitoring tool that is capturing the output of queries being executed by the active SQL Server sessions, then you can go back to your monitoring repository and dig up the queries which these threads were executing. If such data is not available from the time of the issue, more often that not you will have to end up collecting additional data for the next problem occurrence!

You will need to collect additional data if there wasn’t a monitoring software collecting the same to determine the set of queries and sequence of events leading upto the issue. CSS Engineers can dig into the dump using the private symbols and probably provide you with additional information which can help you along the right direction. But alas, even they do not have magic wands if you cannot provide them with additional information like blocking output/profiler traces for 17884 and 17888 conditions caused by excessive blocking or parallelism.

SQL Server 2008 Service Pack 2 provided an update which made the FailoverAnalysis.sql script available again. This was available in SQL Server 2005 and now is available for SQL Server 2008 as well. This is particularly useful for SQL Server Failover Clusters where you can configure SQLDumper to generate a dump before the failover occurs and also set the SqlDMVScriptTimeout property to get an output of the DMVs used in the script before a failover happens.

image

You can refer “Cluster failovers and the Sqldumper.exe utility” section in the KB917825 for more details on the above mentioned feature. So what am I doing writing this post??

This post is aimed at SQL Server Standalone instances where you cannot benefit from sqldumper. Deadlocked Schedulers are reported with EventIDs 17884 or 17888. I am going to use this to create an scheduled task on a Event Log entry for these two specific EventIDs. I am big fan of automating repetitive tasks and the benefit of such an automation is that you can get additional data from the problem occurrence without having to just rely on a SQL Server mini-dump memory file and be at the mercy of the Windows Debugging tools. For this post, you can say a small good-bye to your favorite debugging tool and read on for some automation in monitoring!!

However, there are some requirements for this kind of indigenous monitoring to work:

1. Dedicated Administrator Connection needs to be enabled for your SQL Server instance(s) – This is required because during a Deadlocked Schedulers condition, you would have run out of worker threads. So, new connection requests cannot be processed unless and until you establish a connection through DAC. If you are planning to use Event Subscription and remote data gathering, then you need Remote DAC enabled for your SQL instance(s) being monitored. Note: Event subscription is a feature available on Windows Server 2008 and above.

2. You need sysadmin privileges for the account that will be used to collect the output of the FailoverAnalysis.sql script.

3. Powershell and SQLPS needs to be available on the server being monitored. If you don’t want to install Powershell components, then the same functionality can be achieved using SMO and VBScript which I have used extensively in the past till I became a fan of POSH awesomeness!! Smile

Now that I have the pre-requisites out of the way, let’s get down to the good part…

The Powershell Script

I had already mentioned that I will be using a Powershell script to get the output of the FailoverAnalysis.sql script for the SQL instance that is encountering the 17884 condition. The piping feature of Powershell really makes it easy to write compact scripts to perform a wide variety of tasks. The script is divided into three parts.

The first part of the script actually uses the script in the section “Adding the SQL Server Snap-ins to Windows PowerShell” of the article “Running SQL Server PowerShell” to initialize the SQLPS snap-in in the session that you will be running the script.

The second part of the script reads the Application event log for the first occurrence of an informational message with the text substring “have not been picked up by a worker thread in the last 60 seconds” from any SQL Server instance as the source.

The third part of the script figures out the SQL Server instance name and connects to it to execute the FailoverAnalysis.sql script. You can modify the output location of the script output below as appropriate for your environment.

Script filename: DeadlockSchedulerMonitor.ps1


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

# Script Name: DeadlockSchedulerMonitor #

# Author: Amit Banerjee #

# Date: 9/1/2011 #

# Description: #

# The script reads the first 17884 (Deadlocked Schedulers) event from the #

# Windows Application Event log and identifies the source. Based on the #

# source name, it connects to the SQL Server instance using a DAC #

# connection and collects the FailoverAnalysis.sql script output. #

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

$vMsg = (Get-Date).Year.ToString() + (Get-Date).Month.ToString() + (Get-Date).Day.ToString() + (Get-Date).Hour.ToString() + (Get-Date).Minute.ToString() + ":Performing SQLPS snap-in initialization`n"

Write-Host $vMsg

# Call the initialization script to load the SQLPS snap-in in the session that you running your script

# Add the SQL Server Provider.

$ErrorActionPreference = "Stop"

$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"

if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")

{

Write-Host "SQL Server Provider for Windows PowerShell is not installed.`n"

}

else

{

$item = Get-ItemProperty $sqlpsreg

$sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)

}

# Set mandatory variables for the SQL Server provider

Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0

Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30

Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false

Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000

# Load the snapins, type data, format data

Push-Location

cd $sqlpsPath

Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue

Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinue

Update-TypeData -PrependPath SQLProvider.Types.ps1xml -ErrorAction SilentlyContinue

update-FormatData -prependpath SQLProvider.Format.ps1xml -ErrorAction SilentlyContinue

Pop-Location

$vMsg = (Get-Date).Year.ToString() + (Get-Date).Month.ToString() + (Get-Date).Day.ToString() + (Get-Date).Hour.ToString() + (Get-Date).Minute.ToString() + ":SQLPS snap-in initialization completed`n"

Write-Host $vMsg

# Local variables to store the event log source and the instance name to which the DAC connection needs to be made

[string] $vSource

[string] $vInstName

[string] $vFileName

$vMsg = (Get-Date).Year.ToString() + (Get-Date).Month.ToString() + (Get-Date).Day.ToString() + (Get-Date).Hour.ToString() + (Get-Date).Minute.ToString() + ":Reading Application Event Log`n"

Write-Host $vMsg

# Get the source which generated the 17884 message

$vSource = Get-EventLog -LogName "Application" -Message "*have not been picked up by a worker thread in the last 60 seconds*" -Source MSSQL* -Newest 1

$vSource = $vSource.Source.ToString()

$vMsg = (Get-Date).Year.ToString() + (Get-Date).Month.ToString() + (Get-Date).Day.ToString() + (Get-Date).Hour.ToString() + (Get-Date).Minute.ToString() + ":Connecting to SQL instance and executing script`n"

Write-Host $vMsg

# Check if it is a default instance or named instance and accordingly collect the output

if ($vSource.Equals("MSSQLSERVER"))

{

# Create the filename

$vFileName = "F:\" + (Get-Date).Year.ToString() + (Get-Date).Month.ToString() + (Get-Date).Day.ToString() + (Get-Date).Hour.ToString() + (Get-Date).Minute.ToString() + (Get-Date).Second.ToString() + "_" + $env:ComputerName.ToString() + "_FailoverAnalysis.OUT"

# Use Invoke-Sqlcmd to get the output of the script

Invoke-Sqlcmd -InputFile "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install\FailoverAnalysis.sql" -ServerInstance $env:ComputerName -DedicatedAdministratorConnection | Out-File -filepath $vFileName

}

else

{

# Construct the SQL Server name to connect to for a named instance

$vInstName = $env:ComputerName + "\" + $vSource.Split("$")[-1]

# Create the filename

$vFileName = "F:\" + (Get-Date).Year.ToString() + (Get-Date).Month.ToString() + (Get-Date).Day.ToString() + (Get-Date).Hour.ToString() + (Get-Date).Minute.ToString() + (Get-Date).Second.ToString() + "_" + $vInstName + "_FailoverAnalysis.OUT"

# Use Invoke-Sqlcmd to get the output of the script

Invoke-Sqlcmd -InputFile "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install\FailoverAnalysis.sql" -ServerInstance $vInstName -DedicatedAdministratorConnection | Out-File -filepath $vFileName

}

$vMsg = (Get-Date).Year.ToString() + (Get-Date).Month.ToString() + (Get-Date).Day.ToString() + (Get-Date).Hour.ToString() + (Get-Date).Minute.ToString() + ":Script execution completed`n"

Write-Host $vMsg

The Task Scheduler Configuration

The next thing that is needed to be done is create Task Scheduler task to execute the Powershell script above when a 17884 condition is reported.

1. Open the Task Scheduler MMC using Start –> Administrative Tools –> Task Scheduler to get the Task Scheduler UI. Right click on Event Viewer Tasks and click on Create Task (see screenshot below). Provide the appropriate settings, description and name. The salient points to keep in mind here are:
a. Use the Change User or Group option to provide an account which has permissions to log into the SQL Server instance(s) to gather the output of the FailoverAnalysis.sql script.
b. You can use the Configure for drop-down box to configure the task for Windows Server 2003 as well.image

2. Next click on the Triggers tab. Click on New, which will open the New Trigger dialog box. Use the Begin the Task drop down box to select On an event option (see screenshot below). Then select the Custom radio button and click on New Event Filter button which will open the New Event Filter dialog box. Provide the options as shown by the highlighted items in the screenshot. In the event sources drop down list, I selected all the SQL Server instances installed on my server. You can choose to leave the default option in case you plan to install more instances on this server. Using the default option can be beneficial if you choose to export this task to other servers as well. Provide the Advanced Settings option in the New Trigger dialog box. I chose to Stop task if it runs more than 30 minutes. Once that is done, you should see new trigger entry in the Trigger tab as On an Event with a custom event filter and the status as enabled.image

3. Under the Actions tab, click on New. Select Start a Program from the Action drop-down box. In the Program/script text box, provide Powershell.exe. In the Add arguments (optional) text box, provide the following: -File “F:\DeadlockSchedulerMonitor.ps1”. Or you can choose to provide a .cmd file path whose contents are “Powershell.exe -File “F:\DeadlockSchedulerMonitor.ps1” > F:\DeadlockSchedulerMonitorLog.txt”. I went with the second option. F: drive root is where my .ps1 script and .cmd file are present. You would need to modify this accordingly depending on where your script is. Once that is done, your action tab should be similar to the screenshot below.

image

4. I used the default settings for the Conditions and Settings tab. You can modify this as appropriate for your environment.

Once the task is created, all you need is for a 17884 condition to occur for the script output to be generated.

Let’s see the configuration in action

image

I simulated a 17884 condition on my SQL Server instance, see if the newly created task gets fired. (see above Application Log snippet). Looking into the F: drive, I find that the following files were created

20119212197_<sql instance name>_FailoverAnalysis.OUT
201192121918_<sql instance name>_FailoverAnalysis.OUT

The reason I have two files is that this is a two NUMA node box, and the deadlock schedulers condition was reported for both nodes. Note that this script reports first occurrence which is reported at 60 seconds since the message text snippet that is used to fetch the event from the Application log specifically looks for the 60 seconds keyword.

NOTE: You can monitor for 17888 events using the following message text instead of the one used above in the script for 17884 script and create a new task for the same. Message text: *appear deadlocked due to a large number of worker threads waiting*

What next?

Well with automation the possibilities are endless… Some of the quick things that come to my mind are:

1. You can create an event subscription and use the Forwarded Events log to track all 17884 from different servers which have SQL Server instances installed on them.
2. You can modify the powershell script to perform additional post processing or send out email notifications to your DBAs for the occurrence of such events.
3. You could even add additional post processing to the Powershell script to perform additional tasks.

If you have SCOM or any other Event log monitoring software, the only thing that you need to do is setup a custom alert for 17884 or 17888 error messages.

Have fun monitoring and customizing further!

Reference:

Understanding and Using PowerShell Support in SQL Server 2008
Windows Server 2008 Event Subscription with Task Scheduling

Disclaimer: This script and the solution provided is “AS IS” and any deployment that you do using a similar logic described requires due-diligence and testing on your part. The testing that I did for this was on Windows Server 2008 R2 & SQL Server 2008 R2.