The Complete SQL Server 2019 Database Administration Course

Learn how SQL Server 2019 indexes reduce data pages and boost performance by enabling fast lookups, with a book-index analogy and guidance on which indexes to create.

Master restoring databases to their base structure using restore scripts, and pay attention to the correct file paths and drive locations to ensure the restore succeeds.

Explore how a clustered index builds a single tree with a root, intermediate nodes, and leaves that hold data, and why only one cluster index per table is possible.

Explore the definition and creation of non-clustered indexes, compare them with cluster indexes, and observe how index type affects data organization and page structure in SQL Server 2019.

Explore how a primary key forms a clustered index and how a non clustered index complements it, with key values replicated on every data page and fragmentation implications.

The demo shows that a table scan may read about 4,000 pages, but an appropriate cluster index reduces reads to about 210 pages, changing the execution plan.

This demo shows the table seek, illustrating how index seeks with execution plans speed data access, compare cluster index and heap table scenarios, and discuss when not to force indexes.

Discover how covering indexes speed queries by avoiding key lookups, compared with clustered and nonclustered indexes, using execution plans and SSN filters.

Demonstrates how duplicate indexes harm performance, increase maintenance and disk usage, and why you should avoid creating unnecessary indexes in SQL Server 2019.

Discover how the include clause stores data in the index, saving space and reducing bookmark lookups to boost performance, versus non-include and covering indexes.

Explore how unique indexes and unique constraints affect query plans, and learn to avoid duplicate indexes while examining index usage in SQL Server 2019 database administration.

Consolidate redundant indexes to reduce storage and improve query performance in SQL Server 2019, using last name, first name, and middle initial coverage and execution plan analysis.

Examine how indexing and column order affect query performance by creating cluster and nonclustered indexes, analyzing execution plans, and adjusting index columns to improve response times.

Demonstrates deactivating an index to improve insertion times and reveals which index the query uses. Test scenarios and compare activated versus deactivated indexes, while considering recovery mode and log size.

Explore how a filtered index in SQL Server includes only meaningful values, reduces size, and improves plans by creating and inspecting a date-based filtered cluster index.

Demonstrates how filtered indexes influence query execution plans, showing when an index is used or ignored, how to force the index, and how covering indexes affect results.

Learn how to choose a clustered index for sql server 2019, balancing performance and fragmentation, with guidance on maintenance like overnight rebuilds, default behaviors, and the implications of unique constraints.

Explore how clustering on a NEWID column causes fragmentation and performance degradation, and examine index statistics and page density to understand the impact.

Explore how forcing an index, choosing between clustered and nonclustered indexes, and creating a covering index can improve query performance by reducing reads and favoring seeks over scans.

Over indexing can devastate performance by slowing inserts and increasing maintenance time. Monitor indexes to avoid unnecessary overhead, fragmentation, and disk space usage in highly transactional environments.

In this demo, the sql server 2019 columnstore index enhances performance for large data warehouse queries by using compression and batch mode execution on big tables.

This demo shows how to create an indexed view, first building a clustered index, then a nonclustered index, to materialize a view and accelerate large, aggregated queries in data warehouses.

Explore why fragmentation matters for performance, including its impact on index scans, disk space, and page splits, and how per-index free-space decisions reduce fragmentation and optimize the buffer pool.

This lecture presents an example of fragmentation in SQL Server 2019, showing how page splits after insertions break contiguity between pages and create not contiguous pages.

Demonstrates tracking page splits with extended events in SQL Server 2019, showing how to activate, create, and start extended events and monitor page splits on a cluster index.

Explore how the fill factor (fear factor) affects index pages and fragmentation, showing how 70 percent fill leaves free space to prevent splits, while 100 percent causes splits.

Compare rebuild and reorganize strategies for SQL Server 2019 to reduce fragmentation, choose online or offline options, and decide when to update statistics.

Demonstrates rebuilding an index with abort after wait to manage blocking transactions, using blockers to abort conflicting user transactions and complete maintenance during peak hours.

Optimize SQL Server performance by removing duplicates and consolidating indexes, create custom and clustered indexes for large tables, use covering indexes with includes for critical queries, and rebuild when needed.

Recognize the importance of preventing and addressing database corruption in SQL Server 2019, and learn quick intervention and administration practices to fix corruption and protect data.

Explore two common approaches to SQL Server 2019 corruption and why most issues stem from physical disk sectors rather than memory tests, guiding efficient restoration.

Identify causes of corruption in SQL Server, including storage-related issues, memory corruption from virtual machine snapshots, human error, improper backups, and corruption risks from commands and replication.

This lecture explains that corruption in SQL Server 2019 databases never disappears and remains persistent despite nightly checks, and highlights vigilant monitoring and implications of index creation on detecting corruption.

Demonstrate how Windows reports data read errors, identifies suspect pages, and guides correcting a page such as page 225 within a database.

Demonstrates torn page detection and page checksum modes for validating pages against their headers, exposing anomalies and guiding when to switch default settings via the graphical interface.

Demonstrates using DBCC IND and DBCC PAGE to inspect pages and indexes in SQL Server, highlighting cluster, heap, and nonclustered indexes, page depth, and fragmentation.

Check data consistency to detect corruption, ensuring data integrity with a consistency check, and automate weekly data checks using a job.

Learn to create a job to check data consistency, schedule weekly checks on Sunday, and review results for potential corruption to ensure data integrity.

Learn how checkdb validates the physical integrity of all database objects, checks index validity and page data consistency, and assesses catalog integrity, with notes on usage and performance considerations.

Explore SQL Server 2019 checkdb options, compare physical only checks to full checks, assess data consistency messages, and understand space estimation and snapshot usage for efficient maintenance.

Analyze a worldwide survey on the frequency of checkdb on production databases. Show that many run it without action and many lack awareness of data integrity checks.

Watch a hands-on demo of backup with checksum, detecting a corrupted page (147) and validating data integrity with checks, while noting performance impact and safety considerations.

Demonstrates how to approach repairing corrupted sql server databases, weighing repair versus restoration, and the importance of backups before assessing data loss and integrity checks.

Demonstrate repairing a corrupted cluster index by deactivating and rebuilding it, addressing fragmentation, reassigning pages, and choosing repair or rebuild options to restore index integrity.

Assess whether corruption requires repair or restore, weighing downtime and backup viability. When backups fail, restoration becomes the last resort, and repair may save time depending on the situation.

Demonstrates restoring a corrupted SQL Server 2019 database from backups, preserving the active log portion and validating integrity during the restore process.

Explore the different database states in SQL Server, from online and offline to restoring, recovery mode, suspect, emergency, and repair modes, and how log files affect availability.

Demonstrate corruption using the XVI32 editor by dropping and recreating a database, revealing damaged data pages and the impact of security mode. Warn about granting access and data manipulation risk.

Detach a database, recreate a missing log file, and reattach to recover access, demonstrating log file recovery and database accessibility.

Demonstrates using emergency mode to repair a damaged database, including switching to single-user mode, running repair data, and evaluating when emergency mode fully resolves corruption.

Demonstrates repairing a corrupted SQL Server 2019 database by attaching a damaged file, using offline and emergency modes, and swapping in a copy.

Understand that corruption can occur at any time, affecting disks, snapshots, and keys. Use daily backups and tools to detect, resolve corruption, and regularly check and protect the system database.

Demonstrates classic SQL Server backup and restore workflows, including full backups to disk, optional differential backups, and restoring a database from a backup file.

Watch a practical demo of backup and restore logs in SQL Server 2019, including simulating a crash, restoring the database, and validating recovery steps.

Learn how differential backups use a full backup to speed restores, save time, and optimize production backup strategy.

Demonstrate backing up with init to initialize media and stack backups, including naming each backup and restoring from a specific backup with correct syntax.

Discover how the copy-only backup option preserves the backup chain, demonstrate full, differential, and copy-only backups, and explain how to restore safely in production.

Demonstrates restoring a SQL Server database by moving directory files and replacing existing databases. Shows how to monitor restoration progress and identify the database name involved during the operation.

Explore the different recovery models in SQL Server, including full and simple, and learn how transaction logs, backups, and checkpoints affect point-in-time restores and disk space management.

Demonstrates how a tail log backup complements full backups and log backups to recover and restore a SQL Server 2019 database, via a practical demo.

During this demo, learn how backup with checksum detects page corruption and preserves data integrity in a SQL Server 2019 environment.

Watch a live demo of restoring a single page in a large multi-terabyte database, illustrating a powerful restoration technique that saves hours.

Demonstrates a point-in-time restore using stopat, recovering data from a 10 p.m. backup to a specific moment and outlining the steps to restore production data accurately.

Demonstrate restore with standby to track a restoration, set a stop point, and recover data to a precise time using transaction log backups in SQL Server 2019.

Demonstrate restoring a SQL Server 2019 database from a snapshot, showing how to create a snapshot and perform the restoration in a practical demo.

Demonstrate restoring a SQL Server 2019 database from a specific log sequence number (LSN) using backups, simulating a crash, and tracing the restore steps from the pre-drop LSN.

Explore how compression interacts with backup integrity in SQL Server 2019, testing checksum options, verifying backups, and detecting corruption to ensure reliable restoration.

Checkpoint in SQL Server engine writes modified pages from memory to disk, using the memory pool and disk to ensure data durability.

Demonstrates how to consult backup and restore metadata in SQL Server 2019, using system views and scripts to view backup history, media, location, size, and verify validity.

Demonstrates backing up to several files and multiple destination sites to distribute workload and improve backup performance for SQL Server databases.

Demonstrates backup compression and mirroring in SQL Server 2019, showing performance tradeoffs, storage savings, and how to configure a mirrored backup.

Demonstrates configuring backup options in SQL Server 2019, focusing on buffercount and maxtransfersize. Explains how memory usage affects backup duration and guides testing different values to optimize performance.

Emphasize backing up the system database, especially the master database, to prevent disaster when corruption or failures occur; the lecture demonstrates restoring system databases and the overall system.

Restore the master system database from a backup. Restart the SQL Server, reconnect, and validate access after restoration.

Observe a step-by-step demo to restore all system databases, set up and create a test database, archive and reattach system databases, and verify the restored instance is accessible.

Explore essential backup and restore strategies, define recovery point objective and recovery time objective, and emphasize regular backups to minimize data loss and ensure quick recovery.

Explore configuring and modifying databases, compare options for database creation, identify common pitfalls, and learn how to activate practical options during demonstrations.

Explore how to define data and log files when creating a SQL Server 2019 database, including file locations, sizes, and multi-file configuration for performance.

Define and create a filegroup in SQL Server 2019 to distribute data across multiple disks, configure primary and secondary filegroups, and improve database performance through strategic placement.

Activate the contained database option to isolate a user database from the master, then demonstrate containment, partial type, and single-database access via connection properties.

Explore how to assess and change the database compatibility mode to match applications and restore scenarios, ensuring appropriate performance and compatibility across versions.

Learn how to safely offline a SQL Server 2019 database and use emergency mode to recover from corruption, then bring the database back online and resume operations.

Master single-user and restricted-user modes in the SQL Server 2019 database administration course, switching databases to single-user, restricted, and read-only states. Learn to manage security, connections, and access during maintenance.

Learn to detach and reattach databases using the attach syntax, verify attachments, and manage data and log file associations for smooth database creation by attaching files.

Disable auto close and auto shrink; enable auto create statistics and auto update statistics to keep stats current and improve performance.

Explore target recovery time and how checkpoints affect SQL Server 2019 databases, including default zero intervals, online vs base database behavior, and adjusting checkpoint frequency to minutes.

Explore the checkpoint mechanism in SQL Server: how memory pages are modified, cached, and written to disk by the internal checkpoint, typically every 60 seconds, with a manual demonstration.

Discover the trustworthy option for a database, how to activate and verify it, and the security implications of privileged access and runtime context.

Learn to change the database owner, verify ownership and rights, create a login, and test permissions while being careful with granting rights and using strong passwords.

Compare database recovery models and learn how transaction logs, log backups, and disk space affect restores to a specific time.

Explore maxdop, the max degree of parallelism in SQL Server, showing how default zero uses all CPUs and how to set a value such as four for large queries.

Learn how to change collation in SQL Server, selecting case sensitivity and accent sensitivity for your data, and how to apply and verify settings when creating databases.

Explore how set ansi_null and set quote_identifier influence query behavior through practical demonstrations, including creating tables, triggers, and observing value returns with conditional selections.

Explore changing configurations limited to databases to influence the database's query optimizer, cardinality estimation, and first execution plan, including cache clearing options.

Demonstrate how to enable change tracking on a database for a 24-hour retention period, track changes in a category table, and compare it as an alternative to change data capture.

Demonstrates how to activate and manage a broker service within a database, including creating messages, encrypting content, sending to contacts, monitoring queues, and deactivating the broker.

Demonstrate why enabling db chaining across databases risks security, explain the cross-database property and trust, and show turning it off by default per Microsoft guidance.

Activate and explore the Query Store in SQL Server 2019 to identify high consuming queries, tune execution plans, and monitor performance with options like maximum execution plans and storage.

demonstrates accelerated recovery in sql server 2019, comparing databases with and without the option, showing faster crash recovery and smaller recovery logs for high-volume workloads.

Demonstrates autogrow settings in SQL Server by creating databases, inserting 11 million rows, and monitoring growth performance with no primary key or index, highlighting timings and configuration tradeoffs.

Learn to clone a database in SQL Server 2019 with a simple one-command syntax, using source and target names, and note you cannot clone a master database.

Explore the purpose of isolation modes in SQL Server to manage concurrent transactions and prevent data inconsistencies. Learn how different isolation levels prevent anomalies like dirty reads and ghost data.

Explore read committed mode in SQL Server by observing how locks prevent reading uncommitted data, and how commits and rollbacks reveal or revert changes.

Demonstrates read uncommitted isolation and dirty reads in SQL Server 2019. Explains why this least restrictive mode can cause data anomalies during transactions.

Explore the repeatable read isolation mode in SQL Server 2019 and see how it prevents non-reproducible data during concurrent reads and updates.

Demonstrate how the serializable mode of SQL Server 2019 restricts concurrent operations, with a hands-on demo of begin, insert, select, and commit, and provide guidance on alternative syntax.

Explore snapshot isolation mode in SQL Server 2019, using versioned data to avoid locks and ghost reads in transactions, and show its impact on tempdb and storage.

Learn how the XABORT command interrupts a current transaction, triggers an execution error, and performs a full rollback, and explore on conflict behavior with foreign keys in transaction management.

The demo shows two concurrent updates on two separate tables causing a deadlock, and the server resolves it by terminating the less costly transaction.

Explore the SQL Server 2019 uplock command with a hands-on demo of update, commit, and select operations. See how insertion behaves across transactions.

Evaluate the isolation mode carefully, balancing snapshot safety and serializable options, then test and approve changes with the team before altering the default, to protect data access.