SQL Server Performance Forum – Threads Archive

Huge data replication from partitioned tables

I have 120 millions of rows table (every row is ~90byte) which grows ~55millions on each month. I’m planning to transfer the last recorded data on each hour from server A to server B and delete that "succesfully" transferred data from server A. How can I implement this strategy in a well manner, it looks simple but there is a complexity. Cause querying the table takes more than two hours. I have tried to partitioning the data among the tables and combined them with a view. This behaviour had been succeded (time had been dropped to 10mins). So, I’ve decided to use it by this way. I’m thinking to select that view for the replication and Sql Server 2k would take care and would move out all of the related tables which has been specified in the view into the server B with the same structure (all of the partitioned tables and the combiner view). How can I accomplish this? Thank you

---

You can partition the table when you create the article then in server b you can create the partioned view.

---

This approach already was in my mind. But,.. I want to transfer from tables which were "already partitioned from the main table" and those partitioned tables would be dynamically alter the combiner view’s code when they had been created. So, I do not want to partition the table by hand, it should be automatically done through the view when replicating between the servers. Thank you

---

Well then you can create the article from the partioned view SQL will replicate it, the only problem is the size of the view.

---

Replication should be incremental, and would be on each hour so the data would not be so large I think, am I wrong?

---

http://www.winnetmag.com/Windows/Article/ArticleID/98/98.html &http://www.developer.com/db/article.php/757741 for information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

]]>