SQL Server Database Administrator Part-1

Explore SQL Server configuration options after installation, including memory, data and log file locations, to boost production performance, enable easier troubleshooting and root-cause analysis, and align with best-practice disk placement.

Configure data and transaction log file locations for SQL Server by placing data and log files on separate folders and granting permissions. Restart the instance and validate with a database.

Configure the SQL Server backup folder path by adjusting instance properties, using a default or network location, and verify the setup without restarting the instance.

Investigate logs, startup events, and backup details to troubleshoot SQL Server incidents. Learn to manage locks, gridlock, and the sequencer to recover blocks and monitor system health.

Explore SQL Server patching practices, including hot fixes and cumulative updates, and learn how to test and deploy updates to manufacturing with Microsoft support.

Demonstrate patching SQL Server by downloading updates, applying them, and restarting the machine as a best practice. Verify all databases and enable applications for users post-patch.

Configure remote connections for SQL Server, compare local versus remote protocols, enable the CPA to permit remote access, and verify properties on the production machine.

Demonstrates configuring remote connections to SQL Server by enabling necessary services, adjusting Windows Firewall, selecting port numbers, and verifying cross-machine connectivity across instances.

Explore how transactions handle update and select commands in SQL Server, detailing memory, data pages, the sequencer, and transaction log with commit, checkpoint, and logical vs physical I/O.

See how transactions execute in SQL Server by performing inserts, selects, and checkpoints, and observe dirty and clean pages as the checkpoint process runs.

Configure memory by setting minimum and maximum server memory, use the installation wizard and web dashboard to monitor usage.

Configure worker threads in SQL Server to optimize query processing and background tasks. Learn how max worker threads can be dynamically adjusted across instances, with hardware-aware defaults.

Explore the query life cycle from user submission to execution, including the relational engine, optimizer, and execution plan. Learn how waiting and suspended states, sessions, and background processes affect performance.

Configure the dedicated administrator connection (DAC) to troubleshoot memory issues on a SQL Server, identify memory-heavy queries, and manage buffer cache and per-connection memory settings.

Learn how to configure a dedicated service account for SQL Server, manage permissions, and troubleshoot startup issues using Windows services and SQL Server Configuration Manager.

Explore basic database concepts and essential configuration for performance, while reviewing blockchain references and how connections, memory, and sequences relate to SQL Server administration.

Explore system and user databases, understand how SQL Server stores data, and learn to move databases between locations while applying practical troubleshooting steps for modern SQL Server environments.

Explore system databases and user-defined databases in SQL Server, and learn how the master database serves as a startup database while modern databases act as templates for new databases.

Explore database architecture by examining default creation, file structures, data points, and grouped data in stages. Compare log-first versus data storage and discuss extents, growth phases, and data organization.

Explore how the transaction log records every database change, enabling recovery from crashes, tracking committed and uncommitted transactions, and supporting checkpoint-based recovery.

Demonstrates how transaction log files capture database activity, including begin transaction and log entries with sequence numbers, and how to inspect log records to trace changes across sections.

Investigate how transactions are logged and retained under different recovery models, including full and simple, and how checkpoints influence log truncation. Examine log backups and space usage to manage growth.

Identify the causes of transaction log growth, monitor log space, and apply checkpoint and recovery steps to manage log growth in production databases.

Explore how data files and pages organize SQL Server storage, including extents, free space management, page structures, and sequencing for efficient big data handling.

This demo shows working with pages in a SQL Server database, including data pages, indexing, and how pages affect table storage.

Learn about SQL Server page types, including data pages and index pages, and how pages like boot and free space management, plus server configuration, impact database performance.

Explore how to manage SQL Server database states, using offline and online transitions, backups, restores, and recovery checkpoints to troubleshoot and bring databases back online safely.

Learn how to move SQL Server databases using detach and attach, including preparing, detaching the database, moving files to a new location, and reattaching with appropriate permissions.

See how to move SQL Server databases using offline and online methods, including detaching and taking offline, then relocating data to a new location while preserving data integrity.

Demonstrates moving the master database by relocating its data and log files, updating startup parameters, and restarting the server while handling system databases.

Learn how to move the MSDB database in SQL Server by stopping the server, moving files to a new folder, updating the master database, and verifying permissions.

Learn to recover a sql server database from suspect state using backups, log restores, and emergency mode, restoring from the last backup and resuming multi-user access with no data loss.

Diagnose how large transactions and lock-file rollbacks push a database into suspect state, and examine suspect mode entry and Microsoft references for 2012 and 2014.

Learn how to rebuild the master database in SQL Server by using full backups, restoring from an existing master, and bootstrapping system databases to a fresh master.

Explore SQL Server security overview with authentication options, database and server level security, auditing, and step-by-step permission control from login creation to user mapping across multiple databases.

Explore the three-step process to set up access in SQL Server: create a login, create a database user, and grant permissions.

Demonstrates creating logins and users and granting permissions using the GUI in SQL Server. Explore database-level security, role assignment, and table-level permissions to manage access efficiently.

Explore windows authentication and mixed mode in sql server, detailing how same-domain windows users connect, log in, switch modes, and manage domain-based logins.

Learn to create Windows logins and group logins in SQL Server using Windows authentication, configure domain settings, and grant permissions for efficient database administration.

Explore server roles and privileges, and how different rules shape database security and permissions. Learn security best practices for server objects, processes, and import/export operations.

Demonstrates setting up and managing SQL Server server roles, configuring database access, and understanding how default databases and sysadmin roles govern user permissions.

Explore how to create and apply custom roles in a SQL Server database, configure permissions for different users and views, and grant access across objects.

Learn how to implement auditing in SQL Server, capture database events, and review who performed actions. Configure audit folders, select events such as deletions, and secure log information for compliance.

Demonstrates how to identify and resolve orphan users in SQL Server by examining login and user mappings, backups, and security considerations in log shipping environments.

Discover how to create a user with a password in a SQL Server environment. Explore login and permission settings at the database level and understand practical security considerations.

Explores database level roles and security considerations, outlining how access and safeguards are applied within a SQL Server environment to manage data across the database.

Shows how to create a login and a database user, assign a database role, grant permissions to the role, and test access in the Adventure Works database.

Apply policy based management to enforce common standards across all SQL Server databases, create conditions, and evaluate policy impact on security.

Apply row level security (RLS) by implementing security policies and predicates within the database to control access and safeguard data.

Explore dynamic data masking (ddm) in SQL Server and masking functions to protect personal data by default while enabling controlled access via permissions and custom masks.

Explore checkpoint sequences and how they affect database behavior, and examine common restoration issues to establish foundational concepts for SQL Server database administration.

Explore how SQL Server checkpoints manage changes and logging within a simple recovery model for reliable recovery.

Learn about database recovery models and the importance of the transaction log for restoration. Explore full recovery, differential backups, bulk operations, log shipping, and checkpoint behavior in modern production databases.

Explore recovery models in SQL Server, comparing full, simple, and bulk-logged options, and learn how backups, checkpoints, and log chains influence data recoverability and point-in-time restores.

Explore database backups in SQL Server, comparing physical and logical backups, learn when to use full backups, how to back up to tape, and how backups support restoration.

Explore the three core backup types: full backups, differential backups, and transaction log backups, and learn manual creation and scheduling via maintenance plans to protect and restore SQL Server databases.

Learn to perform and verify SQL Server backups—full, differential, and transaction log—adjust recovery models and set up a maintenance plan.

Create and schedule a maintenance plan that performs full, differential, and transaction log backups for user and system databases. Configure per-task or shared schedules, destinations, and email notifications.

Master full, differential, and log backups, including file group backups, with copy-only options to avoid disturbing schedules. Explore how differential backups use the differential bitmap and how compression optimizes storage.

Explore advanced backup types in SQL Server, including compression, no compression, completion options, splitting backups into multiple files, and myriad backups, balancing space and recovery performance.

Learn to restore an existing database from backups, including compatibility analysis, creating a database from backups, applying full and log backups, and choosing recovery options.

Learn how to take full and transaction log backups, restore with or without recovery, and relocate files to bring a database online after restoration.

Demonstrates restoring a SQL Server database from full and log backups, setting data and log file paths, and using no-recovery and recovery steps to bring the database online.

Explore the three recovery states: recovery, no recovery, and standby during SQL Server restoration, including when to bring a database online, allow read-only access, and handle committed and uncommitted transactions.

Explore tail log backups and restores to recover a damaged database, using the latest full backup, differential backups, and log backups to bring the database online.

Expose the difference between a scheduled transaction log backup and a tail log backup in SQL Server, noting how backups truncate the log and tail backups recover after crashes.

Learn how checksum algorithms detect page corruption during SQL Server backups. Explore how checkpoints read and compare page checksums, and how corrupted pages affect backup success and data integrity.

Explore how to create logical backups by generating scripts that capture selected database schemas and data, tailor scripts for destination servers, and verify script execution.

Learn essential SQL Server administration concepts, including database security, user permissions, database restoration and moving databases, system databases, suspect mode, and checkpoints and recovery models.

Explore essential sql server database administrator skills, including high availability, downtime minimization, disaster recovery, monitoring, and troubleshooting, with hands-on guidance on replication, cross-location branches, and upgrade paths.