0

Is this possible either out of the box or using additional tools or frameworks?

I'm looking to set up at least two simultaneously active machines to better understand how high scalability for databases works. Sites like Facebook must use multiple nodes to service their massive volume of queries and updates, correct?

If I've missed any useful resources in my Googling, even links would be appreciated.

3 Answers3

0

Check this post by MVP and Microsoft Certified Master Brent Ozar:
http://www.brentozar.com/archive/2011/06/scaling-sql-server-growing-out/

It boils down to replication, and it's not simple.

Joel Coel
  • 12,932
  • 14
  • 62
  • 100
0

SQL Server only supports active-passive clustering. It does not offer anything that is comparable to Oracle RAC or IBM PureScale. Therefore it is not possible to have several active database instances.

For query type applications it is possible to clone the same database several times. For update-type workloads it might be possible to partition the data set and allow only one active node to update the corresponding "partition" of data. Partition as described in the last sentence has nothing to do with the partitioning functionality it describes node-ownership of data.

But generally if such a requirement needs to be supported it is better to switch to a product that supports the functionality (like Oracle RAC). The above solutions simply do not scale well in terms of development time and manageability.

steve
  • 154
  • 3
  • How does scaling work for Oracle, IBM and active-active clustering in general? Is it a replication-and-synchronization approach? Or partitioning-based? –  Jun 19 '11 at 03:24
  • Neither. It is a shared-disk cluster - they operate on the same physical database. Before updating a database block the block or the right to modify the block is shipped/sent to the other instance. Generally a fast interconnect is used (Infiniband or Gigabit) to transfer the block or the right to read/modify the block. This is a huge over-simplification, but describes the high-level concept. – steve Jun 19 '11 at 03:38
  • Steve, are you saying even Oracle doesn't support multiple active physical nodes? Multiple instances on the same machine just doesn't seem very effective or beneficial. –  Jun 19 '11 at 04:08
  • Nope. The database instances are on different physical machines. That's the reason you need an interconnect. – steve Jun 19 '11 at 19:14
0

This will be possible with special design (distributed partitioned views) and a business layer which take care where to send the query. There is a reference design with bwin.com With distributed partitioned views it is possible to make the update on any node but (without modifications on upper layers) but I recommend doing this modification to get optimal performance.

Ralf Dietrich
  • 1