| Author |
Message
|
| zpat |
 Posted: Tue Feb 10, 2009 3:07 am Post subject: Options for message archiving? |
 |
|
Jedi Council
Joined: 19 May 2001
Posts: 5866
Location: UK
|
I have a requirement to keep an archive of certain MQ messages (for regulatory reasons) up to 7 years.
The messages are typically 1kb in size and about 25,000 per day. I have MQ v6 and WMB v6.0.
My first thought was to warehouse them in WMB, but it would be a pain to manage the later movement from DB/2 to offline storage.
If I produce flat files on the SAN, the backup process will automatically move them to offline storage, so creating one file a day might work well.
How do other sites perform this sort of function?
Also what would be the suggestion on how to implement a daily queue (containing a copy of the messages) to file unload (platform AIX)? |
|
| Back to top |
|
 |
| vol |
| Posted: Tue Feb 10, 2009 10:43 pm Post subject: |
|
|
Acolyte
Joined: 01 Feb 2009
Posts: 69
|
25Mb a day = 8Gb a year = a rolling 56Gb for 7 years. Not really a lot of storage, Just add an exta disk to the machine to save them on, and don't bother about archiving.
An absurd and futile piece of management non-work.
Scott Adams wrote an amusing piece about this sort of activity. He said that his ideal job would be an archivist for a company, and he would just throw everything away because nobody ever asks to see anything in the archives. In the highly unlikely event that anybody did ask to see something, an event occurring at about the same interval as a solar eclipse visible from the company's head office, he would claim that the previous person in the job had lost it, that the hurricane/flood/power cut (insert disaster here) last year had wiped that particular disk. |
|
| Back to top |
|
|
| exerk |
| Posted: Wed Feb 11, 2009 12:06 am Post subject: |
|
|
Jedi Council
Joined: 02 Nov 2006
Posts: 6339
|
I try to follow the line that as the messages are generated by a business unit, the contents and responsibility for any archiving requirements belong to them; therefore if 'they' request that copies are archived, my usual direction is to give them the logs/copies to safeguard. I have used either the MO03 SupportPac, or have whoever is making the request do it with an application.
_________________
It's puzzling, I don't think I've ever seen anything quite like this before...and it's hard to soar like an eagle when you're surrounded by turkeys. |
|
| Back to top |
|
|
| zpat |
| Posted: Wed Feb 11, 2009 12:35 am Post subject: |
|
|
Jedi Council
Joined: 19 May 2001
Posts: 5866
Location: UK
|
There are valid reasons for the archive for non-repudiation with third-parties and the monetary values involved are extremely high.
Does anyone use MQsoftware's Statwatch for this sort of thing? |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Feb 11, 2009 1:13 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| zpat wrote: |
| There are valid reasons for the archive for non-repudiation with third-parties and the monetary values involved are extremely high. |
It's unusual (especially with high value transactions) for the non-repudiation to require the actual message logs, which are very hard to read back and tend to change format without notice (v5.3 - v6).
I'll therefore assume you mean you want a "plain text" copy of the message for audit purposes. Techniques I have used (or observed being used) are:
1) Copy via an exit (a la mirrorq) and the copy logged to a file/database by a homegrown app. Generally disliked by auditors as it prevents message integirity and non-repudiation (in audit not technical terms)
2) Log by receiving app as part of process to file/database.
3) Log by or via WMB to file/database.
A lot of this will depend on the agreement you have with 3rd parties and how you've implemented the non-repudiation / signature solution.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| zpat |
| Posted: Wed Feb 11, 2009 1:34 am Post subject: |
|
|
Jedi Council
Joined: 19 May 2001
Posts: 5866
Location: UK
|
I didn't mean MQ logs.
I will have a copy of the relevant messages on a queue. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Feb 11, 2009 5:07 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
you might as well just use qload on a regular interval.
But I'd still store them in a database instead of a queue, and make sure the DBAs know that they need to manage the archival of this database. They've got to have existing procedures for maintaining existing data under the same archival requirements. |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Feb 11, 2009 5:13 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| mqjeff wrote: |
| I'd still store them in a database instead of a queue, and make sure the DBAs know that they need to manage the archival of this database. They've got to have existing procedures for maintaining existing data under the same archival requirements. |
FWIW I've observed that people seem to take data more seriously (in terms of protection, retention and storage) when it's in a database. It's certainly (as my most worthy associate points out) going to be easier to add a new database to an existing preservation policy than arrange for a flat file to be backed up. Security of data (I'm guessing these high value transactions are sensitive) is also eaiser in a database scenario.
You'd also gain the benefits that, in the event of a dispute, finding the disputed transaction among some years of data will be easier with an SQL query; the auditors can also amuse themselves running endless reports against this accumulation via ODBC without bothering anyone or accidently deleting / overwriting the files.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| zpat |
| Posted: Wed Feb 11, 2009 5:43 am Post subject: |
|
|
Jedi Council
Joined: 19 May 2001
Posts: 5866
Location: UK
|
Certainly adding them to a database using WMB would make it easier to query them. But as Scott Adams says - it's rare to actually retrieve stuff.
I just have a concern over an ever-increasing database size causing a time-bomb.
A daily qload cron job may be adequate because the SAN policies for file backup are easy to set up.
Perhaps some enterpreneur could offer MQ message backup over the Internet  |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Feb 11, 2009 5:56 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| zpat wrote: |
| I just have a concern over an ever-increasing database size causing a time-bomb. |
Again, the DBAs likely already have this solved, in the same way that the backup admins can handle an ever-increasing file system
| zpat wrote: |
| Perhaps some enterpreneur could offer MQ message backup over the Internet |
Does qloadc not run over a client connection? |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Feb 11, 2009 5:57 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| zpat wrote: |
| Certainly adding them to a database using WMB would make it easier to query them. But as Scott Adams says - it's rare to actually retrieve stuff. |
| zpat wrote: |
| I just have a concern over an ever-increasing database size causing a time-bomb. |
It's easy to delete database rows based on insert date. Much easier (and auditable) than deleting a file (or files) based on create date.
| zpat wrote: |
| A daily qload cron job may be adequate because the SAN policies for file backup are easy to set up. |
I think the point being made is that database backups are already set up. And the DBAs will notice if their backup job stops running long before anyone will notice some funny WMQ job that no-one's quite sure what it's doing........
| zpat wrote: |
| Perhaps some enterpreneur could offer MQ message backup over the Internet |
I'll do it. Provided I can watch, via CCTV from a safe distance, the meeting when you tell your auditors you're sending all this sensitive information over the Internet to a storage facility you've been assured is robust and secure. I bet you'll get as far as "Internet" before they stop listening and start screaming at you.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| bruce2359 |
| Posted: Wed Feb 11, 2009 6:19 am Post subject: |
|
|
Poobah
Joined: 05 Jan 2008
Posts: 9469
Location: US: west coast, almost. Otherwise, enroute.
|
First, this is not an MQ issue; rather, it is an application issue.
Second, 7yr retention is why God invented magnetic tape.
Third, is your important data not being backed up now?! This is an application development and DBA issue to resolve with the usual backup tools and methodologies invented decades ago.
Fourth, iF there is ever a need to meet the requirement to find 7yr old data, then the app developers need to write apps to do so. If this involves restoring the data to a DB, then a restore app or DB utility is called for.
Last, disk is not a good medium for 7yr or 7day retention.
_________________
I like deadlines. I like to wave as they pass by.
ב''ה
Lex Orandi, Lex Credendi, Lex Vivendi. As we Worship, So we Believe, So we Live. |
|
| Back to top |
|
|
| zpat |
| Posted: Wed Feb 11, 2009 6:23 am Post subject: |
|
|
Jedi Council
Joined: 19 May 2001
Posts: 5866
Location: UK
|
I think the database might get too large before it reaches the expiry threshold.
Of course there are backups now and even a hot-standby DR site but this requirement is unusual due to a change in the way we are sending messages out (I can't really go into details here).
The application is not changing and I don't want to ask them to re-work it. |
|
| Back to top |
|
|
| bruce2359 |
| Posted: Wed Feb 11, 2009 6:31 am Post subject: |
|
|
Poobah
Joined: 05 Jan 2008
Posts: 9469
Location: US: west coast, almost. Otherwise, enroute.
|
| Quote: |
| I think the database might get too large before it reaches the expiry threshold |
.
Or the DB might get corrupted or suffer from a disk failure, or loss of the entire site due to fire, flood or famine. This is why there needs to be a frequent and regular DB backup to tape (a good definition of an archive).
_________________
I like deadlines. I like to wave as they pass by.
ב''ה
Lex Orandi, Lex Credendi, Lex Vivendi. As we Worship, So we Believe, So we Live. |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Feb 11, 2009 6:34 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| zpat wrote: |
| I think the database might get too large before it reaches the expiry threshold. |
Speak to your DBAs. I've seen some very large databases in my time.
Note also in this situation, response time is not as much of a factor as typically. Insert speed simply has to be as fast or faster than the enqueue rate as an average (because uninserted messages can sit on the queue), enquiry speed is irrelevant because it's unlikely anyone will ever access it & if they do, it'll be for a batch report.
Consider this - if the database is going to be too large to be held, what's your SAN going to look like? I'm guessing it's used for other things aside from these files that are not going to enjoy being squeezed for space.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
|
|
