How to upsize my LAN database to SQL server?

[munkee]

Hi all,

I have developed a couple of databases that are being used across our companies LAN. There are around 30 users at present with usually 3 - 5 concurrent users at any time (obviously there is the potential for 30 but this in reality never happens).

There have been a number of requests for the database to work off site, either when users are at home or when working away from our LAN on sites around the country. Now the issue with this is that I am pretty sure this LAN databse becomes a WAN when it is to be used offsite and basically grinds to a halt (or atleast as slow as can be imaginable).

I have read briefly about the ms access upsizing wizard for moving my data to a SQL server. I am pretty sure our company has these sql servers but I wanted to get some information from bytes as what exactly I should be asking my IT department for here.

I have seen a few brief tutorials talking about either storing just your data on the sql server and still using JET or converting my database completely to link directly into SQL. I am more than happy with load times etc over the current LAN so I assume JET engine "should" hold up with the added overheads when querying against a SQL server instead?

I am just looking for a bit more information and some recommendations . If any further info is required let me know. Ah I forgot to mention the database currently comprises of around 2k records (queries seem to be pretty damn instant against this also).

[pod]

Hi

I am pretty certain your IT dept will understand your needs right away... as a matter of fact, I just had one of my project database upsized from Access to SQL, and my IT dept was pleased that I asked for this, since SQL is more secure and better for backups than Access is... and they did it for me in a matter of a couple of minutes

[munkee]

Well after talkin with my IT department it seems we are in a bit of a fix. We dont run any terminal servers at the moment. They just remote connect to individual pc's to do tech support.

They have also been told by their superiors (i work for a multinational company with around 500k employees so there seems to always be red tape) that they want to keep "regular" users out of sql servers.

However my IT department can see the benefits of my request to use terminal server and they have come stuck a couple of times before where this could have solved issues so they have now gone away to try and get something sorted along those lines.

It is a start.. and I feel terminal is definitely the easiest option at present.

[MrDeej]

For your wan conserns you could use local .accdb or .mdb files on each computer, which are connected to a SQL server trough VPN or whatever.

When each user has his own copy off the file, and just the data is trafficed trough the WAN the speed issue is less of a bugger. We have been using Access->SQL trough a 2mbit internet connection at one plant connected to my company.

[munkee]

Just thought I would update this thread. Turns out our IT department are slightly out of their depth. We have no direct access to a SQL server I can use. I suggested looking into thin client technology such as terminal services. After a lot of waiting around they eventually came back with an answer which seemed to be "we are out of our depth". It has now been escalated further up the chain to see whether we can get a terminal server up and running..

Worrying really isnt it.

[NeoPa]

Although for the number of users you're talking about a SQL Server back-end makes sense, I don't see it as a requirement for enabling off-site access. Terminal Services is indeed a good solution for this, but assuming the connection is in to a box that is on the LAN, it will work quite happily with an Access back-end still (as happily as it already is that is).

Access is particularly poor at handling slow responding Access back-ends. I have always had to ensure that such Access back-end databases are local, or more accurately, give remote access to users to boxes where the front end is local (PC or LAN). I've always found this to avoid the particular issues of remote back-ends.

PS. Please be clear I am not here championing the use of Access back-ends. Simply dealing with the particular issue directly, which I don't believe is affected by such issues.

[munkee]

Im still pushing for us to be using terminal services. Not that I don't want to learn something new but I have heard of a lot of headaches being caused trying to upsize to SQL server (I've never done that before).

However it seems this is all out of my control now and awaiting regional IT to grant us the necessary hardware needed to make this a go. We are already using access to its capacity in my opinion now, not due to the size of the data being stored but the number of connections to the database.

One thing I have noticed when deploying your programs to a much larger audience (120 users peaking at 26 simultaneous connections last week) is how someone always finds a way to break what you thought was a bulletproof bit of code =p

I will post back with the outcome anyway.

[NeoPa]

Munkee:
One thing I have noticed when deploying your programs to a much larger audience (120 users peaking at 26 simultaneous connections last week) is how someone always finds a way to break what you thought was a bulletproof bit of code =p

On the plus side though, this gives unique opportunities for increasing the robustness of code that is already fairly robust. Learning such extra techniques will always stand you in good stead for future projects :-)

[TheSmileyCoder]

I have made applications for groups of 30-40 and for groups of 1-3 people.

To be honest I much prefer the larger groups, because they give much more feedback, and I learn much more. Both in terms of what not to do, but also quite brilliant suggestions on how to improve the program which I had not thought of myself.