I was asked this question in an interview:
I have a sql server and an asp.net application. I want 24X7 hours availability for my application even if the server crashes.
What are the different ways to achieve it at the code level and at higher level (meaning not at code level)?
Ultimately it will boil down to money. There is a cost for every 'nine' in the mythical 'five nines' (99.999% availability, 5 minutes of down time per year), and that cost is quite high. A 99.999% availability system costs in $ millions, and has to cover hardware, software licenses, dedicated highly trained specialists personnel, training, procedures and so on and so forth. You must consider things like system updates (OS and vendor patches), application upgrades, various maintenance procedures like database reindexing etc etc.
But for a very coarse answer I'd point you toward the High Availability Solutions Overview:
Failover clustering provides high-availability support for an entire instance of SQL Server. A failover cluster is a combination of one or more nodes, or servers, with two or more shared disks. Applications are each installed into a Microsoft Cluster Service (MSCS) cluster group, known as a resource group. At any time, each resource group is owned by only one node in the cluster. The application service has a virtual name that is independent of the node names, and is referred to as the failover cluster instance name. An application can connect to the failover cluster instance by referencing the failover cluster instance name. The application does not have to know which node hosts the failover cluster instance.
Database mirroring is primarily a software solution to increase database availability by supporting almost instantaneous failover. Database mirroring can be used to maintain a single standby database, or mirror database, for a corresponding production database that is referred to as the principal database.
Like database mirroring, log shipping operates at the database level. You can use log shipping to maintain one or more warm standby databases for a corresponding production database that is referred to as the primary database. Standby databases are also referred to as secondary databases. Each secondary database is created by restoring a database backup of the primary database with no recovery, or with standby. Restoring with standby lets you use the resulting secondary database for limited reporting.
Replication uses a publish-subscribe model. This lets a primary server, referred to as the Publisher, distribute data to one or more secondary servers, or Subscribers. Replication enables real-time availability and scalability across these servers. It supports filtering to provide a subset of data at Subscribers, and also allows for partitioned updates. Subscribers are online and available for reporting or other functions, without query recovery. SQL Server offers three types of replication: snapshot, transactional, and merge. Transactional replication provides the lowest latency and is usually used for high availability.
I don't think many people here will give you an answer to an interview question to help you bluff your way through it, and I'm sure that's not what you meant, so here are two learning options for you.
Google "High Availability asp.net". ("High availability" is the term you're looking for)
At the code level there's not much you can do: if your server crashes, it crashes. In terms of hardware, they were probably looking for a phrase like Failover Clustering.
It requires multiple servers, which isn't feasible for some people and may not be necessary. However, if it's critical you achieve near 100% up-time, there is something known as Failover Clustering at the server level which when your server crashes for any number of reasons, one of your other servers will "step in" and take over.
VMware vSphere with Fault Tolerance (FT) or equivalent for other virtualization products. This solution is not limited to 2 server (one fails, the other takes the load), but can bi distribuited to many servers. It's only a matter of how much you want to spend.
This is completly OS-independent, meaning that you can have your application running on Windows Server and your database running on Linux RedHat or vice versa.
Hosting your asp.net application and database on two separate servers with a hot fall over option for both servers will provide greater resiliance, Fall over clustring as suggested above will provide this. But then you also need to think in terms of if the database server goes down that transcations are queued and when the database is restored that transactions are committed in a FIFO fashion.
Generally expanding on that is how I would answer that question, but i would agree with @CXFX that to do it entirely at a code level is impossible.
In practical business, I would look at:
But that's not a case for Stackoverflow.
It's not a quick answer since it takes a lot of real world learning to really master high availability in a data center, on the platform and at the application level. At a high level, here are some things to consider.
To be resilient to server crashes and patching, you'll need load balancing at the site level, some sort of SQL HA solution, and an application that isn't locked to a single server.
For the site level, there are many 3rd party load balancers that are themselves redundant. Or, Microsoft's Application Request Routing (ARR) solution is a great option too.
For SQL Server, the built-in clustering, mirroring or log shipping options often fit the bill, plus products like DoubleTake do a great job at meeting this need.
At the application level, you'll need to make sure that nothing depends on a single node. Session state is the most common dependency. If it's used, it needs to be offloaded to a redundant solution. SQLServer Session State, ScaleOutSoftware and now AppFabric are all options to consider.
True redundancy needs to be geo-redundant across data centers, which need to be far enough apart that they won't be affected by any major natural disaster.
And, no technology is sufficient without a lot of testing and great processes and procedures to know how to handle unexpected situations as smoothly as possible, and to test the various redundant parts of the system at regular intervals.
