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

Question

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

RaufDBA

Old Hand

Points: 369
More actions
December 2, 2019 at 1:25 pm
Go to Answer
#3703404

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: 323518
More actions
December 2, 2019 at 1:44 pm
Answer
#3703416

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!

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

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

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

Lowell SSC Guru Points: 323518 More actions · Answer 2

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: 1004363 More actions · Answer 3

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.

Change is inevitable... Change for the better is not.

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

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 4

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 SSCoach Points: 16155 More actions · Answer 5

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: 736270 More actions · Answer 6

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: 21797 More actions · Answer 7

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: 21797 More actions · Answer 8

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