Comprehensive SQL Server Administration Part 1

Name: Comprehensive SQL Server Administration Part 1
Rating: 4.1 (37 reviews)

Introduction to SQL Server, Authentications, RBAC, Database Maintenance, Disaster Recovery, and High Availability

Created byAdewale Adeniji

Last updated 12/2024

English

English [Auto],

What you'll learn

Microsoft SQL Server, Versions, Editions, Database Types, Configuration Manager and Management Studio
Authentication Types, Role-Based Access Control (Server and Database Roles) and the Principle of Least Privilege
Industry Standard Disaster Recovery Strategies Using RPO, RTO and SLA
Database Maintenance Tasks (Database Backup, Index Maintenance, Statistics Update and Database Integrity Check)
High Availability Solutions such as Log Shipping, Mirroring, AlwaysOn Availability Group and Replication

Course content

8 sections • 84 lectures • 11h 57m total length

What is SQL Server?1:22
History of Microsoft SQL Server and why companies adopt it.
The Evolution (Versions) of SQL Server8:30
Learn how Microsoft SQL Server has evolved since 2008 and its different versions
The Editions of SQL Server6:56
Learn the different editions available in Microsoft SQL Server
The SQL Server Configuration Manager10:02
Explore the interface of SQL Server Configuration Manager
The SQL Server Management Studio2:01
Explore the interface of SQL Server Management Studio
Demo: SQL Server Configuration Manager14:22
Learn the different configurations that are managed on the SQL Server Configuration Manager
Demo: SQL Server Management Studio Part 18:05
Explore the interface of SQL Server Management Studio and its features via technical hands-on demonstration
Demo: SQL Server Management Studio Part 214:53
Continuation of exploring the interface of SQL Server Management Studio and its features via technical hands-on demonstration
SQL Server Databases5:35
A glimpse into the SQL Server databases
Database Types1:50
Identify the different database types in SQL Server
System Databases (Deep Dive)3:36
Explore system databases in more detail
Database Server Environments6:23
Identify and familiarize with the different database environments used in the real world
Demo: Database Types Part 17:05
Technical hands-on demonstration of the database types on SQL Server Management Studio
Demo: Database Types Part 212:13
Technical hands-on demonstration of the database types on SQL Server Management Studio
Demo: Database Types Part 39:59
Technical hands-on demonstration of the database types on SQL Server Management Studio
Knowledge Check: Introduction to SQL Sever

Logins and Users10:35
Understand authentication types in SQL Server and discover the difference between logins and users
Server Roles6:24
Understand different server roles available in SQL Server and their permissions
Database Roles8:42
Understand different database roles available in SQL Server
Permissions5:41
Understand different database roles available in SQL Server and their permissions
Demo: Windows Login10:29
Create Windows login on a SQL Server instance
Demo: Database Roles9:37
Map/Add logins as members of database roles to become users
Demo: Login Failed Error Troubleshooting13:33
Troubleshoot and fix common login failed errors
Demo: Roles and Permissions11:38
Explore database roles and their permissions via technical hands-on demonstration
Demo: Windows Active Directory Group11:46
Create Windows Active Directory group on a SQL Server Instance via technical hands-on demonstration
Demo: SQL Login6:30
Create SQL login on a SQL Server instance via technical hands-on demonstration
Knowledge Check: Authentications, Role-Based Access Control and Permissions

What is a Disaster?1:43
Understand disaster as it pertains to database administration
Disaster Recovery Terminologies3:48
Understand the terminologies used in planning disaster recovery
Database Recovery Models7:36
Understand the different database recovery models
Database Backup Part 114:12
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.
Database Backup Part 29:48
Database Restore5:46
Demo: Disaster Recovery Strategies Part 15:01
Demo: Disaster Recovery Strategies Part 29:14
Demo: Database Backup Part 17:57
Demo: Database Backup Part 210:39
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.
Demo: Database Restore Part 111:11
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.
Demo: Database Restore Part 213:33
Demo: Database Restore Part 38:26
Disaster Recovery Exercises1:21
Knowledge Check: Disaster Recovery Strategies and Database Maintenance

Database Maintenance Tasks11:40
Demo: Database Maintenance Tasks Using GUI Part 19:31
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.
Demo: Database Maintenance Tasks Using GUI Part 29:55
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.
Demo: Database Maintenance Tasks Using GUI Part 310:10
Demo: Database Maintenance Tasks Using Ola Hallengren Part 110:44
Demo: Database Maintenance Tasks Using Ola Hallengren Part 210:09
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.
Practice Exercises0:29

Introduction to High Availability6:05
Introduction to Log Shipping11:22
Log Shipping Pros and Cons9:42
Demo: Log Shipping Configuration Part 17:22
Demo: Log Shipping Configuration Part 211:33
Demo: Log Shipping Configuration Part 311:33
Demo: Failover in Log Shipping Part 19:12
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.
Demo: Failover in Log Shipping Part 210:03
Knowledge Check: Log Shipping, Mirroring, AlwaysOn AG and Replication

Introduction to Database Mirroring10:56
Synchronous vs Asynchronous Commit Modes10:51
Database Mirroring Pros and Cons6:03
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.
Demo: Database Mirroring Configuration Part 111:30
Demo: Database Mirroring Configuration Part 212:45
Demo: Database Mirroring Configuration Part 39:06
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.

Introduction to AlwaysOn Availability Group9:42
AlwaysOn Availability Group Pros and Cons Part 111:29
AlwaysOn Availability Group Pros and Cons Part 25:37
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.
AlwaysOn Availability Group Configuration Steps Part 18:09
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.
AlwaysOn Availability Group Configuration Steps Part 25:08
Demo: AlwaysOn Availability Group Configuration Part 17:48
Demo: AlwaysOn Availability Group Configuration Part 211:49
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.
Demo: AlwaysOn Availability Group Configuration Part 311:33
Demo: AlwaysOn Availability Group Configuration Part 413:06
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.
Demo: AlwaysOn Availability Group Cluster Permission10:36
Demo: AlwaysOn Availability Group Quorum Configuration7:26
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.

Introduction to Replication4:33
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.
Replication Topology2:17
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.
Replication Backward Compatibility8:21
Replication Types9:31
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.
Replication Agents5:26
Transactional vs Snapshot Replication5:36
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.
Replication Pros and Cons6:10
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.
Demo: Transactional Replication Configuration Part 16:32
Demo: Transactional Replication Configuration Part 29:57
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.
Demo: Transactional Replication Configuration Part 39:50
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.
Demo: Transactional Replication Configuration Part 412:51
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.
Demo: Transactional Replication Configuration Part 57:31
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.
Demo: Snapshot Replication Configuration8:09

Requirements

No Prior Information Technology (I.T) Experience Is Required
Windows Personal Computer (Laptop or Desktop)
Reliable Internet Connection
Great Passion to Learn
Existing Virtual Lab Environment for Technical Hands-on Practices

Description

Dive into the world of SQL Server with our comprehensive course designed for existing and prospective Microsoft SQL Server Database Administrators. Whether you're a beginner, junior, or intermediate-level Database Administrator, this course equips you with essential skills and knowledge to excel in the field. You'll gain a solid foundation in SQL Server architecture and functionality, and learn critical topics such as authentications, database maintenance, disaster recovery, and high availability (Log Shipping, Mirroring, AlwaysOn Availability Group, and Replication).

This course is rich in technical hands-on demonstrations to enhance your comprehension of the material, and it includes numerous practice exercises to reinforce your learning. You'll also find a wealth of typical interview questions to help you prepare for job interviews and knowledge checks. Designed to help you break into the IT field or switch roles within the industry, this course ensures you are well-prepared for your career.

This engaging course is perfect for those with a passion for technical hands-on activities and aims to provide a solid foundation in SQL Server administration.

Prerequisite to this course is a virtual lab, with setup videos available in my first course (Comprehensive SQL Server Administration Lab Setup). Trainees with an existing lab can skip the first course and proceed with this course.

Who this course is for:

Individuals with Technical Hands-On Abilities (Prospective Microsoft SQL Server Database Administrators)
Beginner/Junior Microsoft SQL Server Database Administrators
Mid-Level/ Intermediate Microsoft SQL Server Database Administrators

Comprehensive SQL Server Administration Part 1

What you'll learn

Explore related topics

Course content

Introduction to Microsoft SQL Server15 lectures • 1hr 53min

Authentications, Role-Based Access Control and Permissions10 lectures • 1hr 35min

Disaster Recovery14 lectures • 1hr 50min

Database Maintenance7 lectures • 1hr 3min

Log Shipping8 lectures • 1hr 17min

Mirroring6 lectures • 1hr 1min

AlwaysOn Availability Group11 lectures • 1hr 42min

Replication13 lectures • 1hr 37min

Requirements

Description

Who this course is for: