Grant Fritchey (7/29/2014)
1tb isn't quite into the VLDB range, but, once you get past around 3-5tb, you need to start looking at things like SAN snapshots. But, even that could be problematic because the SAN can replicate the corruption.Prior to that, mirroring or AlwaysOn would be your best bets for dealing with corruption issues. While Availability Groups help with some types of HA/DR, they won't help with corruption due to the shared storage aspect.
Thanks all, I will look into database mirroring as well. There are basically 4 instances in 4 global regions I am considering (1 instance has SQL 2008 Enterprise edition, the other 3 have SQL 2008 Standard edition). I don't need automatic failover at this point since, if I encounter a data corruption error on the primary server, I would like to run a checktable or checkdb against the mirror database before switching it to production.
Does database mirroring require a shared storage aspect? I don't think so, and since we're a virtual environment on top of a SAN, I would think the primary, witness and mirror server are all on separate LUNs, thus separate storage. I didn't know availability groups required shared storage, which as you say, won't help if I had data corruption issues.
Also, to the connection string being updated to include automatic failover partner, at this point I don't think we would have automatic failover since I would like to be able to check the mirror before bringing it online.
Just like if I had log shipping (which I read is cumbersome to configure and maintain and I would like to keep it simple), I would ship the logs say 4-6 hours delay and if a corruption occurred, I could check the standby database for corruption before bringing it online.