Sams Teach Yourself Microsoft SQL Server 2000 in 21 Days

Richard Waymire, a group program manager with the SQL Server development team at Microsoft, is also a Microsoft Certified Database Administrator and Systems Engineer. He's been working on various versions of SQL Server since 1994, and joined the Microsoft development team in 1998. Richard has been a programmer, DBA, and trainer on SQL Server before joining Microsoft. Richard still considers himself a database administrator at heart. You can reach Richard at rwaymi@hotmail.com. Rick Sawtell is a Microsoft Certified Solutions Developer and Certified Trainer. He owns his own consulting firm based in the Hawaiian Islands and currently consults, trains, and writes computer books on several Microsoft products. He has more than 17 years of experience in the consulting and programming arena, with emphasis on database front-end and back-end development. You can reach Rick at r_sawtell@hotmail.com.

(NOTE: Each chapter concludes with a Summary, Q&A, and Workshop.)

Introduction.
WEEK 1. AT A GLANCE.

Day 1. Introducing Relational Databases and Microsoft SQL Server 2000.


SQL Server Is Hot! The History of SQL Server. What Is a Database? Designing Relational Databases. SQL Server and the Client/Server Model.

Day 2. Installing Microsoft SQL Server 2000.


What Kinds of SQL Servers Are Available? SQL Server Requirements. Installing SQL Server 2000. Installing Other SQL Server Optional Components. Postinstallation: What Did You Get? Named Versus Default Instances. Upgrading from a Previous Version of SQL Server. Troubleshooting Your Installation. Removing SQL Server. Apply the Latest Service Pack.

Day 3. Working with SQL Server 2000 Management Tools and Utilities.


The Microsoft SQL Server Program Group. The Microsoft SQL ServerSwitch Program Group. Other SQL Server Tools/Utilities.

Day 4. Creating and Administering Databases and Data Files.


Creating a Database. Gathering Database Information. Setting Database Options. Resizing Your Database. Renaming a Database. Working with Database Filegroups. Removing a Database.

Day 5. Setting Up Login and User Security.


The SQL Server Security Access Model. The Security Modes of SQL Server (Including Logins). Database Users. Roles. Application Roles.

Day 6. Establishing Database Permissions.


Why Use Permissions? Implementing Database Permissions. SQL Servers Special Permissions. Statement Permissions. Object Permissions. Permissions on Views, Stored Procedures, and Functions. Ownership Chains. Designing a Permissions Strategy.

Day 7. Backing Up Your Databases.


Protecting Data with Mirroring, Duplexing, and Striping. How Backup Works. Backup Terminology. Backup Considerations. Backing Up User Databases. Backing Up System Databases. Preparing to Implement Backups. Backing Up a Database.

Week 1. In Review.
WEEK 2. AT A GLANCE.

Day 8. Restoring Your Databases.


Restoring Databases. Recovery Scenarios.

Day 9. Data Types and Creating Tables.


Tables. Columns. SQL Server Identifiers. Strings. Binary Data. Integers. Approximate and Exact Numeric Data Types. Special Data Types. Date and Time Data Types. Money. The Auto-incrementing rowversion (timestamp) Data Type. Working with ANSI and Unicode. Creating User-Defined Data Types. Working with Computed Columns. Creating Tables.

Day 10. Retrieving Data with the SELECT Statement.


Writing Simple SELECT Statements. Manipulating Data. System Functions. Data Conversion. Choosing Rows. Eliminating Duplicate Information. Sorting Data Using the ORDER BY Clause. Midday Review. Aggregate Functions. Super Aggregates (ROLLUP and CUBE). Data Correlation. Working with Subqueries. SELECT INTO. UNION Operator.

Day 11. Modifying Data.


Transaction Logging. Inserting Data. Deleting Data. Updating Data.

Day 12. Using Transact-SQL Extensions.


Batches. Scripts. Transactions. Control-of-Flow Language Elements. Locking.

Day 13. Indexing for Performance.


Why Use Indexes? SQL Server Indexes. Index Usage. Index Maintenance. Managing Indexes with SQL Server Enterprise Manager. Index Selection. Full-Text Indexing.

Day 14. Ensuring Data Integrity.


How to Enforce Data Integrity. Types of Integrity. Traditional Methods of Integrity. Ensuring Data Accuracy with ANSI Constraints and Declarative Integrity Mechanisms. Ensuring Data Accuracy with SQL Server Enterprise Manager.

Week 2. In Review.
WEEK 3. AT A GLANCE.

Day 15. Creating Views, Triggers, Stored Procedures, and Functions.


Creating and Manipulating Views. Working with Stored Procedures. Working with Triggers. Working with User-Defined Functions.

Day 16. Understanding Replication Design Methodologies.


What Is Replication? Distributed Data Factors. Distribution Methodologies. The Publisher/Subscriber Metaphor. How Does Replication Work? Replication Scenarios. Publishing SQL Server 2000 Databases to the Internet. Best Practices. Replicating Issues in a Heterogeneous Environment.

Day 17. Implementing Replication Methodologies.


Understanding Security Issues. Setting Up Your Servers. Installing Replication. Replication Considerations. Maintaining Replication.

Day 18. Scheduling Jobs with SQL Server Agent.


Why Automate SQL Server? Which Scheduling Tool Should You Use? SQLMail Integration. Sending Email from SQL Server. Scheduling Jobs. Setting Up Alerts and Operators. Taking Advantage of Performance Monitor Integration. Using the Database Maintenance Plan Wizard.

Day 19. Migrating Data Between Database Servers.


Moving Data with a Backup and Restore. Using BCP to Load Data. Working with the BULK INSERT Command. Working with Data Transformation Services.

Day 20. Performance Monitoring Basics and Tools.


Hardware Selection and Tuning. Windows 2000 Configuration Parameters. Windows 2000 Performance Monitor Counters. Configuring SQL Server. Monitoring SQL Server Performance. The Current Activity Window of SQL Server Enterprise Manager. The Index Tuning Wizard.

Day 21. Using XML with SQL Server 2000.


What Is XML? Installing SQLXML 3.0 Service Pack 1. Accessing SQL Server via Internet Information Server. Reading and Writing XML in SQL Server. New Features in SQLXML 3.0.

Week 3. In Review.
WEEK 4. AT A GLANCE.

Day 22. An Overview of Analysis Services.


Understanding Data Warehousing. Understanding Data Marts. Building a Data Warehouse. Understanding the Components of Analysis Services. Installing Analysis Services. Accessing the Sample Application for Analysis Services.

Day 23. An Overview of English Query.


English Query Components. How English Query Works. Normalization Requirements. Creating an English Query Application. Adding Synonyms to Your Entities. Adding Relationships to Your Model. Testing an English Query Application. Building an English Query Application.

APPENDIXES.

Appendix A. Answers to Quiz Questions.
Appendix B. Installing MSDE 2000.


What Is MSDE 2000? MSDE Licensing. MSDE Components. Installing MSDE 2000. Installing an MSDE 2000 Service Pack.

Appendix C. High Availability Overview.


Microsoft Cluster Service. Overview of High Availability. Terminology. Cluster Server Architecture. Cluster Service Components. Cluster Formation and Operation. Cluster Installation and Configuration. Log Shipping in SQL Server 2000.

Index.