The SQL Server 2000 Black Book is an insightful guide to utilizing the power and flexibility of SQL Server 2000. Geared toward the intermediate to advanced database administrator and programmer, this book will help you leverage the full potential of relational database technology to create a tate-of-the-art database solution. This book will help you install and configure SQL Server 2000, solve complex database management issues.
About the Author
Paul Whitehead is a computer consultant specializing in I.T. support and administrative services for the medical and pharmaceutical industries. Based in Toronto, Canada, Paul is the author of many books.
James H. Russell became acquainted with the Internet in 1997 while he was a student at Indiana University in Indianapolis. He quickly became engulfed in the Internet, becoming editor in chief at Amiga.org and later a member of the Mozilla community, where he worked with Mozilla.org staff and contributors to rewrite the release notes and README file for the Mozilla .6 and 1.0 releases. James has also created and co-designed Web pages; most recently he co-designed www.indybahai.org for the Indianapolis Baha'i community. He also maintains his own blog at http: //weblogs.mozillazine.org/kovu/.
Dalton has spent his career solving complex design and process problems. He has focused on enterprise client/server and database solutions since 1982 and has worked as a consultant, trainer, and project manager for many of the Fortune 500 companies.
Read an Excerpt
Chapter 1: Server HardwareSystems administrators face many challenges today in setting up a new server, regardless of the operating system (OS), application, and hardware manufacturer. An often difficult task is to adapt the installation to the needs of the environment. Administrators should look at as many factors as possible prior to setup to ensure that the machine is configured to the requirements of the users and the application load placed on the server. This approach will help spare administrators from having to upgrade the equipment almost immediately when their performance expectations are not met. Understanding the basic components of server hardware is important for planning your first installation, reinstallation, or upgrade. The following pages will help clear the way and help you choose or configure your server hardware.
Recommended System ConfigurationsThere are many aspects of a system configuration that should be considered. You need to think about how much of a load the server needs to be able to handle. You also need to think about what part of the system will most likely be your bottleneck. And unfortunately, you'll probably need to keep an eye on the budget as well.
Microsoft's recommendations for your system and what most administrators have found to be a more realistic configuration for your server are quite different. Keep in mind that Microsoft's recommended minimum configuration is just that, the bare minimum that SQL Server will install on. Microsoft's recommendations should be implemented with care. Each environment is unique and is rarely a fit for minimums or guidelines. Likewise, other recommendations should also not be followed blindly. Recommendations are intended to give you an idea of where to start and should not be considered the end all solution for choosing your system or platform.
The system requirements for installing Microsoft SQL server are actually very easy to meet, and often lead the administrator into a false sense of security with regard to how well the server will perform. Table 1.1 contains Microsoft's minimum system requirements. Remember, these are the minimum system requirements.
Table 1.1 Microsoft's minimum system configuration....
...The minimum CPU recommendation of an Intel-based Pentium 166 is usually a poor choice for a production machine. This kind of machine should only be considered for a development-type environment. Even then, it should only be used for very light loads. The preference is to use a Pentium II 400MHz or equivalent, at minimum. But depending on the application, processor speed will probably be much less important than memory or disk subsystems.
That's not to say that you should discard your current machine or scrap your plans for a cheaper alternative. Budgets and real-world requirements often do not allow a top-of-the-line machine for every project. The idea is to put your best performing machine as well as your money where it will do the most good. If you are using existing hardware for your data server, take a good inventory of what makes the target machine tick. Know the specifics of the disk access time and memory configuration. Benchmark the machine wherever possible to get an idea of how well it is performing against others in the same class. You might find a less expensive alternative to the planned configuration. Sometimes, a simple memory upgrade is good enough to get a server performing reasonably well.
The minimum RAM recommendation of 32MB for Standard Edition and 64MB for Enterprise Edition is also usually a poor choice for a production machine or even a development machine. Development servers with a very light load should have a minimum of 128MB of RAM, especially if you are running Windows 2000. Actually, the minimum amount of memory in any machine needs to be 128MB for Window NT 4 and 192MB for Windows 2000. You would be hard-pressed to even try to install Windows 9x on a system with 32MB of RAM. RAM is a very performance-sensitive item that can make a significant difference and is relatively cheap and easy to add to a system. Most newly installed production servers today are configured with 256MB to 2GB of RAM with the occasional 4GB machine. Current hardware and specific operating systems can even handle up to 16GB, whereas Windows 2000 DataCenter Server will recognize up to 64GB of RAM. DataCenter is not likely to be a widespread solution due to cost, but rather a hosting solution for only the larger installs.
Always specify more RAM than you think you will need for the server. Have at least 256MB of RAM for a production server. This amount of memory gives you plenty of space for Microsoft SQL Server and a good amount of data cache and procedure cache for performance. You cannot go wrong by adding RAM. Even on an old Pentium Pro processor, giving it additional RAM will allow it to run very well.
Adding RAM to a server is almost always the best starting point in improving performance. However, don't forget about multiprocessor machines. Machines that can hold four or more processors normally have a higher I/O bandwidth as well as improved upgrade paths. As important as RAM is-and it is very important-you also need to take a good look at your drive subsystem. Databases are by nature very I/O-intensive, and your drives can quickly become the bottleneck of the system.
With constant changes in hardware, any server purchase you make today will likely be outdated by the time you finally get your system up and running. This can be very frustrating. Consequently, you should buy servers with good expandability and lots of options. The potential expansion allows you to keep up with changes in the industry and react to changes in software requirements over time. You may want to purchase brand-name servers so that you don't invest money in machines that have poor technical support or that might not be supported the following year. Always check the Windows Hardware Compatibility List (HCL). This is a must. Check each component, from CPU to disk controller, when needed. This ensures that you will not have an operating system problem with the server you are configuring.
NOTE: You can check the current HCL online at www.microsoft.com/hcl/default.asp to get up-to-date information on available options.
You may want to configure your servers with a RAID disk subsystem for your data, which will be covered in detail later in the chapter. When reliable access to data is critical, you should require some sort of RAID configuration for the data to reside on. With the capability of Microsoft Windows NT to implement RAID at the operating system level, this is easily accomplished with even a limited budget. But keep in mind that a hardware solution is always going to perform much better than a software solution.
You may also want to try to keep the operating system and program files separate from the data and log files. Place these files on a separate disk and controller from the data files as well as the log files and mirror the disk when the budget allows. This provides the maximum amount of protection from hard drive failures while keeping performance at the highest possible levels. The number of disks in the RAID 5 array can be as few as three and as many as the disk subsystem can support.
Not everyone can afford this type of configuration for his or her hardware. Nevertheless, it is highly recommended for a fault-tolerant data server. If your budget is tight, cut the hardware mirror and RAID controller out of the plans for the operating system drives and transaction log drives. The data drive RAID subsystem should be the last system you relinquish. Use the built-in, software-driven, RAID option on Microsoft Windows NT servers only as a last resort. You should use this feature and set up your own fault-tolerant disk system for storing data only if no other option is available.
Given the many ways Microsoft SQL Server can write backups of databases to shared drives on other machines, a tape drive for backups is not required on the data server as well. However, this can be a nice feature if you run around-the-clock operations and need to keep performance at high levels 24 hours a day. Moving the backup software and hardware load to another machine is in keeping with the distributed-load concept, which is becoming popular in many enterprises today. You will find techniques in Chapter 5 that will allow you to keep the tape drives off your database server. If you must put your tape drive in the database server, remember not to attach it to the same controller as the data and log devices. (This could slow down performance unnecessarily.)
Invest in a good network interface card-a card with as much bus speed and bandwidth as possible. If you are setting up a cluster, it is recommended that you put two network cards in the server: one for user connections and the other for cluster communications. Standard 100BaseTx Bus Mastering network cards are considered a standard part of servers. Some administrators like to connect to their servers using a switch instead of a hub. Packet switches for heavily used servers are a must. They allow you to connect at full duplex as well as limit the bottleneck of slow networks. If you are setting up a large server environment, you may even want to consider Gigabit Ethernet or some other high bandwidth option for your backplane.
Most of the time you won't need to go overboard on the CD-ROM because you may rarely use it for production purposes. Try to use whichever speed comes with the server. You definitely want to watch where you connect your CD-ROM drive. Most brand-name servers are shipped with IDE CD-ROMs. This keeps the CD-ROM off the SCSI data channels. Placing the CD-ROM or other slow devices, like tape drives, on the same channel as the data slows the entire bus to a crawl. So, if you are going to install a tape backup drive directly on your server, put it on a separate bus from the data.
There is no need for expensive monitors, video cards, or sound cards. This is where you can save your money and buy more RAM. Keep it simple. Video memory is not an issue on a data server. And there is no reason for a sound card in a data server unless you want to hear the server say, "I am slowing down now. Please let your users know. Never mind, they already know." If you follow the recommendations in this book, your server will be just a data server, which is best for your users.
Verify that you have full functionality on the server before installing Microsoft SQL Server. One of the hardest things to do is troubleshoot problems when you have no idea what is working and what is not. Assume nothing. Always use the break-it-down-into-the-simplest-form approach in troubleshooting. If you cannot get out on the network, no one will be able to connect to your server for data.
Consider using a redundant power supply for your unit. Keeping the data available should be any systems administrator's primary focus. Use an uninterruptible power supply (UPS) that is reliable, and test it occasionally. An untested backup strategy is just that: untested. If you think you're under pressure now, wait until the backup won't restore and the system your boss has invested thousands of dollars in does not work. See Table 1.2 for an example of what is considered a minimum system configuration in today's production environment....
Table of Contents
|Chapter 1||Server Hardware||1|
|Chapter 2||Windows Tuning||27|
|Chapter 3||Clustering and Multi-Instances||51|
|Chapter 4||Installing and Upgrading to Microsoft SQL Server 2000||91|
|Chapter 5||Microsoft SQL Server Tools||121|
|Chapter 6||Server Maintenance||209|
|Chapter 7||Data Transformation Services (DTS)||281|
|Chapter 8||Black Book Databases||343|
|Chapter 9||Structured Query Language (SQL)||389|
|Chapter 10||New Features||437|
|Chapter 11||Performance, Tuning, and Optimization||473|
|Chapter 12||Error Handling||531|
|Chapter 14||Stored Procedures and External Stored Procedures||619|
|Chapter 16||Database Design||715|
|Chapter 18||Meta Data||787|
|Chapter 19||Advanced Techniques||811|
|Chapter 21||English Query||891|
|Appendix A||Book Database Schema||921|
|Appendix B||Newsgroups and the Internet||925|
Welcome to the most exciting version of Microsoft SQL Server released to date. Whether this is your first exposure to Microsoft SQL Server or you are upgrading from a previous version, you are sure to find SQL Server 2000 a joy to work with. Over the years, Microsoft SQL Server has evolved from a state-of-the-art client/server database platform into a word-class n-tier database platform that delivers all the power and functionality you need to produce both client/server and Web-enabled applications that will simply amaze your users. Here is the database platform that will take you into the new millennium in style. With new data types, user-defined functions, Internet support, and many more new features, this version of SQL Server is poised to take the market by storm.
SQL Server 2000 Black Book will help you make the most of this exciting new version of SQL Server, as well as provide you with many tips and techniques that can be applied to legacy systems back to at least SQL Server 6.5 and forward into future versions of SQL Server for years to come. This book provides help and guidance for building and configuring your SQL Server and designing rock-solid maintenance plans. Then it moves on to database design, development, and performance tuning topics that will help you make the most of this remarkable database platform.
Is This Book for You?SQL Server 2000 Black Book was written with the intermediate or advanced user in mind. One of the points emphasized in this book is that you can solve any technical problem you are faced with using the tools available to you. What are those tools? How do you research answers to questions? How do you know if you can trust the sources you consult? How do particular features really work, and will they work for you? We cover all of these questions and many more in the pages of this book. We hope that you enjoy reading it and learn as much from it as we did writing it.
This book is geared toward two basic groups of readers. The first group is the database administrators with an intermediate to advanced level of SQL Server knowledge and experience. The second group is SQL developers, also with an intermediate to advanced level of SQL Server knowledge and experience. The Tools chapter (Chapter 5), and the SQL chapter (Chapter 9) in particular are provided to ensure that readers have a solid grounding in these two areas, because this information is key to understanding the material presented in later chapters. Some beginners may find these chapters useful as well. You should possess a good working knowledge of SQL Server and Transact-SQL programming. This book also assumes a basic understanding of Windows NT and windows programs and utilities in general.
Among the topics that are covered are:
- Hardware choices for server planning
- Server maintenance routines every database administrator should know
- New features available in SQL Server 2000
- Advanced SQL programming techniques
- Advanced data manipulation routines
- Database design guidelines for enterprise-level databases
We have tried to keep the language in this book as clear and matter-of-fact as possible. We have supplied you with a substantial technical background, while also supplying numerous hands-on examples to help you quickly become familiar with various topics. This book can be used as a tutorial or as a desktop reference to help you get the most from SQL Server 2000. With this book in hand, you will have no problem creating and maintaining enterprise-level solutions with SQL Server 2000 at their core.
How to Use This BookEach chapter in this book is broken into two sections. The first part of each chapter presents explanatory material about the topics covered in the chapter. The second part of the chapter (the Immediate Solutions) supplies you with some step-by-step examples that reinforce and expand on the information provided in the first part of the chapter as well as providing hands-on practice with the concepts being presented. Chapters 1 through 8 cover installation, configuration, tools and utilities, and setting up the sample databases used throughout the remainder of the book. After completing this section of the book, you should have a working SQL Server 2000 server with a solid maintenance plan in place and the sample databases installed and be ready to work through the examples in the remainder of the book.
The Black Book PhilosophyWritten by experienced professionals, Coriolis Black Books provide immediate solutions to global programming and administrative challenges, helping you complete specific tasks, especially critical ones that are not well documented in other books. The Black Book's unique two-part chapter format-thorough technical overviews followed by practical immediate solutions-is structured to help you use your knowledge, solve problems, and quickly master complex technical issues to become an expert. By breaking down complex topics into easily manageable components, this format helps you quickly find what you're looking for, with the diagrams and code you need to make it happen.
Writing this book has been both difficult and rewarding. It is always interesting to work with a beta version of a product this complex, but writing a book using a product beta can be especially challenging. When the final release version of the product was reviewed, we made very effort to make sure we were up-to-date and that nothing had changed. If you find something that is perhaps a bit different than the actual product, we apologize.
We welcome your feedback on this book. You can either email The Coriolis Group at email@example.com, or you can reach the authors on the Internet through email. Patrick can be reached at firstname.lastname@example.org, and Paul can be reached at email@example.com. Please do not hesitate to send us feedback-whether positive or negative-concerning this book. Errata, updates, and more are available at www.coriolis.com.