SQL Server Reference Guide

All that data stored securely in SQL Server is pretty useless unless you can get at it. Microsoft SQL Server provides several ways to access the data, divided into three major camps: Tools, Programmatic and Third-party. The divisions reflect the users of the method. For Database Administrators (DBAs) the method of access involves tools that Microsoft and others provide for that purpose. An end-user doesn't need to control the server and its data; they just need to be able to access it. For them, a developer writes programs to access the system. Other users rely on Microsoft Office or some other non-SQL Server related program to use the data.

In any case, connecting to a SQL Server Instance and then on to the database is one of the most frequently asked questions in Forums and Newsgroups. SQL Server data is harder to get at than say Microsoft Access or a text file. The reason is that there are more layers involved — it isn’t just a single-user application on the same system where you are. You have to reach across all those layers to get to the data on the server.

Let’s take a look at all those layers and how they fit together. I’ll then explain how each layer works, so that you can troubleshoot issues when they occur, and know which ones to optimize.

First, let’s discuss all the layers in one view, and then we can break it down further. Take a look at this diagram:

Figure 1

Working from the top down, you’ll see the various components and pieces that are part of the process flow of an application talking to SQL Server. At the very top layer is an Application, which I’ll explain further in a moment. For now, you can think of SQL Server Management Studio (SSMS) or a client application sitting here.

The application needs some way to format requests, receive data in the form of columns, rows and so on, and receive messages in a certain format from SQL Server. It does this through the use of a piece of software installed on the client called a “client library”. There are a few of these, which we’ll cover shortly. Note that even if the application is installed on the same system as the server, it’s still called a “client” application, and the path to get data back and forth is the same.

Next, the client library needs to be able to place the request on the network. Since there are different kinds of networks, there are different pieces of software called “network libraries” that SQL Server can use to format up the data properly for the transport.

Just below that is the network itself — from the Network Interface Card (NIC) in the client machine all the way out to the wire, and then through the various network devices to the NIC on the server.

Once the signal gets to the server, SQL Server needs to “decode” what was formatted for the network, so it also includes a set of network library software.

Finally, the SQL Server Services are listening on various network “ports” (sort of a subset of the network number on each machine), checks the authentication of the user, and then allows them (or not) on to the Instance of SQL Server. Once inside, the security is checked again for access to one or more databases and database objects such as tables.

With all of these components involved, you can see that there are more than just a few places for things to go wrong, or that need to be optimized. This isn’t unique to SQL Server by the way — most every Relational Database Management System (RDBMS) works this way. In some, it’s even more complicated!

And you should also know that within each of these components there are still more components. We’ll talk a little about that, but I won’t cover everything here. Whole books are written on these components, but with any luck you won’t have to read all those to get everything up and running.

Applications

Let’s start with those applications. Applications on the client can be anything from something written in a programming language at your organization to something you buy from a vendor such as SAP or Microsoft. In fact, all of the tools for SQL Server such as Enterprise Manager and SQL Server Management Studio are still just “client” applications. All of them follow the same path.

To troubleshoot a system or monitor it for performance tuning, you should always start with the application. Let’s assume that you can’t connect to SQL Server from an application. The first thing to do is to see if any application on that client machine can talk to the SQL Server. Or assume that the “system is slow." Always make sure you check other client machines to see if they are slow as well. It might just be the application that is the issue.

Client Libraries

The application has to use some sort of method to know about SQL Server databases and database objects, such as tables, stored procedures and so on. They do this by “loading” or referencing more software that understands SQL Server.

There are several client libraries that SQL Server can use, but let’s focus on the three main interfaces you’ll come across in the field.

The first and one of the oldest libraries is called Open Database Connectivity or ODBC for short. This library is actually included with most Microsoft operating systems, although it is usually a version that is older than what SQL Server ships with. When an application uses ODBC, the client machine needs to enter some data, such as the name of the connection, the user name and password and so on. That is saved on the client machine, and then the application can call for it by name.

Of course, that means someone usually has to visit each workstation. To overcome this issue and to provide more features, Microsoft invented the Object Linking and Embedding for Databases, or OLEDB provider. This new library could be called by developers, and they could set up all of the parameters without the user saving anything on the client machine. It also allowed for a more distributed communication model.

Modern programs and applications use a new library called ActiveX Database Objects for .NET, or ADO.NET. This library has lots of new features, is easier to call and use, and you’ll see it more and more as time goes on.

There are other libraries that you can use to access SQL Server for special purposes. In fact, I’ve written a few articles here at InformIT discussing the Database Management Objects (DMO) library, as well as the library that replaced it called Server Management Objects (SMO). These libraries are very useful for the DBA, since they work less with the data and more with the database objects themselves.

Network Libraries

Three main libraries are used to format the data for the wire. Shared Memory is available in SQL Server 2005 and higher, and basically uses the memory blocks on the server to act as a network. This means that the application needs to be on the same box as the server to use this protocol. This prevents the traffic from going out on the wire only to come back to the same machine.

One of the most common network protocols is TCP/IP. TCP/IP gives an “address” to each device on the network, using a numbering scheme. The older version of this protocol is version 4, and the newer is version 6. SQL Server can use both of them.

In the early days of Microsoft operating systems, they used the NetBIOS protocol from IBM. Inside this protocol was a wrapper referred to as “Named Pipes," and it is also called that name for SQL Server. It uses a set of network names and text to reach the server and the right proper port.

Network

Whenever you’re troubleshooting or optimizing connections to SQL Server, you can’t ignore the physical network. If you can’t reach the server quickly across the network, your programs aren’t going to be any faster either. Although I won’t cover everything about networks here in this article, I’ll point you to some great resources in the references section at the end.

Another set of network components in both hardware and software that has caused a lot of issues lately are Firewalls. Whether they are based on hardware or software, you need to understand the blocking they do on your application.

SQL Server Services

At the very bottom of the stack are the SQL Server Services that are “listening” on the network interface for calls. Within those addresses reserved for the system, SQL Server uses a set of network ports to communicate back and forth with the applications. In another tutorial I’ll break out this component further, but at the very least you need to have the SQL Server services running and the network configured to allow the client to access them.

InformIT Articles and Sample Chapters

From the book, Upgrading and Repairing Networks, 5th Edition, you can read this free article on planning for the logical design for your network.

Books and eBooks

If you’d like to learn a lot more about networks, you should check out the A+ certifications. Here is a good book to practice for the exams for networking for the A+ cert.

Online Resources

Windows Server 2008 has a lot of new firewall features, and some of those make working with SQL Server a little more complex. More on how to do that is here.