Replication subscription validation in Microsoft SQL Server 2000

Hi,
I have set up a pull transactional replication using 2 machines: one
Publisher and the other Distributor-Subscriber.
Both are running Microsoft SQL Server 2000 SP3. They have different OSes
though: the Publisher has Microsoft Windows Server 2003 SP1 but the
Distributor-Subscriber has Microsoft Windows 2000 Advanced Server SP4.
I have one publication of all objects in a database and one subscriber to
all articles in that publication.
Will the successful result of sp_publication_validation be the sufficient
evidence to make an assumption that the Publication and Subscription
databases are identical if we don't use any text or binary data types? Does
it always need to be a check with checksums?
Many thanks,
Oskar
Oskar,
sp_publication_validation is only a basic comparison. Binary checksums are
needed to be certain, or in some cases I use SQL Data Compare. This can be
useful if you want to manually synchronize the data then reinitialize with
nosync ie you can't afford to reinitialize.
If you are using merge replication, there are some circumstances when rows
won't get replicated (basically those cases where triggers don't fire) and
these cases will be caught by your simple rowcount. Using more arcane
settings like -EXCHANGETYPE can result in the same rowcount but different
data, and while the binary checksum would catch this behaviour the rowcount
might miss it.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Replication subscription validation in Microsoft SQL Server 2000

Hi,
I have set up a pull transactional replication using 2 machines: one
Publisher and the other Distributor-Subscriber.
Both are running Microsoft SQL Server 2000 SP3. They have different OSes
though: the Publisher has Microsoft Windows Server 2003 SP1 but the
Distributor-Subscriber has Microsoft Windows 2000 Advanced Server SP4.
I have one publication of all objects in a database and one subscriber to
all articles in that publication.
Will the successful result of sp_publication_validation be the sufficient
evidence to make an assumption that the Publication and Subscription
databases are identical if we don't use any text or binary data types? Does
it always need to be a check with checksums?
Many thanks,
OskarOskar,
sp_publication_validation is only a basic comparison. Binary checksums are
needed to be certain, or in some cases I use SQL Data Compare. This can be
useful if you want to manually synchronize the data then reinitialize with
nosync ie you can't afford to reinitialize.
If you are using merge replication, there are some circumstances when rows
won't get replicated (basically those cases where triggers don't fire) and
these cases will be caught by your simple rowcount. Using more arcane
settings like -EXCHANGETYPE can result in the same rowcount but different
data, and while the binary checksum would catch this behaviour the rowcount
might miss it.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Replication subscription validation in Microsoft SQL Server 2000

Hi,
I have set up a pull transactional replication using 2 machines: one
Publisher and the other Distributor-Subscriber.
Both are running Microsoft SQL Server 2000 SP3. They have different OSes
though: the Publisher has Microsoft Windows Server 2003 SP1 but the
Distributor-Subscriber has Microsoft Windows 2000 Advanced Server SP4.
I have one publication of all objects in a database and one subscriber to
all articles in that publication.
Will the successful result of sp_publication_validation be the sufficient
evidence to make an assumption that the Publication and Subscription
databases are identical if we don't use any text or binary data types? Does
it always need to be a check with checksums?
--
Many thanks,
OskarOskar,
sp_publication_validation is only a basic comparison. Binary checksums are
needed to be certain, or in some cases I use SQL Data Compare. This can be
useful if you want to manually synchronize the data then reinitialize with
nosync ie you can't afford to reinitialize.
If you are using merge replication, there are some circumstances when rows
won't get replicated (basically those cases where triggers don't fire) and
these cases will be caught by your simple rowcount. Using more arcane
settings like -EXCHANGETYPE can result in the same rowcount but different
data, and while the binary checksum would catch this behaviour the rowcount
might miss it.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Replication Subscription Standard Edition

