Is SQL Server Installed?
Published: May 15, 2015
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…
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