Applies to: SQL Server (all supported versions)

Transactional replication is a good solution to the problem of moving data between continuously connected servers. By using the Replication Wizard, you can easily configure and administer a replication topology.

This tutorial shows you how to configure a transactional replication topology for continuously connected servers. For more information about how transactional replication works, see the  overview of transactional replication.

What you will learn

This tutorial teaches you to publish data from one database to another by using transactional replication.

In this tutorial, you will learn how to:

• Create a publisher via transactional replication.
• Create a subscriber for the transactional publisher.
• Validate the subscription and measure latency.

Prerequisites

This tutorial is for users who are familiar with basic database operations, but who have limited experience with replication. Before you start this tutorial, you must complete Tutorial: Prepare SQL Server for replication.

To complete this tutorial, you need SQL Server, SQL Server Management Studio (SSMS), and an AdventureWorks database:

• At the publisher server (source), install:
  • Any edition of SQL Server, except SQL Server Express or SQL Server Compact. These editions cannot be replication publishers.
  •  
• At the subscriber server (destination), install any edition of SQL Server, except SQL Server Compact. SQL Server Compact cannot be a subscriber in transactional replication.
• SQL Server Management Studio.
• SQL Server 2017 Developer edition.
• Download the AdventureWorks sample database. For instructions on restoring a database in SSMS, see Restoring a database.

 Note

• Replication is not supported on SQL Server instances that are more than two versions apart. For more information, see Supported SQL Server Versions in Replication Topology.
• In SQL Server Management Studio, you must connect to the publisher and subscriber by using a login that is a member of the sysadmin fixed server role. For more information on this role, see Server-level roles..

Estimated time to complete this tutorial: 60 minutes

Configure the publisher for transactional replication

In this section, you create a transactional publication by using SQL Server Management Studio to publish a filtered subset of the Product table in the AdventureWorks2012 sample database. You also add the SQL Server login used by the Distribution Agent to the publication access list (PAL).

Create a publication and define articles

1. Connect to the publisher in SQL Server Management Studio, and then expand the server node.
2. Expand the Replication folder, right-click the Local Publications folder, and select New Publication. This step starts the New Publication Wizard:

Link to PDF : Diagnose and resolve latch contention on SQL Server