4

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.

Questions:

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?

Thanks!

EDIT: anyone have any ideas for a standby MSSQL server configuration?

Marcus
  • 400
  • 2
  • 5
  • 16

3 Answers3

2

Either log shipping or replication will work with DB mirroring - which one you should use depends on your requirements, but replication can be harder to set up and manage than log shipping so personally I would stick with log shipping unless there's a feature of replication that you really need. The links below give more information on how to set each one up.

Is it possible to have the failover database accept writes?

Note: I assume you mean once failover has occured, rather than during day-to-day normal operation.

Certainly with log shipping it is possible to have the failover database in the secondary DC accept writes. If you can take and apply a tail-log backup of the database running in the primary DC (to minimise data loss, and to keep the log chain intact) then you will have an up-to-date copy of the database running, however do not forget that you are running exposed in this situation. Regular log backups can help but if your goal is not to lose any transactions then this cannot be guaranteed when you are running solely on the log shipping secondary in the other DC. It may be better just to run the application in a read-only state until your HA is set up again. From this state you can copy log backups across to the primary datacentre, then reinitialise mirroring.

Useful links:

http://msdn.microsoft.com/en-us/library/ms187016.aspx - Log shipping and database mirroring http://msdn.microsoft.com/en-us/library/ms151799.aspx - Replication and database mirroring

Note: to get write access to a logshipping database, you need to RESTORE DATBASE dbname WITH RECOVERY Afterwards it can be written like the master, BUT you cannot restore any additional logs after you do this. You need to restore a new full backup to get logshipping working again. But it would at lest allow you to failover to it.

Community
  • 1
Rob Watkins
  • 191
  • 5
  • Thanks for the answer, yes I meant once we failover can we accept writes. Looks like the answer is no. Would have no way of restoring a tail log backup as I'm assuming we failover because the whole datacenter is down and thus I could not take a backup. I guess then log shipping with DNS failover for traffic, leaving the DB in read-only mode... – Marcus May 03 '11 at 21:57
1

I am in the planning stages of something similar, rather than across the globe, it's across the US. We are planning on going with log-shipping. It seems (at least to me) to be more robust than replication (which I have worked with), easier to administer, and way easier to setup (at least to me).

Here is a quick list of the pros/cons. The biggest con for us is no auto-failover like Mirroring.

RateControl
  • 1,207
  • 9
  • 20
  • Great to hear from you. We already use mirroring for auto failover for within the datacentre. So the only way I see being able to failover in the event of the DC being down is to direct traffic there with DNS. If it were a client app you could direct the DB connection to the secondary site if the primary is down. For us, the app is in the same location as the DB server, so we either run the primary or if down, the secondary. Seems the secondary would have to be read only and would have to rely on DNS failover. Unless you have any other ideas? – Marcus May 03 '11 at 21:55
  • Lol, nope that about covers it. Good luck, let us know what you decide on, and I'll steal your ideas and plans for my use. – RateControl May 04 '11 at 02:06
0

Generally you're on a good route but consider the following:

  1. DNS caching and
  2. certain servers not honoring TTL

These are the reasons why this provides limited HA increase. Caching of up to 24h is not uncommon. I'd suggest that this is more a DR approach as it's really a thing that you would only want to do in case that your primary site is affected for a longer period since the fail-back also takes 24h to propagate to certain clients.

pfo
  • 5,700
  • 24
  • 36
  • I understand these concerns, thanks. However this question is aimed at finding out how to do the database stuff. – Marcus Apr 27 '11 at 19:09