January 31, 2011 at 12:23 pm
Hi
We have a clustered environment and the important database is mirrored. It is a highly transactional database. We also have a RO (Read Only) environment which is also clustered and use transactional replication to move data from Primary to RO's. With respect to availability, we are doing great. What I need pointers is, what other things I can do for Pro-Active monitoring and to show that we are doing a great job in system availability now and down the road, especially for management.
We also have alerts for job failures, mirroring issues, replication issues etc
Some things I can think and need help with is:
1. Reindexing online where thats not happening now (we cannot afford more than 10 or 15 min downtime). So, how can we do this for about 250GB db?
2. Capture performance counters, but which one are more important for pro-active monitoring?
3. Estimating DB size growth over a period so we increase size accordingly
4. Server uptime over a period, so we show reliability and uptime over a period
Any others you guys can suggest? What other reports I can develop to show management we are doing great?
January 31, 2011 at 12:37 pm
Here are my recommendations.
1) For rebuilding the indexes the scripts found at http://ola.hallengren.com/[/url] are very useful.
2) You may make use of Poweshell to capture Performance Counters. Here is an example.
3) and 4) You may find the SQL Monitor[/url] by RedGate tool useful.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
January 31, 2011 at 12:52 pm
You won't go wrong with Glenn Berry's excellent monitoring scripts: https://sqlserverperformance.wordpress.com/2010/12/29/updated-sql-2005-and-2008-diagnostic-queries/.
-Kevin
Blog at http://KevinEKline.com
Twitter at http://twitter.com/kekline
January 31, 2011 at 1:15 pm
aMSDeveloper (1/31/2011)
HiWe have a clustered environment and the important database is mirrored. It is a highly transactional database. We also have a RO (Read Only) environment which is also clustered and use transactional replication to move data from Primary to RO's. With respect to availability, we are doing great. What I need pointers is, what other things I can do for Pro-Active monitoring and to show that we are doing a great job in system availability now and down the road, especially for management.
We also have alerts for job failures, mirroring issues, replication issues etc
Some things I can think and need help with is:
1. Reindexing online where thats not happening now (we cannot afford more than 10 or 15 min downtime). So, how can we do this for about 250GB db?
2. Capture performance counters, but which one are more important for pro-active monitoring?
3. Estimating DB size growth over a period so we increase size accordingly
4. Server uptime over a period, so we show reliability and uptime over a period
Any others you guys can suggest? What other reports I can develop to show management we are doing great?
1) If you use the reindexing scripts already recommended or take a look at Michelle Ufford's maintenance scripts[/url], you shouldn't have any downtime. A 250gb database is not very big, so you shouldn't have any problems with rebuilding/reorganizing indexes.
2) There are lots of different opinions[/url] on this. Basically, I'd take one set and stick with it until you're sure it's NOT giving you what you need and then make changes. You want to be able to compare stuff over time.
3) There's no magic here. You either take the growth that you've had and extrapolate it out, or you talk to the developers and the business to find out how many transactions of what size they think they'll have. Then based on this you determine how many rows you'll be collecting. From that you look at the data types and sizes and do the math to again extrapolate what growth will be.
4) That's just largely a question of recording when you have outages, planned or unplanned, and reporting back on them.
The extra thing I would spend my time learning is Dynamic Mangement objects. There's an excellent book called "Performance Tuning Using Dynamic Management Views" by Louis Davidson & Tim Ford that's worth a read.
In addition to doing all this work yourself, I'd suggest you might consider a third party product like Red Gate's SQL Monitor (disclosure: I work for them).
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 31, 2011 at 1:42 pm
Great suggestions. Thanks so much. Makes me feel that we are not alone on the planet :).
I am also looking for suggestions on other reports which I can provide to show we are meeting the SLA.
For example, we have a replication latency report showing that we are most times under 2 mins.
I guess suggestions or traps of such kind. What other can I provide mgmt and they can go WOW...?
Please help.
Thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply