Cross Database Queries in SQL Azure DB - Part 2

Hi All

 

So After a month of using Cross Database Queries in SQL Azure DB, I can say that we have a very useful tool.

 

We can use in many scenarios, I will show today one.

 

We are using 10 shards DB's to balance read operations to many DB's. The write operations goes to the DB's as well.

 

I' as a DBA wants to know the status of all DB's, so instead of creating PowerShell that connect to all of the DB's I have created external tables in 1 DB that reads the  

 

sys.dm_db_resource_stats 

 

from all DB's' so I have now 1 real table and 9 external tables.

 

I have created a view that reads from al 10 tables' and now I see all data from all shards.

 

I also put it in PowerBI and I see it in a visual way - and also in my Mobile.

 

Here I have in the first line DTU AVG value for each shard in the last 10 minutes, 1 clock that avg all shards.

 

The second line is the DTU real value in the last 10 Minutes per Shard.

The Third line is the DATA IO value in the last 10 Minutes per Shard.

 

 

 

I More thing, in ssms in sql server 2016 you have special place for external tables:

 

 

 

 

But in SQL Azure - not yet, and even if you will run create script you will not see it.... just use :

 

select * from sys.external_data_sources

select * from sys.external_tables

 

enjoy.