Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 5 down vote favorite

Is it common for SQL Server DBAs to have to struggle with Server Inventory Management due to multiple parties / departments bringing online their own instances of SQL Server?

If so have you been able to successfully add controls to prevent this problem? Also how do you monitor your environment on a regular basis to troll for unmanaged instances so that you can proactively prevent embarrassing situations like databases without backups?

share|improve this question
Just tossing this out there, maybe this is one of those situations where this site overlaps with ServerFault and the question might be better answered by one of their tools (sysadmin vs dba) ... however, that being said, I imagine there are some SSMS-like solutions that will make this easier, especially stuff from MS directly. Curious to hear what answers are out there, +1 from me. – jcolebrand♦ May 22 '11 at 23:30
1  
I ended up writing a package which pulls a list of windows servers from the domain controller (ignoring desktops). I save this list of servers to the database. I subsequently wrote another SSIS package with a WMI task which queries the list of servers and pulls the services on each box. Problem is I am not a domain admin, so I know of at least 2 servers which have SQL and I cannot access. – John DaCosta May 23 '11 at 14:43

4 Answers

up vote 5 down vote accepted

I don't have direct experience with this task but know how folks get around this problem. :-)

You setup some monitoring program to look out for all SQL Server instances on the network and anytime something comes up that is NOT in your radar, you can catch them right away. There are lot of options and here are couple of them. I am pretty there might be others or many variants of these two.

http://www.straightpathsql.com/archives/2009/02/what-sql-instances-are-installed-on-my-network/

http://andrekamman.com/find-sql-servers-on-your-network-with-powershell

That said, I have to tell you that if someone brings an instance without notifying the DBA or production support team then this should be handled politically. Get your boss (in confidence) to talk to their boss and avoid future recurrence of the problem. GL.

share|improve this answer
up vote 2 down vote

I'd like to add to Sankar's answer 2 other means of finding the sql servers in your network:

  • server discovery feature of Management Studio

  • SQL Ping - free tool from www.sqlsecurity.com

These situations are mainly political, if you don't manage them directly, then you can't be held accountable for others' deeds.

share|improve this answer
1  
Perhaps too late to the party, however I am a fan of Quest's SQL discovery Wizard (quest.com/discovery-wizard-for-sql-server). – RateControl May 24 '11 at 13:05
Forgot about it, yep, that's nice one too! – Marian May 24 '11 at 21:24
up vote 1 down vote

You can also use Microsofts MAP tool. This tool will hunt out all the servers on a domain basis, and provided the user has access to those server (domain admin would work). Just filter it out for SQL servers.

share|improve this answer
up vote 0 down vote

There is a wrong assumption made here, namely policing other departments Sql Server is DB admins responsibility. Every and all departments are responsible for what they set up themselves, actively going out there and hunting them is nothing more than exercising political power. As a DB Admin you are only responsible for the servers that you have set up and manage. The best a DB Admin can do is to offer to manage their data for them, otherwise should leave them be.

share|improve this answer
While it does seem political, I do believe there is some value in having this information for other reasons. Among these are Ensuring Licensing Compliance as well Ensuring all Databases on Servers (considered production) have a functioning backup solution. I take inventory on servers, but not on desktops. Dealing with unsupervised installs is a side effect of this, which I think is responsible behavior. – John DaCosta May 29 '11 at 2:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.