SQL Server: Transactional Replication Fundamentals

Learn how to configure, monitor, and troubleshoot SQL Server transactional replication, as well as the fundamentals of how it works and key performance considerations.
Course info
Rating
(235)
Level
Intermediate
Updated
Jun 21, 2013
Duration
1h 52m
Table of contents
Course Introduction
Replication Types and Scenarios
Setup and Configuration
Performance Considerations
Combining Replication with other Technologies
Monitoring
Troubleshooting
Description
Course info
Rating
(235)
Level
Intermediate
Updated
Jun 21, 2013
Duration
1h 52m
Description

SQL Server replication remains a viable data distribution and availability feature that has inherent advantages that are not entirely covered by other out-of-the-box SQL Server features. This course will briefly examine the various replication types before delving more deeply into transactional replication. In addition to the fundamentals, this course will also review the key performance considerations, monitoring techniques and troubleshooting scenarios. After viewing this course you should be ready to use transactional replication in the appropriate contexts, as well as deploy, support and troubleshoot issues accordingly. This course is perfect for developers, DBAs, and anyone responsible for SQL Server replication, from complete beginners through to those with more experience. The information in the course applies to all versions from SQL Server 2005 onwards.

About the author
About the author

Joe Sack is a Principal Program Manager in the SQL Server and Azure SQL Database product team at Microsoft, with a focus on query processing. With over 19 years of experience in the industry, Joe is an author and speaker, specializing in performance tuning and optimization.

More from the author
More courses by Joe Sack
Section Introduction Transcripts
Section Introduction Transcripts

Course Introduction
Hi, this is Joe Sack from SQLskills. com and I am recording this course for Pluralsight. This is the SQL Server Transactional Replication Fundamentals course and you're watching the introduction where I'll briefly describe what to expect over the modules that make up this course. SQL Server replication is a set of technologies you can use for copying and distributing data from one database to another, and this can be both within the same SQL Server Instance and more commonly across SQL Server Instances. There are multiple reasons why people use transactional replication, but the more common reasons include the offloading of data for reporting and ETL processes, increasing data availability, and also scaling out activity across multiple SQL Server Instances. In this course, I'll focus specifically on transactional replication and by the end you should have enough information needed to successfully deploy, configure, tune, troubleshoot, and monitor a transactional replication topology.

Replication Types and Scenarios
Hi, this is Joe Sack from www. SQLskills. com and I'm recording the course for Pluralsight. This is the SQL Server Transactional Replication Fundamentals course. And you're watching module 2, where we'll go over the various common types of replication that SQL Server offers and the common use case scenarios that organizations use it for. Most of this course will cover a specific type of replication, transactional replication. But it's still important to understand the overall set of replication types available in SQL Server. Replication is a set of technologies within SQL Server that can be used to copy and distribute data and database objects to databases on the same or different SQL server instance. This module will cover the various common types of replication used and then also the scenarios where each type might be a good fit.

Setup and Configuration
Hi, this is Joe Sack from SQLskills. com, and I'm recording this course for Pluralsight. This is the SQL Server Transactional Replication Fundamentals course, and you're watching module 3 where we'll walk through how to set up, and configure transactional replication. Setting up replication is actually not that complicated. But, making your, replication solution robust, scalable, and high performing, requires a bit more planning and knowledge, about how the various components under the covers actually work. In this module, I'll start off by talking about the key transactional replication components. And then, we'll step through the various phases, and considerations, and also compare, and contrast, using a wizard for deployment and configuration, versus replication store procedure calls.

Performance Considerations
Hi this is Joe Sack from SQLskills. com and I am recording this course for Pluralsight. This is the SQL server transactional replication fundamentals course and you are watching module 4, where we'll discuss the various common performance considerations that you should understand when supporting a transactional replication topology. Transactional replication can handle high throughput, low-latency data movement requirements. And if you have a balanced topology, without any major bottlenecks, this can usually mean that you're able to stream changes on the publisher to the distributor, and then from the distributor to multiple subscribers with just a few seconds of latency. There are, however, several factors that can influence overall latency, so in this module, we will cover the key factors that you should consider when replication latency is a key factor for your overall requirements and service level agreements.

Combining Replication with other Technologies
Hi, this is Joe Sack from SQLskills. com and I'm recording this course for Pluralsight. The is the SQL Server: Transactional Replication Fundamentals course and you're watching module 5 where we'll discuss how replication interacts with other native SQL Server technologies. It is common to use transactional replication in conjunction with other technologies. And very often you do need to use multiple SQL Server technologies in order to meet your overall business and application requirements. In this brief module I'll discuss some of the interoperability considerations when using transactional replication with database mirroring, failover cluster instances and availability groups which were introduced in SQL Server 2012.

Monitoring
Hi, this is Joe Sack from SQLskills. com and I'm recording this course for Pluralsight. This is the SQL Server Transactional Replication Fundamentals course and you're watching module 6 where we'll discuss monitoring considerations for a transactional replication environment. After deploying transactional replication, you need to establish monitoring for the health and performance of publications of the distributor and subscribers. In this module we'll review the key areas to monitor in order to identify and respond to issues in a timely fashion.

Troubleshooting
Hi, this is Joe Sack from SQLskills. com, and I'm recording this course for Pluralsight. This is the SQL Server transactional replication fundamentals course, and you're watching module 7, where we'll cover how to troubleshoot common problems that can arise in a transactional replication topology. Some companies dismiss transactional replication because they've encountered performance issues. Whether due to throughput or overall latency, and other companies I've worked with have removed transactional replication from an environment or removed it from consideration because of issues with agent failures and data synchronization. So, in this module, I'll cover some of the more common issues and associated troubleshooting steps. I found that once you understand a few basic areas for the subject, then transactional replication becomes a bit more easy to manage and support.