Home > Articles > Data > SQL Server

SQL Server Replication

Defining replication is pretty easy. The basic idea is that you want to copy data from one place to another, automatically. SQL Server 2000 can do that for you. In this excerpt from his book, Essential SQL Server 2000: An Administration Handbook, Buck Woody provides an overview of replication and how it is used.

This article is excerpted from Essential SQL Server 2000: An Administration Handbook (Addison Wesley, 2001, ISBN: 0201742039).

This chapter is from the book

Defining replication is pretty easy. The basic idea is that you want to copy data from one place to another, automatically. SQL Server 2000 can do that for you.

While the concept of replication is simple, several functions are involved, such as collecting the data, defining the conditions for moving the data, and moving the data.

These functions can be installed on different servers or on the same system, but the processes they use will be the same regardless of where they are installed.

The following functions and terms are used in replication:

  • Publisher
  • Publication
  • Article
  • Distributor
  • Subscribers
  • Subscription

The Publisher is the server that has the data you want to replicate. It makes the data available in a package called a Publication. You can set this server to "push" the data out to the receiving server, or have the receiving server "pull" the data in.

A Publication is made up of the various pieces of data you can replicate, such as tables, views, and so on—each individually called an Article.

The Distributor is the next function in the transfer. This function does the work of transferring publication out, and its job changes a bit based on the type of replication you are doing.

The system that receives the data is called the Subscriber. This server can request to "pull" the data from the Publisher rather than the Publisher "pushing" the data.

The Subscriber server, then, will have the information required to get the data from the Publisher, called a Subscription. It's important to note that a Subscription is to a Publication, not an Article. In other words, the Subscriber doesn't pick and choose the data that sent to it, all of the data is what it receives. That can become an important distinction.

SQL Server 2000 uses services called Agents that watch the various pieces of data and schedules to perform the replication.

So to summarize, a Publisher makes a group of information (the Publication, composed of Articles) available for replication. The Distributor performs the work of actually transferring the data to a Subscriber, which has a Subscription for that Publication.

There are two parts to setting up replication—planning and implementing. Never skip the planning step; this simply isn't one of those things that you can follow a wizard for and expect to work.

This planning is normally done in the order I describe here, but you will find that you'll move back and forth through the steps as each decision affects the others.

The first thing you need to plan for replication is the type of replication. The types of replication have a great deal to do with how you'll set up the rest of your plan.

The types are

  • Snapshot
  • Transactional
  • Merge

Snapshot replication is taking an entire set of data and replicating it to another database. This is the most straightforward, easy to set up type, but usually has the largest data size.

Because of its size, this type is not always the best choice for disconnected remote clients. It's normally better suited to static data sets such as lists and catalog items.

Transactional replication takes an initial snapshot of data as well, but then tracks the changes that have been made at the Publisher and sends them to the Subscriber. This type of replication is typically smaller, and is often used for remote clients.

Merge replication allows for updates from both the Publisher and Subscriber of the data. This is very useful for remote clients who make changes in a disconnected fashion in the field to keep the office data set current, as well as receive changes from the office. This type can be a bit more involved to implement due to conflict resolution.

Once you've decided on the type of replication, you'll also need to decide on the physical layout. It's at this step that you decide which server will be the Publisher and which will be the Distributor. Often these are the same machine, but the load placed on your server from each function will have a performance impact.

Another impact in the physical layout choice is the network position of the server.

If the Publisher is behind a firewall, it may be necessary to have the Distributor on the other side of the firewall so that your remote clients can access the Distributor.

The next step in the plan involves selecting the data that you want to replicate.

You can replicate many forms of data, from a complete table to only parts of it, stored procedures or their output, views or their output, and even a user-defined function or its output. Often you'll want just a small subset of your data at another site, and sometimes you need an entire database to be sent to the Subscriber. The factors to consider here are the size of the data and the speed and quality of the connection, and of course the use of the data.

The next decision will be whether the Publisher sends the data along at a defined point, called a "push" subscription, or whether the Subscriber will go get the data at a specified point, called a "pull" subscription. If the user is using a laptop, you may not know when they are online, so you'd want a pull subscription. If you have a constant-connected link to the Subscriber you'll normally set up a push subscription.

Not only are the requirements a factor in this decision, setting the replication timing is vital, especially on push subscriptions. If the links to the Subscribers are constant-connected but very slow, the data should be transferred more often so that it doesn't pile up. This timing aspect is probably one of the most difficult parts of planning.

Now that you have your functional planning done, you'll set up your replication schema.

Setting up the schema involves defining all the pieces from the terms I mentioned earlier. The Publisher, Subscribers, Articles and the rest are all detailed at this stage.

Finally, you'll set the monitoring in place to make sure your replication works. Whenever you set up a process to happen automatically, whether it is backups or any other server process, you don't let it run blindly.

While you do have a feedback mechanism (via e-mail) to monitor maintenance plans, that process is a bit more hands-on with replication.

Let's begin the planning process with exploring the types of replication.

Types Of Replication

There are three main types of replication:

  • Snapshot
  • Transactional
  • Merge


Snapshot replication is taking the data you want to move, in its entirety, and copying it to another server.

Snapshot replication is really well-suited to users who need an off-line copy of some data.

Suppose your sales force has a price list that should be updated daily. You might set the database at their site to pull data early in the morning and then use the data in a static mode throughout the day.

Because you're copying an entire data set that might be quite large, you'll want to limit how often you set this replication to happen.


Transactional replication is where the changes on one server are replicated to another. This means that each time your users perform an insert, select, update, or delete on data in the Publisher, the Distributor sends it on to the Subscriber. This can happen instantly or pile up to go at a certain point in time.

One important thing to keep in mind with this type of replication is that it's an all-or-nothing proposition. If one of the changes in a transaction fails to go to the Subscriber for whatever reason, none of the changes will go. Because of that, this is the most difficult type of replication to monitor, although it's often one of the most useful types.

Transactional replication is used when you need to keep two data locations in sync, or if the data just gets too big to replicate with snapshot replication. Let's look at another example.

Headquarters records sales all day long, and inventory is reduced. Field offices need to know the new inventory counts instantly so that they don't sell what they don't have.

With these requirements, you'd set up transactional replication with the data pushing from the Publisher at headquarters out to the Subscriber servers at each office as soon as the changes occur. This process assumes that you have a constant connection to each office.

An interesting feature is the ability of the Subscriber to make changes based on received data and send those changes back to the Publisher, called two-way replication.

While you can use transactional replication over disconnected or dial-up methods, it requires a bit more monitoring.


Merge replication allows two copies of a data set to be synchronized. The toughest thing to decide here is how to handle conflicts, but if your data lends itself to this type of replication, you shouldn't encounter that too often.

The process involves sending an initial snapshot to the replication partner, and at synchronization the two systems accept each other's data. If there is a conflict, you have a lot of options on how to handle them, including setting default options (like Server A always wins) or having the system prompt you for what to do. Let's look at another example.

The sales force tells you that while they enjoy having a copy of the inventory, what they'd really like is to be able to record actual sales information on the spot. To do this, they need to reduce inventory by the sales amount and bill the customer.

To fulfill this requirement, you'd set up merge replication between a database on their laptops and the server. You could replicate only the data from their region if you wish, guaranteeing that they wouldn't see each other's customers.

One thing to keep in mind is that merge replication is the most restricted type—you can't merge-replicate the results of a stored procedure or an indexed view.

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.


Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.


If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.


This site is not directed to children under the age of 13.


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020