MCSE

I. MICROSOFT SQL SERVER OVERVIEW. 1. An Overview of Microsoft SQL Server. Microsoft SQL Server and Windows NT. Windows NT Subsystems. Windows 95/98. Client/Server and Microsoft SQL Server. Why Use SQL Applications? Ease of Use. Simplify Systems. Enforcement of Business Procedures. Building Custom Applications. Cost Factors. Multiprocessing Versus Multithreaded. Multiprocess Systems. Multithreaded Systems. Networking. Protocol Options. Local Area Networks. Wide Area Networks. Internet or Intranet. Commonly Used SQL Server Interfaces. Transact-SQL. OLE DB. ODBC. Microsoft English Query. Fundamentals of Database Building. The Relational Database. What Is a Relational Database? The Normalized Database. Tables, Columns, and Rows. Device Independence. Key Points. Chapter Review Questions. 2. What Is SQL? T-SQL and ANSI-SQL. Standards and Compatibility. Types of SQL Commands. Data Definition Language (DDL). Data Manipulation Language (DML). Data Control Language (DCL). Commonly Used T-SQL Statements. How to Use T-SQL Statements. SELECT. INSERT. UPDATE. DELETE. Transactions and SQL. Key Points. Chapter Review Questions. 3. SQL Server Structure. Creating a Database with SQL Server 7.0. Compatibility with Earlier Devices. Database Objects. Tables. Views. Database Roles. Defaults. Rules. Stored Procedures. Triggers. Cursors. Indexes. System Catalog. Key Points. Chapter Review Questions. 4. Microsoft SQL Server Components. SQL Mail. Database Maintenance Plans. Processes. Files. master Database. model Database. msdb Database. Sample Databases. tempdb Database. Log Files. Web Files. Key Points. Chapter Review Questions. 2. INSTALLATION AND CONFIGURATION. 5. Installing Microsoft SQL Server 7.0. Operating System Requirements. Windows NT. Windows 95/98. Hardware Requirements. Required User Accounts. Running the Setup Application. Full-Text Search Option. Installation Options. Network Installations. Installing SQL Server Clients Tools. Troubleshooting Setup. Client Connections. Key Points. Chapter Review Questions. 6. Configuration Options. Optimizing Memory Usage. System Memory. Monitoring and Profiling. Performance Monitors. The Profiler. Allocating Memory. Optimizing Disk Performance. The Multithreaded Server. Key Points. Chapter Review Questions. 7. Startup and Shutdown. Starting Microsoft SQL Server. Using the Services Applet. Using the SQL Server Enterprise Manager. SQL Server Service Manager. Using the Server Manager. Using a Command Line Startup. Options for Startup. Troubleshooting Startup. Using the SQL Server Error Log. Using the Windows NT Event Viewer. Viewing the Output Files. Checking Named Pipes. Key Points. Chapter Review Questions. 3. PLANNING AND MANAGING SECURITY. 8. Security. Identification and Authentication. Windows NT Security. SQL Server Security. Database Security. Selecting the Type of Security. Windows NT Authentication. SQL Server Authentication. Mixed Security. Trusted Connections. Setting Security Modes. Creating Users. Devising Usernames. Passwords. Guest User. Using Roles. Application Roles. An Owner. Using the Permissions System. GRANTing Permission. DENYing Permission. REVOKE Permissions. Permissions and the SQL Server Enterprise Manager. Using the SQL Server Security Wizard. Security Between Linked Servers. Key Points. Chapter Review Questions. 4. WORKING WITH DATABASE FILES. 9. Managing Your Database Files. The Relational Database. The Normalized Database. Tables, Columns, and Rows. What Is a Relational Database? Device Independence. Key Points. Chapter Review Questions. 10. What Is Data Integrity? What Is Data Integrity? Entity Integrity. Primary Key Constraint. UNIQUE Constraints. Using the uniqueidentifier Data Type. Domain Integrity. The DEFAULT Constraint. Using the CHECK Constraint. Domain Rules. Datatypes. Referential Integrity. What Is Referential Integrity? User-Defined Integrity. Key Points. Chapter Review Questions. 11. Data Consistency and Concurrency. Statements and Transactions. Using Locks for Consistency. Understanding a Lock. Setting Transaction Isolation Level. Key Points. Chapter Review Questions. 12. Managing Database Space. Planning Database Space Requirements. Planning the Database. Sizing the Log File. Expanding a Database. Shrinking a Database. Deleting a Database. Key Points. Chapter Review Questions. 5. TRANSFERRING DATA. 13. PLANNING DATA TRANSFERS. Where Is the Data Now? What Kind of Data? Transforming Versus Inserting Data. Key Points. Chapter Review Questions. 14. Methods of Transferring Data. Using the Bulk Copy Program (bcp). Making a Database Ready for bcp. Creating the bcp Format File. Editing the bcp Format File. Using SELECT INTO Statements. SQL Server Version Upgrade Wizard. Data Transformation Services (DTS). What Is DTS? Scrubbing and Validating Data. Creating a Data Warehouse. Key Points. Chapter Review Questions. 6. BACKING UP A DATABASE. 15. Why Back Up Your Data. Types of Failures. Client Application Failure. Program Failure. Disk or Media Failures. Key Points.Chapter Review Questions. 16. Backing Up the Database. Using Backups. Forms of Backups. Creating Your Backup Strategy. Creating a Backup Device. Performing a Backup. Heavy Duty Protection-Mirrors and RAID. RAID and Disk Arrays. Using Removable Media. SQL Server Failover Support. Key Points. Chapter Review Questions. 7. RESTORING DATABASES. 17. Restoring the Database. Restoring a Database. Restoring a Damaged Database. Restoring Transaction Logs and Differential Backups. Recovering the Master Database. Key Points. Chapter Review Questions. 8. MONITORING SQL SERVER PERFORMANCE. 18. Optimizing Performance. Optimizing Queries. Cost-Based Optimization. Clustered Indexes. Using Nonclustered Indexes. Creating Useful Indexes. Other Performance-Tuning Factors. Creating Full-Text Search Indexes. Key Points. Chapter Review Questions. 9. AUTOMATING TASKS. 19. The SQL Server Agent. The Role of the SQL Server Agent. Setting Up the SQL Server Agent Service. The SQL Server Agent Error Logs. Multiserver Administration. Creating the Multiserver Group. Defecting a Target Server. Key Points. Chapter Review Questions. 20. Creating and Using Schedules. Defining a Job. Starting a Job Definition. Defining the Steps of a Job. Creating Schedules. Using Notifications on a Job. Troubleshooting Scheduling. Stopping a Scheduled Job. Using a Stored Procedure. Using Alerts. Setting Alerts. Alert Responses. Using a Stored Procedure with Alerts. Removing an Alert. Key Points. Chapter Review Questions. 10. PUBLISHING DATA ON THE WEB. 21. Your Data and the Web. The Web Assistant. Key Points. Chapter Review Questions. 11. Beginning Replication. 22. Replication Basics. Replication Models. Publisher. Subscriber. Replicated Data. Push Versus Pull. Types of Replication. Snapshot Replication. Transactional Replication. Merge Replication. Key Points. Chapter Review Questions. 12. PLANNING A REPLICATION STRATEGY. 23. Planning and Setting Up a Replication Strategy. Planning Replication. What Information Needs to be Replicated. Choosing a Replication Model. When to Replicate. Site Autonomy. Who Can Subscribe? Setting Up Your Servers. Setting Up a Publisher and Distributor. Setting Up Subscribing Servers. Replication and Synchronization. Creating Articles and Publications. Key Points. Chapter Review Questions. 13. MANAGING REPLICATION AND DISTRIBUTED TRANSACTIONS. 24.Replication Management. Distributed Transactions. Two-Phase Commit. Definitions. How the Two-Phase Commit Works. Configuring MS DTC. Problem Areas. Heterogeneous Replication. Monitoring Replication. Performance Monitoring Tools. Troubleshooting Replication and Viewing Error Logs. Key Points. Chapter Review Questions. Appendix: Answers to Chapter Review Questions. Index.