USALaoTu 北美业界DBA专家 Microsoft PMP / MCP 教授 SQL Server 2014 DBA Term2 8:00 to 12:00 Sunday 11/20/16 to 01/22/17

SQL Server 2014 DBA Term2  8:00 to 12:00 Sunday 11/20/16 to 01/22/17
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
(8) 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
(8) 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
(8) 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
(8) 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
(8) Break – 10min
(9) Overview of AlwaysOn HA – 40min
(10) Question/Answer/DBA tips – 10min
(11) Lab for Students discussion and expectation – 10min

Advanced DBA Topics

Class 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