SQL Server Performance Forum – Threads Archive
Replication 15 million Rows
Hi Everyone, I would like to bring an open discussion to get the best solution for my transactional replication. Please share your personal opinion/solution to the below replication issue. My replication environment has 1 publisher and 4 subscribers. There are 12 databases and 13 publications are created in replication environment, there would be around 70 articles (60 tables, 4 views and 6 stored procedures) getting replicated. Few tables have more than 300 millions of records and they keep adding half million everyday. Publisher gets updates through application and SSIS packages. SSIS packages on Publisher pulls data from various external sources every night and transform the data then load that data into publisher. This data has million of records every day and those have to be moved to all four subscribers. It seems that all subscribers are not catching up with publisher because there are millions of transactions (Aprroximately 15 million) everynight pushing to publisher through SSIS packages. This is a transactional replication model and push subscription. If you have any questions regarding replication model please feel free to post them in this thread then I will update as early as possible. Thanks, BhushanBhushan Kalla
We our just setting up a copy or our replication on a clustered server that is also 2005. We have a similar topology and millions of records we are pushing. One thing we did note is that in 2005 there is no clustered index on the distribution database. By adding an index we dropped CPU and latency down to almost a non-existence. Apparently, Microsoft forgot to add that in their design in 2005 (they had one by default added in 2000)
Thank you very mcuh for your reply.
How can we add clustered index on Distribution database? The distribution database is not an updateable database. Would you please give me more details about clustered index on distribution database. Thanks,
Bhushan Bhushan Kalla
]]>