SQL Server DBA

• Installing SQL Server 2022
  • Pre –Installation Steps
  • Installations
  • Viewing the Installation process with Log files
  • Common issues
  • Adding or removing components
  • Installing Service Packs
• Configuration
  • Configuration of Various Services
  • Startup Parameters
  • Configuring data files and log file paths
  • Memory Configuration
  • Remote Connections
  • Configuring network protocols, Ports
  • Configuring Services
  • Configuring default backup folder.

Learning Outcome

At the end of this section, you will be in a position to test the connectivity while installing and also able to enable and disable features. You will gain complete expertise in configuring SQL Server 2022 components in a very high and complex and secure environment. Additionally, you can configure full-text indexing by yourself.

• Working with Databases
  • System Defined databases
  • Moving System Databases
  • Handling TempDB database 
• Database Architecture
  • Data Files
  • Log Files
  • Filegroups
  • Extents
  • Pages –Types
  • Page Architecture
  • Tracking Free Space
  • Log file full – How to troubleshoot. 
• Creating Databases
•  Adding files, Filegroups
• SQL Server Architecture

SQL Server 2022 built on the latest innovations will allow an individual to run SQL Server on your own platform of choice with more security features and functionality. In this sector, you will learn how a SQL Server allows developers and database administrators to develop and create database applications securely using its prominent architecture of security.

• Security Enhancements
• Types of Authentications.
  • Windows Authentication
    • Creating logins from Windows users and groups
    • Orphan logins
• SQL Server Authentication
  • Creating SQL logins and testing logins
  • Setting Authentication Mode
  • Security Auditing
  • Understanding Server roles
  • Working with users
  • Resolving Orphan users
  • Understanding database roles
  • Understanding permissions
  • Major Issues

Learning Outcome

By the end of this session, you will be able to build modern and secure database applications and also you can counter threats using SQL Server 2022 security measures.

In this module, you will gain knowledge on how to back up your own database using SQL Server 2022 backup strategies. At the same time, you will learn how to restore your database securely from the damaged DB. Furthermore, you will get through some more additional concepts such as recovery models, types of backups, point-in-time recovery, and many more.

• Recovery Models Setting Recovery Model
  • Full
  • Bulk Logged
  • Simple
• Database Backups
  • Why do we need backups 
• Backup Types Copy-Only, Mirrored, and Tail log backups
  • Full
  • Differential
  • Transaction Log
  • File or Filegroup
• Backup Devices
• Performing Restoration
• Backup System Databases
• Point-in-time Recovery
• Viewing complete details of the backup process
• Issues related to backups

Learning Outcome

You will be able to perform a backup of your database using various backup models present in SQL Server 2022 by the end of this section. You can even restore your database at a point in time. Finally, You will also be in a position to understand page-level restore, group file restore, and many more in a clear way.

You will get through high availability features in SQL Server 2022 and how this feature makes the availability of servers in the occurrence of any failover event. You will come to know about log shipping feature in this section. SQL Server mainly uses log shipping for disaster recovery and it also involves log backups for the principal databases.

• Introduction to High Availability
• Working with Log Shipping
  • Features
  • Jobs
  • Requirements to implement Log Shipping
  • Configuring Log Shipping
  • Monitoring Log Shipping
  • Manually performing Fail Over
  • Transferring Logins
  • Log Shipping tables and stored procedures
  • Handling Issues

Learning Outcome

By the end of this module, you will gain expertise in the high availability of groups and disaster recovery features. you will also be able to configure and monitor log shipping by yourself.

You will get through the availability groups feature called Always-On and Replication Strategies in this module. This “Always On” is the significant disaster-recovery and availability solution used as a substitute for database mirroring. Another feature of SQL Server is replication, it is a group of technologies used for disturbing objects and data from one database to other databases in different locations through the Internet. Additionally, you will also have also learn concepts such as Patching in Always-On, Troubleshooting, types of Replications, and many more.

• Always-ON
  • HADR Internals & Advantages
  • Setting up HADR
  • Adding a DB to the AG
  • Patching in Always on
  • Adding a Replica in AG
  • Setting up Listener & read-only Replicas in AG
  • Troubleshooting & maintenance
• Replication
  • Replication and Advantages
  • Replication Entities
  • Replication Architecture
  • Replication Agents
  • Types of Replications
  • Configuring Replication 
  • Snapshot Replication
  • Transactional Replication
  • Merge Replication
  • Peer to Peer replication
  • Managing Replication
  • Monitoring and Tuning Replication

Learning Outcome

You will be in a position to troubleshoot replication problems by the end of this section. Every individual who has taken this SQL Server DBA training will be able to identify various strategies of replication and availability of groups.

The SQL Server’s new architecture introduces the data platform called Big Data Cluster which is the combination of Spark, HDFS, and SQL Server database engine. This Big data Cluster present inside the relational database SQL Server engine will form a storage layer to integrate HDFS and Server in order to scale petabytes of data.

• Clustering SQL Server
• Overview of Windows Clustering
• Requirements
• Installation and configuration of SQL Server Clustering
• Applying Service packs and fixes
• Moving groups
• Adding node on a SQL Server Failover Cluster
• Troubleshooting Cluster issues.

Learning Outcome

You will gain hands-on expertise in managing multiple instances on a cluster and able to configure SQL Server clusters

Microsoft SQL Server will allow users to automate administrative tasks. Using this automated administration you can handle various events and tasks easily which will save your time for other functions of administration. Additionally, you will gain knowledge on various concepts such as configuring profiles, job monitoring, resolving errors, and many more.

• Maintenance Plans
• Working with Database Mail Configuring
  • Mail Architecture
  • Configuring Profiles and Accounts
  • Sending Mails
• Implementing Automation
  • Configuring SQL Server Agent
  • Creating Operators, Alerts, Jobs
  • Managing jobs and resolving errors
  • Monitoring Jobs
  • Auto alert when jobs are enabled, disabled or failed

Learning Outcome

By the end of this module, you will be able to automate administrative tasks and also understand how to configure SQL Server agents.

• Database indexes understanding
• Checking index defragmentation & Maintenance
• Database Health Check
• Understanding SQL Server Profiler
• Creating & Running Traces
• Understanding Extended Events
• Creating & configuring Extended Events
• Monitoring Tools
  • System Monitor
  • SQL Server Profiler
  • Database Engine Tuning Advisor
  • Dynamic Management Views
  • SQL Server and Windows Error Logs 
• TroubleshootingLocks
  • Physical Server Performance
  • Connectivity to SQL Server
  • Database Performance
• Dead Locks
• Transaction Isolation Levels
• Understanding Blocking
• Terminating Processes
• Using the DAC
• Managing Databases 

RisingStar Tech offers advanced SQL Server DBA interview questions and answers along with SQL Server DBA resume samples. Take a free sample practice test before appearing in the certification to improve your chances of scoring high.