-2

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?

AjFmO
  • 395
  • 2
  • 4
  • 18
  • What are your particular requirements around needing a "dedicated instance", and what do you mean by that? On some cloud services, it refers to a virtual machine on a host, where no other entities can launch virtual machines on the same host (thereby attracting more charges). – John Rotenstein Aug 12 '19 at 21:15
  • Hi @JohnRotenstein I added more details to the question, please, if there's you think more information is needed let me know. – AjFmO Aug 12 '19 at 21:25

2 Answers2

1

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.

Ken W - Zero Networks
  • 3,533
  • 1
  • 13
  • 18
  • Awesome, this information is great. 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 proc locally. Is your suggestion can achieve this (now this is something more at development side) – AjFmO Aug 13 '19 at 00:22
  • You could use an Azure Data Factory Pipeline to call a stored proc. https://learn.microsoft.com/en-us/azure/data-factory/transform-data-using-stored-procedure – Ken W - Zero Networks Aug 13 '19 at 12:10
  • Thank you, Ken, you pointed me in the right direction, although I'm still kinda confused on which approach to take, definitely more oriented tho. There's another thing regarding what I need, we need SSRS also since this a reporting data solution. What do you suggest? (I'm editing my question to add this last part) – AjFmO Aug 15 '19 at 17:15
0

I suggest you host a static site on blob, an Azure function on consumption model to make calls to SQL database and a SQL database. Of course, there are alternative architecture you can use, however all depends on detailed requirements.

Junaid
  • 1,708
  • 16
  • 25
  • I added some description in the answer, do this blob function support SQL Server Sored procs and Agent Jobs? Do you think is feasible? – AjFmO Aug 12 '19 at 19:43
  • So you use blob storage to host the website which makes a call to Azure function when the user performs an action which then query/updates the database. https://learn.microsoft.com/en-us/azure/architecture/reference-architectures/serverless/web-app – Junaid Aug 12 '19 at 19:48
  • I added more details to the question, please, if there's you think more information is needed let me know. – AjFmO Aug 12 '19 at 21:26