2

I'm doing some research into implementing sharding for one of my large (~2 TB) production databases. Currently the database has HADR via a multi-replica AlwaysOn Availability Group.

Since sharding a database requires it to be broken out into multiple smaller pieces across multiple nodes, is it possible to leverage AlwaysOn Availability Groups with the sharded database?...would I need to create a failover cluster that duplicates every node in my sharded database to multiple secondary replicas to be able to do so?

Is there a better way to implement High Availability for a sharded database? (I know sharding inherently has a level of High Availability, because if one node goes down the rest of the database is still accessible, but I need a solution that is fully High Availability so that no part of the database is ever inaccessible.)

  • SQL Server does not support sharding .,. you have to switch to Postgres - citus or mysql - Vitess. Alternatively, you can check Azure esp Cosmosdb. – Kin Shah Jun 22 at 13:54
  • @KinShah SQL Server does support sharding, just perhaps not as easily implementable or out of the box compared to alternative DBMS. This is an MS article discussing the theoretically side to implementing it: docs.microsoft.com/en-us/previous-versions/msp-n-p/… – J.D. Jun 22 at 15:56
  • Yeh .. I looked at that before .. nothing out of box as compared to mysql or PG. – Kin Shah Jun 22 at 16:17
  • Just by curiosity, why do you want to do sharding ? What will it fix ? – Dominique Boucher Jun 23 at 15:50
  • @DominiqueBoucher Hoping to improve performance to read and/or update the data in my database. I have a database that's a few TB big and has a table with 10s of billions of records. I can't archive the records because changes to the data affect all relevant records historically too. So even though the record might be from 5 or 10 years ago, it still needs to be maintained and updated. Was hoping to leverage parallel processing to improve performance. – J.D. Jun 23 at 17:45
3

There's no difference in HA solutions when you break up a large database into multiple smaller ones. Either Availability Groups or Failover Cluster Instances will work.

AGs have the benefit that you can perform cross-shard queries on any instance that has readable replicas of all of the shards. So, for instance you can have a shared database that's always present on every node, and the app can always perform cross-database queries from. Just note that cross-database transactions (including all writes) are not supported except between databases in the same AG (not blocked though).

| improve this answer | |
  • Thanks, I guess what I'm try to understand is do I need a secondary replica for every sharded node? So for example if I shard my database across 4 nodes, and I need a true High Availability system, do I need at least 8 servers: the 4 sharded nodes + 4 secondary replicas (one per sharded node) to be able to support this? – J.D. Jun 22 at 16:02
  • Or alternatively, would it be possible for me to setup an AlwaysOn AG for my primary database with 2 secondary replicas (one being a read specific replica), and also shard the read specific secondary replica to 3 other nodes which would give me a total of 6 servers? (Primary + Secondary + Secondary Read Specific + 3 Sharded Nodes). – J.D. Jun 22 at 16:03
  • The number of servers is unrelated to the number of shards. You could have 100 databases and just 2 servers. If you choose to have more than 2 servers, you may have every shard replicated to every server, but you don't have to. – David Browne - Microsoft Jun 22 at 16:25

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.