The purpose of this video is to walk you through how to set up and use and enable the Remote DAC and how to connect and some other just sort of useful information about it. Now, the Remote DAC is important because anyone, if they can RDP into the server, can use the direct administrative connection. The beauty of the Remote DAC is that if for some reason you can’t remote in, you can’t RDP in or BP in or whatever other method you use to connect to the server, you can use the Remote DAC from a different computer and you can get in.

Full Transcript:

Hello, holy cow you’re still here. I’m still Erik, you’re still here. This mug still has no whiskey in it. I’m still with Brent Ozar Unlimited, at least until that mug does get some whiskey in it. We’re going to talk today about the Remote DAC. Now, I know what you’re thinking. Kendra already has a video about the Remote DAC. Well, that’s cool, but Kendra went off to do her own thing and I don’t think that Kendra, for everyone forever, wants to be known as the girl who did Brent Ozar’s Remote DAC video. Now I’m going to be the girl who does Brent Ozar’s Remote DAC video. I know I’m not as pretty or as charming, but I have glasses, so maybe you can just pretend. I don’t know, use your imagination. It’s got to be vivid anyway if you’re here [inaudible 0:00:51.5].

So, the purpose of this video is to walk you through how to set up and use and enable the Remote DAC and how to connect and some other just sort of useful information about it. Now, the Remote DAC is important because anyone, if they can RDP into the server, can use the direct administrative connection. The beauty of the Remote DAC is that if for some reason you can’t remote in, you can’t RDP in or BP in or whatever other method you use to connect to the server, you can use the Remote DAC from a different computer and you can get in. It is a single port, it’s a single thread, very low memory, reserved connection to SQL Server. This is not for you to goof off on your SQL Server with; this is not to make sure your index rebuilds run every night. This is for emergencies, something like if you run into thread pool waits or resource semaphore waits and you can’t connect any other way and you need to get it to [inaudible 0:01:49.1] what’s wrong.

The Remote DAC is always there and always open, even if it’s not enabled. It has to be ready just in case you do. Like I said, remote access matters a lot because you know, if you’re stuck outside of your server and you can’t get in, you don’t have a way to physically get on the box that you can use a regular DAC then it’s necessary to have Remote DAC. So the easiest way to check to see if it’s enabled is to just select from sys.configurations where the name equals remote admin connections. You can see right off the bat, the value you see here is zero. It has a minimum of zero and a maximum of one, so you don’t even have too many choices there. It is not an advanced option, so you don’t have to run sp_configure to turn on – to show advanced options here first. You can just run sp_configure for remote administrative connections. You see here – that I ran the reconfigure statement so it solved, and now if you run this you’ll see the value is one.

Now, the way to connect to the Remote DAC, you don’t want to do it in object explorer. You want to do it with your query, so we’re going to take our query and we’re going to go and change the connection. I’ve already done this to make sure it works, so I don’t look too stupid – too much stupider in front of you on video.

So if I didn’t have the Remote DAC enabled, this would eventually just time up. With it enabled on my really slow crappy CTP VM, it will eventually connect. I get this error message every time but it’s lying to be because down here where it counts, I have the little admin prefix on my connection so I know that I’ve been using the Remote DAC. Now that I’m in, I can get in and I can run something like sp_BlitzWho and I can see what’s running. Going to run that a bunch of times, nothing is running on here now. I know it’s a bit anti-climactic, but that’s just an example. Now, if I go over to this other window that also has sp_BlitzWho ready to run just in case, and I go and I try to change my connection, I also try to get this to connect to the admin connection, it’s going to hang out for a little while and then it’s going to yell at me. See? Connection was successfully established but then an error occurred. Pre-log in handshake, oh my goodness, what’s going on, what happened? One admin at a time, you can’t have multiple people in here.

If we go and we look at the error log – get rid of that embarrassing stuff from the last view. You go and look at the error log we’ll see some errors – we’ll see some messages about what went on in there. Expand, expand, expand, you are taking too long, computer – there we go. Error logs, current, [inaudible 0:04:50.8] server log, probably see all the [inaudible 0:04:53.5] stuff that I did in here before, which was lots of fun. So here we have this first window. Here, could not connect because the maximum number of one dedicated to administrator connection already exists. Now you see this error message in here a few times, I think that’s Intellisense connecting. Intellisense, when you connect with the Remote DAC, it tells Intellisense like, me too, and then Intellisense can’t connect. So you get some other error messages around here.

Keep in mind that the DAC does use port 1434 if it’s available. This can vary depending on your configuration and if you use named instances and all that other good stuff. The SQL Server error log, way down at the beginning – I should have kept that open – will show you the port that you use when it starts up.

So what you need to do is what I just did. Everything in this window. Very easy. Enable the Remote DAC – well, first check and see if it is, if it isn’t, then enable it to one step, one query – you’re going to have to show the administrative connections. Always remember to disconnect because someone else might need to get in there and something and you can’t be sitting there hogging it. And make sure that everyone in your organization is aware of how to use it. Practice using it maybe you know, have everyone sit around and say if there’s ever an emergency, this is how you go about fixing it, this is how you go about triaging it. Then you run the stored procedure and it will tell you all the horrible things that are happening on your server.

If you would like more information about your Remote DAC, head over to BrentOzar.com/go/DAC and there’ll be all sorts of cool information there. Thanks for watching.