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

SQL Saturday 76: The WiFi gods were happy


 

I just concluded my presentation on “Debugging the Deadlock for the Scheduler” at SQL Saturday 76 over a Lync meeting. The WiFi and Lync gods decided to be benevolent today and let me present without an untoward incident! As always it was fun to demonstrate the use of a debugger…Winking smileThe attempt of the presentation was to demonstrate what can be done proactively and reactively when dealing with a deadlocked schedulers condition.

A special thank you is due to all the sponsors without whom the event today wouldn’t have been possible: Microsoft New Zealand, RedGate, SQL Pass, New Zealand MS Communities and SQL Services!

The slide deck used for presentation today is available on Slide Share and is embedded below:

The demo files used are available at SkyDrive in the file SQLSat76_Demo.zip The walkthrough for the debugging that I explained along with the use of the Powershell script to automatically collect DMV outputs based on messages in the Windows Application event log are available using the posts available under the walkthroughs link below as well as the zip file mentioned above.

And last but not the least, a big Thank You to Dave [Twitter | Blog] for helping arrange all the logistics so that I could present and also for giving me this opportunity!

Resources:

Whitepaper: How To Diagnose and Correct Errors 17883, 17884, 17887, and 17888
http://msdn.microsoft.com/en-us/library/cc917684.aspx

Walkthroughs
https://troubleshootingsql.com/tag/deadlocked-schedulers/

WinDBG
http://msdn.microsoft.com/en-us/windows/hardware/gg463009

Microsoft Public Symbol Server
http://msdl.microsoft.com/download/symbols

KB: New concurrency and scheduling diagnostics have been added to SQL Server
http://support.microsoft.com/kb/319892/en-us

Cheers!

Oh Image: Art thou valid?


Last week I had written a post on how to determine corrupt binary streams of images stored in a database: The case of the corrupt images. The main reason of that post was to determine the corrupt binary streams in the database before the SSRS reports accessing the inconsistent data while rendering reports and turning green in the face! So if you don’t have control over what is getting stuffed in the database (which is seriously a bad situation to be in), then here is another option for you if you are ready to use Custom Assemblies or Custom Code. I have decided to use a Custom Assembly for illustrating what I mean in this blog post. Ideally the image validation check should happen on the front end application/website from where the image is being uploaded into the database.

I am going to use a simple report which pulls a set of images from a database and displays them. I have two versions of the same report. The first report “Image_NoVerify” doesn’t use the custom assembly where as the “Image” report uses the fnImageValid function defined in VerifyImage.dll to determine the validity of the image.

The source code for the custom assembly is shown below:

/*

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.

*/

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Drawing;

using System.IO;

using System.Security.Permissions;

namespace VerifyImage

{

public static class Class1

{

public static String fnImageValid(byte[] vImgStream)

{

// The file name to store the image at

String vFilePath = @"C:\1.jpg";

// File system permissions asserted to prevent security exceptions while saving the file

FileIOPermission filePerm = new FileIOPermission(System.Security.Permissions.PermissionState.Unrestricted);

filePerm.Assert();

// Check for nulls

if (vImgStream == null)

return "NULL image";

// Store the image on the filesystem

FileStream fs = new FileStream(vFilePath, FileMode.Create, FileAccess.Write);

try

{

// Zero KB image detection

if (vImgStream.GetUpperBound(0) > 0)

{

fs.Write(vImgStream, 0, vImgStream.GetUpperBound(0));

}

else

{

fs.Close();

return "ZERO KB Image detected";

}

}

catch (Exception)

{

fs.Close();

return "Unable to write image to disk";

}

fs.Close();

// Check image saved on filesystem for validity

try

{

using (FileStream fsRead = new FileStream(vFilePath, FileMode.Open, FileAccess.Read, FileShare.Read))

{

using (StreamReader sr = new StreamReader(fsRead))

{

try

{

try

{

// Try and check if the seek is possible. If it fails, then we have problems!

fsRead.Position = 0;

fsRead.Seek(0, SeekOrigin.Begin);

}

catch { }

using (Image img = Image.FromStream(fsRead))

{

// IF there are no errors, then report success

img.Dispose();

fsRead.Close();

return "Valid Image";

}

}

catch (Exception)

{

fsRead.Close();

return "Corrupt Image Detected";

}

}

}

}

catch (Exception Ex)

{

return "Exception Caught: " + Ex.Message.ToString() + ":" + Ex.StackTrace.ToString();

}

}

}

}

You would also need to modify the rssrvpolicy.config file to ensure that the assembly doesn’t fail with permission errors when it is used while executing the report. Note that the values mentioned below is to get this working. You might need to make additional changes for making the security permissions more granular to satisfy the security requirements in your environment.

 <CodeGroup class=”UnionCodeGroup”
version=”2″
PermissionSetName=”FullTrust”
Name=”MyCodeGroup”
Description=”Code group for VerifyImage extension”>
<IMembershipCondition class=”UrlMembershipCondition”
version=”2″
Url=”C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin\VerifyImage.dll”
/>
</CodeGroup>

imageWhen the Image report is saved as a PDF file, you will see the following output for a valid image. The text box uses the following expressionimage to display the output shown in the screen shot on the left:

=Is Image Valid: ” + Fields!SNO.Value.ToString() + VerifyImage.Class1.fnImageValid(Fields!Document.Value).ToString()

If the image is inconsistent, then you will see one of the following outputs shown in the screenshots on the right. You can extend this further by displaying the image only if the function fnImageValid returns true. If the function returns a false, then you can use a stock image to let the user know that image is corrupt. This way you can prevent rendering failures.

imageAs you can see from the data retrieved from the ExecutionLogStorage table in the ReportServer database that the image verification check at times can increase the execution time of the report. Since I am using only a few images to create the report, the execution times in the table mentioned below are comparable. However, if the images are larger or the number of images are more, then the report execution time will increase due to additional validation check processing which wouldn’t necessarily be a linear increase in execution time.

Note that invalid image streams when used in SQL Server 2008 Reporting Services and lower releases results in exceptions being thrown by Reporting Services and causes mini-dumps to be generated.

The DLL was placed in the following locations for the above example to work:

C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin
C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies

I had achieved this using a Post Build event which would automatically copy the latest version of the DLL to the two above mentioned folders. This beats having to manually copy paste the DLL every time I build it.

image

References:

Custom Code and Assembly References in Expressions in Report Designer (SSRS)
http://technet.microsoft.com/en-us/library/ms159238.aspx

How to use custom assemblies or embedded code in Reporting Services
http://support.microsoft.com/kb/920769