Azure Elastic Database Pool

0
votes

I am in the process of designing a SaaS system that will be running in Azure and utilizing SQL Server. The application will leverage an Azure Elastic Database Pool to support scaling out via Sharding. The plan is to run a Shared-Sharded model and leverage a Shard Map to map the Tenant Id to the correct database.

I've been reading the Azure documentation and have successfully setup an Elastic DB Pool across a couple of my test databases in my DEV environment. So I understand how to do implement and in theory how this will work.

What I am unclear on is whether it is best practice to create each Azure SQL Database (Shard) on it's own Azure SQL Server or just to run one Server with multiple databases. I'm trying to work out whether there is potentially any performance bottleneck if I had 30 databases on one server vs. 30 servers with one database each. In either circumstance I am assuming the 30 databases are all part of a single Elastic Pool.

Can anyone help me understand conceptually how the resource are allocated? All the documentation talks about the resource being allocated to the database and the server is never mentioned so I am assuming this is somewhat irrelevant and is simply a container in the this context.

This question is driven by the bulk of my experience being based on a world of physical SQL servers where there is a limit to how many databases you can resource on each server.

1
sql-serverdatabaseazureelastic-database-pool
I don't think it is even possible to have databases in different servers be part of same elastic database pool. – Gaurav Mantri
@GauravMantri That is a good point, the pool I've setup is across 2 databases on the same server, I will create a new server and test that now. – connectedsoftware
@GauravMantri ok you are correct, I've created a new server and database and you can't add that database to my existing elastic pool. Should have spent less time thinking and more time testing it out - thanks! – connectedsoftware

1 Answers

1
votes

I answered my own question shortly after posting by testing in Azure. You can only add databases located on the same database server into an Elastic Pool.