Database Corruption and advice on upsizing

[Guest]

I was not sure where else to turn, and needed some advice. Our company is
now using a database I have built. Several gigs over several files with
anywhere from 5 to 30 users, split back-end and front-end. All machines
running Office XP and Windows XP. My computer as a shared resource acting as
the "server".

I am by no means an expert at all of this, but I know my way around Access
alright. Well we have started to have the occasional file corruption issues.
Not all that often. Mainly people shutting down machines incorrectly. We
do back-ups twice a day, everything automatically. I only have to use them
once every couple of months.

This database is used in every area of our organization. These down times
are becoming an issue and I have to work on a solution.

The easy answer is to upgrade to SQL Server (which we can do). Problem is I
know nothing about it or where to even start. I am looking for some advice
on what products to look at and where to get the information I need. I need
something that will handle database corruption issues, but don't need alot of
the network admin stuff. I just need to upsize this database to a program
that will limit corruption.

We will have some money to spend on this, but I don't need to waste any. I
need to find the right size program for our needs.

Any help would be appreciated. I realize this is a long post. I just need
some help to get started down the path of upsizing, as this is all new to me.

Thanks,
Robert Harris

 

[Sylvain Lafontaine]

Well, upsize to MSDE with linked tables, add the option dbSeeChanges here
and there (Access will tell you where you have forgotten to write them a
runtime by raising an error) and you should be done in less than a few hours
for free.

The upsized system will run slower, particularly with queries on joined
tables but the use of views should alleviate most of these problems
thereafter. Take a look at http://support.microsoft.com/kb/q209123/ if you
need to have updatable views.

S. L.

 

[Albert D. Kallal]

Without question, upsizing to sql server will eliminate the corruption
issues.

You have basically two approaches here:

1) re-write the application. This sounds like a very expensive idea, and I
don't think you need to

2) move the back end data files to sql server, and keep ms-access as the
front end. This is likely your best approach.

Amazing, right now low end database servers (capable of handling 20-150
users without breaking a sweat) are dirt cheap, and in fact free!!

You can get the free edition of sql server here, and this "express" version
is really nice (no more governed/throttle like the old MSDE had!!).

download:
http://lab.msdn.microsoft.com/express/sql/default.aspx

about:
http://www.microsoft.com/sql/express/

About the only problem you going to have here is that free edition does have
a file/data size limit of 4 gigs, and that might not be enough for you.
(however, I can assume that your large data files is due to "millions" of
records, and not that you did something stupid like storing images in the
database..right?). 4 gig is going to be a LOT of data.

We will have some money to spend on this, but I don't need to waste any.
I
need to find the right size program for our needs.

As mentioned, this database engine is free. And, thus you can move the data
to sql server, but you still have to write the user interface, and forms
etc. Thus, it would seem to me the idea here is to simply move the data part
(back end) to the free edition of sql server, and continue to use your front
end.

As for using sql server? Get a book or two...and start reading. Using sql
server is not very hard at all..and certainly has a MUCH smaller learning
curve then what it takes to learn ms-access!

http://support.microsoft.com/default.aspx?scid=kb;en-us;175619&Product=acc

ACC2000: "Access 2000 Upsizing Tools" White Paper Available in Download
Center
http://support.microsoft.com/?id=241743

ACC2002: "Access 2002 Upsizing Tools" White Paper Available in Download
Center
http://support.microsoft.com/?id=294407

ACC2000: Optimizing for Client/Server Performance (odbc)
http://support.microsoft.com/?id=208858

ACC: "Upsizing to Microsoft SQL Server" White Paper Available in Download
Center (a95, and a97)
http://support.microsoft.com/?id=175619

HOW TO: Convert an Access Database to SQL Server (a97,a2000)
http://support.microsoft.com/?id=237980

ACC: Choosing Database Tools White Paper Available in Download Cente

The Choose.exe file contains a document called "Choosing the Right Database
Tools" that discusses Microsoft's database products: Microsoft Access,
Microsoft FoxPro, Microsoft SQL Server, Microsoft Visual Basic, and Open
Database Connectivity (ODBC). Use this document to decide which database
tool is right for you.

http://support.microsoft.com/?id=128384

ACC: Tips for Optimizing Queries on Attached SQL Tables
http://support.microsoft.com/?id=99321

 

[david epsom dot com dot au]

....Take the small amount of money and get a SQL Server consultant
to check that you are automatically making backups and truncating
the log, and that your backup works if the server fails. You won't
get automatic roll-over and clustered servers in the free edition,
so you need to make sure all your systems are in place to handle
any failure.

(david)

 

[Brian Smith]

Without question, upsizing to sql server will eliminate the corruption
issues.

You have basically two approaches here:

1) re-write the application. This sounds like a very expensive idea, and I
don't think you need to

2) move the back end data files to sql server, and keep ms-access as the
front end. This is likely your best approach.

Amazing, right now low end database servers (capable of handling 20-150
users without breaking a sweat) are dirt cheap, and in fact free!!

You can get the free edition of sql server here, and this "express" version
is really nice (no more governed/throttle like the old MSDE had!!).

This sounds very interesting but one thing that I'm not clear about, even
after looking at the various documents you link to below, is whether or not
you need to be using .Net to be able to use SQL Express. One of the articles
mentions that you need Microsoft .Net Framework 2.0. The hosting service I'm
thinking of using supports ASP but not ASP.Net. Does this mean that I cannot
use SQL Express? I know very little about ASP and ASP.NET so hopefully that
wasn't a stupid question. If SQL Express can be run without having ASP.NET
support, do you happen to know how easy it would be to upgrade to SQL
Server, if the need ever arose.

Any insight you could provide on this would be greatly appreciated.

Thanks.

Brian

 

[Albert D. Kallal]

This sounds very interesting but one thing that I'm not clear about, even
after looking at the various documents you link to below, is whether or
not
you need to be using .Net to be able to use SQL Express.

No, the express edition is the replacement for the desktop edition of sql
server. You can use Excel, or just about any product that can connect to a
database server here.

One of the articles
mentions that you need Microsoft .Net Framework 2.0.

You likely need the above frame work installed on the computer that is
running the sql server. (you don't need this for each computer
connecting...but just the server). However, lots of software today needs the
..net framework instilled to run. Anything you write in the new version of VB
requites the .net framework to be installed. The frame work is just a
runtime set of files that you install on your computer. (in fact, it is
exactly the same idea as having ms-access installed on a computer, and THEN
you can simply "copy" your software to that computer and it runs!!. So,
think of the .net framework as ms-access!. Once you installed ms-access (or
..net framework), then you can simply copy a mdb (or .net program) to the
target machine..and it will run. That is all the .net framework is...a
runtime system. So, when you hear of "x-copy" development, that simply means
you can copy your program to the target machine, and it will run. (this is
great, as you don't need a installer anymore!!). So, many .net developers
are
now talking about how great x-copy development is, and you simply can copy
your program to the target computer..and it runs. Of course, us ms-access
developers have had this for 10+ years now!! (as long as ms-access was
installed, then you just sent the user a mde file..and they did NOT have
to go through a complex install process..but just "copy" it to their
computer).

So, sure, the sql express edition needs the .net framework installed.
However, that certainly has NOTHING to do with the fact that you are wiring
software in VB5, VB6, c++, or writing some software in ms-access. The
issues are un-related.

The hosting service I'm
thinking of using supports ASP but not ASP.Net

HUH? When on earth did we (you!!) start taking about web hosting, and using
a
service provider for your database system? (did I complete miss-read you
post!!!as my coffee cup drops to the floor and shatters into little
pieces!!!!)). Yikes, are we talking about a web based system here? I you
are talking about a web based system, then we don't need, or are going to
be
using ms-access here...(we are now in the wrong newsgroup...and talking
about something completely different here!!). For a web based system, you
are not
going to be using ms-access. And, since you would be re-writing your
application, then you can use whatever tools you want...but you sure as the
heck are not going to be using ms-access anymore!!

Does this mean that I cannot
use SQL Express?

You can use sql server, MSDE, or sql express. And, you can interchange them
with NO code changes to your application (assuming a odbc, or ado
connection...and that you use 100% sql server compatibility here). So,
assuming you (somehow) outgrow the sql express edition, then yes you can
seamlessly upgrade to the full version of sql server without having to
change
your code.

I know very little about ASP and ASP.NET so hopefully that
wasn't a stupid question.

No, that is not a dump question. The issue of needing the runtime (.net
framework) for sure is a legitimate question. However, your question about
"hosting" and a web provider was a surprise! Even if we know zero about
..net, and zero about sql server express, I have to hope we understand the
different between your company running its own server, and this server is
NOT
for web services, but internal use. So, what is not clear on your part is
that you have brought up the issue of a web provider, and a host
provider??? I was under the assuming that you were NOT talking about the
internet, and was assuming that you had your on internal computer that you
could place your ms-access back end data on. This data file might be a
simply mdb data file, or perhaps where you install sql server (or now the
free sql server "express" edition). However, at this point, none of this
has ANYTHING to do with the web, and more so nothing to do with a "hosting"
provider.

So, you can simply use the sql server express edition as your database
server..but we are not talking about a web hosted system, nor are we talking
about a hosting server. (or a co-location service here either).

If SQL Express can be run without having ASP.NET
support

Yes, asp .net assumes you are going to be running a web server. And, as I
mentioned, it is very possible I misunderstood your intentions here..and you
are in fact looking to move your application to a web based system. This
is not a bad idea...and at least you are thinking forward here.

, do you happen to know how easy it would be to upgrade to SQL
Server, if the need ever arose.

Zero changes......as sql server express provides 100% compatiabliry with sql
server.....

 

[Brian Smith]

HUH? When on earth did we (you!!) start taking about web hosting, and using
a
service provider for your database system? (did I complete miss-read you
post!!!as my coffee cup drops to the floor and shatters into little
pieces!!!!)). Yikes, are we talking about a web based system here? I you
are talking about a web based system, then we don't need, or are going to
be
using ms-access here...(we are now in the wrong newsgroup...and talking
about something completely different here!!). For a web based system, you
are not
going to be using ms-access. And, since you would be re-writing your
application, then you can use whatever tools you want...but you sure as the
heck are not going to be using ms-access anymore!!

