Chapter 2. Architecture and Tools

When the proper planning is complete and SQL Server 2005 is installed, it is time to configure the system for production use. To properly configure and maintain the landscape, you need a sound understanding of how SQL Server operates. In the first part of this chapter, I focus on the many architectures within SQL Server that affect the transfer of data into and out of the system, including the processing engines, memory, I/O, and the networking layer, which has changed since previous versions. Depending on the edition, SQL Server 2005 does not have the TCP/IP protocol enabled, making the system "secure by default." I show you how to enable that protocol (and others) in this chapter. I explain the path that a query takes and how the system deals with the results. Along the way, I also explain where the various parts of the software are stored.

In the second half of the chapter, I explain the tools you use to control and interface with SQL Server 2005, and I finish up by giving you some practical examples for using them to create your own monitoring environment. The information in this chapter forms the basis for the rest of the book.

Understanding the architecture of a database system is an involved process. Many components make up SQL Server 2005, and knowing how they interact is important for several reasons. Knowing how a query routes through the plumbing of SQL Server will help you pinpoint errors when they occur. You will also be able to find performance bottlenecks, something you just cannot do if you do not know the path the data takes. Understanding the architecture will also help you design systems correctly.