A decent inventory
-
I'm working with several named instances on stand-alone servers and clusters.
The amount of databases and instances continue to grow, and therefore I'm looking for a decent (low-level) database inventory system. The use is for keep on being in control and to automate basic tasks.
Our CMDB is not yet up and running with databases or tables as CI's, so I'm going for practical quick-and-dirty solution focused on DBA challenges.
Does anyone know of such a system?
/Niels Grove-Rasmussen
-
Inventory of the DBs can be acheived using sp_helpdb. Use sqlcmd.exe to connect to a list using trusted connections. Also look at sp_MSForeachdb. Pipe it out to a textfile. This can then give you a list of all databases and their core setup data for each db on each server in the list.
You can do the same using sp_spaceused - at the database level and then down at the table level.
A similar task to grab free disk space.
Finally you can do a similar thing to query the sql agent history tables and backup tables to generate your listings of tasks.
Should only take you a couple of hours to cobble together.
-
That's right - when I know my servers.
I have too many servers and instances to know them by heart.
So I guess, that what I'm looking for is a inventory of the layers below the databases.
We are working on a metadata database, that we will use for automation, capacity management and billing custumers.
We are currently inventing everything from the buttom - inspired by our needs.
The tools are SQL Server Agent, WSH and PowerShell.
/Niels Grove-Rasmussen
-
maybe SQL Server Health and History Tool (SQLH2) utility from MS can help you out. (http://www.microsoft.com/downloads/details.aspx?familyid=eedd10d6-75f7-4763-86de-d2347b8b5f89&displaylang=en)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
'That's right - when I know my servers.'
If your servers are broadcasting their details - someone can tell us the technical term I hope! you can call "sqlcmd.exe -L" to generate a listing.
-
The servers are not broadcasting - and are on several networks; the most are on a primary network, but some are on more or less isolated networks. This is due to the databases and applications, that are unsecured, and therefor placed on separated networks. We even have servers on different AD forests...
/Niels Grove-Rasmussen
-
Well, I guess you'll be doing it by hand then...
-
Give SQLRecon a try - it's a freebie to find all of the SQL Servers on a network or series of networks. It helps if you have domain level authority - this way it can query AD and do a few other cool things. Also I'd speak to the network folks because they may have firewalls and routing rules to deal with not to mention intrusion detection software that may put a quick halt to your scans for the SQL Servers. Additionally is your network folks can provide you a list of sub-nets your discovery process will be greatly expedited.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
I am not sure how SQLRecon does the penetration testing when it hops between networks (i.e., which ports does it use to auto-detect servers?), but it does look like a cool tool. If your 1434 UDP port is blocked (which sounds like it is) between the networks, it may be an issue.
Another idea to try is the following:
Write a 2-line wmi script that gets all the services (or SQL services in particular). The following link is a great resource for free wmi samples:
http://www.activexperts.com/activmonitor/windowsmanagement/wmi/samples/
That would give you an inventory of all your SQL servers. From there, you can easily move on to get all databases on those servers. This technique does require you to know all the machines in the network though (i.e., to have a machine inventory).
-
mind you, you could probably query the active directory to get that.
-
Thank you - all of you.
I will look into SQLRecon ASAP.
BTW - Our SQL Server are spread over several AD forests. And yes - UDP 1434 is mostly blocked...
Right now we are using PowerShell to query known servers and cluster, and store the results in our own database.
The "reverse" automation on the instances and the databases are done by WSH-scripts scheduled and executed on a central SQL Server batch server. The jobs are prepared for remote scheduling - in our case IBM TWS on a mainframe.
This we hope gives us the opportunity to coordinate regular maintenance with the business batch jobs.
Comments or suggestions are very welcome!
/Niels Grove-Rasmussen
-
I've got a similar issue at the moment - new to company, server sprawl, trying to get a handle on things. As a result of this thread I tried out SQLRecon and it turned up a few servers which SQLCMD -L had not found, but similarly SQLCMD finds some which SQLRecon doesn't, even when I feed it the IP addresses of those servers.
I'd be interested to know what SQLCMD is doing that SQLRecon doesn't. Any ideas anyone?
-
For what its work my SQLCMD -L is picking up all sorts of things - a lot of which I think are Office Vista installs which seem to use 2k5 express...
-
The -L option will only pick up servers on your current network segment that are broadcasting. As for SQLRecon missing things, you need to adjust the parameters. SQLRecon also needs to be a Domain Admin in order not to miss anything. Also, firewalled servers may be an issue as well.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
The problem with OSQL -L is that it doesn't discover multiple instances on the same server. Still, it gives you a list of primary points to investigate. I can't think of any way to discover multiple instances (haven't used SQL Recon yet) short of going into the directories and looking for the $ directories under the SQL Server folder.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply