How do I know what requires DAC?

Question

Playing around with SQL Server, I came across a semi-cryptic error. Essentially, I only need to log into DAC. Is there any general advice for when DAC is needed? How do I know I need it to access sys.sysobjvalues without googling for the error message?

What all does DAC provide access to?

Answer 1

DAC (Dedicated Admin Connection) is (a metadata tool) only use for troubleshooting purposes. This is used by SQL Server DBA as a last resort if you can't access or connect to SQL Server. There are some cases wherein a process or someone tries to use all your system memory and CPU and nobody can use SQL Server (no new processes can connect).

DAC was built in SQL Server 2005 because prior to SQL Server 2005, if we can't access/connect to SQL Server, we either kill all the sessions and restart SQL Server. In SQL Server 2005, Microsoft decided to create DAC for troubleshooting purpose (backdoor).

When we connect as DAC (special connection), we got dedicated processor (scheduler) and a reserved memory to run a simple queries. We need a sysadmin permission to connect. We can only connect locally by logging in to the local server and connect (except when you need to troubleshoot cluster or AGs that needs remote DAC). Only 1 session that can connect as DAC in SQL Server and no one can connect as DAC until we disconnect our DAC session.

We can check if someone is using DAC by querying:

SELECT dese.session_id
FROM sys.tcp_endpoints as te
JOIN sys.dm_exec_sessions as dese ON te.endpoint_id = dese.endpoint_id
WHERE te.name ='Dedicated Admin Connection';

What can we do when we are connected as DAC:

1. access to hidden system tables (like sys.sysobjvalues)
2. run simple queries for troubleshooting
3. advanced troubleshooting (like repairing corrupt system table pages)

What we can't do:

1. run complex queries (parallelism is prohibited)
2. backup/restore operation

We can't query systems tables in normal connection.

Try

SELECT * FROM sys.objects WHERE type_desc='SYSTEM_TABLE';

. These are the tables that you can't query in a normal connection.

When we are connected as DAC, you can access systems table (eg. sys.sysrscols)

Lastly, as a best practice always always disconnect the DAC connection after use.

I would recommend using SSMS and read the book about internals of SQL Server and Troubleshooting SQL Server. It's not SQL Server 2017 specific but most of the concepts are still applies to current version of SQL Server.

From Microsoft:

Important: The system base tables are used only within the SQL Server Database Engine and are not for general customer use. They are subject to change and compatibility is not guaranteed.

Access to system base tables by using DAC is designed only for Microsoft personnel, and it is not a supported customer scenario.