Comprehensive SQL Server Administration Part 1

Explore the three database backup types—full, differential, and transaction log—and how their ranges differ: cumulative differential backups vs. sequential transaction logs, for disaster recovery planning.

Use the latest full backup, the latest differential, and all transaction log backups since then to recover from disaster and minimize data loss.

Identify the appropriate SQL Server restore option—recovery, no recovery, or standby—based on the latest full and differential backups and transaction logs, noting online status and read/write accessibility.

Learn how t-log backups enable point-in-time restores and design a weekly full, daily differential, and frequent log backup strategy to minimize data loss.

Always perform a full backup before any differential or log backup, since differential backups reference the last full backup; store on iDrive and name with .back or .trn.

Restore a database by applying full, differential, and transaction log backups to a point in time, ensuring a continuous log sequence using LSNs, with recovery, no recovery, or standby options.

Demonstrates restoring a SQL Server database from full, differential, and transaction log backups, with recovery, and shows how a missing log breaks the chain and prevents restoration.

Demonstrates restoring a SQL Server database with full, differential, and transaction log backups, highlighting the importance of a complete backup chain and standby mode for disaster recovery.

Explore disaster recovery exercises for SQL Server administration, analyzing how to restore from full, differential, and transaction log backups across scenarios and achieve point-in-time recovery.

Explore configuring and monitoring database maintenance tasks, permissions, and disaster recovery to keep SQL Server performing with backups, index maintenance, statistics updates, integrity checks, and cleanup.

Create maintenance plans in SSMS, schedule backups (full weekly, differential daily, log), and optimize indexes by reorganize or rebuild based on fragmentation levels, updating statistics and performing integrity checks.

Learn how to configure GUI maintenance tasks for SQL Server, including reorganizing indexes on user databases, updating statistics with full scans, and scheduling full backups with a 14-day retention.

Learn to install Ola Hallengren's maintenance scripts to automate backups, integrity checks, and index maintenance, then configure parameters and create jobs for system and user databases.

Use Ola Hallengren maintenance scripts to back up system and user databases, run integrity checks and index optimization, and manage differential and transaction log backups with scheduling and manual runs.

Complete the practice exercises on VM zero one and VM zero two to analyze what you learned, demonstrating understanding of the module in this comprehensive SQL Server administration course.

Explore high availability solutions in SQL Server, including log shipping, mirroring, clustering, and always on availability groups, and differentiate high availability from disaster recovery while reviewing RPO, RTO, and SLA.

Explore the pros and cons of log shipping, including multiple secondary servers, read only on secondaries, licensing versatility, and limitations like manual failover, single database per setup, and potential lag.

Configure log shipping by scheduling frequent log backups, provisioning secondary servers, and choosing restore options (standby or no recovery) to keep secondary databases synchronized and accessible.

Disable the RLS backup job, verify all backed up files are copied and restored; manually copy and restore any missing backups, then take the tail log backup and fail over.

Assess the pros and cons of database mirroring, including automatic failover with a witness server, real-time synchronization, no recovery mode, and single secondary limitations.

Configure database mirroring with principal, mirror, and witness using synchronous or asynchronous modes, enable automatic failover with a witness, and open port 5022 on all servers via inbound/outbound firewall rules.

Learn how to configure database mirroring in SQL Server, including starting mirroring, monitoring primary and secondary synchronization, and handling automatic and manual failover with a witness server.

Analyze always on advantages and disadvantages, including synchronous versus asynchronous commit modes, automatic versus manual failover, and enterprise versus standard edition limits, with read-only secondary and distributed availability groups.

Describe how always on availability groups provide database-level redundancy with failover only for grouped databases, explain edition limits, copy-only backups, and risks of orphaned users.

Configure always on availability groups by forming a failover cluster and creating the availability group with databases in full recovery and a backup, then choose synchronous or asynchronous mode.

Shows configuring a Windows failover cluster for Always On availability by validating nodes, using cluster manager, and creating the cluster with an IP and Active Directory entry.

Enable always on on each node via configuration manager, restart services, and create an availability group on a chosen primary, ensuring databases are in full recovery and backed up.

Learn to configure an AlwaysOn availability group by adding a secondary replica, selecting synchronous commit with automatic failover, configuring endpoints, a listener, and backup preferences.

Configure AlwaysOn quorum using the failover cluster manager, select a file share witness placed on a non-cluster node, grant access, and verify health and quorum operation.

Describe how replication copies articles from the publisher to subscribers via a distributor, using publication, distribution, and subscription databases; explain push and pull subscription flows.

Explore replication topology with publisher, distributor, and subscriber, showing push and pull data flow, initialization via snapshot and distribution agents, and transactional replication using the logarithm agent to propagate changes.

Explore transactional, snapshot, and merge replication in SQL Server, starting with snapshots and using agents (snapshot, log reader, distribution, queue reader) to support unidirectional or bidirectional topologies, noting conflicts.

Explore replication agents in SQL Server, including the snapshot, distribution, log reader, and merge agents, and how push and pull subscriptions shape data flow between publisher, distributor, and subscriber.

Compare transactional and snapshot replication, highlighting primary key requirements, real-time versus periodic updates, latency differences, and bidirectional versus unidirectional topologies for SQL Server and Oracle.

Distributes articles from a publisher to multiple subscribers, making them readable and enabling bidirectional updates, while lacking automatic failover and risking large initial snapshots and out-of-sync data.

Configure transactional and snapshot replication by enabling the replication feature and setting up distributor, publisher, and subscriber across lab machines; note that replication supports only tables with a primary key.

Configure transactional replication by designating vm01 as distributor, creating the distribution database, and granting sysadmin to the accounts on vm01 and vm02. Ensure the snapshot folder uses a network path.

Configure transactional replication by enabling distribution, creating a publication from the publisher database, selecting publishable articles with primary keys, setting the snapshot agent, and preparing the subscription.

Configure transactional replication by creating a publication and subscription and selecting push or pull with a distribution agent. Troubleshoot initialization with the snapshot agent and resolve snapshot path access issues.

Configure transactional replication by granting the snapshot and log reader agents necessary permissions, reinitializing subscriptions, and validating distribution and snapshot processes between the publisher, distributor, and subscribers.