For our SaaS app, I want to have a system in place for a datacenter wide failure.
In the main datacentre, we have two servers set up with MSSQL Database Mirroring (synchronised). This provides us with a good enough high availability solution for server failures. If a server dies, it fails over automatically (with the help of a 3rd witness server) within seconds.
I was thinking of using MSSQL Replication or Log Shipping from the mirrored database to keep a warm standby server in another datacentre - note this will be transatlantic and thus high pings ~100ms. I guess I could use some DNS failover service with a short (5 min) TTL that will direct traffic to the second datacenter in the event of a failure of the first.
Can I use replication or log shipping from a mirrored database (seamlessly working if it fails over to the other instance)?
Which is preferred - replication or log shipping or something else?
Is it possible to have the failover database accept writes?
Or would there be potential data loss from the failover and so best to have this read-only until we fail back to the main datacenter?
EDIT: anyone have any ideas for a standby MSSQL server configuration?