Microsoft SQL Server 2000 Resource Kit

Plan, install, maintain, and optimize SQL Server™ 2000, a key Microsoft® .NET Enterprise Server, with expertise from those who know the technology best—the SQL Server 2000 product team and Microsoft Consulting Services. This essential kit is packed full of technical details, tools, and utilities to help you maximize the productivity of your enterprise systems, create efficient database applications, and tune the performance of your servers and databases. The MICROSOFT SQL SERVER 2000 RESOURCE KIT includes information about core functionality and new features of SQL Server 2000, plus a CD-ROM full of unique tools and samples. It also covers vital topics such as data warehousing, security, failover clustering, scalability, XML, and digital dashboards. This powerhouse reference is the essential resource for every SQL Server user who seeks maximum performance from SQL Server 2000. Topics covered include:
• PLANNING: Find out what's new in SQL Server 2000; find the SQL Server edition that's right for your organization, and discover the latest information about licensing, upgrades, and migration.
• ADMINISTRATION: Learn the latest about database administration tools and features, and backup and recovery techniques.
• AVAILABILITY AND SCALABILITY: Discover the best way to cluster database applications; learn about data center staffing, facilities, and operations; and find out how to scale applications up and out with various tools.
• REPLICATION: Get answers to common questions, and find out how to create merge-replication custom conflict resolvers in Microsoft Visual Basic®.
• DATA WAREHOUSING: Develop effective data warehouses using expert techniques for design, partitions, data extraction, transformation, and loading.
• ANALYSIS SERVICES: Get practical solutions to real-world business problems using OLAP cubes, Multidimensional Expressions (MDX), data mining, and data access technologies.
• DIGITAL DASHBOARDS: Learn how to build Web portals using the latest SQL Server technology and the digital dashboard framework.
• PERFORMANCE TUNING AND SECURITY: Learn how to tune, optimize, and troubleshoot SQL Server and SQL Server–based applications for top performance, and get the facts about Microsoft SQL Server 2000 security models.
• XML: Learn how to expose SQL Server data to the Web with XML.
Included on two CD-ROMs:
• A 120-day evaluation copy of SQL Server 2000
• A fully searchable electronic copy of the RESOURCE KIT
• Selected topics of SQL Server Books Online in eBook format for your Pocket PC
• Tools, utilities, and sample code, including a cube and data-mining meta data scripter, digital dashboards, sample log shipper, Transact-SQL and system procedure quick reference, and more

Contents

Part 1: Introducing the SQL Server 2000 and This Resource Kit ..... 1

Chapter 1: Introducing the SQL Server 2000 Resource Kit ..... 3

Chapter 2: New Features in SQL Server 2000 ..... 13

Part 2: Planning ..... 45

Chapter 3: Choosing an Edition of SQL Server 2000 ..... 47

Chapter 4: Choosing How to License SQL Server ..... 57

Chapter 5: Migrating Access 2000 Databases to SQL Server 2000 ..... 63

Chapter 6: Migrating Sybase Databases to SQL Server 2000 ..... 83

Chapter 7: Migrating Oracle Databases to SQL Server 2000 ..... 105

Part 3: Database Administration ..... 211

Chapter 8: Managing Database Change ..... 213

Chapter 9: Storage Engine Enhancements ..... 233

Chapter 10: Implementing Security ..... 255

Chapter 11: Using BLOBs ..... 303

Part 4: Availability ..... 337

Chapter 12: Failover Clustering ..... 339

Chapter 13: Log Shipping ..... 373

Chapter 14: Data Center Availability: Facilities, Staffing, and Operations ..... 393

Chapter 15: High Availability Options ..... 413

Chapter 16: Five Nines: The Ultimate in High Availability ..... 443

Part 5: Data Warehousing ..... 473

Chapter 17: Data Warehouse Design Considerations ..... 475

Chapter 18: Using Partitions in a SQL Server 2000 Data Warehouse ..... 503

Chapter 19: Data Extraction, Transformation, and Loading Techniques ..... 529

Chapter 20: RDBMS Performance Tuning Guide for Data Warehousing ..... 575

Chapter 21: Monitoring the DTS Multiphase Data Pump in Visual Basic ..... 655

Part 6: Analysis Services ..... 673

Chapter 22: Cubes in the Real World ..... 675

Chapter 23: Business Case Solutions Using MDX ..... 711

Chapter 24: Effective Strategies for Data Mining ..... 741

Chapter 25: Getting Data to the Client ..... 777

Chapter 26: Performance Tuning Analysis Services ..... 797

Part 7: Digital Dashboards ..... 837

Chapter 27: Creating an Interactive Digital Dashboard ..... 839

Chapter 28: A Digital Dashboard Browser for Analysis Services Meta Data ..... 853

Part 8: Replication ..... 867

Chapter 29: Common Questions in Replication ..... 869

Chapter 30: Creating Merge Replication Custom Conflict Resolvers Using Visual Basic ..... 879

Part 9: Web Programming ..... 919

Chapter 31: Exposing SQL Server Data to the Web with XML ..... 921

Chapter 32: English Query Best Practices ..... 935

Part 10: Designing for Performance and Scalability ..... 951

Chapter 33: The Data Tier: An Approach to Database Optimization ..... 953

Chapter 34: Identifying Common Administrative Issues ..... 995

Chapter 35: Using Visual Basic to Remotely Manage SQL Server 2000 ..... 1017

Chapter 36: Using Views with a View on Performance ..... 1049

Chapter 37: Extending Triggers with INSTEAD OF ..... 1069

Chapter 38: Scaling Out on SQL Server ..... 1083

Part 11: CD-ROM Content ..... 1101

Chapter 39: Tools, Samples, eBooks, and More ..... 1103

About the Authors ..... 1109

Index ..... 1111