Thursday, October 27, 2011

0 Dedicated administrator connections(DAC)

 

Enable for SQL Express

DAC connection is disable by default in sql express. It can be enable via trace flag 7806. There are 2 ways to enable the trace flag. DBCC TRACEON or via sql server start up parameters.

Method 1: DBCC

  1. -- Enable the Trace
  2. DBCC TRACEON (7806,-1)
  3. -- Check the trace flag status
  4. DBCC TRACESTATUS
  5. -- Turn off the trace flag
  6. DBCC TRACEOFF(7806)

image

However, I can not get that working and still see the error message.

Method 2: Trace flag on the start up parameters

Trace flag -T 7806

image

After bonce the sql server, you can see the DAC port being enable in the sql server error log.

SQL Server error log shows the DAC port

image

Lets try to connect to the DAC.

SQLCMD

SQLCMD -S Servername -E –A

image

If you try to connect to the DAC from SSMS, you will get below error message

Dedicated administrator connections are not supported. (Microsoft.SqlServer.Management.SqlStudio.Explorer)

image

That’s because we are trying to connect through object explorer.

The supportive way is

SSMS>New>Database Engine Query > ADMIN:Servername (on the server name)

image

image

To check whether the connection is really using the DAC, you can join the sys.dm_exec_connections and sys.endpoints as below.

  1. select * from sys.dm_exec_connections ec
  2. join sys.endpoints e on (ec.endpoint_id=e.endpoint_id)
  3. where e.name='Dedicated Admin Connection'
image
 
 
Only one DAC is allowed at any given moment.
Below shows the error message if 2nd DAC try to connect.
image
By default, DAC only allow to connect to the sql server locally. To enable the remote connection:
  1. sp_configure 'remote admin connections', 1
  2. GO
  3. RECONFIGURE;
  4. GO
 

All my previous example shows connect sql server using name instance, this will require SQL browser running to allocate the port number for DAC

if you need to connect to DAC via hostname and port number,

1. Remote connection must be enable

2. Must manually find the port number form error log

  1. sqlcmd -S tcp:PO7,51515
 

image

Reference:

How to: Use the Dedicated Administrator Connection with SQL Server Management Studio http://msdn.microsoft.com/en-us/library/ms178068.aspx
Using a Dedicated Administrator Connection http://msdn.microsoft.com/en-us/library/ms189595.aspx

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates