Mirroring vs. Log Shipping in Sql Server 2005

sql-server-2005 failover mirroring log-shipping
Karim picture Karim · Nov 25, 2008 · Viewed 8.5k times · Source

I'm interested in hearing people's thoughts about the pros and cons of database mirroring vs. log shipping in this scenario: we need to setup a database backup situation wherein there is exactly one secondary server that need not automatically pick up when the primary fails. Recovering and starting with the secondary should not have to take too long though.

Answer

Josiah Peters picture Josiah Peters · May 29, 2009

Mirroring

  • Database mirroring is limited to only two servers.
  • Mirroring with a Witness Server allows for High Availability and automatic fail over.
  • You can configure your DSN string to have both mirrored servers in it so that when they switch you notice nothing.
  • While mirrored, your Mirrored Database cannot be accessed. It is in Synchronizing/Restoring mode.
  • Mirroring with SQL Server 2005 standard edition is not good for load balancing (see sentence above)

Log Shipping

  • You can log ship to multiple servers.
  • Log shipping is only as current as how often the job runs. If you ship logs every 15 minutes, the secondary server could be as far as 15 minutes. Making it more of a Warm Standby.
  • You can leave the database in read only mode while it is being updated. Good for reporting servers.
  • Good for disaster recovery

Related questions

Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More