SQL Server DBA Topics

Please follow the below SQL Server DBA tasks and all these tasks will be described later one after another..!

Module 1:  Starting with SQL Server

• Responsibilities of Database Administrator
• History of SQL Server
• Editions of SQL Server
• Differences between Standard and Enterprise editions
• Instances types in SQL Server
• Default Instance
• Named Instance
• SQL Server Services and Tools

Module 2:  Installing SQL Server

• Pre-requisites
• Installation
• Post Installation verification Session

Module 3:  Functioning of Databases

•  Databases
• Types of Databases and Brief explanation
• System Databases
• User Database
• Database Architecture
• Pages
• Extents
• File groups
• Database
• Creating Database
• Altering Database properties
• Adding Files
• Moving and renaming  the database files
• Transaction Log File Architecture
• Real time Scenario

Module 4:  Security

• Authentication Types in SQL Server
• Types of Logins
• Windows Logins
• SQL Logins
• Creating Users and Logins
• Server roles
• Password policy
• Understanding Database and Server Roles
• Permissions
• Working on access level issues
• Orphan users Finding and Fixing
• Important Queries

Module 5:  Database Backups and Restoration

• Database Backups
• Why we need backups
• Types of Backup
• Full Backup
• Differential Backup
• Transaction Log Backup
• Copy-only, Mirrored, Split and Tail log Backups
• Differences between backups
• Backup Strategy
• Understanding how the data moving from Log to Data File, CHECKPOINT
• Monitoring the space usage of Log File and fixing
• Checking the backup files VALID or CORRUPTED
• Backup storage tables
• Important Queries
• Performing  Restorations
• Types of Restoration
• Attach and Detach
• Shrinking database files

Module 6:  Recovery Models of Database

• Types of Recovery Models
• Full
• Bulk Logged
• Simple
• Differences between Recovery Models
• Setting Recovery Models according to the Scenarios with examples

Module 7:  Automation of SQL SERVER

• Working with SQL SERVER Agent
• Creating Jobs
• Managing Jobs and Resolving issues
• Monitoring Jobs
• Monitoring Tables

 HIGH AVAILABILITY CONCEPTS

Module 8:  Log Shipping

• Log Shipping
• Working with Log Shipping
• Terminology
• Pre-requisites to implement Log Shipping
• Different modes of Log Shipping
• Configuring Log Shipping
• Monitoring
• Manually Performing Failover
• Log Shipping Tables
• Discussing Real Time Scenarios

Module 9:  Database Mirroring

• Working with Database Mirroring
• Terminology
• Pre-requisites to implement Mirroring
• Pre check mirroring ports ENABLED or DISABLED
• Architecture
• Operating Modes
• Differences between Modes
• Port numbers involved in Mirroring
• Requirements for Mirroring
• Configuring Mirroring
• Mirroring failover
• Issues on Mirroring

Module 10:  Replication

• Working with Replication
• Terminology
• Pre-requisites to implement to Replication
• Types of Replications
• DISTRIBUTOR Database
• Agents involved in Replication
• Configuring Replication
• Resolving Issues

Module 11: Clustering

• Introduction about clustering
• Clustering Architecture
• Importance of IP’s involved in Clustering
• SAN importance
• Drive dependency
• Nodes in Clustering

Monitoring Tools

• System Monitor
• SQL Server Profiler
• SQL Server and Windows Error Logs

Database Performance Tanning Tips

• Indexes
• Types of indexes
• Fragmentation in Indexes
• Types of Fragmentation
• Database Tuning Adviser
• Deadlocks
• Blocking
• Terminating Processes

Managing the SQL Server and Databases

• Starting SQL Server in Single user mode
• Changing the Default path of Database files
• Modifying the Logical and Physical names of database files 

Thanks for following this blog. Please comment on this post if have any questions and suggestions.