http://edge.technet.com/Media/Real-World-DBA-Episode-18-Connecting-to-SQL-Server/

This week:

In the news, the SQL Server 2008 Feature Pack for RC0 is now available.

In this week’s feature, we’ll talk about accessing SQL Server.

The web link is on deciding to use SQL Server or Microsoft Access,

and the tip this week deals with data providers.

 

In the News:

In the news, the SQL Server 2008 Feature Pack for RC0 is now available. This pack includes lots of add-ins for SQL Server 2008 RC0, including Backward Compatibility components and Data Mining add-ins and much more.  Find more at http://tinyurl.com/6kjc9w.

 

Feature:

Whenever you connect to a SQL Server database, two broad technological areas are involved. One is the transport, and the other is the programmatic access. Let’s take a quick look at what each of these areas is responsible for. I’m focusing on SQL Server 2005 in this Podcast, since each version has software differences to handle each area.

 

We’ll keep the discussion simple and describe the process from the viewpoint of a client application, a connection to a SQL Server Instance, and the SQL Server Instance itself. Starting at the client end, the transport needs a way to “talk” to a server. Even if the SQL Server Instance is installed on the same system, the client uses a client network library to send and receive information. You can install this software from the SQL Server installation media, but you also have the connectivity built in to a Microsoft Operating System. I’ll explain more about that in a moment.

 

There are four methods that the client network library can use to send information to SQL Server. The first is the TCP/IP protocol, which is in use for most networks today. A TCP/IP network involves a set of unique numbers that identify each “host” or computing device on the network. You can learn more about it at http://tinyurl.com/2wkx. 

 

A client can also use the “Shared Memory” protocol. This is useful when the client and the server are on the same hardware and don’t need to communicate across a network, or the system doesn’t contain a network interface hooked externally.

 

SQL Server 2005 clients can also use the Named Pipes protocol, which is a legacy network protocol used in some older networks. In general, it’s better to use TCP/IP on Microsoft networks, but there are times when the Named Pipes protocol can be useful. You can read more about it at .

 

Finally, you can also choose the Virtual Interface Adaptor, or “VIA” protocol, which uses a specific card address and a port on that card. You can read more about that protocol at http://tinyurl.com/636rny.

 

A client machine with the SQL Server tools can enable a particular protocol and set the order of precedence for each one to talk to the server using the Configuration Manager. Once you set up the client, you’ll need to ensure that the network functions between the client and the server.

 

On the server side, there is a set of network libraries as well. In some editions of SQL Server, the protocols need to be enabled. You can do that using the SQL Server Configuration Manager tool. This is the number one issues for connectivity – since SQL Server is shipped “secure by default”, sometimes these protocols aren’t enabled on the server, so the client can’t reach it. Always check the Configuration Manager tool to ensure you have enabled the protocols you’ll be using.

 

An interesting note is that the server network protocols don’t have an order. This makes sense, because whatever the client protocol is determines what the server will use for that connection. The order there isn’t important to the server, it just answers the protocol.

 

Inside the network packets between the client and the server, you’ll find the Tabular Data Stream, or TDS.  This is an application-layer protocol used by SQL Server for data.

 

So that’s the transport area for connectivity. To enable applications to format the data and requests to be put on the network wire (or memory path), you need programmatic layers on the client.  There are two major types of software libraries, or groups of code, that do this: the ones that come with the operating system, and the libraries provided by SQL Server or any of the programming languages from Microsoft.

 

All of the modern Microsoft operating systems come with a set of libraries that you can use in code to communicate with SQL Server. These are not as “rich”, or complete, as the ones that come with SQL Server or the programming language installations. You’ve probably seen these drivers in the “ODBC” icon of the control panel. ODBC, by the way, stands for Open Database Connectivity. For a while, SQL Server shipped “replacements” for these drivers, which combined with other files made up the Microsoft Data Access Components, or MDAC. This could cause your systems to be out of synch with each other, so it was sometimes difficult to ensure the versions of the drivers were the same on all systems.

 

SQL Server 2005 and the Microsoft programming languages install another set of drivers which can co-exist with the operating system versions, which makes it easier to keep everything in sync. There are two basic classes of these libraries:  the SQL Server Native Client, or SNAC, and the SQL Client.

 

The SNAC libraries include Open Database Connectivity, or ODBC, which is one of the libraries that is also supplied by the operating system. This library is Win32 based, and is one of the most popular access libraries that developers have used over the years.  Another library, called the Object Linking and Embedding for Databases or OLEDB, uses the Distributed Component Object Model or DCOM.

 

The SQL Client library includes the ActiveX Database Objects for dot net, or ADO.NET. This is the library delivered for the managed code set in the .NET programming languages.

 

Finally, there is an older library called the “Database Library” or DBLIB which has been deprecated for SQL Server 2005 and higher.

 

With that quick rundown, we now have a “peg” to hang some more advanced connection topics.

 

Web Link:

Sometimes you don’t need the power of SQL Server for a small, single user application. But how do you know when you should use one over the other? You can read more about this at http://tinyurl.com/2725zg. 

 

Tip of the Week:

SQL Server includes lots of data providers for various platforms , from SQL Server 7 to Oracle and IBM’s DB2. For the best performance, make sure you run a comparison of these providers over the ones from that platform vendor. You can see large performance differences based on which one you use for a given situation.