An instance host a buisness crtical database that must be continuously available to users without data loss. the database include Filestream data. you need to implement high availability soln for the site. which is the best soln? a)failover clustering b)database snapshot c)asynchronous database mirroring d) synchronous database mirroring with witness server Can anybody help me with this question? Thanks in advance
asyncronous database mirroring can't guarantee no data loss and also can't fail over automatically in the case of the primary server failing. I'm not sure what you mean by database snapshot in the context of high availability. AFAIK a database snapshot is simply a means of providing a point in time view of a database, it is not a redundent copy and is tied to the database it is a snapshot of (eg, the database must be online for the snapshot to be readable and they must be on the same server) Clustering sounds most like what you need, (no data loss, single site), but a cluster failover takes time. Will it be a problem if the database is un-available for the time it takes SQL to perform recovery on the database? Another consideration is patches, if you are using SQL 2005 clustering will require you to take the server offline while SQL patches are installed. Mirroring also requires the database storage to be duplicated, I'm assuming if its using FILESTREAM that it is quite big database so this cost might not be insignificant.
I haven't worked much with clustering but isn't it more for protection of a hardware failure? The gotcha to me in the question above is "without data loss" None of the solutions above protect against data loss. If someone drops a table it is dropped on the cluster and mirror. The only protection from that is a load delay with log shipping or some other method of delayed replication. Of course you can recover from a dropped table or other oops type event with a proper backup strategy. I would have to agree with @ThomasRushton though and say that out of the possible answers that Clustering would provide the highest level of availability.