Q: Which solution, SAN Replication, Windows and SQL Clustering, or Database Mirroring should be used for which situations?
A: These are three very different technologies that for the most part aren't competing with each other, but instead are complementary to each other.
SAN Replication vs. SQL Clustering
There's no competition at all here. In fact when doing a geographically distributed cluster (where the SQL Cluster is physically located in two different data centers) SAN Replication is required to handle keeping the data in sync between the two sites. The SAN Replication runs under the cluster so that both storage arrays (one in each site) are kept in sync using either synchronous or asynchronous replication.
SAN Replication vs. Database Mirroring
SAN Replication and Database Mirroring are sort of competing technologies as they both do data replication from one location to another. However when using SAN Replication there isn't a way to automatically fail over like there is with database mirroring. SAN Replication can fail over automatically when used with a Windows Cluster and when you have software which is installed on the Windows Cluster and tells the storage array to fail over. However without a Windows Cluster in place, just having a server sitting waiting for the storage array to present the LUN(s) to the backup server would probably require either some impressing scripting to get the storage array up and running automatically or some manual configuration.
Related: Reasons not to configure automatic failover for database mirroring
SQL Clustering vs. Database Mirroring
With SQL Server clustering and database mirroring we are getting into a competition as SQL Server clustering and database mirroring are semi-competing features. With SQL (and Windows) clustering the database instance runs on one of several machines which are all grouped together. All the databases are failed over from one machine to another as a group using the same name and IP address. The downside with SQL clustering is that there is the requirement of having shared storage as either a shared disk array or a large storage array.
With database mirroring the databases are failed over individually not in a group. With database mirroring however the newer SQL Server Native Client needs to be used so that database failover can be automatic. Without the newer SQL Native Client the client application's connection string would need to be reconfigured with the new active server name.
If you are looking for instance specific failover, and you are ok with having just a single copy of the database then clustering is probably the better option. If you prefer database level failover and you prefer having the second copy of the data then mirroring is probably the better option for you. Mirroring also has an advantage for some companies as there is no requirement for an Active Directory domain so if you are hosted with Amazon's EC2 for example mirroring is still an option where clustering isn't.
Like everything else in the SQL Server world there's no guaranteed correct answer every time for which (if any) of these technologies you should be using. Some, all or none of them may have a place in your shop today.
Related: Database Mirroring for Disaster Recovery and SQL Server on a SAN
Denny