August 17, 2011 at 1:03 pm
Like many of us, I manage a plethora of SQL Server Instances; each with many databases.
I haven't seen any articles about best practices to manage everything that I have to manage.
Example 1)
I recently created a stand alone SQL Server instance for the sole purpose of utilizing the Central Management Servers feature of SQL 2008. It just seems 'funny' that I have this entire server that I'm really only using so that I can reference my other servers I manage. It seems like a waste of a SQL Server Instance.
Example 2)
On our SSIS packages we take advantage of SQL Server Configuration Files. I have a dedicated database created to house the table which contains all of the configurations. The individual packages then use the Filter to obtain the correct configuration values. At first I had one db per server where we had SSIS Packages. I've recently decided that's overkill; and have decided that all of our Production Servers can access a Prod version of our config db, and all of our dev servers can access the Dev version of the same database.
Example 3)
Some of our databases are becoming complex and index maintenance requires a little more care than the one shot rebuild all method. I'm using a script that obtains fragmentation info on all dbs on a server. It's a single stored proc, and a single table. I'm conflicted where to house these. I thought about creating a database on each server that is for DBA Use only for things like this. I'm sure other things would get added over time. I don't like the idea of placing this proc and table into one of my my prod dbs; but it seems 'wrong' to create a db with one table and one proc in it. What the prevailing opinion? Do you DBAs out there have a private db on your servers for various management functions?
So That's it. What are your SQL Server Management Suggestions, Tips, and Tricks? I'd be interested to know how it's being handled by the masses.
August 18, 2011 at 4:31 am
I have two DBA databases on each server which have "stuff" I use, tables, procs, config etc. etc.
I also have some procs/functions I deploy to master to assist me in some work I do.
Most of my monitoring/management, for lack of a better term, is handled through SSRS into dashboard(s) - I also have alerts to warn me of things. Certain reports get published to disk or emailed to others to form a historical record and so on
I've never had to handle more than 50 servers to be honest and in that environment I wasn't the only DBA so load was shared.
I have no issues using a server all for myself - it's only what a monitoring solution would do after all.
regarding index rebuilds, I use configurable selective index rebuilds to avoid unneccesary rebuilds. I collect and store things like index fragmentation and maybe 50 odd perfmon counters per server. Plus reports on database growth and disk space. Once I have enough historical data I use forward predictions in an attempt to forestall nasty surprises!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply