SQL Server Accidental DBA Skills

  • Hi all. I'm not sure the best place to post this, so let me know if there is a more appropriate forum area.

    I was hired internally about a year ago to work on reporting and integrations at small to mid-sized company. I don't have a background with databases, but I know the company well and I generally pick up on tech pretty quickly. My predecessor left the database systems up and running just fine with maintenance, various jobs, and backups in place. In the last year, I've gotten pretty far learning my way around our data and training myself in T-SQL, SSIS, and SSRS, producing pretty solid integrations and reports as they are requested.

    However, we don't have a dedicated DBA, and because I'm the one with my hands in the database, I believe it will fall to me if there's ever a meltdown or if I ever make a careless mistake and wipe out a table.

    I'd like to lessen that background anxiety and get some confidence in handling sql server beyond getting the data back out and to the right people. As far as I can tell, the best way to do that would be to learn my way around the system: msdb, roles, indexes, system tables, all that stuff I don't really need to mess with day to day but will be crucial to understand in a crisis.

    Can anyone recommend good resources for a beginner to learn SQL Server 2012 under the hood? I've tried a few "Accidental DBA" resources, and I tend to find them still a little over my head. I'd like to get a strong sense of the fundamentals and figure out the best way to prepare for how to troubleshoot and resolve some looming crisis.

    Thanks in advance for any help.

  • A 'sand pit' system, where it doesn't matter what kind of mess you make is a first recommendation. It can be either on your machine or another server (VMs are ideal for such a thing). Then I would consider Pluralsight - there's a wealth of SQL Server articles on there for various levels of experience. They do have a free trial period, so you can see if that is suitable.
    There's a good Stairways series on this site, which also caters for all levels.
    The basics are common across most versions of SQL Server, so don't get too hung up on just looking at 2012 articles.
    And ask questions here, just make them clear and show what your investigations were in relation to it.

  • I found Brent Ozar's blog invaluable when I was starting out (actually, I still do). They used to offer a 6 month DBA training plan pdf that was very helpful. You have to register for it so I'm not sure if it's still available, but take a look.

    https://www.brentozar.com/subscribe/

    https://www.brentozar.com/blog/

    Kendra Little also offers loads of really good resources and blog posts: https://sqlworkbooks.com/

  • zech 55624 - Thursday, August 30, 2018 9:58 AM

    Hi all. I'm not sure the best place to post this, so let me know if there is a more appropriate forum area.

    I was hired internally about a year ago to work on reporting and integrations at small to mid-sized company. I don't have a background with databases, but I know the company well and I generally pick up on tech pretty quickly. My predecessor left the database systems up and running just fine with maintenance, various jobs, and backups in place. In the last year, I've gotten pretty far learning my way around our data and training myself in T-SQL, SSIS, and SSRS, producing pretty solid integrations and reports as they are requested.

    However, we don't have a dedicated DBA, and because I'm the one with my hands in the database, I believe it will fall to me if there's ever a meltdown or if I ever make a careless mistake and wipe out a table.

    I'd like to lessen that background anxiety and get some confidence in handling sql server beyond getting the data back out and to the right people. As far as I can tell, the best way to do that would be to learn my way around the system: msdb, roles, indexes, system tables, all that stuff I don't really need to mess with day to day but will be crucial to understand in a crisis.

    Can anyone recommend good resources for a beginner to learn SQL Server 2012 under the hood? I've tried a few "Accidental DBA" resources, and I tend to find them still a little over my head. I'd like to get a strong sense of the fundamentals and figure out the best way to prepare for how to troubleshoot and resolve some looming crisis.

    Thanks in advance for any help.

    If you need to ramp up quickly, then have the company get you some training, especially if you find the "Accidental DBA" resources to be over your head.  It'll be worth it when "it" happens.

    As Beatrix suggests, you also need to develop a sand box that you can learn with (SQL Developer Edition, which is really the Enterprise Edition with special licensing as is FREE) without fear of messing something up.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • The basics for a DBA are: disaster recovery, security, and connectivity. 

    The first thing I would do is confirm that backups are scheduled. You want to at least have a weekly full backup with daily differential backups. Naturally, the backup files should be maintained in a storage system separate from where your database files are located. Also, you want to have all your transactional databases (those where users are performing inserts/update/deletes) in Full recovery mode, so transaction logs are retained between backups. With Simple or Bulk recovery mode transaction logs are periodically truncated, which conserves disk space but limits your ability to perform point in time restores. Next, learn how to restore from differential, full, or point in time backups.

    Next, confirm that user and application accounts are not in the sysadmin or db owner roles.

    Next, learn what all those scheduled jobs in SQL Agent are doing and monitor them.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Thanks for the replies. Backups are on a rigorous schedule, and I've done one or two restores in a test DB. Our Test database server is also used by a development partner for ERP customizations, so I can't just play in there sandpit style as I like. Perhaps another copy of the DB is in order.

    The Brent Ozar 6 mth plan was a terrific suggestion. I found it and it looks like a great way to ease in with the highest priority issues first. In case anyone else comes looking for it, I found it in the First Responder kit offered as a free download on the site: https://www.brentozar.com/first-aid/

Viewing 6 posts - 1 through 5 (of 5 total)

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