Confused about snapshot repl

If I have applied a database dump to the subscriber for a no-sync database
that is using snapshot replication, do I need to keep applying new
snapshots? The distribution agents seem to be "delivering transactions" but
not as many as when I used transactional repl on the same database (this was
no-sync as well.) I haven't run any snapshots, because it says it is
delivering the transactions; not sure if this is correct.
Please help.
Thanks so much,
Kristy
Kirsty,
I'm confused and am not too sure what type of replication you are wanting to
set up. The title of the post implies snapshot, but dong a nosync
initialization for snapshot doesn't really make sense. Can you provide a
little more info on this and also what the transactions are that are being
delivered (sp_browsereplcmds).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Originally it was suppose to be transaction repl. But there were problems on
the host and the person who reset up the subscriptions accidently chose the
snapshot for the pull subscription. One of them was set to Transactional,
but it's been having major latency issues that I can't figure out. I didn't
know if the now snapshots subscriptions were not delivering everything
(because no further snapshots other than the original Db dump and snapshot -
that wasn't used because we shut down access to the db during db dump and
snapshot - has happened,) so I posted about that (see below for thread
questions.
If you never run the snapshot agent pass the first time then how come the
distribution agent looks like it is delivering transactions?
Thanks,
Kristy[vbcol=seagreen]
It only will if you do a no-sync.
Hilary Cotter[vbcol=seagreen]
So are the transactions being replicated then? Or do I have to keep running
snapshots?
Thanks,
Kristy
So that is why I thought it was no-sync. The 2 subscribing DBs do not get
updated in anyway and do not need to update the publisher. In fact, this
would be very bad. They DO need to have the lowest latency as possible. This
is a large DB (100+GB) that is a OLTP system, with broken down subscriptions
(pull) for the 1 production DB to give the info to the 2 subscribing DBs.
Not sure what you need from sp_browsereplcmds, so let me know. There is a
lot of records in there right now, but "I think" they seem to have started
from last night. Not sure if they are in order though, so I can say for
sure.
Clear as mud, right? Sorry. Let me know if you need any more info.
Thanks,
--Kristy
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eyNOUZ9UGHA.2360@.TK2MSFTNGP09.phx.gbl...
> Kirsty,
> I'm confused and am not too sure what type of replication you are wanting
to
> set up. The title of the post implies snapshot, but dong a nosync
> initialization for snapshot doesn't really make sense. Can you provide a
> little more info on this and also what the transactions are that are being
> delivered (sp_browsereplcmds).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Kirsty,
sorry but I don't follow this scenarion. To be honest I think you're getting
the terminology confused as what you're describing doesn't sound like
snapshot replication. Let's get as much systematic info as we can, so pls
can you confirm :
(a) have you set up Transactional Replication with a nosync initialization?
(b) what are the current issues you are encountering?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Okay, here is the info. I have 1 DB that is 100+ GB. It is broken down into
10 publications to try and improve latency issues (as suggested by Hiliary
awhile back ago.) Each publication is subscribed by 2 different servers;
essentially a "hot backup" and a "warm backup", though the later is to be
used for batch printing of reports as well in the near future.
Yes I was getting the terminology confused. Here is why:
When I look at my publisher and the publications in EM, they say they are
transaction replication. HOWEVER, when I run sp_helppullsubscription on both
of the subscriber DBs,
they return a publication_type of 1 (well except for one of them which
returns a 0 and is the one causing me high latency issues.) BOL says 1 =
snapshot and 0 = transactional replication. ADDitionally, if I look in EM on
the subscrtiption server under the pull subscriptions
folder of the subscribing database, they concur with the results from the
sp_helppullsubscription.
So that is why I am confused as to snapshot versus transactional. They are
suppose to be transcational.
The one (out of 20 subscriptions) that is coming up as transactional, is the
one causing me all of the problems. It has a sync type of automatic, because
when it expired because latency got too high (or was marked that it had to
be reinitialized) at that point a snapshot had to be created for that
subscription. The others have a sync_type of 2 (no sync). Whenever I run
sp_browsereplcmds, it seems like all of the outstanding transactions are
stuck in there and are not moving. So latency build up and again reaches an
unacceptable level. EM will say "delivering transactions" but I never see it
say anything else, and the commands seem to just sit in sp_browsereplcmds
result set.
The latency only goes up for this subscription, it never goes down. It just
climbs for a few days and then gets marked for reintialization (which I
would have to do anyways because I can't have a latecny of more than a few
hours.) All of my other subscriptions flux between 1 sec and 10 seconds.
Even the one that is suppose to be delivering the same subscription to a
different server.
So I am at a complete loss at this point. I don't even know if the other
subscriptions are working right since they say in sp_helppullsubscription
that they are snapshot. They are usually having commands dielivered
according to EM or are Idle. I need to figure out 1.) the others are working
right and 2.) what in the heck is wrong with the rogue subscription and how
to fix it.
I have Hiliary's book, but I can't find anything with this problem in it.
I'd sit down and cry right now if it would make me such a drama queen. ;-)
--Kristy
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:elr$CwJVGHA.5852@.TK2MSFTNGP10.phx.gbl...
> Kirsty,
> sorry but I don't follow this scenarion. To be honest I think you're
getting
> the terminology confused as what you're describing doesn't sound like
> snapshot replication. Let's get as much systematic info as we can, so pls
> can you confirm :
> (a) have you set up Transactional Replication with a nosync
initialization?
> (b) what are the current issues you are encountering?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Kristy,
please can you script out 2 pull subscriptions - one that reports that it is
transactional and the other that reports being snapshot and I'll take a
look.
Cheers,
Paul
|||Here is the one that lists as Transactional:
/****** Begin: Script to be run at Subscriber: ******/
exec sp_addpullsubscription @.publisher = N'OPT3', @.publisher_db = N'MyDB',
@.publication = N'5 - Asset', @.independent_agent = N'true',
@.subscription_type = N'pull', @.description = N'', @.update_mode = N'read
only', @.immediate_sync = 0
exec sp_addpullsubscription_agent @.publisher = N'OPT3', @.publisher_db =
N'MyDB', @.publication = N'5 - Asset', @.distributor = N'OPT3',
@.subscriber_security_mode = 1, @.distributor_security_mode = 0,
@.distributor_login = N'mine', @.distributor_password = N'', @.frequency_type =
64, @.frequency_interval = 1, @.frequency_relative_interval = 1,
@.frequency_recurrence_factor = 0, @.frequency_subday = 4,
@.frequency_subday_interval = 5, @.active_start_date = 0, @.active_end_date =
0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959,
@.enabled_for_syncmgr = N'false', @.use_ftp = N'false', @.publication_type = 0,
@.dts_package_location = N'subscriber', @.offloadagent = N'false'
GO
/****** End: Script to be run at Subscriber: ******/

