The DBA Database

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716639

    Comments posted to this topic are about the item The DBA Database

  • sugnu iu

    SSCommitted

    Points: 1577

    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

  • dcaughill

    SSC Veteran

    Points: 207

    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

  • nathaniel.drehmel

    Grasshopper

    Points: 11

    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.

  • Jim Sleeman-388184

    SSC Enthusiast

    Points: 172

    Would / could you share the SSRS solution? I have been thinking about doing the samething, but just haven't found the time.

    Jim

  • Skandalis

    Hall of Fame

    Points: 3407

    In a perfect world MDW would be nice, but there will always be a couple of 2005 and/or 2000 instances floating around...

  • nathaniel.drehmel

    Grasshopper

    Points: 11

    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.

  • nathaniel.drehmel

    Grasshopper

    Points: 11

    @Lian

    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).

    @jim

    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)

  • Eric Heilman

    Old Hand

    Points: 375

    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.

  • dcaughill

    SSC Veteran

    Points: 207

    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

  • Russell Fields-270604

    Old Hand

    Points: 309

    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

  • thadeushuck

    Ten Centuries

    Points: 1367

    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...

  • TravisDBA

    SSCoach

    Points: 15780

    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"

  • Coozie

    Ten Centuries

    Points: 1234

    @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!"

  • RickObsitnik

    Old Hand

    Points: 323

    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 27 total)

You must be logged in to reply to this topic. Login to reply