Complete Microsoft SQL Server Database Administration Course

Learn how to download, install, and configure Windows to support a complete Microsoft SQL Server database administration course.

Prepare your lab by meeting prerequisites: admin rights, six gig disk, four gig memory, 64-bit processor, and Windows Server 2016 or 2019 with matching SQL Server edition.

Set up this course lab on a Windows 10 host with SQL Server Express for limited resources, or use virtualization to run Windows Server and SQL Server Enterprise.

Explore virtualization to see how a single physical server hosts multiple operating systems through a virtualization layer, maximizing RAM and CPU utilization and enabling several OSes to run simultaneously.

Learn to verify system resources and disk space, download and install VMware Workstation Player, and manage licensing differences between version 17 and the free version 16 for non-commercial use.

Download and install Oracle VirtualBox if you choose it; select the correct platform and run the setup wizard, noting its optionality and comparison to VMware Workstation Player.

Create and configure a Windows Server 2016 virtual machine using VMware Workstation Player or Oracle VirtualBox; allocate 4 GB RAM, 1–2 CPUs, 20–60 GB disk, and bridged networking.

Shows how to fix the EFI network timeout error in VMware Player by switching the VM firmware from EFI to BIOS and using a Windows Server ISO.

Download Windows Server 2019 ISO from the Microsoft Evaluation Center, attach it to VMware Workstation Player 16, and perform a custom install with desktop experience and password setup.

Learn to manage virtual machine resources and settings, including memory, processors, disk expansion, network adapters, display, and host time synchronization, plus renaming, repurposing, and auto login with VMware tools.

Download, install, and configure Microsoft SQL Server in a Windows Server 2016 lab environment. Review prerequisites, install SQL Server Management Studio, and set up the Adventureworks database.

Explore available SQL Server versions and the differences between mainstream and extended support, then choose the course’s recommended option of installing 2016, 2019, or 2022.

Identify prerequisites for Microsoft SQL Server 2016 installation, including hardware requirements, 64-bit support, Windows Server 2012–2019, and production guidance for separate data, log, and tempdb disks.

Identify sql server 2022 installation prerequisites: six gb disk, 1.4 ghz cpu, memory 512 mb to 4 gb, ntfs or refs, x64 on Windows 10 or Windows Server 2016+.

Install Chrome on a Windows Server virtual machine by adjusting Internet Explorer security settings and enabling downloads, then download the Adventure Works database for SQL Server administration.

Learn to download sql server 2016 and sql server management studio 2016 inside a virtual machine, choose the ISO image, verify integrity, and shut down for a clean boot.

Install SQL Server 2016 in a lab by creating separate folders for data, log, tempdb, and backups, then run planning and installation checks, configure default instance, authentication, and data directories.

Learn to install and configure SQL Server 2019 in a lab and production environment, covering hardware requirements, data and log file placement, tempdb configuration, and security settings.

Download and install SQL Server 2022 on Windows, using developer edition for learning. Compare production versus test environments and configure the instance, mixed mode authentication, and data directories.

Install SQL Server Management Studio to connect to lab MS SQL server using Windows or SQL Server authentication, explore databases (master, model, msdb, tempdb), and run your first query.

Install SQL Server Management Studio to connect to SQL Server, manage logins, create databases, and schedule jobs; download SSMS 18.12.21 (GA) and note the installer also includes Azure Data Studio.

Download and install SQL Server Management Studio 2019, connect to SQL Server 2022 using Windows authentication, and run queries to explore databases, security, and server objects.

Download and install the AdventureWorks sample database, restore it in SQL Server, and explore its architecture with tables, views, stored procedures, and security while running your first select statement.

Download and install the AdventureWorks 2022 database by restoring its backup in SQL Server using SQL Server Management Studio, then verify data by querying the top 1000 rows.

Explore data and database fundamentals, including tables, columns, rows, keys (primary, foreign, unique), transactions, and asset properties, and learn normalization while creating your first database and table and inserting data.

Explore how data spans raw measurements to information and how database columns enforce data types, such as numbers for a telephone field.

Define a database as an organized collection of structured information. Compare it to file systems and spreadsheets, showing how a database system handles terabytes and enables multiple users.

Discover how data is stored in tables inside a database, the basic storage unit, and how structured data is organized as tables within a folder rather than the database itself.

Explore how relational databases use tables to store data with unique names, define data with columns of specific types, and represent records as rows, with normalization introduced for future lessons.

Explore how keys uniquely identify records in relational databases and prevent duplicate data. Learn how student ID and composite keys enable precise queries and how foreign keys relate tables.

