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 recently upgraded their website to include the following new features:

• Online product ordering for customers.
  
• Online order status checking.
  
• Better search capabilities of product literature.
  

Allowing online product ordering from the website greatly increased the activity on the company's single computer dedicated to Microsoft SQL Server. Adventure Works administrators decided to use this computer as a source for replicated data. All read activity was scaled out to three additional computers running SQL Server, which cache data from the source computer. The caching computers handle all read activity, including users browsing and searching the product catalog, and checking order status. All write activity is directed to the source database.

Applications that use replication for scalability 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 source must reach the cache 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 source.
  
• The data required at the cache might be a subset of the data available at the source.
  

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. For more information on the components of the system, see Replication Publishing Model Overview.

In the diagram above, the source is the Publisher. Some or all of the data at the source is included in the publication, with each table of data being an article (articles can also be other database objects, such as stored procedures). Each cache is a Subscriber to the publication, receiving schema and data as a subscription.

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
  

The primary option to consider for this scenario is filtering. Transactional replication allows you to filter columns and rows, so the tables at Subscribers contain only the data required by your application. For more information, see Filtering Published Data.

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:

• Configuring Distribution
  
• Publishing Data and Database Objects
  
• Subscribing to Publications
  
• Initializing a Subscription
  

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:

• Monitoring (Replication)
  
• Strategies for Backing Up and Restoring Snapshot and Transactional Replication
  
• Troubleshooting (Replication)
  
• Removing Replication
  

Other Resources

Help and Information