SQL Server: Transactional Replication Fundamentals

By Joe Sack
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
(242)
Level
Intermediate
Updated
Jun 21, 2013
Duration
1h 52m
Table of contents
Course Introduction
Course Introduction 1m Course Structure 1m
Replication Types and Scenarios
Common Transactional Replication Topologies 1m Module Introduction 1m Terminology - Publishing Industry Metaphor 1m Types of Replication (1) 1m Types of Replication (2) 1m Types of Replication (3) 2m
Setup and Configuration
Configuring the Distributor 1m Creating the Publication 1m Creating the Pull Subscription 1m Creating the Push Subscription 0m Demo: Configuring the Distributor via T-SQL 3m Demo: Configuring the Distributor via Wizard 6m Demo: Creating a Subscription via T-SQL 3m Demo: Creating a Subscription via Wizard 6m Demo: Creating the Publication via T-SQL 3m Demo: Creating the Publication via Wizard 5m Demo: Testing a Transactional Replication Topology 2m Deploying with Least Privilege (1) 1m Deploying with Least Privilege (2) 1m Deployment: Wizard or Scripts? 1m Module Introduction 1m Transactional Replication Components 1m Transactional Replication Distribution Agent 1m Transactional Replication Initial Dataset 1m Transactional Replication Log Reader Agent 1m sp_addsubscription @sync_type 2m
Performance Considerations
Demo: Checking Distribution Database Settings 3m Demo: Measuring Agent Reader vs. Writer Time 6m Demo: Replication Multiplier Effect 4m Demo: Validating immediate_sync options 1m Demo: Wait Statistics by Agent 5m Distribution Database 1m Factors that Affect Replication Performance (1) 3m Factors that Affect Replication Performance (2) 2m Hidden Costs of a Single Command 1m Log Reader and Transaction Log Activity 1m Module Introduction 1m sp_addpublication's immediate_sync 2m
Combining Replication with other Technologies
Module Introduction 1m Transactional Replication and Availability Groups 1m Transactional Replication and Database Mirroring 2m Transactional Replication and Failover Clustering 1m
Monitoring
Alerts (1) 1m Alerts (2) 1m Demo: Replication Alerts 5m Demo: Replication Monitor Warnings and Thresholds 3m Demo: Using Tracer Token Stored Procedures 1m Establishing Replication Baselines 1m Module Introduction 1m Setting Warnings and Thresholds 1m
Troubleshooting
Course Summary 1m Danger of -SkipErrors 1m Data Not Being Delivered to Subscribers 1m Demo: Replication Validation 2m Demo: Skipping a Failed Command 4m Error 2812: Could not find stored procedure %.*ls 1m Impact of DBCC CHECKDB Repair 1m Module Introduction 1m Process Could Not Execute sp_repldone/sp_replcounters 1m Publisher and Subscriber Data Does Not Match 1m Skipping Specific Commands 1m Tools and General Troubleshooting Steps (1) 2m Tools and General Troubleshooting Steps (2) 1m
Description
Course info
Rating
(242)
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
Joe Sack
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
SQL Server: Common Query Tuning Problems and Solutions - Part 2
Intermediate
2h 17m
Aug 11, 2014
SQL Server: Common Query Tuning Problems and Solutions - Part 1
Intermediate
2h 30m
Jul 7, 2014
SQL Server: Transact-SQL Common Table Expressions
Intermediate
1h 20m
Feb 14, 2014
More courses by Joe Sack

You're the smartest person in the room

PROVE IT

Your team won't just close the skills gap

THEY'LL HURDLE IT