Migrating backend to an Sql Server

[REZ]

Hi,

I need to migrate my backends to a cloud Sql Server to allow users to work from home.
I've been working on Access for quite a few years, but I'm completely lost with Sql servers.
Can anyone give me step by step instructions?
Thank you

 

[GPGeorge]

It might take a bit more than a single thread in a post in an online forum. You're talking about a major process.

Invest some time in online research. Here's a link to a really good overview.

 

[REZ]

Thank you.
I tried doing some online research a while ago, but got very confused.
I'll have a look at the link you sent me

 

[Pat Hartman]

A better solution is to use Citrix or Remote Desktop to allow your users to continue to use the Access application unchanged. Just converting the BE to SQL Server will not solve your problem. Access is NOT optimized to work well over the internet. It works very well on a LAN with SQL Server but the best internet connection provides less than 10% of the speed of the average LAN so a straight conversion will probably be way too slow for your users to be happy with it.

To make Access work with a WAN database, you will almost certainly need to rewrite the application to use bound forms and disconnected recordsets as well as stored procedures. Not something to be undertaken lightly.

Try the straight convert just to give yourself a feel for what you are up against. Then look into Citrix which requires NO changes to the application.

 

[CJ_London]

but the best internet connection provides less than 10% of the speed of the average LAN

think you are being a bit harsh - I've used linked tables to a sql azure backend with reasonable success - response times are good providing you do the usual things of basing forms on queries that return minimal records, don't use domain functions or UDF's, etc- and of course a decent network connection. Also been able to connect to a web based mySQL database, again with decent performance. Longer term, moving the heavy lifting to the BE also shows benefits. In both cases, performance not as good as terminal server/citrix.

Only rewriting required was some renaming of tables - and of course, relinking.

But don't disagree with your recommendation - if the objective is to enable people to work from home, then terminal server/citrix is an easier way to go. Alternatively consider VPN. One of my clients I support uses an access BE via VPN - every user connects via remote desktop when away from the office and I've found that has the same performance levels. But they have invested in decent resources.

@REZ - you've stated your objective - to enable users to work from home. Moving the BE to sql server is one potential solution but a lot depends on your budget, existing resources, the bandwidth of users home broadband and who is going to support it - not just the maintenance of the BE but the infrastructure the BE runs on.

 

[Pat Hartman]

think you are being a bit harsh

I think my experience has been disappointing because I am not using a corporate cloud. I've tried to do this by using cheap third party solutions where I buy a service from GoDaddy or some such company. I can create the BE on the server. I can link to it. But, using it is like watching paint dry.

All the people who have reported success, such as yourself and @KitaYama , have been using a corporate cloud rather than a third-party solution. I don't get the impression that the poster has a corporate cloud so I don't want to raise his expectations.

 

[CJ_London]

Yes I've used corporate cloud, but I've also spent several months using the cheapest azure (i.e. lowest resourced and hosted by MS) to get a feel for how it works. Budgeting for licensing can be a nightmare as you can opt for services along the lines of 'pay as you use' (my term). A sudden burst of activity either slows everyone down or if you have opted for 'pay as you use' additional resources are put into place (up to a limit set by yourself)- and then reduces when the activity reduces. What you use is broken down into various areas - type of server (HDD or SSD), amount of storage, level of network traffic, number of connections etc

 

[Auntiejack56]

I'm sure there are companies out there who cloud convert Access databases. The reason I am sure is that my company is one of them. Runs quick, not expensive. Speed depends on a ton of things.

 

[REZ]

Can you tell me more about your company? What exactly does it offer? do you have a website I can get more info?

 

[Auntiejack56]

I'm unsure about how promotional restrictions work in this forum; I've sent you a message with a few details.

 

[Pat Hartman]

Please send me a link also. Thanks