So I set up a "Replication/Local Publication" on a Production SQL
2005
Database.
It publishes a Database on the production database.
I then set up a "Replication/Local Subscription" on another Database
we call "Report Database"
that will have reports running on it and querying the Subscription
which is feed from the
production database.
They are syncronizing fine and everthing looks great.
My question is how do I access the Subscription? Can I query it like
a
database
using a "Database Engine Query"? Do I have to create a Database
instance on the "Report Database" Server
feeding off the Subscription?
Am I not going to be able to access the subscription because I don't
have snap shots?
I have no idea what to do now? How can I query the subscription? Can
the subscription act like a database?
I am having problems understanding the question. You have transactional
replication set up correctly and it is syncing ok to the Report Database.
This database can now be queried as per usual. if you are concerned about
blocking, you could use read_committed_snapshot isolation level to help with
the concurrency of reports.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Replication makes copies of tables and their data which by default will be
identical to the tables in the publication database. You should be able to
query them in the same way you query them in the 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
"jughead" <mike.aarset@.gmail.com> wrote in message
news:1173882913.696871.315700@.o5g2000hsb.googlegro ups.com...
> So I set up a "Replication/Local Publication" on a Production SQL
> 2005
> Database.
> It publishes a Database on the production database.
> I then set up a "Replication/Local Subscription" on another Database
> we call "Report Database"
> that will have reports running on it and querying the Subscription
> which is feed from the
> production database.
>
> They are syncronizing fine and everthing looks great.
>
> My question is how do I access the Subscription? Can I query it like
> a
> database
> using a "Database Engine Query"? Do I have to create a Database
> instance on the "Report Database" Server
> feeding off the Subscription?
> Am I not going to be able to access the subscription because I don't
> have snap shots?
>
> I have no idea what to do now? How can I query the subscription? Can
> the subscription act like a database?
>
|||On Mar 14, 8:12 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> Replication makes copies of tables and their data which by default will be
> identical to the tables in the publication database. You should be able to
> query them in the same way you query them in the 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 FTShttp://www.indexserverfaq.com
> "jughead" <mike.aar...@.gmail.com> wrote in message
> news:1173882913.696871.315700@.o5g2000hsb.googlegro ups.com...
>
>
>
>
> - Show quoted text -
I'm just wondering how to query the subscription.
When the subscription is created on the Report Database is a database
created on that server?
|||On Mar 14, 8:15 am, "jughead" <mike.aar...@.gmail.com> wrote:
> On Mar 14, 8:12 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>
>
>
>
>
>
>
>
>
> I'm just wondering how to query the subscription.
> When the subscription is created on the Report Database is a database
> created on that server... Hide quoted text -
> - Show quoted text -
I had an existing database on my Report Server that was the same name
of the Database on the Production Server that is getting publicated.
I am going to do a new publication on a different database and see how
it goes. Thanks guys
|||No, its not, you have to pre-create it.
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
"jughead" <mike.aarset@.gmail.com> wrote in message
news:1173885320.499067.58050@.y66g2000hsf.googlegro ups.com...
> On Mar 14, 8:12 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> I'm just wondering how to query the subscription.
> When the subscription is created on the Report Database is a database
> created on that server?
>
|||On Mar 14, 10:58 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> No, its not, you have to pre-create it.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "jughead" <mike.aar...@.gmail.com> wrote in message
> news:1173885320.499067.58050@.y66g2000hsf.googlegro ups.com...
>
>
>
>
>
>
>
>
> - Show quoted text -
You guys are right. I don't know what the heck I was doing when I
created my first Publication and Subscription.
Seems that everything is awesome now!
Thanks everyone
sql

Replication Subscription Error

Hi,
I tried setting a server up as the publisher, distributor and subscriber.
When I tried to create either a push or pull subscription I got the following
message:
Error 14053: Cannot load the DLL replincrementlsn extended procedure, or one
of the DLLs it references. Reason: 126(error not found).
The subscription could not be updated at this time.
The subscription could not be created.
SQL Server Enterprise Manager could not create a pull subscription to
publication XXXX
Error 14053: Cannot load the DLL replincrementlsn extended procedure, or one
of the DLLs it references. Reason: 126(error not found).
The subscription could not be updated at this time.
The subscription could not be created.
Has anyone seen this before?
Server running SQL 2000 on Windows 2000 Server
Thanks
Something is not installed correctly. Reapply your latest service pack.
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
"DavidW" <DavidW@.discussions.microsoft.com> wrote in message
news:895B9996-9D97-4CAA-84F2-FF314F842E6F@.microsoft.com...
> Hi,
> I tried setting a server up as the publisher, distributor and subscriber.
> When I tried to create either a push or pull subscription I got the
> following
> message:
> Error 14053: Cannot load the DLL replincrementlsn extended procedure, or
> one
> of the DLLs it references. Reason: 126(error not found).
> The subscription could not be updated at this time.
> The subscription could not be created.
> SQL Server Enterprise Manager could not create a pull subscription to
> publication XXXX
> Error 14053: Cannot load the DLL replincrementlsn extended procedure, or
> one
> of the DLLs it references. Reason: 126(error not found).
> The subscription could not be updated at this time.
> The subscription could not be created.
> Has anyone seen this before?
> Server running SQL 2000 on Windows 2000 Server
> Thanks
>
|||Thank you Hilary, that has resolved the problem!
"Hilary Cotter" wrote:

> Something is not installed correctly. Reapply your latest service pack.
> --
> 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
> "DavidW" <DavidW@.discussions.microsoft.com> wrote in message
> news:895B9996-9D97-4CAA-84F2-FF314F842E6F@.microsoft.com...
>
>

Replication subscriber views, locks and blocks.

Quick question, do views on the subscription server create locking and
blocking issues?
I have 2 servers set-up with transactional replication. We have scheduled
reports running on the subscribing server, most of those reports using views.
The views seem to create performance, locking and blocking issues.
Thank you very much in advance.
Message posted via http://www.droptable.com
Yes they can. You might want to consider using indexed views depending on
your version and edition of SQL Server.
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
"Frank N via droptable.com" <u10790@.uwe> wrote in message
news:5a40019273826@.uwe...
> Quick question, do views on the subscription server create locking and
> blocking issues?
> I have 2 servers set-up with transactional replication. We have scheduled
> reports running on the subscribing server, most of those reports using
> views.
> The views seem to create performance, locking and blocking issues.
> Thank you very much in advance.
> --
> Message posted via http://www.droptable.com
|||A (non-dirty) read of data takes out a shared lock and this is not peculiar
to views. If these views are used for reporting purposes, you might want to
have a replica to be used for reporting eg using transactional replication,
or database mirroring with database snapshots.
Alternatively you could allow dirty reads (NOLOCK) on the tables in
question - depends on the business constraints.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||So if we place (nolock) hints on all the tables in all the views that should
eliminate the locking?
Paul Ibison wrote:
>A (non-dirty) read of data takes out a shared lock and this is not peculiar
>to views. If these views are used for reporting purposes, you might want to
>have a replica to be used for reporting eg using transactional replication,
>or database mirroring with database snapshots.
>Alternatively you could allow dirty reads (NOLOCK) on the tables in
>question - depends on the business constraints.
>Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
Message posted via http://www.droptable.com
|||Yes - or more easily set the transaction isolation-level to read
uncommitted.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replication says success, but tables not showing in EM?

Hello All,
I am trying to replicate data from a SQL Server (7.0) to another SQL
Server using a one-way immediate push subscription. After executing,
there are no errors in the Snapshot or Push agents, but two of the
tables are missing in the subscription database. Where did they go?
I can find the tables listed in the publication->articles tab and in
the snapshot logs on the publisher. The tables are also listed on the
subscriber database under 'Database Roles Properties' --> Permissions.
Thank You For Your Time And Help,
Nate
Hi Nate,
You may want to check whether subscriptions for the two missing tables were
really created by calling sp_helpsubscription at the publisher. If not,
manually add them by calling sp_addsubscriptions with explicit article names
and rerun snapshot + distribution agents.
-Raymond
"Nate" <nathandeneau@.braintrade.biz> wrote in message
news:1142037215.646558.84890@.i40g2000cwc.googlegro ups.com...
> Hello All,
> I am trying to replicate data from a SQL Server (7.0) to another SQL
> Server using a one-way immediate push subscription. After executing,
> there are no errors in the Snapshot or Push agents, but two of the
> tables are missing in the subscription database. Where did they go?
>
> I can find the tables listed in the publication->articles tab and in
> the snapshot logs on the publisher. The tables are also listed on the
> subscriber database under 'Database Roles Properties' --> Permissions.
>
> Thank You For Your Time And Help,
> Nate
>
|||Everything looks as it should after calling sp_helpsubscription - the
two tables are listed.
|||This looks really strange. If you check the history messages of the snapshot
agent, do you see files for the two missing tables generated? And if you
check the distribution agent history, do you see that the files for the two
missing tables applied? If the tables are relatively small, you may be able
to fix things up by reinitializing the subscription, regenerate the snapshot
and reapply it. You may also want to watch out for processes outside of
replication that may have dropped the tables at the subscriber. What are the
sync_type values of subscriptions to the missing tables?
-Raymond
sql

Replication question - updatable subscription?

Hi all,

I am planning replication around a large SQL Database Server; 3 databases need to be replicated for reporting purposes.

My plan was to use Transaction Replication, as the Reporting database will be mostly read-only. However, we must add a whole bunch of additional, historical data to some of the tables of the reporting database that does not exist in the Publisher. I understand that changes can be made to subscriptions in Transaction Replication scenarios, ideally when the changes are few and far between. (Merge replication is excessive for our purposes.)

I don't want the additional historical data to be propagated back to the publisher. Is it possible that data is written to a subscriber in a Transactional Replication scenario without writing these updates to the Publisher? Given the nature of the data, there will not be conflicts between the historical data and the new changes applied from the publisher.

Any help would be much appreciated!

Thanks,
FarazWhile you are flirting at the edge of what works well, at least what you've described is possible. I'm pretty sure that Microsoft does not recommend it, I'm not sure if they support it, but I have done it under very limited circumstances for very specific purposes.

This is not for the faint of heart. You need to think things out carefully. It might pay you to find someone experienced in replication that you can contract to help set things up and shake out the problems.

-PatP|||Thanks for the reply. We won't be using transactional replication. In fact, from my understanding, one of the goals of replication is ensuring that data consistency is maintained between publishers and subscribers; in our case, we don't actually want the data to be consistent. Hence, we'll go forth with a less elegant, but far simpler solution.

Thanks again!
-Farazsql

Replication question

Using the "Pull New Subscription" wizrd in the enterprise manager, I can
create a subscription without a problem.
Howwever using TSQL I cannot do the same thing:
Using this sequence:
sp_addSubscription (on publisher)
sp_addPullSubscription (on subscriber)
sp_addPullSubscription_Agent (on subscriber)
The error I get is:
the "Subscription is not active"
Any hints on what I'm doing wrong will be most appreciated
when you created your publication you have to set @.status='active'
to check to see if you forgot to do this, expand your database, right click
on your publication and see if the push new susbcription menu option is
enabled.
If it is disabled do this
sp_changepublication 'publicationname','status','active'
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Ernesto" <mathematicus@.yahoo.com> wrote in message
news:4v2dnTB4KNHDF4bcRVn-jw@.speakeasy.net...
> Using the "Pull New Subscription" wizrd in the enterprise manager, I can
> create a subscription without a problem.
> Howwever using TSQL I cannot do the same thing:
> Using this sequence:
> sp_addSubscription (on publisher)
> sp_addPullSubscription (on subscriber)
> sp_addPullSubscription_Agent (on subscriber)
> The error I get is:
> the "Subscription is not active"
> Any hints on what I'm doing wrong will be most appreciated
>
>
|||As I check the status is saya that it is active. yet I still get the error
when doing things via VB code/
Is there any kind of detail I can provide that may halp figure this out?
Thanks for your help
ps too bad your book is not available now!
|||what is the error number you are getting?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Ernesto" <mathematicus@.yahoo.com> wrote in message
news:4bOdnd3Jr_C8e4bcRVn-pA@.speakeasy.net...
> As I check the status is saya that it is active. yet I still get the error
> when doing things via VB code/
> Is there any kind of detail I can provide that may halp figure this out?
> Thanks for your help
> ps too bad your book is not available now!
>

Replication Pull subscription problem

Iam trying to pull subscription and Iam getting same error over and over again. The error says:
****SQL Server Enterprise Merger could not create a pull subscription to publication "database name"
Error 15004: Name cannot be null *****
ThanksHi

Its because of non-existence or invalid server info on sysservers table. Please follow the instructions to fix the above problem.

exec sp_dropserver <your_local_server_name> -- subscriber

exec sp_dropserver <remote_server_name> -- publisher

exec sp_addserver <your_local_server_name>,local

exec sp_addserver <remote_server_name>

shutdown -- it is mandatory to take effect otherwise you still get the same error
go

Start the sql server with SQLSERVERAGENT

exec sp_helpserver -- it should display your local server and publisher

Then, try configure pull subscription

Cheers
Sekar|||What version does this happen in? I thought Microsoft fixed this problem a while ago.

replication process locking up the PFS page

Can anyone tell my why does replication sometimes hold a lock on the PFS page
of the subscription DB? The blocked process shows that it is waiting on
replication SPID and waitresource is "KEY: 11:1:1 (540080683243)" (11 is the
DB id of the subscription DB)
thanks.
What is the pfs page?
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
"MorDeRor" <MorDeRor@.discussions.microsoft.com> wrote in message
news:EFD676CB-BD3C-41C7-A42E-43D2C05A11EC@.microsoft.com...
> Can anyone tell my why does replication sometimes hold a lock on the PFS
> page
> of the subscription DB? The blocked process shows that it is waiting on
> replication SPID and waitresource is "KEY: 11:1:1 (540080683243)" (11 is
> the
> DB id of the subscription DB)
> thanks.
|||It's the Page Free Space page, which SQL Server uses to track the available
space on each data and index page in the database. It's always page ID 1 in
a data file.
Referring back to the initial question, the only scenario where I've heard
about contention on the PFS page refers to heavy creation of objects in
tempdb. Large deallocations could also be a factor.
Regards
Andrew Pike
SQL Server DBA
UBS IB
"Hilary Cotter" wrote:

> What is the pfs page?
> --
> 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
> "MorDeRor" <MorDeRor@.discussions.microsoft.com> wrote in message
> news:EFD676CB-BD3C-41C7-A42E-43D2C05A11EC@.microsoft.com...
>
>
|||Thanks Andrew, I now what you're talking about. I monitor our servers fairly
closely and see very little contention (if any) for the PFS page. The
blocking that I mentioned does not occur in the tempDB rather in the
subscription DB, and it does not happen consistently. There are a lot of
costom SPs that get triggered by replication which move data around, may be
I'll set up something to check for page splits and new extent utilization,
but even even so, it does not make sense, because the blocked query is just a
bunch of select statements, so why should it care that there is a lock on the
PFS page?
"Andrew Pike" wrote:
[vbcol=seagreen]
> It's the Page Free Space page, which SQL Server uses to track the available
> space on each data and index page in the database. It's always page ID 1 in
> a data file.
> Referring back to the initial question, the only scenario where I've heard
> about contention on the PFS page refers to heavy creation of objects in
> tempdb. Large deallocations could also be a factor.
> Regards
> Andrew Pike
> --
> SQL Server DBA
> UBS IB
>
> "Hilary Cotter" wrote:
|||Maybe you could monitor the Latch Wait Time (ms) counter? I think the PFS,
GAM and SGAM pages are synchronised via latching (UP latch?). If there's
memory pressure on the server and the Latch Wait Time is high (>500 ms) that
may be a source of the problem.
Kind Regards
Andrew Pike
SQL Server DBA
UBS IB
"Mor DeRor" wrote:
[vbcol=seagreen]
> Thanks Andrew, I now what you're talking about. I monitor our servers fairly
> closely and see very little contention (if any) for the PFS page. The
> blocking that I mentioned does not occur in the tempDB rather in the
> subscription DB, and it does not happen consistently. There are a lot of
> costom SPs that get triggered by replication which move data around, may be
> I'll set up something to check for page splits and new extent utilization,
> but even even so, it does not make sense, because the blocked query is just a
> bunch of select statements, so why should it care that there is a lock on the
> PFS page?
> "Andrew Pike" wrote:

Replication problem

I am trying to set up a pull subscription replication using SQL Server
Enterprise Manager. I get an error stating:
.....It cannot create named subscription because the publication does not
allow named pull subscription...
How do I enable the named pull subscription on the publisher?
Thanks in advance.
Hi Antonin,
You may have to enable Pull Subscription and Anonymous Subscription for the
Publication.
You may achieve this by
1. Click on Tools -> Replication -> Create and Manage Publication.
2. Expand the Published Database and select the publication name.
3. Click on Properties and Subscription
4. In the Publication Properties Dialog Box
5. Switch to Subscription Option tab
6. In this tab you will find two checkboxes under "Subscription Creation"
option,
"Allow Pull Subscription"
"Allow Anonymous Subscription"
Please tick both the check boxes to allow Pull Anonymous Subscription
for that publication.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Ashish,
Thanks for your reply. I followed your instructions and it worked OK.
However, there is Last Action on the publisher " A snapshot was not
generated because no subscription needed initialization.
Antonin
"Ashish Ruparel [MSFT]" <v-ashrup@.online.microsoft.com> wrote in message
news:TeZcQaXOEHA.1016@.cpmsftngxa10.phx.gbl...
> Hi Antonin,
> You may have to enable Pull Subscription and Anonymous Subscription for
the
> Publication.
> You may achieve this by
> 1. Click on Tools -> Replication -> Create and Manage Publication.
> 2. Expand the Published Database and select the publication name.
> 3. Click on Properties and Subscription
> 4. In the Publication Properties Dialog Box
> 5. Switch to Subscription Option tab
> 6. In this tab you will find two checkboxes under "Subscription Creation"
> option,
> "Allow Pull Subscription"
> "Allow Anonymous Subscription"
> Please tick both the check boxes to allow Pull Anonymous Subscription
> for that publication.
>
> HTH
> Ashish
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>

Replication problem

I am trying to set up a pull subscription replication using SQL Server
Enterprise Manager. I get an error stating:
....It cannot create named subscription because the publication does not
allow named pull subscription...
How do I enable the named pull subscription on the publisher?
Thanks in advance.Hi Antonin,
You may have to enable Pull Subscription and Anonymous Subscription for the
Publication.
You may achieve this by
1. Click on Tools -> Replication -> Create and Manage Publication.
2. Expand the Published Database and select the publication name.
3. Click on Properties and Subscription
4. In the Publication Properties Dialog Box
5. Switch to Subscription Option tab
6. In this tab you will find two checkboxes under "Subscription Creation"
option,
"Allow Pull Subscription"
"Allow Anonymous Subscription"
Please tick both the check boxes to allow Pull Anonymous Subscription
for that publication.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Ashish,
Thanks for your reply. I followed your instructions and it worked OK.
However, there is Last Action on the publisher " A snapshot was not
generated because no subscription needed initialization.
Antonin
"Ashish Ruparel [MSFT]" <v-ashrup@.online.microsoft.com> wrote in message
news:TeZcQaXOEHA.1016@.cpmsftngxa10.phx.gbl...
> Hi Antonin,
> You may have to enable Pull Subscription and Anonymous Subscription for
the
> Publication.
> You may achieve this by
> 1. Click on Tools -> Replication -> Create and Manage Publication.
> 2. Expand the Published Database and select the publication name.
> 3. Click on Properties and Subscription
> 4. In the Publication Properties Dialog Box
> 5. Switch to Subscription Option tab
> 6. In this tab you will find two checkboxes under "Subscription Creation"
> option,
> "Allow Pull Subscription"
> "Allow Anonymous Subscription"
> Please tick both the check boxes to allow Pull Anonymous Subscription
> for that publication.
>
> HTH
> Ashish
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>

Replication problem

I am trying to set up a pull subscription replication using SQL Server
Enterprise Manager. I get an error stating:
....It cannot create named subscription because the publication does not
allow named pull subscription...
How do I enable the named pull subscription on the publisher?
Thanks in advance.Hi Antonin,
You may have to enable Pull Subscription and Anonymous Subscription for the
Publication.
You may achieve this by
1. Click on Tools -> Replication -> Create and Manage Publication.
2. Expand the Published Database and select the publication name.
3. Click on Properties and Subscription
4. In the Publication Properties Dialog Box
5. Switch to Subscription Option tab
6. In this tab you will find two checkboxes under "Subscription Creation"
option,
"Allow Pull Subscription"
"Allow Anonymous Subscription"
Please tick both the check boxes to allow Pull Anonymous Subscription
for that publication.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Ashish,
Thanks for your reply. I followed your instructions and it worked OK.
However, there is Last Action on the publisher " A snapshot was not
generated because no subscription needed initialization.
Antonin
"Ashish Ruparel [MSFT]" <v-ashrup@.online.microsoft.com> wrote in message
news:TeZcQaXOEHA.1016@.cpmsftngxa10.phx.gbl...
> Hi Antonin,
> You may have to enable Pull Subscription and Anonymous Subscription for
the
> Publication.
> You may achieve this by
> 1. Click on Tools -> Replication -> Create and Manage Publication.
> 2. Expand the Published Database and select the publication name.
> 3. Click on Properties and Subscription
> 4. In the Publication Properties Dialog Box
> 5. Switch to Subscription Option tab
> 6. In this tab you will find two checkboxes under "Subscription Creation"
> option,
> "Allow Pull Subscription"
> "Allow Anonymous Subscription"
> Please tick both the check boxes to allow Pull Anonymous Subscription
> for that publication.
>
> HTH
> Ashish
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>

replication please help

Hi gurus, plz help me out!

SNAPSHOT! :mad:
Publisher: 1.5 GB of size. Push merge subscription.256 K full duplex internet connection.
Subscriber: physically at different country. T1 connection. I dial VPN to connect to the publisher which is in seperate domain. Since the domains are different, i am using SQL authentication for my replication process.

The problem with the snapshot is "disconnectivity". Merge agent starts form beginning after running for 2 or 3 hours. I have following entries for MSrepl_errors table in the distribution database:
error_code -2147201001 "The process could not deliver the snapshot to the Subscriber"
error_code 20037 "The process could not bulk copy into table '"dbo"."MY_TABLE"'"
error_code 11 "General network error. Check your network documentation."

The thing that bothers me much is that the VPN scarcely disconnects, only a single packet drop forces the merge agent to retry!!!. To isolate the problem i started ping reqest from publisher to subscriber; i saw merge agent to fail when a packet loss occured for only one second. VPN did not disconnect.
Currently i've chosen "slow link" profile for merge agent.

I started the snapshot 2 days ago and it restarts after every 2 or 3 hours.

Why the heck merge agent starts from the beginning; couldn't it be handled to start where it was when the connection was lost? Packet-loss is normal on the internet connections. I would love to know how u people replicate VLDBs over the internet.
I tried dynamic snapshot but found it rather risky; the merge agent stops with success during the middle of snapshot if connection is dropped leaving the subscriber with inconsistent data.
Any ideas and tips would be very much appreciated.

Howdy!I need u Guys!! :(|||An awful lot depends on what got lost, and where it got lost in the process. Merge replication is also a hodge-podge kind of thing, it is usually "interesting" trying to figure out where the "beginning" of it is, since it scarcely differentiates the difference between tables, much less any order to their changes.

Connections are definitely not "parts is parts" around the world. A connection from one part of the US to another is frequently "rock solid". A connection from here to London is very nearly as good, as is one from London to Sydney. One from the United States to Sydney is absolutely awful. So, we move the data going to Sydney through London... Even though it ends up going most of the way around the world instead of only about one fourth of the way, it is still ots more reliable.

Unless you have lots of time, energy, and money, this is where I'd bring in the "hired guns" to solve the problem. If you bring in someone like Microsoft Professional Services that deals with the various IP providers, knows the problems that exist between point A and point B, and understands the way that your specific replication setup is configured, you'll get an answer much faster and in the long run cheaper than trying to find the solution yourself.

-PatP|||Hi, Pat. Thanx for ur feedback.
I've tried this thing.
Publisher name SERVER1
Subscriber name SERVER2
I uploaded the SNAPSHOT.CAB (300 MB) from the publisher using download manager (if connection disconnects, it resumes where the connection was lost). It got uploaded in 3 hours.
Now i dropped the push merge subscription. Created Pull merge subscription at SERVER2. Placed the SNAPSHOT.CAB into local C drive of SERVER2.
The location i've extracted the snapshot file is:
C:\unc\SERVER1_MBDB_MBDB_PUBLICATION\2004061923325 2
In the Snapshot Location dialogbox for pull merge agent, I specified this location to force the agent to pick snapshot locally.
When i run the merge agent; it fails, for it searches the snapshot files from the default location at the publisher rather than the one i specified :mad: . The error is:

The schema script '\\SERVER1\C$\Program Files\Microsoft SQL Server\MSSQL\ReplData\unc\SERVER1_MBDB_MBDB_PUBLIC ATION\20040619233252\UR_TABLE_1.sch' could not be propagated to the subscriber.

Could not find the cabinet file 'snapshot.cab' in folder 'c:\unc\SERVER1_MBDB_MBDB_PUBLICATION\200406192332 52\unc\SERVER1_MBDB_MBDB_PUBLICATION\2004061923325 2\'

How can i square this location problem.
Any Help!!

Thanx for bearing along!|||If you are using a Merge PULL subscription, check the subscription properties at the subscriber to be sure that they specify the correct directory.

I'm a bit nervous about making suggestions, since you are making some pretty significant changes to what you are doing (push versus pull, where the agent exectutes, etc). Be careful to think out what you are doing!

-PatP|||Hi, thanx again 4 ur reply.
I did specify the correct directory where i placed the snapshot files. But the merge agent tries to access the snapshot from publisher's default directory rather than the local path.

I am tryin to use pull instead of push, for push won't work in my case due to disconnection. However i could move the whole snapshot.cab file to subscriber using a download manager despite the connection is lost b/w the transfer. But it isn't working either here. :confused:

Replication performance measurement

Hello,

I am trying some replication sample.I create a table with thousands of records in the publisher side. as I create a subscription(the subscription database is on a remote machine),
the whole table is created on the remote database.

I wanted to measure the performence as:
1. how much time was taken in filling the whole table in the subscriber side?
2. If i insert some 10000 records on publisher side, I want to measure, how much time was taken in inserting the same records on the subscriber?

How do I measure this ? Can I use some Log reader stuff.

thanks in advance

Are you talking about transactional replication? You can use replication's monitoring tool, which displays some performance information about your syncs, you can also look at the history tables located in the distribution database, such as MSSnapshot_history, MSdistribution_history, MSlogreader_history.|||You can look at less than 1minute, if that is within your LAN, if its a WAN, measure your bandwidth,even worst bandwidth u had, it wont take up more than 5minutes. The memory allocated to your SQL server need some consideration, and another factor is depend on your db design.

Replication on Named Instance SS2K

Is it possible to create a Pull Subscription on a named instance in SQL Server 2000 Developer Edition? When I try, I get an error message 14234 saying that it can't find the value '@.server'. Its hint is to look for the server in sp_helpserver. When I go to QA and connect to that named instance and run sp_helpserver, I see it there.

Sincerely,

can you post the complete error message with all the details. can you also tell us what version of sql server you're connecting to (select @.@.version in query analyzer), as well as what version of sql server you have on the machine that has Enterprise Manager?

Replication of Replications

Is it possible to have a pull subscription and then have a pull
subscription of that subscription? We want to have a server wich
replicates to 2 or more laptops wich will be replicated out to 3 or
more ppc all running sever - msde - sqlce?
Not as such, but it is possible to republish:
A publishes to B
B publishes to C
etc
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||with merge, this is doable. With Transactional replication you will have to
select the option to include DRI on your table articles so the primary keys
will be in place on your subscriber tables. Otherwise you won't have PK's on
B, to replicate to C.
"Shane Lim" <gslim@.blizzardice.com> wrote in message
news:i0alv0l0c35ppj2goua1k4uvdlpngi3fo0@.4ax.com...
> Is it possible to have a pull subscription and then have a pull
> subscription of that subscription? We want to have a server wich
> replicates to 2 or more laptops wich will be replicated out to 3 or
> more ppc all running sever - msde - sqlce?
|||Hilary's answer is more accurate - using alternative synchronization
partners is a possibility in merge replication as well as republishing.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Alright I setup all of my databases as publications and I can create a
pull from the master to the second slave. Everything looks good
except my articles are not visible. When I try to add them I get an
error saying that I cannot create duplicate tablenames. When I try to
create a pull to the second database the error I get states that it
cannot get a mergearticle list. Any ideas?
On Sat, 29 Jan 2005 11:25:37 -0500, "Hilary Cotter"
<hilary.cotter@.gmail.com> wrote:

>with merge, this is doable. With Transactional replication you will have to
>select the option to include DRI on your table articles so the primary keys
>will be in place on your subscriber tables. Otherwise you won't have PK's on
>B, to replicate to C.
>
>"Shane Lim" <gslim@.blizzardice.com> wrote in message
>news:i0alv0l0c35ppj2goua1k4uvdlpngi3fo0@.4ax.com.. .
>

Replication merge instance is not find in Performance Viewer

Hi all,
I set the configuration for Merge replication for Subscription on
one database and
I have created the Merge replication for publication on another
machine.
And I updated columns of subscription database table and I find
publication machine database table also updated with subscription
database table, here my problem starts,
My problem is when I run replication for merge transaction, I have to
find instance of the Windows on my machine in Performance Viewer (to
see performance viewer goto control panel ->Administrator->performance
in this select performance counter as SQL Server: Replication merge)
but I am not finding the instance
Please some body help me out.
RasheedI am not exactly sure what you mean here. In performance monitor the
counters are by instance. The option to select an instance or all instances
is grayed out. This is by design.
--
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
<sk.rasheedfarhan@.gmail.com> wrote in message
news:1163512500.562695.183350@.h48g2000cwc.googlegroups.com...
> Hi all,
> I set the configuration for Merge replication for Subscription on
> one database and
> I have created the Merge replication for publication on another
> machine.
> And I updated columns of subscription database table and I find
> publication machine database table also updated with subscription
> database table, here my problem starts,
> My problem is when I run replication for merge transaction, I have to
> find instance of the Windows on my machine in Performance Viewer (to
> see performance viewer goto control panel ->Administrator->performance
> in this select performance counter as SQL Server: Replication merge)
> but I am not finding the instance
> Please some body help me out.
> Rasheed
>|||thanks for resopnse Mrs. Hilary cotter
I will explain......i am using SQL Server 2000, when I run replication
(it may be transaction or merge replication) windows components will
run internally.
1) when I was running replication transaction I can see instances in
Performance
viewer but
2) when I was running replication merge I cant see instances in
Performance
viewer
so my doubt is for replication merge can we have the Instance or not ?

Replication merge instance is not find in Performance Viewer

Hi all,
I set the configuration for Merge replication for Subscription on
one database and
I have created the Merge replication for publication on another
machine.
And I updated columns of subscription database table and I find
publication machine database table also updated with subscription
database table, here my problem starts,
My problem is when I run replication for merge transaction, I have to
find instance of the Windows on my machine in Performance Viewer (to
see performance viewer goto control panel ->Administrator->performance
in this select performance counter as SQL Server: Replication merge)
but I am not finding the instance
Please some body help me out.
Rasheed
I am not exactly sure what you mean here. In performance monitor the
counters are by instance. The option to select an instance or all instances
is grayed out. This is by design.
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
<sk.rasheedfarhan@.gmail.com> wrote in message
news:1163512500.562695.183350@.h48g2000cwc.googlegr oups.com...
> Hi all,
> I set the configuration for Merge replication for Subscription on
> one database and
> I have created the Merge replication for publication on another
> machine.
> And I updated columns of subscription database table and I find
> publication machine database table also updated with subscription
> database table, here my problem starts,
> My problem is when I run replication for merge transaction, I have to
> find instance of the Windows on my machine in Performance Viewer (to
> see performance viewer goto control panel ->Administrator->performance
> in this select performance counter as SQL Server: Replication merge)
> but I am not finding the instance
> Please some body help me out.
> Rasheed
>
|||thanks for resopnse Mrs. Hilary cotter
I will explain......i am using SQL Server 2000, when I run replication
(it may be transaction or merge replication) windows components will
run internally.
1) when I was running replication transaction I can see instances in
Performance
viewer but
2) when I was running replication merge I cant see instances in
Performance
viewer
so my doubt is for replication merge can we have the Instance or not ?