Replication and Deleting Rows

Hello,
I have a large table with over 20 million rows.
It is replicated accross a 4 subscribers using Pull subscription. One of the
subscribers is also a Distributor.
We are running some processed to "clean up" the data in this table by
issuing a lot of DELETE commands (using Primary Key as the where clause)
against the large table. The DELETE commands are enclosed in BEGIN TRAN -
COMIT TRAN batches of 20 each.
The deletion works extremely fast on the Publisher.
However, it takes very very long time to apply the very same commands on all
of the subscribers. The subscribers also have the PRIMARY KEY index that is
on the Publisher.
We need to delete a few million rows from the large table using this
technique. However, it makes the Subscribers very slow and unresponsive.
Any suggestions?
Thanks,
ArsenConsider doing this in even smaller batches. If that is not possible, and
transactional consistency is not really of importance, then you could use a
parameter called MaxCmdsInTran for logreader (introduced in SQL Server 2000
SP1 I believe). This breaks huge transactions into smaller chunks of
specified size, while storing the commands in distribution database.
There is another approach, that I would recommend. Consider using 'stored
procedure execution' replication. With this method, you keep a copy of the
archiving stored procedure on all subscribers. When you run this procedure
on the publisher, the distribution agent will call this sp on all
subscribers, instead of sending millions of DELETEs over the network. See
SQL Server Books Online for more information on this.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Arsen V." <arsen@.community.nospam> wrote in message
news:%23hpi4tKoEHA.2612@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a large table with over 20 million rows.
> It is replicated accross a 4 subscribers using Pull subscription. One of
the
> subscribers is also a Distributor.
> We are running some processed to "clean up" the data in this table by
> issuing a lot of DELETE commands (using Primary Key as the where clause)
> against the large table. The DELETE commands are enclosed in BEGIN TRAN -
> COMIT TRAN batches of 20 each.
> The deletion works extremely fast on the Publisher.
> However, it takes very very long time to apply the very same commands on
all
> of the subscribers. The subscribers also have the PRIMARY KEY index that
is
> on the Publisher.
> We need to delete a few million rows from the large table using this
> technique. However, it makes the Subscribers very slow and unresponsive.
> Any suggestions?
> Thanks,
> Arsen
>

Replication and Deleting Rows

Hello,
I have a large table with over 20 million rows.
It is replicated accross a 4 subscribers using Pull subscription. One of the
subscribers is also a Distributor.
We are running some processed to "clean up" the data in this table by
issuing a lot of DELETE commands (using Primary Key as the where clause)
against the large table. The DELETE commands are enclosed in BEGIN TRAN -
COMIT TRAN batches of 20 each.
The deletion works extremely fast on the Publisher.
However, it takes very very long time to apply the very same commands on all
of the subscribers. The subscribers also have the PRIMARY KEY index that is
on the Publisher.
We need to delete a few million rows from the large table using this
technique. However, it makes the Subscribers very slow and unresponsive.
Any suggestions?
Thanks,
Arsen
Consider doing this in even smaller batches. If that is not possible, and
transactional consistency is not really of importance, then you could use a
parameter called MaxCmdsInTran for logreader (introduced in SQL Server 2000
SP1 I believe). This breaks huge transactions into smaller chunks of
specified size, while storing the commands in distribution database.
There is another approach, that I would recommend. Consider using 'stored
procedure execution' replication. With this method, you keep a copy of the
archiving stored procedure on all subscribers. When you run this procedure
on the publisher, the distribution agent will call this sp on all
subscribers, instead of sending millions of DELETEs over the network. See
SQL Server Books Online for more information on this.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Arsen V." <arsen@.community.nospam> wrote in message
news:%23hpi4tKoEHA.2612@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a large table with over 20 million rows.
> It is replicated accross a 4 subscribers using Pull subscription. One of
the
> subscribers is also a Distributor.
> We are running some processed to "clean up" the data in this table by
> issuing a lot of DELETE commands (using Primary Key as the where clause)
> against the large table. The DELETE commands are enclosed in BEGIN TRAN -
> COMIT TRAN batches of 20 each.
> The deletion works extremely fast on the Publisher.
> However, it takes very very long time to apply the very same commands on
all
> of the subscribers. The subscribers also have the PRIMARY KEY index that
is
> on the Publisher.
> We need to delete a few million rows from the large table using this
> technique. However, it makes the Subscribers very slow and unresponsive.
> Any suggestions?
> Thanks,
> Arsen
>

Replication accross Domains?

Hello,
Can replication be set up between two servers in different domains? What if
they are in the same forest?
Regards,
Randy.
Randy,
if the domains are trusted (same forest = 2-way trust as you know), then
things are pretty much transparent. If there is no trust, then this article
should help: http://www.replicationanswers.com/InternetArticle.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||It is possible. Are you able to register the subscriber as a Subscriber on
the Publisher? An easy way to do this is to use SQL Authentication. If you
are using a pull subscription its a no brainier beyond this. If you are
using Push with a UNC, the SQL Server agent on the subscriber must have
rights to access the snapshot share. An easy way to fix this is to create a
local machine account on the publisher with the same name and password as
the account you are using for your SQL Server agent account on the
Subscriber. Place this account in the administrators group on the Publisher
if you are using the default snapshot share - otherwise give this account
read and list files and folders rights to the file and folders underlying
the snapshot share.
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
"Randy Galliano" <r_galliano@.yahoo.com> wrote in message
news:%231ev4IkBGHA.1864@.TK2MSFTNGP12.phx.gbl...
> Hello,
> Can replication be set up between two servers in different domains? What
> if
> they are in the same forest?
> Regards,
> Randy.
>

Replicating Stored Procedure permissions issue

I'm having a problem replicating stored procedure permssions accross two sql server 2005 servers, using transactional replication. When I replicate tables, it's easy enough to send the permissions over from the subscriber since there is an option under the articles properties for "Copy permissions." I can't seem to find any such option for SPs, UDFs or views. Am I missing something?

So the other idea I had was to create a script to grant the proper permissions, and run it using sp_addexecscript to my publication. Currently I have two one-way transactional publications. One publication for all my tables, and another for SPs, UDFs and views. I can run sp_addexecscript on the publication containing all my tables with out a problem, but when I run it against the publication containing my SPs, UDFs and views I get the following error:

Msg 21332, Level 16, State 1, Procedure sp_MSrepl_addscriptexec, Line 57
Failed to retrieve information about the publication : SP_UDF_Views_transactional. Check the name again.

I'm not sure why I'm getting this message. If anyone can help me out, I'd greatly appreciate it.

sp_addexecscript must be some user-defined stored procedure, I've never heard of it so I don't know what would cause be the problem. However the workaround would be to use parameter @.post_snapshot_script in sp_addpublication. Supply your permissions TSQL file here, it will automatically be applied at the subscriber after the snapshot is applied.

|||

While it is unfortunate that the option for replicating permissions with SPs\VWs\UDFs is not exposed in the UI (please file a bug at the Microsoft Connect site), you can enable the schema option (0x40000000) for replicating permissions via sp_addarticle.

Hope that helps,

-Raymond

|||Greg,

Thanks for the help, I will give that a try. Also, to clarify, I mistyped in my first post what command I'm using. The command is sp_addscriptexec. Sorry about the confussion.|||Excellent, both methods worked great! Thanks for the help!|||On the first test of altering the schema option, everything worked fine, but now I'm running into errors.

My original @.schema_option value was 0x0000000008000001, I changed it to 0x0000000048000001 to enable permission replication (0x40000000). Once I changed that value it tries to add "WITH EXECUTE AS CALLER" when it replicates the SPs. Then I get the following errors:

Error messages:
Incorrect syntax near 'dbo'. (Source: MSSQLServer, Error number: 102)
Get help: http://help/102
Incorrect syntax near 'dbo'. (Source: MSSQLServer, Error number: 102)
Get help: http://help/102
Incorrect syntax near the keyword 'AS'. (Source: MSSQLServer, Error number: 156)
Get help: http://help/156
Must declare the scalar variable "@.sServer". (Source: MSSQLServer, Error number: 137)
Get help: http://help/137
Must declare the scalar variable "@.iType". (Source: MSSQLServer, Error number: 137)
Get help: http://help/137

Once I change the schema option back to 0x0000000008000001, replication works, but without permissions...
|||Can you post the schema script for the problematic stored procedure generated by the snapshot agent? Thanks.|||

Just to rule out the obvious, can you post the schema option value returned by running sp_helparticle at the publisher as well? Thanks much.

-Raymond

|||Raymond,

Sorry to not get back to you sooner. I was out of the office for a few days. Here is the script I'm using to add the article (if this isn't what you wanted, please let me know).

exec sp_addarticle @.publication = N'dismal_v8_SP_UDF_Views_transactional', @.article = N'ct_getContentItem', @.source_owner = N'dbo', @.source_object = N'ct_getContentItem', @.type = N'proc schema only', @.description = N'', @.creation_script = N'', @.pre_creation_cmd = N'drop', @.schema_option = 0x0000000048000001, @.destination_table = N'ct_getContentItem', @.destination_owner = N'dbo', @.status = 16

Also, the schema_option value returned by sp_helparticle: 0x0000000048000001

It's weird, I'm getting this error on nearly every stored procedure in my database. If I right click, modify, on the stored procedure and then hit execute without making any changes to the SP, this error goes away. Unfotunetly we have about 20 databases that will need to replicate 100 or so stored procedures, so I don't think "recompiling" the stored procedure is an acceptable solution. Let me know what you think. Thanks!|||

Are you getting the error when the initial snapshot is delivered or after you have changed your stored procedures at the publisher and have the "alter procedure" replicated to the subscriber (ddl replication). Not all schema options are honored during ddl replication. Ddl replication also doesn't handle the use of " for delimiting string literals.

If your subscribers are SQL2005 servers, they should have no trouble handling the "execute as" syntax so the problem that you are experiencing may be due to something else. It would be great if you can post the create (alter?) procedure statement that is executed at the subcriber, you should be able to get that from SQL Profiler.

Modifying text objects such as stored procedures by right clicking on the object can modify the object header in unexpected ways, you may want to use direct query to modifying your stored procedures instead.

-Raymond

|||Raymond,

I'm getting the error when the initial snapshot is delivered.

I will try to post the create procedure statement that is executed at the subscriver later today or tomorrow. I've since recreated the publication without the schema option to copy permissions in order to test other items.