How to make SQL Server 2008 redundant?
Solution 1:
is it through clustering or Log shipping or VMware (hypervisor) High Availability features ?
No, no, no.
MIRRORING, 3 servers (one can be a small free one, just deciding which keeps working active).
Log shipping is slowe / delayed, clustering / vmware leaves one database as weak point that still can be corrupted.
Solution 2:
TomTom has it correct, but let me expand on a few key points:
Log shipping is great for DR, not great for up to date data. Your data is always old, say 15 minutes to 1 hour or even more old, depending on your settings. The re-connection is not seamless. You need to re-configure your application to point to the new system. Also, the 2nd database is not online, it's usually in recovery mode (to apply the logs), so you need to take it out of recovery mode in order to use it. This is why it's usually used for disaster recovery only.
Clustering is not what you want when you're on a link with any latency at all. It provides a highly available SQL interface, but it does not make your data highly available, unless your SAN can do that for you. I would never try clustering between DC's.
-
Mirroring. In this case, mirroring is your friend. Mirroring has two basic modes: Synchronous, or A-synchronous. Common to both modes: Each SQL server has its own local copy of the database, but only one SQL server can "own" it. In other words, even though you've got three SQL servers, only one of them is actually in use. The others are all in standby mode, waiting to be activated.
In Synchronous mode, when a write transaction is received, it is executed by all of the SQL servers at the same time, and the next transaction is not processed until each SQL server has reported that it's committed the transaction. This ensures that every single SQL server has completely up-to-date data. This is great for high-speed, low-latency links, not so good for high-latency links as it will effectively slow down your database to the speed of the slowest partner.
In asynchronous mode, SQL server does not wait for all the other SQL partners to commit the transaction. It just commits it locally and moves on. Each other SQL server then might be a few transactions behind, especially if the links between them are heavilly loaded. This means you get full-speed access to the active DB, but you run the risk of loosing a bit of data in the case of a failover. Also, this mode is only available on SQL Enterprise edition.
VMWare HA. Well, this is a whole can of worms. It's good, but again you need to share the same SAN for it to work. Or are you talking about VMWare FT (fault tolerance)? If you are, I'd suggest forgetting about FT for an SQL server at the moment. FT is great in theory, and I'm even using it one one VM, but for most deployments the sacrifices you need to make are too great. Also, because the VM's are in lock-step with eachother, and bandwidth issues will murder performance.
To manage the ability to automagically fail-over, Clustering provides you with a virtual instance that you connect to and everything is done invisible to the user. For mirroring, you can achieve this easilly with the Network Load Balancing role running on :1433 so that when the SQL server goes off the network, the virtual instance switches over the other nodes. Alternatively, depending on your app support, you can specify alternate mirror servers in the SQL connection string.
You may be interested in the response I received to my question, SQL Server 2008 R2 100% availability.