2

Does shard map manager create a single connection pool to manage all the sql connections or does it spawn a new pool for every unique connection string / shard / database in case of one db per customer scenario?

An app connecting to many databases will lead to pool fragmentation and consume huge memory in the server. Microsoft advises to keep this in mind and even proposes a solution in the below link but I am not sure if they use this practise in their shard map manager:

https://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx

Ozmen
  • 43
  • 3

1 Answers1

1

Each unique connection string results in a different connection pool. This means that there will be a separate connection pool per shard that you connect to. The solution at https://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx is not used because it doesn't work for Azure SQL DB (which doesn't support USE statement).

If this becomes a problem for you, the recommended solution is an end-to-end architectural approach where app instances are affinitized to certain shard key ranges. This way each app will only ever connect to a subset of shards which will reduce fragmentation.

Jared Moore
  • 3,765
  • 26
  • 31