SQL Server 2005 Replication

We use replication extensively in our company as we have 38 warehouses in several countries all replicating back to our primary server in London.

Firstly, your replication filters should use Views, even the simple ones. That way, if you need to adjust the filter (read WHERE clause), you just need to alter the view and your done. Otherwise you have to re-publish your data, and re-subscribe everyone which can be a real pain.

You mentioned that you run the same delete on both subscriber and publisher to keep them in-sync. This sends shivers down my spine. Your far better off deleting them in one place and letting the server replicate out to the subscribers the changes made. Since SQL Server 2005, replication is very fast and efficient now. SQL 2000 was and is quite slow for replication. If your using SQL 2005/2008, just make sure your compatibility level (right click on db, properties, options) is set to 90 (2005) or 100 (2008). This switches sql server over to the fast and efficient replication methods.

Another way is to not delete the data, but to keep it and filter it out using a where clause in the publication.

It has been a long time since I actively administered replication but I suspect the answer has to do with the architecture of the log-reader and that you are sharing an article between publications. My understanding is that the log-reader will trawl through the log and look for operations on items that are replicated. Depending on the article settings, the individual changes to the data may be posted to a table in the distribution database or a record of the procedure invocation will be posted. In any case, this is a property of the article and not the publication(s) that the article is a member of. I assume (but have not tested and verified) that you can create multiple articles on top of the same database object and have one be replicated with @type='logbased' and the other with @type='proc exec'

Take all of this with a large pinch of salt: although I now develop on SQL 2008, the last time I did anything with replication was SQL 7.

pjjH