Chapter 1 - Introducing the SQL Server 2000 Resource Kit
Welcome to the Microsoft® SQL Server™ 2000 Resource Kit. This resource kit is designed for people who are already users of SQL Server and who want some helpful tips, advanced techniques, and useful tools or samples to enhance their work with SQL Server 2000.
If you are responsible for administering or designing SQL Server databases, or developing applications that work with SQL Server, this resource kit is for you. This resource kit was written by members of the SQL Server product team and Microsoft Consulting Services, with the intention of extending the information provided in SQL Server Books Online (the online documentation included with SQL Server 2000). The information in this resource kit addresses specific questions from customers, and includes helpful tips for using SQL Server that were not available at the time SQL Server 2000 was released.
In addition to the information in this resource kit, the enclosed Microsoft SQL Server 2000 Resource Kit CD-ROM contains various SQL Server tools, utilities, and code samples. It also contains an online version of the SQL Server 2000 Resource Kit, and selected topics from SQL Server Books Online in eBook format for Microsoft Windows® CE devices.
Inside the Resource Kit
.gif "Cc917613.spacer(en-us,TechNet.10).gif")
Chapter 2: New Features in SQL Server 2000
Taken directly from SQL Server Books Online, this chapter describes the new product features that were introduced in SQL Server 2000, including Analysis Services and English Query.
Chapter 3: Choosing an Edition of SQL Server 2000
This chapter provides an overview of the editions of SQL Server, and helps you choose the edition or editions best suited to your environment and application. Specialized usage of SQL Server in development, mobile, and evaluation scenarios is discussed. Information about obtaining the editions of SQL Server 2000 is also provided.
Chapter 4: Choosing how to License SQL Server
This chapter describes what type of SQL Server licensing to implement. SQL Server has introduced a processor-based license designed for e-business customers building business-to-business, business-to-consumer, and business-to-employee scenarios. These licenses can be used where traditional measures of users or connecting devices are impossible to track. Separate server licenses and per seat CALs (Client Access Licenses) will continue to be offered to customers using SQL Server. This method of licensing may appeal to a non-Web based business with a limited number of users.
Chapter 5: Migrating Access 2000 Databases to SQL Server 2000
This chapter is intended for Microsoft Access developers and managers who are interested in migrating their Access databases to SQL Server 2000. It discusses some of the benefits this migration will provide, and focuses on moving data and queries from Access to SQL Server. Application design considerations are also discussed.
Chapter 6: Migrating Sybase Databases to SQL Server 2000
This chapter is intended for Sybase database administrators and managers who are planning a database application migration to SQL Server 2000. It outlines the steps involved in migrating a Sybase database to SQL Server and helps application developers anticipate issues, based on experience with other customers. The chapter focuses on the differences between Sybase Transact-SQL and Microsoft Transact-SQL statements, as well as administrative differences.
Chapter 7: Migrating Oracle Databases to SQL Server 2000
This chapter is for developers of Oracle applications who want to convert their applications to Microsoft SQL Server. The tools, processes, and techniques required for a successful conversion are described. The essential design points that allow the creation of high-performance, high-concurrency SQL Server-based applications are also discussed.
Chapter 8: Managing Database Change
This chapter describes the best practices for handling database changes from the earliest development stage, through the quality assurance stage, and finally to implementation in the production environment. It provides practical advice for database administrators about handling development issues that arise when supporting a 24 x 7 operation where database implementations must not be a point of failure. This chapter covers management of the development environment and the quality assurance environment. The chapter also addresses issues such as documentation and version control.
Chapter 9: Storage Engine Enhancements
This chapter provides insight into the inner workings of SQL Server architecture. It covers storage engine enhancements and provides tips for using them.
Chapter 10: Implementing Security
This chapter introduces SQL Server administrators and developers to the new security features of SQL Server 2000. New features are outlined, and a detailed discussion is provided about how to best implement security in a Microsoft Windows 2000 domain environment. Source code examples are included for developers who want to implement the security model immediately.
Chapter 11: Using BLOBs
This chapter discusses the basic principles involved when working with binary large objects (BLOBs) in SQL Server. Applying knowledge gathered from the design and implementation of the Microsoft TerraServer database, this chapter enumerates issues to consider when designing tables with BLOB data. Using the Northwind database, this chapter explains the creation of a table with text, ntext, or image columns; and explains the insertion and modification of BLOB data, both on the server and the client.
Chapter 12: Failover Clustering
This chapter presents SQL Server 2000 failover clustering as one option to create high availability for a Web site or application to increase overall system uptime. It describes the components and processes involved in failover clustering, and describes best practices, design considerations, and tips for troubleshooting.
Chapter 13: Log Shipping
This chapter introduces SQL Server 2000 log shipping as a method to create a standby server in the event of a primary server failure.
Chapter 14: Data Center Availability: Facilities, Staffing, and Operations
Data center operations are critical to meeting the high availability standards required for many enterprise and e-commerce applications. This chapter provides guidelines for data center facilities, lists the staff required to run SQL Server in a data center, and suggests standards for data center and SQL Server operations to ensure system availability and recoverability.
Chapter 15: High Availability Options
This chapter discusses the high availability options in SQL Server 2000. It covers failover clustering, log shipping, replication, SANs, and other alternatives. It also covers the importance of people, processes, policies, and procedures.
Chapter 16: Five Nines: The Ultimate in High Availability
In the world of the Internet, e-commerce, and mission critical applications, it is imperative to design highly available backend systems. This chapter gives real world examples of how to design, implement, and support a highly available server running SQL Server 2000.
Chapter 17: Data Warehouse Design Considerations
This chapter addresses issues, choices, and approaches to be considered when designing and implementing a data warehouse. Differences between data warehouses and online transaction processing (OLTP) systems, data warehouse tools such as online analytical processing (OLAP) and data mining, and the purpose of data warehousing must all be considered when designing a data warehouse. A number of considerations that occur in the design of dimensional models, slowly and rapidly changing dimensions, fact tables, and other elements of a data warehouse are discussed. The chapter also provides suggested approaches and alternate techniques.
Chapter 18: Using Partitions in a SQL Server 2000 Data Warehouse
This chapter describes how to use partitions to improve the manageability, query performance, and load speed of data warehouses in SQL Server 2000 Enterprise Edition. Horizontal partitioning of dimensional schema, both in the relational database and in Analysis Services cubes, is addressed.
Chapter 19: Data Extraction, Transformation, and Loading Techniques
This chapter addresses various design considerations and deployment issues involved in developing the ongoing processes that extract, transform, and load data in data warehousing. Topics include data staging database design, as well as techniques for using tools such as Transact-SQL, and Data Transformation Services (DTS) in data extraction, transformation, and loading (ETL) processes. The development, maintenance, and use of ETL meta data is also addressed.
Chapter 20: RDBMS Performance Tuning Guide for Data Warehousing
This chapter covers tools and techniques that can be used to tune any RDBMS, and discusses considerations when tuning an RDBMS that is used for data warehousing.
Chapter 21: Monitoring the DTS Multiphase Data Pump in Visual Basic
This chapter illustrates a technique for monitoring the phases of the DTS data pump process from an out-of-process application in real time. A sample solution presented uses a COM+ event class to communicate to a Microsoft Visual Basic® application, which graphically depicts phase activity and allows you to set phase break points during the execution of a DTS package that contains a Transform Data task or a Data Driven Query task. This may help both in the understanding of multiphase data pump behavior and in troubleshooting.
Chapter 22: Cubes in the Real World
Analysis Services provides a wide variety of features for developing OLAP solutions, but practical application of such features can be challenging. This chapter provides details and guidance for leveraging all of the features of Analysis Services, addressing dimension and cube design, dimension varieties and characteristics, cube varieties and characteristics, and processing dimensions and cubes.
Chapter 23: Business Case Solutions Using MDX
This chapter gives direct and informative answers to a wide variety of common questions involving the Multidimensional Expressions (MDX) query language, grouped according to business usage. As a query language, MDX is designed to support the specific requirements of accessing multidimensional data, including a rich library of functions. The use of MDX is key to the efficient performance of multidimensional queries, and some business questions can be complex to answer with MDX. This chapter addresses some of the more commonly asked business questions, such as how to perform basic basket analysis using MDX, and technical questions, such as how to skip levels when drilling down on multidimensional data.
Each question is explained with an example illustrating the problem, the issues involved in resolving the question, and the MDX solutions used to resolve them. Full details and practical application notes are provided for each MDX solution.
Chapter 24: Effective Strategies for Data Mining
Data mining is not just a set of powerful Analysis Services tools, but also a set of strategies used to discover meaningful business information from large amounts of historical data. This chapter provides guidance in developing data mining solutions, including a discussion on of model-driven and data-driven data mining, an overview of the process used to construct and use data mining models, and practical examples of training and prediction analysis using data mining models. Common issues are addressed at every step discussed in the data mining process, and examples are provided to illustrate the use of MDX for predictive analysis in operational and closed loop data mining.
Chapter 25: Getting Data to the Client
This chapter provides a review of, and usage guidelines for, client-side technologies that can be used to retrieve and process data and meta data stored in the Analysis Services engine. Analysis Services provides access to data and meta data through PivotTable® Service, its implementation of the OLE DB for OLAP specification. Data on the analysis server can be accessed by using PivotTable Service in conjunction with OLE DB, Microsoft ActiveX® Data Objects (ADO), or ActiveX Data Objects (Multidimensional) (ADO MD). Meta data can be accessed by using Decision Support Objects (DSO), and by using PivotTable Service with OLE DB, ADO, or ADO MD.
Brief explanations of sample applications (Meta Data Explorer, Meta Data Scripter, Schema Rowset Explorer) included in the SQL Server 2000 Resource Kit suggest possible uses of the technologies discussed. The Meta Data Scripter, for example, allows you to script the meta data of any object, including dependent objects, that can be selected in Analysis Manager; the accompanying add-in generates a Visual Basic Scripting Edition (VBScript) file that uses DSO to recreate the scripted objects.
Chapter 26: Performance Tuning Analysis Services
This chapter provides detailed technical information about performance tuning Analysis Services, including tuning Analysis Services for overall performance and tuning specifically for query and processing performance. Discussions on Analysis Services architecture, hardware management, memory and storage management, partition strategy, aggregation design, and performance evaluation techniques are all included, with data and examples provided directly from case studies.
Chapter 27: Creating an Interactive Digital Dashboard
This chapter uses a hands-on approach to explain how to deploy a SQL Server-based digital dashboard that contains interactive parts. A digital dashboard is a framework for building portals. SQL Server is one of several platforms that can be used to deploy a dashboard. Although you can build dashboards that contain stand-alone components, a more promising use of the technology is to build lightweight applications that are composed of functionally related parts. In this chapter, you can find out how to set up the tools for digital dashboard development. You also learn how to build a dashboard that displays Northwind customer data in one component and aggregated order information in a second component. The two components are related through an event-driven script that causes summarized order data to appear in response to a customer name selection.
Chapter 28: A Digital Dashboard Browser for Analysis Services Meta Data
This chapter steps through creating a digital dashboard browser to view Analysis Services meta data. Using interactive digital dashboard concepts, the components are created using VBScript and DSO. The completed dashboard allows the user to connect to a selected analysis server, choose a database, and view the cubes, dimensions, roles, and other objects made available by the DSO object model.
Chapter 29: Common Questions in Replication
This chapter provides answers to common replication questions. Subjects covered include types of replication, subscriptions, replication options, and replication implementation.
Chapter 30: Creating Merge Replication Custom Conflict Resolvers Using Visual Basic
This chapter describes how to create a Visual Basic application for merge replication conflict resolution. This chapter documents programmability that enables developers to gather information from the Publisher and Subscriber sites and use that information to resolve data conflicts and propagate the final data to both locations.
Chapter 31: Exposing SQL Server Data to the Web with XML
This chapter offers an introduction to the native XML features of SQL Server 2000. It demonstrates how to configure Internet Information Services (IIS) for SQL XML support and how to retrieve data in various XML formats from Transact-SQL queries in a URL and from XML Template files.
Chapter 32: English Query Best Practices
English Query provides an interface for querying SQL Server in English, which gives those without Transact-SQL knowledge a way to submit ad hoc queries to a database. This chapter provides best practices for starting, expanding, and deploying an English Query application using the tools provided with SQL Server.
Chapter 33: The Data Tier: An Approach to Database Optimization
This chapter outlines the process for optimizing a database in the most effective manner. It covers application/database testing, through performance tuning, and hardware configuration. The chapter follows an example-oriented approach, demonstrating how to focus database administration and design efforts.
Chapter 34: Identifying Common Administrative Issues
This chapter contains two sections. The first section describes a set of stored procedures used to identify some common configuration problems with SQL Server. These stored procedures can be packaged and distributed to DBAs across an enterprise to help maintain a consistent configuration of SQL Server. The second section explains a stored procedure that can help a DBA troubleshoot blocking problems.
Chapter 35: Using Visual Basic to Remotely Manage SQL Server 2000
This chapter provides an overview of the implementation of SQL Distributed Management Objects (SQL-DMO) and SQL Namespace (SQL-NS) objects from within Visual Basic. SQL-DMO and SQL-NS provide a COM interface for database management tasks usually available only within SQL Server itself. The chapter also includes complete Visual Basic code samples and a working application that illustrates the use of the COM interface.
Chapter 36: Using Views with a View on Performance
This chapter explains indexed views, describes specific scenarios that they enhance performance in, and provides guidelines for designing effective indexed views.
Chapter 37: Extending Triggers with INSTEAD OF
New with SQL Server 2000, the INSTEAD OF trigger increases the capabilities of views by allowing updating of multiple tables and enhanced event handling. In this chapter, design guidelines for INSTEAD OF triggers are provided along with several best-practice recommendations and sample code.
Chapter 38: Scaling Out on SQL Server
This chapter covers the process that goes into determining what type of scale-out solution might be best for your application. It also discusses the federated SQL Server configurations, and provides insight about how this technology works.
Chapter 39: Tools, Samples, eBooks, and More
This chapter describes the tools, utilities, and code samples included on the Microsoft SQL Server 2000 Resource Kit CD-ROM. The tools and utilities will help you manage and troubleshoot your SQL Server 2000 installations and databases, and the code samples will help you develop your own applications to use with SQL Server 2000.
Additional Sources of Information
SQL Server 2000 Product Documentation
SQL Server Books Online contains comprehensive documentation for SQL Server 2000. SQL Server Books Online is available from the following sources:
SQL Server 2000
MSDN Library, at https://msdn2.microsoft.com/library/
MSDN download site, at https://msdn2.microsoft.com/downloads/default.aspx
SQL Server 2000 Reference Library, published by Microsoft Press, is a condensed, printed version of carefully selected sections of SQL Server Books Online. It contains the following volumes:
SQL Server 2000 Architecture and XML/Internet Support
Database Creation, Warehousing, and Optimization
Analysis Services (OLAP)
Replication and English Query
Transact-SQL Language Reference
Transact-SQL Stored Procedures and Tables
The SQL Server Reference Library is available from retail bookstores. ISBN# 0-7356-1280-3
SQL Server 2000 Internet Sites
Microsoft SQL Server product Web site, at https://www.microsoft.com/sql/.
Microsoft SQL Server 2000 Analysis Services Web site, at https://www.microsoft.com/sql/evaluation/bi/bianalysis.asp.
Microsoft SQL Server English Query Web site, at https://www.microsoft.com/sql/evaluation/features/english.asp.
Microsoft SQL Server Developer Center, at https://msdn2.microsoft.com/sqlserver/default.aspx.
Microsoft XML Developer Center, at https://msdn2.microsoft.com/xml/default.aspx.
Professional Association for SQL Server (PASS), at https://www.sqlpass.org.
SQL Server Magazine, at https://www.sqlmag.com/.
SQL Server Newsgroups, at news://msnews.microsoft.com/.
Conventions Used in This Resource Kit
The following conventions are used to distinguish elements of text.
Convention |
Used for |
|---|---|
UPPERCASE |
Transact-SQL keywords and SQL elements |
Initial Capitals |
Paths and file names |
Bold |
Database names, table names, column names, stored procedures, command-prompt utilities, menus, commands, dialog-box options, programming elements, and text that must be typed exactly as shown |
Italic |
User-supplied variables, relationships, and phrasings |
Monospace |
Code samples, examples, display text, and error messages |
Resource Kit Support Policy
Microsoft does not support the software supplied in the SQL Server 2000 Resource Kit. Microsoft does not guarantee the performance of the tools, response times for answering questions, or bug fixes for the tools. However, Microsoft does provide a way for customers who purchase the SQL Server 2000 Resource Kit to report any problems with the software and receive feedback for such issues. To report any issues or problems, send e-mail to rkinput@microsoft.com . This e-mail address is used only for issues related to the SQL Server 2000 Resource Kit. For issues related to the SQL Server 2000 product, see the support information included with the product.