03-20-2013 02:49 AM
Our current Act database has been in use since 1999 and thus has a lot of history in it. We would like to be able to archive old items to a separate database, keeping only the last 5 years in our current database.
Act gives you the posibility to remove old items, so that takes care of cleaning the current database. But we'd like to have a copy where only the items older than 5 years are in.
After that we would like to be able to move 1 year of data over to the archive database each year.
How would you suggest we do this?
03-20-2013 03:36 AM
03-20-2013 03:55 AM
ADF file is 1.5GB. SQL Server Management Studio says the largest table is the History table with nearly a million records.
We've got about 13000 Primary contacts (and another 7000 secondary), where our heaviest users have about 20 000 history items under their record.
Logging in under one of those users takes quite a bit longer than logging in under a user with less items. (Logging in under my record takes about 12 seconds, under one with 20000 history items takes up to 2 minutes on a bad day.).
Our attachment folder is currently at 550 000 files and that amount is rising exponentially (along with the amount of new history items being made every year). (60 000 files/year in 2009, 90 000 files/year in 2013).
The fact that Act dumps all of those files in a single directory isn't helping much either.
We've had to all but disable universal search from indexing our database+attachments or our server became unuseable.
We've also had to set a few parameters in the act config files to keep the client on all of our computers from refreshing the attachment directory every time someone attached a mail or file to a record. (30 clients refreshing a directory with 550 000 files generates a lot of network traffic).
So basically as I said above, the amount of data in our database is rising exponentially every year. While we might have things under control for now, we would like to get on top of it before it becomes too bad.
That and there would certainly be a performance improvement.
The reason we would like the archive database to only contain the years not in the main database is to prevent confusion.
03-20-2013 04:05 AM
03-20-2013 05:59 AM
I7 2600 3.4GHz
2x 128GB Crucial M4 SSD in Raid 1
Windows Server 2008 R2
I7 2600 3.4GHz
1x 64GB Crucial M4 SSD
Windows 7 X64
Hardware isn't really the problem in my honest opinion.
I don't really see a reason not to do the archiving. We asked our users to keep track of how often they had to look at history items older than 5 years. It came down to barely once or twice a week over 30 users. We just want to do the archiving right.
Making a current copy of the database and keeping that as archive isn't really that clean since you've got at least 5 years of data in 2 locations. So i stick to my original question: is there any way, be it using an addon or separate tool to make an archive database containing only the stuff we're removing from our main database.
03-20-2013 06:24 AM
11-28-2013 01:47 AM
Mike - we have a similar problem with a large server-hosted database, far too many large attachments in one folder and problems with the issuing of new remote databases. With regard to your comment about calling back in a couple of months, I just wondered whether anything had developed?