Finding lost or forgotten SQL Servers

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]

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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

/******

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]

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

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]

/****** 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:

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

Finding lost or forgotten SQL Servers

Rate

Share

Categories

Share

Rate

Finding lost or forgotten SQL Servers

Rate

Share

Categories

Share

Rate

Related content

Script dimensions using Windows PowerShell and AMO

Introduction to Windows PowerShell for the SQL Server DBA Part 2

Restore Analysis Services database using Windows PowerShell and SQL Server 2008 AMO

Using Windows PowerShell to get SQL Server connection information

Using Powershell to Generate Table-Creation Scripts