Immediate Synchronization?

[Neil]

Is it possible in SQL Server to have replication happen immediately as
changes are made. That is, a change is made on server A, and that change is
automatically applied to server B, rather than the replication happening at
set intervals?

Thanks.

[Hilary Cotter]

Consider distributed transactions for this. You can also set your polling
interval on your log reader and distribution agent to 1s, and your latency
will be around 10-20s.

--
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
"Neil" <no****@nospam.net> wrote in message
news:8K*****************@newsread3.news.pas.earthl ink.net...

Is it possible in SQL Server to have replication happen immediately as
changes are made. That is, a change is made on server A, and that change is automatically applied to server B, rather than the replication happening at set intervals?

Thanks.

[Neil]

I'm using an Access 2000 front end which updates a lot of data through the
ODBC driver. Thus, I wouldn't be able to use distributed transactions in
every situation. Anything else that can immediately update one server from
the other?

Thanks.

"Hilary Cotter" <hi***********@gmail.com> wrote in message
news:e6**************@TK2MSFTNGP10.phx.gbl...

Consider distributed transactions for this. You can also set your polling
interval on your log reader and distribution agent to 1s, and your latency
will be around 10-20s.

--
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
"Neil" <no****@nospam.net> wrote in message
news:8K*****************@newsread3.news.pas.earthl ink.net...

Is it possible in SQL Server to have replication happen immediately as
changes are made. That is, a change is made on server A, and that change

is

automatically applied to server B, rather than the replication happening

at

set intervals?

Thanks.

[Hilary Cotter]

Distributed transactions are a coded solution where you do something like
this
begin trans
insert into server1.database1.dbo.table1 (charcol) values ('test')
insert into msaccessdb.table1 (charcol) values ('test')
if @@errornum<>0
rollback tran
else
commit tran

It can be done using MS DTC as well.
--
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
"Neil" <no****@nospam.net> wrote in message
news:Jg*****************@newsread2.news.pas.earthl ink.net...

I'm using an Access 2000 front end which updates a lot of data through the
ODBC driver. Thus, I wouldn't be able to use distributed transactions in
every situation. Anything else that can immediately update one server from
the other?

Thanks.

"Hilary Cotter" <hi***********@gmail.com> wrote in message
news:e6**************@TK2MSFTNGP10.phx.gbl...

Consider distributed transactions for this. You can also set your polling interval on your log reader and distribution agent to 1s, and your latency will be around 10-20s.

--
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
"Neil" <no****@nospam.net> wrote in message
news:8K*****************@newsread3.news.pas.earthl ink.net...

Is it possible in SQL Server to have replication happen immediately as
changes are made. That is, a change is made on server A, and that

change is

automatically applied to server B, rather than the replication

happening at

set intervals?

Thanks.

[Neil]

What I mean is that I'm using bound forms in the MDB to a large extent.
Updating of the data through the forms is handled automatically through the
ODBC driver without code.

"Hilary Cotter" <hi***********@gmail.com> wrote in message
news:uA**************@TK2MSFTNGP09.phx.gbl...

Distributed transactions are a coded solution where you do something like
this
begin trans
insert into server1.database1.dbo.table1 (charcol) values ('test')
insert into msaccessdb.table1 (charcol) values ('test')
if @@errornum<>0
rollback tran
else
commit tran

It can be done using MS DTC as well.
--
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
"Neil" <no****@nospam.net> wrote in message
news:Jg*****************@newsread2.news.pas.earthl ink.net...

I'm using an Access 2000 front end which updates a lot of data through
the
ODBC driver. Thus, I wouldn't be able to use distributed transactions in
every situation. Anything else that can immediately update one server
from
the other?

Thanks.

"Hilary Cotter" <hi***********@gmail.com> wrote in message
news:e6**************@TK2MSFTNGP10.phx.gbl...

> Consider distributed transactions for this. You can also set your polling > interval on your log reader and distribution agent to 1s, and your latency > will be around 10-20s.
>
> --
> 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
> "Neil" <no****@nospam.net> wrote in message
> news:8K*****************@newsread3.news.pas.earthl ink.net...
>> Is it possible in SQL Server to have replication happen immediately as
>> changes are made. That is, a change is made on server A, and that change > is
>> automatically applied to server B, rather than the replication happening > at
>> set intervals?
>>
>> Thanks.
>>
>>
>
>

[Damien]

Neil wrote:

What I mean is that I'm using bound forms in the MDB to a large extent.
Updating of the data through the forms is handled automatically through the
ODBC driver without code.

Hi Neil,

I know that in an ideal world, you'd like your two servers to be
perfectly synchronised. Can we explore the reasons for such a desire?

If it is for failover, it may be best to look into what clustering can
do for you. That is, if you have the $s to spend, since it quickly gets
expensive. Anything you "roll-your-own" with (e.g. having an update
trigger on the server A tables that updates the server B tables) can
actually make your system *more* brittle (if server B goes down, you'll
probably lose the ability to change anything in server A).

So, my first question is why do you want this immediate
synchronisation? We may be able to offer better ideas...

Damien