The dedicated admin connection (DAC) can help you out of a sticky situation. This was built to help you connect to SQL Server and run basic queries in cases with critical performance problems. This works by telling SQL Server to reserve a thread specifically for processing your queries in an emergency. While it does reserve a connection for you, it is only one thread, there is no parallelism happening here, in fact, you will receive an error.
Typically, what ends up happening and where it can help is when your SQL Server is screaming for resources, but there are none are available. In my experience, this has been related to long-running queries that are not optimized for data the end user wants to return or your SQL Server not allowing new connections. With the DAC you are now able to find the long-running query and kill that session or troubleshoot the issue.
How to enable DAC for remote connections
When first learning about DAC, you will quickly see that it is enabled by default, however, it is only for connections that are originating from the local system. Technically speaking this is because out of the box it is only listening to the local loopback IP (127.0.0.1). This means that if your server is having problems you will need to connect to the instance first and then connect to SQL Server. This can be a real time waster while your boss is potentially standing behind you.
To allow remote connections and alter it listening on only the local loopback to essentially all IP addresses on the local machine, use the code below.
|
1 |
sp_configure 'remote admin connections', 1; |
However, you will also need to run a RECONFIGURE command, the reason I have these separated is that this command will apply all pending changes. It will be best practice to check nothing else is pending first. This can be checked by running the following code. highlight the RECONFIGURE and run just that command after you confirm no other changes.
|
1 2 3 4 |
SELECT * FROM sys.configurations WHERE value <> value_in_use; --RECONFIGURE |
Confirm it is enabled by running a netstat -aon on the SQL Server, I piped this to a findstr command to eliminate the extra port information. The picture below shows both before and after running the commands above. Details around this are documented by Microsoft here.
I will add, that you may have to add a new firewall rule to allow you to connect to SQL Server on this new port since most servers will not have it. Additionally, in that firewall make sure you lock down the source IPs for that connection. This will allow only the IPs you configure in the rule to connect which is especially handy if your computer has a static IP, otherwise, I would suggest putting in a range for the department/office you are in.
How to connect to DAC
The DAC can be connected using SQLCMD or SSMS. To start with SSMS, specifying that you are using the DAC connection on the instance will require you to prefix your instance name with “ADMIN:”. For example, my machine would be ADMIN:win-remote. But, it will not work with object explorer connections on the left-hand side. If you attempt, it will throw the error below. This trips many people up as it clearly says it is not supported, but it is.
Dedicated administrator connections are not supported via SSMS as it establishes multiple connections by design (Microsoft.SqlServer.Management.SqlStudio.Explorer)
Instead, start a new query window, right click on a blank spot in the query window and in connection, select either connect or change connection. From here, you will be presented with the connection pane however, this will only initiate a single connection which the DAC allows.
I find connecting to the DAC easier, but you also will lose some of those niceties. However, what you gain is the ability to change your database you are connecting to by default. This can be huge as it will allow you to bypass a limitation discussed earlier.
To connect to the DAC through command prompt run the following command: (These letters for parameters are case sensitive)
sqlcmd -S {Servername} -U {username} -P {password} -d {databasename} -A
To show the full syntax summary run SQLCMD -?
Details on running SQLCMD queries can be found here.
Finding out who is connecting using DAC
It is important to note that your DAC can only be used by one sysadmin at a time. If you run into a problem connecting at an emergency, it is important you check the error logs for not only potential causes of the issue you experienced but also to see if your DAC was in use. This can be confirmed by the SQL Server log showing error 17810
Message
Could not connect because the maximum number of ‘1’ dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process.
To find out who is using the DAC port you can use this query.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
select CASE WHEN es.session_id= @@SPID THEN 'you are :)' ELSE es.host_name END AS Who_is_running_DAC, es.original_login_name, es.session_id, es.login_time, es.status from sys.endpoints as ep join sys.dm_exec_sessions es on ep.endpoint_id=es.endpoint_id where ep.name='Dedicated Admin Connection' |
This will give you a feed out, if you are running the DAC or what the hostname of the machine that is, along with a few other details of the session. Once I have connected to the DAC I was able to return the following results.
Restrictions
To go over a few more common limitations of the dedicated admin connection.
- If your user’s default database is offline the DAC will fail to connect. The connection will return error 4060. This can be overcome by connecting to the master database with SQLCMD syntax.
- Only members of the SQL Server sysadmin role can connect using the DAC.
Full restrictions and documentation can be found on Microsoft here.
To summarize, I believe the DAC can be a useful utility for an emergency. However, you will need to configure this ahead of time and make sure you understand how to connect and utilize the functionality.
FAQs
What tasks can be run through the Dedicated admin connection?
Very few tasks can be run as it is not designed for parallel workloads.
Can you connect to the Dedicated admin connection through SSMS?
Yes, however you cannot use object explorer, see above on steps to connect.
How can you see who is using your dedicated admin connection?
There is a query you can run against sys.endpoints and sys.dm_exec_sessions to get active DAC connections.
My curious nature leads to me learn about different methodologies for accomplishing tasks more efficiently without compromising on the quality. This does tend to lead one down the rabbit hole, but it often ends in valuable experience that I enjoy sharing with anyone willing to take the time. You can find me on LinkedIn
- Preparing for SQL Server Database Corruption; initial reaction and analysis - February 6, 2019
- Considerations and concerns when setting up Database mail for SQL Server - August 31, 2018
- SQL Server Setup – Instant File Initialization (IFI) - August 31, 2018