I'm administrating a fairly large website (currently about 300 thousand page views a day) which is expected to grow fast. Currently both IIS and SQL Server are running in a quad core server, with RAID 10 SAS Hard Drives and 32 GB of RAM. A less powerful server is configured as cold backup. Databases are synchronized daily and also the site files are moved over to the backup server daily. In case the primary server goes down, the site can be up again in a few hours, but that's not ideal. I'm looking for a solution that will offer:

  • improved performance. In the future it will be necessary to create a web farm to handle the requests, so I need to plan for that.
  • redundancy. If one server goes down, the site should not go down.
  • backup. The data are critical, so the SQL Server configuration should be in such a way that we don't loose data older than 1 day (it's no big issue if the last day data are lost)

Also, the solution should include disaster recovery. If the data center goes in flame, we'll need a solution to be back online in less than one day (we're thinking of keeping a copy of the data and site in our local servers, but we'll need a way to have the process as automatic as possible. The primary server is hosted in a data center in Germany).

The database is 50GB+ while the web application is rather small.

  • 1
    None of what you desire is a small task even in and of themselves. Honestly, if you are unsure of a solution or not comfortable you should hire a consultant to help you get these pieces in place. – squillman Jul 26 '12 at 17:45
  • See my answer below, but I agree with squillman, getting a consultant (disclaimer I'm a consultant) will make this a whole lot easier for you to do as you are looking at a couple of big expensive projects here to get all this done correctly. When it comes to HA and DR if they aren't done correctly the first time, fixing them later is much harder and usually much more expensive. – mrdenny Jul 26 '12 at 17:56

This all sounds pretty standard. I'm going to assume SQL Server 2008 R2 or SQL Server 2012 here for the database part.

The first thing you need to do is get IIS off of the SQL Server and put it onto it's own machine. You'll also need to get some sort of load balancer to put in front of the web farm. I'd recommend something like an F5 or Cisco, though you could go with a Linux based load balancer if you have a Linux person in house. Once you've got the load balancer in place as you need to grow the web farm out doing so is pretty easy. You just buy another server, configure it like normal and add it to the farm in the load balancer.

As for SQL HA, you'll probably want to look at SQL Server Database Mirroring. This will give you two servers in the local data center (though you could put them in different data centers) with automatic fail over if you have the Enterprise Edition of SQL Server.

Setting up the backups to copy from the data center to your office isn't all that hard. Just setup a site to site VPN and copy the files over the network. Bandwidth and latency become the only problem at that point.

Your DR requirement is going to be the hardest part. Having a requirement that you be back up and running in less than a day means that you need to have a contract with another data center, and that you need to have servers already at that data center. Without having this equipment already in place you will never hit your goal of getting the site back up and running within a day as just getting new servers can take weeks (or longer depending on how big the disaster is as you won't be the only people trying to buy new servers).

On the web server site, DR is easy. Simply point the DNS servers to the public IP at the DR site.

For the SQL Server side of things you'll probably want to look at transaction log shipping from the primary site to the DR site. If you want an easier config look at SQL Server 2012's AlwaysOn Availability Groups. They'll do automatic failover, sync and async data replication, etc. AlwaysOn Availability Groups do require an Active Directory domain, so you'll need to look into getting that setup first.

If you haven't noticed yet DR isn't cheap or easy.

  • "require an AD domain"; and there the eating of the elephant begins! ;) – Henrik Jul 26 '12 at 19:04
  • Yep, pretty much. Doing it right ain't easy or cheap. – mrdenny Jul 27 '12 at 19:28
  • I think a PostgreSQL-migration would come at a lower total cost actually, than going full elephant... – Henrik Jul 27 '12 at 23:16
  • Could be. That really depends on how much it costs to actually move the database, and review then test all the SQL code to make sure that it still works in PostgreSQL. Not to mention that there's probably some PostgreSQL costs the come into play when setting up a big HA/DR system like this. And don't forget to include the cost of a PostgreSQL admin to setup and manage the system for you. – mrdenny Jul 31 '12 at 7:02
  • Yes, agreed. Hopefully the system is unit and integration tested and the cost of verifying SQL isn't that high. The admin is already there; that of SQL Server - they shouldn't be afraid of learning new skills. But the server would be free postgresql.org/docs/8.3/static/high-availability.html and so would its HA-stuff. And one wouldn't have to fight AD - a really big and bloated piece of software . Plus; it doesn't work very well across datacenters. – Henrik Jul 31 '12 at 11:47

More about SQL Server:

MS has published many free books, one of which is named Microsoft SQL Server AlwaysOn Solutions Guide for High Availability and Disaster Recovery .

You'll find more technical guidance here.

It also turns out that backup on Windows isn't as easy as on Linux.

High Availability

In general you can do these things for the web sites:

  • Let your webs be stateless; they shouldn't use session state nor view state - it complicates scalability. Instead, let the URL decide what gets shown; by avoiding shared state you can effectively use caches, such as NGINX in front, which speaks cross-platform HTTP:
  • Caching using HTTP -- NOT using 'memcached', 'varnish' or 'MS Velocity' - because you do not want an application cache if you can avoid it - it's a crutch. However, in order to cache properly using HTTP you either need ETags or Last-Modified headers, and you need to fix so that ASP.Net correctly returns 304 Not Changed for dynamic pages that actually haven't changed - this might involve some corrective programming.
  • If you DO need state for legacy reasons or whatever, consider writing a custom state provider. I can recommend having a NoSQL-key-value store as a backend that supports both failover/node failure and key expiry. I recommend the excellent Riak with features like this. If you then add more apps that don't speak Microsoft, you can still use the Riak HTTP interfaces. Remember to serialize with something universally known, like BSON or MessagePack. By doing shared state this way, you can still scale your web sites while having all session state distributed.

In general you can do these things for data:

  • Start separating monolithic large apps into single apps that have individual storages - by doing this you can make more conscious choices on how you move that data to redundancy data centers or servers.
  • You can adapt a very easily distributed way of programming, such as using the CQRS style of writing domain logic with events and event sourcing of entities. This requires some from your programmers though.
  • Start with asynchronous replication (see topmost section) in this answer.
  • Start writing Sagas for dealing with inconsistencies arising from multiple sources of truth (e.g. in case of a network split, or separate reads on same version row in a DB)
  • Start moving towards a datastore that more easily realizes HA - e.g. Riak or Cassandra.

Good luck!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.