
	About Us
	SSWUG Articles
	Articles

	Administering
	Comparison
	General
	Know How
	Replication
	Tuning
	Undocumented
	UDF
	SQL 6.5

	FAQ
	Scripts
	Tips
	Test Exams
	Advertise
	Download
	History
	Search
	Traffic
	Related Links


Add to Favorites

Setup Transactional replication: step by step guide

Alexander Chigrik
chigrik@mssqlcity.com

General concepts
Checking necessary conditions
Checking connect
Example

General concepts

Replication is the process whereby data is copied between databases on the same server or different servers connected by LANs, WANs, or the Internet.

Microsoft SQL Server replication uses a publisher, distributor and subscriber metaphor.

Publisher is the server or database that sends its data to another server or database.

Subscriber is the server or database that receives data from another server or database.

Distributor is the server that manages the flow of data through the replication system. This server contains the distribution database.

Publisher contains publication/publications. Publication is a collection of one or more articles that is sent to a subscriber server or database.

Article is the basic unit of replication and can be a table or a subset of a table.

Subscription is the group of data that a server or database will receive.

There are push and pull subscriptions.

Push subscription is subscription when the publishing server will periodically push transactions out to the subscribing server or database.

Pull subscription is subscription when the subscribing server will periodically connect to the distribution database and pull information.

The Distribution database is a system database, which is stored on the Distributor and does not contain any user tables. This database is used to store snapshot jobs and all transactions waiting to be distributed to Subscribers.

Microsoft SQL Server supports the following replication topologies:

Central publisher
Central subscriber
Central publisher with remote distributor
Central distributor
Publishing subscriber

Central publisher
This is one of the most used replication topologies. In this scenario, one server is configured as Publisher and Distributor and another server/servers is/are configured as Subscriber/Subscribers.

Central subscriber
This is a common topology in data warehousing. Many servers or databases replicate their data to a single central server in one or more databases.

Central publisher with remote distributor
In this topology, distribution database resides on another server than publisher. This topology uses for performance reasons when the level of replication activity increases or the server or network resources become constrained. It reduces Publisher loading, but it increases overall network traffic.

This topology requires separate Microsoft SQL Server installations, one for the Publisher and one for the Distributor.

Central publisher with remote distributor

Central distributor
In this topology, several publishers use only one distributor, which resides on another server than publishers. This is one of the most unused replication topologies, because it has only single point of failure (on the single server with central distributor), and if distributor's server will fail, entire replication scenario will be destroyed.

Publishing subscriber

This is a dual role topology. In this topology, two servers publish the same data. One publishing server sends data to subscriber, and then this subscriber publish data to any number of other subscribers. This is useful when a Publisher should send data to Subscribers over a slow or expensive communications link.

Publishing subscriber

Checking necessary conditions

Check the following before setting up transactional replication:

1. Remember that Microsoft SQL Desktop Edition supports only subscriptions to transaction replication. So, you must use Microsoft SQL Standard, Enterprise or Small Business Server (SBS) editions to publish transaction replication. So, for publish transaction replication you should have Windows NT Server, Windows NT Server Enterprise Edition or Windows NT Small Business Server (on Windows NT WorkStation and Windows 9x you can install only SQL Server Desktop Edition).

2. The Localsystem account has no access to shares on the network as it isn't an authenticated network account. So, if you want to setting up replication you should change the account the MSSQLServer and SQLServerAgent services runs under to an account with the Windows NT administrator's rights.

3. Only members of the sysadmin fixed server role can set up and configure replication, so if you have not these rights, you can not set up replication.

4. You must uncheck trunc. log on chkpt option, if you want to set up transactional replication.

5. Allocate adequate log space for each database that will be published in transactional replication.

6. Allocate adequate disk space for the distribution database.

7. Ensure the server that is being replicated to, is defined as a remote server.

Checking connect

1. Check TCP/IP by using ping utility.

2. Check odbc connection by using odbcping utility.

This is the syntax of odbcping utility:

odbcping [-S Server | -D DSN] [-U Login Id] [-P Password]

If you have received the message like below, the SQL Server works okay:

CONNECTED TO SQL SERVER

ODBC SQL Server Driver Version: 03.70.0623

SQL Server Version: Microsoft SQL Server  7.00 - 7.00.623 (Intel X86)
        Nov 23 1998 21:08:09
        Copyright (c) 1988-1998 Microsoft Corporation
        Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 4)

Example

In this example, I will use two servers: CHIGRIK_A_U - publisher and distributor and MAKSIMUK_I_L - subscriber. I will use transactional replication with push subscription.

Register new remote server in which will be replicated.

Enter the name of remote server and specify Remote Login Mapping:

Remote Server Properties

If you have made all correctly, you receive the following:

Remote Servers

Select Tools -> Replication -> Configure Publishing and Subscribers:

Configure Publishing and Subscribers

This will launch the Configure Publishing and Distribution Wizard:

Configure Publishing and Distribution Wizard

Select the Next button to create the Distributor:

Create the Distributor

Now, you can choose the default settings or set the distribution database name and location, enable other Publishers, or set another settings. Click the Next button.

Distributor Configurations

Click the Finish button.

Complete Wizard

Now, SQL Server creates the distribution database, enables publishing, and setting up the distributor:

Okay message

Because we installed CHIGRIK_A_U as Distributor, the Replication monitor has been added to the console tree on the CHIGRIK_A_U server.

Replication information

Now, we are ready to start creating publications and articles. Select Tools -> Replication -> Create and Manage Publications:

Create and Manage Publications

You will see Create and Manage Publications dialog box:

Create Publication

Choose pubs database and click the Create Publication button.

Start Create Publication Wizard

This will launch the Create Publication wizard.

Choose Replication Type

Select Transactional publication and click Next button.

Immediate-Updating Subscriptions

You can allow immediate-updating subscriptions on this step. If you choose this option then all changes will be applied at the Subscriber and Publisher simultaneously.

Specify Subscriber Types

Choose Subscriber types on this step. If all subscribers are Microsoft SQL Server subscribers, choose the first option.

Specify Articles

Select tables or stored procedures to publish and click Next button.

Choose Publication Name and Description

Choose publication name and description on this step.

Use Default Properties of the Publication

You can define data filters or set another options on this step.

Complete Wizard

Click Finish button to create the publication.

Information message

If publication was created successfully, you will receive the above message.

Create Publication Window

Create new push subscription now.

Start Push Subscription Wizard

Click Next button.

Choose Subscribers

Choose subscribers. Click Next button.

Choose Destination Database

Choose destination database. It's pubs database on the MAKSIMUK_I_L server.

Set Distribution Agent Schedule

If you want to have continuous replication that will send the data as soon as possible, you can choose Continuously option. Otherwise, choose schedule option. Select the Continuously option and click the Next button.

Initialize Subscription

Initialize subscription if it's needed.

Start Required Services

Click Next button.

Complete Wizard

Click Finish button to create the subscription. Now, you will receive the following:

End of the work

Visit The SQL Server Worldwide User's Group for all the latest news and information about SQL Server, Oracle, DB2 and XML for developers and administrators.

(c) 1997, 2005 Bits on the Wire, Inc