Dedicated Admin Connection

I have not had a case where I've really had to use it yet, but I am glad that it is there (just in case).

I have used it once.  The server in question was set for MAXDOP 0, and a huge, complex query was using all of the processor time.  The DAC let me get in and kill it (and change MAXDOP shortly thereafter!)

I've never had a chance to use it.  A blessing and a curse, really.

I really like this new feature and used it couple of times for testing and once for big real issue...

We have logon trigger enabled on all our prod sql servers...One of my colleague altered the logon trigger script(some testing) and executed on one of prod server that really hung everything..Users were not able to connect to server and no one from DBA group was able to connect to server through SSMS .. always throwing some weird error related to triggers..like "trigger enabled connection not allowed".Googled the error didn't find anything..We saw thousands of error messages in Errorlog in 10 minutes.. then i used DAC to    connect to the server and Disabled the logon triggers on server and that allowed us to connect to server.

Its good with some extents, Whats DAC enhancement in SQL 2008

I used DAC to regain control over a server that was unresponsive.

To practice and get familirized with DAC process (of course in dev/testing) is there any we may simulate a complete server lockdown ?

First thought that comes to mind... set all databases to single user mode?  Or you could start the entire service that way (like when you need to rebuild master)?

Very recently used DAC to connect to an almost completly unresponsive production DBMS (and to help determine the probable cause which turned out to be related to an IO subsystem failure). Quite some while prior to that a DAC has also been somewhat helpful on another occasion in establishing a connection to a memory bound DBMS (sufficient to run various DMV queries to make that assessment at any rate). HTH

"To practice and get familirized with DAC process (of course in dev/testing) is there any we may simulate a complete server lockdown ?"

Thinking out loud:

How about kicking off some weird EXE using xp_cmdshell? Or even start notepad.exe? Do that from enough connections and you should have eaten all "normal" schedulers leaving DAC the only available scheduler...

Last year I used it once to disable some logon trigger working inproperly (it caused that nobody could log on to the instance - but it was only a test environment not a production one).

Is there any other option than DAC to logon to SQL Server, like command line options(sqlcmd or osql), etc. and what is the behaviour vs DAC. DAC is the Admin connection (of course), but how about command line options using SA. I would like to know the QUICK TIP and cons of sqlcmd over DAC(if any)

Mani

>> I would like to know the QUICK TIP and cons of sqlcmd over DAC <<

sqlcmd and DAC are not mutually exclusive. sqlcmd is a tool, DAC is a dedicated administrator connection. You can use the DAC connection either through the Management Studio or through sqlcmd. You cannot use osql to use DAC.

Using the DAC is not like using a normal connection with SA. DAC allows you to connect even if SQL Server is hung.

~Kalen

One cool thing with the DAC is that you can see more deeply inside the resource database using it.

On my servers, I always enable it at install time, but I've never used it in anger (touch wood, fingers crossed).

Помнится, во время подготовки к экзамену по Database Development, прочитал в книге об этой возможности,