How do the servers for Fogbugz handle load balancing?

Question

Hi,

I remember hearing Joel say he has 2 different locations where the servers are located, each location has 2 front end servers and 1 back end server.

1. If a one of the hosting facilities goes down, how can he switch over to the other one? (Or is it just going to be a DNS change that will take 24-72 hours to propagate?).

2. How can a single SQL Server instance have so many databases on it? FB has a completely separate database per account. I can't see a single SQL Server instance having more than say 200-250 databases on it! And I'm sure they have more customers than that.

Answer 1

They talked about this in one of the Stack Overflow podcasts, but I can't find it in the transcripts.

1) Each of the two centers handles approximately 1/2 of the users. Fairly often (hourly, I think Joel said) they ship transaction logs to the other site. If site A goes down, they bring up the db backups on site B, and do the DNS switchover. It won't be instantaneous or automated, nor do they want it to be, because they'll be coming up with slightly stale data, and want to avoid that if it's at all possible to bring the broken site back up.

I'm not sure how they handle the DNS situation, but you can set the TTL on DNS records to mere seconds to limit caching, and have failover occur very quickly.

2) Why not? I'm not sure of the hard limit of databases per instance, but there's also nothing keeping you from running multiple instances of SQL Server on your box. I would imagine you're more limited by hardware than software. (You can also run Fogbugz with a MySQL database backend).

Answer 2

Got a few questions in here so I'll break these out:

If a one of the hosting facilities goes down, how can he switch over to the other one?

There's several ways to do this, including database mirroring (new in SQL Server 2005), log shipping, and replication. I've recorded a podcast on SQL Server high availability and disaster recovery options at SQLServerPedia.

(Or is it just going to be a DNS change that will take 24-72 hours to propagate?)

Like the other post mentioned, you can set your DNS time-to-live numbers very slow, but the cooler method uses database mirroring. With mirroring, you can set both the primary and secondary server names in your connection string, and your application will automatically try the second server when the first one doesn't respond.

How can a single SQL Server instance have so many databases on it? FB has a completely separate database per account. I can't see a single SQL Server instance having more than say 200-250 databases on it! And I'm sure they have more customers than that.

The largest SQL Server I've worked with had over a thousand databases, and I've talked to a couple of other DBAs who have worked on systems with more than 2,000 databases on a server. It certainly makes management much more challenging, that's for sure.