Type of Databases on Azure SQL Data Warehouse

Question

Is there a way to look at catalog data to determine if the database is an Azure SQL Data Warehouse Database?

For example, I have an initial database I've created via Azure, however I've found I can create another database in this Azure SQL Data Warehouse database without specifying EDITION or SERVICE_OBJECTIVE.

I'm assuming this is a logical Azure SQL Database like master, but it is not a system database so I'd like to be able to determine the "type" of database I am connecting to. So far I've just thought about looking to see if particular pdw_ views exist however I'm thinking there is probably a better way.

Thank you for your help!

Matt Usher Matt Usher · Accepted Answer · 2015-10-30T00:13:42

Note: The below suggestion works at the time of the post. This behavior is subject to change in the future.

There is not a straightforward way to do this today. I've added a feature request for a way to discover the service objective via TSQL. As a shortcut, you can run the following command:

SELECT
    name,
    recovery_model,
    recovery_model_desc
FROM
    sys.databases
WHERE
    name = DB_NAME();

If the recovery_model = 3 (SIMPLE), this is a SQL Data Warehouse database. If recovery_model = 1 (FULL), this is a SQL database.

Update May 2016:

The following query will return DataWarehouse if you are connected to an Azure SQL DW database:

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Edition') As Edition

Update #2: You can query the sys.database_service_objectives DMV to get this information:

SELECT
 db.[name] AS [Name],
 ds.[edition] AS [Edition],
 ds.[service_objective] AS [ServiceObject]
FROM
 sys.database_service_objectives ds
 JOIN sys.databases db ON ds.database_id = db.database_id

Type of Databases on Azure SQL Data Warehouse

1 Answers