
SQL Server DBA
COURSE DESCRIPTION
-
- Totally 40
- Every Tuesday 6:30pm – 10:30pm.
- Easy to get in and start.
- A newly designed, hands-on focused.
- Everyone has a computer to practice.
- Cross-managing Access, Excel and SQL Server.
Course Outline
Project overview, Requirement Analysis and Case Study
Case 1. Installing SQL Server
– Installing new SQL Server 2012/2016 instances, and applying ServicePack & HotFix
(Standalone and Clustered)
– Post Configurations after Installation
– Configure Surface Area
Case 2. Create Databases
– Files and Filegroups
Case 3. Design and Create Tables
– Determine Data Types
Case 4. Design and Create Indexes
– Creating and adjusting indexes
Case 5. Views
– Designing and creating indexed views
Case 6. Monitoring databases & indexes
– Verifying files, locations, index parameters
– System Monitor Counters
Case 7. Implement Backups
– Full, differential and transaction log backups
Case 8. Database Recovery
– Restore backups
– Recover database to point of time.
Case 9. SQL Server Security
– Design and Manage Logins and Permissions
– Design and Implement Security Policy and an Audit Plan and Audit SQL Server instances
– Handle transparent data encryption
Case 10. Database Security
– Database Users, Roles and Schemas
Case 11. Object Security
– Access permissions & object ownership
– Associated Permission Controls
Case 12. Security Control
– Manage permissions using stored procedures
Case 13. Encryption
– Encrypting data
Case 14. Working with data loading
– Bulk Insert and bcp
– Using Packages for data loading and extracting
Case 15. Tracking Your Change
– Tracking Data Change
Case 16. Disaster Recovery
– Setting up DR environment
Case 17. High availability (1)
– Failover Clustering
Case 18. High availability (2)
– Auto Failover: Database Mirroring
Case 19. High availability (3)
– Implement, verify & manage replication, snapshot, transactional and merge
– Peer-to-peer replication
Case 20. High availability (4)
– Implement, verify & manage log shipping
Case 21. Configuring SQL Server Agent
– Database mail, jobs and alerts
Case 22. Monitoring SQL Server
– Activity Monitor, DMV/DMF & Profiler
– Data collection
Case 23. More Performance Tuning
– Using Database engine Tuning Advisor
Case 24. Partition
– Design and Implement Partitioning
Case 25. Optimizing Query Performance (1)
– Subqueries
Case 26. Optimizing Query Performance (2)
– Incorrect keywords
Case 27. Optimizing Query Performance (3)
– Solving Table-Scan
Case 28. Optimizing Query Performance (4)
– Using Views
Case 29. Optimizing Query Performance (5)
– Cursor
Case 30. Optimizing Query Performance (6)
– Detecting and resolving locking/blocking
Case 31. Optimizing Query Performance (7)
– Optimizing transactions
Case 32. Optimizing Query Performance (8)
– Managing index
Case 33. Optimizing Query Performance (9)
– Execution Plans
Case 34. Optimizing Query Performance (10)
– Statistics
Case 35. More Database Maintenance (1)
– Shrinking database and files
Case 36. More Database Maintenance (2)
– Backup SQL Server system databases
Case 37. More Database Maintenance (3)
– Repair SQL Server from corruption
Case 38. More Database Maintenance (4)
– Managing tempdb
Case 39. More Database Maintenance (5)
– Linked server
Case 40. More Database Maintenance (6)
– Security Auditing
Case 41. More Database Maintenance (7)
– Configuring Errorlog
Case 42. More Database Maintenance (8)
– Using DAC
Case 43. More DBA Stuff (1)
– Using osql
Case 44. More DBA Stuff (2)
– Scripting DBA tasks using T-SQL
Case 45. Touching SQL Server BI (1)
– Processing Data for Extracting, Transforming and Loading (ETL) – SSIS
Case 46. Touching SQL Server BI (2)
– Presenting and Reporting data – SSRS
Case 47. Touching SQL Server BI (3)
– Mining and Analyzing Data – SSAS