Here is the one listed as Snapshot:
/****** Begin: Script to be run at Subscriber: ******/
exec sp_addpullsubscription @.publisher = N'OPT3', @.publisher_db = N'MyDB',
@.publication = N'5 - Asset', @.independent_agent = N'true',
@.subscription_type = N'pull', @.update_mode = N'read only', @.immediate_sync =
0
exec sp_addpullsubscription_agent @.publisher = N'OPT3', @.publisher_db =
N'MyDB', @.publication = N'5 - Asset', @.distributor = N'OPT3',
@.subscriber_security_mode = 1, @.distributor_security_mode = 0,
@.distributor_login = N'mine', @.distributor_password = N'', @.frequency_type =
64, @.frequency_interval = 1, @.frequency_relative_interval = 1,
@.frequency_recurrence_factor = 0, @.frequency_subday = 4,
@.frequency_subday_interval = 5, @.active_start_date = 0, @.active_end_date =
0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959,
@.enabled_for_syncmgr = N'false', @.use_ftp = N'false', @.publication_type = 1,
@.dts_package_location = N'subscriber', @.offloadagent = N'false'
GO
/****** End: Script to be run at Subscriber: ******/
--Kristy
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23e4uaivVGHA.4976@.TK2MSFTNGP11.phx.gbl...
> Kristy,
> please can you script out 2 pull subscriptions - one that reports that it
is
> transactional and the other that reports being snapshot and I'll take a
> look.
> Cheers,
> Paul
>
|||Kristy,
I notice that the @.publication_type = 1 is set differently - were these
created from scripts and perhaps the update was missed out?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I did a "generate script" on them from your request. They were both done
originally via Enterprise manager. THe transactional one (via
publication_type) has been redone many times because it keeps running at
high latency and I have to end up redoing it. I will just sit there and seem
not to deliver anything and just grow and grow until I have to reinitialize.
--Kristy
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OXbhTcLWGHA.3660@.TK2MSFTNGP04.phx.gbl...
> Kristy,
> I notice that the @.publication_type = 1 is set differently - were these
> created from scripts and perhaps the update was missed out?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Kristy,
please can you post up your database schema and a script to create the
publication and subscriptions and I'll try to repro.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)