SQL Server’s DAC

In this blog post I’m going to talk about the DAC and how you can enable it and how it may just save you from a very bad day if you ever have trouble connecting to your SQL Server.

So what is the DAC?

The dedicated admin connection, or DAC, allows an administrator to connect to an instance of SQL Server 2005 or higher in order to diagnose problems in the event other connections are not being accepted.

Typically this is useful when your server has a ‘run away’ query or process which is maxing out system resources and resulting in time out of connections. The DAC reserves a small portion of these system resources so that you will always be able to connect in and run any queries you need to get the instance back up and working as normal.As the name suggests, you have to be a member of the SQL sys admin group to connect using the DAC.

So how do we enable the DAC?

The DAC can be enabled by simply connecting to the SQL Server instance and running the following command (as either a sysadmin or serveradmin in SQL Server);

EXEC sp_configure ‘remote admin connections’,1

GO

RECONFIGURE

GO

If you are not familiar with sp_configure it is basically used as a method to make configuration changes on your server and the RECONFIGURE command is used to apply the setting changes. Be very careful using it.

You should get a message back that states;

Configuration option ‘remote admin connections’ changed from 0 to 1. Run the RECONFIGURE statement to install.

The DAC is now enabled on the instance of SQL Server.

And how do we connect using the DAC?

So it’s time to connect to you server using the DAC, this can be done in one of two ways, either via SSMS or via the command line.

Connecting via both is easy, all you need to do is prefix your server name with ADMIN:

So assuming you server is called ServerA, in SSMS you would just put ADMIN:ServerA as the server name and via the command line you would just run;

SQLCmd –S ADMIN:ServerA

That’s it, but remember you can only have 1 DAC connection, this isn’t really a problem from the command line but in SSMS you need to do it via File>New>Database Engine Query and entering the server name there (with the ADMIN:) this makes sure only the query window connects and nothing else.

You can check that you are connected to the DAC by using the following DMV to look at the endpoints.

SELECT session_id FROM sys.dm_exec_connections WHERE endpoint_id = 1

The query should return one record in which the session_id matches yours.

I hope you found this post useful. As a standard practice we enable the DAC on our servers but we don’t use them unless absolutely necessary. The overhead is almost zero so to me it’s a no-brainer, get it enabled.

Advertisements
About

I am SQL developer/DBA working in London, I have a passion for all things tech, but my real interests are in databases, mainly SQL Server, Postgres and lately even a little bit of Ubuntu and MongoDB.

Posted in Administration, SQL Server
One comment on “SQL Server’s DAC
  1. will says:

    Definitely believe that which you stated. Your favorite reason seemed to be on the web the simplest thing
    to be aware of. I say to you, I certainly get irked while people consider worries that they just don’t know
    about. You managed to hit the nail upon the top and also
    defined out the whole thing without having side-effects , people can take a signal.
    Will probably be back to get more. Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Recent Comments
will on SQL Server’s DAC

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 3 other followers

%d bloggers like this: