Improving Scalability

  • Article

Middle tier applications often use a single database to store data, which can cause scaling limitations as the load against the database increases. When applications perform more reads than writes, such as with a Web-based catalog, it is possible to scale out the read portion of the workload by caching read-only data across multiple databases and connecting the clients evenly across the databases to distribute the load.

The following diagram illustrates a configuration in which application and Web servers use data from any of the three caching servers.

Using replication to scale read activity

If your application also requires increased availability and/or requires that reads and updates for a given user flow to a specific application server and then to a specific caching server, see the example in Improving Both Availability and Scalability.

Adventure Works Cycles Example

Adventure Works Cycles is a fictional manufacturing company used to demonstrate database concepts and scenarios. For more information, see Samples and 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.

Common Requirements for This Scenario

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.

The Type of Replication to Use for This Scenario

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.

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:

See Also

Other Resources

Replicating Data in a Server to Server Environment

Help and Information

Getting SQL Server 2005 Assistance