DAC

Dedicated Administrator Connection (DAC )

DAC is a special diagnostic connection for administrators when standard connections to the server are not possible.

This diagnostic connection allows an administrator to access SQL Server to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests (i.e. SQL SERVER Crashes).

DAC Setup

 1.  Go to Start -> All Programs -> Microsoft SQL Server 2005 à Configuration tools àSurface Area Configuration

2.  ‘Change Computer’ to point to the server

SQL Server Config

SQL Server Config

3.  Click on ‘Surface Area Configuration for Features’

4.  Go to DAC and Check ‘Enable remote DAC’

SQL DAC

SQL DAC

5.  Click  ‘Apply’

6.  Check the SQL Log to get the port number used by DAC

DAC Port numbers -> SERVER1: 1805, SERVER2: 1434

DAC Usage

a)    Using SQL SERVER Management Studio 

When standard connections to the server are not possible, follow the steps to Query the DB Server using SQL SERVER Management Studio

(Note: DAC is already configured) 

  1. From MS SQL Server Management Studio, Click on New Query
  2. Type the server name ‘Admin:XXXX’ i.e.XXXX= Servername (‘Server1’, ‘Server2’)
DAC  Login

DAC Login

     3.  Click Connect to connect as admin 4.    Query the database 

DAC Query Window

DAC Query Window

 

b)      Using SQLCMD

  1. Go to ‘Command Prompt’
  2. Type sqlcmd –Stcp:<server>,<port> Note: The SQL Server error log lists the port number for the DAC, which is 1434 by default.
  3.  Type the Query and execute
SQL Command Prompt

SQL Command Prompt

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

%d bloggers like this: