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

Files and File groups

Database Files

• Primary data files
• Secondary data files
• Log files

Database File groups [Primary & amp; User-defined]

Thread and Task Architecture

Allocating threads to CPU

Using Light weight pooling option

Thread and Fiber Execution

Hot Add CPU

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

• 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

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

• 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

Migrating SQL server

Difference between in-place & amp; Side by Side Migration/Upgradation

Advantages/Disadvantages of In-Place to Side-by-Side

Migrating database by using Back and restore method

Migrating DTS Packages to SSIS

JSP Action Elements

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

JSP Implicit objects

• Request
• response
• config
• application
• session
• pageContext
• page
• exception

Migrating SQL server

Migrating database by using Copy Database Wizard

Migrating database by using Attach and Detach Method

Migrating Logins [Fixing Orphaned Users]

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

• 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 overview
• Pre-requisites/Log-Shipping Process
• Deploying Log Shipping
• Logs hipping Role changing [Fail-Over]
• Frequently Raised Errors In Log-Shipping
• Case Study 1: How to add files to a log-shipped database
• Best Practices on Log-Shipping

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

• 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

• 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