Cry How to...

SQL Server Replication

SQL Server Replication Topics

Capabilities of SQL Server Replication
SQL Server Replication Terminology
Which type(s) of replication do you need?
How to set up a distribution database
How to set publication options
How to create a new publication
How to subscribe to a publication

These notes assume SQL Server 7.

Capabilities of SQL Server Replication

SQL Server 6.5 only allows read-only copies of data to be replicated. However, with SQL Server 7, updateable copies of data can be replicated. This means that copies of the same data can be available on two different servers and should the data on one server be updated then that change will be replicated to (i.e. duplicated on) the other server.

SQL Server Replication Terminology

An understanding of SQL Server Replication terminology is useful, but not essential, for setting up and maintaining a replicated system. The following is a list of the terms used with SQL Server Replication.

Article: An article can be an entire table, select rows from a table, specified columns from a table or a stored procedure. An article is what is to be replicated. See also publication.
Distribution Agent: The distribution agent is a process that moves transactions and snapshots held in the distribution database to subscribers. See also Snapshot replication and Transaction replication.
Distribution Database: A distribution database tracks the changes (to publications) that need to be replicationed (from publishers). A publisher can act as its own distributor, or a remote database server can be used to hold the distribution database.; For transactional replication, the distribution database tracks changes made to publications.; For merge replication it stores only synchronization history.
Distributor: The server that contains the distribution database.
Immediate Transactional Consistency: All sites are guaranteed to always see the same data as the publisher, with no time lag. With immediate transactional consistency each site must simultaneously commit the change. Immediate Transactional Consistency therefore has a performance impact, because of which it is unlikely to be suitable for high performance databases or where replicating over a slow LAN or a WAN.
Merge Agent: The merge agent is a process that merges changes between publication and subscription databases where merge replication is used. See also Merge Replication.
Merge Replication: Type of replication that allows changes to the data to be made at any site. Changes to publications are then merged with the copies of that publication held at the other server(s). Merge replication cannot guarantee transactional consistency because the same or related records can be updated on different servers at the same time. See also snapshot replication and transactional replication.
Publication: The data to be replicated is contained with a publication. A publication can contain a selection of tables and stored procedures. A table included in a publication is called an article. A publication therefore defines the set of data that is to be replicated. Each publication can be replicated as a snapshot publication (using snapshot replication), a transactional publication (using transactional replication) or a merge publication (using merge replication). See also article.
Publisher: A publisher is a server that makes data available for other servers, i.e. it is the server that makes data available for replication.
Pull Subscription: With pull subscription the subscriber asks the publisher for periodic updates. With pull subscription each subscriber can specify the best time for the update. Pull subscription is generally considered the better form of replication when a large number of subscribers are involved, or where the load on the LAN needs to be distributed.
Push Subscription: With push subscription the publisher pushes any changes made to the data out to the subscribers. Typically push subscription is used where changes need to be replicated to the subscribers shortly after the change occurs, i.e. where the the replication of changes is required in near real time.
Replication: The capability to copy (i.e. replicate) data and changes to that data from one database to another.
Snapshot replication: Type of replication where a snapshot is taken of the current publication data. The subscribers copy of the data is then replaced with the entire contents of that snapshot. Snapshot replication requires less processor overhead than either merge or transaction replication because it does not require continuous monitoring of data changes. Probably not suitable for replicating large volumes of data (i.e. large tables) because of the network traffic involved. See also transaction replication and merge replication.
Subscriber: A subscriber is a server that receives updates to the data. Each subscriber is associated with a publisher.
Transactional Consistency: With transactional consistency all sites are guaranteed to have the same data as the publisher. Sites may lag behind the publisher, but the view at each subscriber will be the same as that at the publisher at some point in time.
Transactional Replication: Type of replication where the copies of the transactions are replicated to each subscriber. Transactional replication uses the transaction log to capture changes. These changes are then sent to subscribers and applied in the same order. This guarantees transactional consistency. Transactional replication is well suited where near real-time updates are required. See also snapshot replication and merge replication.

Which type(s) of replication do you need?

The three different types of replication (merge, snapshot and transaction) cannot be mixed within a publication, i.e. for each publication only one type of replication can be used. However, given that there is no limit on the number of publications that can be defined this should not be a problem (if you need a table to participate in a different type of replication from other tables simply place it in another publication).

Each type of replication is suited to different requirements, as illustrated below:

	Merge	Snapshot	Transaction
Edit anywhere	Yes	No	No
Only changes replicated	Yes	No	Yes
Suited for large quantities of data	Yes	No	Yes
Replicate stored procedures	No	Yes	Yes
Read only at subscriber	No	Yes	Yes
Consistency guaranteed	No	Yes	Yes
Complete refresh of data	No	Yes	No

It would be wise to plan for each article/table which form of replication would be most appropriate. As a general rule, I would recommend merge replication for tables that need to be updateable at any server and transaction replication where updates are only to be made at the publisher.

How to set up a distribution database

The distribution database tracks what information needs to be replicated from the publisher to the subscribers. It can be held on a separate server to the publisher or on the same server. (Personally I think it makes more sense for it to be on the same server as the publisher.)

Be aware that replication requires heavy use of the distribution database. It is suggested that the initial size of the data device should be at least 30MB and for the log device at least 15MB.

To set up a distribution database:

Run Enterprise Manager
From the server manager window select a server that is to hold the distribution database.
From the ‘Tools’ menu select ‘Replication’, then from the drop down menu that appears select ‘Configure Publishing and Subscribers...’.
If distribution database has not already been created then the ‘Configure Publishing and Distribution Wizard’ will now run. Click '[Next >]' on the first screen.
On the window ‘Choose Distributor’ - I would recommend using the publisher-server as its own distributor, but another server may be selected at this point. Click ‘[Next >]’.
On the window ‘Use Default Configuration’ - I would recommend using the default settings. Any servers already registered with the server will be listed as potential subscribers. Subscribing servers can be added later if need be, but if you already know what servers you want to replicate to then it would be as well to register them with Enterprise Manager prior to creating the distribution database. If you are not happy with the default list of servers or the location for the distribution database then do not choose the default settings. Click ‘[Next >]’ to progress to the next screen.
If you selected the default configuration then you can select ‘[Finish]’ now. Otherwise you can configure the distribution database name and location, which server is to act as publisher and which databases are to participate in replication.

How to set publication options

The publication options cover all the options relating to the publication of data, short of defining the data itself. It provides a means of setting or changing:

The distribution database, and its properties.
The publishers
The databases to be published
The subscribers - including the subscription schedule, i.e. the frequency at which replication will take place.

An initial set of publication options will normally have been set up when the distribution database was configured. To change or reconfigure the publication options:

Run Enterprise Manager
From the server manager window select the publisher-server.
From the ‘Tools’ menu select ‘Replication’ , and from the sub-menu select ‘Configure Publishing, Subscribers and Distribution...’

The distributor, publisher, published database and subscriber options are each held on different tabs.

To configure when or the frequency at which each subscriber will receive any updates, select the 'Subscribers' tab. Double clicking the subscriber of interest will show its subscription properties. One of the tabs is 'schedules'. On this the timing and frequency of updates for replication can be set.

If the publisher and subscriber are in different NT domains then some thought must be given to which login account the replication agents are to use on the subscriber. To show or configure which accounts are used, select ‘Replication’ from the ‘Tools’ menu, from the sub-menu select ‘Configure Publishing, Subscribers and Distribution...’, click the ‘Subscribers’ tab and double click the subscriber. If the account used by the replication agents must be a valid account at the subscriber. If only transaction (or snapshot) replication is to be used then it must have ‘sysadmin’ or ‘db_owner’ roles for the database being replicated. If merge replication is to be used then it must have the 'sysadmin' role for the database being replicated.

How to create a new publication

A publication defines the data that is to be replicated, and the method of replication. Before a publication is created the distribution database must already have been setup and configured.

To create (or modify) a publication:

Run Enterprise manager
From the server manager window select the publication server.
From the ‘Tools’ menu select ‘Replication’, and from the sub-menu select ‘Create and Manage Publications’. The ‘Create and Manage Publications on {server}’ dialog box should now appear.
To create a new publication first select the database that contains the data to publish and then click ‘[Create Publication...]’. The 'Create Publication Wizard' should now run.

Note:

Unless you absolutely need every server to be completely in step with every other don’t use ‘immediate-updating subscriptions’. This option will cripple performance, because no transaction can complete unless it can complete on every other server.

How to subscribe to a publication

To subscribe to a publication:

Run Enterprise Manager
From the server manager window select the publisher-server.
From the ‘Tools’ menu select ‘Replication’ , and from the sub-menu select ‘Create and Manage Publications...’.
Expand the database of interest and select the publication that is to be subscribed to.
Click ‘[Properties and Subscriptions]’
Select the ‘Subscriptions’ tab.
Click the ‘[Push New...]’ button to add a new subscriber. This will cause the ‘Push Subscription Wizard’ to run.