We have two servers. They are both HP DL 380 G6 servers with the same hardware and specs running Windows Server 2008. Currently the second server is a warm standby for the first with an automatic fail over cluster for SQL 2008 Enterprise.

I would prefer to have both servers running and serving requests. Therefore if our staff need heavy reports generated from the database they can use the secondary server without slowing down the web requests from our users. The idea would be to have a dfs style replication for the databases files to assure symmetrical databases in real or near real time on both servers. Only one server would be serving the web requests. There are no plans for a load balancer currently.

I am familiar with fail over clustering but this sort of simultaneous clustering has eluded me.

can someone shed some light on this? Maybe I am looking at this all wrong.


Addition: Thanks for the responses. I find it hard to belive with 25,000 dollar software you cannot scale out to multiple servers. How do large companies deal with ever growing requirements? No one machine can handle a large site like Facebook if they used MSSQL (which I'm sure they don't but its just an example)

ADDITION2: Still looking for a little more feedback if anyone would be so kind. Remember to look in the comments too.

  • 1
    Just a quick comment on Facebook and large corporations.... these businesses never write to the database synchronously. Facebook has a layer of memcache between the application and the MySQL databases it uses - the memcache is updated/read and the database is updated/re-read asynchronously at a later time. Google "database sharding" to find out more. – Brian Knight Jun 30 '10 at 19:06
  • Understood. My boss basically wants an Active Active cluster with synchronous writes. simply.... is this possible with any DB software? – Campo Jun 30 '10 at 19:10

Not going to work. Cluster and Mirroring are - in the MS world - always active/passive for SQL Server. You can add replication, but that will have to be on top (additional server).

  • 1
    Doesn't have to be extra hardware. Could be a different instance on the existing passive server, and that could handle the reporting load. – mfinni Jun 30 '10 at 17:14
  • Seriously..... how do large corporations who use MS deal with this then? – Campo Jun 30 '10 at 18:35
  • By using one server, or actually having multiple databases (sometimes way too many). Replication is also a possible solution, but it has to be iin the design phase of an application. – TomTom Jul 1 '10 at 16:36

To add to what others have said, SQL Server 2012 allows this with the use of "Always On Availability Groups". You define one node in a cluster as the primary upon which all write activity will occur. You can then use one or more secondary nodes to satisfy read-only queries.

An overview can be found at http://msdn.microsoft.com/en-us/library/ff877884.aspx


Agree with TomTom - it's worth you knowing that Oracle's RAC system allows this, not cheap however...

  • plus from what people told me pretty hard to keep running, too ;) – TomTom Jun 30 '10 at 16:41
  • can't really comment as we have a >100-man-strong Oracle DBA team, I expect them to keep them up, no idea about it if it's not a dedicated role. – Chopper3 Jun 30 '10 at 16:43
  • It used to be hard to keep running, but this is not the case any more. Recent versions ( and up) are very stable. – chenshap Jun 30 '10 at 17:14

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.