Microsoft SQL Server 2000 DBA Survival Guide
Published on 2. March 2001
Book
Mixed media product
1008 pages
978-0-672-32007-1 (ISBN)
Description
Microsoft SQL Server 2000 DBA Survival Guide is organized into several parts that comprise the various jobs and tasks the DBA performs. Each chapter is presented with the goal of providing knowledge and "know-how" to Database Administrators of a SQL Server database. The chapters also offer real world insight and experience by passing on tips, tricks, and suggestions based on what the authors have learned the hard way. The book also takes time to provide checklists and examples for various SQL Server DBA tasks. The book also provide insight into the tasks that make up a DBA's job including discussions of topics such as the creation of naming standards and conventions which are essential for efficient administration.
More details
Language
English
Place of publication
Indianapolis
United States
Publishing group
Pearson Education (US)
Target group
College/higher education
Dimensions
Height: 232 mm
Width: 185 mm
Thickness: 50 mm
Weight
1647 gr
ISBN-13
978-0-672-32007-1 (9780672320071)
Copyright in bibliographic data and cover images is held by Nielsen Book Services Limited or by the publishers or by their respective licensors: all rights reserved.
Schweitzer Classification
Other editions
New editions
Mark Spenik | Orryn Sledge
Microsoft SQL Server 2000 DBA Survival Guide
Book
10/2002
2nd Edition
Sams Publishing
€50.12
Article is exhausted; no reprint
Persons
Mark Spenik is the vice president of Enterprise Technologies at Trilogy Consulting located in Richmond, VA. Mark, a graduate of George Mason University in Fairfax, Va., entered the computer industry in 1985. He has designed and coded large-scale applications and has consulted with numerous firms in application development, implementation, and migration. He has a broad programming background including assembly language, C, C++, HTML, Active Server Pages, and Visual Basic. Mark is a Microsoft Certified Solution Developer (MCSD) and charter member, and is frequently invited to speak at various developer conferences and seminars. Mark has also co-authored or contributed to several books on Microsoft SQL Server or Visual Basic. Mark can be reached via the Internet mspenik@trilogyva.com.
Orryn Sledge is a Practice Director with Fulltilt Solutions in Pittsburgh, Pennsylvania. He specializes in managing and developing large-scale eBusiness solutions. His background includes the Microsoft Internet platform, Oracle, and other popular eBusiness products. He has been actively involved with SQL Server consulting since 1992 and is a Microsoft Certified Solution Developer (MCSD). Orryn is also a frequent speaker at various Microsoft conferences and presentations. He can be reached at osledge@fulltilt.com.
Kari A. Fernandez is an Enterprise Technologies consultant for Trilogy Consulting, in Richmond, Virginia. In 1994 she graduated from Virginia Commonwealth University's School of Business with a bachelor's degree in Information Systems. Since graduation she has specialized in developing database-driven applications for the Web. Kari employs the use of Microsoft's Windows NT Server, Internet Information Server, and SQL Server, as well as Visual Studio software, as the Web development foundation. Kari has succeeded in rapidly developing small Internet and intranet Web applications and has participated in team development of larger-scale Web applications. She assists clients in conceptualizing and analyzing their strategies for Web site architecture and database design. Her expertise in Web site development combines this and the use of Active Server Pages, the ActiveX Data Object model, and SQL Server along with other Web technologies such as VBScript, JavaScript, HTML, and Dynamic HTML. She has also worked with clients in setting up Web development and production site security and version control as well as determining the client's development to production process. Thanks to the tireless support of her husband, Tony, and two children, Jon Erik and Patrick, Kari has been able to fulfill a long-held goal of writing professionally.
Kevin Viers is the Manager of the Enterprise Technologies group at Trilogy Consulting and a graduate of James Madison University. He has over five years of consulting experience specializing in designing, developing, and implementing enterprise solutions. Kevin cut his programming teeth on PowerBuilder and has since developed a broad development background including SAP, Active Server Pages, and Visual Basic. Kevin lives with his wife, Pam, and his boxer, Alli, in Richmond, Virginia. He can be reached via the Internet at kviers@kscsinc.com.
Laura Jones is a consultant in the Richmond metro area of Virginia. She is employed with Trilogy Consulting, which has been recently recognized as one of the top 500 fastest growing IT consulting firms in the United States. She is currently consulting full time as a SQL Server Database Administrator for a Fortune Five company.
Troy D. Rackley is a consultant with the Enterprise Technologies group of Trilogy Consulting. Troy is a Microsoft Certified Solution Developer (MCSD) who always tries to stay one step ahead of the cutting edge. He currently specialized in n-tiered enterprise Web solutions for your local intranet. He was contributing author of the Web Database Developer's guide with Visual Basic 5. Troy lives with his wife Annette in Richmond, Virginia, he can be reached by email at trackley@erols.com.
Anne Yagerline is a senior engineer in client/server development at IKON Technology Services in McLean, Virginia. She develops Windows- and Web-based client/server applications for firms located in the Washington, DC metropolitan area. Anne is a Microsoft Certified Solution Developer (MCSD), with over three years experience with Microsoft SQL Server in particular. Anne can be reached via email at yagerlin@erols.com.
Michael Yocca is a SQL Server and ERwin consultant in the Pittsburgh area, specializing in quality database design. He is Microsoft certified with SQL 6.5 and is a regular contributor to the Pinnacle SQL Professional magazine.
Orryn Sledge is a Practice Director with Fulltilt Solutions in Pittsburgh, Pennsylvania. He specializes in managing and developing large-scale eBusiness solutions. His background includes the Microsoft Internet platform, Oracle, and other popular eBusiness products. He has been actively involved with SQL Server consulting since 1992 and is a Microsoft Certified Solution Developer (MCSD). Orryn is also a frequent speaker at various Microsoft conferences and presentations. He can be reached at osledge@fulltilt.com.
Kari A. Fernandez is an Enterprise Technologies consultant for Trilogy Consulting, in Richmond, Virginia. In 1994 she graduated from Virginia Commonwealth University's School of Business with a bachelor's degree in Information Systems. Since graduation she has specialized in developing database-driven applications for the Web. Kari employs the use of Microsoft's Windows NT Server, Internet Information Server, and SQL Server, as well as Visual Studio software, as the Web development foundation. Kari has succeeded in rapidly developing small Internet and intranet Web applications and has participated in team development of larger-scale Web applications. She assists clients in conceptualizing and analyzing their strategies for Web site architecture and database design. Her expertise in Web site development combines this and the use of Active Server Pages, the ActiveX Data Object model, and SQL Server along with other Web technologies such as VBScript, JavaScript, HTML, and Dynamic HTML. She has also worked with clients in setting up Web development and production site security and version control as well as determining the client's development to production process. Thanks to the tireless support of her husband, Tony, and two children, Jon Erik and Patrick, Kari has been able to fulfill a long-held goal of writing professionally.
Kevin Viers is the Manager of the Enterprise Technologies group at Trilogy Consulting and a graduate of James Madison University. He has over five years of consulting experience specializing in designing, developing, and implementing enterprise solutions. Kevin cut his programming teeth on PowerBuilder and has since developed a broad development background including SAP, Active Server Pages, and Visual Basic. Kevin lives with his wife, Pam, and his boxer, Alli, in Richmond, Virginia. He can be reached via the Internet at kviers@kscsinc.com.
Laura Jones is a consultant in the Richmond metro area of Virginia. She is employed with Trilogy Consulting, which has been recently recognized as one of the top 500 fastest growing IT consulting firms in the United States. She is currently consulting full time as a SQL Server Database Administrator for a Fortune Five company.
Troy D. Rackley is a consultant with the Enterprise Technologies group of Trilogy Consulting. Troy is a Microsoft Certified Solution Developer (MCSD) who always tries to stay one step ahead of the cutting edge. He currently specialized in n-tiered enterprise Web solutions for your local intranet. He was contributing author of the Web Database Developer's guide with Visual Basic 5. Troy lives with his wife Annette in Richmond, Virginia, he can be reached by email at trackley@erols.com.
Anne Yagerline is a senior engineer in client/server development at IKON Technology Services in McLean, Virginia. She develops Windows- and Web-based client/server applications for firms located in the Washington, DC metropolitan area. Anne is a Microsoft Certified Solution Developer (MCSD), with over three years experience with Microsoft SQL Server in particular. Anne can be reached via email at yagerlin@erols.com.
Michael Yocca is a SQL Server and ERwin consultant in the Pittsburgh area, specializing in quality database design. He is Microsoft certified with SQL 6.5 and is a regular contributor to the Pinnacle SQL Professional magazine.
Content
(NOTE: Each chapter concludes with a Summary.)
Introduction.
I. OVERVIEW.
1. Role of the Database Administrator.
Hardware. Network. Operating Systems. File/Print Server. Database Server. Who Does What?
PC and Tech Support. Network Administrator. System Administrator. Webmaster. Database Administrator.
What Is a Database Administrator? Who Are the DBAs? DBA Responsibilities.
Installing and Upgrading an SQL Server. Monitoring the Database Server's Health and Tuning Accordingly. Using Storage Properly. Performing Backup and Recovery Duties. Managing Database Users and Security. Working with Developers. Establishing and Enforcing Standards. Transferring Data. Replicating Data. Data Warehousing. Scheduling Events. Providing 24-Hour Access. Learning Constantly. Does It Pay to Be a DBA?
Tricks of the Trade.
Classes and Training. On the Job. Microsoft TechNet, Microsoft Developers Network, and Internet News Group. Magazines and Books. Certification. Internet.
How the DBA Interacts with Other Team Members.
System Administrator and Network Administrator. Developers. Users.
2. SQL Server Overview.
Architecture.
Symmetric Multiprocessing (SMP). Operating System Support. Network Independence. Reliability.
Operating System Integration.
Taskbar Integration. Control Panel. Event Viewer. The Registry. NT User Accounts. Performance Monitor.
Visual Administration Tools.
SQL Server Service Manager. SQL Server Enterprise Manager. SQL Server Query Analyzer. SQL Server Setup. SQL Server Network Utility. SQL Server Profiler. Version Upgrade Wizard.
SQL Server Companion Products.
Microsoft English Query. Analysis Services.
Nonvisual Administration Tools and Command-Line Tools.
BCP. ISQL OSQL. TEXTCOPY.
Common SQL Server Objects.
Tables. Rules. Defaults. User-Defined Data Types. Views. Triggers. Stored Procedures.
FAQ.
3. The Evolution of SQL Server.
History of SQL Server. What's New in Version 2000.
XML Support. Multi-Instance Support. Data Warehousing and Business Intelligence (BI) Improvements. Windows 2000 Support. Performance and Scalability Improvements. Wizard Improvements. Query Analyzer Improvements. DTS Enhancement. Transact-SQL Enhancements.
II. INSTALLING AND UPGRADING SQL SERVER.
4. Planning an Installation or Upgrade.
Developing an Installation Strategy and Plan.
Step 1: Determine System and User Requirements. Step 2: Select the Right Platform. Step 3: Answer Required Questions and Understand Why They Are Important. Step 4: Install SQL Server.
Developing an Upgrade Strategy and Plan. Upgrade/Installation Planning FAQ.
5. Installing or Upgrading SQL Server.
Different Editions of SQL Server.
Installing SQL Server.
SQL Server Installation Checklist. Step 1: Running Setup. Step 2: Select an Installation Option. Step 3: Select Computer Install Method. Step 4: Installation Type. Step 5: User Information. Step 6: License Agreement. Step 7: Installation Type. Step 8: Instance Name. Step 9: Setup Type and File Location. Step 10: Select Components to Install. Step 11: Authentication Mode. Step 12: Collation Setting. Step 13: Network Libraries. Step 13a: SQL Server, SQL Agent, MSDTC User Accounts, and Auto Start Services-Windows 2000 and NT Only. Step 14: Start Copying Files.
Starting and Stopping SQL Server. Installation Troubleshooting.
Error Log and Windows 2000/NT Application Log. Start SQL Server from the Command Line.
Upgrading SQL Server.
Upgrading SQL Server 7.0 to SQL Server 2000. Upgrading SQL Server 6.5.
Removing SQL Server. Installing Client Tools. Configuring Clients. Installation FAQ.
III. SQL SERVER MANAGEMENT AND CONFIGURATION.
6. Enterprise Management Processes.
Starting, Pausing, and Stopping SQL Server. Starting the SQL Server Enterprise Manager. Navigating the SQL Server Enterprise Manager. Registering a Server. Connecting to a Server. Disconnecting from a Server. Starting, Stopping, and Configuring SQL Server Agent. Starting, Stopping, and Configuring SQL Mail. Using the Enterprise Manager to Perform Common Tasks.
Manage Server Configurations. Manage Logins. Manage Server Roles. Manage Databases. Manage Database Users and Objects. Generate SQL Scripts. Manage Jobs. Manage Alerts. Manage Operators. Monitor User Activity. Manage Data Transformation Packages. Manage Error Logs.
SQL Server Query Analyzer. Starting and Stopping the Distributed Transaction Coordinator (DTC). FAQ.
7. Configuring and Tuning SQL Server.
Configuring SQL Server. SQL Server 2000 Self-Tuning Features.
Memory. Asynchronous Read Ahead.
Configuration Parameters.
allow updates. default language. max text repl size. nested triggers. remote access. remote login timeout. remote query timeout. remote proc trans. show advanced options. two digit year cutoff. user_option.
Advanced Configuration Parameters.
affinity mask. awe enabled. C2 Audit Mode. cost threshold for parallelism. cursor threshold. default full-text language. fill factor. index create memory. lightweight pooling. locks. max degree of parallelism. max server memory. max worker threads. media retention. min memory per query. min server memory. network packet size. open objects. priority boost. query governor cost limit. query wait. recovery interval. scan for startup procs. set working set size. user connections.
Configuration FAQ.
8. Managing Databases.
A Database Primer.
What Is a Database? What Is the Transaction Log? How Databases and Operating System Datafiles Interact.
Database Basics.
Creating a Database. Viewing Information About a Database. Setting Database Options. Expanding the Database and Log Size. Shrinking a Database and Log. Renaming a Database. Deleting a Database. Moving Database Files.
Additional Database Information.
Tip 1: Document the Database. Tip 2: Take Advantage of the model Database.
Filegroups.
Implementing a Filegroup During Database Creation. Implementing a Filegroup for an Existing Database. Adding Secondary Data Files to the Filegroup. Place an Object on the Filegroup. Creating an Index on a Filegroup. Viewing Information About a Filegroup.
Database FAQ.
9. Managing SQL Server Users and Security.
Introduction. An Overview of SQL Server's Security Model.
SQL Server Login. Database User. guest User. Permissions. Roles.
Managing Logins. Managing Server Roles. Managing Database Access and Database Roles. Viewing and Modifying Login Information. Removing Logins. Changing Password. Managing SQL Server Security.
Levels of Security. Security Hierarchy. Granting and Revoking Object Permissions. Granting and Revoking Statement Permissions.
Beyond Security Basics: Suggested Strategies.
Role-Based Security Management. Views for Data Security. Stored Procedures for Data Security. Triggers for Audit Trails.
Managing SQL Server Users and Security FAQs.
IV. DATABASE BACKUP AND RECOVERY.
10. Backup and Recovery.
SQL Server 2000 Backups-Simplified with Recovery Models (But Few Changes Under the Covers).
Recovery Models. Simple Recovery. Full Recovery. Bulk-Logged Recovery. What Is a Database Backup? What Is a Differential Backup? What Is a Transaction Log Backup?
What Is a File/Filegroup Backup?
Selecting the Right Recovery Model for Your Database.
Creating a Backup Device. Performing Database, Transaction Log, Differential and File/File Group Backups.
The Steps to Perform a Backup.
Understanding Log Truncation Options.
TRUNCATE_ONLY. NO_LOG NO_TRUNCATE.
Backup Wizard. Using Multiple Backup Devices (Striped Backups) and Media Sets. Full Recovery Model and Bulk Logged Recovery Model-Using Database. Complete Backups and Transaction Log Backups to Restore a Database.
Day 1: Full Database Backup Occurs. Day 2: Database Modified, Database Corrupted. Using the Backups to Restore the Database. Problem Resolution. Using the Backups to Restore the Database. Restore Example-Reality Check. Using Differential Backups to Speedup the Restore Time.
Performing a Database Restore. Restoring the master Database.
Reattaching Database Files.
Interactive Example of Losing and Restoring a Database.
Step 1: Create a Database. Step 2: Create a Table. Step 3: Create a Backup Device. Step 4: Add Rows to the Table. Step 5: Back Up the TestRestore Database. Step 6: Add More Rows to the Table. Step 7: Back Up the Transaction Log. Step 8: Add More Rows to the Table. Checkpoint: Backup Part of Exercise Completed. Step 9: Shut Down SQL Server. Step 10: Delete the Database Data File. Step 11: Restart SQL Server. Step 12: Restore the Database TestRestore.
Creating a Backup Schedule.
Category 1: Actions that Warrant Dumping a Database. Category 2: Scheduled Database Backups.
Backup and Restore FAQ.
V. SQL DATABASE MAINTENANCE.
11. Developing a SQL Server Maintenance Plan.
Areas of Maintenance.
SQL Server Maintenance. Database Maintenance. Table/Object Maintenance. Job Maintenance. Windows NT Maintenance.
Maintenance Checklist. Maintenance FAQ.
12. Automating Database Administration Tasks.
Introduction. SQL Server Agent.
Jobs. Alerts.
Database Maintenance Plan Wizard. Automating Database Administration Tasks FAQ.
VI. IMPORTING AND EXPORTING DATA.
13. Data Transformation Services.
DTS and the Data Warehouse. DTS and OLE DB/ODBC. The DTS Framework.
Packages. Connections. Tasks. Steps. The DTS Data Pump.
Using the DTS Wizard.
A DTS Wizard Example.
Using the DTS Designer.
Creating a DTS Package and Adding Simple Workflow.
What Is Workflow and Batch Processing?
How DTS Provides Workflow and Batch Processing Capabilities. DTS Package Properties. Individual Task Workflow Properties. Using the Workflow ActiveX Script Property for Loop Control and Conditions.
Using DTS Packages.
Running Packages from the Command Line.
The Data Transformation Services FAQ.
14. Using BCP and BULK INSERT.
BCP. BCP Syntax. Permissions Required to Run BCP. Character Mode Versus Native Mode. Interactive BCP.
File Storage Type. Prefix Length. Field Length. Field Terminator. Format Files.
Sample BCP Scripts.
Simple Import. Simple Export. Comma-Delimited Import. Comma-Delimited Export. Fixed-Length Import. Fixed-Length Export. Skipped Fields on Import. Skipped Fields on Export.
Modes of Operation.
Achieving Fast Mode BCP. Why You Should Be Concerned with Which BCP Mode Is Running.
BCP and Enforcement of Triggers, Rules, Defaults, Constraints, and Unique Indexes. Common BCP Traps. BCP Tips. BULK INSERT. BCP FAQ.
VII. TROUBLESHOOTING.
15. Troubleshooting SQL Server.
SQL Error Messages.
Error Message Number Error Severity. State Number. Error Message.
Using the Error Message Number to Resolve the Error. Deciphering the Error Log. Using the Event Viewer. Killing a Process. Viewing Detailed Process Activity. Using DBCC and Trace Statements to Troubleshoot.
Table/Index Fragmentation.
Troubleshooting Applications. Other Sources of Help and Information.
Technical Support. Microsoft TechNet and Microsoft Developer Network. The Internet. User Groups.
Troubleshooting FAQ.
VIII. ARCHITECTURE AND DATABASE DESIGN.
16. Architecture Features.
SQL Server Thread Scheduling.
What Is a Thread? What Is a Context Switch? What Is SMP? SQL Server 6.x Thread Scheduling. SQL Server 2000 Thread Scheduling.
Disk I/O and Data Management.
Page Size. File Groups.
Multiple Database Instances. Federated Database Servers. Other Enhancements.
Read-Ahead Logic. Locking Enhancements.
17. Database Design Issues.
Problems That Can Arise from an Improperly Designed Database.
Redundant Data. Limited Data Tracking. Inconsistent Data. Update Anomalies. Delete Anomalies Insert Anomalies.
Normalization.
How to Normalize a Database.
Denormalization.
Performance. Ad Hoc Reporting. Denormalization Techniques.
FAQ.
IX. PERFORMANCE AND TUNING.
18. Understanding Indexes.
General Principle Behind Indexes. Structure of SQL Server Indexes.
Clustered Index. Nonclustered Index.
Data Modification and Index Performance Considerations. How to Create Indexes. Other Index Operations.
Indexes on Views and Computed Columns. Viewing Indexes on Tables in a Database. Renaming, Adding, or Deleting an Index.
Suggested Index Strategies.
What to Index. What Not to Index. Clustered or Nonclustered Index.
Letting SQL Server Help with Index Selection. Index FAQ.
19. Query Optimization.
What's a Query Optimizer? What Are Statistics? Basic Query Optimization Suggestions. Tools to Help Optimize a Query.
Index Tuning Wizard. SQL Server Profiler. Showplan. Statistics I/O. Statistics Time Tool.
Reading Showplans. Overriding the Optimizer.
Index Hints. The SET FORCEPLAN ON Command.
Other Tuning Tricks.
Are You Trying to Tune an UPDATE, DELETE, or INSERT Query? Does the Query Reference a View? Are the Datatypes Mismatched? Does the Query Use a Nonsearch Argument?
Query Optimization FAQ.
20. Multiuser Considerations.
Locks.
Understanding SQL Server's Locking Behavior. Physical Locks. Lock Methods. Viewing Locks and Blocking. Tips to Help Minimize Locking and Prevent Deadlocks.
Multiuser Configuration Options.
Transaction Isolation Level. Explicit Locking.
Multiuser FAQ.
X. TRANSACT-SQL.
21. SQL Essentials.
An Overview of Basic SQL Statements. SELECT.
A Simple SELECT Statement. Adding the WHERE Clause. Adding the ORDER BY Clause. Using the WHERE Clause to Join Tables. Using the Join Operator to Join Tables. Aggregate Functions in SQL Statements. Use of the GROUP BY Clause. Use of the HAVING Clause.
INSERT.
Use of the INSERT Statement with a Value List. Use of the INSERT Statement with a SELECT Statement.
UPDATE.
Setting Columns to a Fixed Value with UPDATE Statement. Setting a Column Value Based on Existing Column Values. Setting a Column Based on Values in a Joined Table.
DELETE.
Using DELETE to Delete All Rows from a Table. Using DELETE to Delete Specific Rows from a Table. Using a Subquery to Delete Rows Based on Values in a Different Table.
CREATE TABLE.
Using CREATE TABLE to Create the authors Table.
SELECT...INTO.
Using SELECT...INTO to Create a New authortitles Table.
Distributed Queries.
Distributed Query Restrictions. Adding a Linked Server. Logon IDs. Retrieving Data from a Linked Server. Examples. Linked Servers Pass Through Queries.
22. Using Stored Procedures and Cursors.
What Is a Stored Procedure? Stored Procedure Pros and Cons. How to Create a Stored Procedure. How to Modify a Stored Procedure. Control-of-Flow Language.
The DECLARE Statement. The GOTO Statement. The BEGIN...END Statement. The IF...ELSE Statement. The WAITFOR Statement. The RETURN Statement. The WHILE, BREAK, and CONTINUE Statements. The PRINT Statement. The RAISERROR Statement. Comments.
Parameters Used with Stored Procedures.
Input Parameters. Output Parameters.
Commonly Used Global Variables. How to Debug a Stored Procedure.
Transact-SQL Debugger. Transact-SQL Debug Statements. Other Debugging Tools.
What Is a Cursor? Creating a Cursor.
Step 1: DECLARE the Cursor. Step 2: OPEN the Cursor. Step 3: FETCH from the Cursor. Step 4: CLOSE or DEALLOCATE the Cursor. Positional UPDATE and DELETE. Global Variables.
Putting It All Together.
Example 1: Loop Through a Table. Example 2: Display Object Names and Object Types.
Stored Procedure and Cursor FAQ.
XI. ADVANCED DBA TOPICS.
23. SQL Server 2000 and the Internet.
SQL Server 2000 Web Publishing.
Using the Web Assistant. Web Assistant Jobs. Using the Web Assistant Wizard to Publish an HTML Page. Using the Web Assistant Wizard to Publish Data to a Web Page.
SQL Server 2000 and Managing Web Assistant Jobs. Advanced Template File Example. XML Integration. Retrieving XML Data.
Using XPath Queries. XPath Queries Against XML-Data Reduced (XDR) Schema.
Updating Information via XML. SQL Server and the Web FAQ.
24. Monitoring SQL Server.
Tools for Monitoring SQL Server.
System Monitor. SQL Server Enterprise Manager. SQL Server Profiler.
Monitoring SQL Server FAQ.
25. SQL Mail.
Setting Up Your SQL Server as a Mail Client. Configuring SQL Mail. Configuring SQL Agent Mail. Using SQL Mail.
Sending Email from SQL Server. Processing Incoming Email.
SQL Mail FAQ
26. Using SQL-DMO.
SQL Server's Object Model. Why Use SQL-DMO? Creating Applications with SQL-DMO.
Using Visual Basic. Required SQL-DMO Files. SQL-DMO Checklist.
Enhancing the SQL Server DBA Assistant.
What's in the SQL Server DBA Assistant? Connecting to SQL Server. Filling a Combo Box with Databases. Performing Table Maintenance. Performing Table Exports Using Bulk Copy (BCP).
Using SQL-DMO with Stored Procedures.
OLE Automation Procedures. Transact SQL Example.
SQL-DMO FAQ.
XII. REPLICATION.
27. Replication.
Replication Overview and Terminology.
Publish and Subscribe. Publication and Articles. Subscriptions Types (Push and Pull). Server Roles. Replication Types. Transactional Consistency. The Problem that SQL Server 2000 Replication Cannot Solve. Distribution Database. An Overview of the SQL Server Replication Agents. Synchronization Modes.
Creating and Assigning the Distribution Database. Configuring Replication Distribution Options. Deleting a Distribution Database. Configuring Replication Publishing.
Adding a Publisher to a Distribution Database. Enabling a Database for Publishing and Removing a Database from. Publishing.
Enabling Replication Subscribers. Using the Disabling Publishing and Distribution Wizard. Adding NonSQL Server (Heterogeneous) Subscribers.
Step 1: Create an ODBC Data Source Name (DSN). Step 2: Register the ODBC Source as a Subscribing Server.
Upgrading SQL Server Replication. Replication FAQ.
28. Transactional Replication.
Applicable Uses for Transactional Replication. Replication Agents. Replication Topology.
Central Publisher. Central Publisher with Remote Distributor. Republisher. Central Subscriber.
Immediate Updating Subscribers.
Triggers. Stored Procedures. Microsoft Distributed Transaction Coordinator. Conflict Detection. Loopback Detection.
Recommended Topology for Updating Subscribers. Creating a Transaction Based Publication. Subscriptions. Custom Stored Procedures. Transforming Published Data. Inline Data Validation and Reinitialization. Generating Publication Scripts. Replicating Stored Procedures. Replication Monitor. Transaction Replication FAQ.
29. Snapshot and Merge Replication.
What Is Snapshot Replication?
Snapshot Applications. Snapshot Replication: Step by Step. Planning and Special Design Considerations for Snapshot Replication.
Setting Up a Snapshot Publication.
Name Conflicts. Copy Objects to Destination.
What Is Merge Replication?
Merge Applications. Merge Replication: Step by Step. Merge Replication and Transactional Consistency. Planning and Special Design Considerations for Merge Replication.
Setting Up a Merge Publication. Merge Replication and Resolving Conflicts-Hands-on Example.
Step 1-Create a Database. Step 2-Create a Merge Publication Called test_authors. Step 3-Push the Publication Step 4-Make Changes to the Information in One of the Articles. Step 5-Replicate the Changes. Step 6-Deal with Conflicts.
Troubleshooting Merge and Snapshot Replication. Additional Publication Options.
General Tab. Status Tab. Snapshot Tab.
Alternative Synchronization Partners. Replicating via the Web (Internet). Replication FAQs.
XIII. DATA WAREHOUSING.
30. Introduction to Data Warehousing.
Why Warehouse? What Is a Data Warehouse?
Decision Support Systems (DSS). Online Analytical Processing (OLAP).
Warehouse Data Versus Operational Data. Data Warehousing Components. What Is a Data Mart?
Warehouses Versus Marts.
Transforming Operational Data.
Data Integration or Consolidation. Ensuring Data Quality. Data Mapping and Matching. Summarization. Extracting, Loading, and Refreshing Data. Metadata.
Planning the Warehouse Design.
Top Down or Bottom Up? Dimensional Modeling (Stars and Snowflakes).
Important Design Considerations. Managing a Data Warehouse or Data Mart. Microsoft and SQL Server 2000 Contributions to Data Warehousing.
Data Transformation Services. Repository. Analysis Services
Data Warehousing FAQ.
31. SQL 2000 Analysis Services.
What Is OLAP? Understanding Multidimensional Data. The Microsoft Analysis Manager. Building an OLAP Database.
Creating the Data Source. Defining the Dimensions. Building the Cube.
Data Storage in an OLAP Database.
MOLAP. ROLAP. HOLAP.
Optimizing an OLAP Database.
Aggregations. Partitions.
Managing Multidimensional Data.
Processing the Cube. Merging Partitions. Client Write Back. Securing Your OLAP Data.
Microsoft SQL Server 2000 Analysis Services FAQ.
XIV. APPENDIXES.
Appendix A. Naming Conventions.
Appendix B. DBCC Commands.
Quick Reference. Reading the Output from DBCC Commands. Resolving Errors Reported by DBCC. Essential DBCC Commands. DBCC Commands for Verification.
CHECKALLOC. CHECKCATALOG. CHECKDB. CHECKFILEGROUP. CHECKIDENT. CHECKTABLE. DBREINDEX. SHOWCONTIG. UPDATEUSAGE.
DBCC Commands to Return Process Information. DBCC Commands to Return Performance Monitor Statistics. Trace Flag Commands. Data Cache Commands. Transaction Commands. Other DBCC Commands.
SHOW_STATISTICS. SHRINKDATABASE. SHRINKFILE. USEROPTIONS. DBCC dllname (FREE).
Appendix C. SQL Server Resources.
Appendix D. What's on the CD-ROM.
The SQL Server DBA Assistant.
Installing SQL Server DBA Assistant. Using SQL Server DBA Assistant.
Index.
Introduction.
I. OVERVIEW.
1. Role of the Database Administrator.
Hardware. Network. Operating Systems. File/Print Server. Database Server. Who Does What?
PC and Tech Support. Network Administrator. System Administrator. Webmaster. Database Administrator.
What Is a Database Administrator? Who Are the DBAs? DBA Responsibilities.
Installing and Upgrading an SQL Server. Monitoring the Database Server's Health and Tuning Accordingly. Using Storage Properly. Performing Backup and Recovery Duties. Managing Database Users and Security. Working with Developers. Establishing and Enforcing Standards. Transferring Data. Replicating Data. Data Warehousing. Scheduling Events. Providing 24-Hour Access. Learning Constantly. Does It Pay to Be a DBA?
Tricks of the Trade.
Classes and Training. On the Job. Microsoft TechNet, Microsoft Developers Network, and Internet News Group. Magazines and Books. Certification. Internet.
How the DBA Interacts with Other Team Members.
System Administrator and Network Administrator. Developers. Users.
2. SQL Server Overview.
Architecture.
Symmetric Multiprocessing (SMP). Operating System Support. Network Independence. Reliability.
Operating System Integration.
Taskbar Integration. Control Panel. Event Viewer. The Registry. NT User Accounts. Performance Monitor.
Visual Administration Tools.
SQL Server Service Manager. SQL Server Enterprise Manager. SQL Server Query Analyzer. SQL Server Setup. SQL Server Network Utility. SQL Server Profiler. Version Upgrade Wizard.
SQL Server Companion Products.
Microsoft English Query. Analysis Services.
Nonvisual Administration Tools and Command-Line Tools.
BCP. ISQL OSQL. TEXTCOPY.
Common SQL Server Objects.
Tables. Rules. Defaults. User-Defined Data Types. Views. Triggers. Stored Procedures.
FAQ.
3. The Evolution of SQL Server.
History of SQL Server. What's New in Version 2000.
XML Support. Multi-Instance Support. Data Warehousing and Business Intelligence (BI) Improvements. Windows 2000 Support. Performance and Scalability Improvements. Wizard Improvements. Query Analyzer Improvements. DTS Enhancement. Transact-SQL Enhancements.
II. INSTALLING AND UPGRADING SQL SERVER.
4. Planning an Installation or Upgrade.
Developing an Installation Strategy and Plan.
Step 1: Determine System and User Requirements. Step 2: Select the Right Platform. Step 3: Answer Required Questions and Understand Why They Are Important. Step 4: Install SQL Server.
Developing an Upgrade Strategy and Plan. Upgrade/Installation Planning FAQ.
5. Installing or Upgrading SQL Server.
Different Editions of SQL Server.
Installing SQL Server.
SQL Server Installation Checklist. Step 1: Running Setup. Step 2: Select an Installation Option. Step 3: Select Computer Install Method. Step 4: Installation Type. Step 5: User Information. Step 6: License Agreement. Step 7: Installation Type. Step 8: Instance Name. Step 9: Setup Type and File Location. Step 10: Select Components to Install. Step 11: Authentication Mode. Step 12: Collation Setting. Step 13: Network Libraries. Step 13a: SQL Server, SQL Agent, MSDTC User Accounts, and Auto Start Services-Windows 2000 and NT Only. Step 14: Start Copying Files.
Starting and Stopping SQL Server. Installation Troubleshooting.
Error Log and Windows 2000/NT Application Log. Start SQL Server from the Command Line.
Upgrading SQL Server.
Upgrading SQL Server 7.0 to SQL Server 2000. Upgrading SQL Server 6.5.
Removing SQL Server. Installing Client Tools. Configuring Clients. Installation FAQ.
III. SQL SERVER MANAGEMENT AND CONFIGURATION.
6. Enterprise Management Processes.
Starting, Pausing, and Stopping SQL Server. Starting the SQL Server Enterprise Manager. Navigating the SQL Server Enterprise Manager. Registering a Server. Connecting to a Server. Disconnecting from a Server. Starting, Stopping, and Configuring SQL Server Agent. Starting, Stopping, and Configuring SQL Mail. Using the Enterprise Manager to Perform Common Tasks.
Manage Server Configurations. Manage Logins. Manage Server Roles. Manage Databases. Manage Database Users and Objects. Generate SQL Scripts. Manage Jobs. Manage Alerts. Manage Operators. Monitor User Activity. Manage Data Transformation Packages. Manage Error Logs.
SQL Server Query Analyzer. Starting and Stopping the Distributed Transaction Coordinator (DTC). FAQ.
7. Configuring and Tuning SQL Server.
Configuring SQL Server. SQL Server 2000 Self-Tuning Features.
Memory. Asynchronous Read Ahead.
Configuration Parameters.
allow updates. default language. max text repl size. nested triggers. remote access. remote login timeout. remote query timeout. remote proc trans. show advanced options. two digit year cutoff. user_option.
Advanced Configuration Parameters.
affinity mask. awe enabled. C2 Audit Mode. cost threshold for parallelism. cursor threshold. default full-text language. fill factor. index create memory. lightweight pooling. locks. max degree of parallelism. max server memory. max worker threads. media retention. min memory per query. min server memory. network packet size. open objects. priority boost. query governor cost limit. query wait. recovery interval. scan for startup procs. set working set size. user connections.
Configuration FAQ.
8. Managing Databases.
A Database Primer.
What Is a Database? What Is the Transaction Log? How Databases and Operating System Datafiles Interact.
Database Basics.
Creating a Database. Viewing Information About a Database. Setting Database Options. Expanding the Database and Log Size. Shrinking a Database and Log. Renaming a Database. Deleting a Database. Moving Database Files.
Additional Database Information.
Tip 1: Document the Database. Tip 2: Take Advantage of the model Database.
Filegroups.
Implementing a Filegroup During Database Creation. Implementing a Filegroup for an Existing Database. Adding Secondary Data Files to the Filegroup. Place an Object on the Filegroup. Creating an Index on a Filegroup. Viewing Information About a Filegroup.
Database FAQ.
9. Managing SQL Server Users and Security.
Introduction. An Overview of SQL Server's Security Model.
SQL Server Login. Database User. guest User. Permissions. Roles.
Managing Logins. Managing Server Roles. Managing Database Access and Database Roles. Viewing and Modifying Login Information. Removing Logins. Changing Password. Managing SQL Server Security.
Levels of Security. Security Hierarchy. Granting and Revoking Object Permissions. Granting and Revoking Statement Permissions.
Beyond Security Basics: Suggested Strategies.
Role-Based Security Management. Views for Data Security. Stored Procedures for Data Security. Triggers for Audit Trails.
Managing SQL Server Users and Security FAQs.
IV. DATABASE BACKUP AND RECOVERY.
10. Backup and Recovery.
SQL Server 2000 Backups-Simplified with Recovery Models (But Few Changes Under the Covers).
Recovery Models. Simple Recovery. Full Recovery. Bulk-Logged Recovery. What Is a Database Backup? What Is a Differential Backup? What Is a Transaction Log Backup?
What Is a File/Filegroup Backup?
Selecting the Right Recovery Model for Your Database.
Creating a Backup Device. Performing Database, Transaction Log, Differential and File/File Group Backups.
The Steps to Perform a Backup.
Understanding Log Truncation Options.
TRUNCATE_ONLY. NO_LOG NO_TRUNCATE.
Backup Wizard. Using Multiple Backup Devices (Striped Backups) and Media Sets. Full Recovery Model and Bulk Logged Recovery Model-Using Database. Complete Backups and Transaction Log Backups to Restore a Database.
Day 1: Full Database Backup Occurs. Day 2: Database Modified, Database Corrupted. Using the Backups to Restore the Database. Problem Resolution. Using the Backups to Restore the Database. Restore Example-Reality Check. Using Differential Backups to Speedup the Restore Time.
Performing a Database Restore. Restoring the master Database.
Reattaching Database Files.
Interactive Example of Losing and Restoring a Database.
Step 1: Create a Database. Step 2: Create a Table. Step 3: Create a Backup Device. Step 4: Add Rows to the Table. Step 5: Back Up the TestRestore Database. Step 6: Add More Rows to the Table. Step 7: Back Up the Transaction Log. Step 8: Add More Rows to the Table. Checkpoint: Backup Part of Exercise Completed. Step 9: Shut Down SQL Server. Step 10: Delete the Database Data File. Step 11: Restart SQL Server. Step 12: Restore the Database TestRestore.
Creating a Backup Schedule.
Category 1: Actions that Warrant Dumping a Database. Category 2: Scheduled Database Backups.
Backup and Restore FAQ.
V. SQL DATABASE MAINTENANCE.
11. Developing a SQL Server Maintenance Plan.
Areas of Maintenance.
SQL Server Maintenance. Database Maintenance. Table/Object Maintenance. Job Maintenance. Windows NT Maintenance.
Maintenance Checklist. Maintenance FAQ.
12. Automating Database Administration Tasks.
Introduction. SQL Server Agent.
Jobs. Alerts.
Database Maintenance Plan Wizard. Automating Database Administration Tasks FAQ.
VI. IMPORTING AND EXPORTING DATA.
13. Data Transformation Services.
DTS and the Data Warehouse. DTS and OLE DB/ODBC. The DTS Framework.
Packages. Connections. Tasks. Steps. The DTS Data Pump.
Using the DTS Wizard.
A DTS Wizard Example.
Using the DTS Designer.
Creating a DTS Package and Adding Simple Workflow.
What Is Workflow and Batch Processing?
How DTS Provides Workflow and Batch Processing Capabilities. DTS Package Properties. Individual Task Workflow Properties. Using the Workflow ActiveX Script Property for Loop Control and Conditions.
Using DTS Packages.
Running Packages from the Command Line.
The Data Transformation Services FAQ.
14. Using BCP and BULK INSERT.
BCP. BCP Syntax. Permissions Required to Run BCP. Character Mode Versus Native Mode. Interactive BCP.
File Storage Type. Prefix Length. Field Length. Field Terminator. Format Files.
Sample BCP Scripts.
Simple Import. Simple Export. Comma-Delimited Import. Comma-Delimited Export. Fixed-Length Import. Fixed-Length Export. Skipped Fields on Import. Skipped Fields on Export.
Modes of Operation.
Achieving Fast Mode BCP. Why You Should Be Concerned with Which BCP Mode Is Running.
BCP and Enforcement of Triggers, Rules, Defaults, Constraints, and Unique Indexes. Common BCP Traps. BCP Tips. BULK INSERT. BCP FAQ.
VII. TROUBLESHOOTING.
15. Troubleshooting SQL Server.
SQL Error Messages.
Error Message Number Error Severity. State Number. Error Message.
Using the Error Message Number to Resolve the Error. Deciphering the Error Log. Using the Event Viewer. Killing a Process. Viewing Detailed Process Activity. Using DBCC and Trace Statements to Troubleshoot.
Table/Index Fragmentation.
Troubleshooting Applications. Other Sources of Help and Information.
Technical Support. Microsoft TechNet and Microsoft Developer Network. The Internet. User Groups.
Troubleshooting FAQ.
VIII. ARCHITECTURE AND DATABASE DESIGN.
16. Architecture Features.
SQL Server Thread Scheduling.
What Is a Thread? What Is a Context Switch? What Is SMP? SQL Server 6.x Thread Scheduling. SQL Server 2000 Thread Scheduling.
Disk I/O and Data Management.
Page Size. File Groups.
Multiple Database Instances. Federated Database Servers. Other Enhancements.
Read-Ahead Logic. Locking Enhancements.
17. Database Design Issues.
Problems That Can Arise from an Improperly Designed Database.
Redundant Data. Limited Data Tracking. Inconsistent Data. Update Anomalies. Delete Anomalies Insert Anomalies.
Normalization.
How to Normalize a Database.
Denormalization.
Performance. Ad Hoc Reporting. Denormalization Techniques.
FAQ.
IX. PERFORMANCE AND TUNING.
18. Understanding Indexes.
General Principle Behind Indexes. Structure of SQL Server Indexes.
Clustered Index. Nonclustered Index.
Data Modification and Index Performance Considerations. How to Create Indexes. Other Index Operations.
Indexes on Views and Computed Columns. Viewing Indexes on Tables in a Database. Renaming, Adding, or Deleting an Index.
Suggested Index Strategies.
What to Index. What Not to Index. Clustered or Nonclustered Index.
Letting SQL Server Help with Index Selection. Index FAQ.
19. Query Optimization.
What's a Query Optimizer? What Are Statistics? Basic Query Optimization Suggestions. Tools to Help Optimize a Query.
Index Tuning Wizard. SQL Server Profiler. Showplan. Statistics I/O. Statistics Time Tool.
Reading Showplans. Overriding the Optimizer.
Index Hints. The SET FORCEPLAN ON Command.
Other Tuning Tricks.
Are You Trying to Tune an UPDATE, DELETE, or INSERT Query? Does the Query Reference a View? Are the Datatypes Mismatched? Does the Query Use a Nonsearch Argument?
Query Optimization FAQ.
20. Multiuser Considerations.
Locks.
Understanding SQL Server's Locking Behavior. Physical Locks. Lock Methods. Viewing Locks and Blocking. Tips to Help Minimize Locking and Prevent Deadlocks.
Multiuser Configuration Options.
Transaction Isolation Level. Explicit Locking.
Multiuser FAQ.
X. TRANSACT-SQL.
21. SQL Essentials.
An Overview of Basic SQL Statements. SELECT.
A Simple SELECT Statement. Adding the WHERE Clause. Adding the ORDER BY Clause. Using the WHERE Clause to Join Tables. Using the Join Operator to Join Tables. Aggregate Functions in SQL Statements. Use of the GROUP BY Clause. Use of the HAVING Clause.
INSERT.
Use of the INSERT Statement with a Value List. Use of the INSERT Statement with a SELECT Statement.
UPDATE.
Setting Columns to a Fixed Value with UPDATE Statement. Setting a Column Value Based on Existing Column Values. Setting a Column Based on Values in a Joined Table.
DELETE.
Using DELETE to Delete All Rows from a Table. Using DELETE to Delete Specific Rows from a Table. Using a Subquery to Delete Rows Based on Values in a Different Table.
CREATE TABLE.
Using CREATE TABLE to Create the authors Table.
SELECT...INTO.
Using SELECT...INTO to Create a New authortitles Table.
Distributed Queries.
Distributed Query Restrictions. Adding a Linked Server. Logon IDs. Retrieving Data from a Linked Server. Examples. Linked Servers Pass Through Queries.
22. Using Stored Procedures and Cursors.
What Is a Stored Procedure? Stored Procedure Pros and Cons. How to Create a Stored Procedure. How to Modify a Stored Procedure. Control-of-Flow Language.
The DECLARE Statement. The GOTO Statement. The BEGIN...END Statement. The IF...ELSE Statement. The WAITFOR Statement. The RETURN Statement. The WHILE, BREAK, and CONTINUE Statements. The PRINT Statement. The RAISERROR Statement. Comments.
Parameters Used with Stored Procedures.
Input Parameters. Output Parameters.
Commonly Used Global Variables. How to Debug a Stored Procedure.
Transact-SQL Debugger. Transact-SQL Debug Statements. Other Debugging Tools.
What Is a Cursor? Creating a Cursor.
Step 1: DECLARE the Cursor. Step 2: OPEN the Cursor. Step 3: FETCH from the Cursor. Step 4: CLOSE or DEALLOCATE the Cursor. Positional UPDATE and DELETE. Global Variables.
Putting It All Together.
Example 1: Loop Through a Table. Example 2: Display Object Names and Object Types.
Stored Procedure and Cursor FAQ.
XI. ADVANCED DBA TOPICS.
23. SQL Server 2000 and the Internet.
SQL Server 2000 Web Publishing.
Using the Web Assistant. Web Assistant Jobs. Using the Web Assistant Wizard to Publish an HTML Page. Using the Web Assistant Wizard to Publish Data to a Web Page.
SQL Server 2000 and Managing Web Assistant Jobs. Advanced Template File Example. XML Integration. Retrieving XML Data.
Using XPath Queries. XPath Queries Against XML-Data Reduced (XDR) Schema.
Updating Information via XML. SQL Server and the Web FAQ.
24. Monitoring SQL Server.
Tools for Monitoring SQL Server.
System Monitor. SQL Server Enterprise Manager. SQL Server Profiler.
Monitoring SQL Server FAQ.
25. SQL Mail.
Setting Up Your SQL Server as a Mail Client. Configuring SQL Mail. Configuring SQL Agent Mail. Using SQL Mail.
Sending Email from SQL Server. Processing Incoming Email.
SQL Mail FAQ
26. Using SQL-DMO.
SQL Server's Object Model. Why Use SQL-DMO? Creating Applications with SQL-DMO.
Using Visual Basic. Required SQL-DMO Files. SQL-DMO Checklist.
Enhancing the SQL Server DBA Assistant.
What's in the SQL Server DBA Assistant? Connecting to SQL Server. Filling a Combo Box with Databases. Performing Table Maintenance. Performing Table Exports Using Bulk Copy (BCP).
Using SQL-DMO with Stored Procedures.
OLE Automation Procedures. Transact SQL Example.
SQL-DMO FAQ.
XII. REPLICATION.
27. Replication.
Replication Overview and Terminology.
Publish and Subscribe. Publication and Articles. Subscriptions Types (Push and Pull). Server Roles. Replication Types. Transactional Consistency. The Problem that SQL Server 2000 Replication Cannot Solve. Distribution Database. An Overview of the SQL Server Replication Agents. Synchronization Modes.
Creating and Assigning the Distribution Database. Configuring Replication Distribution Options. Deleting a Distribution Database. Configuring Replication Publishing.
Adding a Publisher to a Distribution Database. Enabling a Database for Publishing and Removing a Database from. Publishing.
Enabling Replication Subscribers. Using the Disabling Publishing and Distribution Wizard. Adding NonSQL Server (Heterogeneous) Subscribers.
Step 1: Create an ODBC Data Source Name (DSN). Step 2: Register the ODBC Source as a Subscribing Server.
Upgrading SQL Server Replication. Replication FAQ.
28. Transactional Replication.
Applicable Uses for Transactional Replication. Replication Agents. Replication Topology.
Central Publisher. Central Publisher with Remote Distributor. Republisher. Central Subscriber.
Immediate Updating Subscribers.
Triggers. Stored Procedures. Microsoft Distributed Transaction Coordinator. Conflict Detection. Loopback Detection.
Recommended Topology for Updating Subscribers. Creating a Transaction Based Publication. Subscriptions. Custom Stored Procedures. Transforming Published Data. Inline Data Validation and Reinitialization. Generating Publication Scripts. Replicating Stored Procedures. Replication Monitor. Transaction Replication FAQ.
29. Snapshot and Merge Replication.
What Is Snapshot Replication?
Snapshot Applications. Snapshot Replication: Step by Step. Planning and Special Design Considerations for Snapshot Replication.
Setting Up a Snapshot Publication.
Name Conflicts. Copy Objects to Destination.
What Is Merge Replication?
Merge Applications. Merge Replication: Step by Step. Merge Replication and Transactional Consistency. Planning and Special Design Considerations for Merge Replication.
Setting Up a Merge Publication. Merge Replication and Resolving Conflicts-Hands-on Example.
Step 1-Create a Database. Step 2-Create a Merge Publication Called test_authors. Step 3-Push the Publication Step 4-Make Changes to the Information in One of the Articles. Step 5-Replicate the Changes. Step 6-Deal with Conflicts.
Troubleshooting Merge and Snapshot Replication. Additional Publication Options.
General Tab. Status Tab. Snapshot Tab.
Alternative Synchronization Partners. Replicating via the Web (Internet). Replication FAQs.
XIII. DATA WAREHOUSING.
30. Introduction to Data Warehousing.
Why Warehouse? What Is a Data Warehouse?
Decision Support Systems (DSS). Online Analytical Processing (OLAP).
Warehouse Data Versus Operational Data. Data Warehousing Components. What Is a Data Mart?
Warehouses Versus Marts.
Transforming Operational Data.
Data Integration or Consolidation. Ensuring Data Quality. Data Mapping and Matching. Summarization. Extracting, Loading, and Refreshing Data. Metadata.
Planning the Warehouse Design.
Top Down or Bottom Up? Dimensional Modeling (Stars and Snowflakes).
Important Design Considerations. Managing a Data Warehouse or Data Mart. Microsoft and SQL Server 2000 Contributions to Data Warehousing.
Data Transformation Services. Repository. Analysis Services
Data Warehousing FAQ.
31. SQL 2000 Analysis Services.
What Is OLAP? Understanding Multidimensional Data. The Microsoft Analysis Manager. Building an OLAP Database.
Creating the Data Source. Defining the Dimensions. Building the Cube.
Data Storage in an OLAP Database.
MOLAP. ROLAP. HOLAP.
Optimizing an OLAP Database.
Aggregations. Partitions.
Managing Multidimensional Data.
Processing the Cube. Merging Partitions. Client Write Back. Securing Your OLAP Data.
Microsoft SQL Server 2000 Analysis Services FAQ.
XIV. APPENDIXES.
Appendix A. Naming Conventions.
Appendix B. DBCC Commands.
Quick Reference. Reading the Output from DBCC Commands. Resolving Errors Reported by DBCC. Essential DBCC Commands. DBCC Commands for Verification.
CHECKALLOC. CHECKCATALOG. CHECKDB. CHECKFILEGROUP. CHECKIDENT. CHECKTABLE. DBREINDEX. SHOWCONTIG. UPDATEUSAGE.
DBCC Commands to Return Process Information. DBCC Commands to Return Performance Monitor Statistics. Trace Flag Commands. Data Cache Commands. Transaction Commands. Other DBCC Commands.
SHOW_STATISTICS. SHRINKDATABASE. SHRINKFILE. USEROPTIONS. DBCC dllname (FREE).
Appendix C. SQL Server Resources.
Appendix D. What's on the CD-ROM.
The SQL Server DBA Assistant.
Installing SQL Server DBA Assistant. Using SQL Server DBA Assistant.
Index.