Cloud Data Platform Evaluation

Goal

Establish a supportable cloud data-platform direction

The goal was to assess Azure SQL Database alongside Azure SQL Managed Instance, compare the relevant service tiers, select the option that best fitted the workload, and identify the application and deployment changes needed before migration.

The evaluation also needed to preserve operational control: repeatable upgrades, appropriate backup and recovery arrangements, reporting continuity, data-loading processes, a replacement for Log Shipping, and documented failover and failback procedures aligned with agreed RTO and RPO service levels.

Method

Assess compatibility before changing the application

The existing databases and queries were assessed with the compatibility checker. That evidence was considered alongside the platform’s use of SQL Server capabilities, connectivity requirements and deployment model when comparing Azure SQL Database with Azure SQL Managed Instance.

The assessment explicitly covered:

• SQL Agent jobs and the work scheduled through them.
• SSRS migration, Power BI compatibility and the reporting access model that still depended on Windows identity.
• Query compatibility and the changes identified by the compatibility checker.
• Database triggers, login triggers and their operational assumptions.
• Cross-database joins and the boundaries they created between databases.
• ETL pipelines, bulk insert operations and the location and access assumptions behind them.
• Existing Log Shipping used to create read-only databases for BI and reporting workloads.
• Replication as a possible replacement for Log Shipping, and the reasons for discounting it.
• General Purpose and Business Critical tiers, including cost, performance, availability and read-scale differences.
• Backup strategy, a high-availability readable secondary, failover and failback procedures, and the RTO and RPO service levels the design needed to meet.

The assessment found that the existing SSRS reports were compatible with Power BI. The reporting move was not therefore blocked by report capability, but it was not a simple hosting change: access control had been built around Windows Active Directory, so authentication and authorisation needed to be redesigned for the Power BI delivery model.

Replication was considered as a replacement for Log Shipping but discounted. The evaluation instead settled on a high-availability readable secondary for BI and reporting, which made the Business Critical tier materially different from General Purpose in both capability and cost. Azure SQL Managed Instance was selected as the migration target. The planned application changes were implemented, environment-specific configuration was moved out of deployment artefacts, and the release process adopted a new DACPAC and controlled database-upgrade process. Deployment and ETL pipelines were updated and used to complete the move to the Azure-hosted target.

Technical Challenges

Database migration included the surrounding operating model

The challenging dependencies were not confined to SQL syntax. Agent jobs, reports, file-based bulk operations, login behaviour and cross-database queries each depended on capabilities or access patterns outside an individual application database.

Deployment was another material boundary. A database could pass a compatibility assessment and still be challenging to operate if upgrades, environment configuration and release ordering were not reproducible. The DACPAC, configuration-injection and pipeline work therefore formed part of the database migration rather than being treated as follow-on automation.

Resilience also needed an Azure-specific design. The readable high-availability secondary addressed the reporting workload that Log Shipping had supported, but it did not remove the need to define backups, disaster-recovery options, failover and failback procedures, and the RTO and RPO service levels against which those procedures would be assessed.

Outcomes

A successful migration grounded in evidence

The evaluation produced a clear rationale for Azure SQL Managed Instance and its service tier, and identified the application, database and delivery changes required to support it. Compatibility findings were converted into implemented refactoring work; replication was discounted; and the high-availability readable secondary, reporting workloads, jobs, bulk loading, upgrades, backups and recovery procedures were incorporated into the delivered solution. It also established a technically compatible route from SSRS to Power BI, with identity redesign identified as the material prerequisite.

The application, databases and release processes were successfully moved to the Azure-hosted platform using the planned migration route. Treating services and operating practices as part of the data-platform change allowed the implementation to complete without separating the database move from the capabilities required to run and support it.

Business benefits

Reduce migration risk without discarding proven capability

• A cloud database choice based on observed workload dependencies rather than assumption.
• Compatibility issues identified before they could emerge during migration.
• SQL Agent, SSRS, ETL, bulk-load and cross-database requirements treated as first-class work.
• A validated route from SSRS to Power BI with the Windows AD access-control dependency made explicit.
• A defined replacement for Log Shipping used by BI and reporting workloads.
• General Purpose and Business Critical capability and cost considered explicitly.
• The evaluated target architecture implemented successfully.
• Repeatable database upgrades through a new DACPAC and revised deployment pipelines.
• Environment configuration separated from deployment artefacts.
• Backup, readable-secondary, failover, failback, RTO and RPO considerations included in the target operating model.

Architecture value

Connecting platform choice to successful operation

The database could not be separated from reporting, scheduled work, deployment, recovery and service commitments. Architecture brought those concerns into one target design, turning evaluation evidence into an implemented platform that the application could run on, the delivery process could upgrade and operational teams could support.