ODW database maintenance

This page describes the cloud equivalent of the classic ODW database maintenance routine for Omada Identity Cloud Private environments running on Azure (AKS, App Services, Azure SQL Database, Service Bus). It covers what Azure handles automatically, what you still need to do, and what you must not do on a PaaS database.

Databases in Cloud Private

All Omada databases reside on a single Azure SQL logical server within a single elastic pool. They are declared in Terraform, not provisioned manually:

Database	db_name	Collation	Defined in
Omada Identity (ES)	ois	SQL_Latin1_General_CP1_CI_AS	iac/infrastructure/iac/common-sql-databases.tf
Omada Identity Audit	oisa	SQL_Latin1_General_CP1_CI_AS	iac/infrastructure/iac/common-sql-databases.tf
Role and Policy Engine	rop	SQL_Latin1_General_CP1_CI_AS	iac/infrastructure/iac/common-sql-databases.tf
Omada Provisioning Service	ops	SQL_Latin1_General_CP1_CI_AS	iac/infrastructure/iac/common-sql-databases.tf
Omada Data Warehouse	odw	Danish_Norwegian_CI_AS	iac/infrastructure/iac/common-sql-databases.tf

The server and pool are defined in iac/infrastructure/iac/common-sql-server.tf and iac/infrastructure/iac/common-sql-elastic-pool.tf. All databases join the elastic pool when applications.omada.sql_server.use_elastic_pool = true in iac/infrastructure/iac/iac-shared-config.tf, in which case per-database sku_name and max_size_gb settings are ignored – the pool governs compute and storage.

warning

The ODW collation is deliberately Danish_Norwegian_CI_AS and differs from the other databases. Do not change it: the ODW BACPAC and Omada's data-warehouse logic depend on this collation.

important

The elastic pool is a shared resource. Heavy maintenance, such as an index rebuild on odw, consumes pool CPU and I/O and can degrade other Omada databases in the pool at the same time. Keep maintenance minimal, online, and off-peak.

Backups, retention, and recovery

Azure SQL Database takes automated backups with no agent, job, or script required on your side:

• Full backups weekly, differential every 12–24 hours, transaction-log every 5–10 minutes – all automatic. You never run BACKUP DATABASE.
• Point-in-time restore (PITR): restore to any second within the short-term retention window. The Terraform module (iac/infrastructure/iac/Modules/SqlDatabase/main.tf) does not override short_term_retention_policy, so the Azure default of 7 days applies. To extend this (maximum 35 days) or add long-term retention (LTR), add the corresponding policy block to the module rather than configuring it manually in the portal, so it remains in Terraform state.
• Backup storage redundancy is environment-driven via database.sql_database.backup_storage_redundancy in iac/infrastructure/iac/iac-shared-config.tf:

Environment	Redundancy	zone_redundant
Dev (dv0000, personal <ll><nnnn>)	Local (LRS)	false
UAT / staging (uat)	Zone (ZRS)	false
Production (prod)	Geo (GRS)	true

The recovery model is fixed at FULL and cannot be changed. The on-premises advice to use bulk-logged recovery for ODW and staging to reduce log growth during imports has no cloud equivalent – there is no log growth or disk problem to solve, because the platform manages the log.

To restore the ODW (for example, after a bad import): use PITR to a new database via the Azure portal or az sql db restore, validate it, then repoint. Do not attempt an in-place restore from a backup file – there are no backup files you control.

Index and statistics maintenance

Two complementary layers cover index and statistics maintenance on Azure SQL Database. Enable the platform layer (see below) on every environment; add the scheduled Ola Hallengren layer for UAT and production, as Omada Identity Cloud offering does. The on-premises recommendation to rebuild all indices weekly is replaced by this approach.

note

On Azure storage, there is no sequential-vs-random I/O penalty, so high fragmentation has far less impact than on-premises. Microsoft's guidance is to maintain selectively by threshold and page count, online, and off-peak – which is precisely what a correctly parameterised Ola schedule does.

Platform automatic tuning

Automatic tuning has three options that should be treated very differently:

• FORCE_LAST_GOOD_PLAN – low-risk and recommended. It only reverts query-plan regressions and never alters the schema. Review and enable it under SQL Database > Automatic tuning in the portal (it is not set by Terraform today).
• CREATE INDEX / DROP INDEX – enable only with caution, and only by a senior DBA who knows the customer's data and workload. Shipping a correct set of indexes is the Omada product's responsibility, delivered in the database BACPAC. Auto-generated indexes can shadow, duplicate, or conflict with the product's design and degrade performance; auto-drop can remove an index the product relies on. If you enable these options, do so on a non-production copy first and track every applied recommendation thoroughly (Query Store before and after, sys.dm_db_tuning_recommendations), ready to revert. The safer default is to leave them off and raise a ticket with Omada when you suspect a missing or harmful index.

