question

hatake avatar image
hatake asked

high availability

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
homeworkhigh-availability
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

pipthegeek avatar image
pipthegeek answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
None of these are ideal, but the best option is (a) - Failover clustering. The others are more used around the DR side of things rather than HA.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

BradleySQL avatar image
BradleySQL answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sorry - just correcting your typo of my name! ;-)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.