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.)
Best Answer
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).