Search code examples
amazon-web-servicesazureserverclouddedicated-server

What is the best solution on AWS or Azure for SQL+Web App+SSIS+SSRS

This is about a Reporting Server solution.

I need some advice to choose a product, which will hold a SQL Database Server and a Web Service App (one that will make a call to a stored procedure and run an SSIS package - not much processing here -) and SSRS. I'm not familiar with this, it needs to be available 24/7, as I said there's no much processing just synchronizing data (few hundreds of thousands of records), what do you suggest me?

Requirements:

  • SQL Server Enterprise 2017: this will hold the database and execute the SSIS package.
  • We have an SSIS package that will be executed from a .Net Web Service app which will execute a Stored Procedure on users demand.
  • The Server needs to run Reporting Services (SSRS).

Considerations:

  • Storage: Database will hold around 750K records (all text).
  • Bandwidth: There will be synchronization (data retrieval or updates only) with an external system.
  • Use: the client has asked to consider a dedicated instance since they will use it at their own discretion.

Now the only issue is, as far as I know, we can't call a Stored Procedure from the outside system (outside the server), or at least I have not found a way to do that, that's why I want to host both solutions in one place, so the Web Service App can call the Stored Procedure Locally. So now I'm wondering, what should I do? should I leverage a full VM? how much will cost?

Solution

  • If you want to do PaaS and not have to manage infrastructure, take a look at the Azure App Service Environment is an Azure App Service feature that provides a fully isolated and dedicated environment for securely running App Service apps at high scale. This capability can host your:

    • Windows web apps
    • Linux web apps
    • Docker containers
    • Mobile apps
    • Functions

    For SQL you can use Azure SQL Database Managed instance,a new deployment option of Azure SQL Database, providing near 100% compatibility with the latest SQL Server on-premises (Enterprise Edition) Database Engine, providing a native virtual network (VNet) implementation that addresses common security concerns, and a business model favorable for on-premises SQL Server customers. This is a fully isolated instance of SQL server.