Sams Teach Yourself Microsoft SQL Server 2000 in 21 Days

Richard Waymire is a Program Manager with the SQL Server development team at Microsoft. He is also a Microsoft Certified Trainer (MCT), Solution Developer (MCSD), Database Administrator (MCDBA), and Systems Engineer + Internet (MCSE). He is a past president of the Pacific Northwest SQL Server User¿s Group. Rick Sawtell is both a Microsoft Certified Solutions Developer (MCSD) as well as a Microsoft Certified Trainer (MCT). He owns his own consulting firm and writes and trains on several Microsoft products.

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?



Relational Database Objects.



Designing Relational Databases.



Interviewing the Customer. Organizing the Objects. Normalizing the Data. Creating the Database and Tables.



SQL Server and the Client/Server Model. Summary. Workshop.



Q&A. Quiz. Exercises.

Day 2. Installing Microsoft SQL Server 2000.


What Kinds of SQL Servers Are Available?



Standard Edition. Enterprise Edition. Personal Edition. Developer Edition. Evaluation Edition. Microsoft SQL Server 2000 Desktop Edition (MSDE).



SQL Server Requirements.



Hardware and Software Options.



Installing SQL Server 2000.



Beginning the Installation Process. Prerequisites Based on Operating System. Installing SQL Server 2000 (Full Product).



Installing Other SQL Server Optional Components.



Microsoft SQL Server Analysis Services. Microsoft English Query.



Postinstallation: What Did You Get?



The SQL Server Services. Installation Folders. Windows Registry Entries. The SQL Server Default Databases. SQL Server Default Login.



Named versus Default Instances.



The Default Instance. A Named Instance. Common Components. Installing a Named Instance.



Upgrading from a Previous Version of SQL Server.



Upgrading from SQL Server.



Troubleshooting Your Installation.



sqlstp.log. *.OUT Files. SQL Server Error. Windows NT/2000 Application Event.



Removing SQL Server. Summary. Workshop.



Q&A. Quiz. Exercises.

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


The Microsoft SQL Server Program Group.



The readme.txt File. Books Online. Service Manager. Client Network Utility. Server Network Utility. Query Analyzer Enterprise Manager. Import and Export Data. Configure SQL XML Support. Profiler.



The Microsoft SQL Server-Switch Program Group.



SQL Server Version Upgrade Wizard. Microsoft SQL Server 6.x Switch. Uninstall SQL Server.



Other SQL Server Tools/Utilities.



Connectivity Tools. Server Diagnostics/Maintenance Utilities. BCP: In a Class by Itself.



Summary. Q&A. Workshop.



Quiz. Exercises.

Day 4. Creating and Administering Databases and Data Files.


Creating a Database. Gathering Database Information. Setting Database Options. Resizing Your Database.



Expanding Your Database. Shrinking Your Database.



Renaming a Database. Working with Database Filegroups. Removing a Database. Summary. Workshop.



Q&A. Quiz. Exercises.

Day 5. Setting Up Login and User Security.


The SQL Server Security Access Model.



Windows Authentication. SQL Server Login Authentication. SQL Server Database Username. Permissions.



The Security Modes of SQL Server (Including Logins).



Mixed Mode Security. Windows Authentication Mode. Setting the Security Mode. Graphically Managed Logins.



Database Users.



Adding a User to a Database. The Guest Username. Adding an Alias. Changing the Database Owner.



Roles.



The Public Role. Serverwide Roles. Database Roles.



Application Roles. Summary. Workshop.



Q&A. Quiz. Exercises.

Day 6. Establishing Database Permissions.


Why Use Permissions? Implementing Database Permissions.



Types of Permissions. Permissions Precedence.



SQL Server's Special Permissions.



Fixed Server Role Permissions. sa. Fixed Database Roles. The Database Owner (dbo). Database Object Owner (dboo) Permissions. User Permissions. The Public Role.



Statement Permissions.



The CREATE DATABASE Permission. The CREATE TABLE, VIEW, FUNCTION, PROCEDURE, DEFAULT, and RULE. Permissions. The BACKUP DATABASE and BACKUP LOG Statement Permissions. Assigning Statement Permissions. Administering Statement Permissions with SQL Server Enterprise. Manager.



Object Permissions.



Assigning Object Permissions.



Permissions on Views, Stored Procedures, and Functions.



Permissions on Views. Permissions on Stored Procedures. Permissions on User-Defined Functions.



Ownership Chains.



Single-Owner Chain. Broken Ownership Chains.



Designing a Permissions Strategy.



Best Practices. Do's and Don'ts. Generating Scripts for Security.



Summary. Workshop.



Q&A. Quiz. Exercises.

Day 7. Backing Up Your Databases.


Protecting Data with Mirroring, Duplexing, and Striping.



RAID.



How Backup Works



Backup Types. Transactions and Checkpoints. Alternative Backup Strategies.



Backup Terminology. Backup Considerations.



Who's Responsible for the Backups? How Will You Verify That the Backups Are Good? Which Databases Should You Back. Where Should You Back Up Your Databases? How Often Should You Back Up Your Databases? How Long Should You Keep Your Backups? How Long Will It Take to Recover a Backup? Do You Have a Disaster Recovery Plan? Miscellaneous Tape Considerations.



Backing Up User Databases.



Database Recovery Models and Backup.



Backing Up System Databases.



The master Database. The MSDB Database. The model Database. The tempdb Database. The Distribution Database.



Preparing to Implement Backups.



Creating a Backup Device. Verifying Database Consistency.



Backing Up a Database.



Backup Device Options. The BACKUP DATABASE Command for Entire Databases. The BACKUP DATABASE Command for Files and Filegroups. The BACKUP LOG Command. Using SQL Server Enterprise Manager for Backups.



Summary. Workshop.



Q&A. Quiz. Exercises.

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

Day 8. Restoring Your Databases.


Restoring Databases.



Automatic Recovery. Manual Recovery.



Recovery Scenarios.



Recovery After Losing a Disk. Recovery After Losing the master Database.



Summary. Workshop.



Q&A. Quiz. Exercises.

Day 9. Data Types and Creating Tables.


Tables. Columns. SQL Server Identifiers.



Data Types. Nullability. Collate.



Strings. Binary Data. Integers. Approximate and Exact Numeric Data Types.



Approximate Numeric Data Types. Exact Numeric Data Types.



Special Data Types.



bit. text and image. RowGUID. sql_variant. table.



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.



Using the CREATE TABLE Statement. Creating Tables with SQL Server Enterprise Manager. Dropping Tables.



Summary. Workshop.



Q&A. Quiz. Exercises.

Day 10. Retrieving Data with the SELECT Statement.


Writing Simple SELECT Statements.



Changing Column Headings. Using Literals.



Manipulating Data.



Arithmetic Operators. Mathematical Functions. String Functions. Date Functions.



System Functions. Data Conversion. Choosing Rows.



Comparison Operators. Ranges. Lists. Character Strings. Unknown Values. Using Multiple Criteria to Retrieve Rows.



Eliminating Duplicate Information. Sorting Data Using the ORDER BY Clause. Midday Review. Aggregate Functions.



GROUP BY and HAVING. COMPUTE and COMPUTE.



Super Aggregates (ROLLUP and CUBE). Data Correlation.



Implementing Joins. Inner Joins. Natural Joins. Cross or Unrestricted Joins. Outer Joins. Self Joins.



Working with Subqueries.



How to Use a Subquery. Types of Subqueries. Correlated Subqueries.



SELECT INTO. UNION Operator. Summary. Workshop.



Q&A. Quiz. Exercises.

Day 11. Modifying Data.


Transaction Logging. Inserting Data.



Using the INSERT VALUES Statement. DEFAULT VALUES. Inserting Data Using SELECT. Using Stored Procedures to Insert Data.



Deleting Data.



Performing a DELETE Using a Lookup Table. Using TRUNCATE TABLE.



Updating Data.



Using the UPDATE Statement. Performing an UPDATE Using a Lookup Table.



Summary. Workshop.



Q&A. Quiz. Exercises.

Day 12. Using Transact-SQL Extensions.


Batches. Scripts. Transactions.



Transaction Types. Distributed Transactions.



Control-of-Flow Language Elements.



BEGIN...END Block. PRINT Statement. IF...ELSE Block. CASE Expressions. Using the WHILE Command.



Locking.



Lock Types. Controlling Locking. Observing Locks.



Summary. Workshop.



Q&A. Quiz. Exercise.

Day 13. Indexing for Performance.


Why Use Indexes?



Index Structures. Index Options.



SQL Server Indexes.



The fillfactor and pad_index Options. The DROP_EXISTING Option. The STATISTICS_NORECOMPUTE Option. The SORT_IN_TEMPDB Option. The IGNORE_DUP_KEY Option.



Index Usage.



When Will My Index Be Used? How Do You Verify Index Usage? Overriding the Optimizer.



Index Maintenance.



Statistics. The DBCC UPDATEUSAGE Command. The DBCC SHOWCONTIG Command. The DBCC DBREINDEX Command. The DBCC INDEXDEFRAG Command. Indexed Views.



Managing Indexes with SQL Server Enterprise Manager. Index Selection.



Clustered Index Choices. Nonclustered Index Choices. The SQL Server Index Tuning Wizard.



Full-Text Indexing. Summary. Workshop.



Q&A. Quiz. Exercises.

Day 14. Ensuring Data Integrity.


How to Enforce Data Integrity. Types of Integrity.



Domain Integrity. Referential Integrity. Entity Integrity.



Traditional Methods of Integrity.



User-Defined Data Types. Defaults. Rules.



Ensuring Data Accuracy with ANSI Constraints and Declarative Integrity.



Mechanisms.



The IDENTITY Property. ANSI Constraints. Dropping Constraints.



Ensuring Data Accuracy with SQL Server Enterprise Manager.



Deferring Constraints. Disabling Constraints.



Summary. Workshop.



Q&A. Quiz. Exercises.

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

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


Creating and Manipulating Views.



Creating Views. Gathering Information on Views. Types of Views. Modifying Data in Views. Altering Views. Removing Views.



Working with Stored Procedures.



Creating Stored Procedures. Gathering Information on Stored Procedures. Creating a Group of Procedures. Using Parameters with Stored Procedures. Using the WITH RECOMPILE Option. Making Your Stored Procedures Run Automatically at SQL Startup. Using the WITH ENCRYPTION Option. Using Remote Stored Procedures.



Working with Triggers.



Understanding the inserted and deleted Tables. Creating Triggers with the CREATE TRIGGER Statement. Enforcing Data Integrity with Triggers. Using INSTEAD OF Triggers. Gathering Information on Triggers.



Working with User-Defined Functions.



Scalar Functions. Inline Table-Valued Functions. Multistatement Table-Valued Functions.



Summary. Workshop.



Q&A. Quiz. Exercises.

Day 16. Understanding Replication Design Methodologies.


What Is Replication? Distributed Data Factors. Distribution Methodologies.



Merge Replication. Snapshot Replication. Transactional Replication. Updateable Subscriptions. Snapshot Replication with Updating Subscribers. Transactional Replication with Updating Subscribers. Distributed Transactions.



The Publisher/Subscriber Metaphor.



Articles. Publications.



How Does Replication Work?



How Does Merge Replication Work? Snapshot Replication Internals. Transaction Replication Internals. Queued Updating Internals.



Replication Scenarios.



Central Publisher. Central Publisher with a Remote Distributor. Republisher. Central Subscriber. Multiple Publishers and Subscribers. Transforming Published Data.



Publishing SQL Server 2000 Databases to the Internet.



Publishing Through. Publishing Through Microsoft Proxy Server. Publishing Through File Transfer Protocol (FTP).



Best Practices. Replicating Issues in a Heterogeneous Environment.



Publishing Data to Non-SQL Server 2000 Databases. Publishing Non-SQL Server 2000 Data to SQL Server 2000.



Summary. Workshop.



Q&A. Quiz. Exercise.

Day 17. Implementing Replication Methodologies.


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



Installing the Distribution Server. Creating a Publication. Creating a Subscription. Manual Synchronization. Verifying the Initial Synchronization. Testing the Replication.



Replication Considerations.



Publishing Issues. Subscriber Issues. Performance Issues.



Maintaining Replication.



Using the Replication Monitor. Distribution Server Management. Replication Scripts. Performance Monitor and Replication. Using Stored Procedures. Administration Considerations. Troubleshooting. Replication Recovery Issues.



Summary. Workshop.



Q&A. Quiz. Exercise.

Day 18. Scheduling Jobs with SQL Server Agent.


Why Automate SQL Server? Which Scheduling Tool Should You Use?



The Windows 2000 Schedule Service. The SQL Server Agent Service.



SQLMail Integration.



Configuring a Microsoft Outlook Messaging Profile. Configuring SQL Mail for SQL Server. Configuring SQL Mail to Use with SQL Server Agent.



Sending Email from SQL Server. Scheduling Jobs.



Creating a New. Setting Up Job Steps. Job Scheduling. Job Notifications. Setting Job System Options. Multiserver Jobs.



Setting Up Alerts and Operators.



Configuring Operators. Creating Alerts. Setting Alert System Options.



Taking Advantage of Performance Monitor Integration. Using the Database Maintenance Plan Wizard. Summary. Workshop.



Q&A. Quiz. Exercises.

Day 19. Migrating Data Between Database Servers.


Moving Data with a Backup and Restore. Using BCP to Load Data.



BCP Permissions. Using the BCP Program Native Mode versus Character Mode. Format Files. Batch Sizes. Fast.



Working with the BULK INSERT Command. Working with Data Transformation Services.



Understanding Data Transformation Packages. Exporting Data with the DTS Import and Export Wizards.



Summary. Workshop.



Q&A. Quiz. Exercises.

Day 20. Performance Monitoring Basics and Tools.


Hardware Selection and Tuning.



Memory. Processor. Network. Disks.



Windows 2000 Configuration Parameters. Windows 2000 Performance Monitor Counters.



Memory. Processor Network. Disk.



Configuring SQL Server.



Processor Options. Memory Configuration Options. I/O Options. Query/Index Options.



Monitoring SQL Server Performance.



SQL Server Performance Monitor Counters. System Stored Procedures for Monitoring Performance.



The Current Activity Window of SQL Server Enterprise Manager.



Using the SQL Server Profiler.



The Index Tuning Wizard. Summary. Workshop.



Q&A. Quiz. Exercises.

Day 21. Using XML with SQL Server 2000.


What Is XML? Accessing SQL Server via Internet Information Server.



Virtual Directories and SQL Server. Using SQL Server Through URLs.



Reading and Writing XML in SQL Server.



Valid SELECT Statements. Using the FOR XML Clause.



Summary. Workshop.



Q&A Quiz. Exercises.

Week 3. In Review.
BONUS DAYS. AT A GLANCE.

Day 22. An Overview of Analysis Services.


Understanding Data Warehousing.



Fact Tables. Dimensions. Aggregation.



Understanding Data Marts. Building a Data Warehouse.



Step 1: Create a Holding Area for the Data. Step 2: Get Data from a Legacy or OLTP System. Step 3: Transform the Data, If Necessary. Step 4: Load the Transformed Data into Your Warehouse. Step 5: Build and Update OLAP Cubes in Analysis Services.



Understanding the Components of Analysis Services.



OLAP. Data Mining. Meta Data Services.



Installing Analysis Services. Accessing the Sample Application for Analysis Services. Summary.



Q&A.

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. Summary.



Q&A.

Appendix A. Answers to Quiz Questions.
Index.