Let me clarify a few things. I had asked about this in the Access Internet
NG but received no reply and probably got lazy here in explaining what I
want to do.

Anyway, what I want to do is setup an online survey which would contain
about 20 questions some of which would be open-ended. Potentially, anywhere
from 20,000 to 100,000 people will complete this survey over about a one
year period. The reason Microsoft Access comes into the question is that I
was going to use Access to store the data on the host's server. I realize
that I have to create the front-end using another tool. I'm very tempted to
use FrontPage as I think it will do I what I want. My big concern, which I
can't seem to find/get a decent answer on, is will Access be able to store
the data and handle the traffic? I have no idea of how many people will come
to the site at once or if Access even plays a role in this in a web
environment. Also, some people will have access to the data and this number
could be as high as 5,000. The goal is to give them access to just the data
that pertains to them.

I've described what I want to do to the web host service I'm currently using
and they claim that I will have no problems. Even so, I still have concerns
about concurrent users based on other Microsoft Access work I have done
(unfortunately, none of it web based).

If you have any insight or advice concerning what I'm trying to do I'd
greatly appreciate it.

Thanks.

Brian

 

[Albert D. Kallal]

Let me clarify a few things. I had asked about this in the Access Internet
NG but received no reply and probably got lazy here in explaining what I
want to do.

No problem at all!! As I mentioned, I was/is likely my fault for not
realizing what was being asked.

Since you are looking to use this with a web based system, then you can't
use ms-access forms, code etc, and in effect really are using very little of
ms-access. Since you are just talking about a data store, then the efforts
saved (or increased) by using sql server or a JET/mdb file is no much
different then using sql server. You don't really save much by using
ms-access, and can't really develop any of the web stuff with ms-access
anyway. So, the asp web page side stiff will not really be any different if
you use a mdb data store, or sql server. And, from a cost/hosting point of
view, there is generally no different in the ISP rates that they charge.

. Also, some people will have access to the data and this number
could be as high as 5,000. The goal is to give them access to just the
data
that pertains to them.

the number of 5000 don't matter, but what does matter the SIMULTANEOUS
number of people.

A data file of 500, or 100,000 records is a mdb/jet data file is very small,
and not a real problem for ms-access to handle.

However, if you expect a high traffic load on the server, or a large
number of people hitting the web site at the same time, then ms-access sis
not appropriate.

And, since your ISP seems to think using a mdb data store is ok..then likely
you don't expect a large number of users at the same time. (if you only
expacet to have 30 users on at the same time, then ms-access will
be fine..but if you expect higher loads....them ms-access would not
be the best choice..and you not saving work, time, money etc. by
using ms-access).

 

[david epsom dot com dot au]

from 20,000 to 100,000 people will complete this survey over about
240 people per day: 30 people per hour, peaking a 1 user (the web
server), with 10 connections. Yes, Access should be able to handle
that, but I'm surprised at this:

described what I want to do to the web host service I'm currently
and they claim that I will have no problems. Even so, I still hav

I guess they are in a better position to know than I am, because
we stopped using Access as a web back end years ago. Here were the
reasons:

Using Jet in an ISAAPI dll meant we had to stop the web server to
update the database structure. (it's not done exactly that way
any more, but you still have problems)

The database corrupted and brought down the site when heavily loaded:

We needed file access (i.e. local login) to the web server to
update or backup the database:

MS recommended against use of Jet with IIS (and in effect said
that use of Jet with IIS was not supported).


Windows is not thread safe when used with Jet and IIS, unless you
take specific precautions to ensure that you aren't multi-threaded
on both sides. This means that IIS works correctly when lightly
loaded, but may corrupt the database when heavily loaded (when
creating a new thread for a new database connection).

A really good web hosting service would be able to guide you
around these problems: is your provider that good?

Regarding SQL Express: Express is a new edition of SQL Server.
It has new features. The full version of SQL Server has not
caught up. If you stick to standard features (tables etc),
then you can use any version of SQL Server. Your Web host may
host SQL Server: I would expect you to pay more.

 

[Albert D. Kallal]

While there are many examples of web sites running with JET/mdb files.

I have to agree with David's recommending here. When you go all the way
to web based stuff, go with a database server......