Database Operations - New Feature Request
|
Logged in as: Guest
|
|
Users viewing this topic:
none
|
|
Login  | |
|
Database Operations - New Feature Request - 8.Aug.2008 5:53:55 PM
|
|
|
Tmueller
Posts: 40
Score: 0
Joined: 4.Jan.2007
Status: offline
|
The Payment Card Industry Data Security Standard Requirement 10.7 requires that "audit trail history be retained for at least one year."
Currently, the database operation jobs allow events to be moved after X days into a backup database. It also allows the oldest events in the backup database to be automatically deleted. With only two databases to work with, the databases can get so large that investigations cannot be realistically performed. Takes forever to open and do searches, etc.
I request the ability to create more databases, possibly create 4 databases or more databases and have the ability to do the following:
1. I would like my primary database to keep x number of days of events.
2. Events moved from primary to first backup database after x days.
3. Events moved from first backup database to second backup database after x days.
4. Events moved from second backup database to third backup database after x days.
5. Events deleted from third backup database after X days old.
This will allow me to keep the required one year of events and the databases staying at a realistic size that I can change to the secondary databases and work with the data.
|
|
|
|
|
RE: Database Operations - New Feature Request - 13.Aug.2008 9:20:17 AM
|
|
|
DrewE
Posts: 1246
Score: 0
Joined: 28.Apr.2008
From: Cary, NC
Status: offline
|
Use the following procedure to create backup databases for longer term storage and archiving:
OBJECTIVE: The objective is to use the 'Move' job to move events into a series of databases for archiving which can be eventually moved off the SQL Server and archived for long term storage. In order to do this you will have to periodically change the name of the backup database to create the follow-on database. However, in order to keep the database sizes from exceding the recommended maximum size of 15 GB (for the primary database - the backup databases can be as large as 30 - 50 GB), we must do the math first.
STEP 1 - Determine your database size restrictions:
1. Find out how much data you archive in one day (determine the number of days of data that are in your primary database, then take the size of the main database file (in GB) and divide it by the number of days) 2. Divide 15 GB by the above result. This will give you the number of days it will take to add 15 GB to your database. Round this value to the nearest week or month number of days.
STEP 2 - Create a maintenance job to move data.
1. Delete all maintenance job you have already configured.
2. Create one new 'Move' job:
- Move events older than X days (determined in Step 1)
- Use the "Move events to another database on the same SQL Server" specify the name according to the appropriate naming scheme (ex. 2008Q1 through 2008Q4 for Quarterly sized databases OR 200801 through 200812 for monthly sized databases).
STEP 3 - on a periodic basis, rename the databases: Depending on the backup database period you choose, change the database name in the job properties to the next database name in the series.
NOTE: Remember you have X number of days of events in the primary database. You must change the name of the backup database in the Move maintenance job on X number of days past the 1st day of the quarter or month. That way, you will have the backup database will have ALL the events for the quarter or month.
NOTES:
1. Make sure that you set the schedule interval to 1 day in the Edit Schedule options... link under Actions. This will keep a consistent number of days of data in the primary database and will allow the job to only have to move one day worth of data to backup daily.
2. Keep in mind that your Report Pack reports or your queries via the Events Browser can only report on the contents of one database. So, consider this when selecting the period that each backup database will contain.
Thank you!
_____________________________
Drew Easley
GFI Software
Talk Tech To Me (GFI Blog) – Follow Us (Twitter) - Watch Us (YouTube) - Join us (Facebook)
|
|
|
|
|
RE: Database Operations - New Feature Request - 13.Aug.2008 9:59:50 AM
|
|
|
Tmueller
Posts: 40
Score: 0
Joined: 4.Jan.2007
Status: offline
|
Thanks, but this is too complicated. It is too manual. I prefer an automated approach. I have automated SQL Server Maintenance jobs running daily to backup my primary and backup databases, so I cannot do your suggested procedure.
I guess I would be asking to much to have my requested process added to a future upgrade. It would also be nice to have the ability for the Report pack to query on the contents of muliple online databases added to future upgrade too.
|
|
|
|
|
RE: Database Operations - New Feature Request - 29.Jan.2009 3:41:59 PM
|
|
|
Tmueller
Posts: 40
Score: 0
Joined: 4.Jan.2007
Status: offline
|
Was wondering if my original request will be available in next build? I have a great need to be able to archive events for at least a year due to the PCI Data Security standards requirement. Currently, the primary and backup database functionality does not allow the archival of more than 75 days of logs. The database size becomes too hard to work with if you archive more then 14 days in the primary database and 60 to 75 days in the backup database.
|
|
|
|
|
RE: Database Operations - New Feature Request - 15.Apr.2009 2:38:27 PM
|
|
|
Tmueller
Posts: 40
Score: 0
Joined: 4.Jan.2007
Status: offline
|
I've never gotten a good answer on this issue. Is a database feature of this type going to be added some time in the future. The databases get so large you cannot work with them. You cannot claim to be PCI compliant until you have the ability to archive 1 year's of logs.
|
|
|
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
 Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts |
|
|
Printable Version
