Login

OTP sent to

MS DBA

Home > Courses > MS DBA

MS DBA

Database

Duration
45 Hours

Course Description

               An MS SQL Server DBA (Database Administrator) manages and maintains SQL Server databases, ensuring their performance, security, and availability. This includes tasks like database design, implementation, configuration, monitoring, performance tuning, backup and recovery, security management, and troubleshooting. They also play a key role in ensuring data integrity and implementing high availability solutions. 

Course Outline For MS DBA

1. Introduction to SQL Server and DBA responsibilities

  • Understanding SQL Server Architecture: Overview of SQL Server components and how they interact.
  • SQL Server Editions and Versions: Exploring different SQL Server editions (e.g., Standard, Enterprise) and their features.
  • Roles and Responsibilities of a SQL Server DBA: Understanding the duties of a DBA, including installation, configuration, maintenance, security, and performance optimization.
  • SQL Server Tools and Utilities: Introduction to essential tools like SQL Server Management Studio (SSMS), SQL Server Configuration Manager, SQL Server Agent, and Azure Data Studio. 

2. SQL Server installation and configuration

  • Pre-Installation Steps: Meeting hardware and software requirements and preparing the environment.
  • Installing SQL Server: Performing unattended or attended installations.
  • Post-Installation Configuration: Configuring services, network protocols, memory, and default paths.
  • Adding and Removing Components: Managing SQL Server features post-installation.
  • Patching and Upgrading SQL Server: Applying service packs, cumulative updates, and hotfixes, and performing in-place or side-by-side upgrades. 

3. Database management

  • Creating and Managing Databases: Working with system and user databases, creating and modifying databases, adding files, filegroups, and managing database options.
  • Database Architecture: Understanding data files, log files, extents, pages, and transaction architecture.
  • Managing File Space: Adding new filegroups, moving objects between filegroups, and managing log file growth.
  • Implementing Contained Databases: Working with contained databases to improve portability and simplify management.
  • Database Maintenance Tasks: Ensuring database integrity with commands like DBCC CHECKDB. 

4. Backup and recovery

  • Backup and Recovery Models: Understanding recovery models (Full, Simple, Bulk-Logged) and their implications for backup and recovery.
  • Backup Types: Performing full, differential, transaction log, and copy-only backups.
  • Backup Devices and Media: Working with backup devices (disk, tape, URL to Azure Blob Storage).
  • Restoring Databases: Performing restores (full, differential, transaction log, point-in-time recovery).
  • Disaster Recovery (DR) Strategies: Designing and implementing DR strategies using backups and other techniques.
  • Testing Backup and Restore Procedures: Verifying the restorability of backups regularly. 

5. Security

  • Authentication and Authorization: Understanding Windows Authentication and SQL Server Authentication, creating logins and users, and managing server and database roles.
  • Permissions Management: Granting and revoking object-level access.
  • SQL Server Security Features: Implementing features like Transparent Data Encryption (TDE), Dynamic Data Masking, Row-Level Security (RLS), and Always Encrypted.
  • Security Auditing: Setting up and reviewing SQL Server audit policies.
  • Best Practices for SQL Server Security: Implementing a defense-in-depth approach, regular patching, using strong password policies, limiting service account permissions, and securing sensitive data. 

6. Performance tuning and optimization

  • Identifying and Resolving Performance Bottlenecks: Using tools and techniques to diagnose and fix performance problems.
  • Query Optimization: Writing efficient T-SQL queries, analyzing execution plans, and using index and query tuning strategies.
  • Index Management: Understanding different index types, creating and maintaining indexes, checking fragmentation, and rebuilding/reorganizing indexes.
  • Using Performance Monitoring Tools: Utilizing tools like Performance Monitor, SQL Server Profiler, Extended Events, and Dynamic Management Views (DMVs).
  • Optimizing Database Design: Applying normalization, choosing appropriate data types, and using filegroups effectively. 

7. High availability and disaster recovery

  • Understanding HA/DR Concepts: Differentiating between High Availability (HA) and Disaster Recovery (DR) and their respective goals.
  • SQL Server HA/DR Solutions: Implementing technologies like:
    • Failover Clustering: Ensuring automatic failover of SQL Server instances within a single data center.
    • Always On Availability Groups: Providing high availability and disaster recovery for a set of user databases with options for read-only replicas and backups.
    • Log Shipping: Automating the backup and restoration of transaction logs between servers.
    • Database Mirroring: Maintaining two copies of a database (Principal and Mirror) for HA or DR purposes (though it is deprecated). 

8. Automation and monitoring

  • Automating Administrative Tasks: Using SQL Server Agent jobs and maintenance plans to schedule and automate tasks.
  • Database Mail: Configuring and using Database Mail for alerts and notifications.
  • Monitoring SQL Server Activity: Implementing monitoring solutions with SQL Server Agent alerts and notifications, and tools like Activity Monitor.
  • Auditing SQL Server Instances: Configuring server and database level auditing to track activity and detect potential security breaches. 

9. Real-time scenarios and troubleshooting

  • Troubleshooting Common Issues: Diagnosing and resolving problems related to connectivity, performance, and database corruption.
  • Real-world Case Studies: Analyzing and solving real-time issues encountered in production environments.
  • Using Ticketing Processes and Tools: Understanding how DBAs interact with ticketing systems like Remedy for incident management. 

10. Cloud integration with Azure SQL DBA

  • Azure SQL Database Services: Overview of Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Azure VMs.
  • Deploying and Configuring Azure SQL Databases: Provisioning and managing Azure SQL Database, Managed Instances, and SQL Server on Azure VMs.
  • Implementing Security in Azure SQL: Configuring security features like Azure Active Directory authentication, TDE, and Advanced Data Security.
  • Monitoring and Optimizing Performance in Azure SQL: Using Azure Monitor, Query Store, Intelligent Insights, and other Azure tools.
  • Automating Tasks in Azure SQL: Utilizing Azure Automation, PowerShell, and Azure CLI to automate database management tasks. 
Enquire Now