SQL Server DBA 2008R2/ 2012 Success Path
Module1: In-Depth Training with Interview Tips
Module2: Advanced Scenarios with Troubleshooting
Database Designing & ITIL
Third Party Tools
Troubleshooting
Performance Tuning
Placement Assistance
Topic 1: Starting with SQL Server
Responsibilities of Database Administrator
Types of DBAs
History of SQL Server - versions
Whats New in SQL Server 2005 & 2008 R2 and 2012 for Administrators?
SQL Server 2005 & 2008 Service Packs
Editions of SQL Server
Tools of SQL Server
Differences between Enterprise and Standard editions
Requirements
o
o
Hardware
Software
Instances
o
o
Advantages of Instances
Types
Default Instance
Named Instances
SQL Server Services
o
o
o
Instanceaware Services
Instanceunaware Services
Start & Stopping Services
Client Server connectivity issues
Topic 2: Installing and configuring
Installing SQL Server 2005 & 2008.
o
o
o
o
o
Pre installation steps
Installations
Viewing installation process with LOG files.
Adding or removing components.
Installing service packs.
1
#502, OptimizeSQL Technologies, Manjeera Square, opp Prime Hospital, Ameerpet
Cont: 040- 66777220, 8143667179, www.optimizesql.com, optimizesql@gmail.com
Configuration
o
o
o
o
o
o
o
o
Configuring various Services.
Startup Parameters.
Configuring data file and log file paths.
Memory configuration
Remote connections
Configuring network protocols, ports.
Configuring services
Configuring default backup folder and authentication in windows registry.
Case Study
o
o
o
o
Troubleshooting SQL Server installation common issues
Rollbacking Service Packs.
Best Practices
Exercise
Topic 3: Working with Databases
Working with databases.
o
o
o
Moving system databases
Handling TempDB database.
Database Architecture.
o
o
o
o
o
o
o
System Defined databases
Data Files
Log Files
Filegroups
Extents
Pages types
Page architecture
Tracking free space
Creating Databases.
Adding files, filegroups.
Case Study
o
o
o
o
Recovering suspect database
Moving system databases
Troubleshooting TempDB issues
Log file full how to solve the problem.
Topic 4: Implementing Security.
Security in SQL Server 2008 R2/ 2012
Security Enhancements
Types of Authentications.
o
o
Windows Authentication
Creating logins from windows users and groups
Orphan logins.
SQL Server Authentication
Creating SQL logins and testing logins
2
#502, OptimizeSQL Technologies, Manjeera Square, opp Prime Hospital, Ameerpet
Cont: 040- 66777220, 8143667179, www.optimizesql.com, optimizesql@gmail.com
Setting authentication Mode
Security Auditing.
Understanding server roles.
Working with users.
Resolving orphan users.
Understanding database roles, custom and application roles.
Understanding permissions.
Encryption and decryption.
Working with certificates and schemas.
Security catalog views and stored procedures.
Case Study
o
o
Connecting to instance without login credentials
Resolving orphan users
Topic 5: Backup and Restoration
Understanding Transaction Log file.
Understanding checkpoints & Lazy writer.
Truncating log file.
Recovery Models
o
o
o
Full
Bulk Logged
Simple
Setting recovery model.
Database Backups
Why we need to backups
Backup Types.
o
o
o
o
Full
Differential
Transaction Log
File or Filegroup
Copy-only, Mirrored and tail log backups.
Backup Devices.
Performing Restoration
Backup system databases.
Compressions
o
o
o
o
o
Row Compression
Page Compression
Data Partitions
Table Compression via T-SQL
Compressing Backups
Using LiteSpeed for backups.
Point-in-time recovery.
Viewing complete details of backup process.
Case Study
Recovering a crashed database.
3
#502, OptimizeSQL Technologies, Manjeera Square, opp Prime Hospital, Ameerpet
Cont: 040- 66777220, 8143667179, www.optimizesql.com, optimizesql@gmail.com
Topic 6: Replication
Replication and advantages
New features 2008 R2/ 2012
Replication Entities
Replication Architecture.
Replication Agents.
Types of Replications
Configuring Replication
o
o
o
Snapshot Replication
Transactional Replication
Merge Replication
Peer to peer replication.
Configuring Oracle publication
Replication Topologies
Managing replication.
Monitoring and Tuning Replication.
Case Study
o
o
o
Troubleshooting Primary Key violation error in Transactional Replication
Troubleshooting T.Log file growth issues.
Troubleshooting out of sync/ latency issues
Topic 7: Automating Administrative Tasks.
Working with Database Mail.
o
o
o
Mail architecture.
Configuring Profiles and Accounts
Sending Mail
Configuring linked servers.
Implementing Automation
o
o
o
o
o
Configuring SQL Server Agent.
Creating Operators, Alerts, Jobs
Managing jobs and resolving errors.
Monitoring jobs.
Auto alert when jobs are enabled, disabled or failed.
Database Snapshots.
Reverting from Snapshot.
Maintenance Plans
Case Study
o
o
Configuring TempDB growth alerts
Configuring T.Log growth alerts
Topic 8: High Availability
Introduction to High Availability.
Working with Log Shipping.
4
#502, OptimizeSQL Technologies, Manjeera Square, opp Prime Hospital, Ameerpet
Cont: 040- 66777220, 8143667179, www.optimizesql.com, optimizesql@gmail.com
o
o
o
o
o
o
o
o
o
Features
Jobs
Requirements to implement Log Shipping.
Configuring Log Shipping.
Monitoring Log Shipping.
Manually performing Fail Over.
Transferring logins.
Log shipping tables and stored procedures.
Handling out of sync issues
Working with Database Mirroring.
o
o
o
o
o
o
o
o
o
o
Advantages
Architecture
Operating Modes
Servers involved in Mirroring
Requirements for Mirroring
Configuring
Monitoring
Using Mirroring Monitor
Using System Monitor
Using Profiler
Using System views and SPs.
Mirroring Fail Over
Mirroring system tables and stored procedures.
Case Study
Handling suspect issues
How to patch mirrored instances
How to move mirrored database files
Threads created for mirroring
Topic 9: Advanced Administration Concepts
Maintenance plans
Monitoring and Tuning SQL Server
o
o
o
o
o
o
Performance counters setup
Measuring performance of server.
Tuning queries.
Tuning databases.
Tuning physical architecture of databases.
Using DTA.
Monitoring Tools
o
o
o
o
o
System Monitor
SQL Server Profiler
Database Engine Tuning Advisor.
Dynamic Management Views.
SQL Server and Windows Error Logs.
Troubleshooting
5
#502, OptimizeSQL Technologies, Manjeera Square, opp Prime Hospital, Ameerpet
Cont: 040- 66777220, 8143667179, www.optimizesql.com, optimizesql@gmail.com
o
o
o
Physical server performance.
Connectivity to SQL Server
Database Performance.
Managing Concurrency
o
o
o
o
o
o
o
SQL Server Architecture
Storage Engine
Buffer pool
Managing execution plans.
Locks
Deadlocks
Transaction Isolation Levels.
Understanding Blocking.
Terminating Processes.
Using the DAC.
Case Study
Relational Engine
Troubleshooting slowly running queries
Using DTA
Managing Databases
o
o
o
o
o
Manage Index Fragmentation
Manage Statistics
Shrink Files
Performing database integrity checks by using DBCC CHECKDB
Index Rebuilding and Reorganizing.
SQL Server Up gradation.
Using DBCC commands.
Resource Governor
Troubleshooting SQL Server
Managing Resource Utilization
Resource
Resource Pools
Creating a Resource Pool
Workload Groups
Creating a Workload Group
Classification
Creating a Classifier Function
Governor
Topic 10: Clustering SQL Server
Introduction to cluster environment & features.
Overview of Windows Clustering.
How SQL Server supports clustering.
Requirements
Installing and configuring SQL Server clustering.
Applying service packs and hot fixes.
Moving groups.
6
#502, OptimizeSQL Technologies, Manjeera Square, opp Prime Hospital, Ameerpet
Cont: 040- 66777220, 8143667179, www.optimizesql.com, optimizesql@gmail.com
Adding node on a SQL Server Failover cluster.
Troubleshooting cluster issues.
Patching clustered instances
Topic 11: SQL Server 2012 DBA Concepts
Introduction to SQL Server 2012 environment
Always On (HA/DR) features
Column Store Indexes
Contained Databases
User defined server roles.
Database recovery advisor.
New/Updated DMVs (Dynamic Management Views)Overview of Windows Clustering.
Participants benefits
After end of course the students get hands-on knowledge on
How to install and configure SQL Server.
How to troubleshoot database corruption errors.
How to handle high availability issues.
How to handle performance issues.
Good material for troubleshooting different scenarios, FAQs
Soft copy material on regularly used scripts, DBCC commands, DMVs etc
Free membership in google groups for future doubts and issues.
Free membership in
www.optimizesql.com/blog
Free placement assistance by our placement team.
Free seminars by old students and working professionals.
7
#502, OptimizeSQL Technologies, Manjeera Square, opp Prime Hospital, Ameerpet
Cont: 040- 66777220, 8143667179, www.optimizesql.com, optimizesql@gmail.com
