Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Browser & DAC


SQL Server Browser & DAC

Author
Message
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16455 Visits: 13207
ako58 (6/13/2012)
The DAC port is assigned dynamically by SQL Server during startup. When connecting to the default instance, the DAC avoids using a SQL Server Resolution Protocol (SSRP) request to the SQL Server Browser Service when connecting. It first connects over TCP port 1434. If that fails, it makes an SSRP call to get the port. If SQL Server Browser is not listening for SSRP requests, the connection request returns an error. Refer to the error log to find the port number DAC is listening on. If SQL Server is configured to accept remote administration connections, the DAC must be initiated with an explicit port number:

sqlcmd –Stcp:<server>,<port>

The SQL Server error log lists the port number for the DAC, which is 1434 by default. If SQL Server is configured to accept local DAC connections only, connect using the loopback adapter using the following command:

sqlcmd –S127.0.0.1,1434


So your copy pasting skills are still intact. What is the point you are trying to make?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
adb2303
adb2303
SSC Eights!
SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)

Group: General Forum Members
Points: 962 Visits: 2886
Koen Verbeeck (6/13/2012)
ako58 (6/13/2012)
The DAC port is assigned dynamically by SQL Server during startup. When connecting to the default instance, the DAC avoids using a SQL Server Resolution Protocol (SSRP) request to the SQL Server Browser Service when connecting. It first connects over TCP port 1434. If that fails, it makes an SSRP call to get the port. If SQL Server Browser is not listening for SSRP requests, the connection request returns an error. Refer to the error log to find the port number DAC is listening on. If SQL Server is configured to accept remote administration connections, the DAC must be initiated with an explicit port number:

sqlcmd –Stcp:<server>,<port>

The SQL Server error log lists the port number for the DAC, which is 1434 by default. If SQL Server is configured to accept local DAC connections only, connect using the loopback adapter using the following command:

sqlcmd –S127.0.0.1,1434


So your copy pasting skills are still intact. What is the point you are trying to make?


+1
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10709 Visits: 12009
Fair question, but it was one of those times when I decided I had to guess what the question meant. Obviously if I know what ports the instances use for their DACs I can connect to them using sqlcmd. Equally obviously I can discover the ports by looking at the SQL Server log for each instance, if I have access and know where it is. I'm one of those people who always has access to the SQL Server log directory for ech instance, and who decides where that directory will be.

But of course that can't be done by a DBA who insists on using SSMS.

I was feeling contrary this morning, so I picked "can't be done", although I know perfectly well that it can, and got my point.

Maybe the author of the question thought you had to have Service Broker to connect through DAC, rather than assuming one of those arrogant developer-hating sql-hating know-nothing DBAs? Well, I have Service Broker disabled and have connected using DAC both to default and to non-default instances (just to make sure I could safely disable service broker).

Tom

Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16455 Visits: 13207
L' Eomot Inversé (6/13/2012)
..., rather than assuming one of those arrogant developer-hating sql-hating know-nothing DBAs?


Hmmm, do I sense some frustration? :-D



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10709 Visits: 12009
Koen Verbeeck (6/13/2012)
ako58 (6/13/2012)
The DAC port is assigned dynamically by SQL Server during startup. When connecting to the default instance, the DAC avoids using a SQL Server Resolution Protocol (SSRP) request to the SQL Server Browser Service when connecting. It first connects over TCP port 1434. If that fails, it makes an SSRP call to get the port. If SQL Server Browser is not listening for SSRP requests, the connection request returns an error. Refer to the error log to find the port number DAC is listening on. If SQL Server is configured to accept remote administration connections, the DAC must be initiated with an explicit port number:

sqlcmd –Stcp:<server>,<port>

The SQL Server error log lists the port number for the DAC, which is 1434 by default. If SQL Server is configured to accept local DAC connections only, connect using the loopback adapter using the following command:

sqlcmd –S127.0.0.1,1434


So your copy pasting skills are still intact. What is the point you are trying to make?

I suspect the point he is attempting to make is that a competent administrator will succede, not fail, because if he knows the port numbers he can connect using sqlcmd. Service Broker is just one option for knowing the port numbers, there are others.

However, the second half of the passage he quotes is utterly irrelevant since the question specifies that the port concerned is not used by SQL. A port is chosen (and logged) by the default instance as well as by the others. It's very poor practise indeed to qote a large chunk of BoL page with no indication at all that it is a quotation and no indication where the quotation comes from.

Incidentally, I think the choice of a Service Broker page as the reference for this question is a bad one; when that page says you can't use DAC it actually means that you can't use DAC if you use SB to get the port numbers. A reference to a page about DAC would have been better.

Tom

heymiky
heymiky
Right there with Babe
Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)

Group: General Forum Members
Points: 761 Visits: 844
Do you mean the SQL Browser Service?
heymiky
heymiky
Right there with Babe
Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)

Group: General Forum Members
Points: 761 Visits: 844
The comment is cut and pasted from the DAC section of BOL also, i'm confused Ermm
heymiky
heymiky
Right there with Babe
Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)

Group: General Forum Members
Points: 761 Visits: 844
This may interest some people, I think the video that goes with it is available on the PASS website http://www.sqlpass.org/, the artical goes into situations when you would use the DAC and what you can do in advance to help yourself.

http://www.brentozar.com/firstresponder/
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10709 Visits: 12009
Koen Verbeeck (6/13/2012)
L' Eomot Inversé (6/13/2012)
..., rather than assuming one of those arrogant developer-hating sql-hating know-nothing DBAs?


Hmmm, do I sense some frustration? :-D

No, not frustration. DBAs like that can, when I meet them, induce not frustration but a very bloody-minded fury. Any frustration I felt about DBAs evaporated decades ago.

Not frustration, because mostly I've been in jobs where I've had control of developer and dba recruitment, so that type could only frustrate me by wasting my time scanning their CVs and I've long recovered from that. :-D Even when I didn't have full control, I usually had a recruitment veto (since the late 70s, anyway, and I didn't meet any DBAs before then) so my main frustration was with my inability to get them to get existing junior DBAs to learn to be competent - that was before I realised that some people are just plain unable to learn technical stuff (and others, like myself, are just plain unable to learn literary criticism or how to be kind to and patient with obstructive red-tape wallahs, despite those abilities being just as important as the ones I have and some others don't).

But know-it-all know-nothing DBAs do exist - and although they don't get a chance to frustrate me they do infuriate me when I encounter them; they hold their jobs by being good at propaganda, at CYA, and denigating anyone who is actually competent as a DBA, and get away with it because too many companies have totally non-technical management. It's entirely analagous to politicians in our democracies - they survive only because the electorate is incompetent at evaluating politicians; maybe someday we will relearn the lessons that the French learnt between the middle of 1787 and September 1793, and the establishment will cease to be a problem; of course that applies to DBAs of a certain calibre, as well as to most politicians.

edit:1793, not 1783. I think it was the 5th when the reign of terror founded on the good Joseph-Ignace's invention is usually accepted as beginning, although the device had been used on occassion before.

Tom

mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4101 Visits: 72512
SQLDBA360 (6/13/2012)
Do you mean the SQL Browser Service?


+1 Smile

Personally I've just started working with Service Broker... I was a little confused, but then I realized he meant SQL Browser Smile



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search