SQL Server DBA

SQL SERVER DBA ONLINE TRAINING

SQL SERVER DBA COURSE CONTENT

  • SQL SERVER ARCHITECTURE

    INTRODUCTION TO SQL SERVER 2012

  • Overview on RDBMS and Beyond Relational
  • What’s New in SQL Server 2012
  • SQL SERVER COMPONENT OVERVIEW

  • The Relational Engine
  • The Command Parser
  • The Query Optimizer
  • The SQL Manager
  • The Database Manager
  • The Query Executor
  • The Storage Engine
  • PAGES & EXTENTS

  • Pages
  • Extents [Uniform & Mixed]
  • Managing Extent Allocations
  • Tracking Free Space
  • FILES AND FILE GROUPS

  • Database Files
    • Primary data files
    • Secondary data files
    • Log files
  • Database File groups [Primary & User-defined]
  • MEMORY ARCHITECTURE

  • 32-bit Vs 64-bit Architecture
  • Dynamic Memory Management
  • Effects of min and max server memory
  • Buffer Management
  • Using AWE
  • The Buffer Pool and the Data Cache
  • Checkpoints
  • INSTALLING, UPGRADING, CONFIGURATION, MANAGING SERVICES AND MIGRATION

    SQL SERVER 2012 INSTALLATION

  • Planning the System/Pre-Requisites
  • Installing SQL server 2008 R2 /2012
  • Installing Analysis Services
  • Installing & Configuring Reporting Services
  • Best Practices on Installation
  • Uninstalling SQL server
  • Common Installation Issues
  • Case study 1: Moving system databases
  • Case study 2: Troubleshooting on a Failed Installationtion
  • UPGRADING TO SQL SERVER 2008 R2/2012

  • Upgrading the server by applying service packs
  • Upgrading the server by applying Hot fixes
  • In-Place Up gradation from SQL server 2008 R2 to 2012
  • Pre-Upgrade Checks
  • Upgrade advisor
  • Best Practices to follow while upgrading
  • CONFIGURING SQL SERVER

  • Configuring Network Protocols from SQL Server configuration manager
  • Configuring features by using SQL Sever surface area configuration manager
  • Configuring other settings through SP_Configure
  • Dedicated Administrator Connection
  • Connecting to DAC
  • Configuring Server and Agent property settings
  • Configuring Database Settings
  • Configuring Memory Settings
  • Configuring Database Mail
  • Configuring Alert system in SQL server agent
  • Tempdb configuration
  • Best Practices on configuration tempdb & Database settings
  • MANAGING SERVICES

  • Starting and Stopping Services through
    • Configuration manager
    • Net Command
    • Command Prompt (sqlsrvr.exe)
  • Start Up parameters
  • Starting SQL server in single user mode
  • Starting SQL server with minimal configuration
  • Case study 1 : Tempdb failure issue
  • Case study 2 : Rebuilding system databases
  • Case study 3 : Changing SQL Server collation
  • MIGRATING SQL SERVER

  • Side-By- Side Migration Techniques
  • Difference between in-place & Side by Side Migration/Upgradation
  • Advantages/Disadvantages of In-Place to Side-by-Side
  • Migrating Databases
    • Migration by using Attach and Detach Method
    • Migration by using Back and restore method
    • Migration by using Copy Database Wizard
    • c
      • Migrating Logins [Fixing Orphaned Users]
      • Migrating Jobs
      • Import & Export
      • Migrating jobs & logins by using SSIS
      • SECURITY, AUTOMATION & MONITORING

        SECURITY

      • Security Principles
      • Server Roles
      • Server and Database Principles
      • Database Roles
      • Creating Logins and mapping Users to databases
      • Creating credentials
      • Creating Schemas
      • Server & Database Securable
      • Granting to Object level Permissions
      • Case study 1: How to Recover “SA” password when you forget it.
      • Case study 2: How to grant execute permissions to a single stored procedure
      • Case study 3: How to grant execute permissions on all stored procedures
      • Best Practices on security
      • AUTOMATING ADMINISTRATIVE TASKS

      • About SQL server Agent
      • Creating Jobs, Alerts and Operators
      • Scheduling the Jobs
      • Creating Maintenance Plans
      • Working with Job activity Monitor
      • Resolving failure Jobs
      • Best practices on job maintenance
      • MONITORING SQL SERVER

      • The Goal of Monitoring
      • Choosing the Appropriate Monitoring Tools
      • Monitoring Job activities by job activity monitor
      • Monitoring SQL Server process by server activity monitor
      • Monitoring SQL Server Error Logs/Windows by log file viewer
      • Best Practices on Monitoring
      • HIGH AVAILABILITY & REPLICATION

        T-LOG ARCHITECTURE

      • Transaction Log Logical Architecture
      • Transaction Log Physical Architecture
      • Checkpoint Operation
      • Write-Ahead Transaction Log
      • Managing T-log
      • Truncating and shrinking the log file
      • Managing T-Log issues by using DBCC commands
      • BACKUP & RESTORE:

      • Recovery Models [Simple, Bulk-Logged & Full]
      • How Backup Works
      • Types of backups
        • Full backup
        • Diff backup
        • T-log backup
        • Copy Only
        • Mirror
        • Tail-Log
        • Compressed backups
      • Restoring Modes [With Recovery, No Recovery, Read only/Standby]
      • Performing Restore (point-in-time recovery)
      • Disaster Recovery Planning
      • Case study on developing and executing a Backup Plan
      • Case study 2: How to restore a suspect database
      • Case study 3: How to recover the database without having ldf file
      • Case study 4: Recovering crashed SQL Server
      • Resolving Backup failures in Real time scenarios
      • Best Practices on Backup & Recovery
      • LOG SHIPPING

      • Log-Shipping Architecture
      • Building DRS for log-shipping
      • Pre-requisites/Log-Shipping Process
      • Deploying Log Shipping
      • Working with Log Shipping Monitor
      • Logs hipping Role changing [Fail-Over]
      • Removing Log Shipping
      • Frequently Raised Errors In Log-Shipping
      • Case study: How to add files to a log-shipped database
      • Best Practices on Log-Shipping
      • DATABASE MIRRORING

      • Overview of Database Mirroring
      • Operating Modes in Database Mirroring
      • Pre-Requisites for Database Mirroring
      • Deploying Database Mirroring
      • Fail-Over from Principle to Mirror
      • Working with Database mirroring monitor
      • Advantages & Disadvantages of database mirroring
      • Database Snapshots
      • Using Database Snapshots for reporting purposes.
      • Case study on moving mirrored files
      • Best practices on Mirroring
      • REPLICATION

      • Replication Overview
      • Replication Models (snapshot/Transactional/Merge/Updatable/Peer to Peer)
      • Replication agents
      • Configuring Distributor
      • Deploying Transactional Replication for High Availability
      • Creating Subscriptions [Homogeneous / heterogeneous]
      • Monitoring Replication by using replication monitor
      • Scripting Replication
      • Best Practices on Replication
      • Case study: Configuring peer to peer replication
      • SQL SERVER CLUSTERING

        SERVER CLUSTERING CONCEPTS

      • What is a cluster and Overview of windows cluster
      • Server cluster technologies
        • Server clusters
        • NLB clusters [Network load balancing]
      • Basic architecture of server clusters
      • How cluster works
      • Types of quorums
      • Basic elements of a 2-Node cluster with single quorum.
      • INSTALLING SQL SERVER 2012 FAIL-OVER CLUSTERING

      • Pre-SQL Server Installation Tasks.
      • Configure SQL Server-Related Service Accounts and Service Account Security
      • Stop Unnecessary Processes or Services
      • Check for Pending Reboots
      • Install SQL Server Setup Support Files
      • SQL Server 2012 Setup
      • Install the First Node
      • Add Nodes to the Instance
      • Perform Post installation Tasks
      • Verify the Configuration
      • Remove Empty Cluster Disk Resource Groups
      • Set the Preferred Node Order for Failover
      • Configure a Static TCP/IP Port for the SQL Server Instance
      • ADMINISTERING A SQL SERVER 2012 FAILOVER CLUSTER

      • Install SQL Server Service Packs, Patches, and Hot fixes
      • Introducing Failover Cluster Management
      • Monitoring the Cluster Nodes
      • Destroying a Cluster- Using Failover Cluster Management
      • Clustered SQL Server Administration
      • Fail over groups between the nodes
      • Automatic failover & Failback
      • Uninstalling a Failover Clustering Instance
      • Best Practices on Clustering
      • Case study 1: Changing the Network Name of a SQL Server Failover Cluster
      • Case Study 2: Changing the Service Accounts for a SQL Server Failover Cluster
      • Case Study 3: Changing the IP Address of a SQL Server Failover Cluster
      • PERFORMANCE TUNING & INDEXING

        INDEXING

      • Index Architecture
      • How to optimally take advantage of indexes
      • Clustered & Non-Clustered indexes
      • Index Fragmentation
      • Index Defragmentation options\update Statistics
      • How to determine fragmentation
      • Best Practices on Indexing
      • PERFORMANCE TUNING

      • Factors That Impact Performance
      • Tools used Activity Monitor, SQL Profiler, Database Tuning Advisor
      • Working with Activity Monitor
      • Blocking [SP_Who2]
      • SQL Profiler [How to capture events data by using Profiler]
      • Deadlocks and deadlock chain detection.
      • Analyzing the data by using DTA [Database Tuning Advisor]
      • Performance Monitor [System Monitor]
      • Dynamic Management Views (DMV)
      • Best Practices on Performance Tuning
      • Case Study A: Performance Counters Setup-Collect-Analyze
      • Case Study B: Performance Counters- Thresholds
      • SQL SERVER 2012 ENGINE/HA ENHANCEMENTS

        SQL SERVER 2012 EDITIONS AND ENGINE ENHANCEMENTS

      • Principal Editions
      • Specialized Editions
      • Breadth Editions
      • 2008 R2 FEATURE AT A GLANCE:

      • Policy Based management
      • Resource Governor
      • Central management servers
      • Change data capture
      • Compression
      • 2012 SECURITY ENHANCEMENTS

      • Default Schema for Groups
      • User-defined server roles
      • Audit Enhancements
      • User-defined audit event
      • Database authentication enhancements
      • Enabling contained databases
      • Creating users
      • New permissions
      • 2012 HIGH-AVAILABILITY AND DISASTER-RECOVERY ENHANCEMENTS

      • SQL Server AlwaysOn
      • AlwaysOn Availability Groups
      • Understanding Concepts and Terminology
      • Configuring Availability Groups
      • Monitoring Availability groups
      • Backups on Secondary
      • AlwaysOn Failover Cluster Instances
      • Support for Server Message Block
      • Database Recovery Advisor
      • Online Operations
      • 2012 HIGH AVAILABILITY: INTEROPERABILITY AND COEXISTENCE

      • Database Mirroring and Failover Clustering
      • Database Mirroring and Log Shipping
      • Database Mirroring and Database Snapshots
      • Replication and Log Shipping
      • Replication and Database Mirroring
      • 2012 PERFORMANCE AND SCALABILITY

      • Columnstore Index Overview
      • Columnstore Index Fundamentals and Architecture
      • New DMV’s and DMF’s
      • CORPORATE AWARENESS AND HANDLING THE ENVIRONMENT

      • Defining the process
      • How they implement the security
      • How they implement process by using CRM tools
      • Knowledge on ticketing tools
      • Succeeding the interviews.