Improving Availability
Replication can be used to replicate data to a standby server, which provides increased availability in case of planned or unplanned system outages. Replication should be used to provide a warm standby if the data required at the standby is a subset of the data required at the primary server. Also consider the following:
If your application requires data at multiple sites to increase scalability and availability, see Improving Both Availability and Scalability.
If your application requires an entire database to be available at a standby server, use database mirroring rather than replication. Database mirroring is more efficient if the whole database needs to be synchronized, and there is no need to use the secondary server for queries. For more information, see Database Mirroring Administration.
The following diagram shows a primary server and a single standby server, with a subset of the data at the primary server available at the secondary server.
.gif "Replicating data to a standby server")
Note
Replication does not provide a mechanism to fail over from one server to another standby server. Any applications that access a given server must be programmed to use another server in the event that the first server is not available.
Adventure Works Cycles Example
Adventure Works Cycles is a fictional manufacturing company used to demonstrate database concepts and scenarios. For more information, see AdventureWorks2008R2 Sample Databases.
Adventure Works Cycles has a number of servers throughout their manufacturing facilities that collect data on defects in the production lines. They use replication to provide availability for these servers. They have written code to redirect queries to a warm standby server during planned and unplanned outages.
Common Requirements for This Scenario
Applications that use replication for availability 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 one server must reach the other servers quickly.
The system should have high throughput: it should handle the replication of a large number of transactions.
Replication processing should require minimal overhead.
The data required at a secondary server might be a subset of the data available at the primary server (see first diagram above).
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, the primary server is the Publisher. Some or all of the data at the primary server 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 standby server is a Subscriber to the publication, receiving schema and data as a subscription. 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
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: