Microsoft SQL Server Database Administration - DBA
SQL Server 2019 continues to push the boundaries of security, availability, and performance for all your data workloads, with new compliance tools, higher performance on the latest hardware, and high availability on Windows, Linux, and containers.
Enhanced PolyBase enables you to query other databases like Oracle, Teradata, and Mongo DB directly from SQL Server without moving or copying the data. And for the first time, SQL Server 2019 goes beyond relational databases with Spark and Hadoop Distributed File System (HDFS) in the box for Big- data capabilities built-in.
Module 1: Installation & Configuration, Relational Database Management Systems/Platforms
- Database Administration & DBA roles Overview
- Introduction to the Microsoft SQL Server Platform and Relational
- Database Management Systems (RDBMS)
- Creation of windows users, such as local and administrative users, and purpose
- Planning & Installing Microsoft SQL Server Instances (2019 or latest).
- Familiar with Microsoft SQL Server Administration Tools and everyday database administration tasks
- Perform post-installation configuration tasks
- Introduction to system and user database
Duration
Weeks and Clock hours
2 weeks ten clock hours
Module 2: Database Designing, Data Modeling, Database Creation & Security
- Microsoft SQL Server Database Designing and Data Modeling Concepts
- Introduction to Data Storage with Microsoft SQL Server and Database File Structure
- Database Creation with Objects (tables, columns, views, indexes, constraints)
- Insert Records, Update Records, Delete Records [essential Transact SQL (T-SQL) statements]
- Managing Storage user Databases, Moving Database Files, and Configuring Microsoft SQL Server Instance and Database Properties
- Introduction to Microsoft SQL Server Instance Level and Database Level Security, Creation Login/User, Mapping/Granting Permission
Duration
Weeks and Clock hours
4 weeks 20 clock hours
Module 3: Database Backup, Recovery Model, and Maintenance
- Understanding Microsoft SQL Server Database Recovery Models
- Planning a Backup Strategy and Perform various type of Database Backup Plans
- Ensure Backup Reliability, Implement/Working with Disaster Recovery and Point-In-Time Backup & Recovery Plan
- Restoring Databases
- Copying or Moving a Data and Database (Data Export & Import)
- Introduction to Microsoft SQL Server Management Tool and SQL Server Agent
- Implement a Database Maintaining Plan (Automated)
Duration
Weeks and Clock hours
4 weeks 20 clock hours
Module 4: Database Mail & Agent, T-SQL, Join & Encryption(TDE)
- Database Mail configuration for task status notification
- Create operator and Alert
- Setup notification with SQL Server agent job/task
- SQL statement, Join
Duration
Weeks and Clock hours
3 weeks 15 clock hours
Module 5: High Availability, Disaster Recovery, VMware, Cloud Computing
- High Availability (HA) Options
- Mirroring (Failover)
- Log shipping
- Replication – Transactional, Snapshot, Merge, and Peer to Peer
- Build Windows Server on VM (Instructor lab)
- Configure Windows Failover Clustering, Quorum configuration
- Always-On High Availability, AG, and listener configuration
- AWS –RDS and EC2 (instructor lab): Database creation, role and policy review, S3 bucket configuration, Migration
Duration
Weeks and Clock hours
3 weeks 15 clock hours
Module 6: Performance Monitoring, Troubleshooting & Performance Tuning
- Monitoring Microsoft SQL Server
- Monitoring Database Activities
- Functions of Database Management Views, DBCC (Database Console Command)
- Performance Monitoring: Server Level (Operating System), Database Level
- Performance Tuning Microsoft SQL Server & Database Level – using SQL Native Tools, Views, Stored Procedures, SQL Profiler, Database Tuning Advisor
- Index Maintaining, Analyze Query Execution Plan,
- Implement Database Routing Maintenance
Duration
Weeks and Clock hours
3 weeks 15 clock hours
Module 7: SSIS, SSRS, Upgrading, Migration, Installing, Configuring, Developing, Deploying SQL Server Components, and provide Career Services
- Installing & Configuring Microsoft SQL Server other Components such as SQL Server Integration Service (SSIS), SQL Server Reporting Service (SSRS)
- Creating Packages, Creating Reports using Business Intelligent Development Studio (BIDS), Visual Studio
- Building, Deploying SSIS Packages, and SSRS Reports
- SQL Server Database Upgrading and Database Migration, SQL Server Migration Assistance (SSMA)
- Provide Information, Reading Materials; Web Links to Practice for Microsoft Certification Exam (MCSA)
- A session for Questions and Answers
- Career Service
- Assisting with Database Administration Job
- Sample DBA Resume Preparation
- Interview Preparation
Duration
Weeks and Clock hours
4 weeks 20 clock hours
Module 8: Revision & Assisting
- Revision Class Topics
- Provide more assistance for the preparation of the Microsoft certification test
- Provide Assistance with Resume Preparation
- Questions & Answers Session (Interview preparation)
- Mockup interview and job marketing assistance