November 14, 2018 at 9:10 am
Hello dear sqlservercentral community,
Our application has a javaEE structure where we are using Hibernate to map the entities into a realtional database (MSSQL 2012). Over the Jboss Webserver clients are able to retrieve data. Currently we use a single database on which production data and historical data are stored. Among the data there are many2one and many2many relations.
About our Application:
We are developing software for the industry and commerce (Some sort of an erp-system).
E.g usecase: There is a warehouse which holds a lot of products or bins (bins can have products). If a bin is requested by a client, than a machine is going to take this requested bin out of the warehouse and place it on a production line where it will be send to the were spending, where a driver will take it and bring it to the client. The route which the bin takes from the warehouse to the deliverer over the production line is stored as historical data. This data is actually only needed to verify the way of the bin. (e.g. customer ordered bin x, and tells us that he didnt recieve bin x, than we can check the historical data (tracking data) and tell excaclty, that robot y took bin x and brought it to the driver y, or this data is used for analytical purpose). Anyway, this data is requested very rarly and should be archived.
My goal is to write an archivetool, which archives the historical data which are created in real time. Entities will have a date where they should be put automated into the archive and a date where they will be automated deleted out of the archive. In addition the archive process should not slow down the productiv system and i do need to take care of many2many and many2one relations. Besides that the productive data and the historical data have to be retrieveable by the clients. (e.g a client can request historical data, productiv data or a combination out of productiv and historical data)
In my research i found these following archive strategies
- Archiving with partitions (Partition 1:current data, Partition 2: Historical Data
- Additional archiv tables which holds the historical data
- Additional archiv database
I would love to here your oppinion about these archival strategies listed above, if these are suited for my problem or even useable to fix my problem and which you would reccomend me to implement.
Thank you in advance
December 18, 2018 at 5:17 am
Hello, I just wanted to let people know that i went with the partitioning strategy and it worked well. All requirements has been matched and we could also see a good performance increase! If there are questions, dont hesitate to write me a pm. Regards
[CLOSED]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply