I'd like some advice. My problem is there are a pair of audit log tables (AuditLog and AuditLogDetail) that now take up about 80% of the space on the database. This has made transferring backups to support really slow, we need to transfer the database so support can look at it and enhancements can be developed against with real data, but we don;t need the AuditLog data for this. The plan is to create another database AuditLogArchive on the same instance as the main database that has these two tables.
There are a few options I've thought of:
Option 1. Keep the system as it is with the AuditLog and AuditLogDetails table in the main database but also add these tables to the new AuditLogArchive database. Then create two agent jobs: Job1 will copy data in small batches across from the main database to the AuditLogArchive database. Job2 will delete in small batches the data on the main database's AuditLog tables that are more than 2 weeks old. That way we will have a small amount of AuditLog info on the original database and a full copy on the AuditLogArchive database.
Option2: Move the table in their entirety to the new database and drop the tables on the main database and change the application so it inserts the AuditLog tables on the new database. I think this is a slightly weaker solution because if something goes wrong with the AuditLogArchive database then the whole system will break, so there would be two links that could break.
Option3: Same as option2 but instead of changing the application just create a view on the old database of the table on the new database, that way the new table can be inserted and selected from using the view.
Option4: Leave the tables as they are but have a trigger that also inserts the data into the new database, then have a job that deletes rows from the main database but not the new archive database.
Option5: Use replication to copy the data from the original database to the new database, create a job to delete older rows on the old database but not to replicate these deletes to the new database.
Has anyone got some advice on which way to proceed or other ideas?