In Cassandra, you can have a partition key to align partitions to particular server nodes.

My understanding is that you can distribute transactional responsibility horizontally in SQL Server 2016 (across horizontal servers) but you always come back to a 'node of truth for the database'.

I'm not sure if you can break that transactional responsibility down to the table or partition level.

For example - suppose I have asset information - I expect to have 1000 million rows in three years. I expect that my server load will increase due to usage, to the point where I'll have to scale my servers (web and database) horizontally.

I want to be able to do reads and writes across three database servers. On the first server I'll partition my assets so that the assets A-I can read and write, on the second I'll have J-R, and the third S-Z.

My question is: Is it possible to have a partition to distribute transaction load horizontally in SQL Server 2016?

  • Can you add an example of how this would be used? – Sir Swears-a-lot Nov 27 '16 at 6:35
  • You could do this with Linked Servers and views (or procedures) with carefully written constraints, however it would be more efficient to put logic in your front end to redirect to the relevant node and then pull the data back and merge it in the app layer. – Nic Nov 28 '16 at 14:27
  • Is that a sufficient example @Peter ? – hawkeye Dec 1 '16 at 9:36
  • Im not aware of any sql features that would achieve that transparently. AG would achieve some of it. If you're writing the app you could implement it from beginning to support a multi db architecture and redirect to other dbs. – Sir Swears-a-lot Dec 1 '16 at 10:03
  • In my exp with multi db architecture you may be better to split load in a round robin approach rather than hard code distribution. Unless you can guarantee normal distribution. It makes life easier when you want to add more servers as you dont have to split/migrate existing data. The exception to that is if the data has a natural grouping and needs to processed together. Can be a pain if its spread over many servers. – Sir Swears-a-lot Dec 1 '16 at 10:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.