Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 How many publications for 78 tables + Agent logins

Author  Topic 

normajeanb
Starting Member

7 Posts

Posted - 2006-07-28 : 15:23:41
I am replicating 78 tables from a master database for a scale-out solution. My dilemma relates to best design practices – I have found only one article about design considerations for publications and this guy recommended creating multiple publications within a single database publisher. So, at this point, I have 78 different publications, each with only a single article (an individual table). Is this good practice? Will performance be enhanced by the parallelism of individual publications for each table article, or will it be hurt by so many publications? Should I try to reduce the number of publications or do you think it’s fine to have one for each table?

Each publication has been set to have it’s own independent distribution agent – I was thinking that this would keep the replicated data more concurrent in the subscribers databases (I have the polling frequency for log and dist. agents set to one second). We don’t have huge updates from clients, just steady transactions.

Any and all advice you could offer about optimizing these 78 table publications would be greatly appreciated.

I am in also in the process of adding more security for my agents and read that I should create local windows accounts for them to use instead of impersonating SqlServerAgent’s account. Another question that I cannot seem to find an answer for is whether I should create a different account for each of the 78 distribution agents or if they can (and should) all share the same windows login without any problems ?
   

- Advertisement -