Azure – Database design issue for Multi-tenant application

Here is where the problem starts:

"[…] When they have downloaded all the data we keep the Database for future."

This is very wrong because it leads to:

"[…] keeping on increasing the Azure Cost immensely, while 90% of the databases are not in use at a given point of time. We already have more than 10 elastic pools each consisting of 500 databases."

This is not only a problem of costs but also a problem with security compliance.

• How long should you store those data?
• Are these data complying with what county policy?

Here is my 2 solution:

1. It goes by itself that if you don’t need those data you just have to delete those databases. You will lower your costs immediately
2. If you cannot delete them, because they are not in use, switch from Elastic Pool to Serverless

EDIT:

Azure SQL Database gets expensive only when you use them.

If they are unused they will cost nothing. But "unused" means no connections to it. If you have some internal tool that wakes them up ever hours they will never fall in serverless state so you will pay a lot.

HOW TO TEST SERVERLESS:

Take a database that you you know it’s unused and put it in serverless state for 1 week; you will see how the cost of that database drop on the Cost Management. And of course, take it off from the Elastc Pool.

You can run this query on the master database:

DECLARE @StartDate date = DATEADD(day, -30, GETDATE()) -- 14 Days

SELECT
    @@SERVERNAME AS ServerName
    ,database_name AS DatabaseName
    ,sysso.edition
    ,sysso.service_objective
    ,(SELECT TOP 1 dtu_limit FROM sys.resource_stats AS rs3 WHERE rs3.database_name = rs1.database_name ORDER BY rs3.start_time DESC)  AS DTU
    /*,(SELECT TOP 1 storage_in_megabytes FROM sys.resource_stats AS rs2 WHERE rs2.database_name = rs1.database_name ORDER BY rs2.start_time DESC)  AS StorageMB */
    /*,(SELECT TOP 1 allocated_storage_in_megabytes FROM sys.resource_stats AS rs4 WHERE rs4.database_name = rs1.database_name ORDER BY rs4.start_time DESC)  AS Allocated_StorageMB*/ 
    ,avcon.AVG_Connections_per_Hour
    ,CAST(MAX(storage_in_megabytes) / 1024 AS DECIMAL(10, 2)) StorageGB
    ,CAST(MAX(allocated_storage_in_megabytes) / 1024 AS DECIMAL(10, 2)) Allocated_StorageGB
    ,MIN(end_time) AS StartTime
    ,MAX(end_time) AS EndTime
    ,CAST(AVG(avg_cpu_percent) AS decimal(4,2)) AS Avg_CPU
    ,MAX(avg_cpu_percent) AS Max_CPU
    ,(COUNT(database_name) - SUM(CASE WHEN avg_cpu_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name) * 100 AS [CPU Fit %]
    ,CAST(AVG(avg_data_io_percent) AS decimal(4,2)) AS Avg_IO
    ,MAX(avg_data_io_percent) AS Max_IO
    ,(COUNT(database_name) - SUM(CASE WHEN avg_data_io_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name) * 100 AS [Data IO Fit %]
    ,CAST(AVG(avg_log_write_percent) AS decimal(4,2)) AS Avg_LogWrite
    ,MAX(avg_log_write_percent) AS Max_LogWrite
    ,(COUNT(database_name) - SUM(CASE WHEN avg_log_write_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name) * 100 AS [Log Write Fit %]
    ,CAST(AVG(max_session_percent) AS decimal(4,2)) AS 'Average % of sessions'
    ,MAX(max_session_percent) AS 'Maximum % of sessions'
    ,CAST(AVG(max_worker_percent) AS decimal(4,2)) AS 'Average % of workers'
    ,MAX(max_worker_percent) AS 'Maximum % of workers'
  
  
FROM sys.resource_stats AS rs1
inner join sys.databases dbs on rs1.database_name = dbs.name
INNER JOIN sys.database_service_objectives sysso on sysso.database_id = dbs.database_id
inner join 

(SELECT t.name
    ,round(avg(CAST(t.Count_Connections AS FLOAT)), 2) AS AVG_Connections_per_Hour
FROM (
    SELECT name
        --,database_name
        --,success_count
        --,start_time
        ,CONVERT(DATE, start_time) AS Dating
        ,DATEPART(HOUR, start_time) AS Houring
        ,sum(CASE 
                WHEN name = database_name
                    THEN success_count
                ELSE 0
                END) AS Count_Connections
    FROM sys.database_connection_stats
    CROSS JOIN sys.databases
    WHERE start_time > @StartDate
        AND database_id != 1
    GROUP BY name
        ,CONVERT(DATE, start_time)
        ,DATEPART(HOUR, start_time)
    ) AS t
GROUP BY t.name) avcon on avcon.name = rs1.database_name


WHERE start_time > @StartDate
GROUP BY database_name, sysso.edition, sysso.service_objective,avcon.AVG_Connections_per_Hour
ORDER BY database_name , sysso.edition, sysso.service_objective

The query will return you statistics for all the databases on the server.

• AVG_Connections_per_Hour: collects data of the last 30 days
• All AVG and MAX statistics: collects data of the last 14 days