Access versus SQLServer

D

dch3

When dealing stricktly with number of records, at what (general) threshold
should I start considering upsizing to SQLServer? We're looking at
implementing a scheduling database. Based on my initial thoughts on the
design, the number of records is going to grow faster than the other DB's
that I've worked.
 
C

Chris O'C via AccessMonster.com

Number of records doesn't matter unless you know the exact size of each
record and every record in the db is the same size. Doubtful you're in that
situation.

What matters is the size of the db file. The max is 2GB for Jet 4 (that's
Access 2K and newer) so if you're going to approach that, upsize to SQL
Server before you run out of space.

Chris
Microsoft MVP
 
D

dch3

Actually the max size of a MDB was in the back of my head. Given how critical
the data is and the typicall number of records returned per query, SQLServer
was more of a given.
 
A

Albert D. Kallal

There is no real rule here.

when you say upsize I assume you talking about the data part of your
application?

You can't build forms in sql server, so I assume you taking about up-sizing
the data (back end) part of your application from jet (mdb) to linked tables
to sql server.

I think a good time is when you find operations are too slow.

However, a well written application in ms-access usually will run faster
then one that uses sql server (we are assuming no network here).

And, thingss such as is the application multi-user, is a network involved
etc. can change this issue/time when you move your back end to sql server.

Do you plan to keep the front end in access, and just move the back end to
sql server?

Do you run a split application now? (this helps when the time times to
migrate the data to sql server).
 
D

dch3

Its going to be split. Some users will use the Front End, others will use an
ASP page to simply retrieve the data. There's some discussion about updating
the data via ASP.
 
J

John W. Vinson

Its going to be split. Some users will use the Front End, others will use an
ASP page to simply retrieve the data. There's some discussion about updating
the data via ASP.

Then you *really* want to store the data in SQL.
 
D

david

I suggest moving to SQL Server if the client can't handle 1 hour offline
(the odd long lunch) and restoration back to last nights backup.

Most people hit these usability limits before they hit limits on the
size of the database or the number of users.

I tried 50 million simple records in a 2GB MDB, and I thought that
was too many. 1 million might be OK, but see previous paragraph.

(david)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top