Email: mail@radicaltechnologies.co.inPhone: 080-42041080 +91 9611824441

MS SQL SERVER DBA

A database administrator (DBA) directs or performs all activities related to maintaining a successful database environment. Responsibilities include designing, implementing, and maintaining the database system; establishing policies and procedures pertaining to the management, security, maintenance, and use of the database management system; and training employees in database management and use. DBA is also responsible for DBA Activities.

Satisfied Learners

Our Courses

Drop A Query

Best MS SQL Server Training in Pune 

 SQL Server Training by  Certified Instructors

Duration of Training: 8 weekends

MS SQL SERVER DBA SYLLABUS

Session 1: Starting with SQL Server  

  • Responsibilities of Database Administrator
  • Types of DBA’s
  • History of SQL Server – Versions
  • What’s new in SQL Server 2012 & 2014 for Administrators?
  • SQL Server 2008 & 2012 Service Packs
  • Editions of SQL Server
  • Differences between Enterprise and Standard Editions
  • Requirements
    • Hardware
    • Software
  • Instances
    • Advantages of Instances
    • Types
      • Default Instance
      • Named Instances

Session 2: Installing and Configuring

  • Installing SQL Server 2008 & 2012
    • Pre –Installation Steps
    • Installations
    • Viewing Installation process with Log files
    • Common issues
    • Adding or removing components
    • Installing Service Packs 
  • Configuration
    • Configuration of Various Services
    • Startup Parameters
    • Configuring data files and log file paths
    • Memory Configuration
    • Remote Connections
    • Configuring network protocols, Ports
    • Configuring Services
    • Configuring default backup folder.

Session 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
    • Log file full – How to troubleshoot. 
  • Creating Databases
  • Adding files, Filegroups

Session 4: Implementing Security

  • Security in SQL Server 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
    • Understanding permissions
    • Working with certificates and schemas
    • Security catalogue views and stored procedures
    • Major Issues

Session 5: Backup and Restoration

  • Understanding Transaction Log Files
  • Understanding Checkpoints and Lazy writer
  • Truncation log file
  • Recovery ModelsSetting Recovery Model
    • Full
    • Bulk Logged
    • Simple
  • Database Backups
    • Why we need backups 
  • Backup TypesCopy-Only, Mirrored and Tail log backups
    • Full
    • Differential
    • Transaction Log
    • File or Filegroup
  • Backup Devices
  • Performing Restoration
  • Backup System Databases
  • CompressionsUsing LiteSpeed for Backups
    • Row Compression
    • Page Compression
    • Compressing Backups
  • Point-in-time Recovery
  • Viewing complete details of backup process
  • Issues related to backups

Session 6: High Availability

  • Introduction to High Availability

6.1.1  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 Issues

6.1.2 Working with Database Mirroring

  • Advantages
  • Architecture
  • Operating Modes
  • Server involved in Mirroring
  • Requirements for Mirroring
  • Configuring
  • MonitoringMirroring Fail Over
    • Using Mirroring Monitor
    • Using System Monitor
    • Using Profiler
    • Using System views and SPs
  • Mirroring System Tables and Stored Procedures
  • Major issues with Mirroring 

6.1.3  Replication

  • Replication and Advantages
  • New Features
  • Replication Entities
  • Replication Architecture
  • Replication Agents
  • Types of Replications
  • Configuring Replication
    • Snapshot Replication
    • Transactional Replication
    • Merge Replication
    • Peer to Peer replication
    • Managing Replication
    • Monitoring and Tuning Replication

Session 7: Automating Administrative Tasks

  • Working with Database MailConfiguring Linked Servers
    • Mail Architecture
    • Configuring Profiles and Accounts
    • Sending Mails
  • 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

Session 8: 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 
  • TroubleshootingLocks
    • Physical Server Performance
    • Connectivity to SQL Server
    • Database Performance
  • Managing ConcurrencyLocks
    • SQL Server Architecture
      • Relational Engine
      • Storage Engine
      • Buffer Pool
      • Managing Execution Plans
  • Dead Locks
  • Transaction Isolation Levels
  • Understanding Blocking
  • Terminating Processes
  • Using the DAC
  • Managing Databases
    • Manage Index Fragmentation
    • Manage Statistics
    • Shrink Files
    • Performing database integrity checks (DBCC CheckDB)
    • Index Rebuilding and Reorganising
    • Migrations
    • Using DBCC Commands
    • Resource Governor

Session 9: Clustering SQL Server

  • Clustering SQL Server
  • Overview of windows Clustering
  • Requirements
  • Installation and configuration of SQL Server Clustering
  • Applying Service packs and fixes
  • Moving groups
  • Adding node on a SQL Server Failover Cluster
  • Troubleshooting Cluster issues. 

Session 10: Performance Tuning

  • Blocking
  • Dead Locking
  • Index’s Rebuild & Reorganise
  • Locks Types
  • Trace Flags
  • DMV’s

Session 11: Introduction of SQL Server 2014

 

Summary

*      SQL Server Introduction

*      Versions and Editions

*      Overview of Management Tools (SQL Profiler, SSMS,SSCM, Tuning Advisor etc.)

*      SQL Server Management Studio (Jobs, SQL Agent, Database Mail, Maintenance Plan)

*      SQL Server Configuration Manager ( Services, Permissions)

*      System Databases (Details Of  Master, Model, TempDB, MSDB databases)

*      Database Operations

*      Recovery Model

*      Backup Types  (Full, Differential And Log Backups)

*      Security (Login’s, Database User, Object Level Permissions)

*      DBCC Commands

*      Disaster Recovery  – Restore Recovery Type

*      High Availabilities – Replication, Log Shipping, Database Mirroring.

*      Type of Indexes and Fragmentation Check.

*      Capacity Planning

*      Installing, Configuring  and Upgrading SQL Server Software

*      Migration of SQL SERVER (Side by Side)

*      Monitoring Tasks (Disk Space, Jobs, Backups)

*      Performance Tuning

Preparing for Interview Questions

Note : Industry Expert and with Real Time Scenarios.

 

  • Having over 12.5 Years of Experience in SQL Server Administrative and SQL Development Activities.
  • Good experience with implementing DR solutions, High Availability of databases using Database mirroring, replications, Clustering and Log Shipping.
  • Excellent backend skills in creating SQL objects like Tables, Stored Procedures, Views, Indexes, Triggers, user defined data types, link servers and Functions.
  • Experience in query optimization and performance tuning Using SQL Profiler, Execution Plan, Performance Monitor etc.
  • Users and databases security management i.e. creating users, roles, granting permissions, policies etc.
  • Good Experience in Database Designing using normalization model.
  • Experience in SQL Server upgrades from SQL Server 2000 to 2005 and 2005 to 2008.
  • Experience in creating Jobs, Alerts, SQL Mail Agent and scheduling SSIS Packages.
  • Configured Active/Active and Active/Passive Cluster with SQL Server 2012 on Windows 2012.
  • Extensive experienceCapacity Planning and sizing for SQL Database Server.
  • Having good Exposure on Team Foundation Server with visual studio 2010.