Sql DBA concepts

CORE DBA CONCEPTS of SQL SERVER 

The History of SQL Server and Relational Databases
Relational database history and SQL Server and ANSI Standards
Relation between Application and Database

Overview of Windows Concepts
Role of Windows Operating System in SQL Server Administration
Windows Operating System Basics
Overview of Built-in accounts, Service accounts
Overview of Users and Groups
Overview of Services

Overview of Network Concepts
Role of Network in SQL Server Administration
Basics of Network Concepts, Network drives
Introduction to Work Group, Domain
Introduction to Active Directory Services (ADS)
Introduction to Domain Controller (DC)
Introduction to Domain Naming Server (DNS)
Introduction to Dynamic Host Configuration Protocol (DHCP)

SQL Server Editions
Different Editions of SQL Server and their features
Tools, Utilities and Components of SQL Server
Choosing Appropriate SQL Server Edition
Licensing issues with SQL Server

Installing and Configuring SQL Server
Pre-installation steps and Installation SQL Server
Configuring the run-time SQL Server system
Patching SQL Server - Service Packs, Hot fixes
Finding Right Service Pack or Hot Fix to be installed
Problems and precautions involved in SQL Server Patching
Upgradation from SQL server 2000/2005 to 2008 and Migration



SQL Server Databases
System Databases Master, MSDB, Model, Temp and Resource
Creating User Databases

SQL Server Data Storage Management
SQL Server File Management Architecture
Creating Database Files
Creating SQL Server Log Files
Using SQL Server File groups
Moving Files or File Groups

SQL Server Security Management
Windows Security Management for the server
Windows Authentication Model
SQL Server Rights and Role Management
SQL Server Authentication and Mixed Mode Authentication
Logins
Mapping roles and databases to logins

SQL Server Backup & Recovery
Overview of SQL Server Backup and Recovery
Full Backups, Incremental Backups and Log Backups
Overview of Backupset, Backupmediaset, Backupmediafamily
Recovery Models in SQL Server
Restoring a SQL Server Database
Difference between Restore and Recovery
Performing Recovery
Backup and Restore through third party tools like Redgate

Overview of Testing, Development, Staging and Production Server Environments
Designing Production server from Test Server and Vice versa
Naming Convention for servers(Compliant to Industry Standards)

SQL Architecture and Memory
Physical Database Architecture
Relational (Logical) Database Architecture
Memory Management

Physical Database Engine Architecture
Pages and Extents
Physical Database Files and Filegroups
Space Allocation and Reuse
Table and Index Architecture

Relational Database Engine Architecture
Query Process Architecture
Memory Architecture
Managing Memory for Large Databases
Thread and Task Architecture
Understanding Non-Uniform Memory Access (NUMA)
Buffer Management
Distributed Query Architecture

Management
Policy Management
Configuring Database Mail
SQL Server Logs
Distribution Transaction Coordinator
Data Collection and Resource Governor (In 2008 only)

SQL Server Agent
Jobs and Job Activity Monitor
Alerts
Operators
Proxies
Error Logs

SQL Server Job Scheduling
Common Database Scheduled Jobs
Setting SQL Server and server alerts thresholds
Creating Customized Error Conditions
Scheduling multi-step job streams
Troubleshooting of failed Jobs

Maintenance Plans
Overview of Maintenance Plan
Tasks in Maintenance Plans
Creation of Maintenance Plan
Monitoring Maintenance Plan
Troubleshooting of failed Jobs created by Maintenance Plan

DBCC Commands
Introduction to Database Console Commands
Maintenance DBCC Commands
Informational DBCC Commands
Validation DBCC Commands
Miscellaneous DBCC Commands

Metadata
Understanding Metadata and its importance
System Tables and Dynamic Management Views(DMV), System Stored Procedures
Querying System Tables, DMV’s and Stored Procedures to obtain Metadata
Role of Master and MSDB in maintaining Metadata

SQL Server Configuration Manager (SSCM)
Managing Services with SQL Server
Starting and Stopping Instance from SSCM
Starting and Stopping SQL Agent from SSCM
Configuring Protocols, Ports and Aliases in SSCM
Introduction to SSIS and DTS
Using DTS to export and Import SQL Server Table data


HIGH AVAILABILITY
Overview of Disaster Recovery and High Availability solutions
Disaster types
Recovery solutions
Standby database overview
High Availability

SQL Server Database Replication
Replication Components and Replication Terminology 
Replication Topologies – Central Publisher, Central Publisher with Remote Distributor, Central 
Subscriber, Central Distributor, Publishing Subscriber
Types of Replication – Snapshot Replication, Merge Replication and Transactional Replication,
Peer-Peer Replication
Configuring Replication – Merge Replication , Transactional Replication, Snapshot Replication

Log Shipping
Log Shipping Overview
Log Shipping Deployment and Administration
Configuring Log Shipping using Management Studio and T-SQL
Monitoring Log Shipping

Database Mirroring
Important:
Introduction
Database Mirroring Overview
Database Mirroring Dynamics
Database Mirroring Availability Scenarios
Implementing Database Mirroring
Database Mirroring and High Availability Technologies
Conclusion

SQL Server Clustering
Overview of Clustering
Hardware and Software Requirement for Clustering.
Installing and Configuring VMWARE for building virtual environment
Installing Windows 2008 clustered environment
Installing SQL Server Active Passive (Cluster) on Windows 2008 clustered environment
Adding a node to existing SQL Server cluster
How to patch SQL Server Clustered Environment by applying SP1 on SQL Server 2008 cluster instance.
What is SQL Server Active Active Clustering
Installing SQL Server Active Active Clustering
Installing Analysis Services (SSAS) on a clustered environment
Installing / Configuring Integration Services (SSIS) on a clustered environment
Remove a node from a running clustered instance
Un-installing SQL Server Cluster

PERFORMANCE TUNING


SQL Architecture and Memory
Physical Database Architecture
Relational (Logical) Database Architecture
Memory Management

Table and Index Structure
Partitioning Tables
Partitioning Index
Clustered Index
Table Scan and Index Scan
Index Seek vs. Index Scan

Locking and Concurrency
Locking Mechanism
Types of Locks
Isolation Levels in SQL Server
Live Lock, Dead Lock and Blocking detection and resolution
Killing processes at OS level

Query Optimization and Programming Efficiency
T-SQL Tuning
Query execution Plan
Estimated Execution Plan

SQL Server performance counters
Understanding different Performance Counters
Processor:% Processor Time
PhysicalDisk:Avg.Disk Queue Length
PhysicalDisk: Disk Read Bytes/sec and Physical Disk: Disk Write Bytes/sec

Server Performance and Activity Monitoring
Perform monitoring tasks with Windows tools
Create SQL Server database alerts with Windows tools
Perform monitoring tasks with SQL Server Management Studio
Perform monitoring tasks with SQL Trace by using Transact-SQL stored procedures
Create and modify traces by using SQL Server Profiler
Start, pause, and stop traces by using SQL Server Profiler
Open traces and configure how traces are displayed by using SQL Server Profiler
Replay Traces by using SQL Server Profiler
Create, modify, and use trace templates by using SQL Server Profiler
Use SQL Server Profiler traces to collect and monitor server performance



Database Engine Tuning Advisor
Create Workloads
Tune a Database
View Tuning Output
Implement Tuning Recommendations
Create XML Input Files
Perform Exploratory Analysis
Review, Evaluate, and Clone Tuning Sessions

Performance Tools and Monitoring
SP_TRACE_SETFILTER (T-SQL) for tracking engine process events
System Monitor for tracking resource usage
Activity Monitor in SSMS to display Processes running on an instance, Blocked Processes,Locks,User activity
System Stored Procedures
DBCC(T-SQL)
Built-in-Functions(T-SQL)
Trace Flags(T-SQL)
SQL Server Profiler
Resource Governor
Data Collection

SQL Server performance tips
Proactive Actions that Ensure Optimized SQL Server Performance
Establishing a Smart SQL Server Monitoring Plan