Pro SQL Server 2005 by Thomas Rizzo, Adam Machanic, Julian Skinner, Louis Davidson, Robin Dewson, Jan Narkiewicz, Joseph Sack, Rob Walters

Pro SQL Server 2005

  • Author: Thomas Rizzo, Adam Machanic, Julian Skinner, Louis Davidson, Robin Dewson, Jan Narkiewicz, Joseph Sack, Rob Walters
  • Softcover: 704 pages
  • Publisher: Apress(Oct 24 2005 12:00AM)
  • Book dimensions: 9.2 x 7.1 x 1.4 inches
  • ISBN: 1590594770
  • ITBookworm score: 4.5 /5

Score Reasoning

I really like this book and it definitely has value, especially as a survey of new technology. The only thing that kept it away from 5-stars was its lack of depth in some areas. Some areas really had some decent depth to the, while others like DMVs and SSMA were only skimmed over. DTA wasn’t covered at all though. I think at the very least it should have a nice appendix of the DMVs and what they do.


This book is basically a survey of Yukon’s new features. Some chapters are high-level walkthroughs of functionality that only touch on the topics enough to let you know what they are. Still other chapters, like the one on mirroring, go into some fairly good detail. I like the book though. I’ve been involved with Yukon for quite some time now and though most of the book was below my current level of knowledge, I did learn some things about SSRS and SSAS. Don’t get me wrong, I did learn other things as well, those were just the biggest things I got out of it. I liked the format and the pace as well. It’s not perfect, but for a survey on Yukon it’s pretty ok. I think someone wanting a quick start guide to Yukon would definitely get their money’s worth out of this book. I’ve used it a few times as a reference for specific syntax simply because it was there and the examples are thorough and explained well. I especially like the sections on the new programming features. The new coding features for developers is excellent and gives some very complete examples of CTEs, PIVOT/UNPIVOT, etc. It’s an excellent chapter. As well, the chapters on the .NET technologies (CLR stored procedures, and assemblies) are also very complete and give some excellent explanation and examples. If you’ve never coded .NET assemblies in Yukon, then these chapters are an excellent place to start. The chapters on XML are also very good as well. Not many DBAs know much about XML and those who do probably don’t know anything about it in Yukon. Frankly I found the chapter on installation a little light. I was hoping to see some enhancements to remote and scripted installation covered. As well, SSMS was a little light. It really didn’t cover the reports, or some of the advanced functionality in them like being able to save them off to excel. Database Tuning Advisor (DTA) isn’t really covered at all, which I find disappointing because it’s supposed to be one of the big new features touted by MS. If nothing else, I think it should’ve gotten some decent ink. It covers SQL Agent and the jobs very nicely, but Profiler is just skimmed over. As well, DMVs are given a little time, but hardly the time they deserve. I guess that’s what makes this a survey book.

Who is this book really for?

This book is definitely for experienced DBAs who need to get a handle on Yukon. There is very little to no exposition on basics so you don’t have to worry about being bored with how to create a table or what a PK is. This book explains the topic at hand, and does it well. It has no trouble finding its audience.

Writing Style

You know, quite often I rag on writing style because so many books just spit up info with no real life to it. And while this book doesn’t go out of its way to try to imitate George Carlin, it does have some personality to it. I can definitely see some of the authors between the pages. There are some spots in a few of the chapter that made me crack a brief smile which is about what you want from a book like this. Be amusing, but don’t cloud the techie stuff. This book isn’t perfect, I really think they could have brought even more of their personalities into their text, but it wasn’t boring in the least. I guess if I had to put an entertainment rating to it I would have to rate it somewhere between Spaceballs and Hey Dude, Where’s my Car.


Boy, did I beat the crap out of this book. I’ve always been very impressed with the quality of Apress books. I’ve still got a couple Apress books from years ago that are still holding together very well. I can tell this one will be the same way. For the past month I’ve carried it, folded it backwards, worked the spine, laid books on it while bent backwards, and tossed it around the room while folded back. My son is learning to walk now, and this is another one he’s managed to get a hold of regardless of where I put it, and a couple times I walked in to find him twisting the spine, and trying to generally tear it up. And other than the gooey spots on the corners, it took everything he threw at it too. This book will last you a good long time with no problems.

Table of Contents

Chapter 1: SQL Server Overview and Installation

Here we start out with a brief overview of SQL’s history. It’s cool to kinda see a little of where SQL has been. Next, and probably the most important thing this chapter has to offer, is a table that lists all the major features and which editions they’re in. And while there’s actually a more complete list in BOL, this one I think is organized better, and gives descriptions of the features. It then goes into the installation process, and gives screenshots of the install itself, but really doesn’t give all that much info about the enhancements to installation. I was really hoping it would go over some of the enhancements to remote install, and specific features of the install itself like the enhanced error logging, but it just didn’t. So this chapter actually ended up being a little light as it turns out.

Chapter 2: SQL Server Management Technologies

Here you get to see some of the basic high-level enhancements to SSMS. It walks you through some screenshots, and stuff like dedicated administrator connection. All of this is great for someone who didn’t work with any of the CTPs, but for someone who has worked with it at all, there really isn’t much here for them. It really makes you wish there were some hidden features that most people don’t know about that could be revealed here, but there aren’t. It does go into some other aspects of management like mail, agent and maintenance plan designer, but again, they’re such high-level views, they’re not much use other than to inform you that they’re there. It also gets into some basics of DMVs and default server trace. There are some other topics here, but nothing worth mentioning in detail. This is clearly just a survey of the management technologies, which I hope will be expounded on later in the book. Again though, for someone who’s never touched Yukon, this is a good overview to be introduced to the new interface.

Chapter 3: Enhancements for Developers

Here they start out talking about deprecating the old-style outer join syntax, and they even make a really big deal about it, but I’ve been in DBs for over 10yrs, and I’ve never seen that style of join in actual production code, so I just don’t think that it’s as big of a deal as they say it is. But it is nice to know that it’s finally gone if nothing else for information. The first really significant topic here comes fairly on… CTEs. I think CTEs are going to be very important because they solve the common problem of recursion. The authors give CTEs ample time here. They give some good examples, and some good explanations. The recursion aspect of CTEs are given good ink so you should really get a good feel for how and when to use them. There are also some good examples given to the TOP clause. Of course, it’s a fairly easy concept to grasp, but they still go through a thorough discussion. Next you go through some really good examples of some of the other new T-SQL features like random data sampling, pivot, output, ranking, except/intersect, etc. There are plenty of other topics covered here that are worthwhile as well, and this is really the first chapter with any real substance. Good work.

Chapter 4: Enhancements for DBAs

Here we start out talking about the different management views available to DBAs. It also lists a short table with a mapping of the new views to the old system tables. This can be helpful when trying to learn things the new way. The table doesn’t offer anything other than just a list though. You then jump into a good discussion on DDL triggers. There’s plenty of code and even a bit of theory offered which makes it a nice intro to this new feature. Now let’s get directly into indexes and the new functionality in creating and managing them. There’s not a ton of time given to the topic, but you really don’t need it… you can either appreciate the enhancements or you can’t. Partitioning is discussed next. It doesn’t give a lot of discussion, but you’ll definitely walk away with a good understand of what it is and how to use it. Snapshots are next and this topic has been beaten up quite a bit over the past couple years so it’s pretty old. Still, it’s worthwhile conversation because it’s a major new feature. It can be argued that it mainly exists for compatibility with DBs like Oracle that have it and that this level of concurrency for SQL isn’t necessary. Honestly, that’s an argument for another time, but one thing I do like is that the book discusses the performance impact on TempDB. This is a huge caveat that should be discussed in any responsible conversation about snapshots and this chapter handles it very well.

Chapter 5: .NET Integration

There’s some decent discussion in the beginning here about the hows and whys of using the CLR with SQL, but the meat of this chapter is the very nice walkthrough showing you how to create an SP in .NET. It’s a good primer because it gives you the basics of why you use the lines of code that exist. It walks you through a good-sized SP that demonstrates a few of the concepts so you can get acquainted with them. In all I’d say this is a worthwhile chapter.

Chapter 6: Programming Assemblies

This is another excellent chapter. There are some really good walkthroughs on creating UDTs, UDFs, and triggers in the CLR. There’s plenty of code here to study by, and enough theory to get you going. I’m not going to say too much about the specifics itself, but yeah, by all means read this chapter. If you’re like most DBAs and you don’t know much about .NET programming, then you’ll appreciate this primer.

Chapter 7: SQL Server and XML

Not many DBAs know a lot about XML. It’s been a small part of SQL for a while now, and because of how difficult it is to work with, it hasn’t caught on very fast. Yukon changes all that supposedly though and this chapter gives us a good primer for XML and the related technologies. It starts out discussing the principles of XML, and how to work with it in SQL2K. It goes into many XML-related topics and I’m not gonna go into all of them here, but it’s definitely worth a read. Even if you know how to work with XML in SQL already it’s good to see it all in one place. Of course, it also gives a very good discussion of XML in Yukon. It carries some good code samples as well so you can follow along and get the hang of the new XML routines.

Chapter 8: SQL Server 2005 XML and XQuery Support

Here you start talking about the XML datatype in Yukon as well as some reasons why you’d use it to begin with and its limitations. Next, the different ways to load XML are covered and then an XQuery primer is given. It actually gives a pretty good XQuery discussion and discusses the different methods with plenty of code given as examples. It then goes into modifying and indexing XML as well as full-text search with XML. There are plenty other topics covered here including endpoints, administration, web services, etc. This chapter is worth the read if you’re looking for a quick and dirty XML primer.

Chapter 9: SQL Server 2005 Reporting Services

Here you start out talking about the basic components of SSRS. Then you move quickly to upgrading and licensing as well as SSMS integration. There are some screenshots that outline the SSMS management tasks to perform, but it’s just a quick walkthrough. It then walks you through all of the major enhancements like the date picker and interactive sorting. It walks you through building a simple report with screenshots just to show you the ropes a little bit. It then goes through some more of the enhancements, and then starts up a walkthrough on report builder. It’s really a high-level view and to really understand SSRS you’ll need to go to one of the specialty books on the topic. This is just a survey tour and I frankly found it kind of light. But again, if that’s what you’re looking for, it’s perfect for you.

Chapter 10: Analysis Services

You start out mainly talking briefly about some of the enhancements in Yukon. You then move right into some OLAP concepts like what cubes, measures, and dimensions are. It then walks you through starting up a project which you should already know how to do from the other sections, but it shows you anyway. It pretty much spends the rest of the time walking you through building a basic cube. It’s ok, but again, not a really involved chapter.

Chapter 11: Security

OK, here we start out by going through some of the new security areas. However, you pretty much get into principals and securables right away. These are two new security terms that you’d better become familiar with. You also get into the new Windows 2003 password policies you can control with SQL principles. You then get into all kinds of things like database roles, object ownership, impersonation, etc. Each section is really kinda short, but I think it gets the point across for its audience. You know though, it really gives a good discussion on schemas and how their security works, so you should really pay attention here. You then get into permissions management and code access security. They’re both good enough at what they do, and there’s of course, code to show you examples of pretty much everything they talk about. You finish up with encryption and certificates. It’s a good discussion, so don’t miss it.

Chapter 12: Service Broker

The discussion here is minimal. It gives a few of the basic concepts and then jumps right into the code. It’s not for the faint of heart. You’ll probably really know what you’re doing when you finish here, though I think a bit more explanation might be in order, but it’s clearly for SQL coders, and not beginners. It’s a decent chapter and will definitely get you started, but again, for a full discussion on this topic, I would go to a book dedicated to it. Maybe even start with BOL before you go spend your money on something else.

Chapter 13: Automation and Monitoring

Ok, this is actually a pretty good chapter. It starts out by talking about the SQL jobs. It discusses security of jobs, and walks you through the steps of creating a new job in SSMS. It then has some pretty exhaustive tables showing you all the rights the different Agent roles have. It then goes into multiple proxy accounts and how and why to use them. It’s a good discussion. Now you get into the Agent subsystems, those being SSIS and SSAS. Another good topic is sharing job schedules. It walks you through creating a shared schedule and how to use it in your jobs… good stuff. Next, we have WMI and agent alerts. It’s very brief, so don’t settle in. Maintenance plans are covered next. It goes on a little longer than I thought it should, but I’ll never count off for a book giving too much info. SQLCMD is covered next and while it’s a decent discussion, I really feel it should have gone into it a bit more… but it’s a pretty short section. Working with database mail is next and it covers it in pretty decent detail, so you should learn something. The chapter finishes up with Profiler and some of its new features. Again, I think it should have gone into more detail, but it’s not bad at all.

Chapter 14: Integration Services

Here we’ve actually got a pretty decent base-level discussion of SSIS. For the most part it starts out talking about some of the new features, the new IDE, etc. You then get into the fundamentals of packages and the new design. It goes through screenshots of examples of some of the tasks, and creating data sources. You then get into event handlers with nice screenshot examples. You pretty much see the same scene for a lot of the other tasks too. It shows you the task, and the basics of how to use it. It also has a nice table that lists and explains most of the new tasks… useful if you’re digging into SSIS for the first time. You then go into the specifics of logging and package configurations. It’s pretty good discussion that doesn’t get cut short or ramble on too long. There are a slew of other topics covered from variables to deployment. If you’ve never touched SSIS, or if you’re just kind of weak on it, then this will give you a good overview to get you started.

Chapter 15: Database Mirroring

I think this is one of the better chapters. It starts out with a basic discussion of HA/DR… the whys and hows, etc. It also gives you an overview of DBM and how it works. One of the things I like is the table showing the level of protections, failover, complexity, etc of the different solutions like DBM, replication, shipping, etc. It’s a nice overview so you can look at the stats right in front of you and make an educated decision based off of your risk assessment. Next it shows you how to setup DBM. There are some good coded examples that won’t leave you guessing. All the different aspects of supporting DBM are covered. Next, it goes into setting up DBM through SSMS. Monitoring DBM is discussed next and both the DMVs and the Perfmon counters involved are given time. Here’s something I really like… the authors give you a sample application to demonstrate DBM. You need to put something in your code depending on how you want the failover handled, and they show you how to do it here. It finishes up with small sections on snapshots, clustering, and replication.

Chapter 16: Notification Services

Here you cover all the aspects of NS. Writing, managing, etc are all covered with plenty of code to support the examples. It’s a good chapter. I’m not going to go into it too much here though, but if you want a nice overview of NS this is the place to start.

-Sean McCown,

Tags: , , , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *