Microsoft SQL Server 2019 Database Administration
Duration: 5 Days (40 Hours)
Microsoft SQL Server 2019 Database Administration Course Overview:
The Microsoft SQL Server 2019 Database Administration training course is designed to provide participants with the essential knowledge and skills needed to effectively administer and manage SQL Server 2019 databases. SQL Server is a widely used relational database management system (RDBMS) that enables organizations to store, retrieve, and analyze data efficiently.
Throughout the course, participants will gain a comprehensive understanding of SQL Server 2019 and its administration tools and techniques. They will learn how to install and configure SQL Server, manage database objects, optimize query performance, implement security measures, and perform backup and recovery operations.
The course covers the following key areas:
- SQL Server Architecture and Installation: Participants will learn about the architecture of SQL Server 2019 and its various components. They will explore different installation options and best practices for setting up a SQL Server environment.
- Database Creation and Management: This module focuses on creating and managing databases in SQL Server 2019. Participants will learn how to create database objects such as tables, views, and stored procedures, as well as manage database files and filegroups.
- Query Performance Optimization: Participants will discover techniques for optimizing query performance in SQL Server. They will learn how to analyze query execution plans, create and manage indexes, and use performance tuning tools to enhance database performance.
- SQL Server Security: This module covers the implementation of security measures in SQL Server 2019. Participants will learn how to configure authentication modes, manage server-level and database-level security roles, and implement data encryption and auditing.
- Backup and Recovery: The course provides insights into performing database backup and recovery operations. Participants will understand different backup strategies, learn how to schedule backups, and explore options for restoring databases to a specific point in time.
By the end of the training, participants will have acquired the necessary skills to effectively administer and manage SQL Server 2019 databases. They will be prepared to take on the role of a SQL Server database administrator and handle key responsibilities related to database administration, performance optimization, security, and backup and recovery.
The Microsoft SQL Server 2019 Database Administration training is suitable for IT professionals who are involved in managing and administering SQL Server databases. It is ideal for database administrators, system administrators, database developers, and IT professionals seeking to enhance their skills in SQL Server administration and management.
Module 1: SQL Server Components and Architecture
- Overview of SQL Server Architecture
- Introduction to the SQL Server Platform
- Installing and Configuring SQL Server 2019
- Installation changes/ Feature Selection /New Tab
- Lab: Identify the Edition and Version of a Running SQL Server Instance, Installing SQL Server 2019
Module 2: Working with Databases
- Introduction to Data Storage with SQL Server
- Managing Storage for System and user Databases
- Configuring the Buffer Pool Extension and Hybrid buffer pool
- Lab: Managing Database Storage
Module 3: Performing Database Maintenance
- Ensuring Database Integrity
- Tempdb Configuration and Memory-optimized TempDB Metadata
- Maintaining Indexes
- Lab: Performing Ongoing Database Maintenance
Module 4: SQL Server Authentication and Authorization
- Connections to SQL Server
- Privileges and permissions to Logins with Database
- Authorization Across Servers
- Lab: Authenticating Users
Module 5: Working with Server and Database Roles
- Working with Server Roles
- Working with Fixed Database Roles
- User-Defined Database and Server Roles
- Lab: Assigning Server and Database Roles
Module 6: Controlling User Access
- Creating and Granting User Access to Objects
- Authorizing Users to Execute Code
- Configuring Permissions at the Schema and Database level
- Lab: Authorizing Users to Access Resources
Module 7: Database Encryption and Auditing
- Auditing Data Access in SQL Server
- Implementing SQL Server Audit
- Managing SQL Server Audit
- Lesson 4: Always Encrypted with Secure Enclaves, Database classification function on 2019
- Lab: Using Auditing and Encryption
Module 8: Planning and Implementing a Backup Strategy
- Understanding Backup Strategies
- SQL Server Transaction Logs
- Planning Backup Strategies
- Lab: Understanding SQL Server Recovery Models
Module 9: Backing Up SQL Server Databases
- Backing Up Databases and Transaction Logs
- Managing Database Backups
- Advanced Database Options
- Lab: Backing Up Databases
Module 10: Restoring SQL Server 2019 Databases
- Understanding the Restore Process
- Restoring SQL Databases
- Point-in-time Recovery with Advance Restore Scenario
- Accelerated Database Recovery
- Lab: Restoring SQL Server Databases
Module 11: Automating SQL Server Management
- Automating SQL Server Management
- Working with SQL Server Agent
- Managing SQL Server Agent Jobs with Security Contexts
- Lab: Automating SQL Server Management
Module 12: SQL Server with Extended Events
- Extended Events Core Concepts
- Working with Extended Events
- Lab: Extended Events
Module 13: Performance Improvement and Monitoring SQL Server 2019
- Monitoring Activity
- Capturing and Managing Performance Data
- Query Execution and Query Plan Analysis
- Statistics Internal
- Query Store
- Lab: Monitoring SQL Server
Module 14: Troubleshooting SQL Server
- Monitoring SQL Server Errors
- A Troubleshooting Methodology for SQL Server
- Resolving Blocking and Locking in SQL Server
- Resolving Connectivity and Login Issues
- Lab: Troubleshooting Common Issues
Module 15: Different Method of Transferring Data
- Transferring Data to and from SQL Server
- Importing and Exporting Table Data
- bulk copy program and BULK INSERT to Import Data
- Lab: Importing and Exporting Data
Microsoft SQL Server 2019 Database Administration Prerequisites:
Q: What is the Microsoft SQL Server 2019 Database Administration training?
A: The Microsoft SQL Server 2019 Database Administration training is a comprehensive program that provides participants with the knowledge and skills required to effectively administer and manage SQL Server 2019 databases. It covers various aspects of SQL Server administration, including installation, configuration, database management, query optimization, security implementation, and backup and recovery.
Q: What are the prerequisites for attending this training?
A: Participants should have a basic understanding of relational databases and SQL concepts. Familiarity with the SQL Server environment and experience with database management systems is beneficial but not mandatory.
Q: What topics are covered in the training?
A: The training covers a wide range of topics, including SQL Server architecture, installation and configuration, database creation and management, query performance optimization, SQL Server security, and backup and recovery operations.
Q: Is this training suitable for beginners?
A: Yes, the training is designed to cater to both beginners and experienced professionals. It provides a solid foundation in SQL Server administration and progresses to more advanced topics. Beginners will gain a comprehensive understanding of SQL Server 2019 and its administration principles, while experienced professionals can enhance their skills and stay updated with the latest features and best practices.
Q: What are the career prospects after completing this training?
A: With the increasing demand for SQL Server database administrators, completing this training can open up various career opportunities. Participants can pursue roles such as SQL Server database administrator, database developer, system administrator, or data analyst in organizations that utilize SQL Server as their database management system.
Q: Will I have hands-on practice during the training?
A: Yes, the training includes hands-on labs and exercises that provide participants with practical experience in administering SQL Server 2019 databases. These hands-on activities allow participants to apply the concepts learned in real-world scenarios.
Q: How long is the training?
A: The duration of the training may vary depending on the format and delivery method. Typically, this training spans multiple days, with each day consisting of several hours of instruction, hands-on exercises, and discussions.
Q: Will I receive any course materials or resources?
A: Yes, participants will receive comprehensive course materials, including slides, lab guides, and additional reference materials to support their learning during and after the training
Q: Is there a certification associated with this training?
A: This training does not include a specific certification. However, it provides valuable knowledge and skills that can contribute to your professional growth and enhance your capabilities in managing Active Directory services.
Q: Can this training be customized for specific organizational needs?
A: This training can be customized to address specific organizational needs. We can discuss customization options based on your requirements.
Q: Who delivers the training?
A: The training is delivered by experienced instructors who have expertise in Active Directory services and Windows Server administration. These instructors possess practical knowledge and industry experience to provide relevant and valuable insights during the training sessions.
Discover the perfect fit for your learning journey
Choose Learning Modality
This course comes with following benefits:
- Practice Labs.
- Get Trained by Certified Trainers.
- Access to the recordings of your class sessions for 90 days.
- Digital courseware
- Experience 24*7 learner support.
Got more questions? We’re all ears and ready to assist!