SQL Server replication, mirroring, log transfer, and failover cluster differences

Replication: Data transfer to other servers to store, do backup.
Mirror: The primary server database is hung and the other one is automatically run as the primary server.
LOG: a controllable image.
Cluster: The primary server is hung, and the other one is automatically run as the primary server, not limited to the database.

Details: Quoted http://hi.baidu.com/jaimy_jie/blog/item/6e099ed52f42a4d250da4b24.html

One, database replication

SQL Server 2008 database replication is a publish/subscribe mechanism for synchronizing data between multiple servers, which we use for synchronous backups of databases. Synchronous backup here refers to the backup server and the primary server for real-time data synchronization, normally only use the primary database server, the backup server only when the primary server failure to put into use. It is a database backup solution that is superior to file backup.

The replication of SQL Server is divided into:

1. Snapshot release:

The publisher sends a snapshot of the published data to the Subscriber at a predetermined interval. Delete all data from the corresponding tables in the subscription database at intervals, and then plug all of the corresponding tables into the subscription database

Using snapshot replication itself is the most appropriate:
1) rarely change data.
2) allow for a period of time a copy of the data that is obsolete to the publisher server.
3) Copy a small amount of data.
4) There are a lot of changes in the short term.

Snapshot replication is most appropriate when the amount of data changes is large, but changes are rare. For example, if a sales organization maintains a list of product prices and these prices are to be fully updated one or two times a year at a fixed time, it is recommended that the full data snapshot be replicated after the data changes. For certain types of data given, more frequent snapshots may also be appropriate. For example, if you update a relatively small table at the publisher during the day, but you can accept a certain latency, you can pass the change as a snapshot at night.

The continuous overhead of snapshot replication at the publisher is lower than the cost of transactional replication, because incremental changes are not tracked. However, if the dataset you are replicating is very large, you will need to use a lot of resources to build and apply the snapshot. When you evaluate whether to use snapshot replication, you need to consider the size of the entire dataset and how often the data is changed.

2. Transactional Publishing:

After the subscriber receives the initial snapshot of the published data, the publisher streams the transaction to the Subscriber.

Transactional replication typically starts with a snapshot of the publication database objects and data. After the initial snapshot has been created, data changes and schema modifications made at the publisher are usually passed to the subscriber at the time of the modification (almost real-time). Data changes are applied to subscribers in the order that they occur at the publisher and transaction boundaries, so transactional consistency can be guaranteed within the publication.

Transactional replication is appropriate for each of the following scenarios:
1). You want the incremental changes to propagate to subscribers when they occur.
2). When changes are made from the Publisher to the Subscriber, the application needs a short lag between the two.
3). The application needs to access the intermediate data state. For example, if a row is changed five times, transactional replication will allow the application to respond to each change (for example, firing a trigger) rather than responding to the row's final data change.
4). The publisher has a large number of INSERT, update, and delete activities.
5). The Publisher or subscriber is not a SQL Server database (for example, Oracle).

By default, Subscribers to transactional publications should be treated as read-only because changes will not propagate back to the publisher. However, transactional replication does provide the option to allow updates at the Subscriber

3. Transactional publications with updatable subscriptions:

After the SQL Server Subscriber receives the initial snapshot of published data, the publisher streams the transaction to the Subscriber. Transactions from Subscribers are applied to the publisher.

4. Merge publications:

After the subscriber receives the initial snapshot of published data, the Publisher and Subscribers can independently update the published data. Changes are periodically merged. Microsoft SQL Server Compact Edition can only subscribe to merge publications.

As with transactional replication, merge replication typically starts with a snapshot of the publication database objects and data, and uses triggers to track subsequent data changes and schema modifications made at the Publisher and Subscribers. The Subscriber synchronizes with the publisher when connected to the network and swaps all rows that have changed between the Publisher and the Subscriber since the last synchronization.

Merge replication is typically used in server-to-client environments. Merge replication applies to the following scenarios:
1). Multiple Subscribers may update the same data at different times and propagate their changes to the publisher and other Subscribers.
2). Subscribers need to receive data, change data offline, and synchronize changes with the Publisher and other subscribers at a later time.
3). Each subscriber requires a different data partition.
4). Conflicts can occur and you need to have the ability to detect and resolve conflicts when a conflict occurs.
5). The application needs the final data change result instead of accessing the intermediate data state. For example, if a row at the Subscriber changes five times before the subscriber synchronizes with the publisher, the row is changed only once at the publisher to reflect the final data change (that is, the value of the fifth change).

Merge replication allows different sites to work autonomously and merge updates into one consolidated result at a later time. Because updates are made on multiple nodes, the same data may be updated by the Publisher and multiple subscribers. As a result, conflicts may arise when merging updates, and merge replication provides multiple ways to handle conflicts

The disadvantage of replication: The table has a primary key, and the table structure can not be changed later, if the schema is stable is also good, if there are many tables that would be more troublesome

Replication Methods and Procedures:

Http://www.cnblogs.com/dudu/archive/2010/08/26/1808540.html

Http://www.cnblogs.com/killkill/archive/2009/07/17/1525733.html

http://dufei.blog.51cto.com/382644/84645

Http://www.cnblogs.com/wangdong/archive/2008/10/24/1318740.html

Second, database mirroring:

Database mirroring:

The advantage is that the system can automatically detect the failure of the primary server and automatically switch to the mirror server.

The disadvantage is that the configuration is complex and the data in the mirrored database is not visible (in SQL Server Management Studio, you can only see that the mirror database is mirrored, you cannot do any database operations, and the simplest queries do not.) If you want to see the truth, look at whether the data in the mirrored database is correct. Only the mirrored database is switched to the primary database is visible)

Database mirroring is clearly a higher level than log shipping. In its simplest form, it actually works like log shipping, but the production server sends transactions to the mirror server much more often, which means that the update is much faster.

For database mirroring, failover functionality is also required to be done manually. But you can add a third SQL Server, called Witness. Witness can act as a normal SQL Server, but keep an eye on the other two mirror servers. When the primary mirror fails, witness can let the second mirror take over the operation, similar to an automatic failover.

In the case of a failover, any in-progress client transactions will be restarted, and the mirror server cannot guarantee that 100% is not losing data because of the delays that persist in the process.

Third, database log transfer:

As the lowest form of high availability, log shipping (shipping) is essentially an extension of the SQL Server replication feature

Allows the solution provider to create multiple database replicas. Log transfers can be sent to a SQL Server instance synchronously with a copy of the database log. These logs are then replayed on the secondary server to keep the database copy up to date.

Some solution providers use log transport as a way to overcome the drawbacks of database mirroring. Database mirroring is a good technique, but it only allows us to implement a copy of the database. Mirroring can be done in a near real-time manner, so database modifications can be quickly written to the secondary database. This can cause problems if the customer database is corrupted or the database record is accidentally deleted.

There are two main advantages of log transfer. First, the solution provider is able to implement a delay so that the log is not replayed immediately. This is important because if there is a problem with the primary (or mirrored) database, the logs can be intercepted before replay, thus preventing the problem from spreading.

Log transport The second major advantage is that it supports implementing multiple database replicas. Some organizations use log transport as a means of maintaining a database copy in the backup datacenter, which prevents data loss when a problem occurs in the primary data center.

Although log transfer is a complementary measure of database mirroring, it is a standalone technology that can be used independently of mirroring technology.

Http://www.searchdatabase.com.cn/showcontent_11708.htm

Quad, failover Cluster

Cluster technology is the most advanced form of Microsoft usability and requires you to set up a Windows cluster.

Instead of transmitting and mirroring in the cluster, two or more computers will be connected to each other in a shared external memory, typically a storage area network (SAN). The database files are stored on this shared storage, and the same set of SQL Server instances runs on the cluster nodes.

In all nodes in the cluster, only one node is actually active, and if this node fails, the other nodes will start the corresponding SQL Server instance and connect the data files of the shared storage. While the entire failover process often takes only a few seconds, Windows Clustering technology ensures that the client always stares at the active node for any given instance of SQL Server.

Cluster technology is very complex, but it is the most efficient technology to achieve high availability. The cluster relies on a single set of database files compared to the two features described earlier. If these files are corrupted, failover does not work because the failover instance is the same as the corrupted file. With mirroring and log shipping, you can copy files in real time, so you don't have to worry about file corruption. In SQL Server, file corruption rarely occurs, so I think the cluster should be a good choice.

Disadvantage of. One of the important issues is that the implementation of recovery is very expensive. Microsoft supports failback only on hardware that is certified by Windows Server. Another problem is that it requires the use of shared storage

SQL Server replication, mirroring, log transfer, and failover cluster differences