Legacy data is being stored in an implementation of Microsoft SQL Server. A cloud-based (SaaS) program needs to make API requests (REST) and receive JSON responses. The question is how do I set up an API on Microsoft SQL Server? My problem is that I do not know where to begin looking or how to use the right search terms. We're a small shop and because I know how to setup and configure a MySQL implementation on a LAMP server, someone thought it would be no different if they put the legacy data on a Microsoft SQL server.
SAAS => REST Request (GET) => Microsoft SQL Server => Database
Database => Microsoft SQL Server => JSON Data => SAAS
I've tried looking into the SQL server for connection setup, but cannot find anything that catches my eye. Then it occurred to me that it might be an IIS issue. No luck there. Whenever I search the web for answers, the results typically end in some company trying to sell me a product that will do this.
I imagine that the layers of communication are:
Configure the server to listen for requests
Establish a connection between the Listener and Microsoft SQL
Configure Microsoft SQL to acknowledge connection requests
Route different requests to different queries
Configure Microsoft SQL to produce a report in JSON and hand it off to whatever transport protocols are running on the server to ship out the data.
HAPPY FOR ME this is Legacy data. It will NEVER change. The only thing I need to configure on the server are GET requests. Right now, this is the only database project that will need to be set up from scratch.
I don't expect anyone here to spell this out for me step-by-step. However, if someone has a link to the step-by-step instructions, I'm happy to go there.