Database Administration with SQL Server 2019

Explore the fundamentals of database administration with SQL Server 2019, including the roles of a DBA, relational databases, DBMS concepts, and core responsibilities like installation, security, and uptime.

Plan a SQL Server 2019 environment using VMware, install Windows Server 2019, then SQL Server 2019 Developer Edition and SQL Server Management Studio, using lesson links and a programs folder.

Install VMware Player on Windows 10, create a Windows Server 2019 virtual machine, and configure ram to 8 gb, nat networking, and the Windows Server iso image.

Install Windows Server 2019 in a VM from an ISO, set up two 25 GB partitions for OS and SQL Server, and map a host programs folder after configuring firewall.

Prepare a production SQL Server 2019 environment with fast solid-state storage and 64 kilobyte allocation units, assigning a dedicated instance named DBA course_DB and enabling mixed mode authentication.

Connect to a SQL Server 2019 instance by configuring shared memory, named pipes, and TCP/IP, enabling TCP/IP, testing connectivity with SQL CMD, and opening port 1433.

Explore a SQL Server 2019 instance, the system databases (master, model, msdb, tempdb), physical and logical structures, and create user databases via SQL Server Management Studio or scripts.

Learn to create logins and users, assign roles, and enforce least privilege via authentication and authorization in SQL Server, mapping access to class one and class two databases.

Explore sql as the core language for managing relational data, covering create read update delete operations, table design, data types, schemas, permissions, and sql server management studio practices.

Learn to configure a SQL Server instance with sp_configure, manage memory settings, enable lock pages in memory, adjust processor affinity, and understand NUMA concepts for optimized performance.

Discover how tempdb serves as a workspace for temporary objects and how configuring 4–8 data files reduces lock contention. Learn to place files on a separate drive and manage growth.

Explore how max degree of parallelism and the cost threshold for parallelism determine when SQL Server uses multiple CPUs, guided by estimated subtree cost and execution plans.

Learn how the transaction log records all operations, uses a log sequence number for recovery after failures, and how virtual log files and auto growth settings protect data integrity.

Learn how backups sustain business continuity by defining RPO and RTO, and mastering full, differential, incremental, and transaction log backups along with recovery models and backup devices.

Learn how to back up and restore SQL Server databases, including full and log backups, restore options (recovery, no recovery, standby), and safe practices like renaming before restore.

Learn to automate backups in SQL Server 2019 with maintenance plans and SQL Server Agent, including scheduling full and log backups, destinations, and reporting, plus alternative Task Scheduler options.

Learn to design high availability and disaster recovery in SQL Server 2019 with log shipping, an HDR solution using primary and secondary databases, full recovery model backups, and standby restores.

Explore cloud basics, compare infrastructure as a service, platform as a service, and software as a service, and learn how to log ship and use warm standby for SQL Server.

Discover how eight kilobyte pages corrupt from storage bugs and software, and learn to actively detect and recover with checksum backups and dbcc checkdb.

Enable the dedicated administrator connection to access an unresponsive SQL Server for emergency troubleshooting, then configure remote admin connections, static ports, and firewall rules.