Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Finding lost or forgotten SQL Servers

By Clay Punnett, (first published: 2011/04/19)

I have recently been tasked with finding all SQL Servers for inventory purposes. As I set to this task, I identified a couple of issues immediately. First, was that my new place of employment doesn't like to purchase any 3rd party applications. Also, most free tools including Microsoft Assessment and Planning toolkit (MAP) scan servers tend to look for the SQL Browser. These kinds of products aren't going to work well because if the SQL Browser is off, then the server will not be found. So, I set out to accomplish the task using PowerShell. Think of PowerShell as a sql query window for every Microsoft product. This script will look for the SQL Server service, running or stopped. Also, it will execute once a week through a SQL Agent job, so I can see what new servers have been installed recently. PowerShell is a wonderful tool, and not just for network administrators. All right, now the good stuff.

First, since it will be executing through a SQL job, the SQL Agent will need to be setup with a security ID strong enough to reach all Windows operating systems throughout the network. This script will go across domains, so no need to run in each domain.

IMPORTANT: if the ID does not have sufficient rights to all computers then it will simply skip past the server even though it saw the server on the network, so your list will not be complete. You may want to reach out to the network security group and let them know in advance, before they think there is a virus running rampant.

Next, we need to create a simple database and table to store the discovered SQL Server names. The following script sets up the tables and databases (you may need to change the file paths):

