If you have to manage or work with an Azure SQL database and want to know what service tier the database is currently operating, but you don't have access to the Azure subscription or CLI.
Or you want to know the status of the service tier after a scale up or scale down, directly from the database; you can do it via T-SQL
Just query the sys.database_service_objectives DMO to obtain this information, this will give information about the service tier, and also will tell you if the database is part of an elastic pool or not.
Basic usage (in the context of the database you need the information):
SELECT * FROM sys.database_service_objectives;
This will return the following information for the current database:
 |
| I am running on a General Purpose tier with 1 VCore |
What if you want the information for all the databases created?
Just change the context to the master database and execute the following query:
SELECT D.name AS DB
, D.create_date
, SO.edition
, SO.service_objective
, SO.elastic_pool_name
FROM sys.databases D
INNER JOIN sys.database_service_objectives SO
ON D.database_id = SO.database_id;
Then you will be able to see each database tier and related elastic pools (if configured).
You can always change your Service objective via T-SQL issuing the ALTER DATABASE command.
More information here.