CODEWITHSUNDEEP
sqlsql-serverazure-sql-databasessms
Bonio
Bonio

Reputation: 342

Query multiple Azure SQL databases - Azure SQL Database cannot be used as a Central Management Server

I need to run a query against all databases (that have the same schema), the problem is these are Azure databases within an Elastic Pool. I read that this can be done using the "Central Management Servers" feature in SQL Management Studio but I have installed the latest version 18.3 but when I try and expand the Azure SQL server under "Central Management Servers" I get the following error:

Azure SQL Database cannot be used as a Central Management Server

The type of query I am trying to run against all the databases is as follows, this works fine on a local SQL Server instance but does not work on Azure SQL Server.

SET NOCOUNT ON;

IF OBJECT_ID (N'tempdb.dbo.#temp') IS NOT NULL
   DROP TABLE #temp

CREATE TABLE #temp
(
      [COUNT] INT
    , DB VARCHAR(50)
)

DECLARE @TableName NVARCHAR(50) 
SELECT @TableName = '[dbo].[CustomAttributes]'

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = STUFF((
    SELECT CHAR(13) + 'SELECT ''' + name + ''', COUNT(1) FROM [' + name + '].' + @TableName + 'WHERE dataType = 2'
    FROM sys.databases 
    WHERE OBJECT_ID('[' + name + ']' + '.' + @TableName) IS NOT NULL
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

INSERT INTO #temp (DB, [COUNT])              
EXEC sys.sp_executesql @SQL

SELECT * 
FROM #temp t

Upvotes: 1

Views: 2065

Answers (1)

Leon Yue
Leon Yue

Reputation: 16431

Azure SQL database doesn't support Administer Multiple Servers Using Central Management Servers.

enter image description here

Since your databases are in the same Elastic pool, you can using the Elastic Query to run a query against all databases.

The elastic query feature (in preview) enables you to run a Transact-SQL query that spans multiple databases in Azure SQL Database. It allows you to perform cross-database queries to access remote tables, and to connect Microsoft and third-party tools (Excel, Power BI, Tableau, etc.) to query across data tiers with multiple databases. Using this feature, you can scale out queries to large data tiers in SQL Database and visualize the results in business intelligence (BI) reports.

For more details, please see T-SQL querying:

  1. Reporting across scaled-out cloud databases (preview)
  2. Query across cloud databases with different schemas (preview)

Hope this helps.

Upvotes: 2

Related Questions