SQL Server Performance

Huge data replication from partitioned tables

Discussion in 'Performance Tuning for SQL Server Replication' started by inancgumus, Jun 10, 2004.

  1. inancgumus New Member

    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
  2. Raulie New Member

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

    quote:Originally posted by Lazy_DBA

    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
  4. Raulie New Member

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

    quote:Originally posted by Lazy_DBA

    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?
  6. satya Moderator

Share This Page