Why do I need DAC if I am able to connect to the server via sqlcmd?

  • RaufDBA

    SSC Enthusiast

    Points: 181

    DAC took my attention at the moment when I was unable to connect to the SQL Server using a traditional way (the reason was deleting logon trigger). I made some searches on the web and it turned out that the only way to access the Server was to use DAC connection which unfortunately was turned off by default. However, I was able to overcome the problem (I removed trigger) using just sqlcmd. My question is if I can connect to the SQL Server just using sqlcmd, what is the purpose of DAC then?

  • Lowell

    SSC Guru

    Points: 323442

    DAC guarantees some memory is held in reserve for an admin to be able to connect,  whether you connect via sqlcmd or SSMS.

    so if the CPU on the server is 100%, and it's taking forever for a regular connection to connect via ssms, that's when the DAC comes in handy. for a trigger like you identified, restarting in single user mode and connecting via sql command is just another tool in the toolbox.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Jeff Moden

    SSC Guru

    Points: 995161

    A lot of people will disagree with me on many fronts but I always keep an active SSMS session running in an RDP session on my production servers.  It has saved my butt on more than one occasion.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Lowell

    SSC Guru

    Points: 323442

    that would only work on a very small environment right? I've got 64 prod servers and nearly 200 total including non-prod, and the number of times in the last year I've had to do a desperate connection, whether RDP to the server, sqlcmd or whatever I can count on a couple of fingers.

    In my shop, we consider DAC to be somewhat of a security risk, so it is disabled on all servers.

    my network is reasonably reliable, so your mileage may vary, of course.

     

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Jeff Moden

    SSC Guru

    Points: 995161

    Lowell wrote:

    that would only work on a very small environment right? I've got 64 prod servers and nearly 200 total including non-prod, and the number of times in the last year I've had to do a desperate connection, whether RDP to the server, sqlcmd or whatever I can count on a couple of fingers.

    In my shop, we consider DAC to be somewhat of a security risk, so it is disabled on all servers.

    my network is reasonably reliable, so your mileage may vary, of course.

    I'll admit that I don't have anywhere near that number of servers so, correct, it may not work for you.

    You've piqued my curiosity a bit... if you have DAC disabled on all servers and something goes haywire on one of them, what do you do?  Restart the SQL Service in the single user mode?  That wouldn't be a bad idea but there could certainly be an important data loss the might rollback when the service restarts.

     

    Disclaimer... I've never had to use DAC because of what I do with my smaller number of servers so I don't know all of it's capabilities in times of trouble.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716659

    Don't know that I'd disable it, but it is only for local connections by default. You can avoid allowing remote access, but still have local access if your server gets buried by a workload.

  • frederico_fonseca

    SSChampion

    Points: 14233

    Agree with Lowell - for small shops having SSMS open may be OK - for larger ones not really feasible. my shop its 250 Prod and 300 non prod - and some of the instances are "small" so having something else eating memory isn't good either.

    All our mission critical servers are clustered - so anything really bad a fail over normally solves it - and the "data loss" isn't big on our case - as those where it would be "important" the data can be reprocessed easily.

    Again I don't remember when I last had to use DAC  - only case that I could have used it was on a dev server - someone set max memory to 128... server fails immediately - and not even sure if DAC would help in this case.

     

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716659

    If the server is started, the DAC helps. If it fails to start, you'd need to use a different switch (-g?) to fix memory.

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21197

    Jeff Moden wrote:

    A lot of people will disagree with me on many fronts but I always keep an active SSMS session running in an RDP session on my production servers.  It has saved my butt on more than one occasion.

    wanna try that when your sysadmin enables an overnight service pack? you get shouted at for blocking it

    MVDBA

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21197

    I have an application from a 3rd party vendor (a network monitoring tool) that uses the dac all of the time - if I need to use the dac then I have to kill their connection. (only 1 dac at a time)

    I've tried to explain this to them , but they ignore me

    MVDBA

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

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