Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2010
    Posts
    3

    Unanswered: Suggestions needed

    In brief, I designed a "global" MSSQL 2008 database for the company consisting of a failover cluster with a mirrored database between Germany and the US. Works like a champ! The application intended for use with the global database backend is a German application that evidently works great with a local database, but if the db is located anywhere else (i.e. over a WAN), a certain portion of the application takes as long as 15 minutes to load an xls, convert it to an xml file and compare it against the database. During setup of this global database, testing indicated load times of 60-85 seconds, which we considered ok for the application, given the location of the db and latency. Anyway, now the vendor suggests a local database, and with planned rollouts to Italy and China, what method can be suggested to me that allows me to synchronize all of the local databases worldwide so that data will be uniform everywhere? How do the big global companies do it?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'd suspect that the application is processing too much data on the client side, when that logic should be located on the database server. This means that a large amount of data must be transferred locally in order to perform the calculations, rather than simply transferring the results directly.

    You'd be better off if the database was employed as a true "server", and this business logic was handled within the DB, but you probably cannot change the vendor's application or architecture. I'd suggest you push back on them, and let them know that their current design is not scalable, and ask whether they are willing to support a client of your size. If not, look at taking your business elsewhere.

    As a possible solution, you could look at implementing database replication.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Use a Groaci nose-flute? If you don't get that reference, I'm appalled at your choice of names!

    If you can change the schema, then I would strongly suggest using Peer-to-Peer Transactional Replication. It works wonderfully when the schema supports it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jul 2010
    Posts
    3
    Indeed soft one!

    Would database replication work for me, given that I will end up with 4 or 5 local databases? The vendor will not readily cooperate, saying the problem is all mine, but the company has too much invested to dump them at the moment.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Can you alter the schema to add a GUID primary key to every table that you need to replicate? There are other solutions, but this is very low impact/quick/easy if you can do it.

    If you do that, you can then create N databases on 1 to N servers at 1 to N locations and use merge replication to "ferry rows" from one database to another behind the scenes. You have to watch out for two different databases changing the same row (table/PK) such as London deleting a row that Mumbai just updated, but you can define conflict resolution rules to cope with the vast majority of those problems.

    Because of your relative inexperience with replication options and the complexity involved in dealing with an outside vendor, I'd really suggest that you contract out this decision. Bringing in an expert will allow you to "open the kimono" and allow them to pick the best option for your circumstances. While that will cost you a bit up front (possibly several thousand dollars in the worst-case), I think that getting an expert opinion will actually save your firm money in the long run.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jul 2010
    Posts
    3
    Indeed. Seems like quite complex solution well beyond my current skill set. Are there any other options? Is the new SQL Azure rolled out by MS a viable option?

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The issue isn't that the technology is beyond you in general, but making an architecture decision is beyond your exposure at this point in time.

    I have the feeling that you've got a very good grip on the technology in general, but there are so many choices that can drastically affect the cost and security of the long term solution and many of those are things that I wouldn't recommend discussing in a public forum.

    Once you have an architecture outlined, I'm pretty comfortable that you can implement it nicely. There will be a learning curve, and it might make you sweat a bit, but it won't seriously tax you.

    Azure is likely to suffer the same performance problems you application is already experiencing. The symptoms that you've described are common with applications that are constructed with a client-centric design. This is a common performance problem associated with developers that think/design/code locally instead of globally.

    Because this discussion has moved from the area of "database design" squarely into the area of "Microsoft SQL Server", I'm going to move the thread into the appropriate forum.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Pat Phelan View Post
    Can you alter the schema to add a GUID primary key to every table that you need to replicate?
    Replication will do that on its own.

    Only Laumer I ever read was the Bolo series (Courier?), in which Retief does make an appearance but I don't recall any references to nose flutes.
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You of all people will appreciate the Retief stories! Imagine James Bond in a universe populated largely by burecrats with a few really nasty adversaries and a small minority of folks like you and I willing to assist.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah, I got the picture from the one Bolo-related Retief story. But really, I liked the other Bolo stories better.

    I've never been that much into James Bond movies, and Retief seemed pretty formulaic and cliche'.

    Giant fighting cybernetic tanks, however....now that's cool.
    If it's not practically useful, then it's practically useless.

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

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Pat Phelan View Post
    Imagine James Bond in a universe populated largely by burecrats with a few really nasty adversaries and a small minority of folks like you and I willing to assist.
    I'm more of the evil nemesis type. Though I would gladly cooperate in the dispatching of bureaucrats.
    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
  •