Auto-update statistics is on by default. The Ola schedule described below still refreshes modified statistics explicitly, which is more reliable for the large, bulk-loaded ODW tables.

Diagnosing a one-off performance problem

Check fragmentation and page density for the indexes behind a slow query (run against the affected database):

Format: SQL

SELECT  OBJECT_NAME(ips.object_id)            AS table_name,
        i.name                                AS index_name,
        ips.avg_fragmentation_in_percent,
        ips.avg_page_space_used_in_percent,
        ips.page_count
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
JOIN    sys.indexes i
        ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE   ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;

Thresholds: 5–30% > REORGANIZE; above 30% > REBUILD. Indexes under 1,000 pages are skipped.

Scheduled Ola Hallengren IndexOptimize (recommended for UAT and production)

Omada Identity Cloud uses the Ola Hallengren SQL Server Index and Statistics Maintenance solution on a schedule, and the same approach is recommended for Omada Identity Cloud Private UAT and production databases. IndexOptimize is fully supported on Azure SQL Database, including resumable online rebuilds, so it runs without blocking the workload.

Deploy it: run the relevant parts of Ola's MaintenanceSolution.sql against each Omada database (ois, oisa, rop, ops, odw). On Azure SQL Database, the objects (dbo.IndexOptimize, dbo.CommandLog, dbo.CommandExecute) are created per database, because there is no shared master or msdb and no SQL Server Agent. Then schedule the call via Elastic Jobs or Azure Automation (see Scheduling).

Recommended call – threshold-based, online, resumable, statistics included, time-boxed, and logged:

Format: SQL

EXECUTE dbo.IndexOptimize
    @Databases              = 'odw',
    @FragmentationLow       = NULL,
    @FragmentationMedium    = 'INDEX_REORGANIZE',
    @FragmentationHigh      = 'INDEX_REBUILD_ONLINE,INDEX_REORGANIZE',
    @FragmentationLevel1    = 5,
    @FragmentationLevel2    = 30,
    @MinNumberOfPages       = 1000,
    @UpdateStatistics       = 'ALL',
    @OnlyModifiedStatistics = 'Y',
    @Resumable              = 'Y',
    @TimeLimit              = 3600,
    @LogToTable             = 'Y';

note

Ola's default @FragmentationHigh setting allows an offline fallback (INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE). On a pooled production database, omit the offline fallback and fall back to INDEX_REORGANIZE instead, so maintenance can never take blocking object-level locks. Keep @TimeLimit comfortably inside your maintenance window and run during off-peak hours.

For a statistics-only run (lighter weight, for example, nightly), set @FragmentationMedium and @FragmentationHigh to NULL and keep @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'Y'.

Scheduling

Azure SQL Database has no SQL Server Agent. Options for scheduling IndexOptimize:

• Elastic Jobs – the Azure-native SQL Agent replacement: schedules T-SQL across one or many databases in the pool, logs status, and retries. Preferred for a recurring IndexOptimize across all five databases.
• Azure Automation runbook or scheduled Azure DevOps pipeline – if you prefer the schedule to live alongside the rest of the IaC tooling. The repository's sql_execute Ansible module (Entra/SP auth) can drive an ad-hoc run.
• Manual or ad-hoc – sqlcmd, SSMS, or Azure Data Studio for one-off maintenance.

Whatever you choose, run during off-peak hours and monitor the elastic pool DTU/vCore and log-IO metrics during the window, as maintenance on one database draws from resources shared by all databases in the pool.

Application-level housekeeping

The history retention, import log removal, and old data purge items from the online documentation are Omada application concerns, not SQL maintenance tasks. In Cloud Private, they are configured the same way as on-premises – through Omada, not the database:

• History and data-warehouse retention is set in the Omada admin UI / import configuration, and the ODW import process enforces it. Do not delete rows directly from ODW tables – this will corrupt the warehouse state.
• Import status and logs are observed via the portal and Azure telemetry (Application Insights and Log Analytics), not by reading SQL Agent job history. See Searching logs and checking for errors for where import logs and errors live in the cloud deployment.

What not to do

The following actions are harmful on Azure SQL Database:

• Do not shrink databases or files as routine maintenance – it re-fragments indexes and churns the log. Only shrink as a deliberate one-off to stay under a tier size limit, and rebuild affected indexes afterwards.
• Do not run manual BACKUP DATABASE to truncate the log – unsupported and unnecessary; the platform owns backups.
• Do not change the recovery model – it is fixed at FULL.
• Do not run offline or blocking index rebuilds on the pooled production database.
• Do not delete ODW rows by hand to manage history – use Omada's retention settings.
• Do not change the odw collation (Danish_Norwegian_CI_AS) – it is intentional.