January 24, 2010 at 8:37 am
I work for a small distribution business with 5 locations. We are using Activant's Prophet 21 SQL-based software since go-live April 2007, and has been running well ... until we experienced corruption in the database.
My role is ostensibly VP-Finance & Operations, but I also handle all IT related responsibilities. Activant never really schooled us in checking SQL logs or checking results of daily DBCC CHECKDB as a standard practice ... if I had known about it we could have found the corruption much sooner and taken remedial action.
Anyway, my question is what are the best practices for monitoring the health of the SQL databases besides checking daily logs? I see many different free tools listed in SQLServerCentral.com's articles -- should I use these? What do others do?
TIA for any ideas! 🙂
January 26, 2010 at 5:37 am
I'm an Accidental DBA as well. Just in the past month I have been putting together a small kit of T-Sql, PerfMon routines and a Log analyzer.
Google on "Brad McGehee" and "Sql Server DBA Best Practices". It will give you a good overview. Also go through "How To Become an Exceptional DBA" after that. These are eBooks he has made freely available and they're very helpful for peeking into the routines of a trained DBA. It will also give you an idea as to your own limits in this role. IMHO, a techies ability to admit what s/he does not know is of almost as much value as knowing alot. You don't want to get so far into something that you find yourself over your head and your organization's work is slowing down! The site that hosts Mcgehee's books is also an excellent place to bookmark and spend some time in. Lots of well written articles on really practical things.
When I run a performance monitor I do so on my local machine and point it to my production server. My counters log data every 15 seconds and my workers are not compromised in the least by this time period. The logs are saved locally and it impacts the db server less as it is writing these to my disk, and for other reasons as well I'm sure. I set my counters initially for a general overview of the health of our system I googled around and found a multipart series on doing this. It revealed some disk issues. That was last week (I did say I just started right?). This week I am running a log specifically to look at disk counters. This has already led to the discovery of an incorrectly set offset on our DB's RAID. That's where the politics entered. My suggestion is not to request that the techies do something until you are absolutely certain that all other variables have been optimized. Hardware changes should be the last resort to making a change in your system ... but I digress.
Google on "PAL" Log Analyzer. It is powered by Microsoft Log Parser and is truly awesome for dumbing down the performance monitor logs. These make little sense to us ADBAs so having this tool is essential. Microsof'ts Log parser is an amazing little tool that can make sense of any logfile you throw at it. It even has a whole book written about it which I've picked up.
I use a TSQL once a week to look at the growth of my backups. This gives me an idea of how fast the database is growing. Good for thinking about when I will need to add physical stuff but can also bring up questions, such as the one I have had to grapple with this week, about optimal transaction log sizes. I have an LDF that has gone crazy due in part to a default growth rate of 10% on th etransaction log.
I also have a tsql script that looks at columns in each table in my db. I discovered that whoever set up this db didn't think much about the column datatypes and consequently we are bloated. An employeeID does not need to be an numeric(18,0). We are small nonprofit! It is also good to have that info for other reasons.
I won't take the time to put the TSQl that I use here as those are readily available elsewhere but this, I hope, gives you a general overview of a few basic tasks for getting formalizing your approach and hopefully extracting as much value as possible from the time you spend as a dba.
Good Luck!
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
January 26, 2010 at 11:28 am
Thanks for your detailed post! I will definitely look into the resources you recommend.
It's going to take me some time, though, due to my other principal responsibilities here. You said it with the following relevant comment :
MothInTheMachine (1/26/2010)
... You don't want to get so far into something that you find yourself over your head and your organization's work is slowing down! ...
I completely concur.
-- Karl
February 16, 2010 at 2:11 pm
I worked for Activant / Prophet 21 for 10 years and I now do consulting work for distributors running P21. We have a number of automated routines we run each day as well as manual checks and balances. There is a lot Activant does not tell you during the sales process or even during the implementation. If you are interested in some help, you can contact me at sean.cashman@live.com.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply