SQLServerCentral Article

Review of SQL2K Resource Kit

,

The SQL Server 2000 Resource Kit has been out for a while now. Much like the

various Windows Resource Kits, however, it's still useful as a source of

information. It's one of the resources I keep handy on my bookshelf as well as

on my workstation. In this review I'll cover why. But since the Resource Kit is

a large book, I'll break up the review into the same sections as the book

itself.

Part 1: Introducing SQL Server 2000 and This Resource Kit

This section, covering the first two chapters,  introduces the resource

kit, points the reader to a few additional sources for information on SQL Server

and also covers the new enhancements in the product. While extremely valuable

when the book was first published, this is one area you can probably skim

through now unless you're new to SQL Server 2000. All of the information here is

very high level and brief.

Part 2: Planning

Planning starts with a chapter on the various versions of SQL Server 2000. I

see this question every so often on the forums and if you need a quick down and

dirty of what the different versions offer, Chapter 3 provides that breakdown.

In fact, Microsoft's offered documentation on their web site is simply the

chapter in Microsoft Word format. Chapter 4 looks at licensing and talks a bit about per processor licensing.

If you need information on what kind of SQL Server purchase to make for your

organization, start here. I'll offer one proviso: look to Microsoft's web pages

on SQL Server licensing and your sales representative for up-to-date information

and help on what options are best for you before making that purchase.

Chapters 5-7 cover converting from another database platform (Microsoft

Access, Sybase, Oracle) to SQL Server 2000. The Microsoft Access documentation

is fairly straight forward. One nice feature is the set of tables relating

Access commands, data types, and syntax to the applicable SQL Server ones. The

same is done with the Visual Basic functions. For Sybase and Oracle there is

similar documentation. It's been a good five years since I've done Sybase

administration and I'm not well-versed in Oracle, so I'll leave off commenting

on these chapters. If you're an Oracle DBA transitioning to SQL Server 2000, you

may also want to look at Chris Kempster's eBook, SQL Server 2000 for the

Oracle DBA.

Part 3: Database Administration

Chapter 8 covers how to manage change control on databases. There have been

entire books dedicated to this topic and chapter 8 therefore stays very high

level. None of it is anything really new; standard, no-nonsense practices are

presented. Chapter 9 talks about enhancements to the storage engine. There's

some good information here, but again, most of it is high-level. If you're

really looking to know more about the internals, there are a couple of good

books out there covering such. James Travis has written a review on Ken

Henderson's book and then there is always Kalen Delaney's Inside SQL Server

2000.

Chapter 10 has a bit more meat than the other two chapters as it covers SQL

Server security. Naturally this is a section that's near and dear to my heart

and there is a good overall coverage of how to use the features in SQL Server

2000 to create a more secure operating environment. There's so much to SQL

Server security that one chapter can't cover it all, but if you want to get a

good start, this chapter is the place. If you're familiar with SQL Server 6.5

but aren't too familiar with what SQL Server 2000 offers, this chapter has some

coverage of what's changed. So far as SQL Server 7.0 is concerned, the security

model is basically the same but there is a high-level overview of the new

features in SQL Server 2000.

After Chapter 10, the authors tackle a subject many a DBA cringes at: Binary

Large OBjects, or BLOBs. Microsoft has proven SQL Server 2000 can handle BLOBs

with its own TerraServer and some hard information about how they decided to

handle the project is covered. But BLOBs have a reputation for a reason and the

chapter also covers some of the issues with BLOBs in the database. Finally, some

code examples of how to process and deal with BLOBs are also given. If you've

got to make a decision on handling BLOBs within SQL Server, this chapter is a

good read.

Part 4: Availability

Chapters 12 through 16 cover making SQL Server more available and represents

a lot of critical thinking on how to keep your database server healthy and

running. With the emphasis of little to no downtime, this section is an

important read. Of course, many of us feel the concept of "no

downtime" is good in theory, but potentially bad in practice. Steve Jones

covered this in an article, Is 0% Downtime Possible, but I digress.

Chapter 12 deals with failover clustering, a subject that can be a difficult

one when first approached. The Resource Kit does a good job of getting the

basics down, including some "techie" details on configuring the

cluster. However, I wouldn't recommend Chapter 12 as your only source. I've

included some links under the Additional Resources section to look at closely if

you are considering a clustered solution. Chapter 13 delves into a similar

subject: log shipping. Log shipping is also a high-availability solution, albeit

one I don't use in practice since my organization relies on clustered SQL

Servers. However, the concept is sound: create a stand-by backup database on a

different server by copying and applying transaction log backups. If the main

goes down, you bring the stand-by on-line and re-point. This gives you additional

