Click here to Skip to main content
15,867,704 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more: , +
We have a relative big SQl Server 2008 Database that is runing in our Companys LAN. The management want's that ONLY SOME of the order data could be called from the internet, so the clienst could track the status,change order parameetrs etc..

Because I have no expiriance in online applications, databases and security I would like to at least make a start that uses best practise to awoid problems in the future development and expansion of the application. I would like to start with small features and add new ones as my "online" skills get bether.

Requerements:
- clients can see their orders and change parameters
- clients can add new orders or cancel/delete them

Dificulties: the whole application is on a local server and must remain like this. Reason is that the German law doesn't alow us to make some sensitive data, like finantial, employ etc... , reachable from the internet. The order statuses (stored in a seperate table) can be saved online because we use orderID-s and without the main order table they have no sence and can not be used like that.

Idea:
Main idea:
We make a server that is not in our LAN. Store there all data in MySQL and work with API calls with them.

Order statuses:
We store all status data only in that MySQL table and if we need them in our Application we call them from the Internet using the same API-s like our customers.

Orders
This is more complicated. If the users can add,change and delete orders and I can not save all order data in the MySQL table (only the most importand data) how could I assure that the LAN SQL table and MySQL table are synced correctly?

On the Internet I found this article:
http://www.ideaexcursion.com/2009/02/25/howto-setup-sql-server-linked-server-to-mysql/[^]

that shows how to link a SQL with a MySQL table. That would just make the API cals away, but still i would have to sync the data between the two tables either with SQL TRIGGERS or manualy using application code (for what I think is not a good idea or a good practise).

Question/s:
Are there best practices for sutch kind of problems and if yes what are they?
Are there solutions in witch only a part of a SQL table is synced with a MySQL table?
On what security details should I take care of to not expose or data to everyone?
Am I on the right trak to solve this problem using a best practice or am I making a big mistake from the beginning?
Posted

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900