SQL Server Administration Part 3

Name: SQL Server Administration Part 3
Rating: 4.6 (2827 reviews)

Learn High Availability Solutions like Log Shipping, Database Mirroring, Replication and Always On Availability Group

Created byRaphael Asghar

Last updated 4/2020

English

What you'll learn

At the end of the course, the student will be able to understand, configure and monitor various high availability (HA) solutions such as log shipping, database mirroring, replication, peer to peer replication, and the great always on availability group technology. This experience will help the student gain a better understanding of how and when to utilize each technology depending on the needs of the company. This knowledge will enhance the opportunity for each student to progress faster within a company and promote his skill set to a higher level.

Course content

1 section • 39 lectures • 8h 34m total length

Getting A SQL DBA Job6:35
1. VMware Workstation 12 and Domain Controller Part 119:59
POST ADDENDUM8:11
APPS NEEDED7:34
CREATE VM18:22
INSTALL SQL SERVER 20179:43
ADVENTUREWORKS 2017 DB8:13
Install and configure SQL Server 2017, install SSMS, connect and explore the management interface, and restore the AdventureWorks 2017 backup to demonstrate database administration in a virtualized environment.
COMPARISON SQL 2014 VS 20172:38
Compare SQL 2014 versus 2017 to show that database administration principles apply across versions, with similar front end interfaces and guidance to update Adventureworks references and scripts for 2017.
2. VMware Workstation 12 and Domain Controller Part 219:50
Promote a server to a domain controller, configure Active Directory, create a new forest, set up DNS, and assign a static IP for future VMs.
3. Server1 installation part 111:15
Set up a domain controller and three SQL Server nodes, assign static IPs (50–53), join the domain, and install SQL Server 2014/2016 to architect a basic HA environment.
4. Server1 installation part 29:34
Rename the server to server one, join the domain with the cluster admin account, configure the IPs, and install sql 2014; then repeat for server two with shared folders.
5. Server 2 installation part 113:35
6. Server 2 installation part 213:25
7. Server 2 installation part 315:00
Learn how to install sql server across environments, compare 2014 and 2016, address .net framework 3.5 prerequisites, and install ssms separately after sql server in 2016.
8. Server 2 installation part 415:34
Install SQL 2016 using the SSMS installer across three servers, with required restarts and updates. Connect to multiple SQL instances and explore high availability options like clustering and Always On.
9. Log Shipping Part 115:33
Set up log shipping as a high-availability solution by configuring four virtual machines, creating domain accounts and SQL logins, and running SQL Server Agent under domain permissions with Windows authentication.
10. Log Shipping Part 218:32
Set up SQL Server log shipping to protect data with a primary and secondary server by transferring transactional logs to a warm standby, enabling disaster recovery.
11. Log Shipping Part 317:06
Explore log shipping for the test database: back up transaction logs on the primary server, copy to the secondary, and restore in standby mode every 15 minutes for high availability.
12. Log shipping Manual Failover part 112:11
Learn how to perform a manual failover in log shipping by reversing roles between the primary and secondary servers, syncing transaction logs, and ensuring synchronization.
13. Log shipping Manual Failover part 210:48
Execute a step-by-step log shipping reversal: tail log backup with no recovery, restore on the secondary, switch roles, test data propagation, and restart the process while noting login migration later.
14. Orphans18:29
Identify how orphan users arise when logins don’t migrate during log shipping or restore. Fix them by recreating logins, mapping users, and syncing sids with sp_change_users_login_update_one.
15. Monitoring Log Shipping14:16
Learn to monitor log shipping in SQL Server using reports, stored procedures, and error logs to track backup, copy, and restore between primary and secondary servers.
16. Database Mirroring18:43
17. Database Mirroring using GUI part 112:31
Learn to configure database mirroring on a single machine by creating two SQL Server instances, backing up the database and log to a shared folder, and restoring with no recovery.
18. Database Mirroring using GUI part 213:11
Master database mirroring setup using the GUI to configure security, establish principal and mirror endpoints, and manage optional witness, failover, and synchronous versus asynchronous modes.
19. Database Mirroring Monitoring part 112:40
Monitor database mirroring using the GUI and SQL scripts, tracking synchronization, unsent logs, unrestored logs, and threshold alerts to ensure reliable failover readiness.
20. Database Mirroring Monitoring part 29:55
Explore how to monitor database mirroring with GUI and SQL scripts, check unsent and unrestored logs, verify sync status, and manage failover and endpoint configurations.
21. Replication12:43
Explore SQL replication, including publishers, publications, distributors, and subscribers, and learn how transactional replication uses log reader and distribution agents to move only committed table data to subscribers.
22. Transactional Replication part 111:15
Learn to configure transactional replication with a single server acting as distributor and publisher, create domain user rep and logins, and publish foods database with primary keys on its tables.
23. Transactional Replication part 212:35
Configure transactional replication by selecting publishable tables with primary keys, creating a publication and subscription, and initializing snapshot and distribution agents. Monitor status with the Replication Monitor.
24. Peer to Peer Replication part 111:14
25. Peer to Peer Replication part 212:44
Set up a distributor and configure peer-to-peer topology across three SQL Server nodes, create a computers publication, and verify data propagation by inserting and updating records on each node.
26. Always On17:19
Explore always on availability groups that replicate databases from a primary replica to multiple read-only secondary replicas, enabling automatic failover and offloading backups and reporting.
27. Always On Requisite part 19:53
Learn to implement always on availability groups by configuring failover cluster services on three servers and preparing backups, full recovery mode, and a shared folder accessible by all nodes.
28. Always On Requisite part 212:16
Register all servers and test connections, create Rep one and Rep two databases with mixed recovery models, back up to the shared folder, and prepare for Always On.
29. Always On Setup Cluster14:40
Learn to build a three-node failover cluster, validate configuration, assign a cluster IP, name the cluster, enable always on, and restart services for availability groups.
30. Always On Demo part 114:50
Demonstrates creating an Always On availability group, configuring databases as a synchronized group, enabling a read-only secondary, and performing a failover between primary and secondary replicas.
31. Always On Demo part 218:15
Explore how to configure and manage SQL Server Always On availability groups, perform failovers, ensure synchronization, configure read-only secondary access, and test disaster recovery strategies.
32. Backup Replicas13:23
Learn how to use always on availability groups to back up on secondary replicas, with copy-only full backups and transactional log backups, while offloading workload from primary to secondary.

Requirements

It is strongly advised that students take SQL Administration Part 1 and SQL Administration Part 2 first before taking this course as this builds upon the concepts taught and discussed in those courses. Also, Learn T-SQL course is highly recommended. All the material and software that the students needs will be downloaded and the SQL scripts will be provided by me where applicable.

Description

This SQL Server Administration Part 3 course is designed for the student who has already taken or is taking Part 1 and Part 2, which are prerequisite courses. This course will cover High Availability solutions such as log shipping, database mirroring, replication, peer to peer replication and always on availability group technologies. Upon completing this course, the student will be well versed in configuring, managing, and monitoring virtually all tasks that a SQL SBA does in his daily, weekly and monthly duties. The materials that the student will need such as documents and SQL scripts will be provided via a zip file that the student requests via email. Upon completion of this course, the student will have the training, education and knowledge that a college provides in their courses about SQL Server Administration.

Who this course is for:

Students who are interested in learning more advanced technologies that SQL Server has to offer such as database mirroring, and always on; those who are accidental SQL DBAs and are in charge of deploying solutions for safe guarding databases throughout their environment. Students that have not taken SQL Administration Part 1 and SQL Administration Part 2 should not this course as those courses are prerequisites. This course build upon those tow previous courses.