question

TomJoyce-6178 avatar image
0 Votes"
TomJoyce-6178 asked pituach edited

List of SQL database with Hybrid pricing

I have a lot of SQL databases across multiple subscriptions. I need to list out all the SQL databases that has a column that lets me know if Hybrid pricing is being used or not. It is easy to do with Servers but need to also do the samething for SQL databases so we know what we need to work on to help reduce our costs.

azure-sql-database
· 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 @tomjoyce-6178,

Thanks for using Microsoft Q&A !!
Is this column is created and maintained by you across all databases ? If you are using SSMS then you could probably create a server group and execute the query on the server group to get the results from all the servers, if this works in your case. Please refer to the documentation for details.

Thanks
Saurabh

0 Votes 0 ·

1 Answer

pituach avatar image
0 Votes"
pituach answered pituach edited

Good day Tom,

List of SQL database with Hybrid pricing

I assume that you mean databases which are using your local SQL Server licenses with Software Assurance - Azure Hybrid Benefit.

This is great question, but I think that it is not supported in transact SQL (yet).

According to the documentation you can select the pricing tier but there is no option to select the license type (Hybrid for example).

To get the information of the pricing tier for all the databases, you can use sys.database_service_objectives. This view returns the edition but not the information about the license type.

There is a parameter named LicenseType in the SERVERPROPERTY, but it is always returns DISABLED. Maybe in the future it will work.

It is a good feedback for the data platform team to add this option using sys.database_service_objectives

What is the solution?


In the meantime, you can get the information with PowerShell.

Execute the command: Get-AzSqlDatabase -ResourceGroupName "your resource name" -ServerName "your server name"

This will return all the information about all the databases in the server including the parameter LicenseType which is what you are looking for (value BasePrice means Azure Hybrid Benefit) - You can run this on all servers


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.