THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

A DBA utility database in every SQL Server instance?

I was reading Louis Davidson’s post earlier today, and what he said below caught my attention:

 

“I am a big believer in having the database be as self contained as possible, so I try to put [maintenance] objects and such in the database, typically in a schema named utility.”

 

This is different from what I typically do, and got me wondering how people out there managing their maintenance objects. I typically store all the maintenance objects in a dedicated database on every SQL Server instance across the enterprise. There are a number of advantages in doing so. First, it allows me to deploy the maintenance objects once per instance at the server build time as part of the standard server build procedures.

 

In addition, most of the maintenance procedures are generic across all user databases, and can be executed and tracked centrally, if not across the enterprise, at least per instance. It would be a lot of repetition to deploy them into every database.

 

On a more subjective level, it seems to me to better leave a user database alone to host the application objects only without being ‘polluted’ by the maintenance objects.

 

I'm curious how you manage your maintenance objects out there in the real world. Do you put them all in a utility database or distribute them in the user databases?

Published Monday, March 16, 2009 6:54 PM by Linchi Shea
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Michael Zilberstein said:

I use to create dedicated database named "DBA" on every instance. Mostly it serves as repository for collecting information like IO, Wait stats, blocking locks etc.

March 16, 2009 6:33 PM
 

Andrew Kelly said:

I have been using a dedicated db for maintenance objects for years and see no reason to change it. I can do all the tasks I need with a single deployement per instance and upkeep is minimal.

March 16, 2009 8:42 PM
 

James Luetkehoelter said:

Utitility database per instance. I agree with trying to keep the user database isolated.

March 16, 2009 11:14 PM
 

Greg Linwood said:

Centralise as much as possible - on a per database basis within an instance, but also within a single instance wherever possible.

March 17, 2009 12:05 AM
 

simon said:

On large consolidated servers with several Instances on each physical server, I like the idea of the 1st (Default) Instance being a Management Instance to contain multiple management DB's for various purposes (performance, utility, logins to Audit Logins across the Server's other Named Instances via Logon Triggers). The Management Instance has SQL Agent configured as a Master Server and the Named Instances are Target Servers, to allow centralised Jobs for Backups etc.  But then, I do tend to go overboard with this sort of thing...

March 17, 2009 7:29 AM
 

Linchi Shea said:

> On large consolidated servers with several Instances on each physical server, I like the idea of the 1st (Default)

> Instance being a Management Instance to contain multiple management DB's for various purposes (performance, utility,

> logins to Audit Logins across the Server's other Named Instances via Logon Triggers).

I'm not sure I'd do that. Instead, I'd prefer a separate dedicated physical server for all the centralized DBA stuff (e.g. central jobs, central inventory, central perfmon repository, etc). In other words, I'd keep a utility DBA database in each prod SQL instance for maintenance objects and temp maintenance info local to the instance, and keep all the heavier stuff on the dedicated DBA server. Using the default instance on each multi-instance server is too much a waste.

March 17, 2009 9:07 AM
 

Steve LaRochelle said:

We actually have two utilities-type databases per instance. One contains generic, product related SPs, functions, etc that are independent of the other databases (like a UDF to strip out certain characters from an input which is used by all databases).  The other is for administrative purpose objects (like a customized version of sp_who2).  In both cases, we have one central database and replicate the objects to all other subscribers.  That way, when we have a new version of an administrative/utility object, we only need to deploy it once.

March 17, 2009 9:12 AM
 

noeldr said:

One separate utility db per instance.

March 17, 2009 9:40 AM
 

Morris Lewis said:

Currently, I have 1 db per instance that holds my maintenance sprocs and data from any long term monitoring I do. I don't believe in adding anything to a vendor application database because experience has shown that the vendor will either erase them with an upgrade or claim they are the reason for whatever is wrong.

In the next few weeks, I will be moving to a centralized management tool I've been writing off and on for years. I have close to 400 hundred databases across 16 servers now and expect both numbers at least to double in 2 years. Centralizing is going to help me do a lot of stuff more efficiently.

March 17, 2009 10:08 AM
 

AaronBertrand said:

Linchi, that assumes you have a spare server capable of handling everything coming at it from all your instances.  In my case it is much easier to store only instance-specific stuff on a dedicated database in each instance than to try to throw everything at a single central instance.  (Uptime and connectivity to this instance become an issue in your case as well.  In my scenario the utility stuff is all self-contained.)

March 17, 2009 10:27 AM
 

Alexander Kuznetsov said:

Linchi,

I always consider the following three scenarios:

1. If two production databases are restored as of different points of time, can it cause trouble?

2. If two production databases are cloned in development as of different points of time, can it cause problems?

3. If two production databases need different maintenance objects (like one client upgraded but another is still on an older version), how can I handle that?

These three scenarios can motivate me to have a completely self contained DB.

March 17, 2009 11:14 AM
 

DouglasTaft said:

I use a bit of both.  I have a dedicated database, DBAManagement.  I also have a couple of procedures which reside in each and every user database on every database server under a schema, dbaMGMT.  As I have added objects to my management database and schema, I have tried to use names which I hope will not be adopted by MS for their MGMT tools to avoid conflicts.  

I have never had good luck with a central repository for all databases, there are always exceptions which do not fit a single model.  This has been especially true in environments with one or more AD trees.  Even in the same forest there are always issues with Linked Servers, which necessitates a non DB driven solution, recently C# and PowerShell.  Usually I am lucky enough to be able to place code on a non database server, but then if I am relying on the management server to run the jobs. I have to hope the management server will not go down, or build in a check to ensure the server is live.  

Not sure what my point is, I guess I am still trying to figure out the best approach for maintaining and monitoring the databases and database servers under my care.  Good topic.

March 17, 2009 11:21 AM
 

Linchi Shea said:

Aaron;

The central repository on a dedicated server should really just be for that -- a central repository. I wasn't advocating driving all the prod jobs from a central utility server. When it comes to load on this server, I guess there are two key htings: (1) inventory info, and (2) perfmon like info. For the inventory  info, there isn't really much load. For the perf info, you probably need a decent server to be comprehensive. I'd argue that you'd derive great value even if you just dedicate a tiny server to inventory your environment daily.

DouglasTaft;

> I have never had good luck with a central repository for all databases, there are always exceptions which do not fit a single model.

It's not robust (or at least cumbersome) to maintain a central inventory/repository using strictly T-SQL.

March 17, 2009 2:21 PM
 

GrumpyOldDBA said:

Generally have two databases per server - one containing, usually, the procs and functions I use ( common code and objects ), one containing the data and anything local to that server. Been doing this for as long as I can remember.

March 18, 2009 5:17 AM
 

TiborKaraszi said:

I typically use one Utility database per instance. Perhaps things will change over time as 2008 had more "multi server" features - time will tell...

March 18, 2009 8:38 AM
 

Michelle Ufford said:

I previously was deploying all of my maintenance objects to individual databases.  More and more, however, I'm liking the ease-of-use for a centralized utility database.  

March 18, 2009 9:28 AM
 

rich said:

One database per sql instance.

It's called sql_tools and contains many utility procedures for managing the database as well as many tracking tables to record various db and system metrics.

I develop it continuously and it follows me from role to role.

March 18, 2009 6:21 PM
 

G D Milner said:

I tend to create a database on every server (production or dev) called AdminDB -- so it's at or need the top of the list. In that I put all utility things that I don't want in any database or in master DB. I WILL but a few procs for general use and general user use in master, but anything that is for DBA use only goes in AdminDB. Trace results, etc go there as well.

Applications that need their own databases, for example DMVStats from   Codeplex, I generally let them have their default DB name.

Cheers.

March 18, 2009 7:32 PM
 

Prasanna said:

I have been sending this as a "FEATURE Request" to Microsoft since 2000 or 2001 (when I was a contractor there), but no luck :)

I believe there are lots of SMART DBAs & SMART SQL Developers who use the COMMON code/functions again & again across multiple Databases / Applications; if MSFT can shell (invest) sometime in this, it can be a great value add back to the entire community. Now that we have CodePlex, may be it can be a great place for MSFT to start this right there...

Not that all code can be used as it is, but for sure quite a few code can be used & I totally get that, but you got to start somewhere... :)

March 21, 2009 12:43 PM
 

Prasanna said:

Hey "Michael Zilberstein", could you please share the DB Script of "DBA" prabhupr70   at  yahoo

March 21, 2009 12:47 PM
 

Rudyx said:

master database for system wide procs. a dba_stats database for everything else including execution results and statistics that fall outside the realm of msdb. and yes, all procs in master begin with sp_ (only about 1/2 a dozen) - it's really easy if you have a standard server build - especially with 200+ instances !

March 21, 2009 11:31 PM
 

Chad Miller said:

I have two utility related database on every SQL Server instance. One for utility which contain the typical generic maintenance procedures and tables and another for objcopy which is used to create temporary full or partial copies of tables. The tables in objcopy are used to recover before images of manual data updates and dropped after a period of time. By keeping objcopy separate I avoid junking up the user database with temporary objects.

March 22, 2009 11:46 AM
 

David K said:

I Run one Utility database for the whole enterprise.  In this way I don't pollute any of my Prod / QA / Dev servers.  I also have one central point to change any of my maintenance / reporting jobs.  I use Powershell extensively to manage this type of deployment.

March 23, 2009 3:01 AM
 

Linchi Shea said:

> I Run one Utility database for the whole enterprise.

I would not consider having a dedicated utility database on a prod instance as 'polluting the instance.' But I'm curious if you have ever run into an issue of this single utility database for the whole enterprise being a single point of failure. What happens if that central utility server needs to go into maintenance itself?

March 23, 2009 8:21 AM
 

Saravanan said:

One DBA database per instance. Dont want to clash the app i/o with the utility I/O. Dont want the developers to point fingers when their code is having issue.

March 23, 2009 11:07 AM
 

niikola said:

One DBA database per instance + one centralized containing most important data. We had that in some "heavy duty" servers and it never caused a performance issues. Central database was used for our (DBA's) monitoring, reporting and statistics tool (although some of reports were connecting to the local databases for more detailed reports).

March 26, 2009 6:23 AM
 

Rex Gibson (UN) said:

We the same model utility db for maintenance on each server/instance and then run collections on an interval to the centeral system. So stats about maintenance are kept in each individual instance so that they are self contained. Then for ease of reporting and capacity planning all the maintenance stats get collected to a central model.

Perfmon stats are done centrally as these are smaller more repetative stats if we have any up time issues they are relatively short time spans and do not effect Perfmon stats on the average.

Currently am working on a dynamic collection SSIS package now that can collect from a new "satellite" by simply adding a row to a table. Should be done in a few weeks as it is a low priority. Right now we have to modify the collection package and added copy/import functions for each new instance which is cumbersome.

Also SQL Sentry and related products are in use here which follows a similar model.

Loved this post.

April 1, 2009 6:08 PM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement