Courses Offered: SCJP SCWCD Design patterns EJB CORE JAVA AJAX Adv. Java XML STRUTS Web services SPRING HIBERNATE  

       

SQL SERVER DBA Course Details
 

Subscribe and Access : 5200+ FREE Videos and 21+ Subjects Like CRT, SoftSkills, JAVA, Hadoop, Microsoft .NET, Testing Tools etc..

Batch Date: July 8th @ 8:00PM

Faculty: Mr. Kareem

Duration : 45 Days

Venue :
DURGA SOFTWARE SOLUTIONS at Maitrivanam
Plot No : 202, IInd Floor ,
HUDA Maitrivanam,
Ameerpet, Hyderabad-500038.

Ph.No: +91 - 9246212143, 80 96 96 96 96


Syllabus:

SQL SERVER DBA

Topic 1: Starting with SQL Server

1. Responsibilities of Database Administrator
2. Types of DBAs
3. History of SQL Server -versions
4. What is new in SQL Server 2005, 2008R2, 2012, 2014, 2016 and 2017 for Administrators?
5. Tools and components of SQL Server
6. SQL Server Services

  • Instance–aware Services
  • Instance–unaware Services
  • Start & Stopping Services

7. Instances

  • Advantages of Instances
  • Types
    • Default Instance
    • Named Instances

8. Requirements

  • Hardware
  • Software

9. Installing SQL Server

  • Pre – installation steps
  • Installations
  • Viewing installation process with LOG files.
  • Adding or removing components.

10. Troubleshooting SQL Server installation common issues

Topic 2: Configuring SQL Server

1. Why configuration?

  • Advantages
  • Main Configuration Features

2. Configuration of

  • Configuring various Services.
  • Backup folders.
  • Configuring data file and log file paths.
  • Configuring ErrorLogs
  • Patching SQL Server
  • Remote connections
  • Tempdb Configurations
  • Memory configuration
  • Configuring max worker threads
  • Configuring DAC
  • Configuring service accounts

3. Case Study

  • Patching issues
  • Connectivity issues
  • How trollback service packs

Topic 3: Working with Databases

1. Working with Databases.

  • System Defined databases
  • Mving system databases
  • Handling TempDB database.

2. Database Architecture.

  • Data Files
  • Lg Files
  • Filegrups
  • Extents
  • Pages–types
  • Page architecture
  • Tracking frees pace

3. Transactin Lg file architecture and cmmn issues
4. Data file architecture.
5. Creating Databases.
6. Adding files, Filegrups.
7. Database States
8. Case Study

  • Recvering suspect database
  • Mving system databases
  • Trubleshting TempDB issues
  • Lg file full – hw t slve the prblem.
  • Rebuilding system databases

Topic 4: Implementing Security.

1. Security in SQL Server
2. Security Enhancements
3. Types of Authentications.

  • Windows Authentication
    • Creating logins from windows users and groups
    • Orphan logins.
  • SQL Server Authentication
    • Creating SQL logins and testing logins

4. Setting authentication Mode Security Auditing.
5. Understanding server roles. Working with users.
6. Resolving orphan users.
7. Working with

  • User defined roles
  • Contained databases
  • Auditing
  • Policy based management

8. Understanding database roles, custom and application roles. Understanding permissions.
9. Encryption and decryption.
10. Working with certificates and schemas.
11. Security catalog views and stored procedures.
12. Case Study

  • Connecting tinstance without login credentials
  • Resolving orphan users

Topic 5: Backup and Restoration

1. Understanding Transaction Log file.
2. Understanding checkpoints & Lazy writer. Truncating log file.
3. Recovery Models

  • Full
  • Bulk Logged
  • Simple

4. Setting recovery model.
5. Database Backups

  • Full
  • Differential
  • Transaction Log
  • File or Filegroups
  • Copy-only, Mirrored, Split, Tail Log backups

6. Performing Restoration
7. Backup system databases.
8. Compressions

  • Row Compression
  • Page Compression
  • Data Partitions
  • Table Compression via T-SQL
  • Compressing Backups

9. Viewing complete details of backup process.
10. Case Study

  • Recovering a crashed database.
    Common backup and restore issues.

Topic 6: Automating Administrative Tasks

1. Working with Database Mail.

  • Mail architecture.
  • Configuring Profiles and Accounts
  • Sending Mail

2. Configuring linked servers.
3. Implementing Automation

  • Configuring SQL Server Agent.
  • Creating Operators, Alerts, Jobs
  • Managing jobs and resolving errors.
  • Monitoring jobs.
  • Autalert when jobs are enabled, disabled or failed.
  • Database Snapshots.

4. Case Study

  • Configuring TempDB growth alerts
  • Configuring T.Log growth alerts

Topic 7: High Availability

1. Introduction to High Availability.
2. Working with Log Shipping.

  • Features
  • Log Shipping Architecture
  • 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
3. Working with Database Mirroring.
  • Advantages
  • Architecture
  • perating Mdes
  • Servers invlved in Mirrring
  • Requirements fr Mirrring
  • Cnfiguring
  • Mnitring
    • Using Mirrring Mnitr
    • Using System Mnitr
    • Using Prfiler
    • Using System views and SPs.
  • Mirrring Fail ver
  • Mirrring system tables and stred prcedures.
  • Case Study
    • Handling suspect issues
    • Hw t patch mirrred instances
    • Hw t mve mirrred database files

Topic 8: Replication

1. Replication and advantages
2. New features 2008 R2/ 2012/2014/2016
3. Replication Entities
4. Replication Architecture.
5. Replication Agents.
6. Types of Replications
7. Configuring Replication

  • Snapsht Replicatin
  • Transactinal Replicatin
  • Merge

8. Peer t peer replicatin.
9. Managing replicatin.
10. Mnitring and Tuning Replicatin
11. Case Study

  • Trubleshting Primary Key vilatin errr in Transactinal Replicatin
  • Trubleshting T.Lg file grwth issues.
  • Trubleshting ut f sync/ latency issues

Topic 9: SQL Server Performance Tuning

1. What is database maintenance and performance tuning
2. SQL Server Architecture

  • Layers f SQL Server architecture
  • Prtcl Layer
  • Relatinal engine
  • Strage Engine
  • Buffer Cache
  • What is Lck and Latch
  • SQL Server backgrund prcesses
  • Page Life Expectancy

3. Executin Plan Basics

  • Hw plans are generated
  • Estimated executin plans
  • Actual executin plans
  • Check infrmatin abut executin plans
  • When the plans are recmpiled
  • Plan life time
  • Hw t remve executin plans
  • Events causing plans t remve frm buffer cache

4. Wrking with indexes

  • Table Scan
  • Index
  • Types f Index
    • Clustered
    • NnClustered
  • Index Management
    • Index Fragmentatin
    • Index Rerganizing
    • Index Rebuilding

5. Statistics

  • Statistics basics
  • Hw the statistics are created
  • Hw t check statistics
  • What is stale statistics?
  • Hw t update
  • Incremental Statistics in SQL Server 2014
  • Patching t clear statistics bugs

6. Transactins and Lcks
7. Islatin Levels
8. Blcking and deadlcks in SQL Server
9. Tools

  • Prfiler, DTA, Activity Mnitr

10. Case Study

  • Trubleshting slwly running queries Using DTA

Topic 10: Clustering SQL Server

1. Introduction to cluster environment & features.
2. Overview of Windows Clustering.
3. How SQL Server supports clustering.
4. Requirements
5. Installing and configuring SQL Server clustering.
6. Testing Failover
7. Applying service packs and hot fixes.
8. Active Passive and Active - Active Cluster
9. Adding virtual disks
10. Troubleshooting cluster issues

Topic 11: Implementing Always On

1. Introduction to SQL Server Always On (HA/DR) features
2. Limitations of existing features
3. Always on benefits
4. Always on architecture
5. Pre Requisites to configure AO
6. Configuring Always On
7. Always On Availability Groups
8. Availability Group Failover
9. Configuring Listener
10. Monitoring AO
11. Adding databases and replicas
12. New enhancements in 2014/2016
13. Resource Governor IO enhancements
14. Regular issues and FAQs

Troubleshooting Covered in the program

1. CPU Issues
2. Memory Issues
3. IO Issues
4. Regular Wait tasks
5. Log file full Issue
6. Database Suspect issue
7. Backup failures
8. Troubleshooting Replication Issues
9. Troubleshooting Logshipping
10. Troubleshooting Mirroring
11. Troubleshooting Clustering
12. Always On Troubleshooting Issues
13. Troubleshooting Slow running Queries