Immediate Synchronization?

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.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" <nospam@.nospam.net> wrote in message
news:8KRTe.2584$9x2.1236@.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.|||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" <hilary.cotter@.gmail.com> wrote in message
news:e6RW67FtFHA.3252@.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" <nospam@.nospam.net> wrote in message
> news:8KRTe.2584$9x2.1236@.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.
>>
>>|||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" <nospam@.nospam.net> wrote in message
news:Jg9Ue.7705$4P5.4425@.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" <hilary.cotter@.gmail.com> wrote in message
> news:e6RW67FtFHA.3252@.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" <nospam@.nospam.net> wrote in message
> > news:8KRTe.2584$9x2.1236@.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.
> >>
> >>|||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" <hilary.cotter@.gmail.com> wrote in message
news:uAWYaJUtFHA.1256@.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" <nospam@.nospam.net> wrote in message
> news:Jg9Ue.7705$4P5.4425@.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" <hilary.cotter@.gmail.com> wrote in message
>> news:e6RW67FtFHA.3252@.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" <nospam@.nospam.net> wrote in message
>> > news:8KRTe.2584$9x2.1236@.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 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