Question DB Replication

[Quist]

I am using MS Access in a corporate environment as a component database for a CAD system.
The DB (mdb-format) is located on a file server and accessed by the CAD system through an ODBC connection so no user but the administrators is actually using MS Access.
We now need to expand the CAD system into some other officies in other countries and when trying to point their ODBC connection to the existing DB-file the performance becomes unacceptable.
I therefore need to synchronize or replicate the database to local servers.
I started by trying an external sync but if the DB is edited from two sites at the same time data risk to get lost.
The best option therefore seems to be DB Replication from within MS Access.
We are on MS Access 2010 but after some reading I realize that MS Access 2013 don't have any support for replication and I want a "future proof solution".
Basically I have no experience for databases so I therefore would be thankfull for some suggestions on what my options are.

 

[HiTechCoach]

Access database replication was dropped in the new ACE (.accdb) database format that was introduces with Access 2007. The old JET (.mdb) format still supports replication but has reach end-of-life and is not longer updated.

Access does not play well over a WAN. this includes replication.

About future-proofing:
Unfortunately you have outgrown the Access database engine of JET (.mdb) and ACE (.accdb).

Moving into the future you should not use a .mdb. You should upsize to a true client server RBDBMS SQL Server.

Since you are using ODBC I would recommend that you switch to an SQL Server, You could use the free versions from Microsoft, mySQL, Oracle, etc. You may also find that it will work over fine WAN and not need replication.

 

[CJ_London]

you can create your own replication process for ACE but this is something for the experts - if you are not access/vba savvy then probably not for you and still at risk from unstable connections - you also need to consider timing - only one replica can be synchronised at a time.

Since the issue is speed across the network rather than volume of data, I suspect that simply using SQL server as a backend instead of access will not resolve your issue - you will still be pulling data across the network although you will have the benefit of server side processing and reduced risk from unstable connections. But a lot of this depends on how well your front end is written (both CAD system and admin system) and how easily you can adapt them to utilise server side processing.

I think HiTechCoaches solution of sql server express for each office server and synchronising them may be a solution, but you'll need to investigate whether the express version has all the functionality you require.

You could investigate boosting the connection speeds between your various offices by increasing WAN bandwidth which is basically why your db is performing badly.

The least painful way I am aware of which 'resolves' poor network speeds is to use terminal server or citrix - however both come with a cost. These host your access front end, CAD system and backend (so performance is basically 'server side' - like having them on your pc) and only transmit across the network the screen pixel changes, so very low data volumes.

An alternative which I've not yet tested is to use Azure which is a cloud based server - so far as I am aware you can connect your access front end to a backend located on Azure (SQL server) - it still comes with a cost, but based on usage rather than licensing. Benefit is each office utilises its own LAN broadband connection without having to go through the WAN. However you would need to investigate whether your CAD system can connect to a web based backend.

And finally SharePoint - I'm not a big fan myself, but worth investigating as a possible relatively easy migration of the backend and conversion of the frontend. You would need to investigate whether your CAD system can use Sharepoint.

 

[HiTechCoach]

I was actually not recommending any form of replication since SS Express does not support replication.


What I am recommending is migrating to SSE (SQL Server Express), This would still be a spared database. Depending on what eh CAD system plus from the database, Views in SSE may be a lot more efficient. Same for the Access front end.

It will take some testing and application tweaks to get the acceptable performance.

I have Access front ends that connect over slow WAN connections hitting SSE back end that have good performance. In some cases I have moved the database to a Cloud hosting server, like Azure or AWS, to take advantage of their extra bandwidth.

 

[Quist]

Thanks for good replies!
Why are MS dropping support for replication in Access??

I have looked at the Citrix option before but the CAD developer advises against it so it looks as if a migration to SQL is what I will have to do.
I agree with the consumption that it is WAN-performance (latensies) that 'kills' my initial setup so replication will probably be a must. It was therefore depressing to learn that MS SSE don't have support for replication.

I will also take a look at the Azure and SharePoint suggestions. I guess it all boils down to if it is possible in Windows to make an ODBC connection to a cloud based backend.

 

[CJ_London]

Why are MS dropping support for replication in Access??

they dropped it with the launch of 2007 - but the functionality was retained as backward compatibility with earlier versions which utilised it. So if you are using a 2003/.mdb backend you can still use it - it just isn't supported anymore. As to why? there are others who can give a more definitive answer but my understanding is it is because of the move towards www etc.

However I have a couple of clients who still use it - primarily because they need access to data when they are out in the field and there is no broadband connection or it is flaky at best - e.g. show events. But they sync on return to the office on a 'good' connection, not on a daily basis on a slow connection.

 

[CJ_London]

MS SSE don't have support for replication

From the blurb, it looks like SSE can be synced - but the master needs to be full blown sql server - see this link

https://msdn.microsoft.com/en-us/library/ff928676(v=sql.110).aspx

It may be you need to rethink how your processes need to work, rather than trying to use/adapt an existing process - for example how often are there changes to the data? Are the changes only required locally? If the WAN did not exist, how would you deal with the problem?