Multi Site database considerations - suggestions please

[DaveSH]

I am posting this question in multiple forums due to the various issues involved. I am looking for any experiences and or suggestions to help me make decisions.

I currently have an Acess mdb for document control in our engineering group running at 2 different sites. One in PA and one in AZ.

Architecture:

-The 2 sites have NT networks and are now connected via T1
-We have a citrix server running at the AZ location to support the companies main business operating software. This could be used.

Goals

-I want to combine the tables of the 2 separate databases, and begin to run out of a common database.

-I would like to stick with access if I can.

-Primary administration will be done from PA.

Basic Info:

-The usage of this database is relatively low.
-It is only edited for a few minutes at a time usually by a handful of users.
-Another 10 or so users may acces it read only 15 minutes a day or so.
- Current size of front end is about 1 meg.
- Current size of back end is 800K in PA and 500K in AZ
- I estimate back end size to grow steadily at about 1-2 meg per year.

The main questions I have at this point are:

1 Where to store the Tables ?

2 THe database contains OLE links to CAD drawings The Cad drawings will be stored on networks at both sites. Should I / can I create some sort of local table to store these links.

3 What is the best method of combining the tables where an autonumber PK field is used as a foreign key in relationships without disrupting the relationship.

4 Should I run the database on the AZ server and utilize the Citrix server.
Dave
gallagherd@earthlink.net

 

[jpa69]

We run a 30mb Access MDE Client with a SQL server backend, we use citrix for remote sites who each have a 256k leased line for up to 10 users per site. Plus remote users dial in with RAS.

In our experience of M$Access has limitations as a network backend and eventually you are forced to use a proper client server model such as SQL Server. Not sure what triggers this, it can be the size of Db or the number of users. You will know when you have to do this as it will get very slow!

So to answer your questions;


"1 Where to store the Tables ?"

In one place on one server (perhaps with replication if you do go SQL server)

"2 THe database contains OLE links to CAD drawings The Cad drawings will be stored on networks at both sites. Should I / can I create some sort of local table to store these links. "

Store these in SQL Server or at least on one site. Consider implementing a publishing procedure, where people in your second site put an object for import into the DB. (we do this with photo's which are named in a convention of (Surname-Firstname-RefNum.jpg). We keep these out of SQL server at the moment because we are awating a server upgrade and need the disk space.


"3 What is the best method of combining the tables where an autonumber PK field is used as a foreign key in relationships without disrupting the relationship."

Don't use autonumber as the foreign key, too easy for it all to change in the event of corruption. It is better to use a number that can't change, i.e. that is hardcoded somewhere.



"4 Should I run the database on the AZ server and utilize the Citrix server"

Yes, or just Terminal services if it is adequate for your needs. But create some fault tolerence.

The key thing to consider is the nightmare situation of what would happen if you were hit by a bus. How would they understand what was where. Also your fault tolerence needs to be considered.

It sounds as if they need what I call "the reality chat" this is where you tell them that they have reached a computing stage where they have to get serious and spend some serious bucks migrating to a professional system"

Good Luck!