Boston Computer School

You are here:  Home » Courses » Microsoft » SQL Server DBA 

SQL Server DBA

SQL SERVER ADMINISTRATION

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

i. Simple Recovery Model

ii. Full Recovery Model

iii. Bulk – Logged Recovery Model

Types of Backups

i. Data (Full) Backups

ii. Differential Backups

iii. Log Backups

iv. Copy-Only Backups

v. 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

i. Complete, Differential and Log Restores

ii. Point-In-Time Restores

iii. File and File Group Restores

iv. Page Restores

v. Piecemeal and Online Restores

vi. Reverting to Database Snapshots

Replication

  • Replication Fundamentals
  • Use of Replication

i. Data Warehousing

ii. Distributing and Consolidating Data

iii. Offloading Report Processing

  • Replication Components
  • Types of Replication

i. Snapshot Replication

ii. Transactional Replication

iii. Merge Replication
 

  • Configuring Replication
  • Configure Distributor