Performance and Size of Table, SQL Server

[EddieN1]

I have an "audit" table (inserts only) that is housed on my Access Backend. We're writing about 15,000 records per day to it. Two questions:

1. Will performance degrade as the table gets bigger?


2. Would moving the table to SQL Server 2008 R2 improve performance?

Thanks for your help, Eddie

[orange]

Is performance bad now, or is this a hypothetical question?
How big are these audit records?

[EddieN1]

It is hypothetical. I just implemented the audit table and watching it grow. The table has about 250 columns. Is this significant? Eddie

[orange]

Can you tell us more about the audit table/record layout?
250 columns is rare.

[EddieN1]

I'm not sure what else to tell you. Has only the Primary Key, no other indexes. When the operational table is changed, Access inserts a "Before" image of the record into the Audit table. I have about 125 users in the system. Almost all of the real data resides on SQL Server. I have the Audit table in the Access Backend because I can manipulate it better there. If I can get significant performance improvement by putting it on SQL Server, I might do that. In the meantime, if a table with over 250,000 rows would affect performance, I could keep only the most recent two weeks of audit trail. However, I would like to keep the history for about six months.

[orange]

If you use SQL Server as the backend, then transaction processing (beginTrans/EndTrans and possible Rollback should be almost automatic. Haven't done this with Sql server( and don't use sql server myself), but have done lots with ADABAS and Oracle in the past. I think, if you get some of the sql server people to respond, you will find a lot of your effort for this Audit log can be done in sql server by setting a few options.

I found this while typing ( dated but thedatabase server stuff is good)
http://cdn.ttgtmedia.com/searchDataM...oft_Office.pdf

[EddieN1]

I started to put the transaction logging in SQL Server with something like a Trigger. However, i couldn't save/access some information that is very important to me. I have about 15 different forms/datasheets that update various parts of the primary table. One I look at the audit trail and examine a change, I want to know what Form was used to make the change. SQL Server won't know that without A LOT of program changes on my part. I have already built the audit logic and it contains the Form that was used to make the change.

[orange]

When you design the various transactions, you can identify the transaction type. You can also identify what forms initiate the transactions and such. Irecall working with the transaction logs and identifying specific units of work by the various users. Users, transactions, before and after images, BeginTrans, CommitTrans etc.

If you know your system, and which Forms initiate which transaction(s), you could do a mapping of these. However, b ased on what you have said and where you are currently, it could be a lot of work.

Someone familiar and experienced with SQLServer may be able to advise you on the specifics.