SQL Server 2012/14 DBA Term1 (100% English) 8:00 to 12:00 Sunday 09/13 to 10/25
7 x 4 hour sessions, total 28 hours.Teacher Mr. Jon, Microsoft PMP / MCP:Sixteen years of professional IT experience as a Database Administrator with large corporate databases. Strong experience with Hosting Solution on Healthcare industry. Over 4 years of DBA Engineering Team Lead managing multiple projects and mentoring team of DBAs both onshore and offshore.
Highly proficient in implementing SQL High Availability Solution using AlwaysOn/Replication/Log Shipping/Windows Clustering.
Expertise with VLDB (>10 TB) and complex database solution.
Expert level experience with capacity planning, DR, Database Servers infrastructure configuration and maintenance.
DB Migrations / upgrades and consolidations (including P2V) database server to SQL Server 2012.
Extensive experience with automate DBA tasks, proactive monitoring, virtualization, backup & restore.
Expert experience for analyzing/troubleshooting and problem solving database errors (Including deadlock errors, performance issues and connectivity errors).
Experienced working with business partners and users of different groups and departments.
Course Content:Class 1: SQL Server DBA Basics / Install automation / Best Practices – 4hrs(1) Introduction - 20min
(2) Start with some basic/interesting topics - 30min
- What do you expect in the DBA job interview?
- What are the role of a DBA? Do you wait for tickets to come and then start working?
- What is orphaned user, when do you see that, how to troubleshoot?
- Your transaction log is full - what do you do?
- Transaction log backup is not truncating the log file, what is going wrong?
(3) Break - 10 min
(4) SQL 2012/2014 a brief overview – 30min
(5) Start with some installation tips. (Assuming all students know how to install SQL 2012/2014) - 20min
- How do you automate installation?
- How do you automate Service Pack and Cumulative update installation?
(5) Break - 10 min
(6) Demo on installation automation – 30min
(7) Installation/upgrade on Failover Cluster/AlwaysOn Availability - 20min
(
Break - 10 min
(9) Installation best practices – 30min
- Where to place "tempdb" database
- Storage allocation
(10) Question / Answer / Interview Tips - 20min
(11) Lab for Students - discussion and expectation - 10min
Class 2: backup and recovery/Database Maintenance - 4hrs (1) Discussion on Lab exercise from previous class - 20min
(2) Database Backup - 30min
- Recovery Model
- Full/Differential/T-log backup
(3) Break - 10min
(4) Restoring Database - 40min
- Restoring Database
- Restoring to point-of-failure
(5) Question/Answer/DBA tips - 10min
(6) Break - 10min
(7) Backup History and maintenance plan - 50 min
- Designing Maintenance plan for Backup
- SQL Server Agent for scheduling backups
- Database Email notification
(
Break - 10min
(9) Restoring System databases - 40min
(10) Question/Answer/DBA tips - 10min
(11) Lab for Students discussion and expectation - 10min
Class 3: Automation of SQL Server/Agent Jobs/SQL mail and SQL Server Security - 4hrs(1) Discussion on Lab exercise from previous class - 20min
(2) How to configure SQL Agent - 20min
(3) Configure SQL Mail and automating Alerts – 10min
(3) Break - 10min
(4) Automating maintenance plan/backup/restore - 30min
(5) Introduction to Powershell scripting - 20 min
(6) Break - 10min
(7) Database Security - 50 min
(
Break - 10min
(9) Configuring contained database - 40min
(10) Question/Answer/DBA tips - 10min
(11) Lab for Students discussion and expectation - 10min
Class 4: Performance Tuning/Database Maintenance/monitoring - 4hrs(1) Discussion on Lab exercise from previous class - 10min
(2) Understanding Database Indexes - 15min
(3) Checking index fragmentation and defrag them - 25min
- Rebuild vs Reorganize index
(4) Break - 10min
(5) Database Statistics / Maintenance plan - 30 min
(6) SQL Server Maintenance Plan and Agent Jobs for regular maintenance - 20min
(7) Break - 10min
(
SQL Server Profiler - 50 min
- Creating Traces and analyze them
- Extended Event basic for SQL 2014
(9) Break - 10min
(10) VLDBs, SAN, SSD and partitioning Strategies - 20
(11) How to troubleshoot database blocking with SQL DMV - 30
(12) Question/Answer/DBA tips - 10min
(13) Lab for Students discussion and expectation - 10min
Class 5: High availability - 4hrs(1) Discussion on Lab exercise from previous class - 20min
(2) Log-shipping demo - 30min
(3) Break - 10min
(4) Mirroring - Pros-Cons – 20min
(5) Transactional Replication – 30min
(6) Break – 10min
(7) Achieving high availability with Failover Cluster Instances – 50min
(
Break – 10min
(9) Overview of AlwaysOn HA – 40min
(10) Question/Answer/DBA tips - 10min
(11) Lab for Students discussion and expectation - 10min
Advanced DBA TopicsClass 6: Some Advanced SQL 2012/2014 Topics - 4Hrs- SQL 2014 Change Data Capture
- SQL 2014 SSD Buffer pool extension
- SQL 2014 in memory OLTP Engine
- SSRS install and configure
- SQL 2014 Extended Event configuration
Class 7: How to configure AlwaysOn High Availability – 4hrs More information later.