The SQL Server (DAC) was created if you are having critical performance issues. The (DAC) can help you research the issue. The (DAC) connects to a separate port by default 1434. The (DAC) port is assigned dynamically by the SQL Server at startup. The SQL Server Browser service also needs to be started. Once the connection is established you can then run queries in case of an emergency.
One example that is normally the case that the (DAC) can help you identify is long-running processes, consuming all the resources. Once all the resources are exhausted you won’t be able to log in normally to investigate. If the (DAC) is enabled, you now have a way to connect and research the issues. If it is a long-running process, you can kill the SPID at that time.
While you research the (DAC) on your systems you will notice that this is disabled by default. Below we will go through the steps on how to check the status by either using syntax or the GUI. Including how to set up.
Let’s get started.
Step 1. In SQL Server Management Studio (SSMS).
- a. Open a new query window
- b. We will now run a query to gather some information not all the columns just what’s relevant.
- SELECT configuration_id,name,value,value_in_use, description
- FROM sys.configurations
- where name = ‘remote admin connections’;
c. You should have something that looks like this. See the value in use is 0 means disabled.
d. Next let’s run the query to enable this option.
- sp_configure ‘remote admin connections’, 1;
- GO
- RECONFIGURE
e. Let’s test and see the configuration again now.
Run this again
- SELECT configuration_id,name,value,value_in_use, description
- FROM sys.configurations
- where name = ‘remote admin connections’;
f. Now you can see how the 2 values should look once enabled
g. This is where you can enable the (DAC) through the GUI.
Right-click on your server name, then in the drop-down go to Facets. Once the window pops up in the Facet: section. You will have a drop-down to select surface area configuration.
H. Here you can see the options and the drop down to enable.
Step 2. You might be wondering; how do we connect using the (DAC)? Let’s go!
When using the (DAC) you can connect a couple of different ways. Either using SSMS or SQLCMD. If you choose to use SSMS. (DAC) uses a single resource and can not be run in parallel. You would select a new query. Then in the pop login box, enter ADMIN:YourServerName and then click ok
If you do have some issues connecting below the URL to Microsoft to check and troubleshoot different scenarios. Every environment is different, just in case you have an issue please reference.
If you are familiar with SQLCMD you can connect to the command line
In Command like you can connect using this command – sqlcmd -S tcp:<server>,<port>
You can show full syntax by running SQLCMS -?
Step 3. Once you are in the query window here are a few of the commands you can use
- Basic DBCC commands such as
- DBCC FREEPROCCACHE,
- DBCC FREESYSTEMCACHE,
- DBCC DROPCLEANBUFFERS Â and DBCC SQLPERF.
- SP_WHO2
- KILL SPID – Note depending on the state of the server, the kill command doesn’t always work. Then you’re left with the only option to restart
- Â the SQL Server
Avoid resource-intensive commands such as DBCC CHECKDB, DBCC DBREINDEX, or DBCC SHRINKDATABASE.
Step 4. If there is a need to see who is connected to the (DAC) you can run the following query. This sometimes occurs that you get an error. Could not connect because the maximum number of ‘1’ dedicated administrator connections already exist. Note that only sysadmins can run this.
You can also verify in the SQL Server Log to see if error 17810 shows up. Then it’s confirmed someone has used it.
This is the query to see who is using the (DAC)
select
CASE
WHEN es.session_id= @@SPID THEN ‘WHO_Is _Using_The_DAC:)’
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’
The result will look like this below:
Conclusion:
We have learned that the (DAC) should be configured before an emergency has occurred. Once the (DAC) is configured, you are ready for any emergency you might need to use this type of access to your SQL Server.
Discover more from SQLYARD
Subscribe to get the latest posts sent to your email.
