[dba-SQLServer] SQL Server security

SQL Server 2005 Express has a capacity of 4 Gb.  I think I remember that 1.5
GB in Access might be larger in SQL Server - someone else probably knows.

SQL Server 2008 R2 Express has a capacity of 10 Gb.

Typing data into a memo field will lock one page of records, even if you're
using Access 2000+ and have set your mdb for Record-Level Locking.

Dan

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, January 05, 2011 12:00 PM
To: Sqlserver-Dba; VBA; Access Developers discussion and problem solving
Subject: [dba-SQLServer] SQL Server security

I am having performance issues in a largish Access application, a Disability
Insurance Claim call 
center app.

I have one particular table which is not huge in terms of field count but it
does have a lot of 
records and most of the fields are indexed, and it has about 800K records in
it.  This table holds 
"contact" info, as in phone calls that the users have.  They document every
"contact" with every 
one, claimants, doctors, lawyers, etc. into a memo field and also date of
call, ClaimID FK, employee 
id FK, contact type id FK etc.  Kind of a mini center of the universe for
this application.

The result is that people are storing new records in this table constantly
throughout the day and we 
are getting a lot of "record locked..." issues caused by (AFAICT) the time
it takes Jet to store the 
records and update all of the indexes, and probably the memo storage area of
the mdb.

Just to give a picture, this one table has been moved out to it's own mdb
and that mdb is about 700 
megabytes after a compact.  Most of the rest of the database (150 tables) is
in another mdb and 
after compact that database is 800 megabytes, so this one table is close to
as big as the rest of 
the db.

I do not have experience in a transactional database using SQL Server, but I
am thinking that SQL 
Server express 2005 will not have an issue keeping up with this kind of
usage - 25 users adding 
records to this table all day without causing locking issues like I am
seeing now.

My issue at this point is that they use a network logon and force the users
to change their password 
every 30 days.  Is SQL Server going to use that same network username /
password database or does it 
use a list of usernames / passwords physically on the server itself?  IOW
will Windows 
authentication work or will I need to go to SQL Server username / password?

-- 
John W. Colby
www.ColbyConsulting.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com