List Out All the SQL servers In domain

HI ALL

In One Of the Group the question was asked that How to list all the Servers in which SQL is installed?

It is genuine query. There are many scenario when you need to Find out how much SQL servers are there in the domain ? many a times if a environment is not properly managed and someone takes charge as DBA first thing asked is how much servers we have ?

On The Internet we will find the many tools to do so. Microsoft uses their tool MAP to  discover the servers it is perfect tool and easy to use but many time third party tool is not allowed in those scenario  Power-shell comes to rescue . Powershell  script perfect solution to find all the SQL servers in domain.I searched on the net for that as i am not a developer and powershell is not my forte . i find the below script .

You need to change the domain here . replace corporate with your domain.

once you run this script in powershell , on the screen you will get the list of all the servers in the  domain and the result of ping status . but actual thing you will find in the location  “C:\Users\username” . you will get the excel file named as Yourdomain_Servers_out  this file will list out all the discovered  sql server with details of Server, Instance, Version, Edition .

param

(

      [string]$domain = “corporate”

)

## ==================================================================================

## Title       : Find All Servers in a Domain With SQL

## Description : Get a listing of all servers in a domain, test the connection

##               then check the registry for MS SQL Server Info.

##                 Output(ServerName, InstanceName, Version and Edition).

##                 Assumes that instances of MS SQL Server can be found under:

##                 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names

## Author      : C.Perry

## Date        : 10/2/2012

## Input       : -domain <fully.qualified.domainname>    

## Output      : List of SQL Server names

## Usage       : PS> . FindAllServersWithSQL.ps1 -domain dev.construction.enet

## Notes       :

## Tag           : SQL Server, test-connection, ping, AD, WMI

## Change log  :

## ==================================================================================

# INITIALIZATION SECTION

cls

# Domain context

#$domain = $null

#$domain=”<domainName>”

# Initialize variables and files

$dom = $null

$ErrorActionPreference = “Continue”

$found = $null

$InstNameskey = “SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names”

$RegInstNameKey = $null

$MSSQLkey = “SOFTWARE\Microsoft\Microsoft SQL Server”

$notfound = $null

#Output file goes into directory you execute from

$outfile = “$domain” + “_Servers_out.csv” 

$reg = $null

$regInstance = $null

$regInstanceData = $null

$regKey = $null

$root = $null

$SetupVersionKey = $null

$SQLServerkey = $null

$sbky = $null

$sub = $null

$type = [Microsoft.Win32.RegistryHive]::LocalMachine

“Server, Instance, Version, Edition” | Out-File $outfile

# Domain Initalization

# create the domain context object

$context = new-object System.DirectoryServices.ActiveDirectory.DirectoryContext(“domain”,$domain)

# get the domain object

$dom = [system.directoryservices.activedirectory.domain]::GetDomain($context)

# Debug line #$dom 

# go to the root of the Domain

$root = $dom.GetDirectoryEntry()

#create the AD Directory Searcher object

$searcher = new-object System.DirectoryServices.DirectorySearcher($root)

#filter for all servers that do not start with “wde”

$filter=”(&(objectClass=Computer)(operatingSystem=Windows Server*) (!cn=wde*))”

$searcher.filter = $filter

# By default, an Active Directory search returns only 1000 items.

# If your domain includes 1001 items, then that last item will not be returned.

# The way to get around that issue is to assign a value to the PageSize property. 

# When you do that, your search script will return (in this case) the first 1,000 items, 

# pause for a split second, then return the next 1,000. 

# This process will continue until all the items meeting the search criteria have been returned.

$searcher.pageSize=1000

$colProplist = “name”

foreach ($j in $colPropList){$searcher.PropertiesToLoad.Add($j)}

# get all matching computers

$colResults = $searcher.FindAll()

# PROCESS Section

# interate through all found servers

foreach ($objResult in $colResults)

{#Begin ForEach

    $objItem = $objResult.Properties

    [string]$Server=$objItem.name

    Try

    {

        IF (test-connection -computername $Server  -count 1 -TimeToLive 4 -erroraction continue -quiet)

        {#IfConnectionFound       

            $found = $Server + ” is pingable”

            #echo $found

            $InstanceNameskey = “SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names”

            $MSSQLkey = “SOFTWARE\Microsoft\Microsoft SQL Server”

            $type = [Microsoft.Win32.RegistryHive]::LocalMachine

            $regKey = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($type, $Server)

            $SQLServerkey = $null

            $SQLServerkey = $regKey.OpenSubKey($MSSQLkey)

            # Check to see if MS SQL Server is installed

            IF ($SQLServerkey)

            {#Begin IF $SQLSERVERKEY    

                #DEBUG Write to Host “Sub Keys”

                #Write-Host

                #Write-Host “Sub Keys for $MSSQLkey”

                #Write-Host “——–“

                #Foreach($sbky in $SQLServerkey.GetSubKeyNames()){$sbky}

                $Instkey = $null

                $Instkey = $regKey.OpenSubKey($InstanceNameskey)

                # Check to see in chargeable Instances of MS SQL Server are installed

                IF ($Instkey)

                { 

                    #DEBUG Write-Host “Values” of SubKeys

                    #Write-Host

                    #Write-Host “Sub Keys for $InstanceNameskey”

                    #Write-Host “——“

                    #Foreach($sub in $Instkey.GetSubKeyNames()){$sub}

                    foreach ($regInstance in $Instkey.GetSubKeyNames())  

                    {  

                        $RegInstNameKey = $null

                        $SetupKey = $null

                        $SetupKey = “$InstanceNameskey\$regInstance”

                        $RegInstNameKey = $regKey.OpenSubKey($SetupKey)

                        #Open Instance Names Key and get all SQL Instances

                        foreach ($SetupInstance in $RegInstNameKey.GetValueNames())  

                        {  

                            $version = $null  

                            $edition = $null

                            $regInstanceData = $null

                            $SetupVersionKey = $null

                            $VersionInfo = $null

                            $versionKey = $null

                            $regInstanceData = $RegInstNameKey.GetValue($SetupInstance) 

                            $SetupVersionKey = “$MSSQLkey\$regInstanceData\Setup”

                            #Open the SQL Instance Setup Key and get the version and edition

                            $versionKey = $regKey.OpenSubKey($SetupVersionKey)

                            $version = $versionKey.GetValue(‘PatchLevel’) 

                            $edition = $versionKey.GetValue(‘Edition’)   

                            # Write the version and edition info to output file

                            $VersionInfo = $Server + ‘,’ + $regInstanceData + ‘,’ + $version + ‘,’ + $edition  

                            $versionInfo | Out-File $outfile -Append 

                        }#end foreach $SetupInstance

                    }#end foreach $regInstance

                }#end If $instKey

                ELSE

                {#Begin No Instance Found

                    $found = $found + ” but no chargable instance found.”

                    echo $found

                }#End No Instance Found

            }#end If $SQLServerKey

        }#end If Connectionfound

        ELSE

        {#ELSE Connection Not Found

            $notfound = $Server + ” not pingable”

            echo $notfound

        }

    }#endTry

    Catch

    {

         $exceptionType = $_.Exception.GetType()

        if ($exceptionType -match ‘System.Management.Automation.MethodInvocation’)

        {#IfExc

             #Attempt to access an non existant computer

             $Wha=$Server + ” – ” +$_.Exception.Message

               write-host  -backgroundcolor red  -foregroundcolor Black $Wha   

        }#endIfExc

        if ($exceptionType -match ‘System.UnauthorizedAccessException’)

          {#IfEx

            $UnauthorizedExceptionType = $Server + ” Access denied – insufficent privileges”

            # write-host “Exception: $exceptionType”

            write-host -backgroundcolor red “UnauthorizedException: $UnauthorizedExceptionType”

           }#endIfEx

        if ($exceptionType -match ‘System.Management.Automation.RuntimeException’)

            {#IfExc

               # Attempt to access an non existant array, output is suppressed

               write-host  -backgroundcolor cyan  -foregroundcolor black “$Server – A runtime exception occured: ”   $_.Exception.Message; 

            }#endIfExc

    }#end Catch

}#end ForEach servers in domain

#number of servers

$colResults.count

enjoy the script and comments are always welcome

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s