1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

SQL Replication - Windows/Linux/MySQL/SQL Server

Discussion in 'MySQL' started by mikelbeck, Mar 10, 2008.

  1. #1
    I'm setting up a bunch of servers that will be located in various locations around the world. All of them will have a local database server, and that will communicate back to the main database server. Updates can occur at any point, and any updates will need to be replicated out to all of the other servers.

    I can get servers with Windows 2003 Standard/SQL Server Standard, Windows 2003 Standard/MySQL 5, or Linux (Red Hat or CentOS)/MySQL 5. I know that SQL Server can be configured for multiple replication partners and is pretty reliable. I haven't done any replication with MySQL on Windows or Linux.

    Has anybody else done this?
    Are there any limits on the number of replication partners with MySQL?
    How is the performance?
    How is the reliability?
    Does it support multiple masters or is it a single master and multiple slaves?
     
    mikelbeck, Mar 10, 2008 IP
  2. entwickler

    entwickler Member

    Messages:
    74
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    41
    #2
    I guess that if you can afford SQL Server go with it. It will be easier to configure. And SQL Server 2005 is with SP2 so it is patched enough.



    MCDBA
     
    entwickler, Mar 11, 2008 IP
  3. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #3
    Depending on your budget, and your expected usage, there is a variety of configurations you can go with.

    What sort of load are you expecting on each node? Also, what is going to be accessing the independent databases, web applications, desktop applications, etc.?
     
    jestep, Mar 11, 2008 IP
  4. mikelbeck

    mikelbeck Well-Known Member

    Messages:
    790
    Likes Received:
    19
    Best Answers:
    0
    Trophy Points:
    108
    #4
    The load will be relatively low and will be accessed by web applications.
     
    mikelbeck, Mar 11, 2008 IP
  5. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #5
    Since it is primarily for web usage, I think that I would recommend a sort of wagon wheel setup. You would have a central master database or cluster (for reliability). The central master database replicates to each slave node. Any time you need to read from the database you read from the individual nodes, and any time you need to insert or update, you query the master database, which then replicates the changes to the nodes. This will be the easiest way to keep the data consistent across all of the nodes. The other option would be to run multiple master/slave databases, but this can be really messy especially if the databases are a long distance away, or when you start getting a bunch of them.

    The wagon wheel idea would be fairly easy to setup with either MSSQL or MySQL. If most of your web servers are *nix based, then I would go with MySQL on Linux servers. Otherwise if the budget supports it, and you don't need to connect with a lot of *nix servers, go with MSSQL. MSSQL will be significantly easier to setup, but the licensing is really expensive, and integrating a linux server with MSSQL is hardly worth the effort.

    The key in this situation is to make sure the central database is very reliable, as the entire system relies on it. However, even if it crashed, the other databases would still be fully functional for querying, or could be used to restore the master database.

    Anyway, this is the way I would do it.
     
    jestep, Mar 12, 2008 IP
  6. mikelbeck

    mikelbeck Well-Known Member

    Messages:
    790
    Likes Received:
    19
    Best Answers:
    0
    Trophy Points:
    108
    #6
    That's an interesting idea... But I think that it won't work in this scenario.

    There will be a "master" database that will be centrally located in the US. There will be other databases scattered throughout the world, some on very slow links. The users who would be using these scattered databases will be local to the server it's running on, so the only time the slow link would be into play is when the user or database would have to go back to the master. The local users will be updating the local database, so I figured those databases would send their data back to the master. Updates could also occur at the master, so there would have to be two-way replication.

    One thing to note is that the data from the scattered databases wouldn't have to be sent back to the "master" in real time, that could occur on a schedule - once per day for example. Some - but not all of the data updated on the "master" may have to be sent to the other databases immediately.

    The web servers are going to be IIS, and in most cases the database server will run on the same machine as the web server. Since we're going the Microsoft route so far I'm kind of leaning towards going with MSSQL. Plus from what I've ready it's much easier to configure replication on SQL Server than it is on MySQL.
     
    mikelbeck, Mar 15, 2008 IP
  7. theweekendchef

    theweekendchef Peon

    Messages:
    69
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #7
    Since you are already using MS for everything else and Replication on SQL Server is so configurable and reliable I would go that route. I current manage over 200 SQL Servers that replicate every night in both directions. Stores replicate sales and corporate replicates changes to prices, products etc. All of it is scheduled through out the day depending on the links between the sites. I have a very easy job and little work to do. :) Just don't tell my boss.
     
    theweekendchef, Mar 18, 2008 IP
  8. mikelbeck

    mikelbeck Well-Known Member

    Messages:
    790
    Likes Received:
    19
    Best Answers:
    0
    Trophy Points:
    108
    #8
    Your secret is safe with me. And the few hundred million other people on the Internet who may see this thread. ;-)

    Thanks for the info.
     
    mikelbeck, Mar 18, 2008 IP