1

Does Microsoft support implementing Database Sharding using Sql Server 2008 R2 (any edition). From my limited understanding of it, I think, we could probably implement it using Sql Server Express. But I was not sure if Microsoft supports it or not.

Database Sharding Resource Link

Other than Distributed Partitioned Views, Data-Dependent Routing, what other options are available for Scale-out? (Peer-to-Peer Transactional Replication, Federated DB?)

Please share any URL that describe any implementation architectures in detail.

Thanks,
_UB

UB01
  • 937
  • 2
  • 9
  • 18

2 Answers2

6

No, SQL Server doesn't support sharding. SQL Server scales up not out.

This applies to RDBMS in general:

  • CAP theorem says you can have at most 2 of C, A and P.
  • RDBMS are ACID compliant which is the point of using them

The "C" in both acronyms is "Consistency" and sharding sacrifices "Consistency"
So generally sharding and consistency are mutually exclusive

There are exceptions (more or less) of course. Some examples:

Stefan Rogin
  • 274
  • 1
  • 2
  • 15
gbn
  • 69,809
  • 8
  • 163
  • 243
  • Thank you. From what I know, there are some options available to scale out with Sql Server. I can't recollect the details, but it is possible. In database Sharding, Consistency is not impossible. There is lot of architectural planning that is needed though. I have not heard of CAP theorem, but I will read the article. – UB01 May 15 '12 at 14:18
  • I didn't say not impossible, but it isn't generally done. For scale out, see http://dba.stackexchange.com/a/7454/630 – gbn May 15 '12 at 14:27
  • Thank you, it is useful. Though the answer is not what I would like to see, it is what it is. – UB01 May 15 '12 at 15:18
  • Scaling Out SQL Server : http://msdn.microsoft.com/en-us/library/aa479364.aspx – Stefan Rogin Dec 17 '14 at 14:13
  • @clickstefan: none of those are real "scaling out" in the sense of "add more nodes" – gbn Dec 22 '14 at 13:09
  • @gbn probably :), but I thought it's worth to have it here for reference, so people know their official solution for "scaling out". – Stefan Rogin Dec 22 '14 at 13:44
  • I see that according to the official documentation, MySQL Cluster Evaluation guide: "In terms of CAP, MySQL Cluster will sacrifice availability to maintain consistency in the event of an unsurvivable network partition." – Stefan Rogin Dec 22 '14 at 13:49
  • @clickstefan: which is what SQL Server/Oracle.PG/Sybase do too – gbn Dec 23 '14 at 06:59
  • so Mysql cluster doesn't have either C or A from CAP? – Stefan Rogin Dec 23 '14 at 11:25
1

+1 to gbn. It's worth mentioning that SQL Azure does support sharding (federations) if you were considering other options.

http://social.technet.microsoft.com/wiki/contents/articles/1926.how-to-shard-with-sql-azure.aspx

AdaTheDev
  • 291
  • 1
  • 2
  • 8