compare and synchronize SQL...recomendations?

Hi,

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 :) DerekHi 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?|||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|||Hello,
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.|||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.|||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

GW