Wednesday, November 19, 2014

Work Smarter, Not Harder - The UTILITY Instance

If your a SQL Server DBA, chances are you've walked into environments that were pretty disorganized to say the least.  Nobody seems to have a complete list of SQL Server instances, the instances are not configured consistently, database recovery models and growth rates are all over the board, the master database is muddied up with old versions of scripts and other objects - the list is endless.  And it's a nightmare to administer.

I've come up with an organizational approach that has worked very well for me over the years.  The heart of the organizational effort starts by having a centralized administrative instance.  I personally call this instance my "\UTILITY" instance.  It acts as the Central Management Server, is used for Policy Based Management, is the master server (MSX) for common SQL Agent jobs, houses the core DBADB (which every instance I administer has) which in turn is where I keep my favorite administrative scripts, and finally it's where I keep my monitoring and utility databases.  It's important to note that I push out my administrative scripts to all instances using replication.  If a new version comes out, I simply update the central version and reinitialize all subscriber instances so they too receive a fresh, updated copy.

If you have questions and/or feedback on this approach, I would love to hear it!  I'm always looking to work smarter, not harder!

Here is a graphical representation of my \UTILITY instance's responsibilities: