"1U YYEAR TUPGRADE B ER PRO ECTION PLAN
.net ENTERPRISE
™
SQL SERVER 2000 DATABASES
®
DESIGNING
FOR
SERVERS
FREE Monthly Technology Updates FREE Downloadable HTML FREE Membership to Access.Globalknowledge
“This book is a solid introduction to a critical component of the Windows 2000 Server family. It will be a valuable title in your IT library.”
—Richard Martin, Database Administrator and Windows DNA Developer, MCP+I, MCSE, MCSD, MCDBA, MCT Dominion Technology Group, Inc.
Robert Patton, MCDBA, MCSD, MCSE+I, MCP+I Jennifer Ogle, MCSE, MCNE, Oracle DBA TECHNICAL EDITOR and CONTRIBUTOR: Travis Laird, MCSE, MCDBA, A+, Network+, i-Net+, CIW
solutions@syngress.com
With over 1,500,000 copies of our MCSE, MCSD, CompTIA, and Cisco study guides in print, we have come to know many of you personally. By listening, we've learned what you like and dislike about typical computer books. The most requested item has been for a web-based service that keeps you current on the topic of the book and related technologies. In response, we have created solutions@syngress.com, a service that includes the following features:
s
A one-year warranty against content obsolescence that occurs as the result of vendor product upgrades. We will provide regular web updates for affected chapters. Monthly mailings that respond to customer FAQs and provide detailed explanations of the most difficult topics, written by content experts exclusively for us. Regularly updated links to sites that our editors have determined offer valuable additional information on key topics. Access to “Ask the Author”™ customer query forms that allow readers to post questions to be addressed by our authors and editors.
s
s
s
Once you’ve purchased this book, browse to
www.syngress.com/solutions.
To register, you will need to have the book handy to verify your purchase. Thank you for giving us the opportunity to serve you.
SQL SERVER 2000 DATABASES
FOR
DESIGNING
.NET ENTERPRISE SERVERS
Syngress Publishing, Inc., the author(s), and any person or firm involved in the writing, editing, or production (collectively “Makers”) of this book (“the Work”) do not guarantee or warrant the results to be obtained from the Work. There is no guarantee of any kind, expressed or implied, regarding the Work or its contents. The Work is sold AS IS and WITHOUT WARRANTY. You may have other legal rights, which vary from state to state. In no event will Makers be liable to you for damages, including any loss of profits, lost savings, or other incidental or consequential damages arising out from the Work or its contents. Because some states do not allow the exclusion or limitation of liability for consequential or incidental damages, the above limitation may not apply to you. You should always use reasonable case, including backup and other appropriate precautions, when working with computers, networks, data, and files. Syngress Media® and Syngress® are registered trademarks of Syngress Media, Inc. “Career Advancement Through Skill Enhancement™,” “Ask the Author™,” “Ask the Author UPDATE™,” “Mission Critical™,” and “Hack Proofing™” are trademarks of Syngress Publishing, Inc. Brands and product names mentioned in this book are trademarks or service marks of their respective companies. KEY 001 002 003 004 005 006 007 008 009 010 SERIAL NUMBER 58PPL99DSE LSKDJ9878M C3N44T8FQ7 KJ675HCC25 QCUCA94D26 PF62XD2G73 DT74HH52A4 LKJFARY343 65SKNSDAD5 6487FPS25N
PUBLISHED BY Syngress Publishing, Inc. 800 Hingham Street Rockland, MA 02370 Designing SQL Server 2000 Databases for .NET Enterprise Servers Copyright © 2001 by Syngress Publishing, Inc. All rights reserved. Printed in the United States of America. Except as permitted under the Copyright Act of 1976, no part of this publication may be reproduced or distributed in any form or by any means, or stored in a database or retrieval system, without the prior written permission of the publisher, with the exception that the program listings may be entered, stored, and executed in a computer system, but they may not be reproduced for publication. Printed in the United States of America 1234567890 ISBN: 1-928994-19-9 Copy edit by: Darlene Bordwell Technical edit by: Travis Laird Project Editor: Maribeth Corona-Evans Index by: Robert Saigh Page Layout and Art by: Shannon Tozier Co-Publisher: Richard Kristof
Distributed by Publishers Group West
Acknowledgments
We would like to acknowledge the following people for their kindness and support in making this book possible. Richard Kristof, Duncan Anderson, Jennifer Gould, Robert Woodruff, Kevin Murray, Dale Leatherwood, Rhonda Harmon, and Robert Sanregret of Global Knowledge, for their generous access to the IT industry’s best courses, instructors and training facilities. Ralph Troupe, Rhonda St. John, and the team at Callisma for their invaluable insight into the challenges of designing, deploying and supporting world-class enterprise networks. Karen Cross, Lance Tilford, Meaghan Cunningham, Kim Wylie, Harry Kirchner, Bill Richter, Kevin Votel, Brittin Clark, and Sarah MacLachlan of Publishers Group West for sharing their incredible marketing experience and expertise. Mary Ging, Caroline Hird, Simon Beale, Caroline Wheeler, Victoria Fuller, Jonathan Bunkell, and Klaus Beran of Harcourt International for making certain that our vision remains worldwide in scope. Anneke Baeten, Annabel Dent, and Laurie Giles of Harcourt Australia for all their help. David Buckland, Wendi Wong, Daniel Loh, Marie Chieng, Lucy Chong, Leslie Lim, Audrey Gan, and Joseph Chan of Transquest Publishers for the enthusiasm with which they receive our books. Kwon Sung June at Acorn Publishing for his support. Ethan Atkin at Cranbury International for his help in expanding the Syngress program. Joe Pisco, Helen Moyer, and the great folks at InterCity Press for all their help.
v
From Global Knowledge
At Global Knowledge we strive to support the multiplicity of learning styles required by our students to achieve success as technical professionals. As the world's largest IT training company, Global Knowledge is uniquely positioned to offer these books. The expertise gained each year from providing instructor-led training to hundreds of thousands of students worldwide has been captured in book form to enhance your learning experience. We hope that the quality of these books demonstrates our commitment to your lifelong learning success. Whether you choose to learn through the written word, computer based training, Web delivery, or instructor-led training, Global Knowledge is committed to providing you with the very best in each of these categories. For those of you who know Global Knowledge, or those of you who have just found us for the first time, our goal is to be your lifelong competency partner. Thank your for the opportunity to serve you. We look forward to serving your needs again in the future. Warmest regards,
Duncan Anderson President and Chief Executive Officer, Global Knowledge
vi
Contributors
Robert A. Patton (MCDBA, MCSD, MCSE+I, MCP+I) is a Software Engineer specializing in Microsoft Windows DNA applications using Interdev and Visual Basic with Windows 2000 and SQL Server. He is currently a Senior Applications Developer at PurchasingFirst.com in Dublin, OH and, in his position there, has done work for First Union National Bank, Corporate Strategic Services, the Midland Life Insurance Company, and Sykes Enterprises. Robert attended the University of Chicago, where he studied Public Policy. He earned his Bachelor of Science degree in Software Engineering from The Ohio State University. He lives in Dublin, OH with his wife Jenny and their sons Michael and Alex. Jennifer Ogle (MCSE, MCP+I, MCNE, Oracle DBA) is Owner and President of Radioactive Frog Web Designs, Inc. (www.radfrog.com), which specializes in contract Web site design and development and database administration. Jennifer has over 20 years of experience in the IT industry, specializing in government, scientific, and manufacturing applications. Her recent assignments include SQL and Oracle development for Hoover Materials Handling Group and Lockheed Martin. In addition to her extensive software and hardware experience, she has programmed with Java, Visual Basic, and Visual InterDev, among many others. Jennifer lives with her husband Kent in Knoxville, TN. Sean Campbell (MCSE, MCDBA, MCSD, MCT) is an Owner of 3 Leaf Solutions, LLC. He has worked in the education field for 7 years, with 5 years focused on IT training and consulting. Sean has worked with SQL Server since version 6. He has developed custom training and delivered MOC training for a number of years on SQL Server and other development technologies and has authored numerous Microsoft TechNet sessions on topics such as supporting SQL and upgrading to SQL Server 2000. Sean’s consulting experiences have run the gamut from database administration and application upgrades to .NET development. Sean has been working on projects with Microsoft since early Beta releases of SQL Server 2000 and has focused on SQL Server 2000’s administration and implementation enhancements.
vii
Mark Horninger (A+, MCSE+I, MCSD, MCDBA) is President and Founder of Haverford Consultants, Inc. (www.haverford-consultants.com), located in the suburbs of Philadelphia, PA. He develops custom applications and system engineering solutions, specializing primarily in Microsoft operating systems and Microsoft BackOffice solutions. Mark has over 12 years of computer consulting experience and has passed 26 Microsoft certified exams. During his career, Mark has worked on many extensive projects including database development using SQL 6.5, SQL 7, and SQL 2000; application development; training; embedded systems development; and Windows NT and 2000 project rollout planning and implementations. Mark lives with his wife Debbie and two children in Havertown, PA. Steve Maier (MCSD) is a Senior Software Engineer for Heidelberg Digital L.L.C. (www.us.heidelberg.com), located in Rochester, NY. He develops custom PostScript drivers for Microsoft operating systems. Steve has 10 years of computer programming experience in DOS, Windows, and UNIX. During his career, Steve has done database development, manufacturing application development, driver development, and game development. He has also worked on FDA-approved medical software and has taught a variety of college classes including programming, system analysis, and PC repair. Steve lives with his wife Lisa and two children in Rochester, NY. Henk-Evert Sonder (CCNA) has over 15 years of experience as an Information and Communication Technologies (ICT) professional, building and maintaining ICT infrastructures. In recent years, he has specialized in integrating ICT infrastructures with business applications and the security that comes with it. Currently, Henk works as a Senior Consultant for a large Dutch ICT solutions provider. His company, IT Selective, helps retailers get e-connected. Henk has also contributed to other Syngress books, including the E-mail Virus Protection Handbook (ISBN: 1-92899423-7). Scott Delaney (MCSD, MCDBA, MCSE) is a Senior Consultant with TurnAround Solutions (www.turnaroundsolutions.com), a leading e-commerce consulting and product development firm based in Australia. TurnAround has a core client list including blue chips and multinationals and is known for its well-trained staff, processes, and quality development. In addition to his database work with SQL Server and Oracle, Scott also develops custom C++ and Java solutions. His current pet project is the
viii
development of SayIT, a full-featured speech synthesis package for use by people who have lost the ability to speak due to illnesses such as ALS or stroke. Scott lives in Tasmania, Australia with his wife Paula. John Iwasz (MCSD) has over 5 years consulting experience developing custom applications. He specializes in n-tier and Web development using Microsoft technologies. John recently wrote an article on the IIS Metabase for ASP Today. During his career, John has worked on enterprise-wide projects as well as commercial development. His has designed and developed Web site front ends, middle tier components, and determined database architecture. John lives in Philadelphia, PA. Melissa Craft (CCNA, MCSE, Network+, MCNE, Citrix CCA) is Director of e-Business Offering Development for MicroAge Technology Services. MicroAge is a global systems integrator headquartered in Tempe, AZ. MicroAge provides IT design, project management, and support for distributed computing systems. Melissa is a key contributor to the business development and implementation of e-business services. As such, she develops enterprise-wide technology solutions and methodologies focused on client organizations. These technology solutions touch every part of a system’s lifecycle—from network design, testing, and implementation to operational management and strategic planning. Melissa holds a bachelor’s degree from the University of Michigan and is a member of the IEEE, the Society of Women Engineers, and American MENSA, Ltd. Melissa currently resides in Glendale, AZ with her family, Dan, Justine, and Taylor. Melissa is the author of Syngress Publishing’s best-selling Managing Active Directory for Windows 2000 Server (ISBN: 1-928994-07-5). Cameron Wakefield (MCSD, MCP) is a Senior Software Engineer at Computer Science Innovations, Inc. (www.csihq.com) in Melbourne, FL, where he develops custom software solutions ranging from satellite communications to data-mining applications. His development work spans a broad spectrum including Visual C++, Visual Basic, COM, ADO, ASP, Delphi, CORBA, and UNIX. Cameron also develops software for a Brazilian hematology company as well as business-to-business Web applications. He also teaches Microsoft certification courses for Herzing College (AATP). He has passed 10 Microsoft certification exams. Cameron’s formal education was in computer science with a minor in math at Rollins College. He lives in Rockledge, FL with his wife Lorraine and daughter Rachel.
ix
Bret Stateham (CNE, MCSE, MCSD, MCDBA, MCT) is the Owner of Net Connex Technology Training and Consulting, L.L.C. (www.netconnex.com) in San Diego, CA. Bret’s background includes extensive networking, development, and training experience. His most recent projects have involved custom training, database development and administration, XML schema development and standardization, custom application development, and countless hours developing Active Server Pages-based Web solutions. Bret and his wife Lori live in San Diego, CA.
Technical Editor and Contributor
Travis Laird (MCSE, MCDBA, A+, Network+, i-Net+, CIW) has extensive experience in a broad range of technology subjects ranging from network design and support through software design and development. Travis has worked on numerous e-commerce and line-of-business solutions based on Microsoft technologies including Windows 2000, Visual Basic, IIS, and SQL Server. Travis currently works as an Independent Consultant in the New York City area. He recently designed and developed a Web-based e-commerce solution allowing a client to reach global markets with over 30,000 products. He attended Syracuse University and graduated with a Bachelor of Science degree in Finance and Management Information Systems. Travis lives in Westchester, NY and enjoys traveling with his ever-supportive fiancée Maria.
x
Contents
Introduction Chapter 1 SQL Server 2000 Overview and Migration Strategies
Introduction Overview of SQL Server 2000: A .NET Enterprise Server The Future of Windows DNA: Microsoft.NET New and Enhanced Features of SQL Server 2000 XML Support Development Tools and Technologies Query Analyzer New Data Types Indexed Views Trigger Enhancements Referential Integrity Enhancements User-Defined Functions Index Enhancements Shared Session Information Collation Support Extended Properties Meta Data Services Analysis Services OLAP Enhancements Data Mining SQL Server Administration Windows 2000 Active Directory Integration Scalability and Availability Scalability Enhancements Distributed Partitioned Views Fail-Over Clustering Log Shipping Data Transformation Services Replication Services Active Directory Integration
xxvii 1
2 3 4 8 10 14 14 15 16 16 17 18 18 19 19 20 20 20 21 22 23 24 24 25 26 26 27 27 28 29
xi
xii
Contents
Queued Updating Subscribers Multiple Server Instance Support Web and Internet Standards Support Web Access Using Hypertext Transfer Protocol Secure Sockets Layer SQL Server 2000 Versions, Features, and Requirements Common Edition Requirements SQL Server Licensing and Pricing Enterprise Edition Hardware Requirements and Capacity Limits Operating System Compatibility Standard Edition Hardware Requirements Software and Operating System Compatibility Personal Edition Hardware Requirements Software and Operating System Compatibility Developer Edition SQL Server 2000 Desktop Engine SQL Server 2000 Windows CE Edition Should You Migrate to SQL Server 2000? How Will SQL Server 2000 Benefit My Organization? Will SQL Server 2000 Fit into My Organization? Steps to a Successful SQL Server Migration Planning a SQL Server Migration Determine Existing and Future Application Requirements Inventory Existing Database Servers Train Database Administrators and Support Personnel Next Steps to Successful SQL Server Migration Migrating to SQL Server 2000 Upgrading from SQL Server 6.5: Active/Passive Mode Upgrading from SQL Server 6.5: Active/Active Mode Upgrading from SQL Server 7.0: Active/Passive Mode Upgrading from SQL Server 7.0: Active/Active Mode Summary FAQs
29 29 31 31 31 32 33 34 35 35 36 36 36 37 37 37 38 38 39 39 41 42 43 43 44 45 45 45 46 46 49 50 50 50 52 54
Chapter 2 Installing and Configuring SQL Server 2000
Introduction Planning a SQL Server Installation Installation Requirements Hardware Requirements Software Requirements SQL Server Licensing
57
58 59 60 60 61 62
Contents
xiii
Installation Options Local vs. Remote Installation Creating Service Accounts for SQL Server Changing User Accounts Disk Imaging Support Answer File for Automated Installations Multiple Server Instances Collation Options Upgrading to SQL Server 2000 Installing SQL Server Standard Installation Advanced Installation Configuring Cluster Support Unattended Installation Configuration Options and Settings SQL Server Properties Server Network Utility Client Network Utility SQL Server Agent SQL Mail Summary FAQs
63 63 65 66 67 67 68 68 69 71 71 75 75 76 76 76 86 87 89 90 92 92
Chapter 3 SQL Server Scalability and Availability
Introduction Scaling Up vs. Scaling Out TPC Benchmarks SQL Server Fail-Over Clustering SQL 2000 Fail-Over Clustering Architecture Planning for SQL Server Clustering Clustering Capabilities and Requirements Microsoft Cluster Service System Area Networks Shared Disks Log Shipping Implementing Fail-Over Clustering Setting Up Network Adapters Setting Up Shared Disks Setting Up MSCS on NT 4.0 Setting Up MSCS on Windows 2000 (Advanced Server/Datacenter Server) Upgrading to SQL Fail-Over Clustering Setting Up SQL Server 2000 Fail-Over Clustering Distributed Partitioned Views Federated Servers Data Partitioning
93
94 94 97 98 99 99 100 101 101 102 102 102 103 103 104 104 105 108 109 109 111
xiv
Contents
Creating Distributed Partitioned Views Creating Linked Servers Partitioning Your Data Creating a Distributed View Using and Updating a Distributed View Log Shipping Setting Up Log Shipping Monitoring Log Shipping Indexed Views Requirements for an Indexed View Creating an Indexed View Summary FAQs
111 111 112 112 113 115 116 120 121 122 124 124 125
Chapter 4 Designing and Creating SQL Server Databases
Introduction SQL Server 2000 Architecture Relational Databases SQL Server System Databases Master TempDB msdb Model Pubs Northwind Physical Sto..."
|
You need to upgrade your Flash Player , or try to enable javascript in order see this document properly.
|
|
Comments
