ABNORMAL Transactional Replication behaviour

Actually i have set up a transactional replication with one publisher
and 1 subscriber (planning, then, to add other 6 subscribers later on).
The point is that after setting everything up, the replication works
really fine for some minutes and then just stops without any warning or
error!! The system is composed by this replication and some others
applications that writes on some tables on the subscriber. This app
starts writting data... everything works fine and then, BANG!, without
any reason data is not sent to the publisher anymore! SQL server doesn't
seems to notice that!
The replication has itself some filters and some tables have numeric
counters with identity YES (also the one that is written by the
application i mentioned before) . But, when i set up the publication i
assign a range to this counters and the replication blocks itself before
reaching the 1 % of the range i assing to this field!!
Anyone has an idea of what is happening?
Please... somebody help me!!
F.P.
what does sp_browsereplcmds reveal? Run this in the distribution database to
see if commands are pooling there.
If they aren't run sp_repltrans in your publication database.
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
"newsgroup" <fabio.pliger@.siavr.it> wrote in message
news:nVn4f.10530$65.287427@.twister1.libero.it...
> Actually i have set up a transactional replication with one publisher and
> 1 subscriber (planning, then, to add other 6 subscribers later on). The
> point is that after setting everything up, the replication works really
> fine for some minutes and then just stops without any warning or error!!
> The system is composed by this replication and some others applications
> that writes on some tables on the subscriber. This app starts writting
> data... everything works fine and then, BANG!, without any reason data is
> not sent to the publisher anymore! SQL server doesn't seems to notice
> that!
> The replication has itself some filters and some tables have numeric
> counters with identity YES (also the one that is written by the
> application i mentioned before) . But, when i set up the publication i
> assign a range to this counters and the replication blocks itself before
> reaching the 1 % of the range i assing to this field!!
> Anyone has an idea of what is happening?
> Please... somebody help me!!
> F.P.
|||Thanks Hilary for the advice, but sp_browsereplcmds reveals only that
the last command was the one for the last record send from the
subscriber to the published database(no cmds for the records in the
subscriber and not replicated to the publisher). Then i tried to run
sp_repltrans on my publication db but it returns me no results... All
the agents continue "working" without any error, but no more records are
replicated (neither from pubisher to subscriber or vice-versa). Wherelse
can i search for the transactions failed? I mean...
Any other idea? The main thing that upsets me is that i'm not able to
see what's going wrong!! I'll try changing some configurations... I'm
using a push subscription... is it better to use a pull one?
I really need some help...
thanks...
Hilary Cotter ha scritto:
> what does ? Run this in the distribution database to
> see if commands are pooling there.
> If they aren't run sp_repltrans in your publication database.
>

A useable but non-updating subscriber?

Is there a way in SQL 2005 to be able to use a subscriber to test run data
and not have the updated data sent back to the publisher using any method of
replication? We will have 1 publisher and 2 subscribers (all 2005), and we
would like to use one of the subscribers for running test jobs, without
having the data returned to the publisher.
Thanks in advance
Nancy
Use either transactional or snapshot replication.
You can also use merge replication with the ExchangeType downloadonly
setting.
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
"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:eoSdBsreHHA.4560@.TK2MSFTNGP03.phx.gbl...
> Is there a way in SQL 2005 to be able to use a subscriber to test run data
> and not have the updated data sent back to the publisher using any method
> of replication? We will have 1 publisher and 2 subscribers (all 2005),
> and we would like to use one of the subscribers for running test jobs,
> without having the data returned to the publisher.
> Thanks in advance
> Nancy
>
|||So with transactional (in 2005), the subscription database would be usable
for reporting or testing, it woudn't be in a read only state, is that
correct? For some reason we were thinking the subscription database would
be read only.
Thanks,
Nancy
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eudjU0reHHA.1252@.TK2MSFTNGP04.phx.gbl...
> Use either transactional or snapshot replication.
> You can also use merge replication with the ExchangeType downloadonly
> setting.
> --
> 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
>
> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
> news:eoSdBsreHHA.4560@.TK2MSFTNGP03.phx.gbl...
>
|||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
"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:ubDFB5reHHA.3632@.TK2MSFTNGP02.phx.gbl...
> So with transactional (in 2005), the subscription database would be usable
> for reporting or testing, it woudn't be in a read only state, is that
> correct? For some reason we were thinking the subscription database would
> be read only.
> Thanks,
> Nancy
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eudjU0reHHA.1252@.TK2MSFTNGP04.phx.gbl...
>
|||Yes it will be readable and you can write to it, in other words put new
indexes on. When you update data you might cause your replication agent to
fail unless you run in the continue on data consistency error profile.
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
"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:ubDFB5reHHA.3632@.TK2MSFTNGP02.phx.gbl...
> So with transactional (in 2005), the subscription database would be usable
> for reporting or testing, it woudn't be in a read only state, is that
> correct? For some reason we were thinking the subscription database would
> be read only.
> Thanks,
> Nancy
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eudjU0reHHA.1252@.TK2MSFTNGP04.phx.gbl...
>
|||Thanks for the information,
Nancy
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23qSxO1seHHA.4848@.TK2MSFTNGP06.phx.gbl...
> Yes it will be readable and you can write to it, in other words put new
> indexes on. When you update data you might cause your replication agent to
> fail unless you run in the continue on data consistency error profile.
> --
> 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
>
> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
> news:ubDFB5reHHA.3632@.TK2MSFTNGP02.phx.gbl...
>
|||As well as Hilary's recommendation of transactional replication in this case,
I'd add that you could look at Read Committed Snapshot as a tool of
maintaining greater concurrency for those reports run during synchronization.
Paul Ibison
sql