Export (0) Print
Expand All

Keeping Data Close to Users

SQL Server 2000

Data distribution is the process of ensuring that data is available to people when they need it. Here are examples of using replication to give data to the users who need it. Publish data:

  • From a central site, partitioning it, and distributing it to various regional offices.

  • To a read-only database so that users can execute queries and analyses without interrupting transaction processing on a production database.

  • From multiple databases into a central database, which could be a data mart or data warehouse.

  • To a backup database as part of a standby solution.

  • To support mobile, disconnected users.

  • Over the Internet, so it can be available on-demand with  anonymous subscriptions.


Suppose the company Northwind Traders (originators of the Northwind sample database) has regional offices around the world. Some regional offices will only be reading the data, while other offices are responsible for keeping up-to-date information on the customers and orders in that particular region.

Using replication, Northwind Traders can replicate partitions of the central orders online transaction processing (OLTP) database to each region, and filter the data based on the city, region, or user who is accessing the data.

If the regional office only needs to read the data and not make changes, the central office can filter the data to create the appropriate partitions based on region or other criteria and then publish that data to Subscribers. Depending on how often and the how much data is modified at the publishing site, this type of application could use snapshot replication or transactional replication.

If a regional office will make changes to the data and needs autonomy, the data can be filtered, replicated to the region, and the regional office can make changes to its data as needed. When the changes need to be propagated to the corporate office or to other regions, the regional office can synchronize with the corporate office and those changes will be propagated automatically to the other regions when they synchronize with, and are accepted by, the corporate office. If the regional office needs to distribute the corporate data to its sales force within the region, it can republish the data to the necessary sites.

There are several options for scheduling distribution of the data and modifying the data at the different regional offices. If the regional offices are continuously and reliably connected, multiple offices can update the data and propagate changes to the corporate office immediately. The data is then propagated to other regions within seconds (immediate updating), or if a site is disconnected for a limited amount of time, data modifications can be stored in a queue until the connection with the corporate office is reestablished (queued updating).

© 2015 Microsoft