Chapter Overview

Chapter 2: Windows Architectural Overview

In this chapter, we'll talk about how Windows works from an architectural standpoint. We'll discuss the various components of Windows and how Windows applications are constructed. You'll learn about DLLs, virtual memory, CPU nuances, and a variety of other Windows elements that affect how complex Windows applications such as SQL Server behave.

Chapter 3: Processes and Threads

This chapter covers Windows' process and threading architecture. You'll learn what a process is, how it differs from a thread, and how SQL Server behaves itself as a process. You'll learn about Windows' scheduler and how threads are scheduled for execution, and you'll explore thread synchronization in depth. You'll work through several C++ applications and xprocs that demonstrate how processes and threads work under Windows and how this applies to SQL Server. Starting in this chapter and continuing throughout the Foundations section, we'll build a series of applications that search text files for strings. We'll create several different versions of a text search application—each one employing different Windows foundational concepts—so that you can readily see how the various Windows technologies are typically used in real applications. We'll pick up where this chapter leaves off when we discuss SQL Server's User Mode Scheduler (UMS) later in the book (Chapter 10).

Chapter 4: Memory Fundamentals

In this chapter, you'll learn about Windows' memory management. You'll learn about the difference between virtual memory and physical memory, as well as the difference between virtual memory and heaps. You'll learn how Windows apportions memory to applications and how it translates virtual memory addresses into physical memory addresses. We'll continue the text file search theme started in the previous chapter and build several applications that illustrate how Windows applications can make use of the memory management facilities provided by the operating system. You'll gain insight into how SQL Server makes use of some of these facilities by building apps that feature them. We'll build on the concepts taught in this chapter when we get to SQL Server Memory Management (Chapter 11) later in the book.

Chapter 5: I/O Fundamentals

We'll take a tour of Windows' foundational I/O facilities in this chapter. We'll continue the text file search theme and build applications that make use of synchronous I/O, asynchronous I/O, nonbuffered file I/O, scatter-gather I/O, and file I/O using memory-mapped files. You'll gain insight into how SQL Server makes use of Windows' I/O facilities by seeing them at work in real applications. In SQL Server as a Server (Chapter 9), we'll discuss how SQL Server makes use of these concepts.

Chapter 6: Networking

In this chapter, we'll explore Windows' networking application programming interfaces (APIs). You'll learn about Windows sockets and named pipes, as well as the RPC API. We'll continue our exploration of text search applications and build applications that make use of Windows' networking APIs to communicate with one another and process input and output. You'll also gain some insight into how SQL Server makes use of Windows' networking facilities. We'll build on the information in this chapter when we discuss how SQL Server employs the various networking APIs in Chapter 9.

Chapter 7: COM

We'll explore the basics of Microsoft's Component Object Model (COM) technology and discuss how COM is used by SQL Server. You'll learn about threading models, interfaces, marshaling, reference counting, and many other COM concepts. We'll talk about how Windows applications typically make use of COM, and we'll talk about some of the ways in which SQL Server uses it. This chapter will provide the background you'll need to work through the ODSOLE chapter later in the book.

Chapter 8: XML

In this chapter, you'll learn about the eXtensible Markup Language (XML). You'll learn how to construct your own XML documents and how HTML and XML fundamentally differ. You'll learn about attributes, elements, and schemas, and you'll learn to apply XML style sheets to transform your data. This chapter will provide the background and foundational information you'll need to work through Chapter 18 (SQLXML) later in the book.

Chapter 9: SQL Server as a Server

We'll discuss how SQL Server behaves as a Windows server application in this chapter. We'll pull together several of the concepts discussed earlier in the book and show how they're employed by SQL Server. For example, we'll show how SQL Server uses the Windows networking APIs to listen for new connections and schedule them for processing via UMS. We'll talk about the DLLs imported by SQL Server and what they're used for, and we'll talk about where SQL Server fits in the general taxonomy of Windows applications.

Chapter 10: User Mode Scheduler

In this chapter, we'll investigate how SQL Server schedules work to be done using worker threads and fibers. You'll learn how SQL Server's scheduler compares to Windows' scheduler, and you'll learn how SQL Server makes use of Windows scheduling facilities and synchronization objects. By delving into UMS, you'll develop great insight into how SQL Server carries out client requests.

Chapter 11: SQL Server Memory Management

We'll build on the earlier discussion of Windows memory management fundamentals and show how the concepts we learned are applied by SQL Server to manage its memory. You'll learn about the BPool and the MemToLeave regions, about how Address Windowing Extensions (AWE) fits into the whole picture, and how SQL Server attempts to balance high performance with efficient resource utilization.

Chapter 12: Query Processor

In this chapter, we'll document how the SQL Server query processor works internally and how it processes and optimizes queries. You'll learn about the four major stages of query optimization and how each one affects the overall optimization process. You'll learn how indexes, statistics, and constraints are used by the optimizer to generate efficient execution plans, and you'll learn how to structure queries for maximum performance.

Chapter 13: Transactions

The Transactions chapter examines SQL Server transactions in depth. We'll write several Transact-SQL queries that make use of transactions, then explore how they work and how SQL Server's transaction management constructs work in general. You'll learn how to avoid common errors and how to properly use SQL Server's transaction management facilities in your own applications.

Chapter 14: Cursors

In this chapter, we'll explore how SQL Server cursors work. You'll learn about the different types of cursors, how to use them, and how to avoid common mistakes. We'll talk about how transactions and cursors interrelate and how you can avoid common concurrency and performance issues caused by cursor misuse.

Chapter 15: ODSOLE

We'll explore how you can make use of COM objects from within Transact-SQL via SQL Server's Open Data Services Object Linking and Embedding (ODSOLE) facility. You'll learn about the sp_OA extended procedures, how to use them, and when not to use them. You'll build several interesting COM objects for use within Transact-SQL including a bevy of financial functions, array functions, and string manipulation functions. You'll likely find much of the sample code in this chapter to be very useful.

Chapter 16: Full-Text Search

In this chapter, we'll explore SQL Server's Full-Text Search (FTS) facility. You'll learn how it works and how it is designed. You'll explore FTS queries and how to make use of FTS in your own code.

Chapter 17: Server Federations

You'll learn about distributed partitioned views and how they relate to server federations in this chapter. You'll dig into a few execution plans and delve into how partitioned views affect performance and scalability.

Chapter 18: SQLXML

In this chapter, you'll explore the many aspects of SQL Server's XML technology. You'll learn about FOR XML, OPENXML(), sp_xml_preparedocument, updategrams, templates, the SQLXML managed classes, and so on. We'll build on the XML fundamentals we explored in Chapter 8 and explore how SQL Server exposes a powerful collection of XML-enabled features and how to make use of those features in your own applications.

Chapter 19: Notification Services

In this chapter, you'll get to explore SQL Server's Notification Services technology. You'll learn about how it is designed and how the typical Notification Services application is architected. You'll see how easy the Notification Services platform makes building and deploying notification-oriented applications that are fully functional, very powerful, and scalable. You'll finish up the chapter by building a Notification Services application of your own and a subscription management application using VB.NET.

Chapter 20: Data Transformation Services

You'll explore SQL Server's Data Transformation Services (DTS) in this chapter. We'll talk about how DTS is designed, the fundamental components that make up DTS packages, and the ways you can leverage them to build data transformation applications that are powerful and flexible. We'll build a number of packages that explore the many features and facilities of DTS. You'll finish up with a project that teaches you how to access and control DTS packages via Automation.

Chapter 21: Snapshot Replication

In this chapter, you'll explore snapshot replication. You'll learn how data typically moves between a publisher and a subscriber in snapshot replication and how you can track exactly what is moved between them. You'll learn about the distribution database and how the Snapshot Agent facilitates the replication process.

Chapter 22: Transactional Replication

As with the previous chapter, this chapter is dedicated to replication. In this case, we'll discuss transactional replication and the ways it differs from snapshot replication and merge replication. You'll learn how the Log Reader Agent reads the transaction log and sends changes to subscribers by way of the distribution database. You'll learn about immediate and queued updating subscribers, and you'll see how they work internally.

Chapter 23: Merge Replication

As the title suggests, you'll explore merge replication in this chapter. You'll set up several subscriptions, then watch as they participate in a generic merge replication scenario. You'll learn about generations and conflict resolvers, and you'll see how merge provides a flexible (yet complex) data replication facility.

Chapter 24: Finding Undocumented Features

In this chapter, you'll learn how to track down undocumented features. Rather than provide a smorgasbord of undocumented features as I've done in previous books, this chapter shows how to find these hidden goodies yourself. You'll learn how to use Profiler to find undocumented features and commands, as well as how to search the text of system procedures for undocumented DBCC commands and trace flags. With the skills and data you acquire in this chapter, you should be able to dig up undocumented features and commands on your own.

Chapter 25: DTSDIAG

In this chapter, I'll introduce you to a utility implemented via a collection of DTS packages that can collect diagnostic data from SQL Server. Using this tool, you can concurrently collect Profiler traces, blocking script output, SQLDIAG reports, Perfmon logs, event logs, and a number of other useful diagnostics. This tool demonstrates several useful DTS techniques including how to automate a DTS package from Visual Basic, how to modularize a DTS application by breaking it up into separate packages, and how to use a DTS package as a workflow manager for other tasks.