pay-ms

[accordions id="207"]

SQL Server DBA

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