Community
Showing results for 
Search instead for 
Do you mean 
Reply

[Sage Act Premium 2013] Moving data to Archive Database.

Copper Contributor
Posts: 150
Country: Belgium

[Sage Act Premium 2013] Moving data to Archive Database.

Hello,

 

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?

 

 

Platinum Elite Contributor
Posts: 14,388
Country: Australia

Re: [Sage Act Premium 2013] Moving data to Archive Database.

ACT! doesn't have an archive function ...

You could create a copy of the database and mark all users (other than the Admin) as browse.

Then clear out the old History..

Next year, merge the live database into the archive one (to add the year's new data)

But, a question - how big is the ADF file? Do you really need to do this?
Copper Contributor
Posts: 150
Country: Belgium

Re: [Sage Act Premium 2013] Moving data to Archive Database.

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.

 

Platinum Elite Contributor
Posts: 14,388
Country: Australia

Re: [Sage Act Premium 2013] Moving data to Archive Database.

The ADF is supported up to 10GB (after that needs SQL Standard)

What server specs - os, ram, hdd speed (rpm), processor?

I might have a possible solution for the attachment issue soon.
Copper Contributor
Posts: 150
Country: Belgium

Re: [Sage Act Premium 2013] Moving data to Archive Database.

Server:

I7 2600 3.4GHz

8GB DDR3

2x 128GB Crucial M4 SSD in Raid 1

Windows Server 2008 R2

 

 

Clients:

I7 2600 3.4GHz

8GB DDR3

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.

 

 

 

Platinum Elite Contributor
Posts: 14,388
Country: Australia

Re: [Sage Act Premium 2013] Moving data to Archive Database.

Only the stuff you're removing?
You'd need all Contacts/Companies/Opportunities for the Hist to attach to.

So, my pref would be the way I described above and let the Archive DB have everything up-to the date last run - speed is less essential as it's not current.

If it's really important to only have what's removed (without the overlap),I haven't seen a commercial addon doing it ... but I could do it with a combination of itExport and itImport

BTW: I don't know if the server is dedicated to ACT! ... but SQL Express (if you're using that) can only use 1GB RAM and 1 processor.

Also, if you contact me in a couple of month, I might be able to tell you (or show you) a product that will resolve the attachment issue and give you search back :-)
Copper Contributor
Posts: 69
Country: UK

Re: [Sage Act Premium 2013] Moving data to Archive Database.

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?

 

Andy