Course Outline

I. Database Performance and Tuning

Breeze through a review of administering SQL Server 2005 including XML, errors, and certificates and delve into fine tuning your SQL Server database by benchmarking, measuring performance, and reducing locking and blocking.

• Implementing Constraints
  
  • Primary Key
    
  • Unique constraints (null values)
    
  • Check contraints
    
• Implementing Triggers
  
• XML Datatype
  
• Implementing XML Schemas
  
• Implementing Stored Procedures
  
  • Transact-SQL (T-SQL)
    
  • User defines stored procedures
    
  • CLR User defined stored procedures
    
  • Extended stored procedures
    
• Creating Parameterized Stored Procedures
  
• Creating Functions
  
• Handling Errors
  
• Controlling Execution Context
  
• SQL Server Common Language Runtime
  
• Importing and Configuring Assemblies
  
• Creating Managed Database Objects
  
• Overview of SQL Server Security
  
  • Authentication
    
  • Authorization
    
  • Roles
    
  • Permissions
    
• Securing the Server Scope
  
• Securing the Database Scope
  
• Managing Keys and Certificates in SQL Server
  
• Importance of Benchmarking
  
  • SQL Server 2005 Profiler
    
  • SQL stored procedures
    
  • SQL Activity Monitor
    
• Key Measures for Query Performance: Sysmon
  
• Key Measures for Query Performance: Profiler
  
• Guidelines for Identifying Locking and Blocking
  
  • Activity Monitor
    
  • System Monitor; SQL Server locks object
    
  • SP_lock stored procedure
    
• Performance Optimization Model: Queries
  
• What Is Query Logical Flow?
  
• Considerations for Using Subqueries
  
• Guidelines for Building Efficient Queries
  
• Performance Optimization Model: Indexes
  
  • clustered
    
  • non-clustered
    
• Considerations for Using Indexes
  
• Clustered Index Design
  
• Non-Clustered Index Design
  
• Database Tuning Advisor
  
• Performance Optimization Model: Locking and Blocking
  
• Strategies to Reduce Locking and Blocking

Conduct 26 hands-on labs during this section— See DETAILS for specifics

II. High Availability Solutions

Build a high availability solution with your SQL Server database by clustering, database mirroring, and database replicating.

• Designing the Platform for Clustering
  
• Designing the SQL Server Cluster Implementation
  
  • Hardware and software requirements
    
  • Price
    
  • Location
    
  • Management
    
  • Service level and database level failover
    
  • Availability
    
• Migrating and Upgrading SQL Server Clusters
  
• Designing an Operations Plan for Clustering
  
• Introduction to Designing a Log Shipping Solution
  
  • Designing the log shipping server roles and topology
    
  • Designing an upgrade strategy
    
  • Designing an operations plan for log shipping
    
• Designing Log Shipping Server Roles and Topology
  
• Designing a Log Shipping Upgrade Strategy
  
• Designing an Operations Plan for Log Shipping
  
• Designing a Database Mirroring Solution
  
  • Design database rolls and topology for database mirroring
    
  • Convert high availability solutions to database mirroring
    
  • Design an opertions plan for database mirroring
    
• Database Roles and Topology for Database Mirroring
  
• Converting High-availability Solutions to Database Mirroring
  
• Designing an Operations Plan for Database Mirroring
  
• Introduction to Designing a Replication Solution
  
• Designing a Replication Solution
  
• Designing a Replication Upgrade Strategy
  
• Designing an Operations Plan for Replication

Conduct 6 complex hands-on labs in this section— See DETAILS for specifics

III. SQL Server Integration Solutions (SSIS)

Build your SQL Server Integration Services solution (SSIS) by evaluating, planning, designing, implementing, and monitoring SSIS.

• Identifying Data Sources and Destinations
  
• Evaluating Source Data
  
• Identifying Staging Requirements
  
• Planning Packages
  
• Planning Package Development
  
  • Requirements definition
    
  • Requirements analysis
    
  • Preliminary design
    
  • Implementation
    
  • Testing
    
  • Maintenance and Operation
    
• Designing Package Control Flow
  
• Understanding Data Flow
  
• Designing Data Flow Operations
  
• Handling Data Changes
  
• Logging ETL Operations
  
  • Logging for all or some tasks
    
  • Task and container inheritance
    
  • Multiple logs
    
  • Logging for packages
    
  • Logging for tasks and containers
    
• Handling Errors in SSIS
  
• Implementing Reliable ETL Processes with SSIS
  
• Monitoring SSIS Performance
  
  • SSIS logs
    
  • System monitor
    
  • SQL Server profiler
    
  • Log key events and time stamps
    
  • Log on warning events
    
  • Log PipelineExecutionTrees
    
  • PipeExecutionPlan events
    
• Optimizing SSIS Packages
  
• Scaling Out SSIS Packages
  
• Deploying SSIS Packages Operating an SSIS Solution

Conduct 9 hands-on labs during this section— See DETAILS for specifics