Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2007

    Unanswered: compare and synchronize SQL...recomendations?


    First post on the site, yippee! So first of all I'm not a DB pro but occasionally need to hire someone, and to do this properly I need to understand the basic workflow.

    Could really use some advice on comparing and synchronizing 2 different SQL databases in different geographical locations..... need updates from website A SQL database to be automatically updated to website B in as close to real time as possible. The whole database doesn't need to be transferred every time, only specific information regarding personal profiles and their activity on the other site. The end result would be that users can login to their profiles any time on either website and see up-to-date reports on their activities/results.

    I know it would be much easier to host both databases on the same server but for various reasons this is not an option.

    The past day was research into all the various SQL DB synch software. Having a hard time finding unbiased viewpoints...

    First of all is there any reliable and secure open source alternatives? I searched sourceforge and found 'SQL Server DB Compare and Synchronize', but it doesn't seem to have full automation.

    Also found a few paid for solutions through other forums such as 'Red Gate' products but they would cost upward of $3-500

    I need something that is secure and reliable, don't mind a bit of customization but would prefer a solution out of the box.

    any advice/articles/reading material/recommendations would be greatly appreciated

    have a nice day Derek

  2. #2
    Join Date
    Nov 2003
    Hi there,

    SQL server has various different functionality out of the box to update 2 databases with the same information, there is replication, log shipping and database mirroring (sql 2005 only).

    What version of SQL server do you have?

  3. #3
    Join Date
    Jul 2007
    Thanks for the reply...

    I'm no expert on SQL, have only done a couple days of research into this specific problem. At the moment my customer says that it's possible...still trying to find out the details on their servers.

    From what I understand this is easy to set up if the database servers are with the same ISP.

    Is this possible if there are 2 different servers in 2 different geographical locations. Server from one location only passes on select pieces of information to the other server in another location. And they want the transfer of information to be automatic? I'm drawing a blank

    Please any recommended links, reading material, OS programs, etc would be great.

    Thanks a bunch

  4. #4
    Join Date
    Dec 2006
    from what you describe SQL transactional relication sounds perfect for you. Its easy to set up, a wizard is supplied in the SQL GUI, and it comes for free with SQL. Do a search on the web on 'SQL transactional replication'.
    The service account SQLAgent runs under on the source server (known as publisher) just needs rights on the destination server.

  5. #5
    Join Date
    Mar 2007
    Geographical location should be irrelevant (i think) to the SQL Agent replication tool.

    Basically all it needs is a hostname/ip to connect to. So providing both servers are accessible externally from their localnet you should be fine. This may take some firewall configuration on both ends.

    Also bear in mind allowing unsecured information transactions across the net is insecure. I recommend trying to use SSL or encryption of some kind. I'm sure someone here can advise how best to go about this approach for SQL Server.

  6. #6
    Join Date
    Jan 2003
    Nottinghamshire, UK
    I'd seriously Consider Merge replication instead of transactional if theres any chance the pipe between the two boxes drops at any time. Merge recovers better & you can set it to run from every minute or every whenever.

    Consider if there are updates/Inserts on Both or just one box.
    Consider Full DB or Full Table or Filtered Table Synch
    Consider Replication Conflicts and how they should be resolved
    Consider who will provide 24/7 maintainence - it will one day error !
    where will the distribution service sit - Is it Push/Pull
    Consider security with leaving your SQL box open to the Internet?

    Replication can be set up easily enough but theres lots of options that are best considered through experience if you want a good & trouble free solution.

    Good Luck

    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

Posting Permissions

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