Explore how candidate keys identify records and how a primary key is chosen, with student id or email as examples; review foreign keys, unique and alternate keys, and composite keys.

Explore the relational database model, its table-based structure and key relationships, and learn how a relational database management system uses sql to create, update, administer, and access data.

Explore how a database transaction groups SQL statements into an atomic unit that is either committed or rolled back, guided by ACID properties: atomicity, consistency, isolation, durability.

Explore how normalization reduces data redundancy and maintains data integrity by applying 1NF, 2NF, and 3NF in a practical design, illustrated with the order and customer tables.

Create your first database and its table in Microsoft SQL Server Management Studio, then populate the table with data using insert statements and view the results.

Explore the basics of SQL commands, define what a SQL statement is, learn its types and usage, see examples, and perform lab work to run and test statements.

Explore how data manipulation language (DML) handles select, insert, update, and delete statements with practical examples. Understand how where conditions guide retrieval and updates on the student table.

Learn how data definition language shapes database schemas with create, alter, drop, and truncate statements to define and manage tables, schemas, and objects, including procedures and functions.

Explore data control language (DCL) in SQL Server by applying grant and revoke statements to manage permissions on tables and views, including grant option and public access.

Explore t-sql transaction control language (tcl) commands that group dml statements into transactions, including begin transaction, commit, rollback, and savepoint, with practical examples.

Explore how to query and manipulate data with SQL commands, focusing on select statements, expressions, where, order by, group by, joins, subquery, and insert, update, delete operations.

Learn to create tables and temp tables in MySQL with create table syntax, primary keys, data types, and constraints, and compare regular tables to session-based temporary tables in temp db.

Explore how SQL server views act as virtual tables that pull data from multiple tables without storing it. Learn to create views with select and joins for reuse.

Master the select statement in SQL by learning how to retrieve data using from, where, group by, having, and order by, and how to select specific columns or all columns.

Explore SQL operators, expressions, and conditions, including arithmetic, relational, and logical operators, with examples of boolean, numerical, and date expressions used in where clauses.

Learn to apply where, order by, group by, and having to filter, sort, and aggregate data in SQL Server with practical examples. Get hands-on with practical SQL queries.

Learn how to join data from two tables using joins to analyze customer and transaction information in a relational database.

Explore inner, left, right, and full joins in SQL Server, learn how to join tables on a matching column, and examine practical examples with nulls and aliases.

Learn subqueries, inner or nested queries, in where clauses, with rules for single-column selects and the in operator for multiple rows, plus practical lab examples using Adventureworks tables.

Master the insert statement in SQL Server to add rows, specify columns, insert multiple rows, and retrieve data from other tables, including select into and the output clause for auditing.

Master the update statement in detail, using the set, where, and from clauses, updating single or multiple rows, and even updating from another table via joins.

Download and install Windows Server 2016 in a virtual machine, attach the ISO image, install VMware Tools, rename the host, and configure time settings for SQL Server deployment.

Master the delete statement in detail, a dml command that removes rows. Use where, from (optional), top, subqueries, and joins, and protect data with transactions in dev and production environments.

Understand the difference between delete and truncate: truncate has no where clause and deallocates pages, while delete logs rows and can be rolled back.

Discover what a stored procedure (SP) is in Microsoft SQL Server, how it accepts parameters, and why it boosts performance, security, and network efficiency across applications.

Explain what a function is in SQL Server, how it accepts input and returns a value, and distinguish built-in from user-defined scalar and table-valued functions.

Learn what a trigger is in SQL Server and how it automatically runs on table events. Examine DML, DDL, and logon triggers, and see create trigger syntax and audit examples.

Discover how an index boosts SQL Server performance by avoiding full table scans. Understand heaps, clustered and non-clustered indexes, and the B-tree structure with root and leaf nodes.

Understand clustered and non-clustered indexes in SQL Server, including how data and pointers are stored, plus design considerations like composite and unique indexes and performance tradeoffs.

Explore how SQL Server index fragmentation degrades performance, distinguishing internal and external fragmentation, and learn to diagnose with DMVs and fix by rebuild or reorganize in labs.

Learn core SQL Server database administration by exploring management tools, SQL Server Management Studio, and SQL Server Configuration Manager, and examining system databases like MASTER, TEMPDB, and MSDB.

Explore SQL Server Management Studio, SQL Server Configuration Manager, SQL Server Profiler, and Database Engine Tuning Advisor to administer, monitor, and optimize the SQL Server environment.

Learn to use SQL Server Management Studio to connect to database engines, explore server and database properties, run queries, view execution plans, and perform core database admin tasks.

Master SQL Server Management Studio by learning to generate and interpret execution plans, compare estimated and actual plans, and monitor activity, logs, backups, and maintenance tasks.

Explore SQL Server Configuration Manager, a graphical interface to manage SQL Server services, login accounts, startup parameters, and network protocols. Learn to configure TCP/IP and change the default port 1433.

Explore Microsoft SQL Server system databases, their critical roles like master, tempdb, msdb, resource, model, and distribution, and learn the do's and don'ts to keep each instance healthy.

Preserve master database as the core repository for system and instance metadata; newer versions use a hidden resource database, and keeping current backups is critical to SQL Server startup.

Explore tempdb, the system database that holds temporary user objects (global/local temp tables, table variables, cursors) and internal objects, work tables, and the version store.

Msdb is a system database used by SQL Server Agent to manage alerts and jobs and to store backup, restore history, and database mail, with a default simple recovery model.

Explore model and resource system databases in sql server, seeing how model serves as a template for new databases, how resource holds system objects read-only, and how upgrades leverage it.

Take a deep dive into MS SQL Server, covering management tools, page architecture, and the transaction log, then explore SQL Server and database architecture.

Explore pages and extents as the fundamental storage units in SQL Server, including eight-page extents, and how page architecture and PFS guide efficient disk I/O.

Explore the Microsoft SQL Server architecture, including protocol layer, relational engine, and storage engine, and how query processing uses the cmd parser, optimizer, query executor, and buffer and transaction managers.

Explore Microsoft SQL Server database architecture, including primary data files (mdf) and transaction log files (ldf), secondary data files, and file groups, with strategies for performance and file group backups.

Explore transaction log architecture and write-ahead logging, including ldf files, virtual log files, and log sequence numbers, and how checkpoints enable point in time recovery.

Master Microsoft SQL Server backup and restore, including full, differential, and transaction log backups, recovery models, tail log backups, and point-in-time restores, plus dbcc checkdb.

Explain what a database backup is, why it's essential for DBAs, and media used for backups from on-site to cloud, USB, tape, and LAN storage, with policy and restore testing.

Explore recovery models in SQL Server and their impact on backups and restores, including simple, full, and bulk-logged options.

Understand how full backups capture the entire database and the active transaction log to enable a consistent restore, and how simple, full, and bulk log recovery models shape backup strategies.

Differential backups capture changes since the last full backup, using that full backup as the base. Restoring requires first applying the full backup, then the differential.

Explore how transaction log backups require a full backup, shorten work loss, and truncate logs, and how log backup chains, tail-log backups, and the full recovery model enable point-in-time recovery.

Learn to perform full, differential, and transaction log backups in SQL Server using GUI or scripts, with options for file groups, split backups, and verification.

Learn how to restore a SQL Server database from full, differential, and log backups, using complete, file, or page restores under different recovery models for consistent online or offline recovery.

Learn how to restore full, differential, and transaction log backups in SQL Server, using no recovery for staged restores and recovery for final online databases.

Learn to perform a point in time restore in SQL Server by restoring a full backup, a differential backup, and log backups to a stop time.

Demonstrate backup and restore workflows using full, differential, and transaction log backups, including tail-log backups. Apply full, differential, and point-in-time restores under a full recovery model.

Explore how the DBCC checkdb command validates the database’s logical and physical integrity, running allocation, table, and catalog checks, with options for no info messages and physical only.

Identify corrupted pages with checkdb in SQL Server, then restore only those pages from a full backup, supported by a robust backup strategy and page level recovery.

Restore the SQL Server master database from backups to recover logins and data after corruption. Apply single-user mode and dbcc checkdb to verify integrity across online or offline recovery scenarios.

Create maintenance plans in SQL Server Management Studio to automatically back up databases, rebuild or reorganize indexes, verify integrity, and manage backups with scheduled weekly, daily, and hourly subplans.

Explore SQL Server security models, authentication modes, logins, server roles, and database users with deny and revoke permissions, plus a hands-on lab to apply the concepts.

Understand the MS SQL server security model by distinguishing server logins from database users, and applying granular permissions from sysadmin access to table and stored procedure rights.

Explore Windows authentication and SQL Server authentication, learn how to enable mixed mode, and understand the authentication and authorization flow in SQL Server.

Explore SQL Server security hierarchy, focusing on principals, logins, and permissions, including Windows and SQL Server logins, server roles, and user-defined roles for authentication and authorization.

Map server logins to database users, including Windows and SQL Server logins, and manage permissions with fixed and user defined database roles in AdventureWorks.

Explore SQL Server permissions, including grant, deny, and revoke, and how they apply to logins, users, schemas, and securables like tables, procedures, and functions.