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

  • @jdanton: @mobileck It 100% depends, but on 2014 by default I think it’s 3 MB (log+data). But will orders (100s-1000s) more on busy system #sqlhelp
  • @mobileck: Anyone know the minimum amount of space tempdb will use on a new instance (including system objects)? Seeing 2.75MB on 2014 #sqlhelp
  • @SQLSoldier: @MidnightDBA SMO via .Net. Why no PowerShell. PowerShell works with 2008 +. #sqlhelp
  • @MidnightDBA: Server: No SSMS, no powershell, SQL 08 r2. Best TSQL script to create sp_add_job statements, et al, for all jobs on the instance? #sqlhelp
  • @AMtwo: @ajneuman AFAIK, the only way to upgrade is to point SSRS at the DB, and let it do it's thing. What error do you get on upgrade? #sqlhelp
  • @ajneuman: Is there a way to manually upgrade the Report Server databae? It's failing to upgrade from version 147 to 174. #sqlhelp #SSRS #sqlserver2016
  • @AngryPets: @SQLYard #sqlhelp Nope - they just need to be sized to handle the indexes on them. I'd check file stalls vs all files to see if helping/etc.
  • @SQLPrincess: #sqlhelp Any ideas on how to reverse engineer a data flow diagram from queries to show the data transformations?
  • @SQLYard: @AngryPets Thnks so the 3 filegroups different sizes are normal then? I was thinking they needed to be aligned in size. #sqlhelp
  • @AMtwo: @SqlRyan If you want to use source control with your report designer, use Visual Studio + SSDT. #sqlhelp

Welcome , today is Saturday, October 1, 2016