Sql-server – Define priority of database access into SQL Server 2008

I would suggest RAID10 for heavy database work that isn't mainly read-only, not RAID5 or 6. On a 4-drive RAID6 array each block write (or partial block write) could be turned into a read (to get the other data block) followed by three writes (one for the initial write, two for the two parity blocks) which can have a significant write performance impact. With RAID10 each (partial) block write is just two writes to disk.

If your database access did include very few writes then RAID6 may be preferable for redundancy as a RAID6 array can survive any two drives failing where-as a RAID10 array of four drive will only survive 4 of the 6 possible combinations of two failed drives, but you state that you expect the activity to be both read and write intensive (and you have good backup and disaster recovery plans, right?).

Obviously make sure you go for a 64 bit edition of Windows and SQL Server, otherwise you'll not be able to use all that RAM without performance draining hacks like PAE. While we are on memory: I would suggest more if you can. RAM is not expensive these days, even good know-brand ECC capable RAM, so going up from 8 to how-ever-much-you-can-fit-on-the-board isn't a bad idea. For most applications in SQL server (with big enough databases) you will notice the benefit under any appreciable load as it will greatly reduce I/O operations for read queries as a larger working set can be held in memory. From your description it sounds like you have a data size and activity pattern that would benefit from how ever much RAM you can practically throw at it. Many new servers support 16Gb of RAM these days, and 32Gb is not uncommon (and increasingly neither is support for 64Gb, though that is getting into the "specialist" market so you may have to pay a premium for the extra).

Brent covered log shipping and database mirroring well so I won't go into that. Required reading on this topic is Allan Hirt's book Pro SQL Server 2005 High Availability. I know this is for 2005, but it's 95% relevant for SQL Server 2008 as well. You must read this to have a good understanding of the options available. Here are my additions to Brent's response:

Failover Clustering

If financial, power and server room resources are not a constraint then this is my preferred choice for high availability for SQL Server. You need shared disk storage, usually a SAN for this to work and I prefer to place the C drives on the SAN too for easy DR. The way I set it up is to have a quorum LUN (Q), an MSDTC LUN (M), and a mount point for each Instance of SQL Server in the cluster. Within the mount point set up a LUN for SQLData, SQLLogs, SQLBackups and optionally SQLtempdb. For ONE instance you will end up with D:\SQLData, D:\SQLLogs, D:\SQLBackups, D:\SQLtempdb (for example). For the next instance you might have E:\SQLData, E:\SQLLogs, E:\SQLBackups, E:\SQLtempdb. All of the shared disks need to be presented to all nodes in the cluster. Failover is automatic and takes around 20 seconds in my production environment. It is robust, but can be tricky to set up if you are inexperienced.

Virtualised SQL Servers

An option you haven't explored is the use of vmware ESX server to host your database servers. I really like this option but haven't the confidence to deploy it in production environments yet. I have deployed it very successfully in non-production environments and the technology is outstanding. I think it is only suitable for moderate to lightly loaded SQL Servers and should not be used if performance is critical or you have high workloads. A one to one mapping of SQL Server to ESX hosts is a very desirable configuration. vmware VMotion is great technology with much shorter downtimes than failover clustering. I saw a demonstration once of a video being played on a server and the server was failed over with the video running with no glitches. Now, that's impressive!

SQL Server replication

This may not work well for third-party applications because it may require changes to the schema. SQL Server replication was not designed for high availability, rather it was designed to make copies of data available in other locations. I would not recommend using this for high availablity due to the complexities of it. However it can be useful in certain scenarios due to the low level of granularity that it offers - you can do horizontal and vertical partitioning of data for example.

Third party disk replication

A solution such as NSI's double take could be considered for high availability also, however I prefer to use it for disaster recovery for non-SAN based systems. It basically replicates data at the block level to a target server and the target server watches the source server for availablity. If it becomes unavailable, it triggers a failover condition and you can set it up to automatically fail-over or alert for manual fail-over. Fail-over times are similar to SQL Server clustering. The advantages are you don't need any special hardware to do it, but the software licenses can be expensive.

Backup and Restore

Not really a high availability solution, but for some people with looser requirements, this very simple solution may offer everything you need. Simply backup the databases on a schedule to a backup server, and make sure the backup files are available on the target machine. Set up a job to restore the files as they are backed up and you have a crude high availability solution on the cheap.