Find online courses made by experts from around the world.
Take your courses with you and learn anywhere, anytime.
Learn and practice real-world skills and achieve your goals.
This is a deep dive course on understanding how SQL Server works to effectively choose the right high availability and disaster recovery solution that will address every organization's need to protect digital assets. Packed with videos explaining concept and demos to reinforce the lessons, this course is what every IT professional needs to properly manage and protect a SQL Server database.
Not for you? No problem.
30 day money back guarantee.
Learn on the go.
Desktop, iOS and Android.
Certificate of completion.
|High Availability and Disaster Recovery (HA/DR) is an area that every SQL Server DBA needs to be comfortable with. In this online course, learn what needs to be considered when embarking on a HA/DR project, the different features and technologies available in SQL Server, and implement them accordingly to meet recovery objectives and service level agreements of mission-critical databases.|
|With data becoming the lifeblood of every business in our era, high availability and disaster recovery has become a critical business strategy. In this lesson, we'll look at the different trends that drove this need and understand the main goal for this course|
|Section 1: Non-Technology Aspects of High Availability and Disaster Recovery|
|So, you think high availability and disaster recover is all about technology? Think again. In this lesson, we will look at a key principle that I've personally developed to address the main components of what it takes to implement a successful high availability and disaster recovery project. The concept is so practical that you can take and apply it in just about any aspect of your life outside of IT.|
|I call these "The Alphabet Soup of High Availability and Disaster Recovery." These acronyms define the key concepts that we need to understand in order to choose the right technology with the right people and the right budget. We need to learn these concepts by heart in order to have the right conversation with the business stakeholders and get their buy-in.|
|No, we won't be watching Narnia in this lesson. We will, however, be reminded of things that the movie represents. In this lesson, we will learn who needs to be a part of our team, what other hardware have we not considered and what other storage media is missing from our toolbox. At the end of this lesson, we'll be able to create our own list of things that we need to include in our high availability and disaster recovery plan even before we see SQL Server.|
|Section 2: SQL Server Recovery Models|
|We know that SQL Server database recovery models control how the database behaves when dealing with transactions in the log. But there's more to it than that. In this lesson, we will look at the main reason for configuring your database recovery models and how they should influence your backup strategies. We'll also look at a recovery model that I bet you won't find anywhere in SQL Server Books Online|
|This is the demo that accompanies the database recovery models lecture. In this demo, we will look at the default recovery model from the model database, changing the default recovery model and investigating the undocumented pseudo-simple recovery model. We will also look at a Windows PowerShell script to check which of your databases are in pseudo-simple recovery model.|
|This is the first demo that accompanies the database backup types lesson. In this demo, we will look at how to create the different backup types using SQL Server Management Studio. We will also look at the risks involved when switching your database recovery model from FULL to BULK_LOGGED and back to FULL and how to mitigate them.|
|Section 3: SQL Server Database Backups|
|This lesson will look at different SQL Server backup types and the most common ones used in the field. We will also look at some guidelines on choosing the appropriate backup strategies to meet your recovery objectives and service level agreements.|
|This is the second demo that accompanies the database backup types lesson. In this demo, we will look at understanding how multi-base differential backups work. The walkthrough will show you that not all latest DIFFERENTIAL backups can be restored with the FULL database backup. This example is a great way to learn how SQL Server reads log sequence numbers stored in the backup media to construct an effective restore chain.|
|Section 4: SQL Server Transaction Log Internals|
|This lesson talks about understanding the SQL Server transaction log. We will look at how the transaction log works, what virtual log files (VLFs) are, the circular nature of the transaction log, and how it affects all of the high availability and disaster recovery technologies that you will implement with your SQL Server infrastructure. The key idea in this lesson is that the transaction log is the most important part of your SQL Server database.|
|This is the demo that accompanies the lesson on the transaction log internals. This explains the structure of the transaction log file, the number and size of the virtual log files (VLFs), the circular nature of the log and what permits or prevents the transaction log from getting truncated/cleared. This also shows how to use the undocumented functions - fn_dblog() and fn_dump_dblog() - to analyze the transaction log records directly from the log file or from backups.|
|This a short demo on what happens to your SQL Server database when the transaction log fills up. I used a SharePoint Server 2013 site collection as an example to illustrate how a database can be switched to read-only without us knowing as a side effect of the transaction log file filling up. Feel free to invite a SharePoint administrator to watch this video with you.|
|Section 5: SQL Server Database Restore and Recovery|
|This lesson describes restore and recovery, the different phases of restore, how crash recovery works and how it affects all of the high availability and disaster recovery technologies that you choose to implement on your SQL Server database.|
|Now that we've learned about how the transaction log works, how recovery models affect logging, the different backup types and how restore and recovery works, it's time to look at the different factors that affect recovery. This lesson will reinforce the concepts learned from the previous lessons and look at the different factors that effect database recovery. You can take the concepts that you'll learn in this lesson to constantly evaluate whether or not your strategies still meet your recovery objectives and service level agreements.|
|See how the effects of a simple configuration change can improve your database performance - whether you're creating a new database, extending the size due to an autogrowth event, adding another file or even as critical as a database restore. This can significantly reduce the time it takes to meet your recovery objectives. This configuration needs to be on all of your SQL Server instances, regardless of editions.|
This demo video is a compilation of different database recovery techniques that SQL Server DBAs should be familiar and comfortable with. We will look at recovering a database to a specific point in time, isolating critical objects or using table partitioning as an HA/DR option (more commonly called online piecemeal restore) and performing page-level restores.
|Section 6: SQL Server System Databases|
It's not enough to protect your user databases. System databases are at the core of a SQL Server instance. Without them, you won't be able to start your SQL Server service. In this lesson, we will look at what they are and what they are responsible for. We will also look at how to protect the system databases and how we can prepare in case the system databases are unavailable.
This is the demo that accompanies the lesson on protecting system databases. We will look at recovering a SQL Server instance when the drive that hosts the system databases becomes unavailable and how to move the system databases to a different drive as part of your disaster recovery process.
|Section 7: SQL Server Database Mirroring|
This lesson talks about database mirroring, how it works, the different scenarios that we need to deal with and the factors affecting failover time. The concepts learned in this lesson will form the basis for understanding the new SQL Server 2012 Availability Groups feature.
This is the first demo that accompanies the lesson on database mirroring. We will look at a common database mirroring configuration - SQL Server instances that are in the same Active Directory domain. We will also simulate some of the scenarios we talked about in the lesson - failure of the mirror database and simultaneous failure of both the mirror server and the witness server - and how those failures affect database availability and the SEND the REDO queues in a database mirroring configuration.
This is the second demo that accompanies the lesson on database mirroring. We will look at configuring database mirroring across servers that are not members of an Active Directory domain. The steps in doing so are tricky so make sure to test this out in your lab prior to implementing it in your production environment.
|Section 8: SQL Server Log Shipping|
This lesson describes how SQL Server log shipping works and the underlying concepts behind it. At a high level, log shipping is simply an automated process of taking a log backup, copying it and restoring it on a standby database. It's really no different from how transaction log backup and restore works.
This is the demo that accompanies the lesson on SQL Server log shipping. We will go thru the traditional way of configuring log shipping for a SQL Server database using SQL Server Management Studio. We will also look at a potential solution that you can use even on editions of SQL Server that don't officially support log shipping, such as the Express Editions
|Section 9: SQL Server Failover Clustering|
This lesson discusses the fundamentals of Windows Server Failover Clustering in the context of a SQL Server failover clustered instance. We will look at the different concepts to understand how Windows Server Failover Clustering works to support highly available SQL Server databases. The concepts learned in this lesson also form the foundation for understanding the new SQL Server 2012 Availability Groups feature.
This is the first demo that accompanies the lesson on SQL Server failover clustering fundamentals. We will build a traditional 2-node SQL Server failover clustered instance on Windows Server 2012 from start to finish and install SQL Server 2012 Service Pack 1.
SQL Server 2012 natively supports multi-subet/geographically dispersed clusters. In this second demo on failover clustering, we will build a 2-node SQL Server failover clustered instance that spans across multiple geographical locations. We will also look at the different network configurations that need to be considered to achieve our recovery objectives and service level agreements
|Section 10: SQL Server 2012 Availability Groups|
This lesson discusses the new Availability Groups feature in SQL Server 2012. We will be drawing parallels between Availability Groups and the concepts behind database mirroring and failover clustering to better understand the feature. You will be surprised that you actually know some of the things covered in this lesson based on your previous knowledge of other existing technologies.
This is the demo that accompanies the lesson on SQL Server 2012 Availability Groups. We will convert an existing database mirroring and log shipping configuration into an Availability Group configuration. In the process, we will configure readable secondaries and read-only routing to redirect read-only workloads to any of your chosen readable secondaries that isn't the primary.
This demo presents a more complex environment - an Availability Group that uses named instances as replicas and spread out across different geographical locations. It's a combination of the concepts we've learned from multi-subnet failover clusters and Availability Groups providing both high availability and disaster recovery for your mission critical databases.
|Section 11: SQL Server Replication|
A lot of customers use SQL Server Replication as part of their high availability and disaster recovery strategies. In this lesson, we will cover the original intent behind SQL Server replication, how it works and some of the use cases for using this feature.
We sum up the course by answering the ultimate question: How do we choose the right high availability and disaster recovery solution for our SQL Server databases? All along, the answer has been emphasized on all of the lessons in this course.
Edwin M Sarmiento is a Microsoft Certified Master and a Microsoft SQL Server MVP from Ottawa, Canada. He specializes in high availability, disaster recovery and system infrastructures running on the Microsoft server technology stack. He is very passionate about technology but has interests in music, professional and organizational development, leadership and management matters when not working with databases. He lives up to his primary mission statement – “To help people and organizations grow and develop their full potential as God has planned for them“
He's a regular contributor on the website MSSQLTips and has contributed to the SQL Server MVP Deep Dives book series - Volume 1 and Volume 2 - and one of the Technical Editors/Reviewers of the book SQL Server 2012 with PowerShell V3 Cookbook. He's actively involved in the SQL Server community by answering questions in Microsoft forums and speaking at conferences like SQLSaturday, SQLRally, SQLConnections, SQL PASS Community Summit, Microsoft TechDays, SQLLive360, DevTeach and other user group events.