ASP.NET - SQL Process Design Connect to Multiple DBs

Question

Scenario

Web server running an ASP.NET site that's connected to a sql server instance for all the web related DB needs (products, pricing, users, etc.)...

The companies accounting, inventory control(FIFO, etc.) and whatnot are mainly done on another system which uses a different SQL server...much more complex, for obvious reasons.

What I Need

What I need is to retrieve the current quantity on hand for every product that's on the web by connecting to the internal accounting sql server, even though the website uses a different sql server for it's own requirements.

The reason I can't have the information that is required to calculate the quantity on hand for every product on the web to be on the same sql server is because there are way too many tables that are needed to calculated that information...what's on purchase order, what's on sales order, and the physical inventory.

What is the best way to accomplish this without altering either database?

Answer 1

You can achieve this in two ways.

1. If you can connect directly to the SQL server from the website. Do that.

2. Create a Service (Web/Windows Hosted) which will be able to access internal complex SQL Server and be exposed to your web site so that it can access the information you need there.

Hope this helps.