COURSE 9050 | 5-DAY SESSION
Extreme Microsoft SQL Server 2005

---

Leverage over 70 real world, hands-on labs to become a master SQL Server DBA

In-Class Workshops and Group Exercises:

With over 70 labs, this SQL Server 2005 course is more “hands-on” than any other course you’ll find. You will leave this class with tons of real-world experience that will propel you up through your company.

A recent survey of over 8,300 IT projects that were undertaken by 365 different companies makes the problem clear - only 16% of these projects were completed on-time and on-budget. An earlier survey of over 1,000 general projects found only slightly more than 12% were considered successful.

If you are an IT manager, a software development project manager, or a team member responsible for project estimation, Microsoft SQL Server 2005: Advanced Topics will provide you with the tools and understanding to produce the scope, cost, and time estimates your project needs to succeed. Projects don't fail at the end, they fail at the beginning.

Specifically you will:

Implement UNIQUE Constraints

Implement CHECK Constraints

Create a DDL Trigger

Create a DML Trigger

Create a Stored Procedure

Create a Parameterized Stored Procedure

Create an Aggregate Function

Create a Scalar Function

Use the TRY…CATCH Construct

Use the @@Error Function

Using the EXECUTE AS Clause

Implement an XML Schema

Create a DDL Assembly

Import an Assembly

Create Database Using SSMS

Create Database Using T-SQL

Create Database Using the Template Explorer

Rename a Database

Change a Database Owner

Space Used by a Database

Decrease the Database Size

Create a Table

Create a View

Create Logins

Create and Delete Users

Create Roles

Create a Certificate

Encrypt and Decrypt Data

Secure the Server Scope

Secure the Database Scope

Measure System Performance using System Monitor

Measure System Performance using the SQL Server 2005 Profiler

Identify Locking and Blocking

Create a SQL Statement Using the SELECT Clause

Create a SQL Statement using the FROM Clause

Create a SQL Statement Using the WHERE Clause

Create a SQL Statement Using the GROUP BY Clause

Create a SQL Statement Using the HAVING Clause

Create a SQL Statement Using the ORDER BY Clause

Create a Clustered Index

Using a Clustered Index

Using a Non-Clustered Index

How to use the Database Tuning Advisor using a Query

How to use the Database Tuning Advisor using a Trace Log

Implement a Table-Level Lock

Implement a Page-Level Lock

Implement a Row-Level Lock

Implement Query to Reduce Locking and Blocking

Implement Strategies to Reduce Locking and Blocking

Implement a SQL Cluster

Implement a Log Shipping Solution

Implement a SQL Mirroring Solution

Install a SQL Server 2005 Instance for the Witness Server

Implement a Replication Solution

Configure Peer-to-Peer Replication

Install SQL Server 2005 Reporting Services

Working With SQL Server 2005 Reporting Services

Configure Report Manager

How to Develop Basic Reports

How to Use the Report Wizard

Implement Custom Applications with Reporting Services

Using the Reporting Services Web Features

Implement a Distribution Strategy

Securing Reporting Services

Optimizing the Reporting Services Performance

How to Use the Report Builder

Install SQL Server 2005 Integration Services (SSIS).

Working With Business Intelligence Development Studio (BIDS).

How to Use the SSIS Import and Export Wizard.

Build an SQL Server 2005 Integration Services Package.

How to Execute and SSIS Package from the Command Line.

How to Log ETL Operations.

How to Handle Errors in SQL Server 2005 Integration Services.

Monitor, Optimize, and Scale SQL 2005 Server Packages.

Deploying a SQL Server 2005 Integration Service Package.

Immediate Benefits of Taking This Class:

1. Understand data verification in the database using constraints and triggers.
   
2. Create stored procedures and learn their pros and cons
   
3. Store, access, modify and index XML data in its native format.
   
4. Work with and leverage the new schema object model.
   
5. Secure your data as well as your customers’ data.
   
6. Improve calculations and analysis of data using user defined functions built with Common Language Runtime.
   
7. Use Performance Monitor and SQL Profiler to optimize SQL server performance.
   
8. Write optimized TSQL queries to improve performance.
   
9. Use the Database Tuning Advisor for superior performance.
   
10. Use indexes to optimize access to data in the database.
    
11. Analyze SQL locks to determine and eliminate bottlenecks due to locking.
    
12. Design highly available database solutions.
    
13. Configure SQL Server for Microsoft Clustering.
    
14. Understand differences and benefits of log shipping and database mirroring.
    
15. How to use database replication to your advantage.
    
16. Use SQL Reporting services to efficiently and effectively display information from SQL server.
    
17. Configure Reporting Services both internally and externally.
    
18. Setup and manage a secure Reporting Services deployment.
    
19. Design Reports using SQL Server Reporting Services (SSRS).
    
20. Scale your reporting environment up as your company’s needs grow.
    
21. Move data to and from a variety of data sources and destinations.
    
22. Leverage the power of SQL Server Integration Services (SSIS) in your environment.
    
23. Be able to transform data into any form you need.
    
24. Deploy SSIS Packages.
    
25. Control data flow through SQL server using SSIS Packages.
    

Who Should Attend:

This class provides definitive instruction on how to harness the latest features of Microsoft’s SQL Server 2005. Anyone involved in the use or management of a database using SQL 2005 will find this class invaluable, including:

• Database Designers
  
• Database Administrators
  
• Database Developers
  
• Data managers in multiple database environments
  
• Senior database managers and administrators
  
• SQL Server 2000 DBA’s moving to SQL Server 2005
  
• Project managers with SQL Server 2005 projects