Help on choosing which SQL Server 2008 scale-out solution to pick (replication, ...)

Question

I am currently crossing the jungle of SQL Server scale-out technologies like replication, log-shipping, mirroring... I have the following constraints on my choice:

• I want the read-only load to be spread accross the primary and the secondary (mirror, subscriber) server
  • Write load can be sent directly to the primary server
  • The solution should be nearly maintainance free. Schema changes should just replicate to the secondary server (attention: replication has some serious constraints here as it seems)
  • Written data should be accessible very quickly (in under 1s, but better would be instantaneously) on the secondary server
  • On server failure I can tollerate up to one hour of data loss easily. I am more concerned with easy scalability

Here are some options for what I could pick: http://msdn.microsoft.com/en-us/library/bb510414.aspx. Any experience you could share?

Answer 1

Those are all High Availability solutions, not scale-out. SQL Server has no easy scale-out solution, nor do any other (relational) databases. Using master-slaves replication scales as much as allowed by the master write scale-up possibility. Using master-master replication multiplexes the writes and comes with consistency issues. Almost all large-scale deployments that had attempted replication based solutions had to abandon it.

One alternative is to rethink your application in terms of independent data-fiefdoms communicating by messaging, the way MySpace scales out.

Another alternative is to abandon some constraints (write consistency, read consistency, recoverability, typed schemas, referential integrity) and choose a nosql engine that can scale out freely once liberated of these constraints (Cassandra, HBase, MongoDB).

Ultimately scale-out is such a fundamental requirement that you must design your application around the solution and embrace all the (severe) restrictions imposed by scale out. Note though that all relational engines can scale -up a long way and the worldwide number of deployments that require scale out beyond what a a database can scale-up can be counted on your fingers.