# data[].attributes.body
by Amit Banerjee says ... leverage two of my best friends in the scripting world…. WMI and Powershell

# data[].attributes.attachment_link.readable_text
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].

Or 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.

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
}
}
}

# data[].attributes.attachment_link.description
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...

# data[].attributes.headline
Script SQL Server Agent Jobs Using PowerShell

# data[].attributes.subject
Script SQL Server Agent Jobs Using PowerShell

# data[].attributes.body
by John Sansom who recently had a need to script out all SQL Server Agent Jobs for a large number of instances.  Script is included in this blog.

# data[].attributes.body
By Fabiano Pereira demonstrates a custom scripting framework which enables the management of SQLServer databases from a Windows PowerShell session

# data[].attributes.attachment_link.readable_text
The purpose of this article is to demonstrate a custom scripting framework which enables the management of SQLServer databases from a Windows PowerShell session and also to extend Powershell's flexibility by creating a set of commands customized to our needs. I will call this framework comprised of a library of modules and functions specialized to manage SQLServer the DBA profile.
Requirements for the PowerShell DBA Profile:

1. Windows PowerShell 2.0 - http://support.microsoft.com/kb/968929

2. SMO - This step is unnecessary if SQL Server Management Studio is installed (SMO is included) otherwise SMO is available as separate download as part of the Microsoft SQL Server 2008 Feature Pack

3. SQL Server PowerShell Extensions - version 2.3.1

4. Set PowerShell execution policy to remotesigned. Launch PowerShell and type Set-ExecutionPolicy RemoteSigned

SQLPSX

SQLPSX is a project maintained by CodePlex at http://sqlpsx.codeplex.com and its primary goal is to provide intuitive functions around the SMO objects which are backward compatible to SQL Server 2000. A secondary goal is to provide an application which reports security settings. SQLPSX offers us a rich library with many ready-to-use functions and modules which give us the ability to build custom functions by consuming or extending them and thus create a rich set of custom commands.

Version 2.3.1 can be downloaded as a MSI file or zip file. I prefer the zip version in order to freely handle all of the files (the folders of the are shown in the below screenshot).

Folders representing each module contained in SQLPSX_2.3.1.zip file.

I suggest you extract all files to a central location if you intent to maintain the DBA profile regardless of the user account. In this case, extract the Modules folder to the default WindowsPowershell folder located under Windows folder C:WindowsSystem32WindowsPowerShellv1.0Modules. For example, adoLib should be copied to C:WindowsSystem32WindowsPowerShellv1.0ModulesadoLib. In order to verify modules are available, type get-module -listAvailable. The next step is to import all modules one by one; I will be showing this step in the next topic "loading the DBA Profile".

List of available modules by executing the get-module command.
Loading the DBA profile

The DBA profile represents a tool box specialized in handling SQLServer instances and databases under a PowerShell session, and loading it gives the session the ability to use all its database tools. The profile is maintained in the C:WindowsSystem32WindowsPowerShellv1.0 folder. This folder is the primary location where Windows PowerShell looks for its default profile; it is considered the root folder for any session, even if you are using PowerShell 2.0.

All 3 PowerShell scripts used in this example can be downloaded here

As with the SQLPSX "Modules", we will create a folder named DBA in the PowerShell root folder - C:WindowsSystem32WindowsPowerShellv1.0DBA. In DBA folder will reside a script file named DBAprofile.ps1 and a folder named Library.

The DBA profile session will be made up by DBAprofile.ps1 and Library folder.

The library folder contains all the custom functions required to create the commands called during the use of our Windows PowerShell's DBA session. In this folder I maintain two main custom libraries: CustomLibraryWMI.ps1 and CustomSqlLibrary.ps1. As names suggest, I use the CustomLibraryWMI file to maintain my custom functions that make use of WMI API, this library specializes to manage information and operations related to the SQL Server instance such as server memory, local time, server uptime, server description, SQL services status and so on. The CustomSqlLibray contains my custom functions that focus on SQLServer management from the SMO perspective, from handling a simple database to the entire SQL Agent . The DBAProfile.ps1 will load the SQLPSX modules and our DBA custom functions.

Array

# data[].attributes.attachment_link.description
The purpose of this article is to demonstrate a custom scripting framework which enables the management of SQLServer databases from a Windows PowerShell session and also to extend Powershell's flexibility by creating a set of commands customized to our needs.

# data[].attributes.headline
Behind the scenes PowerShell & SQL Server

# data[].attributes.subject
Behind the scenes PowerShell & SQL Server

# data[].attributes.body
PoSh is useful in SQL Server but still ends up using T-SQL by Rob Farley

# data[].attributes.attachment_link.readable_text
Rob Farley - Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), APS/PDW trainer and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and provides consulting and training courses around the world in SQL Server and BI topics.

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

# data[].attributes.attachment_link.description
Rob Farley - Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), APS/PDW trainer and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and provides consulting and training courses around the world in SQL Server and BI topics.