Configuration of Split DB (AccessXP)

Topic

New Reply

This is more of a configuration question but goes something like this. I have a split database that has to be used by our employees in both our Pennsylvania office and our New York office. Both offices are connected via T1 line. New York users can get access to Pennsylvania server and vice versa. When I put the back-end of DB on the Pennsylvania server, the New York users have problems. (I use UNC path names in front-ends). Problems include timing out and/or very slow performance. Do I make two back-ends and push one to each office each week when I do my weekly updates?
Are there some tricks to optimizing code in front-ends that makes things work faster? How would you configure such a system?

Some info: Front-ends about 120MB, Back-end about 100MB (contains tables only)

Reply | Quote

Replies

Are the front ends located on workstations or servers? Make sure your tables have good indexes. Also, when returning recordsets, try and limit the number of records being returned with criteria. This should improve performance greatly if you are using large recordsets. I have one multi-office application which I upsized to SQL server. This is not for the faint of heart, though. But it is something you might want to look into.

Reply | Quote

Thanks. Front-ends are located on workstations, mostly WindowsXP with OfficeXP. Tables are indexed but I am curious about what gets pulled to the user’s front end when they open a report based on a query. Is the recordset made on the backend and then just the results sent over the lines to the front-end or do entire tables come accross for processing on the user desktop?

Also, did SQL Server improve performance?

Thanks,

Reply | Quote

Yes, performance was improved with SQL server. I’m still monitoring it though and am currently researching replication.

I’m not sure about your first question. I’ll see if I can find out though.

Reply | Quote

To clarify things a bit – when you run a query in Access using .mdb tables, all the work is done in the front-end even if you are using tables linked to the backend. In other words, the Jet engine runs on your local workstation, so it has to pull each table you are working on over to the workstation. On a 100MBit LAN, no problem, but on a shared 1MBit pipe it takes a while, and if there’s a fair bit of traffic you start getting timeouts and other fun things.

SQL Server on the other hand does all of the processing on the server and simply sends the results across to the workstation. That’s generally true, even if you are using ODBC linked tables – the ODBC driver converts the Jet SQL into TSQL and passes it to SQL Server. If you want to get fancier and used stored procedures and so forth, you can use PassThru queries, or you can switch to an ADP arrangement. So Yes, SQL Server does improve performance, in some cases dramatically. If you already use it in other applications and have a DBA setup, go for it. If not, there is a fairly significant learning curve you will have to surmount in order to not expose data to things like the Slammer worm that struck 10 days or so ago.

Never the less, depending on the traffic on your T1, you may still not get adequate performance. In that case you might want to look at Windows Terminal Services or Citrix. We’ve found them to be pretty usable at DSL speeds, and acceptable at ISDN speeds.

Reply | Quote