Is SQL Server Installed?
Published: 6th January 2014
This came about due to incredibly bizarre request I received which happened to throw me a little. Basically I was told by a business that they actually had no idea how many SQL Servers they had or on which machines they could be running. They asked if I knew a way to find them.

Normally I’m a big fan of things like oSQL in DOS, but this is notorious for missing out huge swathes of instances when being run, especially if the instances are on different domains. Also I could have attempted to ping port 1433 (SQL Server default port) but there’s no guarantee that the default is still being used. Therefore I needed another idea and so I turned to Powershell.

Powershell is something I don’t use enough, but it is incredibly useful and something I should utilise more. The reason I chose to use it is because it can go to a windows machine and give you a list of installed services, including their current state and settings.

Based on this I came up with a little script (it’s a little messy as it was a rush job, but it did work) which runs from within SQL Server and uses xp_cmdShell to call Powershell.

The script simply allows you to pass in an IP range (it can be adapted for multiple ranges without much difficulty) and then it fires the Powershell command at each IP. If it receives a response then it looks through the returned data for a SQL Server service. If it finds one then it determines whether the service is running or not and also whether it’s in the Manual, Disabled, or Automatic mode.

As I said, it’s a little rough and ready, but it did the job for me and therefore might help someone else out there…

if object_id('tempDB..#results') is not null drop table #results
if object_id('tempDB..#temp') is not null drop table #temp
if object_id('tempDB..#t') is not null drop table #t
go

create table #results
(
      
id int identity(1, 1),
      
serverIP varchar(25),
      
instance varchar(100),
      
isSQLServer bit,
      
mode varchar(10),
      
isSQLRunning bit
)

declare @results table
(
      
id int identity(1, 1),
      
rawData varchar(100)
)
            
declare @serverList table
(
      
id int identity(1, 1),
      
serverName varchar(100)
)
insert into @serverList
select '10.11.255.1'

/*
-- obviously for a company you would like to cover an IP range when looking for servers:

declare @ip tinyint = 1

while @ip <= 255
begin
   insert into @serverList
   select '10.11.255.' + convert(varchar, @ip)

   set @ip += 1
end
*/

declare @sql varchar(2000), @serverName varchar(100), @counter int = 1

while @counter <= (select max(id) from @serverList)
begin
      select
@serverName = serverName
      
from @serverList
      
where id = @counter

      
select @sql = 'powershell.exe Get-WMIObject Win32_Service -ComputerName ' + @serverName

      
insert into @results
      
exec xp_cmdshell @sql

      
select *
      
into #temp
      
from @results

      
if exists
      (
            
select *
            
from #temp
            
where rawData like '%SQL%'
      
)
      
begin
            declare
@temp table
            
(
                  
id int,
                  
instance varchar(100)
            )
            
insert into @temp
            
select id, rawData
            
from #temp
            
where rawData like 'Name%MSSQLSERVER'
            
or rawData like 'Name%MSSQL$%'

            
insert into @temp
            
select id, rawData
            
from #temp
            
where id in
            
(
                  
select id + 2
                  
from @temp
                  
union all
                  
select id + 3
                  
from @temp
            
)
            
            
select *
            
into #t
            
from
            
(
                  
select substring(instance, charindex(':', instance)+2, len(instance)) instance,
                              
case (substring(mode, charindex(':', mode)+2, len(mode))) when 'Disabled' then 0 when 'Manual' then 2 else 1 end mode,
                              
case (substring(running, charindex(':', running)+2, len(running))) when 'Running' then 1 else 0 end running
                  
from
                  
(
                        
select t.instance, t1.instance mode, t2.instance running
                        
from @temp t
                        
join @temp t1
                        
on t.id = t1.id - 2
                        
join @temp t2
                        
on t.id = t2.id - 3
                        
where t.instance like 'Name%'
                  
) r
            
) s
            
            
if exists(select * from #t)
            
begin
                  insert into
#results(serverIP, instance, isSQLServer, mode, isSQLRunning)
                  
select @serverName, instance, 1, case mode when 1 then 'Automatic' when 0 then 'Disabled' when 2 then 'Manual' end, running
                  
from #t
            
end
            else
            begin
                  insert into
#results(serverIP, isSQLServer)
                  
select @serverName, 0
            
end
      end
      
      select
@counter += 1
end

select
*
from #results
Comments:
NB: Comments will only appear once they have been moderated.