Need to help on SQL Database on Azure

Question

Hi All,
I have been asked to provide an Architecture/Solution for hosting Small Database on Azure. This DB is small in size now and going to be used for reporting purposes (Mainly Power BI and Excel Spreadsheet or SSRS) but keep in mind that this DB can be grown in the future and can be used as a small or mini Data Warehouse.

Basically, we are going to pull Data From API and Store it in this SQL DB for reporting purposes. We are using SQL Python for ETL and running Python scripts on SQL Server 2017. These Python scripts are scheduled to run on SQL Server Agent. Also, the plan is to build this on-premise then either lift and shift or migrate.

I thought of the following solutions:

1. (IaaS): Host this SQL DB on Azure VM with SQL 2017 or a higher version with SQL Standard license. If we go with this solution then SSIS & SSRS can be hosted on the same VM if required.
2. (PaaS): Azure SQL Database vs. Azure SQL Managed Instance: https://learn.microsoft.com/en-us/azure/azure-sql/database/features-comparison As we need SQL Server Agent to schedule and run Python Scripts, so we can only go with Azure SQL Managed Instance as Azure SQL Database does not have SQL Server Agent. https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-database-paas-overview

We are looking for a future-proof, cost-effective solution.

Guru's please can you guide us.

Thanks!

Answer 1

Hi,

Your description is completely vague and you can not be given direct advice on what to do without knowing the needs and build an orderly system characterization.

In the forum we can help in focused questions and mostly with technical question. It is much more complex to discuss architecture of future system and even more to give any advice. The maximum we can do in this is probably give you points to think about and the general recommendation to get an architect to your help - a person that will spend some time to learn the needs and the company in general and will be able to advice you on the next steps.

SQL 2017 or a higher version

It make no sense for most cases to select old version if you need to buy a new license. You need a VERY good reason to spend money on SQL Server 2017 instead of the latest version!

SQL Standard license

Why standard and not the free version express?
In fact, you should first ask yourself why relational database, next ask yourself why SQL Family, and then ask yourself why this version...

If we go with this solution then SSIS & SSRS can be hosted on the same VM if required.

And if you go with other solutions then you have other options.

You can use for example Azure SQL Database and manage the reports using Power BI or using DirectQuery.

As we need SQL Server Agent to schedule and run Python Scripts

There are many reasons to use Azure SQL Managed Instance but non of these is this. Python is supported in Azure SQL Database as well and there multiple ways to schedule tasks -for example you can use elastic jobs.