SQL Server 2008 R2 Basics: HA Features



Replication
Replication is a server-based tool that you can use to synchronize data between two or more DBs. Replication can send data from one SQL Server to another, or it can replicate data to Oracle, Access, or any other DB that is accessible via ODBC or OLE DB. SQL Server supports three kinds of replication:

The availability and functionality of replication might be restricted, depending on the edition of SQL Server 2008 you are running.



Snapshot Replication
With snapshot replication, the server takes a picture, or snapshot, of the data in a table at a single point in time. Usually, if this operation is scheduled, the target data is simply replaced at each update. This form of replication is correct for small data sets, infrequent update periods (or for a one-time replication operation), or management simplicity.



Transactional Replication
Orginally set up with a snapshot, the server maintains downstream replication targets by reading the transaction log at the source and applying each change at the targets. For every insert, update, and delete operation, the server sends a copy of the operation to every downstream DB. This is correct if low-latency replicas are needed. Transactional replication can typically keep DBs in sync within about five seconds of latency, depending on the underlying network infrastructure. Keep in mind that transactional replication does not guarantee identical DBs at any given point in time. Rather, it guarantees that each change at the source will eventually be propagated to the targets. If you need to guarantee that two DBs are transnationally identical, you should look into Distributed Transactions or DB mirroring. Transactional replication might be used for a website that supports a huge number of concurrent browsers but only a few updates, such as a large and popular messaging board.

All updates would be done against the replication source DB and would be replicated in near-real-time to all the downstream targets. Each downstream target could support several web servers, and each incoming web request would be balanced among the web farm. If the system needed to be scaled to support more read requests, you could simply add more web servers and DBs and add the DB to the replication scheme.



Merge Replication
With snapshot and transactional replication, a single source of data exists from which all the replication targets are replenished. In some situations, it might be necessary or desirable to allow the replication targets to accept changes to the replicated tables and merge these changes together at some later date. Merge replication allows data to be modified by the subscribers and synchronized at a later time. This synchronization could be as soon as a few seconds, or it could be a day later. Merge replication would be helpful for a sales DB that is replicated from a central SQL Server DB out to several dozen sales laptops. As the sales personnel make sales calls, they can add new data to the customer DB or change errors in the current data. When the salespeople return to the office, they can synchronize their laptops with the central DB. Their changes are submitted, and the laptops get refreshed with whatever new data was entered since the last synchronization.



Immediate Updating
Immediate updating allows a replication target to immediately modify data at the source. This task is accomplished by using a trigger to run a distributed transaction. Immediate updating is performance intensive, but it allows for updates to be initiated from anywhere in the replication architecture.



DB Mirroring
The DB mirroring feature available in SQL Server 2008 offers a solution for increasing DB availability. Essentially, DB mirroring maintains two copies of a single DB that reside on different instances of SQL Server, typically on server instances that reside on systems in different locations. In a typical DB mirroring scenario, one server instance serves as the primary DB to which the client apps connect, and the other server instance acts as a hot or warm standby server. DB mirroring involves re-applying every modification operation that occurs on the primary DB onto the mirror DB as quickly as possible. This is accomplished by sending every active transaction log record produced on the primary server to the mirror server. The log records are applied to the mirror DB, in sequence, as quickly as possible. Unlike replication, which works at the logical level, DB mirroring works at the level of the physical log record. The mirror DB is an exact copy of the primary DB.