USE [master]
GO
CREATE DATABASE [SQL_Names] ON PRIMARY 
( NAME = N'SQL_Names', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SQL_Names.mdf' , SIZE = 3096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'SQL_Names_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SQL_Names_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
USE [SQL_Names]
GO
/****** Object: Table [dbo].[FoundSQLServers] Script Date: 02/15/2011 11:01:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FoundSQLServers](
 [SQLServerName] [varchar](100) NULL,
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [Date] [datetime] NOT NULL,
 CONSTRAINT [PK_FoundSQLServers] PRIMARY KEY CLUSTERED 
(
 [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[FoundSQLServers] ADD CONSTRAINT [DF_FoundSQLServers_Date] DEFAULT (getdate()) FOR [Date]
GO
CREATE NONCLUSTERED INDEX [nc_index_FoundSQLServers] ON [dbo].[FoundSQLServers] 
(
 [SQLServerName] ASC
)
INCLUDE ([Date]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Next let's create the PowerShell script and save it to 'C:\PowerShell' as 'FoundSQLServers.ps1'. You need to update the $destserver variable with the name of your SQL Server:

$destserver = "YourSQLServer"
$destDB = "SQL_Names"
$destTable = "FoundSQLServers"
function Set-FoundSqlServers
{
 param($ComputerName)
 $Query = "INSERT INTO $destTable (SQLServerName) VALUES ('$($ComputerName)')"
 $conn = new-object System.Data.SqlClient.SQLConnection
 $ConnectionString = "Server={0};Database={1};Integrated Security=True;" -f $destserver,$destDB
 $conn.ConnectionString = $ConnectionString
 $conn.Open()
 $cmd = new-object system.Data.SqlClient.SqlCommand($Query,$conn)
 [void]$cmd.ExecuteNonQuery()
 $conn.Close()
}
function Find-Computers
{
 $computers = @()
 net view |% {
 if ($_ -match "\\\\(\S+)") {$computers += $matches[1]}
 }
 $computers |? {(get-service -computername $_ |% {$_.name}) -match "sql"}
}
Find-Computers | foreach-object {Set-FoundSqlServers $_}

Now, let's create a stored procedure to build a list of new server names and email a report to yourself in HTML format. You will need to update the @EmailRecipients and @profile_name variables:

USE [SQL_Names]
GO

/****** Object: StoredProcedure [dbo].[sp_SendWeeklySQLReport] Script Date: 02/15/2011 10:50:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/******
Stored Procedure builds a list of new SQL Servers found and emails a report to @EmailRecipients.
by Clay Punnett
PunnettC@Yahoo.com
@SQLHeap
******/
CREATE Procedure [dbo].[sp_SendWeeklySQLReport]
as

Set Nocount On

Declare @SQLCount int, @EmailRecipients varchar(100), @HTML nvarchar(MAX);

Set @EmailRecipients = 'your.email@yourplace.com'

Set @SQLCount = (
Select COUNT(*)
From SQL_Names.dbo.FoundSQLServers a (nolock)
Where a.SQLServerName not in (
Select Distinct SQLServerName
From SQL_Names.dbo.FoundSQLServers b (nolock)
Where LEFT(b.[Date], 11) < LEFT(GETDATE(), 11)
)
and LEFT(a.[Date], 11) = LEFT(GETDATE(), 11)
)

If (@SQLCount = 0)
Begin

Set @HTML =
N'<H1>New SQL servers found.</H1>' +
N'<table border="3" bordercolor="blue">' +
N'<tr>No new SQL Servers found in the passed week.</tr>' +
N'</table>'

End

If (@SQLCount > 0)
Begin

Set @HTML =
N'<H1>New SQL servers found.</H1>' +
N'<table border="3" bordercolor="blue">' +
N'<tr><th>SQLServerNames</th></tr>' +
CAST((Select td = SQLServerName, ''
From SQL_Names.dbo.FoundSQLServers a (nolock)
Where a.SQLServerName not in (
Select Distinct SQLServerName
From SQL_Names.dbo.FoundSQLServers b (nolock)
Where LEFT(b.[Date], 11) < LEFT(GETDATE(), 11)
)
and LEFT(a.[Date], 11) = LEFT(GETDATE(), 11)
Order by a.SQLServerName
For XML PATH('tr'), TYPE) as nvarchar(MAX)) +
N'</table>';

End

If (@SQLCount = 0)
Begin

EXEC msdb.dbo.sp_send_dbmail
@recipients = @EmailRecipients,
@subject = 'New SQL servers found Report',
@body = @HTML,
@body_format = 'HTML',
@profile_name = 'yourprofilename';

End

If (@SQLCount > 0)
Begin

EXEC msdb.dbo.sp_send_dbmail
@recipients = @EmailRecipients,
@subject = 'New SQL servers found Report',
@body = @HTML,
@body_format = 'HTML',
@profile_name = 'yourprofilename';

End

GO

Finally, let's create the job to run every Monday at 9am (or whenever) and email a list of only the new SQL Servers:

USE [msdb]
GO

/****** Object: Job [Find All SQL Servers] Script Date: 02/14/2011 14:43:41 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 02/14/2011 14:43:41 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Find All SQL Servers',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Find All SQL Servers throughout the entire network.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Execute PowerShell script] Script Date: 02/14/2011 14:43:41 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Execute PowerShell script',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'PowerShell',
@command=N'powershell.exe -File "C:\PowerShell\FoundSQLServers.ps1"',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Email Report] Script Date: 02/14/2011 14:43:41 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Email Report',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC sp_SendWeeklySQLReport',
@database_name=N'SQL_Names',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Monday Morning',
@enabled=1,
@freq_type=8,
@freq_interval=3,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20110214,
@active_end_date=99991231,
@active_start_time=90000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Note that when you see new SQL Servers, it may be because the computer simply was not on during the previous runs. These scripts pull in just the computer name. Also, They can be modified to gather more data such as instance name, sql version, sql edition, etc.

Respectfully,
Clay Punnett

Total article views: 15443 | Views in the last 30 days: 10
 
Related Articles
FORUM

How do i grant permission to msdb.dbo.sp_send_dbmail ?

How do i grant permission to msdb.dbo.sp_send_dbmail ?

FORUM

msdb.dbo.sp_send_dbmail

msdb.dbo.sp_send_dbmail

FORUM

how to retrieve value from msdb.dbo.sp_help_job

I need to retreive the current status of a job from msdb.dbo.sp_help_job

FORUM

msdb.dbo.sp_send_dbmail Not working with SQL JOB

msdb.dbo.sp_send_dbmail Not working with SQL JOB

FORUM

SQL Server 2005 script

SQL Server 2005 script

Tags
powershell    
sql discovery    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones