Advanced

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