Database Server Design Question

[grip003]

Hey everyone:

 

I have been writing software for a company for a couple of years now using VB6.0 (sorry) and C#. The software was designed to work on a local area network. However, the company has expanded and now owns multiple locations that want access to the main database (through my software). This was temporarily solved by using a VPN connection, but that has become horribly slow. So, my suggestion was to create a C# server program that handles all requests to the database (it is a MySQL database). I would open a single port and use port forwarding to allow access to this new program. The program would then look at the request, make sure it is valid, and then query or update the database, returning the necessary values back to the client program. Does this sound like a good solution? I would appreciate any suggestions, thanks.

[grip003]

I think I have come up with a much better solution. What about port forwarding the port I listen on for the MySQL server, and then just change the ip addresses used in my software to the static ip address for the company? I am just worried about security issues...

[cpopham]

You can get various peices of information off of Windows PC using C# or VB .NET. You could get the MAC address of the PCs, you can google to find out how. This would take more work, but then you could verify these physical MAC addresses against a table and if they mtached they would get in and if they did not, you could deny them. You could build a small application that each user could run that would get the MAC address and forward it on to where you could do whatever with it.

 

Chester

[samsmithnz]

Is adding SQL Servers in the other locations and replicating the data an option?

[grip003]

Well, we really don't want to do replication. Right now there is just one big database that all the different locations need access to, even though all the data in the tables is broken down by location. In the long run, we want to create seperate databases for each location, because they generally don't share the same information (except at the main location). Then we could replicate from the other locations, each using their respective databases. The main location just needs lookup capability from the other locations.

 

I've been trying to get port forwarding working so I don't have to change any of my code, except for the connection string. I am having difficulty getting this to work at my office, I think because I don't have a static ip address. I really just want to see if it would work, and then migrate the port forwarding to the main location. I guess this has turned into a port forwarding/mysql issue. If anyone has a solution I would be very happy.

[samsmithnz]

It sounds like opening ports and such isn't going to be a very good solution. It might be faster once it's working, but you're losing all the security you have built into your VPN sessions. We have a simliar problem here, where some applications are designed to be used in the office, and even if you're at home on a Cable connection, they slow down considerably. We solved the problem by installing SQL servers in each office.

 

I'm not sure what else you could really do...