October 4, 2006 at 8:36 am
Hi Everyone,
I would like to bring an open discussion about the best solution for my transaction replication issues. 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,
Bhushan
October 5, 2006 at 12:14 pm
I will try to do thri SSIS as Millions Transactions are in question or i will create more Publishers & divide Tables(Articles) within them, so that i can have more associations of AGENTS for those Publishers.
October 5, 2006 at 12:26 pm
Also you can think about Database mirroring if that option is open.
October 6, 2006 at 3:13 pm
Definitely is an architectural issue to keep replication at that pace but it is not strange to me. First separate the distribution database into its own server and secondly change subscribers to pull. I can handle 600 million row tables and growing ... with that configuration.
As expressed above Database mirrioring sounds like ideal but if you need realtime access at subscribers it may not be an option. On the other hand if you are ok with subscribers having time lag use DBSnaphots with Mirroring.
Cheers,
* Noel
October 8, 2006 at 1:26 pm
Thanks to all of you. Database mirriong and snapshot should not statisfy my database requirements because the data should be more than 2 minutes latency. Some one suggested me to create clustered index in distribution database that would improve replication enormously. Is anyone aware of this technique? Please share your experience with this one.
Thanks,
Bhushan
October 11, 2006 at 10:11 pm
I am not sure that you should be tinkering with the distribution database, apart for maintenance purposes. Since you have a nightly process to load the data into the publisher database(s), could you not replicate a stored procedure execution instead to load the data to all the servers (publisher and 4 subscribers)?
October 30, 2006 at 1:46 am
Hello Dear fellow, I am looking replication and interface between Sql server and Oracle database how can I get the solution if you have any idea pls forward me.
thanks
October 30, 2006 at 3:04 am
Obviously row length etc will affect the capacity of transactional replication but I would expect 500,000 rows a day to be problematic.
Solutions I have worked on with a publisher and 1 distributor / subscriber have had issues with this kind of volume. Normally works for a while then something causes the logs/distribution database to grow and the whole thing blows up.
The only way to be sure that I know of is to test it.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply