Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Review of SQL2K Resource Kit

By Brian Kelley,

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).

 

Total article views: 8894 | Views in the last 30 days: 5
 
Related Articles
FORUM

Cluster missing SQL Server Resources

Cluster missing SQL Server Resources

FORUM

Server becomes very slow [RESOURCE_SEMAPHORE] waittype.

Server becomes very slow [RESOURCE_SEMAPHORE] waittype.

ARTICLE

SQL Server DTS Resources

A list of DTS resources, including bugs, issues, books, and some good programming resources.

BLOG

PASS Virtual Chapter Events : October 2013

  Join SQL Server professionals from around the world for free online technical training and networ...

BLOG

PASS Virtual Chapter Events : October 2013

  Join SQL Server professionals from around the world for free online technical training and networ...

Tags
administration    
configuring    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones