Azure SQL: Single Database or Elastic Pool?

Question

When using SQL Server I can create as many databases as I want with the T-SQL "CREATE DATABASE" command.

After reading azure-sql-revealed-a-guide-to-the-cloud-for-sql-server-professionals and looking at the calculator I'm confused about the option of "Single Database" and "Elastic Pool" for Azure SQL... Does this mean that when I choose the Single Database option I can only issue the "az sql db create" command once?

What is the difference between az sql db create and the T-SQL command "CREATE DATABASE"?

Sat Apr 10 2021 Update:

Thank you AnuragSharma...

As per my pervious discussion, it looks like a single tiny serverless database would be a minimum of about $5/mo. Can you please guide me on using the pricing calculator so I can learn how much that monthly cost increases each time I use the "CREATE DATABASE" command for another tiny serverless database (which I just learned from the book Practical Azure SQL Database for Modern Developers page 26 is not a candidate for elastic pool). Would each new serverless database be approximately another $5/month or would it be less because they can share the same server and firewall?

Thanks
Siegfried

Answer 1

Hi @Siegfried Heintze , welcome to Microsoft QnA forum.

Both the command 'CREATE DATABASE' and 'az sql db create' are similar in Azure SQL to create databases. CREATE DATABASE is a T-SQL Command where as 'az sql db create' is an Azure CLI command.

Assume we have created an Azure SQL Database 'Database1' with General Purpose pricing tier under Azure SQL Server 'Server1'. Let us look into below commands

T-SQL:

create database testdb2  

Running above command will create another database 'testDB2' under 'Server1'. As we have not specified any other parameter this new database 'testDB2' will be created under same pricing tier which is General Purpose.

CREATE DATABASE testdb2( EDITION = 'standard', SERVICE_OBJECTIVE = 'S0', MAXSIZE = 500 MB ) ;  

Running above command will create 'testDB2' under 'Server1' but as we have specified the service objective and size, this database will be created under standard pricing tier.

Now lets check the Azure CLI commands for the same:

az sql db create --name testdb2 --resource-group <resource-grp-name> --server Server1  

Above command is going to create testdb2 under 'Server1' with same General Purpose tier as we have not specified anything.

az sql db create --name testdb2 --resource-group <resource-grp-name> --server Server1 --service-objective S0  

Above command will create testdb2 under 'Server1' with standard pricing tier.

Another thing to note is the difference between Single Database and Elastic Pool

When we create a single database we allocate resources for this single database only. As an example if we create a Server 'Server1', we can add as many single databases to this server and each of these database will work based on the service tier it is created in. Like 1 database can belong to General Purpose and another could belong to Business critical. This way we can configure each database as per the need of it.

Elastic pool works on the concept of shared compute of databases. Now again we can add many databases to the server in elastic pool but these databases will share the resources. As an example, if you create an elastic pool with 500 DTUs and add 5 standard Databases, each database will share the available DTUs. I would suggest you to go through below link that explains this scenario in great details:

Difference between single and elastic SQL Database

Please let us know if this helps or else we can discuss further.

----------

If answer helps, please mark it 'Accept Answer'