[dba-SQLServer] SQL Server security

Dan,

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

Yep, and yep.  Actually AFAICT the preliminary import of Access into SQL Server gives me a SQL 
Server database file that is slightly smaller than the compacted access be file.

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

Yes, but it won't run on Windows 2000.  SQL Server 2005 will.

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

Yep, one of many issues with Access as the sizes get larger.  I am hoping that SQL Server express 
will solve these for me.

John W. Colby
www.ColbyConsulting.com

On 1/5/2011 1:17 PM, Dan Waters wrote:
> 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?
>