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.
Neil,
this is possible from subscriber to publisher in the case of immediate
updating subscribers. In other cases you have to accept a degree of latency,
which can be minimised by optimisation.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||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
> at
>
|||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...[vbcol=seagreen]
> 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...
polling[vbcol=seagreen]
latency[vbcol=seagreen]
change[vbcol=seagreen]
happening
>
|||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...
> polling
> latency
> change
> happening
>
|||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