Certificate Authentication

SQL Server Security and Basic Administration

1. Introduction to SQL Server Security and Administration
   • Importance of SQL Server security and database management
   • Key responsibilities of SQL Server administrators

SQL Server Security and Basic Administration

1. Server Roles
   • Overview of SQL Server roles and their significance
   • Configuring and managing server roles
2. Database Roles
   • Server vs. database roles
   • Default and custom roles in SQL Server
3. SA Account
   • Risks and best practices associated with the SA (System Administrator) account
   • Securing and managing the SA account
4. Creating and Managing User Accounts
   • Creating new user accounts
   • Managing permissions and access control for users
5. Schemas
   • Importance of schemas in database structure
   • Creating and managing schemas to control access and organization of objects

High Availability

1. Log Shipping
   • Concept and implementation of Log Shipping
   • Configuring Log Shipping for redundancy and recovery
2. Database Mirroring
   • Overview of Database Mirroring
   • Types of Database Mirroring: High Safety vs. High Performance
3. Server Clustering
   • What is SQL Server Clustering and how it ensures high availability
   • Configuration and benefits of SQL Server Clustering for fault tolerance

Replication

1. Snapshot Replication
   • Configuring Snapshot Replication
   • Use cases and limitations
2. Transactional Replication
   • Setting up and configuring Transactional Replication
   • Benefits and challenges of Transactional Replication
3. Merge Replication
   • Introduction to Merge Replication
   • Configuration and real-world use cases

SQL Server Activity Monitor

1. SQL Profiler
   • Overview and configuration of SQL Profiler for performance monitoring
   • Capturing and analyzing SQL Server events
2. Extended Events
   • Introduction to Extended Events for advanced SQL Server monitoring
   • Configuring and utilizing Extended Events for diagnostics

SQL Server Upgrade

1. In-Place vs. Side-By-Side Upgrades
   • Differences between In-Place and Side-By-Side upgrades
   • Choosing the right upgrade method based on your environment
2. Service Packs and Cumulative Updates
   • Importance of applying Service Packs and Cumulative Updates
   • Best practices for updating SQL Server instances
3. Cluster Upgrades
   • Considerations for upgrading SQL Server in a clustered environment
   • Step-by-step guide for cluster upgrades

Performance and Tuning

1. Query Store
   • Benefits of using the Query Store for monitoring query performance
   • Configuring and analyzing data in the Query Store
2. Execution Plans
   • How to read and interpret Execution Plans
   • Using Execution Plans for query optimization
3. SQL Profiler for Performance Tuning
   • Using SQL Profiler to identify performance bottlenecks
   • Key metrics and events to track for performance tuning
4. Extended Events for Performance Tuning
   • Configuring Extended Events for detailed performance analysis
   • Using Extended Events to capture specific performance issues

Operating System Monitoring

1. Monitoring CPU, Memory, Disk I/O, etc.
   • Overview of key system resources to monitor for SQL Server performance
   • Tools and techniques for monitoring CPU, memory, and disk I/O

Disaster Recovery (DR) and Incremental Backups

1. Disaster Recovery with Incremental Backups
   • The importance of incremental backups in a disaster recovery plan
   • Configuring and using incremental backups for faster recovery

Data Corruption Recovery

1. Handling Data Corruption
   • Detecting and diagnosing data corruption (e.g., corruption in a single table)
   • Steps to repair and recover from data corruption using DBCC commands