Course Duration in Hours
120
120
SQL Server 2012 DBA COURSE CONTENT
Course Description
SQL Server 2012 DBA training provides knowledge and skills to maintain a Microsoft SQL
Server 2012 database. The course focuses on teaching individuals to use SQL Server 2012 product features and tools related to maintaining a database. You will be confident to perform database administration and maintenance as their primary area of responsibility, or work in environments where databases play a key role in their primary job.
Course Topics
1 : Introduction, SQL Server Fundamentals and terminologies (versions , Services , System db and objects ACID properties and Isolation Levels).
a. SQL server versions and basic differences
b. ACID properties and their use in sql server
c. Isolation levels
d. Database architecture
e. System DB and moving them across disks and versions
f. How to speed up usual work for a DBA
2: Recovery Model; Backup Restore, and impact of T-Log
a. Recovery Model and how database recovery works
b. Database backup and restore
c. How backup restore internally works
d. Transaction Log Architecture
e. Why T-log required in Sql server and how it helps above all to work in sql server
3: SQL Server Architecture - (SQL Server model and Physical Database Architecture)
a. SQL Model
b. SQL Protocols and Query Processor c. Page & Extents
b. Physical Database Files & File Groups c. Space Allocation & Reuse
d. Table & Index Architecture
4: SQL Server Architecture and OS
a. Semaphores and Mutex
b. Use of AWE , PAE , and /3gb Switch
c. What is Virtual Address space
d. Deadlock , Checkpoint , lazywriter, Bufferpool
e. Max and Min worker and SP configurations
5: SQL Server Maintenance task including consistency
a. Re Indexing and Reorganizing
b. Statistics
c. Consistency check and Suspected pages
d. shrinking
6: Sql server security and Network Communication
a. Sql server protocols
b. Authentication types and differences c. Memory in Connection
d. Roles and views
e. Migrating permissions
7: SQL Server storage engine and memory management
a. Storage Engine
b. Acess methods
c. Memory management and buffer pool
d. observing Memory Intervals
8: Replication
a. Pre-Requisites for setting up replication
b. Setup
c. Failover
d. Practical troubleshooting
9: Log shipping and mirroring
a. Pre-Requisites for setting up log shipping and Mirroring
b. Setup
c. Failover
d. Practical troubleshooting
10: Performance Tuning and Tools
a. Querys
b. Profiler Trace
c. SET options
d. Execution Plan
e. Client Statistics
f. Tuning Advisor
g. Performance Monitor
h. DMVs
11: DMV s and Profiler
a. Setup profiler
b. When and how to use profiles
c. What are impact of using profilers
d. Types of DMVs and how to use it
12 : DBCC Commands and Performance query
a. DBCC commands and their description
b. Performance and health check queries and there descriptions
13 : SQL Cluster Patching, Migration
a. Defining sql cluster
b. Types of Migration
c. Process to follow for migration (step by step)
d. Patching Sql server
e. Patching SQL server cluster
14 : Discussion on Regular issue / Troubleshooting done By DBA
a. blocking
b. log file full
c. killing spid
d. checking fragmentation, stats
e. user not able to connect
f. Sql server not able to connect
15: Discussion on Major issues / Troubleshooting done By DBA
a. Database Corruption
b. Data file shrink not working
c. SQL server is responding very slow d. High CPU or High Memory
e. Replication / Log shipping / Mirroring broke down
16: SQL server Interview Questions
Any Graduate
Sgraph Infotech, Marathahalli (Bangalore),Bangalore,IN