message format request for 1434

Question

the 9 SQL open specifications are a very small part of the whole proprietary SQL landscape
- even more so if you realise that 3 belong to MS Office and have no SQL content !

my current interest is to discover the formats of request and response to 1434 poll (to SQL Browser)
- ie UDP broadcast out and UDP reply from [unhidden] instances

FYI the reason is that I am frustrated by the SMO (and hence SQLCMD -Lc) lists that are atrocious to identify instances
- typically the server name is shown (eg MYSVR) and not the instances (MYSVR\BKUPEXEC, MYSVR\MSSERVER [default], etc)

By using Network Monitor (plaudit to MS for making available to non-SMS audience) I can see responses include protocol+port, version etc [even without a login to the instance(s)], but a formal definition would be appreciated

I had hoped that SQL2008 would be better at surfacing such riches, but without this I propose to write to network API to request and handle responses.


As this is one of the few (I only found another ONE!) targetted to this forum, hopefully MS will address this request properly and quickly

TIA
Dick

Answer 1

Dick,

The Protocol Team has received your inquiry. We are investigating this and will follow-up with you shortly.

Dominic Salemno

SENIOR SUPPORT ESCALATION ENGINEER

Answer 2

Hello Dick,

 

Thank you for your question.  This forum is specifically for questions related to the Open Protocol Specifications Documentation.  At this point I am not clear which Open Specification Document you may be referring to, http://msdn.microsoft.com/en-us/library/dd208104(PROT.10).aspx  However, if I understand your issue correctly you are asking how to enumerate/identify instances of SQL Server.

 

This reference may assist with how do accomplish that goal: Enumerating Instances of SQL Server (ADO.NET) through SqlDataSourceEnumerator Class, http://msdn.microsoft.com/en-us/library/a6t1z9x2.aspx  However, note this sample is dependent on the SQL Browser service.

 

An alternative is to use WMI, as detailed on this blog, http://blogs.msdn.com/sqlexpress/archive/2006/07/29/faq-detecting-sql-server-2005-using-wmi.aspx

 

You mention problems attempting to do this with SMO, though it should be possible, and this forum may be helpful, http://social.msdn.microsoft.com/Forums/en-US/sqlsmoanddmo/threads/

 

SQL Server 2008 Books Online (BOL), SQL Server Management Objects (SMO) http://msdn.microsoft.com/en-us/library/cc285859.aspx

 

Regards,

Mark Miller

Escalation Engineer

US-CSS DSC PROTOCOL TEAM

Answer 3

thanks for your answer and URLs

I have found that this one (not in your list) was the most helpful
http://sqlblogcasts.com/blogs/jonsayce/archive/2008/02/10/programatically-listing-sql-servers.aspx

which implies that developers have to do heavy lifting (SMO+WMI+registry) that really belongs in the MS-supplied assemblies
- please feed that back to product teams for future enhancement request to simplify our increasingly-convoluted lives

The particular manifestation is that SQLCMD -L (or -Lc) is also flawed and does not correctly reflect local or remote instances
- I am not talking about UDP/timeout/offline instances, but the ones that DO answer the call

e.g. I have 2 named instances (no default instance) on my local box and get just the name
- this looks like the default instance (aka MSSQLSERVER if I remember correctly)

thus whatever mechanism that SQLCMD uses should be beefed up (as per top URL) meantime

I am relieved that I will not have to resort to [UDP] network calls myself, and can attempt all the various sensing within SMO etc
- thus don't need this particular forum [OpenSpecs] to keep this thread open [but DON'T forget to hassle SQL team!]

thanks
Dick

Answer 4

Hi Dick,

I am glad that worked out for you.  Thank you for your feedback.

Regards,

Mark Miller

Escalation Engineer

US-CSS DSC PROTOCOL TEAM