Sync Data Between Two SQL Servers

[Daniel]

Ok..here is my problem. I have two SQL servers that I need to have
identical, at least semi-real time data on. One is in a public DMZ
with full access to and from the internet, and one is behind a
corporate firewall and the box can get out to the internet but cannot
be hit directly from the internet in. The two are on seperate
networks and cannot be connected via lan. Is there a way for me to
have semi-real time data transfers between the two to keep both
servers identical? I have thought that the server behind the firewall
could initiate an XML session with my dmz sql server and process
updates, but I am unfamiliar with how it would know what has changed
on the remote server and how the remote server would know what has
changed on the server behind the corporate firewall. Any good ideas?

Thanks

Dan Hirsch

[Thomas]

Erland,

there's a tool (freeware) for data transfer only via T-SQL.
That's easy to deploy and execute via Query Analyzer, so no
connections between servers are required.

http://www.sqlscripter.com

Regards
Thomas

[Daniel]

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn*********************@127.0.0.1>...

Daniel (sp***********@yahoo.com) writes:

Ok..here is my problem. I have two SQL servers that I need to have
identical, at least semi-real time data on. One is in a public DMZ
with full access to and from the internet, and one is behind a
corporate firewall and the box can get out to the internet but cannot
be hit directly from the internet in. The two are on seperate
networks and cannot be connected via lan. Is there a way for me to
have semi-real time data transfers between the two to keep both
servers identical? I have thought that the server behind the firewall
could initiate an XML session with my dmz sql server and process
updates, but I am unfamiliar with how it would know what has changed
on the remote server and how the remote server would know what has
changed on the server behind the corporate firewall. Any good ideas?

The obvious tool to use for this scenario is merge replication. Merge
replication is not really for the faint of heart. You can guess yourself:
what if the saw row has been updated both on the DMZ machine and on
the firewalled machine? Well, at least merge replication is prepared to
deal with conflicts.

As for your particular scenario with the firewall, I don't have any
immediate ideas, but I think it should be possible one way or another.
As Fardude says there is a distributor that controls action.

In any case, my experience of merge replication is non-existent. I would
suggest that you drop into the newsgrup
microsoft.public.sqlserver.replication. You should get better help
there,

Thanks for both your help..I will repost in the replication newsgroup
as you suggested.

[Erland Sommarskog]

Thomas (po********@sqlscripter.com) writes:

there's a tool (freeware) for data transfer only via T-SQL.
That's easy to deploy and execute via Query Analyzer, so no
connections between servers are required.

http://www.sqlscripter.com

I know that. :-)

However, Daniel wanted something close to real-time, so I don't think
he wants something that you run from Query Analyzer.n

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp