Post reply

Advice & Hints For Rising SQL Server DBA

  • January 31, 2020 at 7:35 am

    #3721901

    I'm with SQL Server since 5 years in a small environment (5 SQL Server Instances 2014-2016, 20 DBs, small servers) and acquired some SQL Server certifications.

    Luckily I got a very interesting job offer to become DBA of a 300 Instances (2005 - 2019) with round about 1000 DBs (there is a 12 TB DW). My question to the veterans and more experienced DBAs:

    1. What is in your opinion the biggest challenge when moving to such a big SQL Server environment?
    2. What are the hints you would wish to have been told, when you "entered the arena" first time?

  • January 31, 2020 at 10:16 am

    #3721940

    1. Get everything off of the older versions of SQL Server as fast as possible. 2008 and less are all out of support and that means that your data is at risk on those platforms. This will prove hyper painful and the business will resist because they don't see a problem. Is it on fire? Nope. Spend your time elsewhere til it is. Yet, when it's on fire, it'll be bad. So you've got to have the fight with them about technical debt, and that's not a fun fight.
    2. Really knowing how to do recovery. Yeah, yeah, I did it once and it worked... sort of. So, I'm good. Then, when the real emergency happens and your cublicle is filled with managers (because they never stand outside of it, they seem to want to watch your every key stroke), you're trying to lookup syntax under pressure and it's a nightmare. So, lots of practice in recovery. At least once every couple of weeks, minimum.

    Hope that's helpful. I'm sure others will have input and feedback as well.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • January 31, 2020 at 10:54 am

    #3721953

    Thanks, that's indeed very helpful.

    Let me add some remarks:

    1. Yes, I fully agree. Actually one reason for hiring me was to get rid off all the < 2016 instances, an another was to consolidate theses 1000 DBs on approximately 100 instances.
    2. I think (and this includes me as well), this is the most underrated topic. I will work on this.

  • January 31, 2020 at 1:59 pm

    #3722006

    @@caffeine_level wrote:

    Thanks, that's indeed very helpful.

    Let me add some remarks:

     

      <li style="list-style-type: none;">

    1. Yes, I fully agree. Actually one reason for hiring me was to get rid off all the < 2016 instances, an another was to consolidate theses 1000 DBs on approximately 100 instances.

     

      <li style="list-style-type: none;">

    1. I think (and this includes me as well), this is the most underrated topic. I will work on this.

    don't talk to me about consolidation to me at the minute 🙂

    my advice (on top of grant's very solid advice) is pro-active monitoring, you cannot log into 1000 servers every day. so script up a job and deploy it everywhere that sends you all the info you need (backups worked? sql agent job worked? etc etc)

    if it's 1000 servers (I guess a retail environment) then build a web page and show the key failures - you do not want 5000 emails every day... it fatigues you and you start ignoring it.

    I used to work for a global casino and gambling brand.. every reception device and cash device was treated like a server - no way you can monitor replication, backups and error logs  with email

    MVDBA

  • January 31, 2020 at 3:14 pm

    #3722041

    MVDBA (Mike Vessey) wrote:

    @@caffeine_level wrote:

    Thanks, that's indeed very helpful.

    Let me add some remarks:

     

      1. <li style="list-style-type: none;">

     

        1. Yes, I fully agree. Actually one reason for hiring me was to get rid off all the < 2016 instances, an another was to consolidate theses 1000 DBs on approximately 100 instances.

     

     

     

      1. <li style="list-style-type: none;">

     

        1. I think (and this includes me as well), this is the most underrated topic. I will work on this.

     

    don't talk to me about consolidation to me at the minute 🙂

    my advice (on top of grant's very solid advice) is pro-active monitoring, you cannot log into 1000 servers every day. so script up a job and deploy it everywhere that sends you all the info you need (backups worked? sql agent job worked? etc etc)

    if it's 1000 servers (I guess a retail environment) then build a web page and show the key failures - you do not want 5000 emails every day... it fatigues you and you start ignoring it.

    I used to work for a global casino and gambling brand.. every reception device and cash device was treated like a server - no way you can monitor replication, backups and error logs  with email

    When it comes to that, I agree with Mike, but I'd suggest buy vs. build. Yes, third party monitoring can be expensive, but what you're paying for is time. Time to spend on other things. And yes, I work for a vendor (the best one). Doesn't mean I'm wrong.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • January 31, 2020 at 3:44 pm

    #3722062

    "if it's 1000 servers" -> No, it's only round about 300 servers.

    "so script up a job and deploy it everywhere that sends you all the info you need (backups worked? sql agent job worked? etc etc)" -> I consider to build up an automation using dbatools to manage the herd.

    "Yes, third party monitoring can be expensive, but what you're paying for is time. Time to spend on other things. And yes, I work for a vendor (the best one). Doesn't mean I'm wrong." -> I'm open to that.

  • January 31, 2020 at 4:08 pm

    #3722070

    @@caffeine_level wrote:

    "if it's 1000 servers" -> No, it's only round about 300 servers.

    "so script up a job and deploy it everywhere that sends you all the info you need (backups worked? sql agent job worked? etc etc)" -> I consider to build up an automation using dbatools to manage the herd.

    "Yes, third party monitoring can be expensive, but what you're paying for is time. Time to spend on other things. And yes, I work for a vendor (the best one). Doesn't mean I'm wrong." -> I'm open to that.

    I don't work for a vendor but Redgate has a great suite of tools

    for job tracking, though ...I still would build my own, msdb should give you a bunch of stuff.. then combine it with what Redgate sql monitor can do.

    I used 1000 servers as an example - but I would warn you (Grant, please don't tell your sales guys that I said this), licencing for 300 servers would be a bit costly (for any 3rd party product)

    MVDBA

  • January 31, 2020 at 7:07 pm

    #3722121

    MVDBA (Mike Vessey) wrote:

    @@caffeine_level wrote:

    "if it's 1000 servers" -> No, it's only round about 300 servers.

    "so script up a job and deploy it everywhere that sends you all the info you need (backups worked? sql agent job worked? etc etc)" -> I consider to build up an automation using dbatools to manage the herd.

    "Yes, third party monitoring can be expensive, but what you're paying for is time. Time to spend on other things. And yes, I work for a vendor (the best one). Doesn't mean I'm wrong." -> I'm open to that.

    I don't work for a vendor but Redgate has a great suite of tools

    for job tracking, though ...I still would build my own, msdb should give you a bunch of stuff.. then combine it with what Redgate sql monitor can do.

    I used 1000 servers as an example - but I would warn you (Grant, please don't tell your sales guys that I said this), licencing for 300 servers would be a bit costly (for any 3rd party product)

     

    We do this even in my relatively small.. to medium sized environment.. (62 instances ~800 databases); we have kind of a home grown reporting system with a central database on each instance where we gather all kinds of auditing, performance, and backup information and use SSRS reports to view data when necessary. We do also use Redgate SQL monitor, but it is nice having something that you've built yourself and intricately understand.

  • February 3, 2020 at 9:35 am

    #3722497

    SSRS - ouch - painfully slow

    get your web server running some basic MVC code and stick a data grid on screen for failed job history 🙂

    MVDBA

  • February 3, 2020 at 9:38 am

    #3722498

    MVDBA (Mike Vessey) wrote:

    SSRS - ouch - painfully slow

    get your web server running some basic MVC code and stick a data grid on screen for failed job history 🙂

    if as a DBA I can call a world of tanks API and get my clan stats in XML and then put it on grid using MVC then i'm sure all of you smart youngsters can do it better

    MVDBA

Viewing 10 posts - 1 through 9 (of 9 total)

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