question

SiegfriedHeintze-9929 avatar image
0 Votes"
SiegfriedHeintze-9929 asked AnuragSharma-08 commented

Azure SQL: Single Database or Elastic Pool?

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


azure-sql-database
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SiegfriedHeintze-9929, wanted to follow up on this if you have further queries. if answer helps, you can mark it 'Accept Answer'

0 Votes 0 ·

Yes I have a further query. Please see my Apr 10 update.

0 Votes 0 ·

Hi @SiegfriedHeintze-9929, i have posted the response on the same answer. Could you please check and let us know if this helps or more information is needed. If answer helps you can mark it as 'Accept Answer'.

0 Votes 0 ·

1 Answer

AnuragSharma-08 avatar image
1 Vote"
AnuragSharma-08 answered AnuragSharma-08 commented

Hi @SiegfriedHeintze-9929, 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'





· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SiegfriedHeintze-9929, thanks for replying back.

As rightly mentioned by you Serverless compute is only supported for General Purpose tier as of now and not available for elastic pool.

That means that each instance of Serverless will be costing individually irrespective if they all share the same server and firewall. But good part of Serverless is its costing can go down further based on minimum setting and usage. We can lower the cost to $2-3/month as well.

87611-image.png

Pricing calculator

Please let me know if this helps.

0 Votes 0 ·
image.png (50.9 KiB)