0

We are preparing to move our SaaS product(single-tenant-per-database model) to Azure SQL with database sharding, from what I learned, a shard is a database and each elastic pool max contains 100 databases. Let's say I have 10 shards in an elastic pool, i.e. 10 shard-databases.

My question is:

  1. These 10 shards(databases) will be counted on the number of databases, so I have 90 databases capacity in the pool?
  2. OR 10 shards only be counted as ONE database and I still have 99 database capacity in the pool?

If it is the case of above option 1, I think there is no reason to use sharding here as we can create a standalone database-per-tenant in the pool without sharding.

Our goal is to save the number of databases in the pool for the free tenants, we thought one database sharded could contain multiple shards for multiple tenants, but finally realized a shard is a database.

Improve this question
2
  • each shard is counted as a separate database for billing purposes. So, if you have 10 shards in an elastic pool, those 10 shards will count towards the total number of databases in the pool. Commented Sep 15, 2023 at 6:54
  • Thanks, @DileeprajnarayanThumula, if that is the case, I think the only option for us is multi-tenant-shard, i.e. allow multiple tenants to use the same shard/database. Commented Sep 15, 2023 at 7:09

1 Answer 1

Reset to default
1

Sharding is a technique to distribute large amounts of identically structured data across a number of independent databases.

  • Each shard is counted as a separate database in the elastic pool
  • Therefore, if you have 10 shards in an elastic pool, you will have 10 databases in the pool.
  • The number of databases in the pool is equal to the number of shards in the pool.

As you mentioned your goal is to save the number of databases in the pool for the free tenants As per Scaling out with Azure SQL Database

  • For Free trial it is cost-effective to use a multi-tenant database for the data

  • When your trial is finished and you choose pay-as-you go, a single-tenant database can be better since it provides better performance.

  • If you have created data during the trial period, use the split-merge tool to move the data from the multi-tenant to the new single-tenant database.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks, we read the above links and we have a question about the Identity column with moving shards, there is no problem with moving the trial/free account(multi-tenant database) to the single-tenant(dedicated) database. But how about downgrading, we want to move/downgrade the account from a single-tenant database to a multi-tenant database, how do we handle the Identify conflict issue?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.