I don't have expertise in architecting databases, and I've been teaching myself new stuff every day. I'd like to make an Internet-scale application using SQL Server as the data store. I haven't found any good information online with regards to scaling out SQL Server.
My understanding is that scaling out is great for write throughput, but it doesn't necessarily scale reads. A simple example (which is relevant in my case) is, if data is sharded by posting user id, status 1 posted by user X living in shard A will have all its likes and comments across the whole federation. So, if I need to fetch the comments on this status, I need to hit every database and merge and sort/filter results in application memory. This is bad for the databases because they are kept busy and bad for the web servers because I will be using CPU and RAM for post processing the objects. Ideally, I'd like to write to one database and read from one database for maximum scalability.
Now, what I'm thinking of doing is, instead of sharding by posting user id, shard by receiving user id. So, if user X posts status 1, user Y living in shard B can insert a comment in shard A, and I can enforce a parent-child relationship between the status and the comment. User Z living in shard C can insert a like in shard A for the comment, so the comment and the like can constitute a parent-child relationship. The benefit of this approach is I query only one database to get all the comments and likes for a specific status rather than naively querying every single shard.
However, I need to get results like "comments on status 1 by people who are male or 18+ years old". This is a crucial functionality I want to implement. I still have to hit other databases to get information about the users. In order to eliminate this, I'm thinking of creating a sync group where one database (hub) syncs all user deltas to all shards (every 5 minutes). I'm okay with eventual consistency though it has its own problems for example, if a user deletes their account, from the time the account is deleted to the time the delta is persisted to a shard, other users will not see the change potentially adding child objects to objects created by that user. This seems to me a data integrity issue.
I'm also aware of replication and caching to increase read throughput.
My question is, which approach should I pursue? If I choose the second one, will I have trouble syncing data across potentially hundreds or thousands of servers? Not to mention the hub is essentially a single point of failure.