Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

SQL Server Browser & DAC Expand / Collapse
Author
Message
Posted Wednesday, June 13, 2012 2:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:28 AM
Points: 13,570, Visits: 10,447
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1314923
Posted Wednesday, June 13, 2012 2:53 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:41 AM
Points: 833, Visits: 2,439
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
Post #1314937
Posted Wednesday, June 13, 2012 6:33 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:37 AM
Points: 8,684, Visits: 9,217
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
Post #1315071
Posted Wednesday, June 13, 2012 6:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:28 AM
Points: 13,570, Visits: 10,447
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?




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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1315073
Posted Wednesday, June 13, 2012 6:51 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:37 AM
Points: 8,684, Visits: 9,217
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
Post #1315083
Posted Wednesday, June 13, 2012 6:53 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:19 AM
Points: 728, Visits: 774
Do you mean the SQL Browser Service?
Post #1315085
Posted Wednesday, June 13, 2012 7:02 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:19 AM
Points: 728, Visits: 774
The comment is cut and pasted from the DAC section of BOL also, i'm confused
Post #1315089
Posted Wednesday, June 13, 2012 7:15 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:19 AM
Points: 728, Visits: 774
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/
Post #1315097
Posted Wednesday, June 13, 2012 7:45 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:37 AM
Points: 8,684, Visits: 9,217
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?

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. 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
Post #1315130
Posted Wednesday, June 13, 2012 7:48 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:04 AM
Points: 3,670, Visits: 72,433
SQLDBA360 (6/13/2012)
Do you mean the SQL Browser Service?


+1 :)

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




--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
Post #1315131
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse