Overview:
This course provides the knowledge and skills to maintain a Microsoft SQL Server 2005 database. It focuses on teaching you how to use SQL Server 2005 product features and tools related to maintaining a database.
Learning Method:
Instructor led.
This course uses a combination of instructor presentations and hands-on practical exercises based on the application of what is covered throughout the course.
Who will benefit from this course?
IT Professionals who want to become skilled on SQL Server 2005 product features and technologies needed to maintain a database.
Pre-requisites
Before attending this course, you must have:
- working knowledge of Transact-SQL
- working knowledge of relational databases
- some experience with database design
In addition, it is recommended, but not required, that you have completed:
- Writing Queries Using Transact-SQL (Course Code: 2071)
- Implementing a Microsoft SQL Server 2005 Database (Course Code: 2779)
What can you expect to gain from this course?
After completing this course, you will be able to:
- install and configure SQL Server 2005
- manage database files
- backup and restore databases
- manage security
- monitor SQL Server
- transfer data into and out of SQL Server
- automate administrative tasks
- replicate data between SQL Server instances
- maintain high availability.
Course Content:
Module 1: Installing and Configuring SQL Server 2005
- In this module you will be:
- preparing to install SQL Server
- installing SQL Server 2005
- managing a SQL Server 2005 installation
- Exercise 1:Installing and configuring a SQL Server 2005
Module 2:Managing databases and files
- In this module you will be:
- planning a database
- creating a database
- managing a databases
- monitoring and managing filegroup usage
- viewing database metadata
- Exercise 2:Managing Databases and Files
Module 3:Disaster Recovery
- In this module you will be:
- planning and implementing a backup strategy
- backing up user databases
- restoring user databases
- performing online restore operations
- recovering data from database snapshots
- using a system database and executing a disaster recovery
- restoring and recovering a database
- performing piecemeal backup and restore operations
- restoring the master database
- Exercise 1:Disaster Recovery
Module 4:Managing Security
- In this module you will be:
- managing principals, securables, and permissions
- implementing cryptography in a SQL Server database.
- reviewing SQL Server security
- protecting the server scope
- protecting the database scope
- managing keys and certificates in SQL Server
- creating logins and assigning server scope permissions
- creating and managing users
- using a certificate to protect data
- Exercise 4: Managing Security
Module 5:Monitoring SQL Server
- In this module you will be:
- monitoring SQL Server performance and activity
- tracing SQL Server activity
- implementing DDL triggers
- viewing current activity
- using system monitor
- using SQL Server profiler
- using DDL triggers
- using event notifications.
- Exercise 5: Monitoring SQL Server
Module 6:Transferring Data
- In this module you will be:
- transferring and transforming data
- reviewing data transfer
- using SQL Server integration services
- reviewing the features of SQL Server integration services
- creating an SSIS package
- deploying an SSIS package
- using SSIS to extract data, perform lookups, sort, and split data
.
- Exercise 6: Transferring Data
Module 7:Automating Administrative Tasks
- In this module you will be:
- 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
- Exercise 1:Automating Administrative Tasks
Module 8:Implementing Replication
- In this module you will be:
- reviewing the concepts underpinning replication, it purpose and application in several common scenarios
- implementing replication
- configuring replication in some common scenarios
- creating a publication
- creating a subscription
- implementing HTTP merge replication
- Exercise 8: Implementing Replication
Module 9:Maintaining High Availability
- Using SQL Server 2005 you will be:
- implementing server clustering
- implementing database mirroring
- implementing log shipping
- implementing peer-to-peer replication
- configuring database mirroring to support failover
- implementing distributed high availability
- Exercise 9: Maintaining High Availability