For replication gurus

Hi
I dont want to use built in replication mechanism in sql 2005.
Reason:
Two servers that need to be synchronised, 95% from A to B and 5% from B
to A, are located in two different cities and the internet connection
is not vey reliable.
Is there an alternative.
I was thinking of monitoring all changes by datestimetamp and then
prepare a huge composite sql statement for the changes and email this
text file to the other server where it would be executed. This would
be done once everyday.
If someone has a better solution i will really appreciate to know the
details.
Thanks in advance.
Newish
Hi
Well , have you looked into linked servers in the BOL?
"Newish" <ahussain3@.gmail.com> wrote in message
news:1163517504.861790.68990@.m73g2000cwd.googlegro ups.com...
> Hi
> I dont want to use built in replication mechanism in sql 2005.
> Reason:
> Two servers that need to be synchronised, 95% from A to B and 5% from B
> to A, are located in two different cities and the internet connection
> is not vey reliable.
> Is there an alternative.
> I was thinking of monitoring all changes by datestimetamp and then
> prepare a huge composite sql statement for the changes and email this
> text file to the other server where it would be executed. This would
> be done once everyday.
> If someone has a better solution i will really appreciate to know the
> details.
> Thanks in advance.
> Newish
>
|||This will work for updates and inserts, but what about your deletes?
If you are trying to merge data from two servers where the data is in the
same tables you will need to do some sort of auditing based on PK info. IE
an audit table for each table you are merging which contains the pk of the
row modified an indicator as to whether it is an update, insert or delete
and then use triggers to write to these audit tables. Then you need to be
able to handle the condition where a row is updated on one side and deleted
on the other, as well as PK collisions.
It gets complex very quickly. Merge replication is designed for scenarios
like this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Newish" <ahussain3@.gmail.com> wrote in message
news:1163517504.861790.68990@.m73g2000cwd.googlegro ups.com...
> Hi
> I dont want to use built in replication mechanism in sql 2005.
> Reason:
> Two servers that need to be synchronised, 95% from A to B and 5% from B
> to A, are located in two different cities and the internet connection
> is not vey reliable.
> Is there an alternative.
> I was thinking of monitoring all changes by datestimetamp and then
> prepare a huge composite sql statement for the changes and email this
> text file to the other server where it would be executed. This would
> be done once everyday.
> If someone has a better solution i will really appreciate to know the
> details.
> Thanks in advance.
> Newish
>
|||I agree with Hilary.
BTW merge replication is resilient to the connection being dropped, and you
can optimise the profile for this occasion.
I have replicated to ships where the satellite comms are repeatedly dropped
when there are large waves that obscure the transmitter/receiver, and there
can't be too many more demanding replication setups than this, yet the merge
synchronization proved robust.
Alternative solutions eg DataCompare are available, but you'd have to
program much of the functionality that comes for free with merge
replication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks for your responses.
Exactly what happens if the line drops. Is everything rolled back. Is
the merge transaction based.
Regards
Newish
Paul Ibison wrote:

> I agree with Hilary.
> BTW merge replication is resilient to the connection being dropped, and you
> can optimise the profile for this occasion.
> I have replicated to ships where the satellite comms are repeatedly dropped
> when there are large waves that obscure the transmitter/receiver, and there
> can't be too many more demanding replication setups than this, yet the merge
> synchronization proved robust.
> Alternative solutions eg DataCompare are available, but you'd have to
> program much of the functionality that comes for free with merge
> replication.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||The transaction for processing the current row will get rolled back, but the
unprocessed changes in the batch will get reapplied when the merge agent
next runs.
This'll change to encompass >1 row if you are using logical records.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .