Professional Training in SQL Server Administration (SQL DBA)

• Responsibilities of Database Administrator  
• History of SQL Server
• Editions of SQL Server
• Differences between Standard and Enterprise editions
• Instance types in SQL Server  

               Default Instance 

               Named Instance 

• SQL Server Services and Tools 

• Pre-requisites
• Installation
• Post Installation verification Session 

• Different Types of Objects in RDBMS databases
• What is the purpose of Table object?
• Understand Different terminologies like Columns, Rows, Data & Attributes
• SQL Commands
• Different data types in SQL
• DDL - Data Definition Language (CREATE, ALTER & DROP)
• DML - Data Manipulation Language (INSERT, UPDATE & DELETE)
• TCL - Transaction Control Language (BEGIN TRAN, COMMIT, ROLLBACK & SAVEPOINT)
• DCL - Data Control Language (GRANT & REVOKE)
• DRL - Data Retrieval Language (SELECT)

• Databases  
• Creating database
• Altering database properties  

                  Adding Files  

                  Moving and renaming the database files

• Types of Databases and brief explanation  

                  System Databases  

                  User Database 

• Database Architecture  

                  Pages  

                  Extents  

                  Files

                  File groups  

• Transaction Log File Architecture  

• Authentication Types in SQL Server 
• Types of Logins  

                  Windows Logins 

                  SQL Logins

• Creating Users and Logins  
• Server roles
• Password policy
• Understanding Database and Server Roles 
• Grant and revoke permissions
• Working on access level issues
• Find orphan users and fixing them
• Important queries related to verify user's access

•  Database Backups

                  Why we need backups

• Types of Backups  

                  Full Backup   Differential Backup   Transaction Log Backup

• Copy-only, Mirrored, Split and Tail log Backups 
• Differences between backups
• Backup Strategy
• CHECKPOINT 
• Monitor and fix the space f transaction log files
• Checking the backup files VALID or CORRUPTED
• Backup storage tables
• Important queries to check database backups
• Performing database restores  

                  Types of Restoration  

                  Attach and Detach

• Shrinking database files 

• Types of Recovery Models
• Full
• Bulk Logged
• Simple
• Differences between Recovery Models
• Setting Recovery Models according to the Scenarios with examples

• Working with SQL SERVER Agent
• Creating Jobs
• Managing Jobs and Resolving issues
• Monitoring Jobs
• Monitoring Tables

• Log shipping
• Understand the key terms in disaster recovery
• Recovery time objective
• Recovery point objective
• What is a data center?
• Primary and secondary data centers
• Understand the disaster recovery solutions
• Working with Log shipping
• Log shipping terminology
• Pre-requisites to implement Log shipping
• Different modes of Log shipping
• Configuring Log shipping
• Monitoring
• Manually performing a failover
• Log shipping Tables
• Discussing Real Time Scenarios

• Working with Database Mirroring
• Terminology
• Pre-requisites to implement Mirroring
• Pre check mirroring ports ENABLED or DISABLED
• Architecture
• Operating Modes
• Differences between Modes 
• Port numbers involved in Mirroring
• Requirements for Mirroring
• Configuring Mirroring
• Mirroring failover
• Issues on Mirroring 
• Limitations and disadvantages of database mirroring

• Replication
• Pre-requisites to implement to Replication
• Types of Replications
• Snapshot publication
• Transactional publication
• Peer to peer publication
• Merge publication
• Pull subscriptions
• Push subscriptions
• Usage of snapshot job
• Snapshot path and files
• Usage of log reader job
• Usage of Distribution database and repl commands and transaction tables
• Usage of distributor job
• Understand the process of applying the snapshots onto subscriber.
• Understand the process of applying changes to subscriber.
• Distribution cleanup job
• Monitor the replication process
• Identify and troubleshoot real-time issues in replication 

• Understand the concept of High Availability
• Understand the role of Window Cluster and Quorum disk
• Pre-requisites to Enable AGA feature on SQL Server service
• Prerequisites to Setup databases to AGA groups
• Configure the SQL Server Always On Availability Groups
• Add a database to New Availability Group by using AGA wizard on Primary replica
• Add a database to existing Availability Group by using AGA wizard on Primary replica
• Remove database from availability group
• Restore a database from backup which is in availability groups
• Configure AGA Group with Asynchronous
• Test auto failover by take down the current Primary replica
• AUTO SEEDING option
• Do manual failover by from Dashboard
• Add AGA listener
• Test the connections by using Listener
• Create one more AGA group and add different set of databases
• Add listener to another AGA group and test the connection
• Check the status of Asynchronous commit changes
• Install the Service pack2 (On SQL Server 2014/2016) on both nodes
• Add AD users and groups to Replicas
• Grant Access to SQL Authenticated Logins
• Setup the jobs in AGA Replica to run only on Primary replica
• Monitor the status of synchronization by using Dashboard
• Different forms of failovers (Auto failover, manual failover and forced failover)
• Configure read-only routing list for Read Intent connections
• Accessible secondaries for report purpose

• System Monitor
• SQL Server Profiler
• SQL Server and Windows Error Logs 

• Locks and session blockings
• Different types of locks
• Monitor the locks
• Understand the deadlocks
• Generate a deadlock situation
• Examine the deadlock situation
• Troubleshoot to minimize occurrences of deadlocks
• Isolation levels
• Read committed isolation level
• Read uncommitted isolation level
• Serializable isolation level
• Non repeatable reads isolation level
• Test blocking sessions with different isolation levels
• Understand the heap tables and storing of data
• Understand the clustered tables and storing of data

• Tables with indexes
• Different types of indexes
• Understand the B-Tree architecture
• Understand why indexes improve the performance of read operation
• Statistics on a table
• Query optimizer role
• Fragmentation in indexes 
• Understand why fragmentation occur
• Troubleshoot fragmentation issue
• Rebuild/reorganize the indexes
• Usage of fill factor
• Understand the execution plans
• Examine the execution plans to identify the performance issues
• Index scan vs index seek vs table scan operations
• Add proper indexes based on the sql queries
• Non clustered indexes and/or covering indexes
• Best recommendations to create non clustered or covering indexes in the tables 

• Using DMV’s and DMF’s to analyze the Query Performance
• Compress the tables to reduce its size which in-turn helps to improve the query performance
• Table partitions
• Database Tuning Adviser to trouble shoot long running queries
• Identify and troubleshoot IO, CPU & Network bottlenecks 
• Troubleshoot tempdb issues

• SQL Server Architecture
• Starting SQL Server in Single user mode
• Modifying the Logical and Physical names of database files
• System databases - Master, Model, MSDB & TempDB
• Rebuilding System databases 

• Procedure to migrate the SQL Server
• In-Place upgrade and Sid by side Migrate
• Use the Microsoft Database Migration Assistance Tool
• Migrate to Azure VM

• Introduction about clustering
• Clustering Architecture
• Importance of IP’s involved in Clustering
• SAN importance
• Drive dependency
• Nodes in Clustering
• Implementation of 2-node cluster
• Installation and Configuration of Domain System
• Configuring the SAN Storage
• Installing SQL Server with Cluster features
• Adding Nodes to Clustering
• Monitoring and troubleshooting the Clustering 

• Understand IAAS, PAAS, SAAS
• License Model
• SQL Server on Azure VM
• SQL Server Managed Instances
• Elastic Pools
• Data Security
• Monitoring Azure resources
• Optimize SQL Server performance in Azure
• Optimize Query performance
• Automate the Alerts
• Disaster Recovery and High Availability in Azure