November 10, 2014

Posted in:

Tagged:


What is Microsoft SQL Server Log Shipping?

Microsoft SQL Server Log Shipping

Log shipping is an inexpensive and powerful method for increasing database availability. Log Shipping is a feature of Microsoft SQL Server in which recorded changes to a database (transaction logs) are automatically transferred and executed on a secondary database server. In the event that the primary database becomes unavailable, the secondary database server can be quickly promoted to the primary server role to resume service until the primary server is restored. The ability to move database changes in real time from one database to another is not unique to Microsoft SQL Server. Log shipping is simply Microsoft’s implementation of the concept.

SQL Server Log Shipping

 

 

 

 

 

 

 

Log Shipping Use-Cases

When to Use?

  • When cost is a consideration. The secondary SQL server can reside on less powerful system, unlike with SQL mirroring.
  • When SQL Mirroring does not support functionality that is required by the client—Cross-database transactions and distributed transactions are not supported by SQL mirroring. Atomicity/integrity cannot be guaranteed when using these transaction types. Logical inconsistencies could occur if the database fails-over before the transaction is committed.
  • When a client requires a read-only secondary server to improve performance on the primary system, or to prevent the primary database from becoming locked during certain operations such as reporting.
  • When read/write splitting is being used for horizontal scaling, and read data can be out of sync due to log shipping backup intervals.
  • When the projected amount of data loss during a primary server failure is acceptable.

When NOT to Use?

  • When the business impact of an hour of possible downtime is unacceptable.
  • When the business impact of 15 minutes of possible data loss is unacceptable.
  • When “100% operational” is a requirement of your business continuity plan.

Go back