This is the first in what will likely be many projects and I’m hoping to find someone who I can work with throughout all the projects (there is currently no timeline for the other projects, although I’m hoping to begin over the next few months).
I currently have an in-house, .NET point of sales application using a SQL Server database that runs independently in two of my shops. In the future I will be having an app written to allow my staff to remotely manage some of their work. Because of this, I need a way to sync both local and remote database changes with each other. I am thinking of having a central database, hosted in AWS which can be read from and updated using AWS Lambda’s (with Authentication and security) and a way to pull the changes from the central AWS database to the local databases as well as push local changes to the central one.
The two shops are separate companies and any changes sent between the local and AWS database need to be segregated by that company. Each company has a few users, each of which can have a company code, login name and password for authentication and to identify which company the data is from.
I need a method to track the changes made between the central database and the local databases and I was thinking that using AWS Lambda’s to interact with the central database and a local Windows service to interact with the local database as well as interact with the AWS Lambdas would be the best way to do it. (If you have a better idea on how to achieve this, I’m open to suggestions).
I will also need a method added to the Lambda that will allow file transfer to and from Amazon S3 storage to let my staff (eventually) upload PDF’s and store them. The files will need to be tracked so that they can be pulled back out and downloaded. These should also be segregated by company and use the same authentication.
NOTE: Because eventually mobile apps will be needing to access the central database using the Lambdas, whatever you build, must have methods of security and authentication (I’m open to your suggestions) as well as be something that an app will be able to access and work with.
NOTE: I will be adding another few shops to this within the next 6 months and some of them will be initially running SQL Express, so please make sure that anything you build uses functionality that supports SQL Express.
I will supply:
I have attached a word document with some SQL scripts to build sample tables from our current database and have supplied some sample data in there as well. You are required to use these samples to decide what changes are required to our current database in the local version as well as what changes would be required to be able to use the database as the central database (they can of course be different). Initially I thought using staging tables to host the changed data, or perhaps adding additional fields to track changes but it’s really up to you on what you think is best.
What you will deliver:
(As mentioned, if you have a better and more efficient way of achieving what I need please let me know)
1 x AWS Lambda (or an alternative) written in C# to update, insert and delete information in a SQL database (with security and authentication) as well and send, receive and store PDF files to an Amazon s# instance - segregated by user/company.
1 x Windows service (or an alternative) that will periodically push updates from the local database to the AWS hosted database and pull updates from the AWS hosted database to the local database (sync changes)
1 x Central SQL database, modified from the supplied sample to work with the AWS Lambda (which I will deploy on an AWS SQL instance)
1 x local (on-site) SQL database, modified from the supplied sample to work with the Windows service (which will be deployed in several locations)
If you have any questions please let me know.