You did not explain why you want to partition the table and what do you expect from partitioning. You only mention table size, which is hardly a criteria for partitioning. Performance wise partitioning will make everything slower, not faster. The best you can hope for is on-par performance with the unpartitioned table. Some scenarios that do make sense with partitioning are:
- ETL needs like load jobs that manipulate data intensively in staging tables and then switch in the entire staging table in one fast operation
- need to remove large volumes of data that has past retention period (monthly switch out and truncate)
- administrative reasons, like the need to rebuild individual partitions
Many quote reasons like 'move old data to slower disks' but I don't buy that argument much. And another oft quoted reason is to distribute data in multiple files, but that is incorrect thinking as a filegroup can contain multiple files across many volumes and the engine would distribute IO across them anyway w/o any partitioning need.
You mention that your developers quote a case when partitioning by data increased performance. Perhaps is a case of a time series data that was clustered by id, not by date and in which all range queries (typical for time series) had to do table scans. Partitioning appeared to help because partition elimination reduced the amount of data scanned. But a proper clustered index would had addressed the problem much better (no surprise there, indexing is the usually correct answer to query performance issues).
An extreme case when partitioning is useful is hash partitioning to help spread insert last page latch contention.
But when push come to shove the partitioning is an 'all-or-nothing' approach that has a very heavy impact (consider that you can no longer have an unique primary key that does not include the partitioning field anymore) and percolates through the data model everywhere (eg. a lot of foreign keys have to be re-designed). It requires careful administration. Query Optimizer can misfire some horrible plans in presence of partitioning.
Kendra Little has a nice article explaining pros and cons: How To Decide if You Should Use Table Partitioning
Your question focused on this:
However, my question is regarding the non sharded data which is
common/universal. An example of data like this may be an Inventory
table for instance or possibly an Employee table, user table etc .
When you're doing sharding, and you have data that all of the shards need to see, you have to classify that data with a few attributes:
Does it change frequently? In your examples, you listed Inventory, Employee, and User. Typically inventory changes very fast, but the Employees records only change periodically (say, a few hundred updates per day).
How much delay can each shard tolerate? Even though the Inventory may constantly be changing, you can typically tolerate a large amount of delay (minutes or even hours) on a table like that. If you're selling unique items with a very limited quantity that you can never restock (think original artworks), then you don't shard that data at all - you only query the original database. However, in most online stores, you're not selling out of every item every day, and you're going to restock things quickly anyway, so you don't really need up-to-the-millisecond counts of inventory. In fact, in most cases, you only need an In-Stock flag that's either 0 or 1, and a central process updates that flag. That way, you don't have to push every up/down bump of item count out to every shard. Employee or User data, on the other hand, may need to be pushed out to every shard nearly instantaneously.
Will you be joining from the sharded tables to the non-sharded ones? Ideally, the answer here is no - you should make two separate queries to get the data, and then join them on the app side. This gets a lot harder from an app perspective, but it gives you the capability to get the freshest data from each source.
Is this original data, or copied? Another way to think of this question: what do you need to back up, and how frequently? Typically in a high-volume sharding environment, you want the backups to be as fast and as small as possible. (After all, you need to protect each node, and you want all of the shards to fail over to DR at the same point in time - not have some shards with newer data than others.) This means the sharded data and the non-sharded data should be in completely separate databases - even if they're on the same server. I may need constant transaction log backups of my sharded (original) data, but I may not need to back up the non-sharded data at all. It's probably easier for me to just refresh my Employees or Users table from the single source of truth rather than back it up on every shard. If all of my data is in a single database, though, I lose that capability.
Now, about your concerns:
"Transactional issues...you will no longer be able to do this easily." Correct. In sharded scenarios, throw the concept of a transaction out the window. It gets worse, too - for the sharded data, you could have one shard up and online, and another shard down temporarily due to a cluster instance failover or restart. You need to plan for failure of any part of the system, at any time.
"Not possible to do cross database referential integrity." Correct. When you split a single table out across multiple servers, you're putting your big boy pants on and telling the database server that you're taking over for tough tasks like point-in-time backups, relationships between tables, and combining data from multiple sources. It's on you and your code now.
"Recoding large areas of the system so that it knows to write common data to the new universal database but read common data from the shards." Correct here as well. There's no easy button for this, but once you've built this into the app, you're able to scale like crazy. I'd argue that the easier way to do this is to split the app's connections by reads.
"increased database trips." - Yes, if you break the data into multiple servers, the app is going to have to reach out to the network more. The key is to implement caching as well so that some of this data can be stored in lower-cost, higher-throughput, lock-free systems. The fastest query is the one you never make.
I've also laid out more pros and cons to dividing up multi-tenant databases here, such as performance tuning on individual shards, different backup/recovery strategies per shard, and schema deployment challenges.
Best Answer
Be very careful not to conflate table partitioning with (and?) database sharding. This specific article doesn't do itself any favors by having such a generic and overloaded title, and you're not the first person to start referring to sharding as partitioning.
In fact, there is already a pull request to change the title to Horizontal, vertical, and functional data partitioning.
The
minimize requirements for referential integritybit implies that you shouldn't be sharding data that you commonly join. If your question is "when would handling joins in the application be beneficial?" it would be a narrow use case, probably where latency between the application and a shard is better than the latency between shards (think distributed app and geo-replicated data), and when all of the data is being cached closer to the application anyway (e.g. Redis), as most good applications will plan for at the outset these days. Make sure you have done proper research to determine that (a) you need sharding at all, and (b) that you should be separating data you plan to join a lot. There are almost certainly going to be separation tactics that minimize the need to join across shards or in the application.Sharding works best when each node that you're writing to contains data that is largely independent and can live without any of the other nodes. When they depend on each other and you're joining between them, you've largely defeated the purpose of separating them in the first place. A good use case is when each customer/tenant has its own shard (or its own database on one of the shards). Most activity for any given tenant is focused on that shard. When central data is needed, it is often replicated to each of the shards, rather than joined across shards. This is the one of the models APS and Azure SQL Data Warehouse use.
They are talking about aggregate queries there, and I think they are implying that you are concatenating the results, not joining them. For example if customer A is on shard 1 and customer B is on shard 2, and you want to show total sales by customer, the app can (even asynchronously) run an aggregate query on shard 1 for customer A, and an aggregate query on shard 2 for customer B, do that in parallel, and then just union those results together. No joining across shards. I think you are reading way too much into it by saying they are encouraging it or suggesting it is necessary.
And if you want an answer to why Microsoft says something in a very specific way, you're going to have to ask Microsoft. We can help you interpret what they probably mean, and even suggest scenarios where it makes sense, but we can't "get to the bottom" of anything.