What are the scenarios for using mirroring, log shipping, replication and clustering in SQL Server

Question

Hi,

As far as i know SQL Server provides 4 techniques for better availability.

I think these are the primary usage scenarios, in summary :-

1) Replication would be primarily suited for online-offline data synchronization scenarios (laptop , mobile devices, remote servers).

2) Log shipping could be used to have a failover server with manual switching, whereas

3) Database Mirroring is an automatic failover technique

4) Failover Clustering is an advanced type of database mirroring.

Am i right ?

Thanks.

Answer 1

In SQL 2008 Enterprise there is also something called Change Data Capture (CDC) which we are using successfully where I work.

We have an overly normalized database which makes it too hard to get information out. We needed to change the data structure at the same time as we replicate this data to another server for reports and such.

It works extremely well for us.

Answer 2

AFAIK log shipping and replication would probably be better suited the other way around.

Log shipping is scheduled synchronization, therefor replication would be better suited for manual switching because the backup-server would be as up to date as it could be unless you had any communication-problem (however, log shipping would have the same issue).

offline-data isn't as sensitive to delays as a backup-server, but personally I don't really see the need for log-shipping at all, I can't see when it ever would be a more suitable alternative to replication (but it could be that replication wasn't implemented before sql2005)

Maybe I'm confusing replication with mirroring, and as a note, mirroring doesn't give you automatic failover, only HA-cluster gives you that functionality, meaning:

using atleast SQL server 2005 standard, Windows Enterprise and a shared data-storage (like a SAN).

Answer 3

Hi,

Failover clustering is an availability technology that provides redundancy at the hardware level and is built on top of Windows Clustering technology, i.e. it is not specific to SQL Server.

For example, the processor blows up on Server A. Fortunately Server A is part of a SQL Server Cluster and so Server B takes over the job of providing the SQL Server Service, within a matter of seconds. All of this occurs automatically and is transparent to the database users and or application being served by the cluster.

The main difference between Database Mirroring and clustering is that SQL Clustering provides redundancy at the instance level whereas database mirroring provides redundancy at the database level.

The following link provides a comparison between these two technologies that you may find of use.

http://msdn.microsoft.com/en-us/library/ms191309(SQL.90).aspx

Log shipping is considered more of a redundancy technology.

For example, it can be used to provide a full copy of your primary environment, typically used as a warm standby that can be manually brought online. This can be used to provide additional redundancy to your backup strategy. Log shipping can also be used to offload reporting from a primary server by creating a read only copy of the production database at an alternative location/server.

Replication is quite a diverse technology and can be used to cater for a number of different scenarios, the choice of which will determine the specific type of replication that is implemented.

For example, merge replication can be used to support distributed processing by spreading the workload of an application across several servers, i.e. distributed processing architectures.

Merge replication often requires an application that is relatively aware of its environment. Techniques such as conflict resolution also have to be taken into consideration in order to ensure data consistency across the entire integrated environment.

Transactional Replication can be used in a similar fashion to log shipping however you can limit the specific objects that are replicated to the subscriber. This can be useful if only a subset of tables is required for reporting purposes.

I hope this clears things up for you a little. You can find a wealth of documentation regarding each of these technologies within SQL Server books online, or by searching for each technology in Google. That said if you have any specific queries I would be happy to help so feel free to drop me line.

Cheers, John