Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2007
    Posts
    5

    Unanswered: Database automated part-archiving

    Hi all,
    i am a student doing my final year project and i have the duty to develop a automated part-archiving for our MsSql Server 2000 Database, but its not so simple .

    the environment:
    - MsSql Server 2000
    - Suns App-Server Glasfish
    - Java (J2EE)
    - Hibernate

    the requirements:
    1.)
    Some tables should be synchronized and redundant (the same data in both databases)

    2.)
    If the maindatabase is changing the schema the archivedatabse must do the same changes.

    3.)
    The datas in some tables (the ones that aren't synchronized) which are older then 2 Years should be moving (copy, paste) to the archivedatabase every 24 hours.

    4.)
    If there are is a access for a data which are not in the maindatabase, it is necassary to get it from the archivedatabase.


    I don't find a way to realise this ...
    can some one give me a hint?

    ... Sorry for my bad english, i hope i explained my problem good enough.

    Thanks for ur answers

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you have been sitting in class all semester and you have not a clue where to begin? Do you have any thoughts about how to do this?

    This message board thing works a lot better if you have a solution in mind and you want our opinions. it does not work so well if you want someone to do your homework for you.

    we have a team lead at my job that gets his\her work done by tasking it out to other people. everyone knows that person is worthless.

    gee, its the middle of April. I guess you have 2 or 3 weeks to knock this out. Good luck.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Apr 2007
    Posts
    5
    Offtopic:
    yes u are right, there are always people who want that others do there work and people who ask questions (most of the time these people are comming to me), wait for answers and didn't do a littlebit one her own.
    If u think i am worthless because i ask a question (for a not trival problem i think), it's fine but please don't spam the forum.
    And no, i still have 3-4 month.
    And yes, i have somethink in my mind, but i want to hear other opinions bevor i talk about it, so that it would not affect the answers.

    Back to topic:

    1 and 2)
    Maybe i will try to make replication for the schema and the synchronized tables or read out the meta-data and use them to synchronize the schema and the tables.

    3)
    A simple thread can read the old datas from the maindatabase and write them in the archive database. But this is not ACID save. So, i musst find a save way to log the actions and if something went wrong i must undo the work.

    4)
    Catch every query and check if the result is valid, if not query the archivdatabase.
    (But at witch point i should catch the query. At my company(yes i do my final year projekt in a company) we are using Hibernate and i don't know where i should catch the query, after hibernate i think its not possible, in hibernate ... i will not change the hibernate framework, befor hibernate it's not allowed because the application-code should not affect from my changes.



    I am thank full for all ur opinions.
    Last edited by JimKnopf; 04-16-07 at 10:58.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I never said you are worthless and if you did not launch a personal attack on me I would have helped you out today because things are a little light on my end today.

    I was simply helping you by telling you how to get a better response here and you did that, but since you do not appreciate my advice you can go some where else.

    (_X_)
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Apr 2007
    Posts
    5
    Hmm, i didn't mean to launch an attack at u.
    I am sorry if it looks like that.
    The truth is, that i think that u launched an attack at me and didn't want to help me .

    So, thanks for ur advice and a nice finishing time.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Good lord....

    In any case, you looking to automate Structure changes in SQL 2k?

    Are you sure it's not SQL 2k5?

    So you are asking for opinions, what have you come up with so far?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Jeez, I would never automate cascading schematic changes between databases. There is an all-important phase called "testing" which is impossible to include in automation. Was this thesis your idea, or was it assigned to you?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Apr 2007
    Posts
    5
    @blindman:
    It was assigned to me and wasn't my idear.
    The reason for this solution is, that they have some tables that are realy large and grow every Day. To tune-up the access time they want to split the tables in old and new datas. But they want that the work after this is nearly like they have only one Database. So if they make schematic changes to the database (maindatabase) it should cascade to the other database (archivdatabase).

    @Brett Kaiser
    2k5 is on the roadmap but at the moment it is still Sql-Server 2k.
    If it is only possible with 2k5 than there is no other way then using 2k5.
    I am still in the designing-phase.
    I think i should try to use transaktion-replikation to synchronize the tables which should be synchronized (like Employees or some think like this) but i still don't know if the table schema will be replikated too and if i could only replikate selected tables, that is somethink i will glean today.
    For the tables where the data shouldn't be synchronized (new data in the maindatabase, old data in the archivdatabase) i don't know how to cascade the schema from the tables. So i think i will write a program which willl read the meta-data form the tables (maindatabase) and update the tables in the archivdatabase. Then i have a delay between the struktur change in the main- and the update in the archivdatabase, but that shouldn't make a problem if i did this every time befor i move data from the maindb to the archivdb. In a thread i will move every 24hours (for example) data older then 2 years from the maindb to the archivdb but this is not transaktionsave (ACID) so i will log the status in a file (or maybe in the maindb) so that it will be possible to make somethink like a rollback if the transaktion fails (like a loose of power).

    Thanks 4 ur help and opinions
    Last edited by JimKnopf; 04-17-07 at 05:35.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    My buddy at work bought a zero-gravity pen last week. It was developed by NASA for astronauts to use in outer space. It contains a tiny CO2 cartridge that pressurizes the ink cylinder enabling it to write upside-down, or at any angle.
    When faced with the same challenge the Russians used a pencil.

    If you want to synchronize schematic changes between databases, then script all your modifications and run them on both databases. What you are endeavoring to do violates the KISS principle and my crystal ball tells me you are going to have more down time from process failures and corrupted data than it would take to to proper testing of modifications.

    Thus sayeth the blindman.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Apr 2007
    Posts
    5
    Thank u, i think i will do how u say and don't automate the schema synchronisation to "Keep It Simple, Stupid".

    For the synchronisation of the datas i will write a program, that will compensate
    the datas in the archivdb.

    For the moving of the datas i will write a program, that will looking if the data in the maindb should move to the archivdb. The problem is, how to find all the datas which are depending on the moving data, because they all must move to. But i think i can find it out with the Meta-Informations from the tables ... that shouldn't be a problem.


    Thank you for your help, opinion, and advice.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It is absolutely appropriate to automate the update and transfer of the data. It is just modifications to the schema which you should not be automating.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •