Need Advise! Calculate in Access, or use Views in SQL Express? (1 Viewer)

Hi.

I am going to upscale my backend to an SQL Express server because of corruption that occurs due to the way our network is set up. It is not possible for me to change the network setup.

The backend is relatively small in size (5mb and growing). Its updated with the access frontend by 10-15 users (also growing). It has turned out to be an essential application in our company, and reliability is therefore very important. I need to get rid of this corruption source.

We all work within terminal server clients (citrix), and users tend to disconnect from the client rather than logging of, which in turn corrupts the access backend. I believe that SQL would stop this corruption source, but...



I was planning to write views in sql to replace many of the queries that exists in the frontend today, but i am having second thougts.

SQL Express is limited to one CPU (max 4 cores).
Wouldtn it be better to just "dump" the tabledata to the access frontend (db size only 5mb's), and do all calculation there? The frontend users have limitless CPU power (we can beef it up by adding another cpu), while the Sql server only has ONE?

This is essentially what happens today when the users work in Access front and backend.

Eventually I will probably need to "go pro", with full SQL features, but it is too expensive right now.

Does any one here have experience with this?

Will depend on a lot of factors you dont mention here.
Generally speaking though, replicating data is a bad idea and as such I would in 95% of all cases go with keepnig the data on the server and running queries there .

Which factors?
What do you mean by replicating data?

replicating, as in "dump" the tabledata to the access frontend

well 5 mb is nothing, what happens when it is 50 or 500 megs

Do your users do data manipulation, if so how to keep things in synch?

How complex does your application do things? highly involved calculations are something different from a default select.

Probably more factors to way in.

if your network is wireless, better to move to move the front end to TS as well (one copy for each user, can be runtime), regardless of what you backend is. Then if the user disconnects from TS, their front end will close as well.

You will probably also see a vast improvement in performance over what you have now - virtually equivalent to having the back end on your own machine.

I wouldn't worry too much about SQL Server express resources, don't forget these are only used when it is actually doing something - which unless you are churning large amounts of data (5mb is not large), isn't very often.

If your long term strategy is to upsize to sql server, then going via express is a good step to take

First to CJ: Both FrontEnd and BackEnd is on the terminal server.
The only reason for my wish to upsize is to stop the DB from corrupting when the uses disconnects (and then often the server reboots due to no active connetions). This aint good.

Is it wrong to upsize when the only reason being to avoid this network issue?

Now to namliam:
When I say "dump", I mean that I link the frontend to the SQL tables, not SQL views that restrict the records loaded to the frontend.
If I interpret the SQL correctly, that means that ALL data is included in the frontEnd calucaltions? Which I want.

This is how we use the database:
Customers has preferences, this change and gets updated/replaces. They also have different holdings that come and go. But the size of changed data are quite small.
Some holdings change, som customers are added, but the "core data" will probably still be the same in the futre (about 5mb's). Lets say it doubles to 10 within a few years. This is still a really small database, even in Access, and probably not a problem for the network.

In the future, we will include other kinds of data. A lot of data. This is not part of the DB today, and will be established in the SQL environment with new tables/views. This data is more based on historical data, comparison data etc.


But for the existing application and areas of use, I would like to keep the crosstab queries I use (that is not available in SQL), and I am quite happy with the performance, and will probably be happy for many years to come. The calculations are somewhat complex, (many sub-sub queries). The day this gets to slow, we need to move on to a more powerful system. The only reason for mye wish to upscale today, is because the database corrupts when a user disconnects by fault.

So what I would like is to continue "AS IS", but get rid of this reason for corruption, hence my wish to have an engine that does not allow the DB to be set inconsistent due to network issues.

Am I way of here?

Well you can upsize your tables without impacting your performance on the user end...
Yes sure go ahead. However pulling "all" data accross the network for any query /report/thing the user does is going to cause slowdown sooner rather than later.... Regardless of size

The point of making as much queries as possible on the server is to limit throughput over the network, eventually cuasing i/o on the user end. I/o KILLS performance.

First to CJ: Both FrontEnd and BackEnd is on the terminal server.

Click to expand...

That does not compute with this statement

SQL Express is limited to one CPU (max 4 cores).
Wouldtn it be better to just "dump" the tabledata to the access frontend (db size only 5mb's), and do all calculation there? The frontend users have limitless CPU power (we can beef it up by adding another cpu), while the Sql server only has ONE?

Click to expand...

The 'power' in this context is where the front end is - so if your front end is on terminal server, it will be using the 'power' from there, not the users CPU. That is why it is so fast compared with a user based front end and network backend - all you are transmitting down the network is updates to the screen image.

I'm also concerned you say 'Both FrontEnd and BackEnd is on the terminal server'. This implies all users are using the same front end - and that is quite possibly the reason for your corruption - each user should have their own front end - each user has their own userspace so it should be located there.

The only reason for my wish to upsize is to stop the DB from corrupting when the uses disconnects (and then often the server reboots due to no active connetions)

Click to expand...

I support a number of systems on terminal server (some going back 10 years) and have never had a problem - I don't know about server reboots, they usually happen at something like 3 in the morning and any user who happens to be on line is a) given a few minutes warning and b) properly closed out by the server if they don't do it themselves.

So upsizing is certainly an option, and as I said before, if you are going in that direction, why not. SQL Server may be better protected against corruption but you aught to be clear about what is causing the corruption otherwise you may spend a lot of time converting and not solving the problem

Although there isn't a crosstab in SQL Server there are options. There is now a pivot query here

http://www.mssqltips.com/sqlservertip/1019/crosstab-queries-using-pivot-in-sql-server/

and this link has ways it used to be done
http://www.mssqltips.com/sqlservertip/937/cross-tab-queries-with-sql-server-2000/

Hi.
The backend is in the "common" area. Hidden. Never moves.
Each user have their own frontend. I use the policy to copy the latest version of the frontend to the users desktop (still within TS).
So when they log on, they have "fresh" frontend.

The SQL server we are going to use, is located in the terminal environment, but SQLExpress "strangles" the CPU use to 4 cores.
However, msaccess is not strangled, and therefore I can ask our supplier to virtually add a few cpu cores to the terminal servers, if thats needed. At least this is what they told me.

About different servers:
It's possible that our TS supplier has a bad setup, i'm not sure. I dont know this setup particulary well.

It seems that we are on a few different "servers"/"computers". So when I log on, I come to the "best" server available. Probably a file server or something.

I have tested this, and found it to be true. I have logfile that records who I am, what I am doing, and which "computer/server" I am on. Its about 10 different computer/server names that frequent my logfile. It's random which computer/server I log on to. When I disconnect, then reconnect and do a change in the db, its not always the same computer/server name that ends up in my logfile, And often we get this corruption issue.

I suspect that the VBA code I use for logging database use is the trigger that causes the corruption. Its and old Allen Brown logfile code. I think the error handler is somewhat outdated, and it is not able to find the old session of the user, because the server/computername is not the same.

First I would like to take care of the network issue (sql), then obviousely I need to change the log.

I would doubt very much if corruption is anything to do with your network configuration, although using a flaky wireless won't help.

Change to a different backend by all means, but I think there is something else going on

gemma-the-husky said:

I would doubt very much if corruption is anything to do with your network configuration, although using a flaky wireless won't help.

Change to a different backend by all means, but I think there is something else going on

Click to expand...

This could be the best answer so far. I do not agree with this corruption argument at all.

Why SQL Server?

If you must change use MySQL.

Thank you for all your replies. I have now upsized to SQL, and the upsizing wizard revealed an error that might be the source of mye corruption issue.

The primary key in my log table had autonumber, indeks Yes (Duplicates ok) turned on.
SQL didnt accept this, obviousely. I changed this to no duplicates, and suspect mye corruption issue died with this change. Time will show.

The system is now ultraslow, so I'll keep the access backend activated for some time until I have rewritten queries into views.