Ajouter à une liste

Librairie Eyrolles - Paris 5e
Indisponible

Microsoft SQL Server 2000

A Guide to Enhancements and New Features

Rahul Sharma

558 pages, parution le 09/03/2002

Ajouter à une liste

Indisponible

Résumé

Microsoft's SQL Server™ 2000 promises performance, scalability, and speed. Microsoft SQL Server™ 2000: A Guide to Enhancements and New Features shows database administrators and developers how to deliver on these promises. This book illustrates all the significant enhancements and additions in SQL Server 2000 and shows how they can be used most effectively. Readers learn by example how to use security features, replication, backup procedures, and data warehousing and optimization techniques.

Beginning with an overview of SQL Server 2000, this book discusses online transaction processing (OLTP) and online analytical processing (OLAP), features a tour of different SQL Server releases, and offers a guide to installation. The author describes and demonstrates the changes since SQL Server 7.0, thoroughly exploring SQL Server 2000's capacity as a Web-enabled database server. Readers are then immersed in advanced database administration topics such as performance optimization and debugging techniques.

Microsoft SQL Server™ 2000 also serves as a resource for those new to SQL Server, providing a basic introduction and details about how to make a conversion work, as well as a feature-by-feature comparison to Oracle 9i. Database administrators will find a collection of clearly explained scripts and utilities to simplify day-to-day tasks. This book concludes with a discussion of how SQL Server fits into the .NET environment and what to expect of the next release.

Sample code is used extensively to illustrate feature sets and illuminate topics covered. The accompanying CD includes this sample code as well as scriptsand utilities. Rahul Sharma also shares his tips for optimizing applications' performance with adjustments to code, settings, and hardware. He alerts readers to common errors and fixes, and provides a checklist to guide readers through the installation and verification of the database server's stability. Rahul has translated his experience into knowledge and ready-to-use tools that database administrators and developers need to realize the many benefits of moving to SQL Server 2000.

Contents

(NOTE: Each chapter concludes with a Summary.)
Preface.
Acknowledgments.
1. SQL Server 2000 Overview.

SQL Server Editions.
Hardware Requirements for Installation.
Installation of SQL Server 2000.

Debugging Installation Issues with SQL Server 2000.

Upgrading from a Previous Version of SQL Server.
Edition Upgrades.
Adding Components to an Instance of SQL Server 2000.
Uninstalling SQL Server 2000.
Unattended Installs.
Installing SQL Server Using SMS.
Rebuilding the Registry.
Multiple Instances.

Default Instances.

Named Instances.
File Locations for Multiple Instances of SQL Server.
Network Protocols.
Collations Support for Internationalization.
Checklist for a Successful Install of SQL Server.
Maintenance and Integrity Checks.

I. NEW FEATURES AND IMPROVEMENTS OVER SQL SERVER 7.0.

2. Enhancements and Changes to Existing Features.

Feature Sets.

Fully Web-Enabled.

English Query.
More Scalable and Reliable.
BizTalk Server 2000.
Multi-Instance Support.
Reduction in Development Time.
Accessing Data via Internet.
Web-Based Analysis.
VI SAN Support.
Indexed Database Views.
T-SQL Debugger.
User-Defined Functions.
New Data Types.
Cascading DRI.
Distributed PartitionedViews.
OLAP Services.
DTS.
INSTEAD OF and AFTER Triggers.
Collation Enhancements.
Full-Text Search Enhancements.
Multiple Instances of SQL Server.
Index Enhancements.
Failover Clustering Enhancements.
Net-Library Enhancements.
64-GB Memory Support.
Distributed Query Enhancements.
Kerberos and Security Delegation.
Backup and Restore Enhancements.
Scalability Enhancements for Utility Operations.
Text in Row Data.

3. XML Support in SQL Server 2000.

Generating XML with the SELECT Statement.
Generating XML over the Internet.
Retrieving XML-Formatted Data from SQL Server.
Reserved Characters.
XML Templates.
Using EXPLICIT Mode.
XSL Stylesheets.
XML System Stored Procedures.
OPENXML.
Writing Queries Against an XML Document.

OPENXML Parameters.

XPath Expression to Identify the Nodes to Be Processed (rowpattern).
Description of the Rowset to Be Generated.
Mapping Between the Rowset Columns and the XML Nodes.
Specifying Metaproperties in OPENXML.
Examples.

4. Engine Enhancements in SQL Server 2000.

The Enhancements.
Interacting with Data.

Reading Data More Effectively.

Top n Enhanced.
Shared Scans.
Concurrency.
Tables and Indexes.

In-Row Text.

New Data Types.
Indexes.
Logging and Recovery.

Logical Log Marks.

Shrinking the Transaction Log.
Recovery Models.
Improved Backup Functionality.
Administrative Improvements.

Database Verification.

Database State Control.
System Process IDs and Units of Work.
Dynamic Tuning.
Data Storage Components.

Memory Pool.

Files, Filegroups, and Disks.

5. DTS Enhancements.

New Features in DTS.

New Custom Tasks.

Enhanced Logging Facilities.
Saving DTS Packages to Visual Basic Files.
Using the Multiphase Data Pump.
Using Parameterized Queries.
Using Global Variables to Pass Information Between DTS Packages.
Using DTS Designer.

Tasks: Defining Steps in a Package.

Workflows: Setting Task Precedence.
Connections: Accessing and Moving Data.
Data Pump: Transforming Data.

Data Pump Error Logging.

Data Pump Phases.
Options for Saving DTS Packages.
DTS as an Application Development Platform.

6. Profiler and Index-Tuning Wizard Enhancements.

SQL Profiler Terminology 113Starting Profiler Trace.

General.

Events.
Data Columns.
Filters.
Templates.
Defining Your Own Trace Using Stored Procedures.
Defining a Server-Side Trace.
Known Bug in SQL Server 2000 Profiler.

Symptoms.

Cause.
Resolution.
Index Tuning Wizard.
Analyzing Index Tuning Wizard Output.
Starting the Index Tuning Wizard.
Itwiz.exe.

7. Replication Enhancements.

Types of Replication.
Replication Options.
New Features and Improvements.

Implementing Replication.

Merge Replication.
Transactional Replication.
Queued Updating.
Transforming Published Data.
Replication Usability.
Log Shipping.

Configuring Log Shipping with the Database Maintenance Plan Wizard.

Configuring Log Shipping Manually.

8. New Data Types.

Bigint.
sql_variant.
Table.

9. User-Defined Functions.

Types of User-Defined Functions.

Scalar Functions.

Table-Valued Functions.
Obtaining Information About Functions.

10. Indexed Views.

Performance Gains from Indexed Views.
Benefits of Using Indexed Views.
Query Optimizer.
Design Considerations.
Creating an Indexed View.

Using SET Options to Obtain Consistent Results.

Using Deterministic Functions.
Additional Requirements.
Maintaining Indexed Views.

11. Trigger Alternatives.

Types of Triggers.

AFTER Triggers.

INSTEAD OF Triggers.
Core Difference Between INSTEAD OF and AFTER Triggers.
Which Trigger to Pick?
Designing INSTEAD OF Triggers.

12. Meta Data Services Enhancements.

Meta Data Fundamentals.

Meta Data Is Abstract.

Meta Data Has Context.
Meta Data Has Multiple Purposes.
Meta Data Management.
Information Model Fundamentals.
Information Model Building Blocks.
Standard Information Models.
Importance of Information Models.
New Features in Meta Data Services.

Meta Data Browser Enhancement.

XML Encoding Enhancements.
XML in Meta Data Services.

13. Tool Enhancements.

Query Analyzer.
Templates.

Using Templates in SQL Query Analyzer.

Building Your Own Template.
Copy Database Wizard.

Uses of Copy Database Wizard.

Copy and Move Process.

14. Backup and Recovery Enhancements.

Backup and Restore Architecture.

Capabilities.

Recovery Models.

Simple Recovery.

Full Recovery.
Bulk-Logged Recovery.
Switching Recovery Models.

15. Analysis Services Enhancements.

Cube Enhancements.
Dimension Enhancements.
Data Mining Enhancements.
Other Enhancements.

16. Distributed Partitioned Views.

What Are Distributed Partitioned Views?

How Distributed Partitioned Views Work.

Building Distributed Partitioned Tables and Views.
How the Query Processor Uses Distributed Partitioned Views.
Designing Systems That Maximize Distributed Partitioned View Performance.

Designing Partitions.

Designing the Application Tier for Load Balancing.
Designing for High Availability.
Backing Up and Restoring Federated Database Servers.
Updatable Partitioned Views.

Table Rules.

Column Rules.
Partitioning Column Rules.
Data Modification Rules.
INSERT Statements.
UPDATE Statements.
DELETE Statements.
Distributed Partition View Rules.

17. T-SQL Debugger.
18. Cascading Declarative Referential Integrity.

Cascading Referential Integrity Constraints.
Multiple Cascading Actions.
Triggers and Cascading Referential Actions.
Cascading Referential Constraints Catalog Information.

19. Collation Enhancements.

How Character Data Is Stored.
International Data and Unicode.
Sort Order.
What Is Unicode and How Can It Be Used?

UCS-2.

UTF-16.
UTF-8.
Data Types in SQL Server 2000.

Non-Unicode Text Types: char, varchar, text.

Unicode Text Types: nchar, nvarchar, ntext.
Date/Time Types: datetime, smalldatetime.
Collation in SQL Server 2000.
Collations Specified at Multiple Levels.

Collations at the Server Level.

Collations at the Database Level.
Collations at the Column Level.
Collations in Expressions.
Considerations Before Changing the Collation of a Database.
Collation Precedence.
COLLATE Keyword.
Limitations of the COLLATE Keyword.
Issues with Defining Collation at the Column Level.
LCIDs and Collations.
ISO Strings and Collations.
Multilingual Data in the User Interface.
Multilingual Information in the Grid and SQL Panes of SQL Query Analyzer.
Format Issues in the Query Designer.
Sort Order.
Double-Byte (DBCS) Characters.
Getting to SQL Server Data (Data Access Methods).
Multilingual Transact-SQL.

II. ADVANCED DBA TOPICS.

20. SQL-Distributed Management Objects.

What Is SQL-DMO?
SQL-DMO Object Model.
ExecuteImmediate Method.
Script Method.
SQL-DMO Code for Executing a Job.
SQL-DMO Code to Play with Triggers.

21. Microsoft Desktop Engine.

Portable and Compatible.

Minimum Requirements.

Features and Limitations.
MSDE Install.
Scenarios for Using MSDE.
Developing Applications with MSDE.

Sample Application.

MSDE Service Pack1.

22. Administration.

Database Consistency Checks (DBCCs).
Undocumented DBCC Commands.
Information Schema Views.
Orphaned Sessions.

Issues with Orphaned Sessions.

Resolving Issues through the Query Analyzer.
Changing the Check Frequency.
Linked Servers.

Creating Linked Servers.

Setting the Linked Server Options.
Creating Logins for the Linked Server.
Querying the Linked Server.

23. Debugging Database Performance Issues.

Performance Monitor or Task Manager?
Performance Monitor.

Using the Performance Monitor.

Which Process Is the Bottleneck?
Task Manager.
Performance Monitor Counters.

CPU Performance Counters.

I/O Performance Counters.
Memory Performance Counters.
Network Performance Counters.
SQL Server Performance Counters.
Settings.
Event Viewer.
SQL Server Profiler.
Hardware Issues.
Database Setup.
SQL Tips.

Create and Use Proper Indexes.

Use Clustered Indexes.
Short Index Keys.
Use Covering Indexes.
Help SQL Server Choose Indexes.
Estimate Index Selectivity.
Use the SQL Server Query Optimizer.
Use Indexes Effectively.
Use Foreign Keys for Joins.
Use Concatenated Keys Correctly.
Use Only Selective Indexes.
Avoid NULL Checks.
Avoid Functions and Expressions on the Indexed Columns.
Avoid the Inequality Operator.
Avoid Full Table Scans.
Use EXISTS to Check for the Existence of a Record.
Replace NOT IN with NOTEXISTS.
Large Insert, Update, and Delete SQLs.
Do Not Use Positional Numbers.
Use Group Functions Carefully.

24. SQL Server 2000 Service Pack 1.

Before the Install.
Installing Service Pack 1.
Install Database Components SP1.

Install Analysis Services SP1.

Install Desktop Engine SP1.
Removing Service Pack 1/Rolling Back to the Previous State.
Fixing Bugs.

Fixes for MDAC.

SQL Server 2000 Fixes.

III. COMPARISON WITH ORACLE 9I.

25. Oracle 9i vs. SQL Server 2000.

Database Definition.
Database System Catalogs.
Physical and Logical Storage Structures.
Striping Data.
Transaction Logs and Automatic Recovery.
Networks.
Comparison of Features Provided by the Two RDBMS.
New Features in Oracle 9i.

26. Migrating from Oracle 9i Databases to SQL Server 2000.

Database Security and Roles.

Database File Encryption.

Network Security.
Login Accounts.
Groups, Roles, and Permissions.
Database Users and the Guest Account.
Sysadmin Role.
db-owner Role.
Defining Database Objects.

Database Object Identifiers.

Qualifying Table Names.
Creating Tables.
Table and Index Storage Parameters.
Views.
Indexed Views/Materialized Views.

Indexes.

Clustered Indexes.
Nonclustered Indexes.
Index Syntax and Naming.
Index Data Storage Parameters.
Ignoring Duplicate Keys.
Indexes on Computed Columns.
Using Temporary Tables.
Data Types.

Using Unicode Data.

Object-Level Permissions.
Enforcing Data Integrity and Business Rules.
Naming Constraints.
Primary Keys and Unique Columns.
Adding and Removing Constraints.
Generating Unique Values.
Domain Integrety.
Referential Integrity.
User-Defined Integrity.
Delaying the Execution of a Stored Procedure.
Specifying Parameters in a Stored Procedure.
Triggers.
Transactions, Locking, and Concurrency.

Transactions.

Locking and Transaction Isolation.
Dynamic Locking.
Changing Default Locking Behavior.
Handling Deadlocks.
Insert Statements: The Forgotten Culprit.
Remote Transactions.
Distributed Transactions.
Two-Phase Commit Processing.
SQL Language Support.

SELECT and Data Manipulation Statements.

SELECT Statements.
INSERT Statements.
UPDATE Statements.
DELETE Statements.
TRUNCATE TABLE Statement.
Manipulating Data in Identity and Timestamp Columns.
Locking Requested Rows.
Row Aggregates and the Compute Clause.

Join Clauses.

Reading and Modifying BLOBs.
Functions.
Conditional Tests.
Converting Values to Different Data Types.
User-Defined Functions.
Comparison Operators.

Pattern Matches.

Using NULL in Comparisons.
String Concatenation.
Control-of-Flow Language.

Declaring Variables.

Assigning Variables.
Statement Blocks.
Conditional Processing.
Repeated Statement Execution (Looping).
GOTO Statement.
PRINT Statement.
Returning from Stored Procedures.
Raising Program Errors.
Implementing Cursors.
Declaring a Cursor.
Opening a Cursor.
Fetching Data.
CURRENT OF Clause.
Closing a Cursor.
Developing and Administering Database Replication.
Moving Data and Applications.

IV. SCRIPTS AND UTILITIES.

27. Administration and Utility Scripts.

Scripts.

Disable Constraints.

Enable Constraints.
USP_Find_Lock.
USP_Lock_Info.
Greatest.
Update Statistics.
USP_Active.
Objects.
Number_Of_Rows.
USP_Update_One_Table.
USP_System_Info.
USP_Null_Columns.
USP_SetUP_Paths.
USP_Identity_Columns.
USP_DB_File_Info.
USP_NOIndexes.
USP_RenameDB.
USP_ForceDropDB.
USP_Trace_BlackBox.
Registry_Info.
USP_KillConnections.
USP_FK_PK.
USP_DefragDatabase.
USP_DBUpdate.
USP_Calc_Space.
BackUp.vbs.
USP_PingServers.
Schema Comparison Scripts.
USP_MonitorServices.
USP_DTSReplaceOwner.
DTS Packages.

Refresh Databases.

Back Up Databases and Version Control.
ScriptData.
ExportPackages.vbs.
ImportPackages.vbs.
Executing DTS Through T-SQL.
Copying Databases from Server to Server.

Method 1.
Method 2.
Method 3.
Method 4.
Method 5.

V. SQL SERVER.NET.

28. SQL Server 2000 and .NET.

What Is Microsoft.NET?
From Products Vendor to Services Vendor.
Web Services.
.NET Servers.
SQL Server 2000 and .NET.
Next Release of Microsoft Server.
Windows XP and .NET.

Appendix.
Index.

Caractéristiques techniques

	PAPIER
Éditeur(s)	Addison Wesley
Auteur(s)	Rahul Sharma
Parution	09/03/2002
Nb. de pages	558
Format	18,5 x 23,5
Couverture	Broché
Poids	887g
Intérieur	Noir et Blanc
EAN13	9780201752830