Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Sep 2007
    Posts
    4

    Question Unanswered: Database Design- Referencing multiple database

    Hi All,
    I am designing database where few of the master tables will reside in different database or in case different server. Scenario is
    Server "A" with Database "A" may host the "Accounts" table.
    Server "B" with Database "B" may host the "Product" table.
    I am designing database "Project" which will hosted in Server "A".
    My application requires this master tables [readonly access] as data inserted in my application refers this tables. Also there are reports to be generated which refer this tables.
    How do i design my database and sql queries?
    I am thinking of approach of having equivalent tables created in my database and writing service which keep tables in my database in sync. This will ensure good perfomance during transaction and reports as they will need to refer this table locally as opposed to different database or different server.

    Any thoughts on above approach?? or any better/standard way for such scenarios ?

    Thanks in Advance. Your inputs will be of great help.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    why multiple databases? It makes it harder to enforce referential integrity and it can cause other headaches. i just recently ran into an issue where i swapped out a database instead of doinng a restore and the application broke because database objects that reference another database actually store the database id instead of the name. it was easy to fix but I still had an hour of down time. thank god it was not a production server.
    “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
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by versatile_me
    Hi All,
    Hi.
    Quote Originally Posted by versatile_me
    Hi All,
    I am designing database where few of the master tables will reside in different database or in case different server.
    Really....
    Quote Originally Posted by versatile_me
    Scenario is
    Server "A" with Database "A" may host the "Accounts" table.
    Server "B" with Database "B" may host the "Product" table.
    I am designing database "Project" which will hosted in Server "A".
    Quote Originally Posted by versatile_me
    How do i design my database and sql queries?
    In a word: "poorly".
    Quote Originally Posted by versatile_me
    I am thinking of approach of having equivalent tables created in my database and writing service which keep tables in my database in sync. This will ensure good perfomance during transaction and reports as they will need to refer this table locally as opposed to different database or different server.
    Well, that plan certainly is........"creative"....
    Quote Originally Posted by versatile_me
    Any thoughts on above approach?? or any better/standard way for such scenarios ?
    I'd say, practically anything else would be better.
    Quote Originally Posted by versatile_me
    Thanks in Advance. Your inputs will be of great help.
    No problem.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Jul 2005
    Location
    New Zealand
    Posts
    61
    Ahh, the much-vaunted (but almost certainly mythical and definitely undocumented) 'unreplication' functionality of SQL Server!

    On a more serious note, if you really want to proceed down this route you will need to set up Linked Servers and enable the MSDTC service.

    I would implore you to think again.

    Lempster

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    That's certainly a novel approach - doomed but novel. Out of curiosity what advantages were you expecting from spreading your system across different servers? and what was your database(s) supposed to hold?

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How versatile

    but, um, how much experience do you have?

    Oh, I get it, you're in upper manglement
    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
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Ouch Brett...that'll likely leave a scar.

    I assumed from the description that between the lines there is a plain motivation. Seems to me that the distant (aka remote) databases are already in existance, and the O.P. has been given a task to pull some data in from other servers, and followed the logical path to the current planned implementation!

    Sheesh. How silly is it to have databases spread all 'round when you can just copy them all to one place and just keep them in synch. ?
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by versatile_me
    Hi All,
    I am designing database

    I didn't read that all

    I designing a housebuilding business

    I plan to have all of my nails located in DC, all of my wood in Oregon, all the fixtures in NY, and all of my roofing supplies in N'orleans (There all over the place and free)
    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.

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    sometimes i go to other sql boards after following a link and i am always surprised at the difference in the content from this place.
    Last edited by Thrasymachus; 09-28-07 at 16:42.
    “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.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    how so?

    too short
    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.

  11. #11
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    i think this board has more of a tendency to slam the OP.
    “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.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Nothing like the real world, eh
    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.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No soup for you, poster!
    If it's not practically useful, then it's practically useless.

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

  14. #14
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    There are scenarios where distributed databases are a good idea - but they don't come up very often (at least I've never met one). Must admit it would be quite interesting to work on one though. I think it's important to hear the reasoning behind his system and find out why he thinks it's so versatile.

  15. #15
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    I agree there, Mike. Sometimes you need to know the rationale behind the current design before dashing said design upon the jagged and bloody rocks of despair.

    If nothing else, it also allows one to more pointedly help (and/or taunt) the original poster's assumptions and reasoning.

    As I am sure you are aware, sometimes stuff just comes across initially so far off the wall it's hard to resist a good-natured jab in the ribs and perhaps a well-intentioned kick in the groin.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

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