SQL Homework – May 2019 – SQLCMD and the DAC

5

May 1, 2019 by Kenneth Fisher

Yes, it’s that time again. Time to do your homework. This month your homework is to set up the DAC (dedicated admin connection) for remote access and practice with it using SQLCMD.

For those of you that don’t know the DAC is a single user connection that has a dedicated set of resources. What this means is that when your instance is sluggish or even unresponsive the DAC may very well be able to connect and you can run diagnostic queries and possibly even fix things. However, in order to do that you need practice!

  • Turn on remote DAC connections.
  • Learn why SSMS can be somewhat problematic when using the DAC.
  • Use SQLCMD to connect using the DAC.
  • Run a few diagnostic queries.
    • What’s trying to run right now?
    • What’s the memory usage on the instance?
    • What queries are using the most memory?
    • Same with CPU.
    • Anything else you can think of.
  • In another session check what session has the DAC open and who’s doing it. (You’d be surprised but I’ve seen people open the DAC for no particular reason.)
  •  
    I want to point out that this one is particularly important. You won’t use the DAC often but if you need it your instance probably has some pretty significant issues. Given that it’s a single connection you won’t be able to use a lot of the tools you are used to. For most of us, this is going to require practice. Possibly a lot of it. And you really don’t want to take time to practice while your users are screaming about an outage.

    5 thoughts on “SQL Homework – May 2019 – SQLCMD and the DAC

    1. Max Vernon says:

      Great article, Kenneth. I was surprised to see Microsoft recommending to disable DAC access from the network. Seems to be the entire point of having it on is so you can access SWL Server when the server itself is unresponsive! I wrote a post about it at https://www.sqlserverscience.com/security/vulnerability-assessments-recommends-disabling-remote-admin-connections/

      • Max Vernon says:

        *SQL obvs, not SWL. Damn mobile.

      • Great post. I wasn’t aware of that. I think I’m with you on this one. The primary benefit of the DAC is that it has dedicated resources. I can’t think of how someone would connect using the DAC and cause more problems than they would just connecting normally. Not to mention the fact that if my SQL Instance is unresponsive and I need the DAC, I’m probably not going to be able to remote to the server easily anyway.

        • Max Vernon says:

          Cheers… it seems weird that they’d recommend having the remote DAC disabled. Aside from looking at the odd undocumented feature, literally the only times I’ve relied on it is when the server is 100% CPU-bound, and it’s the *only* thing that will respond!

          • Exactly! And to the best of my knowledge the only thing the DAC can do that a regular account can’t is look at the system tables. Which you can’t edit anyway.

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out /  Change )

    Google photo

    You are commenting using your Google account. Log Out /  Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out /  Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out /  Change )

    Connecting to %s

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    Enter your email address to follow this blog and receive notifications of new posts by email.

    Join 2,513 other followers

    Follow me on Twitter

    ToadWorld Pro of the Month November 2013
    %d bloggers like this: