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