July 30, 2012 at 9:12 pm
Comments posted to this topic are about the item The DBA Database
July 31, 2012 at 4:28 am
My company as a policy does not allow to build extra databases. So for me i built a CMS instance and use powershell to manage my instances
July 31, 2012 at 5:39 am
My mentor and our Sr. DBA (wisely) put a DBA database in place for all critical environments. The setup gives us tables to audit/log DDL events, log certain other activity, monitor blocking, monitor statistics, and work with trace analysis and it has proven to be extremely valuable many times over! Couldn't agree more.
------------------------
David Caughill
Production DBA
Grad Student
Coffee Enthusiast
July 31, 2012 at 6:13 am
Have you considered using the Management Data Warehouse feature available natively in SQL 2008 and higher?
http://msdn.microsoft.com/en-us/library/dd939169(v=SQL.100).aspx
We've found it to be quite useful for performance diagnostics, without the need for custom scripting. It's all built into SQL Server.
That said, we did build on Bill Ramos' excellent work to convert the reporting capability which ships with MDW to an SSRS based solution.
July 31, 2012 at 6:34 am
Would / could you share the SSRS solution? I have been thinking about doing the samething, but just haven't found the time.
Jim
July 31, 2012 at 6:41 am
In a perfect world MDW would be nice, but there will always be a couple of 2005 and/or 2000 instances floating around...
July 31, 2012 at 6:42 am
I could, but would direct you to Bill's blog first:
http://blogs.msdn.com/b/billramo/
There's a six part series there on migrating MDW reports to SSRS. Bill's approach leverages a new query data collector to take advantage of the 'fingerprint' feature. As my organization already had a large deployment going, I had a need to build on Bill's work (or take away from you might say) by re-writing his reports to work with the out of the box query collector. If you have the ablity to deploy his query colelctor I would encourage you to go that route as the 'fingerprint' feature certainly adds value in aggregating similar, non-parameterized, queries by KPI.
July 31, 2012 at 6:46 am
Certainly true, but hopefully less and less. We (briefly) considered reverse engineering the SQL 2008 MDW / data collector solution to gather data from SQL 2005 instances, but then decided the effort outweighed the long term gains. Better to focus on moving those instances to SQL 2008 (or 2012, now).
If after reading over Bill's blog you find you need query reports which work with the out of the box collectors let me know. That and an overview report which only displays instances to the end user to which they should have access are the two principal changes I've made to Bill's work. (So far)
July 31, 2012 at 6:49 am
As part of a development team we have shared DBA resources, and cannot access most of the "good" stuff about the server ourselves. That being said, we do have a DBA-type database on each server as well as a centralized database where all the data is collected. We mainly store job run information so we can monitor the jobs and capture error information to help with error recovery rather than digging through the job history in SSMS. Using a query is much faster and much more informative.
July 31, 2012 at 6:52 am
nathaniel.drehmel (7/31/2012)
I could, but would direct you to Bill's blog first:http://blogs.msdn.com/b/billramo/
There's a six part series there on migrating MDW reports to SSRS. Bill's approach leverages a new query data collector to take advantage of the 'fingerprint' feature. As my organization already had a large deployment going, I had a need to build on Bill's work (or take away from you might say) by re-writing his reports to work with the out of the box query collector. If you have the ablity to deploy his query colelctor I would encourage you to go that route as the 'fingerprint' feature certainly adds value in aggregating similar, non-parameterized, queries by KPI.
Thanks for this - we've been doing a lot of SSRS work lately and I'd LOVE to harness this. Definitely going to check it out!
------------------------
David Caughill
Production DBA
Grad Student
Coffee Enthusiast
July 31, 2012 at 7:04 am
I have DBA databases on my servers as well. Doing much the same things that others have described. Absolutely essential.
In addition to all the private DBA stuff, we do collect some information that we want others to see. For example, we use Service Broker to capture Event Notifications for blocking and deadlocking.
For anything we want to make public we create views or functions that cover what we want to expose and grant access to developers and other support staff. This is still a DBA database, but the DBAs are providing details that others can use to improve their systems.
RLF
July 31, 2012 at 7:29 am
a dba database is critical for CDC efforts and security during code releases, audits and dr checks where I work. I use performance collectors for monitoring my 2008 and higher databases but for the older stuff, I rely on custom pulled data using the dba database's views and procs & a msg broker/sql agent system that ports data over to a central db where I can aggregate and store trend/kpi details; Going to be a few more years before I see us getting rid of 2005. Why sharepoint does not have a inplace upgrade for their backend I do not know plus we have so many old systems with no developer's supporting them that 2000 & 2005 will never seem to die...
July 31, 2012 at 7:43 am
Yes, and it holds all the DBA stuff we need to do and house on a daily basis. Ours is called "DBA_MetaData". At the last place i worked it was called "DBA_Utility", and "Yes" they are quite handy to have. 😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
July 31, 2012 at 8:07 am
@cusanom I've written @PowerShell scripts to inventory our entire network of sql servers on a scheduled basis and dump everything into a single database. I was hated by my boss and really didn't make any friends at first when I was telling them what I wanted to do. However, we had nothing! I worked on my own at night from home, building scripts and a database...under radar. Once I had most of it built and they could see the data I collected, they were ecstatic. Now, my boss is requiring me to do it. "What a switch, eah!"
July 31, 2012 at 8:23 am
I've been doing that since I started working as a DBA for the exact reasons you gave Steve. I never wanted anything to be attached to any other DB especially in production, but I always need that reach. Next step is to figure out how to use them in conjunction with Central Management System TIME PERMITTING.
Good topic Steve!
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply