Schedules:

There are currently no scheduled classes for this course.

 
  Introduction to SQL Server Database Administration
 

5 Days (SQ-DBAINTR-101-EN)

Description
Target Audience
Prerequisites
Course Objectives
Course Summary Outline

Description

Often developers are faced with performing database administration tasks against the databases they are developing against. This 5-day introduction level course is designed for developers and power users who have found themselves faced with becoming a database administrator. This course prepares individuals who are familiar with databases with the skills they need to understand the tasks that must be performed to maintain and administrate Microsoft SQL Server 2005 and 2008 databases.

Target Audience

This course is intended for:

  • SQL Server Power Users
  • Developers who need to maintain and administrate SQL Server databases
  • Individuals who understand database concepts and who would like to become SQL Server DBAs.

Prerequisites

Before attending this course, it is recommended that students have the following skills:

  • Experience with and understanding of database concepts
  • Experience with SQL Server development
  • Experience with Transact-SQL

Course Objectives

This course has been designed with the objective of providing the fundamentals needed to install, maintain, and administrate a SQL Server 2005 or SQL Server 2008 database. The course attendees will not only learn the topic listed in the course outline, they have the opportunity to learn SQL Server administrative best practices and tips/tricks from some of the most experienced SQL Server database administrators in the world.

Course Summary Outline

Module 01: Introduction to SQL Server Components

This module will provide the course attendees with the basic understanding of SQL Server and the components that can be installed with each SQL Server installation.  The module will cover the following topics:

  • Introduction to the Database Engine
  • Introduction to SQL Server Integration Services (SSIS)
  • Introduction to Reporting Services
  • Introduction to Analytical Services
  • Introduction to Full-Text Search
  • Introduction to Database Mail
  • Introduction to Service Broker

Module 02:  Installing SQL Server

This module will provide the course attendees with the basic understanding of installing and configuring SQL Server 2005 and SQL Server 2008.  The module will cover the following topics:

  • Understanding service accounts
  • Understanding authentication methods
  • Understanding file locations
  • Understanding default paths
  • Understanding files and file groups
  • Understanding database options
  • Understanding recovery model
  • Understanding collations

Module 03:  Understanding the SQL Server Agent

This module will provide the course attendees with the basic understanding of how to configure SQL Server jobs using the SQL Server Agent.  The module will cover the following topics:

  • Creating and scheduling jobs
  • Disabling and enabling jobs
  • Changing job step order
  • Logging Job Execution
  • Reviewing Job history

Module 04:  Basic Error Monitoring

This module will provide the course attendees with the basic understanding of SQL Server error monitoring.  The module will cover the following topics:

  • Reviewing error logs
  • Reviewing agent error logs
  • Reviewing event logs

Module 05:  Understanding Database Creation

This module will provide the course attendees with the basic understanding of how to create and configure SQL Server databases.  The module will cover the following topics:

  • Creating database tables
  • Database options
  • Altering database options
  • Dropping databases

Module 06:  Understanding Data Types

This module will provide the course attendees with the basic understanding of the data types used in SQL Server 2005 and 2008.  The module will cover the following topics:

  • SQL Server data types
  • User-defined data types

Module 07:  Understanding Database Tables

This module will provide the course attendees with the basic understanding of database table creation.  The module will cover the following topics:

  • Creating database tables
  • Altering database tables
  • Dropping database tables
  • Creating scripts to deploy changes to multiple environments
  • Managing table permissions (GRANT, DENY, REVOKE)

Module 08:  Understanding Database Views

This module will provide the course attendees with the basic understanding of database view creation.  The module will cover the following topics:

  • Creating views
  • Altering views
  • Dropping views
  • Using the WITH SCHEMABINDING option
  • Using the WITH CHECK OPTION option
  • Managing view permissions (GRANT, DENY, REVOKE)

Module 09:  Understanding Database Stored Procedures

This module will provide the course attendees with the basic understanding of database stored procedures.  The module will cover the following topics:

  • Creating stored procedures
  • Altering stored procedures
  • Dropping stored procedures
  • Managing stored procedure permissions (GRANT, DENY, REVOKE)

Module 10:  SQL Server Security 

