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.

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.



"Chris O'C via AccessMonster.com" wrote:

> 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
>
>
> dch3 wrote:
> >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.
>
> --
> Message posted via http://www.accessmonster.com
>
>

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).


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)

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.

"Albert D. Kallal" wrote:

> 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).
>
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> (E-Mail Removed)
>
>
>

On Mon, 18 Aug 2008 10:10:03 -0700, dch3 <(E-Mail Removed)>
wrote:

>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.
--

John W. Vinson [MVP]

My thought precisely

"John W. Vinson" wrote:

> On Mon, 18 Aug 2008 10:10:03 -0700, dch3 <(E-Mail Removed)>
> wrote:
>
> >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.
> --
>
> John W. Vinson [MVP]
>

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)

"dch3" <(E-Mail Removed)> wrote in message
news:C1ACD26E-9721-4065-ACDC-(E-Mail Removed)...
> 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.