This T-SQL Tuesday we are hosted by one of the DBA’s of the night: Jen McCown (b/t). She want’s us to talk about Strategies for managing an enterprise. Honestly, had I know she was hosting I probably would have seen this one coming since she and her husband Sean McCown (b/t) do a session on Enterprise scripting (that I’m told is very good) and will be presenting a precon at the Pass Summit this year on the same subject.

So what’s my two cents? There are lots of great tools and scripts that help with managing your enterprise but they all have one major requirement. You need to know what you have in your environment. There are two ways to generate the information. Manually or with a tool.

Discovery Tools

These tools explore your network and find all of the instances in the environment. I’ll be honest here and say that I’ve never used a discovery tool personally. So I did what I do when I need some information. I ask someone. In this case I tweeted to the #sqlhelp hashtag. I had quite a few answers within minutes and several more that trickled in over the next hour or two (as of me writing this post). In case you were wondering this is one of the amazing things that available from the SQL Server community. The amount of information out there and the willingness to share is just astounding. So here is the list of discovery tools mentioned along with some comments. I’m not going to include individual names just because I got so many responses but you can look at the chain here.

• SQLPing
• Nmap – This one was mentioned by three different people and they included a warning that this tool can be considered a hacking tool so make sure you absolutely have permission to run it before doing so.
• PowerDoc – This tool by Kendal Van Dyke (b/t) was the most popular at four mentions.
• Dell Discovery Wizard – Two mentions and a comment that you can run diagnostic queries through the tool as well.
• Idera’s SQL Discovery – Two mentions. This one is part of the Idera Toolset which is something I use and I can say from personal experience that a lot of the tools in it are awesome so I imagine this one is too.
• Microsoft’s MAP toolkit – Two mentions with a comment that there are some nice reports included.

 
Manually maintaining a list

This method can be a good one for small enterprises. You create a table and populate it manually as you install new instances. Most of the time this isn’t a big deal since how often do you really need to install a new instance? At my current company (not a small enterprise), however, we have an instance table that we maintain manually and it’s a pain. We are constantly having problems keeping it up to date. That said it CAN work, but you have to stay vigilant.

A combination of the two

In my ideal world we would use a discovery tool to generate an initial list and store that data in a table. We would then have a process that used the discovery tool to make periodic updates to the table but have the option of adding/changing the information manually (active/inactive, not supported etc).

What can you do with the information?

Geez, what can’t you do! I’ve used our instance table to write SSIS packages that loop through a list of instances, populate a CMS (Central Management Server) and generate an XML file for Idera’s Toolset to mention just a few things.

Filed under: Documentation, Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tuesday Tagged: Documentation, microsoft sql server, T-SQL Tuesday