A DBA utility database in every SQL Server instance?

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.

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.

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

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

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...

> 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.

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.

One separate utility db per instance.

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.

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.)

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.

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.

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.

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.

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

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.  

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.

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.

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... :)

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

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 !

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.

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.

> 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?

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.

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).

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.