Database replication is all about distributing data. There are many common scenarios where replication may make sense, whether it is to enable a mobile workforce, to allow intensive reporting or data mining without interfering with day-to-day operations, or to improve data availability.
At the most basic level, changes made to one database (the publisher) are distributed to one or more targets (the subscribers). This way, you can distribute data to different locations and to remote users over local networks or the Internet.
The first step in deciding whether replication is a good fit for your organization is to consider the business problems that need to be solved. Microsoft breaks these down into two broad categories: “server to server” and “server and client” (where a client may be a workstation, tablet, laptop, mobile device, etc.):
Server to Server Scenarios
Improving scalability and availability
By automatically writing data to multiple servers in near-real time, you can achieve a double benefit:
In terms of availability, replication is just one of several available technologies. It should be noted that replication is not a replacement for a proper backup strategy!
Data warehousing and reporting
Replication can be used to move data from your database to another server where it is then processed for use in reporting, dashboards or other business intelligence systems.
Integrating data from multiple sites
Data is often "rolled up" from remote offices and consolidated at a central office. Similarly, data can be replicated out to remote offices.
Integrating heterogeneous data
It is not uncommon for organizations to store data in databases from multiple vendors, perhaps even running on different operating systems. In addition to other integration technologies (such as Integration Services), SQL Server can support data replication with certain Oracle and IBM (DB2) databases.
Offloading batch processing
Certain batch operations may have a lot of overhead that could interfere with day-to-day operations if run on the main server. Offloading this means that the processing can occur on another server, and the (relatively small) result can be passed back to the main server.
Server and Client Scenarios
Exchanging data with mobile users
Many applications require data to be available to remote users, including sales people, delivery drivers, and so on. These applications include customer relationship management (CRM), sales force automation (SFA), and field force automation (FFA) applications.
Consumer point of sale (POS) applications
POS applications, such as checkout terminals and ATM machines, require data to be replicated from remote sites to a central site.
Integrating data from multiple sites
Applications often integrate data from multiple sites. For example an application that supports regional offices might require data to flow in one or both directions between regional offices and a central office.
Once you’ve determined that replication is a good fit, the next step is to determine what topology to use in your scenario.
Microsoft SQL Server 2008 R2 provides several different topologies when it comes to replication. Selecting the appropriate type of replication depends on many factors including the physical environment, the amount of data to be replicated, and whether the data can be updated at the subscribers (and, therefore, conflicts are possible).
Replication typically begins with an initial synchronization of the published objects between the Publisher and the Subscribers – a “snapshot” is created, which is a copy of all of the objects and data specified as part of the publication. After this initial synchronization, what happens depends on the type of topology you’ve chosen.
This TechNet article gives a good overview of the suitability of each kind of replication:
Is Appropriate When
Is Often Used
Data changes infrequently.
It is acceptable to have copies of data that are out of date with respect to the Publisher for a period of time.
Replicating small volumes of data.
A large volume of changes occurs over a short period of time.
Server to Server
You want incremental changes to be propagated to Subscribers as they occur.
The application requires low latency between the time changes are made at the Publisher and the changes arrive at the Subscriber.
The application requires access to intermediate data states. For example, if a row changes five times, transactional replication allows an application to respond to each change (such as firing a trigger), not simply the net data change to the row.
The Publisher has a very high volume of insert, update, and delete activity.
The Publisher or Subscriber is a non-SQL Server database, such as Oracle.
Server to Server
Multiple Subscribers might update the same data at various times and propagate those changes to the Publisher and to other Subscribers.
Subscribers need to receive data, make changes offline, and later synchronize changes with the Publisher and other Subscribers.
Each Subscriber requires a different partition of data.
Conflicts might occur and, when they do, you need the ability to detect and resolve them.
The application requires net data change rather than access to intermediate data states. For example, if a row changes five times at a Subscriber before it synchronizes with a Publisher, the row changes only once at the Publisher to reflect the net data change (that is, the fifth value).
Server and Client
SQL Server Editions
Once you’ve determined the topology that makes sense for your scenario, you need to ensure that you are using an appropriate edition of SQL Server:
Up to 25 as Publisher
Up to 5 as Publisher
Publishing data from SQL to non-SQL subscribers
Publishing data from Oracle to SQL