Moving to MS SQL

[Sasha B]

Hi I am a Network Admin (not a programmer) who started creating a Company DB
programs with Access. I want to be proactive and understand why and when I
should consider moving from MS Access to MS SQL. Can somebody tell me what
limitations Access has? When I should be ready move my Access DB programms
to MS SQL?

Thank you in advance,

Alex

 

[Robert Morley]

There's a lot of different criteria that you could use to decide when to
move, but I think probably two things come to mind as being the most
important (though I'm sure others will add more and/or disagree...you know
how it goes <g>).

Easily the biggest thing is the question of how many users will be accessing
the database simultaneously. Access slows down tremendously when multiple
users are using a database at the same time, more with every user. If
you've got more than a handful of users, it's probably time to consider
migrating.

The second consideration is the size of your tables. The exact amount will
vary depending on the nature of your tables and you query-design skills, but
typically if you've got more than a few hundred thousand rows in any given
table (less if you're sharing this on a network), you should consider
migrating to SQL Server.

Along with the above, Access has a file size limit of 2 Gig (or at least it
did in MDB format; I'm not sure about the new ACCDB format). So if you're
approaching that size, even after running a "Compact & Repair", it's
definitely time to consider migration.


Rob

 

[Sasha B]

Thank you Robert.

There's a lot of different criteria that you could use to decide when to
move, but I think probably two things come to mind as being the most
important (though I'm sure others will add more and/or disagree...you know
how it goes <g>).

Easily the biggest thing is the question of how many users will be
accessing the database simultaneously. Access slows down tremendously
when multiple users are using a database at the same time, more with every
user. If you've got more than a handful of users, it's probably time to
consider migrating.

The second consideration is the size of your tables. The exact amount
will vary depending on the nature of your tables and you query-design
skills, but typically if you've got more than a few hundred thousand rows
in any given table (less if you're sharing this on a network), you should
consider migrating to SQL Server.

Along with the above, Access has a file size limit of 2 Gig (or at least
it did in MDB format; I'm not sure about the new ACCDB format). So if
you're approaching that size, even after running a "Compact & Repair",
it's definitely time to consider migration.


Rob

 

[Tom van Stiphout]

On Tue, 13 May 2008 12:51:54 -0400, Robert Morley

One additional major point is security: SQL Server can be much more
secure than an Access db. Workgroup security and database password
(the only options in Access) are toys compared with what SQL Server
can do. Workgroup security was dropped by MSFT in version 2007, most
likely because the company realized it was never very secure.

Another one is availability. Access databases corrupt. It may take a
while, but your day will come. At that time you better have a good
backup or else... SQL Server databases don't corrupt. They can get
messed up if for example you're putting the db on a single drive and
it develops a bad spot or a disk crash, but then you'll be looking for
another job for not putting it on semi-indestructible RAID5.
Availability also has other aspects, such as redundancy, database
farm, scale up, scale out, just to name a few keywords. Hardware costs
go up rapidly for those solutions though.

-Tom.

 

[a a r o n . k e m p f]

when I worked at MSN we had a strict policy against RAID5.

Personally-- I thnk that RAID5 should be reliable enough.

On the other hand-- www.tomshardware.com had an article once upon a
time about RAID6.

but most companies-- with a siliver spoon in hand (I'm referring to
MS) just use RAID 1 + 0.

-Aaron

 

[David W. Fenton]

Workgroup security was dropped by MSFT in version 2007

No it wasn't.

 

[a a r o n . k e m p f]

yes, it was.

Workgroup security is not present in version 2007.

-Aaron

 

[Tom van Stiphout]

On 15 May 2008 01:35:03 GMT, "David W. Fenton"

Well, I meant for the new accdb file format. If you are using mdb it
is indeed still supported.
-Tom.

 

[David W. Fenton]

Well, I meant for the new accdb file format. If you are using mdb
it is indeed still supported.

Well, please say what you mean -- there is an awful lot of
misinformation out there from people making blanket statements about
what A2K7 does not "support". I have to deal with them all the time
precisely because of that lack of specificity.

 

[a a r o n . k e m p f]

look buddy-- Access MDB is dead; it has been for a decade.

Anyone that is using it should be out of a job.

I'd stop running around jerking off to images of 'jet replication' and
'workgroup security'-- it's just not there; it's not usable, it's not
reliable.

And it's not in Access 2007.

-Aaron

 

[jsmaccready]

Pretty strong language. This is by no means settled and certainly not as you
suggest.

Consider the fullness of the following link and links from it.
http://www.sqlservercentral.com/Forums/Topic440468-131-1.aspx#bm502356
You must be a member - free - to read.

Bottom Line, MS said it was settled about 8 years ago, but has since
unsettled it.
That link will take you to LONG threads from last summer, last winter and
today.

I'm not saying or hinting that workgroup security in JET is preferable to SQL
security.

However, MDB/DAO being buried by ADP/ADO is a different story.
Advise to be careful and specific
---------------------------------------------------

look buddy-- Access MDB is dead; it has been for a decade.

Anyone that is using it should be out of a job.

I'd stop running around jerking off to images of 'jet replication' and
'workgroup security'-- it's just not there; it's not usable, it's not
reliable.

And it's not in Access 2007.

-Aaron

[quoted text clipped - 11 lines]

David W. Fenton                  http://www.dfenton.com/
usenet at dfenton dot com    http://www.dfenton.com/DFA/

 

[David W. Fenton]

"a a r o n . k e m p f @ g m a i l . c o m" <[email protected]>
wrote in
:

I'd stop running around jerking off to images of 'jet replication'
and 'workgroup security'-- it's just not there; it's not usable,
it's not reliable.

And it's not in Access 2007.

Yes, it is, you moron.

Why do you insist on posting things that are transparently false? It
makes you look even more idiotic than you already do with your
one-size-fits-all "SQL Server is the answer" posts.