DBA

Topics to be covered in SQL database administration.

1. Introduction to SQL server

2. SQL server editions

3. Databases
   -Types of Database
   -System Databases
   -User Database

4.  T-LOG architecture
   -Transaction Log Logical and Physical Architecture
   -Checkpoint Operation
   -Managing T-log
   -Truncating and shrinking the log file

5. Memory Architecture
   -Pages
   -Extents [Uniform & Mixed]
   -Tracking Free Space on files

6. Files and File groups
   -Database files
   -Primary data file
   -Secondary data files
   -Log files

7.  Installing SQL server
   -Pre-Requisites
   -Installing SQL server 2008 R2 /2012
   -Best Practices on Installation
   -Uninstalling SQL server

8. SQL server Up-gradation
    -SQL server service packs & Hot fixes
    -In-place and side by side up-gradation.
    -Backup and Restore
    -copy wizard
    -Attach and Detach

9. Configuring SQL server
    -SQL Server configuration manager
    -Dedicated Administrator Connection
    -Configuring Database Settings
    -Configuring Memory Settings
    -Configuring Database Mail

10- Managing services
    -Starting and Stopping Services through
    -Start Up parameters
    -Starting SQL server in single user mode

11. SQL server Security
     - Logins and Users
     - Server Roles, Database roles and User mapping
     - Securables and Permissions

12. Automate administrative tasks
     - SQL server Agent
     - Creating Jobs, Alerts and Operators
     - Scheduling the Jobs
     - Creating Maintenance Plans
     - Job activity Monitor

13. Monitoring SQL server
     - Monitoring Tools
     - Job activity monitor
     - SQL Server Error Logs/Windows by log file viewer

14. Recovery Models of Database
     - Types of Recovery Models
     - Full - Bulk Logged  - Simple recovery models.

15. Backup & Restore
     - Types of Backups
     - Compressed backups
     - Restoring Modes [With Recovery, No Recovery, Read only/Standby]
     - Point in time recovery
     - Restoration.

16. Log shipping
     - Log-Shipping Architecture
     - Pre-requisites/Log-Shipping Process
     - Deploying Log Shipping
     - Monitoring Log Shipping
     - Log shipping Role changing [Fail-Over]
     - Removing Log Shipping

17. Mirroring
     - Overview of Database Mirroring
     - Operating Modes in Database Mirroring
     - Pre-Requisites for Database Mirroring
     - Deploying Database Mirroring
     - Fail-Over from Principle to Mirror
     - Database mirroring monitor

18. Clustering
     - What is a cluster and Overview of windows cluster
     - Two node cluster.and multiple node cluster
     - Active/Active and Active/Passive cluster
     - Installing Cluster on servers, Installing SQL on server.

19. Replication
     - Replication Overview
     - Replication Models (snapshot/Transactional/Merge/Updatable/Peer to Peer)
     - Replication agents
     - Configuring Distributor
     - Creating Subscriptions
     - Deploying Transactional Replication for High Availability
     - Replication types

20. Indexing and Performance tuning.
     - Index Architecture
     - How to optimally take advantage of indexes
     - Clustered & Non-Clustered indexes
     - Checking Index Fragmentation
     - Index De-fragmentation ,update Statistics and Rebuild indexes.
     - Activity Monitor, SQL Profiler, Database Tuning Advisor
     - Blocking transactions Deadlocks
     - Dynamic Management Views (DMV)

21. SQL Server AlwaysOn
     - AlwaysOn Availability Groups
     - Configuring Availability Groups
     - Monitoring Availability groups
     - Backups on Secondary
     - AlwaysOn Failover Cluster Instances
     - Online Operations