Loading huge MS Access DB through slow WAN

[fs483]

Hello,

I have a situation where one my customer has a relatively large database that needs to be loaded at a remote site but I don't know what's the most efficient way to do it. The access database is around 100 megs so far and will increase. The remote site will be linked to the Internet through a satellite link. Now the application that loads the database will be installed on each workstation. The data is needed to generate 3d models. Now if everytime the application opens (for each user), 100 or more MB of data will need to travel over a slow satellite link (64Kb), it's going to take a long time. The database can and will be modified by both local users and remote users. What's the best way to keep both databases synchronized ?

Thanks
anthony

 

[Dmasch]

Get a Terminal server at the main office and just setup the remote site to log into it via RDP. This way the database never gets moved back and forth, it stays on the server. This will be your best bet.

 

[fs483]

We will be using Citrix Presentation Server instead of RDP. However the software will be installed on the local workstations at the remote site. Those applications need USB loopbacks to unlock the software. Once unlocked it needs to access the DB. The DB will only be accessible through Citrix. I don't think the local application can retreive the data from the ICA. The software cannot be published on the Citrix Server either (I think). We will need some tests to confirm. I think it's a licensing issue also...

 

[GeorgeOu]

You should not be using Access on anything but the LAN. If you want to use the WAN, you need to "upsize" it to SQL server. You can convert the data backend to point to the SQL server and use all the same forms as before. The queries will be converted to "stored procedures" that run on the server side, thus eliminating massive bandwidth requirements.

The best way to do this is to use MS Access ADP (advanced data project). The ADP files are a single file that can be easily deployed to the clients, and you don't even need to set up ODBC System DSNs on the client machines. Using ADP or using a MDP front-end with SQL Server backend is much more robust. I've used this type of setup on 21.1 kbps modem connections and it works fine and it's even faster than using a remote MDB file on a T1 line. Having the data on SQL server is also a lot more reliable. MDP files are extremely prone to database corruption when it's used on the LAN or if it gets above 10 MBs.



George Ou
Network Systems Architect

Get more powerful articles and tools from my webpage

http://www.lanarchitect.net

 

[fs483]

Hi GeorgeOu,

I'll check if it's possible but we are not even using MS Access. The special 3D software (front end) uses an MS Access DB and the software itself loads up the DB. If I were to switched to SQL, I don't know how would the front end software connect to the SQL DB.

Anthony

 

[GeorgeOu]

Any respectable front end should be able to "upsize" to a SQL server backend. As a matter of fact, the front end should not know the difference when you do it because it is all seamless. If the front end must point to an MS Access DB, then you would simply keep it that way. Take the MS Access DB and upsize it to SQL server. Your customized frontend will still point to the MDB file and it wouldn't know the difference accept the speed will dramatically increase. The MDB file would in turn point to the ODBC link to the SQL Server. If you don't want to pay for the SQL server license, you can even use the free MYSQL. It will achieve the same thing.



George Ou
Network Systems Architect

Get more powerful articles and tools from my webpage

http://www.lanarchitect.net

 

[fs483]

Ok, got some more info. The software is called GEMCOM at

http://www.gemcomsoftware.com

. It's a software used in the mining business to plot exploration drills in 3D and other amazing things. For the licensing issue, they use FlexLM. I've used it a long time ago in my University days and remember having a lot of problems... Hopefully it's easier to install/run now. I just checked the DB today and it has grown to 300MB already and these are only EXPLORATION drills, we haven't even begun building the mine and how to extract the gold. Reading from the site, they do mention something about SQL but unfortunately for a software that costs $$$$ they have very little info. I'll have to call them up and see what they suggest. Currently we have a single user license using a USB dongle to unlock the software. With multi-user license, they use the FlexLM to manage the license... I wonder if that software will play nice with Citrix Presentation Server 3.0...

 

[GeorgeOu]

First of all, Citrix will usually play nice with anything if you can tweak the application to run in a multi-user environment. For some applications that are poorly written, I've had to manually write some scripts to copy files to the user's profile on the Citrix server to get the app to work. In theory, if they use a USB dongle to limit the number of licenses, that may not prevent multiple Citrix sessions from using the same USB dongle.

As for your assessment on cost, this is very typical of these types of applications due to the lack of economy of scale.

Back to the topic of using a SQL backend, like I said, it shouldn't matter. The application probably points to a static MDB access file. If you simply took that MDB file and "upsized" it to SQL server or used a free MYSQL backend through an ODBC connection, your application would not know the difference. The change is transparent because all the tables would now simply be pointers with the same name that in reality point to the SQL server. Also keep in mind, MS Access MDB files are notorious for data corruption once they get above 100 MBs.


George

George Ou
Network Systems Architect

Get more powerful articles and tools from my webpage

http://www.lanarchitect.net

 

[fs483]

How would I "upsize" my MS Access DB to SQL ? Is there a tool from MS Access to do that ?

 

[GeorgeOu]

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q237980

Here are instructions for using their simple upsizing wizard. Note that you should back the MDB file up before you try anything so you can go back if it does not work.

Note that I don't give any warrantee that will work. If you don't do it right and loose all of your data, I take no responsibility!



George Ou
Network Systems Architect

Get more powerful articles and tools from my webpage

http://www.lanarchitect.net