Dedicated Administrator Connection

Categories: News, Professional, SSC
Tags: , , ,
Comments: 3 Comments
Published on: January 19, 2012

Recently you may have read my article about some hidden functions in SQL Server.  In that article you learned that those functions were in some DMOs and that you could get at them through the resource database.

Today I found myself learning more about the resource database.  Due to what I had learned in my prior foray into the resource database, I was curious if certain other functions might call some hidden functions in that database.

Sadly – they did not.  But in my travels I did happen across something else that is in that database.  Those items are called system base tables.  Unlike the trio of functions from the last article – you can get to these but it is STRONGLY advised to not do it.

Naturally, I want to check these tables out – especially since the MSDN article does say how to get to them.  I will write about some adventures into looking at these tables in the future.  I already found one interesting thing that seemed odd – but first I will need to login using the DAC and start testing to confirm a hypothesis.

For now, I want to cover how to create a Dedicated Administrator Connection.  This should be something that DBAs know how to do.  It isn’t difficult, and I will only cover one method and leave the other method to the Microsoft documentation.

You can create a DAC through either SSMS or through SQLCMD.  You can create one remotely, but you will need to enable that option since it is disabled by default.  You can find the method for creating this connection via SQLCMD here.

To create a connection through SSMS, it is rather easy as well.  You simply add (case insensitive) “admin:” to the beginning of your server as shown in this image.

In order for this to work, you will need to have the browser service running.  If it is not running, you will get an error message.  This error message is informative if you read it.  It will provide a clue to look at the browser service.

Once you have successfully created this connection, you can now use it when necessary to perform administrative tasks or for some learning opportunities.  If you open a query using this connection you will see something like this next image in your query tab.

You can see in the tab of this query tab that there is the label “ADMIN:”.  This is your DAC connection.  You are limited to one of these at a time – period.

If you try to create a second connection, you will get a nasty message.  The message is not entirely informative – just understand that you are getting it because you already have a DAC open.

It is a good idea to become familiar with how to connect via the DAC.  I have a connection saved for quick access.  Luckily I have a development server which I can test and use for learning opportunities.  As the warning MSDN states: “Access to system base tables by using DAC is designed only for Microsoft personnel, and it is not a supported customer scenario.”  If you venture into the system base tables via the DAC – Microsoft will not support it if you break it.

3 Comments - Leave a comment
  1. […] I am going to skip the part of setting a database into single_user mode because we are presuming that the condition already exists.  To start a DAC session, I am going to point you to a previous article I did on the topic – here. […]

  2. […] I am going to skip the part of setting a database into single_user mode because we are presuming that the condition already exists.  To start a DAC session, I am going to point you to a previous article I did on the topic – here. […]

  3. […] I am going to skip the part of setting a database into single_user mode because we are presuming that the condition already exists.  To start a DAC session, I am going to point you to a previous article I did on the topic – here. […]

Leave a comment

Your email address will not be published. Required fields are marked *










Calendar
January 2012
M T W T F S S
« Dec   Feb »
 1
2345678
9101112131415
16171819202122
23242526272829
3031  
Content
SQLHelp

SQLHelp

  • @Ko_Ver: @NickyvV no, no special tricks in the package :) #sqlhelp
  • @NickyvV: @Ko_Ver I've seen that last week that the Job (step) succeeded, but it actually failed with an error #sqlhelp
  • @NickyvV: @Ko_Ver Is FailParentOnFailure set to false on a task/container maybe? #sqlhelp
  • @Ko_Ver: When I start an Agent job, the "job running" pop-up says there's an error. But SSIS catalog and Agent history says jobs succeed. #sqlhelp
  • @DbaMayor: #sqlhelp I'm seeing LCK_M_U and LCK_M_RS_U waits on sec readonly replica with only reporting queries...anyone faced similar issue?..thnks
  • @TrustMeImADBA: Prob not a good question for #sqlhelp but what is the chances time-service changing the time 23 sec forward made my sql cluster failover?
  • @zippy1981: Seem to be that I need to install SSMS vNext Verifying now #sqlhelp
  • @ChrisAdkin8: @YaHoznaDBA you should also compare avg writelog waits to avg tran log write stall,the two are not necessarily foced to be the same #sqlhelp
  • @toddkleinhans: #sqlhelp Will try using lock priority. Totally forgot about that. Thanks!
  • @DBArgenis: @toddkleinhans Compatibility mode is for T-SQL constructs and Query Optimization, nothing else. #sqlhelp

Welcome , today is Tuesday, February 21, 2017