SQL Server 2005 & 2008 R2 Database Replication, Mirroring, Log Shipping and SharePoint.

This research is from SQL Server Replication- Providing High Availability using Database (from Microsoft)

Plan for disaster recovery    |   Plan for Availability    |   SP Programmatic Admin

SharePoint Requirements

See this article that indicates SharePoint supports Mirroring SP Databases Specs including sizing and placement
Async mirroring has no latency requirements, so you can mirror across geo dispersed farms but, only content dbs for the most part as there is potential for data loss and for dbs to come out of sync with original dbs (hence the mirroring name async). This cannot happen and is not supported with config and sa dbs as they must be synced guaranteed and bit data loss is permitted.
In sync mirroring all dbs are always in sync "guaranteed", by nature of how the mirroring works so you can mirror all dbs including config and sa dbs. However sync mirroring requires by nature less than one millisecond latency and that’s typically hard to achieve across geo disp farms, hence our stretched farm requirement…

What is Replication

Transactional replication is the mechanism that Microsoft® SQL Server® provides to publish incremental data and schema changes to subscribers. The changes are published (the replication stream) in the order in which they occur, and typically there is low latency between the time the change is made on the Publisher and the time the change takes effect on the Subscriber. This enables a number of scenarios, such as scaling out a query workload or propagating data from a central office to remote offices and vice-versa. This form of replication always uses a hierarchical hub and spoke topology.

What is Mirroring

Database mirroring works at the database level and provides a single copy of the mirrored database that must reside on a different server instance, usually on a separate physical server in a different location. One server instance serves the database to clients (the principal server). The other instance acts as a hot or warm standby server (the mirror server),

What is Log Shipping

SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually. An optional third server instance, known as the monitor server, records the history and status of backup and restore operations and, optionally, raises alerts if these operations fail to occur as scheduled.

About Log Shipping   |   Log Shipping Administration   |   Configure Log Shipping

 

What is Difference

Database mirroring maintains an exact copy of the database on the mirror server by redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending every active transaction log record to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Unlike replication, which works at the transaction level (by harvesting and forwarding INSERT, UPDATE, and DELETE operations from the transaction log of the publication database), database mirroring works at the level of the physical log record (by sending the actual log records to the mirror server). Database mirroring does not eliminate the need to backup data. In general, if the primary database is corrupted for any reason other than hardware failure, then the mirror database will be corrupted in the same way.

 

More About Mirroring

Various mechanisms in SQL Server provide database-level redundancy, such as backup/restore, log shipping, and database mirroring (in SQL Server 2005 and later). Database mirroring is the only mechanism that provides a real-time, exact copy of the protected database with the guarantee of zero data loss (when the mirror is synchronized).

A database mirroring session runs with either synchronous or asynchronous operation. Under asynchronous operation (also called high-performance mode), transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance. Under synchronous operation (also called high-safety mode), a transaction is committed on both partners, but at the cost of increased transaction latency. In high-safety mode, it is possible to allow automatic failovers by adding a third witness server. In all other configurations, failovers must be performed manually.The transaction safety level of a mirroring session is controlled by the SAFETY property of the ALTER DATABASE statement. Synchronous mirroring is when SAFETY is FULL; asynchronous is when SAFETY is OFF.

http://msdn.microsoft.com/en-us/library/bb934127(SQL.100).aspx for SQL Server 2008

http://msdn.microsoft.com/en-us/library/ms177412.aspx for SQL Server 2005

More about Replication

Transactional replication and peer-to-peer replication use the same architecture to move changes between the servers in a replication topology. The following illustration is an overview of the components involved in transactional replication.

image

The roles (3 above) of the various replication servers can be played by one server or by individual servers (the more common case), and it is possible for a server to play any combination of roles. Regardless, the various servers and databases must be protected to ensure that the replication stream is highly available.

Transactional replication relies on various agents to perform the tasks associated with tracking changes and distributing data. These agents are:

Snapshot Agent, which runs at the Distributor. This agent prepares schema and initial data files of published tables and other objects, stores the snapshot files, and records information about synchronization in the distribution database.

Distribution Agent, which runs at the Distributor for push subscriptions, and at the Subscriber for pull subscriptions. This agent applies the (optional) initial snapshot to the Subscribers and moves transactions held in the distribution database to Subscribers•

 Log Reader Agent, which runs at the Distributor. This agent connects to the Publisher and moves transactions marked for replication from the transaction log of the publication database to the distribution database.

Queue Reader Agent, which runs at the Distributor. This agent is only used for transactional replication with updateable subscriptions and moves changes made on the Subscribers back to the Publisher.

For more detailed information on SQL Server Replication, see the following "SQL Server Replication" topics in SQL Server Books Online:

http://msdn.microsoft.com/en-us/library/ms151198(SQL.100).aspx for SQL Server 2008

http://msdn.microsoft.com/en-us/library/ms151198(SQL.90).aspx for SQL Server 2005

Database Mirroring and Replication Together

The degree to which database mirroring can be combined with transactional replication depends on which replication database is being considered, as the level of support varies by database. Peer-to-peer replication with database mirroring is not supported. The following table lists the replication databases and corresponding level of integration with database mirroring.

Replication database

Integration with database mirroring

Distribution

No. Not a supported combination of technologies.

Publication

Yes, with automatic failover of replication agents.

Subscription

Yes, with manual failover and configuration of the replication agent.

All replication agents that connect to the publication database (that is, Snapshot Agent, Log Reader Agent, Queue Reader Agent, and for completeness, the Merge Agent) are mirroring-aware. They can be configured such that if a mirroring failover occurs, they automatically reconnect to the new principal server, and then replication continues. Therefore, mirroring the publication database is fully supported, but the state of the mirroring partnership and the specific failover scenario can affect the behavior of the Log Reader Agent. This is discussed more fully in the next section, Mirroring the Publication Database.

Advertisements