Is TDS used only if server is local?

  • Thread starter Thread starter Brett
  • Start date Start date
B

Brett

In order to use TDS via ADO.NET, does the SQL Server need to be on the same
machine?

If not, this will have to involve TCP/IP or named pipes correct? How does
TDS help in that case?

Thanks,
Brett
 
TDS is used by the underlying data drivers and providers. It's the ONLY
"language" that SQL Server speaks so it's always used behind the
scenes--whether your SS is on the local system or on one 70 miles away on
the network somewhere.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
It sounds as though you are saying the driver is used in all cases. On page
706 of 'Programming Microsoft Visual Basic .NET' version 2003, these
ADO.NET drivers are listed:

- OLE DB .NET
- SQL Server .NET
- ODBC .NET
- .NET Data provider for Oracle
- SQLXML Library

In reference to SQL Server .NET Data Provider, here is what the author says,
"This provider has been specifically written to access SQL Server 7 or later
using Tabular Data Stream (TDS) as the communication medium. TDS is SQL
Server's native protocol, so you can expect this provider to give you better
performance than the OLE DB Data Provider."

If TDS is always behind the scenes, why does the author specifically mention
it for SQL Server .NET Data Provider? What is wrong with using the other
drivers vs. this one?

Thanks,
Brett
 
As Bill Vaughn said, TDS is what SQL Server understands!!

Both SQLServer provider and OleDB provider use TDS eventually, but the OleDB
provider has to go through a bunch of complicated interfaces on your local
machine before it gets to TDS - which is why the performance hit.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik
 
TDS will work if the SQL Server is on a different machine, in which case
TCP/IP or named pipes must be used?

Thanks,
Brett
 
TCP/IP or Named pipes is the "envelope" that delivers the TDS to and from
SS. Either work. Each has its own advantages and disadvantages.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Let me clarify a little:

TDS is an "application level protocol", that allows SQL Server server and
clients communicate, ask for execution of SQL statements, specify
parameters, describe result-sets metadata, send rows, etc.

Client and server send and receive TDS packets through some "transport";
typically the client and server TDS parsers don't even know what transport
is being used, that's delegated to a lower layer. That lower layer uses some
transport that's appropriate for each situation - it can be shared memory
(if it's in the same box) or some network IPC such as sockets or named
pipes.

So if you're talking to SQL Server you're using TDS, regardless of the
underlying transport mechanism.

** SQL Server 2005 will support non-TDS communications as well: you can use
HTTP/SOAP to talk to the server. However, client providers still use TDS.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.
 
Pablo,

Thank you. If TDS are the actual data (packats), than how is there any
speed difference between:

1. CFMX accessing SQL Server stored procedures via ODBC SQL Server driver

2. VB.NET accessing SQL Server stored procedures via .NET SQL Server data
provider

In practical terms, how can this really be any faster? Option 2 may provide
10 ms or so right?

Thanks,
Brett
 
In general if you're working on a .NET application we recommend the .NET
Data Provider for SQL Server because it has less layers, that is, the
provider itself talks to the server (using TDS - in fact, the TDS parser
itself is implemented in managed code). If you have a .NET app that uses the
ODBC managed provider + the SQL Server ODBC driver, then you have .NET ODBC
Driver --> ODBC Driver --> TDS, so you end up with an extra layer and all
the marshalling and magic needed to have .NET use ODBC handles, bindings,
etc.

The ODBC managed provider is useful when talking to data-sources for which
there is no managed provider but there is an ODBC driver available.

I hope this helps clarify the issue.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.
 
Pablo,

I understand all the extra work involved. I'm saying practically that it
doesn't really matter. With a good server, the time saved is the less than
the blink of an eye. No one is going to do any better off of 10ms.

Brett
 
Ah, we're not seeing that. Yes, server-side operations do take a significant
amount of time, but because of the one-size-fits-all nature of OLE DB (and
ODBC to a lesser extent), the data access interface often makes clumsy use
of the server by not leveraging "native" methods to perform operations.
SqlClient is a "native" interface--not designed or intended for use with any
other DBMS (not even Sybase). It can use "shortcuts" that an OLE DB
interface cannot use. As a result, applications written against OLE DB tend
to be slower as a function of how many operations they're executing/sec.



--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Even if it was so small, do you have a reason to prefer ODBC to the .NET
provider ?
I would see things the other way round, using the .NET provider unless I
really need something in ODBC...

Patrice
 
The ODBC .NET data provider (unlike the OleDb provider) does not depend on
COM and (in my tests) is 20% faster. However, I do not (ever) suggest it's
use over "native" managed providers if one is available (like SqlClient or
the Oracle and other "real" managed providers that aren't simply wrappers
for their OLE DB providers.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top