Call us : (+91) 9177166122

SQL Server DBA Online Training Course

SQL Server DBA

Topic 1: Starting with SQL Server 2014/2016

  • History of SQL Server - versions

  • What’s New in SQL Server 2005& 2008 R2 and 2012 for Administrators?

  • SQL Server 2005& 2008 Service Packs

  • Editions of SQL Server

  • Tools of SQL Server

  • Differences between Enterprise and Standard editions

  • Requirements

    • Hardware

    • Software

  • Instances

    • Advantages of Instances

    • Types

      • Default Instance

      • Named Instances

  • SQL Server Services

  • Installing SQL Server 2014/2016

    • Pre installation steps

    • Installations 2014/2016. New changes in 2016 installation.

    • Viewing installation process with LOG files.

    • Adding or removing components.

    • Installing service packs.

  • Exercises


Topic 2: Installing and configuring

  • Configuration

    • Configuring various Services.

    • Startup Parameters.

    • Configuring data file and log file paths.

    • Configuring default backup folder and

    • Configuring services

    • Remote connections

    • Exercise

    • Configuring network protocols, ports.

    • Memory configuration


  • Case Study

    • Troubleshooting SQL Server installation common issues

    • Rollbacking Service Packs.

    • Best Practices

    • Exercise

Topic 3: Working with Databases


  • Working with databases.

    • System Defined databases

    • Moving system databases

    • Handling TempDB database.


  • Database Architecture.

    • Data Files

    • Log Files

    • Filegroups

    • Extents

    • Pages– types

    • Page architecture

    • Tracking free space

  • Creating Databases.

  • Adding files, filegroups.

  • Case Study

    • Recovering suspect database

    • Moving system databases

    • Troubleshooting TempDB issues

    • Log file full – how to solve the problem.

Topic 4: Implementing Security.

  • Security in SQL Server 2008 R2/ 2012

  • Security Enhancements

  • Types of Authentications.

    • Windows Authentication

      • Creating logins from windows users and groups

      • Orphan logins.

    • SQL Server Authentication

      • Creating SQL logins and testing logins



  • Setting authentication Mode

  • Security Auditing.

  • Understanding server roles.

  • Working with users.

  • Resolving orphan users.



  • Understanding database roles, custom and application roles.

  • Understanding permissions.

  • Encryption and decryption.

  • Working with certificates and schemas.

  • Security catalog views and stored procedures.

  • Case Study

    • Connecting to instance without login credentials Resolving orphan users


Topic 5: Backup and Restoration


  • Understanding Transaction Log file.

  • Understanding checkpoints& Lazy writer.

  • Truncating log file.

  • Recovery Models

    • Full

    • Bulk Logged

    • Simple

  • Setting recovery model.

  • Database Backups

    • Why we need to backups

  • Backup Types.

    • Full

    • Differential

    • Transaction Log

    • File or Filegroup

    • Copy-only, Mirrored and tail log backups.

  • Backup Devices.

  • Performing Restoration

  • Backup system databases.

  • Compressions

    • Row Compression

    • Page Compression

    • Data Partitions

    • Table Compression via T-SQL

    • Compressing Backups

  • Viewing complete details of backup process.

  • Case Study

Recovering a crashed database

Point-in-time recovery

Topic 6: Replication


  • Replication and advantages

  • New features 2008 R2/ 2012

  • Replication Entities

  • Replication Architecture.

  • Replication Agents.

  • Types of Replications

  • Configuring Replication

    • Snapshot Replication

    • Transactional Replication

    • Merge Replication

    • Peer to peer replication.

  • Configuring Oracle publication

  • Replication Topologies

  • Managing replication.

  • Monitoring and Tuning Replication.

  • Case Study

    • Troubleshooting Primary Key violation error in Transactional Replication

    • Troubleshooting T.Log file growth issues.

    • Troubleshooting out of sync/ latency issues


