How to find database servers on a network?

Question

question

Tarantula asked Jul 11, '11

How to find database servers on a network?

I am starting new at an organisation as a Junior DBA and wanted to know more details about all database servers in this organisation. I was wondering if there could be a way to show a list of all existing database servers on the network. I have never done this before but wanted to know the best way to proceed. I also came across MS MAP toolkit but was wondering what might be the network risks, pros and cons of using that tool. Any suggestions and answers are welcome.

sql database server network

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2011-07-11T09:26:56Z

Tim answered Jul 11, '11

The MS Map tool works great. I would let your network security team know that you need to run the tool as it does scan the entire network. I have never had it cause any network slowness issues or anything like that.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Shawn_Melton commented · Jul 11, 2011 at 10:05 AM

http://technet.microsoft.com/en-us/edge/Microsoft-Assessment-and-Planning-MAP-Toolkit-50-Overview-1-of-6.aspx Overview demo of the MAP 5.0 tool mentioned.

0 ·

Answer 2 · 2011-07-11T09:31:01Z

ThomasRushton answered Jul 11, '11

A starting point would be to run `OSQL -L`. Or `SQLCMD -L`. Take the output of that, and connect to each server identified and run the same command. See if you can find any other servers. I once wrote an SSIS package to do just that and found quite a few servers that the in-house DBA didn't know about.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2011-07-11T11:22:55Z

grrl_geek answered Jul 11, '11

You can also use PowerShell. Aaron Nelson has an excellent post on how to do so: http://sqlvariant.com/wordpress/index.php/2010/11/finding-sql-servers-with-powershell-part-2/.

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim commented · Jul 11, 2011 at 11:52 AM

Does that work on finding SQL 2000 and below?

0 ·

grrl_geek commented · Jul 15, 2011 at 08:24 AM

No. This option uses SMO, which can only be used in SQL 2005 +.

0 ·

Answer 4 · 2011-07-15T09:05:26Z

KenJ answered Jul 15, '11

- SQLPing by SQL Security- [ http://www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx][1] - Discovery Wizard for SQL Server by Quest - [ http://www.quest.com/discovery-wizard-for-sql-server/][2] These can run active scans so may turn up more instances than the sqlcmd -L approach. Make sure your network/security team knows you'll be running them. [1]: http://www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx [2]: http://www.quest.com/discovery-wizard-for-sql-server/

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

K. Brian Kelley commented · Jul 15, 2011 at 12:00 PM

This is the only solution that can find SQL Servers that are turned off and SQL Servers that are on computers you can't reach with a trusted connection. This is where MS MAP and the PowerShell scripts can fail (Aaron is going to kill me).

0 ·

question