The basic layout of my problem is this:
Website (ASP.NET/C#) hosted at a dedicated hosting company (location 1)
Company database (SQL Server) with records of relevant data (location 2).
Location 1 & 2 connected through VPN.
Customer visiting the website and wanting to pull data from the company database.
No possibility of changing the server locations or layout (i.e. moving the website to an in-office server isn't possible).
What I want to do is figure out the best way to handle the data acces in this case, minimizing the need for time-expensive database calls over the VPN. The first idea I'm getting is this:
When a user enters the section of the
website needing the DB data, you pull
all the needed tables from the
database into a in-memory dataset. All
subsequent views/updates to the data
is done on this dataset. When the user
leaves (logout, session timeout,
browser closed etc) the dataset gets
sent to the SQL server.
I'm not sure if this is a realistic solution, and it obviously has some problems. If two web visitors are performing updates on the same data, the one finishing up last will have their changes overwriting the first ones. There's also no way of knowing you have the latest data (i.e. if a customer pulls som info on their projects and we update this info while they are viewing them, they won't see these changes PLUS the above overwriting issue will arise).
The other solution would be to somehow aggregate database calls and make sure they only happen when you need them, e.g. during data updates but not during data views. But then again the longer a pause between these refreshing DB calls, the bigger a chance that the data view is out of date as per the problem described above.
Any input on the above or some fresh ideas would be most welcome.
