SQL Database migration from VM to Azure SQL

Question

Hi ,

I hope you can assist with this. We’re planning to migrate our SQL databases from an Azure VM to Azure SQL.

For context, we have SSAS tabular models (with complex logic and calculations), Power BI reporting services,

and a .NET web application (using stored procedures and some reports are connected to it from PBRS), all currently relying on the SQL database

hosted on an Azure VM. We're evaluating the best approach—whether to use Azure SQL for storage and Databricks for SSAS processing and analytics.

We're uncertain if the SSAS models and the web application will integrate smoothly with Databricks.

If not Azure SQL, how feasible is a full migration to Databricks? Additionally, we have users accessing the database via Power Query in Excel.

Could you recommend whether Azure SQL, a hybrid approach (Azure SQL + Databricks), or Databricks alone would be most suitable for our scenario?

I have further questions regarding budget estimates but will hold off until this is clarified.

Answer 1

Hi Sumayya Sayed,

Thanks for reaching out to Microsoft Q&A.

For the migration of your sql databases from an azure vm to azure sql, following are the approaches considering your current setup and requirements:

1. Azure SQL alone:

This would be the most straightforward migration path. Azure sql database offers high compatibility with sql server, which minimizes changes needed for your applications and reports.

Compatibility: direct migration might require minimal changes to your .net application and power bi reports.

SSAS integration: azure sql doesn't natively support ssas. You would need to continue managing ssas on a vm or consider azure analysis services (aas) as an alternative to ssas for hosting tabular models.

2. Hybrid approach: azure sql + databricks

Using azure sql for storage and databricks for processing and analytics can offer powerful analytics capabilities but might introduce complexity in terms of integration and architecture changes.

Data storage and handling: store transactional data in azure sql and use databricks for heavy analytics and processing tasks that are beyond typical sql capabilities.

Ssas and databricks: databricks doesn’t directly support ssas tabular models. You might have to redesign the ssas models into spark-based solutions within databricks, which could be complex and time-consuming.

Application integration: databricks can connect to .net applications via jdbc/odbc, but this setup might be more complex compared to using azure sql directly.

3. Databricks alone

Migrating entirely to databricks would involve significant changes to your architecture and potentially your data strategy, as databricks is not a direct replacement for a transactional database like sql server.

Data lake: databricks functions well with a data lake architecture, requiring you to adjust how data is stored and processed.

Application and reporting: significant rework might be needed for your .net application and reporting services to integrate smoothly with databricks.

User access: excel connectivity through power query would require additional setup, potentially through data exports or using databricks’ odbc drivers.

Recommended approach:

Given your use of ssas tabular models, power bi, and a .net application heavily integrated with your sql database, a hybrid approach utilizing azure sql for db storage and azure analysis services for handling ssas models might be the most suitable. This approach minimizes disruption to your existing applications and reporting services while still enabling enhanced analytics capabilities where needed.

Using databricks additionally for specific heavy analytics needs can further enhance your capabilities without completely migrating all operations to it, thus providing flexibility and powerful analytical processing without the overhead of completely redesigning your data infrastructure.

You can further explore specific migration strategies, such as using the azure database migration service (DMS) which supports migration from sql server on azure vm to azure sql database, ensuring a smooth transition with minimal downtime.

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.