Share via

  • Article

Integrating Data from Multiple Sites (Server)

Many companies have regional offices or entities that collect and process data that must be sent to a central location. For example:

  • Inventory data can be "rolled up" or consolidated from a number of servers at local warehouses into a central server at corporate headquarters.

  • Information from autonomous business divisions within a company can be sent to a central server.

  • Order processing information from dispersed locations can be consolidated.

In some cases, data is also sent from the central site to remote sites. This data is typically intended to be read-only data at the remote site, such as a set of product inventory tables that are only updated at a central site.

The following diagram shows a typical scenario, in which data is rolled up from remote sites. Read-only data is also sent to each remote site.

Replicating data to regional offices

Adventure Works Cycles Example

Adventure Works Cycles is a fictional manufacturing company used to demonstrate database concepts and scenarios. For more information, see AdventureWorks Sample Databases.

Adventure Works Cycles has a number of regional sales offices throughout the United States. The offices use replication in two ways:

  • To provide order information for order fulfillment and reporting purposes. Data is collected and processed at each sales office and then replicated to the central office.

  • To provide data and ordering capabilities to their mobile sales staff. This scenario is described in the topic Exchanging Data with Mobile Users.

Common Requirements for This Scenario

Applications for regional offices typically have the following requirements, which an appropriate replication solution must address:

  • The system must maintain transactional consistency.

  • The system should have low latency: updates at the remote sites must reach the central site quickly.

  • The system should have high throughput: it should handle the replication of a large number of transactions.

  • Replication processing should require minimal overhead on the remote sites.

  • Data changes might flow in both directions: in some cases, read-only data is sent to remote sites, in addition to data being consolidated from the remote sites to the central site.

  • The data required at the central site might be a subset of the data available at each remote site.

The Type of Replication to Use for This Scenario

Microsoft SQL Server uses a publishing industry metaphor to describe the components of the replication system. The components include the Publisher, Subscribers, publications and articles, and subscriptions.

  • In the diagram above, each remote site is a Publisher. Some or all of the data at the remote site is included in the publication, with each table of data being an article (articles can also be other database objects, such as stored procedures). The central site is a Subscriber to these publications, receiving schema and data as subscriptions.

  • The central site also serves as a Publisher for the data that is sent to the remote sites. Each remote site Subscribes to the publication from the central site.

For more information on the components of the system, see Replication Publishing Model Overview.

SQL Server offers different types of replication for different application requirements: snapshot replication, transactional replication, and merge replication. This scenario is best implemented with transactional replication, which is well suited to handle the requirements outlined in the previous section. For more information on transactional replication, see Transactional Replication Overview and How Transactional Replication Works.

By design, transactional replication addresses the principal requirements for this scenario:

  • Transactional consistency

  • Low latency

  • High Throughput

  • Minimal overhead

Note

A similar scenario can be implemented with merge replication. If your application requires conflict resolution or filters that provide each remote site with a unique set of data, use merge replication. For more information, see Integrating Data from Multiple Sites (Client).

Steps for Implementing This Scenario

To implement this scenario, you must first create a publication and subscriptions, and then initialize each subscription. Click the links below for more information about each step:

After the subscription is initialized and data is flowing between the Publisher and Subscribers, you might need to consult the following topics for information on common administration and monitoring tasks: