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
[quoted text, click to view]
It only will if you do a no-sync.

--
Hilary Cotter
[quoted text, click to view]
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


[quoted text, click to view]

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


[quoted text, click to view]

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





[quoted text, click to view]

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


[quoted text, click to view]

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)

I'll post below the items that are relevant to your request (no need to post
my ENTIRE schema. LOL.) Let me know if you need anything else.

--Kristy





/****************************Databse schema*************************/

if exists (select * from dbo.sysobjects where id =
object_id(N'[PRODSERV].[Asset]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [PRODSERV].[Asset]
GO



if exists (select * from dbo.sysobjects where id =
object_id(N'[PRODSERV].[AssetClassProd]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [PRODSERV].[AssetClassProd]
GO



if exists (select * from dbo.sysobjects where id =
object_id(N'[PRODSERV].[AssetReject]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [PRODSERV].[AssetReject]
GO



if exists (select * from dbo.sysobjects where id =
object_id(N'[PRODSERV].[AssetTypeCat]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [PRODSERV].[AssetTypeCat]
GO



if exists (select * from dbo.sysobjects where id =
object_id(N'[PRODSERV].[AssetUse]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [PRODSERV].[AssetUse]
GO



if exists (select * from dbo.sysobjects where id =
object_id(N'[PRODSERV].[BusinessTypeCat]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [PRODSERV].[BusinessTypeCat]
GO



if exists (select * from dbo.sysobjects where id =
object_id(N'[PRODSERV].[PayOptCat]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [PRODSERV].[PayOptCat]
GO



if exists (select * from dbo.sysobjects where id =
object_id(N'[PRODSERV].[PlanFirmAstClass]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [PRODSERV].[PlanFirmAstClass]
GO



if exists (select * from dbo.sysobjects where id =
object_id(N'[PRODSERV].[ProdTypeCat]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [PRODSERV].[ProdTypeCat]
GO



if exists (select * from dbo.sysobjects where id =
object_id(N'[PRODSERV].[Vesting]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [PRODSERV].[Vesting]
GO



CREATE TABLE [PRODSERV].[Asset] (
[AssetID] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ProductID] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccountID] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DeathDisposal] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ReinvestType] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AssetType] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ManagedType] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PayOpt] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DataSource] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IntendUse] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SellMethod] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BudgetItemCode] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PlanFirmAstClassID] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[FinPlanEventID] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PropertyType] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InsuranceID] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BusinessType] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CDFrequency] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Units] [decimal](25, 6) NULL ,
[ParAmount] [decimal](25, 6) NULL ,
[InitialInvestment] [decimal](25, 6) NULL ,
[InitialInvestDate] [datetime] NULL ,
[InvestedAmount] [decimal](25, 6) NULL ,
[TaxBasis] [decimal](25, 6) NULL ,
[Value] [decimal](25, 6) NULL ,
[ValDate] [datetime] NULL ,
[GeneratedValue] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MaturityDate] [datetime] NULL ,
[Investment] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ReportExclude] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Managed] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Repositionable] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Exchange1035] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CurrentRate] [decimal](10, 5) NULL ,
[GrowthRate] [decimal](10, 5) NULL ,
[StandardDeviation] [decimal](10, 5) NULL ,
[DownloadDate] [datetime] NULL ,
[DownloadUnits] [decimal](25, 6) NULL ,
[DownloadAsOfUnits] [decimal](25, 6) NULL ,
[DownloadValue] [decimal](25, 6) NULL ,
[LatestTradeDate] [datetime] NULL ,
[SHM] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SHMP] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TranReject] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Note] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CapitalImprovement] [decimal](25, 6) NULL ,
[PersonalUsage] [decimal](10, 5) NULL ,
[StrikePrice] [decimal](25, 6) NULL ,
[ExpirationDate] [datetime] NULL ,
[Qualified] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GuaranteeRate] [decimal](10, 5) NULL ,
[GuaranteeDate] [datetime] NULL ,
[TotalPremium] [decimal](25, 6) NULL ,
[IssueDate] [datetime] NULL ,
[AgeAvail] [smallint] NULL ,
[AnnualIncome] [decimal](25, 6) NULL ,
[SurrenderCharge] [decimal](25, 6) NULL ,
[SurrenderValue] [decimal](25, 6) NULL ,
[SurrenderValueDate] [datetime] NULL ,
[BuySellAgreement] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GrossRevenue] [decimal](25, 6) NULL ,
[PercentOwnership] [decimal](10, 5) NULL ,
[OriginationDate] [datetime] NULL ,
[AvgCostPerUnit] [decimal](25, 6) NULL ,
[MutualFundAcctNum] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Discounted] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Confidential] [bit] NOT NULL ,
[LastModified] [datetime] NULL ,
[HostUpdated] [datetime] NULL ,
[OwnerNum] [int] NULL ,
[GroupNum] [int] NULL ,
[ModifiedByNum] [int] NULL ,
[RecStatus] [smallint] NULL ,
[ShareAccess] [smallint] NULL ,
[ExtShare] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO



CREATE TABLE [PRODSERV].[AssetClassProd] (
[AssetClassProdID] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ProductID] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FinancialPlannerID] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PlanFirmAstClassID] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[FinInstAstClassID] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[DefaultProduct] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Pct] [decimal](10, 5) NULL ,
[Sequence] [smallint] NULL ,
[Confidential] [bit] NOT NULL ,
[LastModified] [datetime] NULL ,
[HostUpdated] [datetime] NULL ,
[OwnerNum] [int] NULL ,
[GroupNum] [int] NULL ,
[ModifiedByNum] [int] NULL ,
[RecStatus] [smallint] NULL ,

Kristy,
please can you also send over the PK script and I'll repro it today.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

ALTER TABLE [PRODSERV].[Asset] ADD

CONSTRAINT [DF__Asset__Managed__553C041B] DEFAULT ('Y') FOR
[Managed],

CONSTRAINT [PK_ASSET] PRIMARY KEY NONCLUSTERED

(

[AssetID]

) ON [PRIMARY] ,

CONSTRAINT [CKC_ASSET_DOWNLOADDATE] CHECK ([DownloadDate] is
null or [DownloadDate] >= '01/01/1753' and [DownloadDate] <= '12/31/9999'),

CONSTRAINT [CKC_ASSET_EXPIRATIONDATE] CHECK ([ExpirationDate] is
null or [ExpirationDate] >= '01/01/1753' and [ExpirationDate] <=
'12/31/9999'),

CONSTRAINT [CKC_ASSET_GUARANTEEDATE] CHECK ([GuaranteeDate] is
null or [GuaranteeDate] >= '01/01/1753' and [GuaranteeDate] <=
'12/31/9999'),

CONSTRAINT [CKC_ASSET_INITIALINVESTDATE] CHECK
([InitialInvestDate] is null or [InitialInvestDate] >= '01/01/1753' and
[InitialInvestDate] <= '12/31/9999'),

CONSTRAINT [CKC_ASSET_ISSUEDATE] CHECK ([IssueDate] is null or
[IssueDate] >= '01/01/1753' and [IssueDate] <= '12/31/9999'),

CONSTRAINT [CKC_ASSET_LATESTTRADEDATE] CHECK ([LatestTradeDate]
is null or [LatestTradeDate] >= '01/01/1753' and [LatestTradeDate] <=
'12/31/9999'),

CONSTRAINT [CKC_ASSET_MATURITYDATE] CHECK ([MaturityDate] is
null or [MaturityDate] >= '01/01/1753' and [MaturityDate] <= '12/31/9999'),

CONSTRAINT [CKC_ASSET_ORIGINATIONDATE] CHECK ([OriginationDate]
is null or [OriginationDate] >= '01/01/1753' and [OriginationDate] <=
'12/31/9999'),

CONSTRAINT [CKC_ASSET_SURRENDERVALUEDATE] CHECK
([SurrenderValueDate] is null or [SurrenderValueDate] >= '01/01/1753' and
[SurrenderValueDate] <= '12/31/9999'),

CONSTRAINT [CKC_ASSET_VALDATE] CHECK ([ValDate] is null or
[ValDate] >= '01/01/1753' and [ValDate] <= '12/31/9999')

GO



ALTER TABLE [PRODSERV].[AssetClassProd] ADD

CONSTRAINT [PK_ASSETCLASSPROD] PRIMARY KEY CLUSTERED

(

[AssetClassProdID]

) ON [PRIMARY]

GO



ALTER TABLE [PRODSERV].[AssetReject] ADD

CONSTRAINT [PK_ASSETREJECT] PRIMARY KEY CLUSTERED

(

[AssetRejectID]

) ON [PRIMARY]

GO



ALTER TABLE [PRODSERV].[AssetTypeCat] ADD

CONSTRAINT [PK_ASSETTYPECAT] PRIMARY KEY CLUSTERED

(

[AssetType]

) ON [PRIMARY]

GO



ALTER TABLE [PRODSERV].[AssetUse] ADD

CONSTRAINT [PK_ASSETUSE] PRIMARY KEY CLUSTERED

(

[AssetUseID]

) ON [PRIMARY]

GO



ALTER TABLE [PRODSERV].[BusinessTypeCat] ADD

CONSTRAINT [PK_BUSINESSTYPECAT] PRIMARY KEY CLUSTERED

(

[BusinessType]

) ON [PRIMARY]

GO



ALTER TABLE [PRODSERV].[PayOptCat] ADD

CONSTRAINT [PK_PAYOPTCAT] PRIMARY KEY CLUSTERED

(

[PayOpt]

) ON [PRIMARY]

GO



ALTER TABLE [PRODSERV].[PlanFirmAstClass] ADD

CONSTRAINT [PK_PLANFIRMASTCLASS] PRIMARY KEY CLUSTERED

(

[PlanFirmAstClassID]

) ON [PRIMARY]

GO



ALTER TABLE [PRODSERV].[ProdTypeCat] ADD

CONSTRAINT [PK_PRODTYPECAT] PRIMARY KEY CLUSTERED

(

[ProdType]

) ON [PRIMARY]

GO



ALTER TABLE [PRODSERV].[Vesting] ADD

CONSTRAINT [PK_VESTING] PRIMARY KEY CLUSTERED

(

[VestingID]

) ON [PRIMARY]

GO





--Kristy























[quoted text, click to view]

OK - finally got round to reproing the whole scenario. I expected the
different publication_types to be the key but in my test I see no difference
between the 2 subscriptions. Both replicate ok and at the same speed. I'd
suggest opening a support case on this one. Actually, firstly I'd change all
the subscriptions to be the correct @publication_type to check my suspicion
that this is a red herring. After that see if the performance issues are
consistant with a particular subscriber and box. It might be simply network
issues so I'd check the transmission rate from that box. Another option is
locking issues on a particular subscriber might be slowing things down.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)