MS SQL Server Database Administration

Faculty profile:

Real time professionals with 10+ years of experience.

Duration:

40 Hours

Audience:

Database Developers.

Software needed on student PC:

 Windows OS on laptop or desktop and MS SQL Server

 Course Objectives

  • SQL Server Architecture, Managing Physical structure of the DB
  • Server and Database configurations
  • RAID and Capacity Planning
  • ACID and Transaction Management
  • SQL Server Objects, Partitioning
  • Backup, Recovery and Replication
  • User and Security Management

 

Course Topics

 Introduction to Microsoft SQL Server

  • What is a Database?
  • Why to use database?
  • What is RDBMS?
  • Types of RDBMS
  • Advantages of SQL Server
  • Requirements and scope of SQL Server

Roles and Responsibilities of DBA

  • What is Administration?
  • Roles of DBA
  • Licensing and Pricing of SQL Server

System Design and Architecture

  • RAID Configuration
  • Capacity Planning
  • Installation
  • Upgrades and Backward Compatibility

SQL Server Database Design

  • Logical and Physical architecture of database
  • Creating Database
  • Modifying structure of Database
  • Files and File Groups
  • Data Placement and Audit Placement

SQL Server Objects

  • Data Types
  • Tables
  • Constraints
  • Views
  • Indexes and performance Considerations
  • Triggers
  • Stored Procedures
  • User Defined Functions
  • Cursors
  • Synonyms

Transactions

  • ACID Properties
  • Commit Modes and Rollbacks
  • Savepoints
  • Transaction Locks and Locking Mechanism
  • Isolation Levels
  • Blocking and Deadlocks

Database Snapshots

  • What is a DB Snapshot
  • Why we use Snapshots
  • How to design Snapshots
  • Advantages and disadvantages
  • Precautions when designing Snapshots

SQL Server Partitions

  • Partitioning Fundamentals
  • Designing Partitions
  • Creating Partitions
  • Viewing Partition Information
  • Maintaining Partitions

Backup Fundamentals

  • System Failures and need of Backups
  • Use of Transaction Log
  • Microsoft SQL Server Automatic Recovery
  • Recovery Models
  • Simple Recovery Model
  • Full Recovery Model
  • Bulk – Logged Recovery Model

Types of Backups

  • Data (Full) Backups
  • Differential Backups
  • Log Backups
  • Copy-Only Backups
  • Full-Text Catalog Backups
  • Media Devices and Media Sets
  • Overview of Backup History Tables
  • Real world Backup Strategy

Restoring Data

  • Restore and Recovery Concepts
  • Restoring Data from Backups
  • Complete, Differential and Log Restores
  • Point-In-Time Restores
  • File and File Group Restores
  • Page Restores
  • Piecemeal and Online Restores
  • Reverting to Database Snapshots

Replication

  • Replication Fundamentals
  • Use of Replication
  • Data Warehousing
  • Distributing and Consolidating Data
  • Offloading Report Processing
  • Replication Components
  • Types of Replication
  • Snapshot Replication
  • Transactional Replication
  • Merge Replication
  • Configuring Replication
  • Configure Distributor