Splitting Database Users

Hi Experts,

We have an in place Access 2000/2003 FE Application that is currently linked to 50% SQL BE and the other 50% is still in Access BE.

Now management wants to split users to work in 2 locations, its a few blocks distance from one to the other.
What is the best way to organize that in terms of software development\architecture  regarding performance?

(Our IT guy claims that it would be best if database is on cloud and we access thru the web.., and he doesn't think that Access is the right product for that, however I am only familiar with Access/SQL programming)

What are my options?
LVL 6
bfuchsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kelvin SparksCommented:
A sql databse in cloud and local Access can work - depends how you've built things. If the two locations are on the same network and network speed is OK, then you may not have to do anything.

This question doesn't really provide enough detail in terms of your database(s) design, network setup and so on.

Kelvin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
> If the two locations are on the same network and network speed is OK,
> then you may not have to do anything.

True.

But why not move the Access backend to the SQL Server?

/gustav
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
(Our IT guy claims that it would be best if database is on cloud and we access thru the web.
All IT people think this, since it's the only thing they really understand. They're hardware junkies, and don't understand that not everything is suitable for a web interface.

I'd agree with gustav - move all the data to SQL Server, and you shouldn't have any issues. I'm not sure Azure is the best solution, if you stay with an Access FE, however. An Azure database is cloud-based, which means you're connecting through the internet, and internet connections are fluid - i.e. they can go down-up-down-up very quickly. Browsers handle that inherently. Access does not.
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

bfuchsAuthor Commented:
Hi Experts,

Out IT guy claims that its not quite possible to have one network for such a distance...maybe you guys can tell me what is the limit in distance that can be wired under one network?

Re moving all tables to SQL, I know this should have  be done already long time ago, however there were constantly more urgent things to take care off, and this got always pushed off..

However now that this moving users issue came up suddenly..I'm not sure if I can do the conversion from Access to SQL which involves changing allot of code in front end as well, before that splitting takes place.

In addition I would like to know, how exactly will that work after I manage to convert all tables to SQL?

FYI- Currently we already have some users in that other location and they connect to the server using remote desktop connection, and server is set as terminal server, however he claims that this setup was good for a small quantity of users, but when moving dozens of users that will not be adequate.

Re Azure I'm not familiar with it, is it like a new software that would require a extensive learning curve & allot of programming adjustments to our app, or will it be just like upgrading to a new Access version?

Thanks,
Ben
0
PatHartmanCommented:
If your queries join the Access tables to the SQL Server tables, you already have a performance hit.  it is best to move the Access tables to SQL Server.

If you can run Citrix, that is the best solution to support remote users.  It won't require any changes to the app and it is very fast.  Remote users frequently have better performance than local users because the FE/BE are on the same "box" and there is no network latency.  If you don't have enough users to justify your own Citrix server, then you can purchase a hosted solution on a per seat basis.  But using the third-party solution would mean that you can't have any local users.  All users would need to use Citrix since the BE would be on the Citrix server.

Another possible solution would be to create an Access web hybrid.  This requires Office 2013 AND the latest version of SharePoint.  If you aren't currently running these, then this solution would be the most expensive.  This solution would require converting all the tables to Azure.  Access would then link to Azure.  I personally didn't have much success with this since my experience was way too slow to be useful.  People who report good results are invariably hosting SharePoint/Azure on their own servers rather than relying on a third-party.  I'm guessing the difference is tuning.  The hosting companies I tried simply didn't understand how to tune the Azure database to work well with Access.
0
Kelvin SparksCommented:
Let's not beat around the bush here. Access is a thick client. An FE built using the standard wizards will suffer if connected to a BE in the cloud. As Scott has said, all data MUST be in the cloud, any 50:50 solution will die if you try and deploy it.

The Access FE must be designed to use cloud data -redesign anything that is bound to tables - you don't want to be delivering thousands of records over the internet. If you use binding, bind only to the record you are using.

I have a SQL database with Access front ends (accde's) and installed on runtimes up to 2,000 km apart and all connected to a SQL Server database I host on a shared platform with 10 million plus records. It performs not too much more slowly than a local copy of the database. It can be done, BUT your FE must be designed with that in mind. If yours hasn't, then you'll be very disappointed with the outcome and should push back to IT in terms of that solution until you can provide a suitable solution.

I've used Citrix and virtual machines. Both have their place (and cost). You may need to look at them short term, but really need to look at the application itself. If you saw the need for change some time back and didn't act, then I doubt that EE can really bail you out (yes I know, constraints at work may be at play).

Kelvin
0
Gustav BrockCIOCommented:
>  its not quite possible to have one network for such a distance.

It's not the distance but the bandwidth that matters. If you have a 10+ Mbit/s fiber, it may work with the attached access database and definitely will if you connect to a common SQL Server at either location.

/gustav
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Out IT guy claims that its not quite possible to have one network for such a distance
Your company should consider firing your IT guy, since he's obviously not very bright (or good at his job).
however he claims that this setup was good for a small quantity of users, but when moving dozens of users that will not be adequate.
I have several clients who run hundreds of users in an RDS environment. Again, might be time to find a new IT guy. This one is obviously not interested in helping the company to succeed, but rather to do what is easiest for him.

As the others have mentioned - it might be time for a sit-down with the powers that be and explain all this to them, and suggest your own course of action outside of the IT department. From my read of your provided details, it would seem a conversion to SQL Server, with all databases connected to a common SQL database, would be the right way forward. Anything else is a stop-gap measure, at best. If the database is valuable to the company, then it would seem they'd want to do thing the right way, and not the IT-easy way.
0
PatHartmanCommented:
The Access FE must be designed to use cloud data -redesign anything that is bound to tables - you don't want to be delivering thousands of records over the internet. If you use binding, bind only to the record you are using.
This would be best practice even for local non-ACE BE's.  You can only benefit from linking to a server based RDBMS if you use criteria to limit the rows returned.  So forms should use queries with criteria rather than the old-style Access method which uses filters for forms.  The vast majority of my forms show only a single record although subforms show all related records.  Given the nature of the app, they would be limited to at most a few hundred rows if the client has been with us for many years.
0
bfuchsAuthor Commented:
Hi Experts,

First of all, I would like to thank you for your time & opinions provided here.

At the moment our manager is leaving for vacation, planning to return in about 10 days, I will definitely present him with all these options, especially those that are not in my area of expertise (Like those network issues/solutions), and let him decide which route to take..

In Addition, If some of you have any additional information or similar experience to share that can be useful in order to make the decision, please post it here.

I will keep you posted upon his return

Thanks,
Ben
0
BitsqueezerCommented:
Hi Ben,

Currently we already have some users in that other location and they connect to the server using remote desktop connection, and server is set as terminal server, however he claims that this setup was good for a small quantity of users, but when moving dozens of users that will not be adequate.

for your current situation, this is already the best solution. It's simply wrong that "dozens of users" will be problematic, it depends only on the server which works as terminal server. It should have enough memory and network bandwidth, it should have enough licences to work with the wanted number of users, that's all.
If you have at least Windows 2008 R2 server the frontend on the terminal server can run as RemoteApp (which is new since 2008 server) so that the user on the other location will get an installation file which installs a variant of remote desktop. This one only starts the wanted application on the user's desktop as remote application, looks to the user like he has started a local application. That means, you only need the remote resources for this application, not a complete remote desktop, on this way you can run more users with this application without loading a complete remote desktop.
As Access needs a 100% connection this solution works even with a bad Internet connection, also with slow connections, as all is executed on the remote server, and a connection loss doesn't matter, simply reconnect and go on working.
We use this solution here for an external location which has a very slow Internet connection with only 1 MBit for around 100 people. Now currently around 35 of them are working at the same time using RemoteApp Access frontend and they have no problems. This model is so successful that we tested it between Germany and US and there was also no performance problem. We now deploy the frontend for around 400 users, all working with the same server around the world and it's faster than the desktop installation most of them had before.

Of course you should consider to move the Access backend to SQL Server to 100% but this can be done later if you have other things to do - with RemoteApp you can simply deploy the solution as it is built now without any design changes and you can upsize the application later when you have time to redesign it.

Cheers,

Christian
0
bfuchsAuthor Commented:
Hi Bitsqueezer,

(Good to hear from you every once in a while at least..-:)

Thanks for sharing your experience, I'm keeping all those opinions for the upcoming meeting, just waiting for our managers return.
The more people assuring that this setup would work well even after the planned split, the better off I am in terms of convincing the manager to take this path.

Thanks,
Ben
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
A few things to be aware of prior to your meeting, since I'm sure the IT dept will bring these up:

Setting up a RDS system will require some investment. You'll need a machine capable of running the RDS system, and that machine will require a Microsoft Server OS (RDS comes with the Server OS).

You'll need Windows and RDS CAL licensing for that server. You may already have this in place, of course, but be aware that RDS licenses are separate from standard Windows Licenses. In other words, having sufficient Server CALs does not mean you have sufficient RDS CALs. You purchase those separately.

You're running Office 2000/2003, but check to be sure that the OS you end up running on the RDS system will work with those older versions of Office (they should, but RDS and Office has always had quirks).

If you move up to a newer version of Office, and you're using an RDS environment, you'll have to use the "Network" version of Office to comply with MSFT licensing: http://www.microsoft.com/licensing/about-licensing/briefs/remote-desktop-services.aspx
0
bfuchsAuthor Commented:
@Scott,

Thanks for bringing all these up.
We currently have already close to a dozen users connecting remotely to the server thru RDS, I guess all the licensing issues you're mentioning had to be done already for those in place, its just a matter of adding additional licenses as qty of users expands.

FYI- Re Office version, we have 2003 SP3 for all remote users (and doesn't look like we are planning to change any time soon, unless we're being forced to), and Windows Version is Windows Server2008 R2, the SQL version is 2008.

The manager is due to return any day and guess meeting will take place the end of this week or perhaps beginning of next week, will keep you all informed as thing develop..

Thanks,
Ben
0
bfuchsAuthor Commented:
Hi Experts,

At the moment our meeting was pushed off, and its unknown to me when we will resume, therefore I think there is no point to leave this question open, I guess when the meeting will take place I will post again & insert a link here  to the new question, so you will know how this will end up.

Meanwhile I got to hear all your expert advise and will save them for when the time will be appropriate.

Thanks very much to all participants!

Ben
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.