options outside of clustering. Now, if you don't have Enterprise Edition, you

can't use "automated" log shipping (nor failover clsutering, for that matter), but there are

a plenty of write-ups on how to perform manual log shipping around.

Chapters 14-16 take a step back from the technical and talk about location,

people, and procedures. You can have the best hardware in the world, but if

everything else that goes into supporting that SQL Server isn't up to snuff,

you're going to have issues. Chapter 14 looks at the data center and what it

takes to have a reliable one. Chapter 15 looks at some additional high

availability options at a 50,000 feet perspective, including transactional replication and

the like. Finally, Chapter 16 considers what it takes to get to the mythical

five nines.

Part 5: Data Warehousing

As businesses put more and more of their data in large databases, data

warehousing naturally comes into play. Companies like Wal-Mart rely heavily on

their sales data to figure out where to build new stores, what items to stock,

and the like. But data warehousing isn't just for the big guys. Aggregating and

analyzing data can be of benefit to smaller organizations as well. Chapters

17-21 talk about some of the concepts of data warehousing and what SQL Server

has in place to assist the DBA tasked with building and maintaining data

warehouses and data marts.

Chapter 17 talks about design considerations. Speaking from personal

experience, warehousing is something that needs to be carefully planned. Nothing

is as aggravating as spending man-weeks making a change to a warehouse and

finding out the change really affected just 0.08 % of the total data and wasn't

critical to any business processes. Admittedly, if we had done a better job of

defining requirements, we wouldn't have been burned in such a way and so I point

to this section of the Resource Kit. Chapter 17

addresses covering the requirements and designing a system that works for the

users. It also briefly covers the schemas that are typical in warehousing as

they tend to differ from On-Line Transaction Processing (OLTP) or "live

processes."

Chapters 18-21 get more into the technology and processes to support

warehousing. Chapter 18 covers the use of data partitions, which isn't the same

as partitioned views/federated databases, but rather the concept of breaking up

the data into logical blocks (such as by year). Chapter 19 covers the concept of

Extraction, Transformation, and Loading (ETL). This is the data warehouse

building itself. Chapter 20 covers performance tuning concepts for warehouses,

which can be an important concern given the amount of data in most warehouses

will be greater than what is usually kept active in an OTLP database. Chapter 21

looks at monitoring the multiphase data pump in DTS, typically a key component

in building most warehouses.

Part 6: Analysis Services

A logical follow-up to warehousing would by Microsoft SQL Server Analysis

Services. Part 6 covers this technology, first packaged with SQL Server 7.

Again, most everything is high-level as there are quite a bit to using Analysis

Services effectively. If you're interested in finding out more about Analysis

Services, give this section a read. If it spurs your interest, look for books on

Analysis Services as there are several good ones out there. 

As far as how the chapters breakdown, Chapter 22 is all about designing

cubes: specifically cubes in the "real world." If you've not done cube

design before you may be interested in this chapter as a lot of the basic

concepts are covered. Chapter 23 goes into using Multidimensional Expressions (MDX)

language to further access the data. To get the most out of Analysis Services

you often have to rely on MDX. This chapter shows some of the uses. Chapter 24

discusses data mining. Data mining, or active support for it, is new to SQL

Server 2000. This chapter is an overview of the data mining process and

discusses some of the considerations for doing so. Chapter 25 covers client

access to Analysis Services. This is a relatively short chapter which basically

points the readers at the technologies for access. The coverage of Analysis

Services wraps up in chapter 26 with coverage of how to performance tune

Analysis Services. Again, AS tends to handle large amounts of data, therefore

watching and planning for performance becomes an important concern.

Part 7: Digital Dashboards

A few years ago, when this book originally came out, digital dashboards and

portal technologies were heavily talked about as the next "big thing."

So it's not surprising there is coverage of this topic within the SQL Server

2000 Resource Kit. Chapter 27  is dated. It refers to the Digital Dashboard

Resource Kit (DDRK) but the DDRK is now officially retired. In its place are

Microsoft SharePoint and resources with the Office Developer. Chapter 28 refers

to accessing Analysis Services metadata using the DDRK, so it too is

"expired." Therefore, if you're looking for information on digital

dashboards, the SQL Server 2000 Resource Kit isn't the place. I've included more

up-to-date links in the Additional Resources section.

Part 8: Replication

This section was a bit disappointing when I first looked it a while ago.

Looking back at it for the review, my opinion hasn't changed much. Replication

is one of those SQL Server technologies either you love or you hate and one

would think it would garner more press in this book than a meager two chapters.

Chapter 29 is basically an FAQ on replication, but it's not very detailed. There

are far better articles on the web covering this subject, including several from

