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 on port 80 with sqlServer 2005

Can replication be done over port 80 with SQL Server 2005?
This would remove the "hotel firewall blocks port 1433" scenario.......
Thanks
You can run SQL server on any port including 1433, or 80. Causes havoc with
web servers running on this port though.
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
"astro" <astro@.bcmn.com> wrote in message
news:3HZlf.1272$7S.24@.tornado.rdc-kc.rr.com...
> Can replication be done over port 80 with SQL Server 2005?
> This would remove the "hotel firewall blocks port 1433" scenario.......
>
> Thanks
>
|||that makes sense...but how do you deal with traveling sales staff that need
to replicate on their hotel connection when all ports except 80 (web) and 25
(smtp) and 110 (pop3) have been locked down? Has anyone else ran into this?
Or maybe this is some other issue.....
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OAc0HoM$FHA.272@.TK2MSFTNGP09.phx.gbl...
> You can run SQL server on any port including 1433, or 80. Causes havoc
> with web servers running on this port though.
> --
> 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
> "astro" <astro@.bcmn.com> wrote in message
> news:3HZlf.1272$7S.24@.tornado.rdc-kc.rr.com...
>
|||I've noticed limitations are enforced if you enable web access in your
publication: first is the dynamic snapshots are not created or used as new
subs try to sync, 2nd you can use logical record, were replication can treat
joined tables as a transaction (order hdr/lines for example)
the new prepared dyn snap shots are awesome, our initial sync times went
from 40mins to ~ 2mins!! (and it was 2+ hours on sql2000) and its all
automatic...
also if you have the pub web enabled, yet still sync through the tradition
means (which you can, you can change it in the sub), dyn snapshouts still do
not work...
"astro" <astro@.bcmn.com> wrote in message
news:3HZlf.1272$7S.24@.tornado.rdc-kc.rr.com...
> Can replication be done over port 80 with SQL Server 2005?
> This would remove the "hotel firewall blocks port 1433" scenario.......
>
> Thanks
>
|||sorry correction-- *CAN'T* use logical records
"S c o t t K r a m e r" <sckramer2000@.hotmail.com> wrote in message
news:d10dc$439bed58$a227293d$4609@.ALLTEL.NET...
> I've noticed limitations are enforced if you enable web access in your
> publication: first is the dynamic snapshots are not created or used as new
> subs try to sync, 2nd you can use logical record, were replication can
> treat joined tables as a transaction (order hdr/lines for example)
> the new prepared dyn snap shots are awesome, our initial sync times went
> from 40mins to ~ 2mins!! (and it was 2+ hours on sql2000) and its all
> automatic...
> also if you have the pub web enabled, yet still sync through the tradition
> means (which you can, you can change it in the sub), dyn snapshouts still
> do not work...
> "astro" <astro@.bcmn.com> wrote in message
> news:3HZlf.1272$7S.24@.tornado.rdc-kc.rr.com...
>
|||I'll have to look at this.....
Thanks.
"S c o t t K r a m e r" <sckramer2000@.hotmail.com> wrote in message
news:d10dc$439bed58$a227293d$4609@.ALLTEL.NET...
> I've noticed limitations are enforced if you enable web access in your
> publication: first is the dynamic snapshots are not created or used as new
> subs try to sync, 2nd you can use logical record, were replication can
> treat joined tables as a transaction (order hdr/lines for example)
> the new prepared dyn snap shots are awesome, our initial sync times went
> from 40mins to ~ 2mins!! (and it was 2+ hours on sql2000) and its all
> automatic...
> also if you have the pub web enabled, yet still sync through the tradition
> means (which you can, you can change it in the sub), dyn snapshouts still
> do not work...
> "astro" <astro@.bcmn.com> wrote in message
> news:3HZlf.1272$7S.24@.tornado.rdc-kc.rr.com...
>
|||cool!
sql2005 Merge replication speed is amazing now, makes sql2000 seem like the
stone age!!
"astro" <astro@.bcmn.com> wrote in message
news:hDhnf.9358$Dk.8912@.tornado.rdc-kc.rr.com...
> I'll have to look at this.....
> Thanks.
> "S c o t t K r a m e r" <sckramer2000@.hotmail.com> wrote in message
> news:d10dc$439bed58$a227293d$4609@.ALLTEL.NET...
>