Azure, SQL Server and Database

Question

One of my customer is developing multi-tenant solution. And I'm working as developer for the automation of resource provisioning part. The solution is developed such that each tenant have their resources separate from each other.

So for example, a single tenant will require a SQL database (PAAS), A Storage Account, and also many other resources.

One of the requirement that, customer set is, he wants to have X number of databases to be hosted on a SQL server (a logical server not VM). Which I don't think is valid having been using SQL as PAAS.

So My question is, Should we create SQL Server and SQL database for each tenant?

Or

Should we create a SQL server then host X number of databases on that server. when server reaches limits (X databases), create another server and execute same logic.

In either scenario, what difference does it make from Database Performace, Pricing and Database security point of view?

FYI, My thinking is that, If I host 'X' database on a single SQL Logical Server or If I create 'X' SQL Logical Server for 'X' SQL database hosting, It won't make any difference from Pricing and Database Performace point of view.

Answer 1

Few differences i could think of, if you go with single server for all clients..

1.Administrator Password is per Server and using this,one client can have access to other databases as well..
2.Azure has a limit of how many DTU's can be capped under one server,so if you have many databases under one server..This may lead to few issues like
a.)frequent DTU increase requests
b.)some times automated backup may fail,if there are no DTU's available(Backup needs to copy the whole database,so in this process ,DTU's needed will be equal to database which is backed up)

Answer 2

Your question is too broad, as there are many opinions and approaches to your question.

But in any way you should take a look at elastic database pools: https://azure.microsoft.com/en-us/documentation/articles/sql-database-elastic-pool/ which is a feature exactly designed for multi-tenant SaaS solutions.

Your end solution may be a combination of both - you may want to use a single server to "bigger" tenants, while you can host multiple small tenants together in a single server.

Security shall not be a factor with big weight because, when you use database contained credentials for application access, it does not really matter whether the databases are allocated in single logical server or not.