Course suitable for: This course is intended for IT Professionals wanting to become skilled on SQL Server 2005 product features and technologies for Implementing and maintaining a SQL 2005 database. The course is also suitable to any IT Professionals wanting to update their skills from SQL 2000 to SQL 2005.
All delegates should have a working knowledge of:
- Basic knowledge of the Microsoft Windows operating system and its core functionality.
- Working knowledge of Transact-SQL.
- Working knowledge of relational databases.
- Some experience with database design.
Course objectives: At the end of the course, delegates will be able to:
- Install and configure SQL Server 2005.
- Manage database files.
- Manage security.
- Perform administrative tasks.
- Backup databases.
- Rstore databases.
- Monitor SQL Server.
- Troubleshoot SQL Server.
- Transfer data.
- Maintain high availability.
- Create databases and database files.
- Create data types and tables.
- Use XML-related features in Microsoft SQL Server 2005.
- Plan, create, and optimize indexes.
- Implement data integrity in Microsoft SQL Server 2005 databases by Using constraints, triggers, and XML schemas.
- Implement views.
- Implement stored procedures and functions.
- Implement managed code in the database.
- Use Service Broker to build a messaging-based solution.
Course content:Installing and configuring SQL Server 2005
- Preparing to install SQL Server
- Installing SQL Server
- Managing a SQL Server 2005 Installation
Managing Databases and Files
- Planning databases
- Creating databases
- Creating Filegroups
- Creating Schemas
- Creating Database Snapshots
- Managing databases
Creating Data Types and Tables
- Creating Data Types
- Creating Tables
- Creating Partitioned Tables
Writing Queries Using Transact-SQL
- Getting started with Transact-SQL.
- Performing basic queries.
- Grouping and summarizing data.
- Joining data from multiple tables.
- Working with subqueries.
- Modifying data.
- Querying metadata, XML, and full-text indexes.
- Using programming objects for data retrieval.
- Using advanced querying techniques.
Using XML
- Retrieving XML by Using FOR XML
- Shredding XML by Using OPENXML
- Using the xml Data Type
Creating and Tuning Indexes
- Planning Indexes
- Creating Indexes
- Optimizing Indexes
- Creating XML Indexes Implementing Data Integrity
- Data Integrity Overview
- Implementing Constraints
- Implementing Triggers
- Implementing XML Schemas
Implementing Views
- Introduction to Views
- Creating and Managing Views
- Optimising Performance by Using Views
Implementing Stored Procedures and Functions
- Implementing Stored Procedures
- Creating Parameterized Stored Procedures
- Creating Functions
- Handling Errors
- Controlling Execution Context
Implementing Managed Code in the Database
- Introduction to the SQL Server Common Language Runtime
- Importing and Configuring Assemblies
- Creating Managed Database Objects
Using Service Broker
- Service Broker Overview
- Creating Service Broker Objects
- Sending and Receiving Messages
Managing Security
- Overview of SQL Server Security
- Securing the Server Scope
- Securing the Database Scope
- Implementing Cryptography in SQL Server
Disaster Recovery
- Planning a Backup Strategy
- Backing Up User Databases
- Restoring User Databases
- Recovering Data from Database Snapshots
System Database and Disaster Recovery Monitoring SQL Server
- Viewing Current Activity
- Using System Monitor and SQL Server Profiler
Using DDL Triggers and Event Notifications Transferring Data
- Overview of Data Transfer
- Introduction to SQL Server Integration Services (SSIS)
- Using SQL Server Integration Services
Automating Administrative Tasks
- Automating Administrative Tasks in SQL Server 2005
- Configuring the SQL Server Agent
- Creating Jobs and Operators
- Creating Alerts
- Managing Multiple Servers
- Managing SQL Server Agent Security
Maintaining High Availability
- Introduction to High Availability
- Implementing Server Clustering, Database Mirroring and Log Shipping
Introduction to Replication
Further courses to consider
M2781 - Designing MS SQL Server 2005 Server-Side Solutions.