Finding lost or forgotten SQL Servers

Question

Finding lost or forgotten SQL Servers

Viewing 13 posts - 46 through 57 (of 57 total)

You must be logged in to reply to this topic. Login to reply

SQLHeap Hall of Fame Points: 3572 More actions · Answer 1

s.youngman (7/25/2013)
I have a similar powrshell script that I run from cmd line on an adhoc basis.
I get round the subnet issue like this

Very nice!

There is an exception to every rule, except this one...

WayneS SSC Guru Points: 95460 More actions · Answer 2

Has anyone tried:

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

It's description is: "Provides a mechanism for enumerating all available instances of SQL Server within the local network."

So what I wonder is:

Does it only see the instances that are actively running?

Does it cross sub-nets?

Does it cross domains?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

s.youngman Valued Member Points: 55 More actions · Answer 3

WayneS (7/25/2013)
Has anyone tried:
[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()
It's description is: "Provides a mechanism for enumerating all available instances of SQL Server within the local network."
So what I wonder is:
Does it only see the instances that are actively running?
Does it cross sub-nets?
Does it cross domains?

I think it is the same as sqlcmd -L

georgiabasser Valued Member Points: 61 More actions · Answer 4

Good article. I can relate to your organization not wanting to purchase 3rd party tools. Nice script and thought out process. Will be trying it out in my environment.

Thanks for sharing.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 5

s.youngman (7/25/2013)
WayneS (7/25/2013)
Has anyone tried:
[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()
It's description is: "Provides a mechanism for enumerating all available instances of SQL Server within the local network."
So what I wonder is:
Does it only see the instances that are actively running?
Does it cross sub-nets?
Does it cross domains?
I think it is the same as sqlcmd -L

It is.

K. Brian Kelley
@kbriankelley

chuck.hamilton SSCommitted Points: 1539 More actions · Answer 6

Since SQL always registers an SPN when it starts, using setpn to find the instances might be faster than using "new view"

setspn -F -Q MSSQLsvc/*

I'm sure this could be massged into the powershell script but I'm not good enough with PS yet to write up how.

chudman SSCrazy Points: 2453 More actions · Answer 7

The MAP scan easily finds all versions of SQL and all components installed if you have the elevated permissions required to see all machines on your domain. SQL Browser being on or off does not influence the findings when we run this tool. Since it is free, and collects a LOT of useful information, I recommend it for any enterprise level asset management effort.

Jeff Bennett

SQL Server DBA

Saint Louis, MO

SQLHeap Hall of Fame Points: 3572 More actions · Answer 8

SQL Browser being on or off does not influence the findings when we run this tool.

That used to be the case, a couple years ago anyway. Haven't verified that lately.

There is an exception to every rule, except this one...

PHYData DBA SSCertifiable Points: 7541 More actions · Answer 9

Nice article and interesting script.

Your description of MAPS is totally wrong though.

I will not only find any instance of MSSQL with Browser turded off, but with the right settings it will find your Oracle, MySQL and other SQL style servers.

Maybe you should look into it. :smooooth: Maybe

Edit - This includes the edition that was released for making upgrade assessments for the 2008 and 2008 R2 releases. So your statement "it did not a couple of years ago" also does not compute. 😉

Jim P. SSCrazy Eights Points: 8725 More actions · Answer 10

My company hosts our various software packages on over a hundred different servers and domains. Some of it is SQL Server and then there is other database and applications.

I built a batch file to write data to a central location with a userid that can only access that location. Then I process the text files to consolidate the data. The outside batch file copies the daily batch file over and runs the sub-batch file. So if I want to collect AD info on a given night, I can just change the sub batch file.

----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.

427cjackson Grasshopper Points: 11 More actions · Answer 11

Nice post. I like the automation. I've been using a command periodically to get the servers/instances/versions, but it's manual.

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

arooney SSC Rookie Points: 48 More actions · Answer 12

Nice Article! This scenario is riddled with 'reg flags', although I have been around enough to say its a common issue. For most enterprise environments, assets are mapped down to the floor/rack/server/purpose level and some include naming conventions to include this information. I could never use such a script in my production environment due to restrictions around segregation of duties, generic domain level admin id's.

With that said, it resolves a common issue for most shops were financial auditing constraints do not govern IT access and security models.

Thanks For Sharing.

Dave Pendleton SSC-Addicted Points: 405 More actions · Answer 13

Interesting as an academic excercise, but as chudman pointed out, the MAP Toolkit does all this and a huge amount more.

Run it as a DA and then generate the reports. Don't just stop at the standard reports, either. Connect to the database (it uses LocalDB) and dig around, particularly in the Views. There's an amazing amount of information in there.

Regards,