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 set-up stored procedures

Hello everyone,
I have two questions:
1. about sp_adddistributor - in the case when a remote distributor is being
used does it have to be run both at the publisher and at the distributor?
2. about sp_addsubscriber - where does it fit in the chain of replication
setup SPs? I have this sequence so far:
sp_adddistributor (@.distributor)
sp_adddistributiondb (@.distributor)
sp_adddistpublisher (@.distributor/@.publisher)
sp_replicationdboption (@.publisher)
sp_addpublication (@.publisher)
sp_addarticle (@.publisher)
sp_addsubscriber (@.publisher) is this the right point of execution?
sp_addsubscription (@.publisher)
thanks in advance
sp_adddistributor is run only on the distributor. You have a correct
placement for the add subscriber statement, but really it can be run anytime
after the distributor is created.
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:E505E5B2-6FDD-46A1-8D40-CD2D241A4B5C@.microsoft.com...
> Hello everyone,
> I have two questions:
> 1. about sp_adddistributor - in the case when a remote distributor is
> being
> used does it have to be run both at the publisher and at the distributor?
> 2. about sp_addsubscriber - where does it fit in the chain of replication
> setup SPs? I have this sequence so far:
> sp_adddistributor (@.distributor)
> sp_adddistributiondb (@.distributor)
> sp_adddistpublisher (@.distributor/@.publisher)
> sp_replicationdboption (@.publisher)
> sp_addpublication (@.publisher)
> sp_addarticle (@.publisher)
> sp_addsubscriber (@.publisher) is this the right point of execution?
> sp_addsubscription (@.publisher)
> thanks in advance
>
|||Hi Hilary,
I'm glad you picked up on this question. My confusion started from comparing
the EM generated scripts to the setup procedures described in your book where
I could not find the add subscriber stored procedure.
also, do I have to run grant access statements, or is there a default set of
rights that gets assigned to these objects that could be good enough?
Thanks
Mor
"Hilary Cotter" wrote:

> sp_adddistributor is run only on the distributor. You have a correct
> placement for the add subscriber statement, but really it can be run anytime
> after the distributor is created.
>
> --
> 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:E505E5B2-6FDD-46A1-8D40-CD2D241A4B5C@.microsoft.com...
>
>

Replication Question

Hi,

I am trying to set-up a transactional replication.

1. Created a Publication with 1 table containing only the first four columns (out of 10 columns).

2. When I create a subscription, how do I map the Publication Columns to Subscription columns? In my subscriber DB, I need different column names. I don't get an option to map the columns ...

How do I do it? TIA.

1. To create article with part of columns, please refer to Books On line, title <To define a column filter for an article published in a snapshot or transactional publication>

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/repref9/html/504fd568-13ae-4dc4-88da-950c1ec1e8c1.htm

2. Subscription can have table name different to published table name, but columns name has to be same.

Thanks

Yunjing

Replication Problem

High I hope someone can help me.

I'm trying to set-up replication between us and a business client.
We have a VPN into their network on 1 account set against 1 userid.
We then have a SQL SERVER acocunt on their Sql Server 7 machine.

We are running Win2000 and SQL Server 2000 Enterprise Std.

The publication has been set-up and looks okay
so has the pull subscription on our server.

When running the replication though I'm getting the following error.

The process could not connect to Distributor 'NXTIMSVR'.

Any ideas would be appriciated.

CliveWhere is your Distributor reside at? how was the account setup in the Distributor?|||Originally posted by joejcheng
Where is your Distributor reside at? how was the account setup in the Distributor?
The distributer is a the remote client site, I do not know how the distributer was set-up for we have just been asked to look after a section of their data.
Is there something that I could ask them to check.|||The first thing you have to make sure is that your publisher has to be able to access the Distributor. The ID that you setup your publication is the one you need to check. Then your subscirber has to be able to pull in information from the Distirbutor, so it need to be able to access the Distributor also.|||The publisher can see the distributer, its the PULL subscriber that is not connecting to the distributer.

Clive