• Installing, Upgrading, Configuration,
• Managing Services and Migration

• Security, Automation & Monitoring

• Architecture &Internals of SQL Server Engine

• High Availability ,Replication, Clustering, AOAG

• Indexing &Performance Tuning

• Introduction to SQL Server 2005,2008,2012,2014,2016,2017,2019.
• Overview on RDBMS and Beyond Relational
• System databases
• What’s New in SQL Server
• DBA Check List

• Components of SQL Server
• Protocols
• Tabular Data Stream
• The Relational Engine
• The Storage Engine
• SQL OS

• Pages & Extents
• Pages
• Extents [Uniform & Mixed]
• Managing Extent Allocations

• Files and File groups
• Database Files
  1. Primary data files
  2. Secondary data files
  3. 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

• Allocating threads to CPU
• Using Light weight pooling option
• Thread and Fiber Execution
• Hot Add CPU

• Installing, Upgrading, Configuration, Managing services and Migration

• SQL server Installation
• Installing SQL server
• Pre-Requisites
• Installing SQL server
• Best Practices on Installation

• Upgrading to SQL server
• Upgrading the server by applying service packs
• Upgrade advisor
• In-Place Up gradation from SQL server 2005 to 2008
• Best Practices to follow while upgrading

• Configuring SQL Server
• Configuring Network Protocols from SQL Server configuration manager
• Configuring features by using SQL Server surface area configuration manager
• Dedicated Administrator Connection [DAC]
• Using DAC with SQL Server Management Studio
• Configuring through sp_confgiure
• Configuring Database Mail
• Best Practices on configuration/Database settings

• Managing services
• Starting and Stopping Services through
  1. Configuration manager
  2. Net Command
  3. Command Prompt [sqlsrvr.exe]
• Start Up parameters
• Starting SQL server in single user mode
• Starting SQL server with minimal configuration

• Difference between in-place & Side by Side Migration/Upgradation
• Advantages/Disadvantages of In-Place to Side-by-Side
• Migrating database by using Back and restore method
• Migrating database by using Copy Database Wizard
• Migrating database by using Attach and Detach Method
• Migrating Logins [Fixing Orphaned Users]
• Migrating DTS Packages to SSIS
• Import & Export

• Security
• Security Architecture
• Fixed Server Roles
• Creating Logins
• Creating credentials
• Fixed Database Roles
• Database users
• Permissions

• Automating Administrative Tasks
• About SQL server Agent
• Creating Jobs, Alerts and Operators
• Creating Maintenance Plans
• Best Practices on Job maintenance

• Monitoring SQL Server
• Choosing the Appropriate Monitoring Tools
• Log File Viewer
• Activity Monitor
• Best Practices on Monitoring

• High availability and Replication

• T-Log Architecture
• Transaction Log Logical Architecture
• Transaction Log Physical Architecture
• Checkpoint Operation
• Write-Ahead Transaction Log
• Transaction log truncation
• How transaction log truncation works
• Shrinking the log file

• Backup & Restore:
• How Backup Works
• Recovery Models
• Types of backups
• Recovery states

• Log Shipping

1. Log shipping overview
2. Pre-requisites/Log-Shipping Process
3. Deploying Log Shipping
4. Logs hipping Role changing [Fail-Over]
5. Frequently Raised Errors In Log-Shipping
6. Case Study 1: How to add files to a log-shipped database
7. Best Practices on Log-Shipping

• • Database Mirroring
  • Overview of Database Mirroring
  • Operating Modes in Database Mirroring
  • Pre-Requisites for Database Mirroring
  • Deploying Database Mirroring
  • Advantages & Disadvantages of database mirroring
  • Mirroring Vs Clustering
  • Database Snapshots
  • Creating database snapshot
  • Best practices on Mirroring

• Replication
• Replication terminology
• Configuring distributor
• Creating publication
• Creating subscriptions
• Peer to peer replication
• Best Practices on Replication

• Clustering SQL server
• What is clustering
• What are types of clustering
• How does clustering work
• Clustering SQL server
• Getting Information on SQL Server Cluster
• Installing SQL Server 2008 cluster
• Adding a node on a SQL Server 2008 Failover Cluster
• Applying patches on a SQL Server 2008 cluster
• Best Practices on clustering

• Indexing
• Table Organization
• Clustered Tables, Heaps, and Indexes
• Heap Structures
• Clustered Index Structures
• Non clustered Index Structures
• Scan and Seek
• Index Fragmentation
• How to determine fragmentation?
• Best practices on indexing

• Performance Tuning
• Factors That Impact Performance
• Transactions and Isolation Levels
• SQL Server Configuration
• Memory-Related Configuration Options
• Database Tuning Advisor – DTA
• Best Practices on Performance Tuning