[dba-VB] SQL Server security

jwcolby jwcolby at colbyconsulting.com
Wed Jan 5 12:00:15 CST 2011


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



More information about the dba-VB mailing list