ONLINE TRAINING

SQL Server DBA Online Training Course

Course	SQL Server DBA
Duration	30 Day(s)
Phone/Whats-app	91 9962560044
Email	sqldbahouse@gmail.com

Topic 1: Starting with SQL Server 2014/2016

• History of SQL Server - versions
• Whats New in SQL Server 2005& 2008 R2 and 2012 and 2014, 2016 for Administrators?
• SQL Server 2005& 2008 Service Packs
• Editions of SQL Server
• Tools of SQL Server
• Differences between Enterprise and Standard editions
• Requirements
  • Hardware
  • Software
• Instances
  • Advantages of Instances
  • Types
    • Default Instance
    • Named Instances
• SQL Server Services
• Installing SQL Server 2014/2016
  • Pre installation steps
  • Installations 2014/2016. New changes in 2016 installation.
  • Viewing installation process with LOG files.
  • Adding or removing components.
  • Installing service packs.
• Exercises

Topic 2: Installing and configuring

• Configuration
  • Configuring various Services.
  • Startup Parameters.
  • Configuring data file and log file paths.
  • Configuring default backup folder and
  • Configuring services
  • Remote connections
  • Exercise
  • Configuring network protocols, ports.
  • Memory configuration

• Case Study
  • Troubleshooting SQL Server installation common issues
  • Rollbacking Service Packs.
  • Best Practices
  • Exercise

Topic 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, filegroups.
• Case Study
  • 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.
  • 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
  • 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
  • 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
• Viewing complete details of backup process.
• Case Study

Recovering a crashed database

Point-in-time recovery

Topic 6: Replication

• Replication and advantages
• New features 2008 R2/ 2012
• Replication Entities
• Replication Architecture.
• Replication Agents.
• Types of Replications
• Configuring Replication
  • Snapshot Replication
  • Transactional Replication
  • Merge Replication
  • Peer to peer replication.
• Configuring Oracle publication
• Replication Topologies
• Managing replication.
• Monitoring and Tuning Replication.
• Case Study
  • 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.
  • 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

Topic 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

Topic 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.

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

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.
• Adding node on a SQL Server Failover cluster.
• Troubleshooting cluster issues.
• Patching clustered instances

Topic 11: SQL Server 2012/2014/2016 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.
• Incremental Statistics
• Lock priority of online operations
• New Design for Cardinality Estimation
• Delayed Durability
• Enhanced AO options
• Partition Switching and Indexing

Topic 12: Troubleshooting Scenarios Covered

• Troubleshooting and Common Issues
• CPU Issues
• Memory Issues
• IO Issues
• Regular Wait tasks
• Log file full Issue
• Database Suspect issue
• Backup failures
• Troubleshooting Replication Issues
• Troubleshooting Logshipping
• Troubleshooting Mirroring
• Troubleshooting Clustering
• 2012 Always On Troubleshooting Issues
• Troubleshooting Slow running Queries
• Troubleshooting TempDB Issues