This module will provide the course attendees with the basic understanding of how to configure SQL Server security.  The module will cover the following topics:

  • Manage Logins
    • Creating/Altering logins
    • Disabling/enabling logins
    • Password policy enforcement
    • Fixed server roles
  • Database Users and Roles
    • User mapping
    • User-defined roles
    • Fixed roles
    • Guest role
    • Public role
    • Creating and deleting user roles
  • Database Security 
    • Schemas

Module 11:  SQL Server Backups and Restores

This module will provide the course attendees with the basic understanding of how to perform SQL Server backups and restores.  The module will cover the following topics:

  • Backing Up SQL Server
    • Creating full backups
    • Creating differential backups
    • Creating transaction log backups
    • Creating compressed backups
    • Creating File and filegroup backups
    • Verifying backups
  • Restoring SQL Server
    • Performing full restores
    • Performing differential restores
    • Performing transaction log restores
    • Verifying restore

Module 12:  Database Maintenance

This module will provide the course attendees with the basic understanding of SQL Server maintenance and maintenance plans.  The module will cover the following topics:

  • Creating maintenance plans
  • Altering maintenance plans
  • Viewing maintenance plan history
  • DBCC Checks

Module 13:  Policy Management

This module will provide the course attendees with the basic understanding of using the SQL Server 2008’s Policy Management component.  The module will cover the following topics:

  • Creating a policy
  • Verifying policies
  • Scheduling a policy compliance check
  • Enforcing a policy
  • Creating a condition

Module 14: Understanding Table Indexes

This module will provide the course attendees with the basic understanding of how table indexes work and the different types of indexes available in SQL Server 2005 and SQL Server 2008.  The module will cover the following topics:

  • Understanding clustered and non-clustered indexes
  • Understanding XML indexes
  • Creating indexes
  • Altering indexes
  • Dropping indexes
  • Disabling and enabling indexes
  • Indexes with included columns
  • Creating filtered indexes
  • Rebuilding/reorganizing indexes
  • Online/offline index operations
  • Unique Indexes

Module 15:   Understanding Table Constraints

This module will provide the course attendees with the basic understanding of constraints available when creating database tables.  The module will cover the following topics:

  • PRIMARY KEY
  • FOREIGN KEY
  •  UNIQUE
  • CHECK
  • Enabling/disabling
  • NOCHECK
  • SET IDENTITY_INSERT

Module 16:  Understanding Triggers

This module will provide the course attendees with the basic understanding of both table and DDL triggers.  The module will cover the following topics:

  • Types of DML triggers
    • INSERTED
    • DELETED
    • INSTEAD OF
  • Creating DML triggers
  • Dropping DML triggers
  • Disabling DML triggers
  • Types of DDL triggers
  • Creating DDL triggers
  • Dropping DDL triggers
  • Disabling DDL triggers
  • Returning event data with DDL triggers

Module 17:  Using CLR Objects

This module will provide the course attendees with the basic understanding of CLR objects and how to implement them.  The module will cover the following topics:

  • Creating CLR objects
  • Permission sets (SAFE, UNSAFE, EXTERNAL_ACCESS)

Module 18:  Understanding SQL Server Concurrency

This module will provide the course attendees with the basic understanding of locking, blocking, and deadlocking in a SQL Server database.  The module will cover the following topics:

  • Locking
  • Blocking
  • Deadlocking

Module 19:  Introduction to Replication

This module will provide the course attendees with the basic understanding of the different types of SQL Server replication and how to implement basic replication.  The module will cover the following topics:

  • Introduction to Snapshot Replication
  • Introduction to Transactional Replication
    • Updating Subscribers
    • Peer-To-Peer
  • Introduction to Merge Replication

Module 20:  Introduction to Database Snapshots

This module will provide the course attendees with the basic understanding of database snapshots and how to use database snapshots.  The module will cover the following topics:

  • Creating database snapshots
  • Reverting from database snapshots
  • Dropping database snapshots

Module 21:  Introduction to Log Shipping

This module will provide the course attendees with the basic understanding of how to implement log shopping in a SQL Server environment.  The module will cover the following topics:

  • Setting up log shipping

Module 22:  Introduction to Database Mirroring

This module will provide the course attendees with the basic understanding of how to set up and maintain SQL Server database mirroring.  The module will cover the following topics:

  • Setting up database mirroring