Microsoft SQL Server 2019 Administration

Explore data storage in SQL Server by managing database files such as mdf and ldf, secondary data files, logical vs physical file names, and file groups to optimize disk usage.

Review system databases (master, model, msdb, tempdb, resource) and how memory optimized tempdb metadata storage, enabled via alter server configuration, can boost performance after a restart.

Distribute data and log files across disks using file groups, separate highly active tables and logs for parallel access, and allocate RAM for tempdb and memory-optimized tables.

Move database files using alter database modify file or detach and attach in SQL Server Management Studio, offline steps, physically relocate mdf and ldf, then reattach and verify.

Explore data recovery by examining recovery models, planning backup strategies, verifying backup integrity, and mastering restores, including point-in-time recovery and data transfer of tables or entire databases.

Understand full, bulk logged, and simple recovery models, including log backups and truncation, and point in time restores; switch to bulk logged for bulk copies and keep simple for non-production.

Plan a backup strategy for system databases (master, model, msdb) and user databases, noting manual full backups after master changes and that tempdb and resource are not backed up.

Compare full and differential backups, noting how each copies data and logs. Recognize how transaction log backups truncate logs, and how tail log backups aid restores with minimal data loss.

Explore SQL Server Management Studio backup options, including full, differential, and transaction log backups, with file and file group backups, media set handling, and verification for reliability.

Understand how the SQL Server restore process returns a database to a stable state with minimal data loss by analyzing transaction states, redoing committed work, and undoing uncommitted work.

restore a database by applying the last full backup as a baseline, then differential backups, then transaction log backups. ensure no gaps and recover to online with correct recovery.

Learn to restore databases in SQL Server Management Studio using the visual restore tools, including tail log backups, differential and transaction log restores, and file relocation or overwrite.

Demonstrates t-sql backup and restore by creating a database, performing a full backup, capturing the tail log backup, simulating a failure, and restoring with no recovery to recover all data.

Enable accelerated database recovery to speed large restores in SQL Server 2019 and Azure. Use log shipping with standby for reporting and perform partial and point-in-time restores for targeted recovery.

Explore data transfer options in SQL Server, including cross-database queries, log shipping, and restoring to a new location; compare database mirroring with automatic failover and high-safety modes.

Navigate the import export wizard in SQL Server Management Studio to transfer data between sources and destinations, configure mappings, and save package for execution by SQL Server Integration Services engine.

Learn to copy or move databases using the copy database wizard in SQL Management Studio, comparing detach-attach and SQL Server Management Objects methods, with rename or drop options.

Monitor SQL Server health using dynamic management views and functions, monitor dashboards, and alerts for CPU or memory spikes; learn tracing with extended events and the database engine tuning advisor.

Explore dynamic management views and functions in sql server, covering server and database level permissions and examples like tempdb log space, missing index details, and virtual log files for maintenance.

Explore server reports at database and server levels to monitor disk usage, backup and restore activity, blocking transactions, and missing indexes, guided by performance dashboards and dynamic management queries.

Learn how system monitor uses performance counters to track sql server performance in real time and from data, and how to create data collector sets to schedule and playback results.

Explore tracing sql server workload activity by understanding events, event classes, and templates, and learn how extended events and the database tuning advisor replace the deprecated sql server profiler.

Learn extended events, the profiler replacement, to monitor SQL Server and OS via sessions; configure a session to capture sql statement completed events with a like filter and live traces.

Explore how the database tuning advisor analyzes workloads to recommend indexes, partitioning, and other optimizations, using plan cache and query store data to improve SQL Server 2019 performance.

Explore authentication and authorization fundamentals in SQL Server, covering principals and roles, permissions, auditing, and data encryption across server, database, and data levels.

Configure server-level security in SQL Server by choosing Windows or mixed authentication, creating logins and server roles, and using credentials, audits, and certificate-based transport security.

Discover how SQL Server database-level security maps logins to database users, uses schemas to aggregate permissions, and employs roles, audit specifications, and keys and certificates to enforce access.

Implement row level security in SQL Server 2019 using policies and a predicate function to restrict orders to a salesperson’s rows, while managers see all.

Explore database security tools in SQL Server Management Studio, including vulnerability assessment and data discovery and classification, to identify issues, classify sensitive data, and enforce encryption and audit-ready policies.

Explore how a contained database isolates itself from others, stores its own user information, and enhances portability for moving databases between hosts via containment partial and server-level authentication.

Audit server- and database-level activities to detect events and failed logins, store logs in files or Windows event logs, and configure audits and audit specifications for login attempts and deletions.

Implement transparent data encryption to protect data at rest with a server master key, certificate, and database encryption key; back up keys to ensure accessibility on valid SQL Server instances.

Master SQL server maintenance basics, including integrity checks, backups, and index maintenance to prevent fragmentation. Automate routines with maintenance plans and SQL agent jobs, and configure email alerts for issues.

Understand how the database consistency checker (dbcc) validates data with check allocation, check table, and check catalog for primary database integrity, and how maintenance plans automate these checks.

Explore how indexes speed searches by organizing data, manage fragmentation with rebuild or reorganize strategies, and monitor health with statistics and dynamic management views.

Automate routine database maintenance with maintenance plans and the wizard, covering backups, index maintenance, and statistics updates. Schedule tasks, integrity checks, and reports to streamline SQL Server administration.

Learn to automate SQL Server tasks with SQL Server Agent by creating jobs and steps (T-SQL, SSIS, PowerShell, OS commands) stored in MSDB, using schedules, alerts, and multi-server targets.

Set up and manage SQL Server alerts to monitor errors and events using Windows log, performance counters, and WMI events, adjusting severity with sp alter message and raise error.

Configure database mail with profiles and SMTP accounts to enable email notifications, then review SQL Server Agent jobs, alerts, operators, proxies, and master and target servers for comprehensive maintenance.