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: ,,
Advertisements

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.

Getting the Default Data and Log path using PowerShell


I remember a while back being asked about a way to fetch the Default Data and Log path for a SQL Server instance. This information is present in the registry keys and the keys are created only if you modify the default paths to a path other than the SQLDataRoot path using which the SQL instance was installed.

So I thought I would put together a quick post to fetch this information using a Powershell script. The function takes in the instance name as the parameter value. Note that for a default instance, the instance name is MSSQLServer.


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

# Author: Amit Banerjee

# Information: Finds out default data and log file paths for a given SQL Server instance

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

Function fnGetDefaultDBLocation

{

Param ([string] $vInstance)

# Get the registry key associated with the Instance Name

$vRegInst = (Get-ItemProperty -Path HKLM:"SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL" -ErrorAction SilentlyContinue).$vInstance

$vRegPath = "SOFTWARE\Microsoft\Microsoft SQL Server\" + $vRegInst + "\MSSQLServer"

# Get the Data and Log file paths if available

$vDataPath = (Get-ItemProperty -Path HKLM:$vRegPath -ErrorAction SilentlyContinue).DefaultData

$vLogPath = (Get-ItemProperty -Path HKLM:$vRegPath -ErrorAction SilentlyContinue).DefaultLog

# Report the entries found

if ($vDataPath.Length -lt 1)

{

$vRegPath = "SOFTWARE\Microsoft\Microsoft SQL Server\" + $vRegInst + "\Setup"

$vDataPath = (Get-ItemProperty -Path HKLM:$vRegPath -ErrorAction SilentlyContinue).SQLDataRoot + "\Data\"

Write-Host "Default Data Path: " $vDataPath

}

else

{

Write-Host "Default Data Path:" $vDataPath

}

if ($vLogPath.Length -lt 1)

{

$vRegPath = "SOFTWARE\Microsoft\Microsoft SQL Server\" + $vRegInst + "\Setup"

$vDataPath = (Get-ItemProperty -Path HKLM:$vRegPath -ErrorAction SilentlyContinue).SQLDataRoot + "\Data\"

Write-Host "Default Log Path: " $vDataPath

}

else

{

Write-Host "Default Log Path:" $vLogPath

}

}

fnGetDefaultDBLocation "MSSQLServer"