Andy Warren. Chapter 30 discusses building a Visual Basic component to deal with

conflict resolution in merge replication. There is coverage of the Microsoft SQL

Replication Conflict Library and how to use it. If you need to write your own

custom component this may be of use to you.

Part 9: Web Programming

Part 9 covers both XML and English Query and how to use them in web

technologies to access SQL Server. Chapter 31 provides a brief tutorial on

getting XML support running and how to access it. If you've not been exposed

much to using the XML features of SQL Server, this isn't a bad intro. The

chapter walks through some basics and isn't intended for much more than that. It

also offers links to the various XML technologies pages for Microsoft in case

you're interested in delving further. Chapter 32 covers English Query. I'll be

honest and say I've never used English Query as my organization hasn't had a

need to do so. Therefore, whether or not the chapter truly lives up to the

billing of "English Query Best Practices" or not I'll leave for others

to say. 

Part 10: Designing for Performance and Scalability

At the end of the day we all want our database servers to perform like

greased lightning. That's what this section of the book is all about. Chapters

33 and 34 aren't for the faint of heart as they get more technical than most

other sections of the book. Chapter 33 is all about optimization, what to look

for, what to plan for, and how to conceptually design for a fine running system.

Chapter 34 is about dealing with common administrative issues like configuration

and application performance. There is a fairly detailed explanation of how the

stored procedures provided with the book for this chapter work. It's a follow on

to chapter 33 only where chapter 33 looks primarily at information we collect

outside of SQL Server, chapter 34 looks within.

Chapter 35 covers using Visual Basic and SQL-DMO to build your own

administration applications.  There are a bunch of great utilities out

there that have been built using SQL-DMO and other technologies. However, if

none of them quite meet your needs this chapter gives you a it to start with.

The material walks you through a sample application for those who like the

hands-on exmaples. Of course, the chapter looks at Visual Basic and none of the

.NET technologies (which weren't out when the book was written), so the content

is slightly dated.

Chapters 36 and 37 dive back inside SQL Server. Chapter 36 looks how to

increase performance on views, specifically indexed views. Indexed views are a

new feature in SQL Server 2000 and can solve performance issues related to views

experienced in SQL Server 7. Chapter 37 covers the use of INSTEAD OF triggers,

also a new feature in SQL Server 2000. INSTEAD OF triggers fire before an

operation takes place, meaning you can intercept that data change before it

actually is performed. Since INSTEAD OF triggers fire before the operation, they

can be used to test the data coming in, allowing you to bubble up custom error

messages rather sending back a cryptic constraint violation error. This chapter

covers some of those concepts and also points to remember when using INSTEAD OF

triggers.

Chapter 38, the last chapter with real content, is about scaling out SQL

Server using the concept of federated databases. Clustering, log-shipping, and

similar technologies help make SQL server reliable, however, they do nothing to

balance the load. The only option to increase performance was to scale up,

meaning bigger and more powerful servers. There hadn't been a solution to scale

out and distribute the load across multiple servers. To somewhat fill that void

Microsoft implemented partitioned views and the federated database. This chapter

covers this new technology, the basic theory how it works, the design

considerations to take into account, and some ideas on how to administer the

partitions. If you are looking at very large databases, especially with data

that can be partitioned, this chapter may be of interest to you.

Part 11: CD-ROM Content

The final chapter of the book, chapter 39, covers what's on the CD included

with the book. The CD-ROM with an electronic copy of the book is extremely valuable.

In addition, the sample code and scripts presented in the book are also

included. The CD-ROM may be the best part of the whole package.

Concluding Remarks

The SQL Server 2000 Resource Kit is still a good book even as we look at

Yukon, now known as SQL Server 2005, on the horizon. Since the book was first

published in 2001, some of the material is dated. However, there is still enough

good information in the book to warrant a second-look. It's a good

"overview" book meaning it covers a lot about SQL Server 2000 at a

high level. You won't find a lot of in-depth stuff in it because the book,

already around 1200 pages, would simply be too large. Perhaps SQL Server 2005's

resource kit will look much like the Windows Server resource kits and be broken

up into multiple books. This is a book I'd recommend, especially for someone

trying to get their feet wet into SQL Server from another database platform

(even SQL Server 7) or from a predominantly development role. If you're

uncertain about whether to lay down your hard-earned money or an experienced DBA

who may only need to occasionally reference it, fear not! Microsoft has the book

on-line. I've included the link in the Additional Resources section. 

Additional Resources

 

 ©

2004 by K. Brian Kelley.

http://www.truthsolutions.com/

 Author of Start to Finish Guide to SQL Server Performance

Monitoring (http://www.netimpress.com).

 

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating