Designing a High Availability Strategy - Pro SQL Server Internals - page 624

Make sure that the shared locations you use for backup storage have enough space to accommodate your
backup files. You can reduce the storage size and transmission time and improve the performance of the backup and
restore process by using backup compression if it is supported by the SQL Server version and edition, and if you have
adequate CPU resources to handle the compression overhead.
Log shipping is, perhaps, the easiest solution to set up and maintain. Nevertheless, you should keep in mind
possible data loss and consider combining it with other technologies if such data loss is unacceptable or if automatic
failover is required.

■ Note you can read more about log shipping at: http://technet.microsoft.com/en-us/library/ms187103.aspx .

Replication
In contrast to the technologies that we have already discussed in this chapter, replication is far more than a high
availability solution. The main goal of replication is to copy and replicate data across multiple databases. Even though
it can be used as a high availability technology, it is hardly its main purpose.
Replication works in the scope of publications , which are a collection of database objects. It makes replication a
good choice if you want to protect just a subset of the data in the database, for example, a few critical tables. Another key
difference between replication and other high availability techniques is that replication allows you to implement a solution
where data can be modified in multiple places. It could require the implementation of a complex conflict detection
mechanism and, in some cases, have negative performance impact, although this is a small price to pay in some scenarios.
There are three major types of replication available in SQL Server:

Snapshot replication generates and distributes a snapshot of the data based on some
schedule. One example when this could be useful is a set of tables that are updated based
on some schedule, perhaps once per week. You may consider using snapshot replication
and distribute the data from those tables after the update. Another example is that of a
small table with highly volatile data. In this case, when you do not need to have an
up-to-date copy of the data on the secondary servers, a snapshot replication would carry
much less overhead as compared to other replication types.

Merge replication allows you to replicate and merge changes across multiple servers,
especially in scenarios when those servers are infrequently connected to each other. One
possible example is a company with a central server and separate servers in branch offices.
The data can be updated in every branch office and merged/distributed across the servers
using merge replication. Unfortunately, merge replication requires changes in the database
schema and using triggers, which can introduce performance issues.

Transactional replication allows you to replicate changes between different servers with
relatively low latency, usually in seconds. By default, secondary servers, called subscribers,
are read-only, although you have the option to update data there. A special kind of
transactional replication, called peer-to-peer replication, is available in the Enterprise
Edition of SQL Server, and it allows you to build a solution with multiple updateable
databases hosted on the different servers and replicating data between each other.

Transaction replication is the most appropriate replication type to be used as a high availability technology
for updateable data. Figure 31-9 illustrates the components used in transactional replication. The primary server,
called publisher , has the special job known as Log Reader Agent , which is constantly scanning the transaction log of
the database configured for replication and harvesting log records that represent changes in the publications. Those
log records are converted to the logical operations ( INSERT , UPDATE , DELETE ) and are stored in another distribution
database , usually on another server called distributor . Finally, the distributor either pushes those changes to
subscribers or, alternatively, subscribers will pull them from the distributor based on the replication configuration.