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?