SQL DBA Training in Hyderabad

Algorithm Class – sql dba training in Hyderabad

SQL SERVER COURSE CONTENT

SQL Server DBA Success Path
Module1: In-Depth Training with Interview Tips
Module2: Advanced Scenarios with Troubleshooting
Database Designing , ITIL and Third Party Tools
Troubleshooting
Performance Tuning, Always ON

SNO TOPIC SUB TOPIC
1 Starting with SQL Server

Responsibilities of Database Administrator

Types of DBAs

History of SQL Server – versions

What’s New in SQL Server 2012, 2014 & 2016 for Administrators?

SQL Server 2012 ,2014 & 2016 Service Packs
Editions of SQL ServeTools of SQL Server

Differences between Enterprise and Standard edition
Requirements

 Hardware
 Software 

Instances
 Advantages of Instances
 Types
 Default Instance
 Named Instances
SQL Server Services
 Instance–aware Services
 Instance–unaware Services
 Start & Stopping Services
Client Server connectivity issues

 

 

2 Installing and configuring Installing SQL Server 2012,2014 & 2016.
 Pre – installation steps
 Installations
 Viewing installation process with LOG files.
 Adding or removing components.
 Installing service packs.
Configuration
 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
 Troubleshooting SQL Server installation common issues
 Rollbacking Service Packs.
 Best Practices
 Exercise
3 Working with Databases Working with databases.
 System Defined databases
 Moving system databases
 Handling TempDB database.
Database Architecture.
 Data Files
 Log Files
 Filegroups
 Extents
 Pages – types
 Page architecture
 Tracking free space
 Creating Databases.
 Adding files, file groups.
Case Study
 Recovering suspect database
 Moving system databases
 Troubleshooting TempDB issues
 Log file full – how to solve the problem.
4 Implementing Security. Security in SQL Server
Security Enhancements
 Types of Authentications.
 Windows Authentication.
Creating logins from windows users and groups.
Orphan logins.
 SQL Server Authentication
Creating SQL logins and testing logins
 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
 Resolving orphan users
5 Backup and Restoration  Understanding Transaction Log file.
 Understanding checkpoints & Lazy writer.
 Truncating log file.
 Recovery Models
Full
Bulk Logged
Simple
 Setting recovery model.
 Database Backups
 Why we need to backups
 Backup Types.
Full
Differential
Transaction Log
File or Filegroup
Copy-only, Mirrored and tail log backups.
 Backup Devices.
 Performing Restoration
 Backup system databases.
 Compressions
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.
6 Replication  Replication and advantages
 New features
 Replication Entities
 Replication Architecture.
 Replication Agents.
 Types of Replications
Snapshot Replication
Transactional Replication
Merge Replication
Peer to peer replication.
 Configuring Oracle publication
 Replication Topologies
 Managing replication.
 Monitoring and Tuning Replication.
 Configuring Replication
Case Study
 Troubleshooting Primary Key violation error in Transactional Replication.
 Troubleshooting T.Log file growth issues.
 Troubleshooting out of sync/ latency issues
7 Automating Administrative Tasks.
 Working with Database Mail.
 Mail architecture.
 Configuring Profiles and Accounts
 Sending Mail
 Configuring linked servers.
 Implementing Automation
 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
Configuring TempDB growth alerts
Configuring T.Log growth alerts
 8   High Availability Introduction to High Availability.
Working with Log Shipping.
 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.
 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
Working with Always ON .
 Advantages.
 Architecture.
 Replicas
 Servers involved in Always ON.
 Requirements for Always ON.
 Configuring
 Monitoring
Always ON Fail Over
 9  Advanced Administration

Concepts

 Maintenance plans.

 Monitoring and Tuning SQL Server
 Performance counters setup
 Measuring performance of server.
 Tuning queries.
 Tuning databases.
 Tuning physical architecture of databases.
 Using DTA.
Monitoring Tools
 System Monitor.
 SQL Server Profiler.
 Database Engine Tuning Advisor..
 Dynamic Management Views..
 SQL Server and Windows Error Logs.
 3rd part tools.
Troubleshooting
 Physical server performance.
 Connectivity to SQL Server.
 Database Performance.
 Managing Concurrency.
 SQL Server Architecture.
 Relational Engine.
 Storage Engine.
 Buffer pool
Managing execution plans.
 Locks.
 Deadlocks.
 Transaction Isolation Levels.
 Understanding Blocking.
 Terminating Processes.
 Using the DAC.
Case Study
 Troubleshooting slowly running queries
 Using DTA
Managing Databases
 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 Governor.
 Resource Pools.
 Creating a Resource Pool.
 Workload Groups.
 Creating a Workload Group.
 Classification
 Creating a Classifier Function

 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.(Theory part).
 Applying service packs and hot fixes.
 Moving groups.
 Adding node on a SQL Server Failover cluster.  Troubleshooting cluster issues.
 Patching clustered instances
 11  SQL Server 2012 , 2014 and 2016 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 Support for
 How to install and configure SQL Server.
 How to troubleshoot database corruption errors.
 How to handle high availability issues.
 How to handle performance issues.
 FAQs.