1 2 Previous Next 16 Replies Latest reply on Dec 18, 2019 1:54 PM by Jason Miller

    Archiving doc

    Edison Pioneer
      Share This:

      Hi all,

       

      I am going to embark on archiving different forms and their corresponding humongous amount of records, along with their associations in all of our enviroments - PROD, QA and DEV.

       

      Planning to start with QA and DEV, and then take this over to PROD.

       

      Would anyone be kind enough to share a standard doc for archiving which I can use. I honestly believe many people here should have performed archiving in the past and might have documented all the procedure, so that the future consultant who works on the project knows the gory details.

       

      While I know some of the essentials which need to be mentioned on the doc such as archiving policy et al., any new insights or suggestions would be deeply appreciated.

       

      Thanks in advance for helping a chum out

       

      PS  - We are on ROD platform.

        • 2. Re: Archiving doc
          Edison Pioneer

          Thanks for taking interest Nikhil

          But I am looking for a doc more like a Solutions Design doc , which we create to detail out the work we have done. Not BMC doc.

          • 3. Re: Archiving doc
            Phillip Brockhaus

            When you say "archiving" - Do you mean moving the data to a different form on the same server, or do you mean moving them to a separate reporting server or archive server?

             

            (Or, for that matter, even though it's not really "archiving" - Do you mean just deleting the data?)

            2 of 2 people found this helpful
            • 4. Re: Archiving doc
              Sinisa Mikor

              It sounds to me like Edison is looking for hints about process or tools for documenting already undertaken customizations (which may or may not lack proper technical documentation).

              • 5. Re: Archiving doc
                Carl Wilson

                Hi Edison,

                Archiving is completely at the descression of the client, and usually based on company policies and governance.

                As a consultant I usually recommend only having a years worth of data in the main forms, for performance reasons, and archiving off data older than this where reporting can access the archived data.

                 

                Although you can have data older than a year, you need to be vigilant on indexing as the data grows, and performance expectations come into play.

                 

                Usually > 60k plus records in a table starts to be in the realm of needing indexes applied.  Although there are a number of "base" indexes missing OOB that can help with record counts less than 60k.

                 

                Certain forms in the system require regular purging e.g. AR System Email Messages, etc. due to the amount of records created, and with the later versions of AR there is the inbuilt Archiving Framework that allows you to set the days before the main system records are archived - which is configuration based and not customisation.

                 

                So, every organisation is different and with different methods of archiving (e.g. Archive or delete) it really is customer defined and there is "no one policy fits all customers" type of settings available.

                 

                That said, the new inbuilt Archiving Framework definitely goes a long way to helping with these configurations as it targets the main forms where performance is key. 

                 

                Cheers

                Carl

                2 of 2 people found this helpful
                • 6. Re: Archiving doc
                  Edison Pioneer

                  HI Carl,

                   

                  Thanks for such an elaborate answer.

                   

                  Would you please elucidate on >*"there are a number of "base" indexes missing OOB that can help with record counts less than 60k"*< since I was not able to understand it completely?

                   

                  Do you mean indexing is not really performed if records are lesser than 60k, or do you mean some simple/ default indexing algorithm does the search functionality, and once 60k records are crossed, we have to perform FTS?

                   

                  Also, after reading your answer, I started thinking about how indexing and archiving interplay with each other?

                   

                  Furthermore, suppose I have millions of records and half of them are archived. When I search for something on that records, do archived records get searched as well?

                   

                  I spoke with BMC and there are few forms like AR Systems Email Messages , NTE Notifier , AR System Historical License usage where records are in tens of millions. My client has advised me to straightaway purge/ permanently all of that data, and not necessarily bother archiving them. So I am writing custom escalations on Historical License Usage form now.

                  • 7. Re: Archiving doc
                    Edison Pioneer

                    Hi Carl,

                     

                    Forgot to ask this, but would you please tell me whats the impact of deleting the following records and if any other related pertinent forms require deletion?

                     

                    AR System Email Messages

                     

                    NTE:Notifier Log

                     

                    NTE:Notifier

                     

                    RBE:Transaction

                     

                    AR System Historical License Usage

                     

                    Actually, I see email forms have archive forms like AR System Email Association Archive, AR System Email Attachments Archive, AR System Email Error Logs Archive, AR System Email Error Messages Archive. Does that mean emails are already archived? Do I need to archive them, or should I just delete them?

                     

                    Same question for NTE:Notifier_Log_Archive

                    • 8. Re: Archiving doc
                      Edison Pioneer

                      HI Phillip

                       

                      Thanks for taking interest in this.

                       

                      I mean the former one - moving the data to a different form on the same server.

                       

                      Just so that you know, there are many forms which we are thinking of purging/ permanently deleting totally.

                       

                      Thanks again,

                      Ed

                      • 9. Re: Archiving doc
                        Edison Pioneer

                        Hi Sinisa

                         

                        Thanks for taking interest.

                         

                        You got me right actually, expect for the tiny part about "already undertaken customizations"

                         

                        I am undertaking archiving and I wanna document it properly so that future engineers are aware of all the changes made to the system

                        • 10. Re: Archiving doc
                          Carl Wilson

                          Hi,

                          if you look at the Archiving options (AR System Administration > AR System Archive Manager Console), you will see that some of the forms you mention are included in the policies which just need to be enabled.  These policies "generally" delete from the Primary form and copy to the Archive form.

                          You can change the policy on the form level, but you will have to overlay and customise the qualifications.

                           

                          The forms you mention all can be purged, but if there is some kind of requirement for data retention then you can use the Archiving Policies to store the data.

                           

                          Most forms have basic indexing applied, however based on how an organisation uses the data in these forms dictates what additional indexes are required.  This is where the performance tuning options come into play, and BMC do provide OOB ways to measure long running queries and optimize these accordingly.

                           

                          Cheers

                          Carl

                          3 of 3 people found this helpful
                          • 11. Re: Archiving doc
                            Phillip Brockhaus

                            For your initial one-time purge, if you have access to someone with moderate SQL skills, I would suggest deleting with an sql statement instead of using remedy workflow with a one-off escalation. Especially if we are talking about millions of records. There would be a vast difference in execution time and on server impact. Just make sure your where clause is perfect. Also, make sure you include the H tables and, if they exists, the B tables.

                             

                            For ongoing archiving, Carl Wilson pretty much nailed it. You can set up archiving policies for each form, and for each one you can choose to delete from primary and copy to archive, or to JUST delete from primary without copying it anywhere.

                             

                            If you really need to, you can create a union view in the database of the primary form and the archive form and then create a Remedy view form on top of that and then users who needed access to old data and fresh data at the same time could have it. Just make sure that this new form is used only for reporting and auditing and that all live work happens on the ordinary form. (If you do this, make sure to map "Original_Create_Date" and "Original_Request_ID" from the archive form to the create date column and the request ID column.)

                            • 12. Re: Archiving doc
                              LJ LongWing

                              I have a tool that I recommend using for deletes

                               

                              Delete Requests – A Programming Legacy

                               

                              It uses SQL Syntax to identify the records needing to be deleted, but it deletes them through the API....yes it causes filters to fire, but that's a plus in my mind, but it also automatically takes care of T, B, H, and S tables, as well as object relationships that might be in play as well.

                               

                              It's multi-threaded and does chunking for performance reasons (to not put too much load on the server).....

                               

                              In general, a better option than deleting directly at the DB.

                              2 of 2 people found this helpful
                              • 13. Re: Archiving doc
                                Jason Miller

                                I second this tool as a happy medium between direct SQL and using the api. I have been very impressed with performance of this tool and it had the added benefit of cleaning up B and H tables along with triggering any workflow that fires on delete (AR System Email Messages for example).

                                • 14. Re: Archiving doc
                                  Phillip Brockhaus

                                  I still think SQL delete bears consideration; especially if we are talking about records that are 5 years old in ar system email messages or something like that.

                                  1 2 Previous Next