Topic 7: Automating Administrative Tasks.

  • Working with Database Mail.

    • Mail architecture.

    • Configuring Profiles and Accounts

    • Sending Mail

  • Configuring linked servers.

  • Implementing Automation

    • Configuring SQL Server Agent.

    • Creating Operators, Alerts, Jobs

    • Managing jobs and resolving errors.

    • Monitoring jobs.

    • Auto alert when jobs are enabled, disabled or failed.

  • Database Snapshots.

  • Reverting from Snapshot.

  • Maintenance Plans

  • Case Study

    • Configuring TempDB growth alerts

    • Configuring T.Log growth alerts


Topic 8: High Availability

  • Introduction to High Availability.

  • Working with Log Shipping.

    • Features

    • Jobs

    • Requirements to implement Log Shipping.

    • Configuring Log Shipping.

    • Monitoring Log Shipping.

    • Manually performing Fail Over.

    • Transferring logins.

    • Log shipping tables and stored procedures.

Handling out of sync issues


  • Working with Database Mirroring.

    • Advantages

    • Architecture

    • Operating Modes

    • Servers involved in Mirroring

    • Requirements for Mirroring

    • Configuring

    • Monitoring

      • Using Mirroring Monitor

      • Using System Monitor

      • Using Profiler

Using System views and SPs.

    • Mirroring Fail Over

    • Mirroring system tables and stored procedures.

    • Case Study

      • Handling suspect issues

      • How to patch mirrored instances

      • How to move mirrored database files

      • Threads created for mirroring

Topic 9: Advanced Administration Concepts


  • Maintenance plans

  • Monitoring and Tuning SQL Server

    • Performance counters setup

    • Measuring performance of server.

    • Tuning queries.

    • Tuning databases.

    • Tuning physical architecture of databases.

    • Using DTA.

  • Monitoring Tools

    • System Monitor

    • SQL Server Profiler

    • Database Engine Tuning Advisor.

    • Dynamic Management Views.

    • SQL Server and Windows Error Logs.

Troubleshooting

    • Physical server performance.

    • Connectivity to SQL Server

    • Database Performance.

  • Managing Concurrency

    • SQL Server Architecture

      • Relational Engine

      • Storage Engine

      • Buffer pool

      • Managing execution plans.

    • Locks

    • Deadlocks

    • Transaction Isolation Levels.

    • Understanding Blocking.

    • Terminating Processes.

    • Using the DAC.

    • Case Study

      • Troubleshooting slowly running queries

      • Using DTA

  • Managing Databases

    • Manage Index Fragmentation

    • Manage Statistics

    • Shrink Files

    • Performing database integrity checks by using DBCC CHECKDB

    • Index Rebuilding and Reorganizing.

  • SQL Server Up gradation.

  • Using DBCC commands.

  • Resource Governor

Topic 10: Clustering SQL Server

  • Introduction to cluster environment & features.

  • Overview of Windows Clustering.

  • How SQL Server supports clustering.

  • Requirements

Installing and configuring SQL Server clustering

  • Applying service packs and hot fixes.

  • Moving groups.

  • Adding node on a SQL Server Failover cluster.

  • Troubleshooting cluster issues.

  • Patching clustered instances

Topic 11: SQL Server 2012/2014/2016 DBA Concepts

  • Introduction to SQL Server 2012 environment

  • Always On (HA/DR) features

  • Column Store Indexes

  • Contained Databases

  • User defined server roles.

  • Database recovery advisor.

  • New/Updated DMVs (Dynamic Management Views)Overview of Windows Clustering.

  • Incremental Statistics

  • Lock priority of online operations

  • New Design for Cardinality Estimation

  • Delayed Durability

  • Enhanced AO options

  • Partition Switching and Indexing

Topic 12: Troubleshooting Scenarios Covered

  • Troubleshooting and Common Issues

  • CPU Issues

  • Memory Issues

  • IO Issues

  • Regular Wait tasks

  • Log file full Issue

  • Database Suspect issue

  • Backup failures

  • Troubleshooting Replication Issues

  • Troubleshooting Logshipping

  • Troubleshooting Mirroring

  • Troubleshooting Clustering

  • 2012 Always On Troubleshooting Issues

  • Troubleshooting Slow running Queries

  • Troubleshooting TempDB Issues