Useful Features in SSMS for Managing a Large SQL Server Environment

I really enjoyed presenting at SQL Rally about challenges DBAs face in large SQL environments.  My evaluations were not as stellar as I had hoped (I actually ended up pretty much setting the average bar) but all the comments against my presentation were spot-on.  Mostly they were that the presentation covered too much and was too general.  I had actually said this prior to my presentation so maybe I'm partly to blame for planting the seed. The past is the past and that presentation really was designed as an outline to provide fuel for thought - and future blog posts and articles!  This is where this post comes in.  

Countless articles have already been written about SSMS features. SSMS has certainly come a long way since the days of Enterprise Manager.  One way Microsoft has advanced the tool is in the realm of managing a large number of instances. I think people overlook some of the features developed in SSMS 2005 and continued into SSMS 2008. I've been managing large environments for years and I'm even still discovering new tips and tricks that make my job more efficient. Here are few notable points.  Again, the details of these can be gathered in other technical postings and some I may elaborate on in future posts, but here's the gist:

1) Use CMS (Central Management Server)
This is a no-brainer for a large environment. Out of all the changes to SSMS I truly believe this is the single greatest change.  Simply put CMS allows you to designate a SQL Server as repository for all your registered servers.  Why is this important?  Because of CMS all DBAs on a team now have a single, standard list of all the registered SQL Servers in the enterprise. Additionally, in the age of VM we add a server almost every week (I built 3 last week) and now all I have to do is register it in the CMS and everyone sees it. I use to have to send out a notification telling everyone to register the server and I would have to assume they did. Many times people forgot and I now have a DBA who doesn't realize we manage a server. This can prove a bit awkward for after-hours support calls.   There are other advantages to using CMS that I'll get into in later posts.

2) Using Groups to Add Servers to Multiple Categories
In a large database environment information comes to you in many forms depending on the where the information originates. For example, a non-technical user may inquire about a database problem but have no idea of anything beyond the application name listed as in icon on his desktop.  You also may get requests from internal IT folks like the server or storage teams who know nothing about the database but do know the server name.  The solution for this in SSMS is to create multiple groups.  You cannot put the same server name multiple times in one group but you can add the same name to different groups.  Here are my groupings - your mileage may vary:

Here's my reasoning. The "ALL SERVERS" is my list of all the servers in the environment. If someone comes to me with a server name,  I look here to see if we support it. If it's not listed then we don't support it.

Under "APPLICATIONS" is a list of more groups with names like "SHAREPOINT", "XEROX", and "LENEL".  Each group is the general name of the application and under that group is the registered server.  This allows me to quickly find the server if a user refers to the system by the application name.  the "DEPARTMENT" group I don't use too much but it allows me to separate out internal systems from external systems. In this group there are other groups listed such as "CITRIX TEAM", "MESSAGING TEAM", and "SERVER TEAM".  Each group has their own list of registered servers.

"LOCATION" is helpful if you have a geographically distributed environment. If  a user knows no other information except for where their server is physically located then this group allows me to have a fighting chance of tracking it down.

"NONPROD" and "PRODUCTION" help me separate our servers for monitoring and support. The screenshot doesn't show it but under "PRODUCTION" I have groups named "SQL SERVER 2000", "SQL SERVER 2005" and "SQL SERVER 2008" which is used for Policy Management.

Finally, there is "POC" which stands for "proof of concept". These are servers created by various teams for testing or product selection.  I keep these separate because they tend to (hopefully) only stick around for a short amount of time but I want to keep track of them so I can remind the users that they eventually need to go away to free up licenses.

3) Use Templates (and Soon-to-Arrive Snippets)
Templates I've found are a rarely used feature and I've had difficulty getting my team to use them.  Templates in SSMS lack a lot of functionality but I think it's a good start.  Here is a link to a Tim Ford article at MSSQLTips thoroughly explaining templates in SSMS: http://www.mssqltips.com/tip.asp?tip=1465.  Right now templates aren't great for collaboration but they do allow you to somewhat standardize your scripts and easily access them. I keep a list of a few key scripts and pass them out to my team when I update any of them.  I know Microsoft has some plans to expand the idea using snippets.  Snippets will be similar to templates but allow for a centralized source for code.  Aaron has a good summary on his blog here: http://sqlblog.com/blogs/aaron_bertrand/archive/2010/11/23/sql-server-11-denali-the-new-vs-shell.aspx

These 3 simple items will do wonders to help you manage your large environment.  They also help keep your team in sync.  The Central Management Server is the groundwork for implementing additional services. In another blog post I'll talk about more using services like Policy Management, MDW (Management Data Warehouse), and Multi-Instance Management to help monitor large, complex environments.