SQL 101 - Replication vs. Log Shipping vs. Clustering

Continuing the "Englishification" of SQL Server for those new to the product...

These three terms are the most incorrectly used terms in all of SQL Server, not just by CEOs and pointy haired managers but by some very sharp developers and more than a few experienced DBAs. If you don't know them, pull up a virtual chair for a 3 minute primer.

Clustering - its all about high availability and uptime

In its most basic form, a Windows/SQL "Cluster" is two or more servers (nodes) attached to a shared storage - a SAN. Only ONE of the nodes is running the SQL Server instance at any given time. So...you can have a 4 node cluster with one SQL Server instance, and it will NOT be running 4 times faster. If the node you are running on suffers a meltdown, the Cluster service moves it to another node. Key Point: This is NOT a fully redundant solution!!! If the SAN dies, your data is gone. Period. Go find your backups.

Log Shipping - move that data!

Log Shipping's sole purpose is disaster recovery. There is a secondary benefit that you can use the destination server as a reporting server if you set it up in a specific configuration.

LS is nothing more than a glorified backup/copy/restore process with a GUI and jobs:
Backup the data on Server A.
Copy the backup files to Server B
Restore the files on Server B.

You cannot edit the data on Server B...just read it.

Replication (no, I won't discuss the different types here):

Replication is all about distributed processing. This means having the data in two different places (Walla Walla, WA and Kissimmee, FL for example) so users don't have to depend on the server and WAN in the remote city. Or for sales/field personnel entering data from their cars.

Replication can be a partial DR solution, but understand that not everything gets replicated (security changes), and only new data gets sent automatically. Schema changes, new tables, etc. do not.


Summary:

• Clustering - High availability is its only purpose.
• Log Shipping - Disaster recovery/possible reporting server
• Replication - Distributed data processing with some DR benefit.
• NONE of these are gonig to increase performance!!!!

Yes, you can combine some of these. Set up two clusters in different cities and Log Ship between them. Now you have HA and DR. Expensive, but effective.

For all the SQL Experts that are chomping at the bit ready to scream that I left out what LUN is, or didn't discuss Geoclustering, please see the post title...this is a 101 level post :)

Thanks,

Kevin3NF