Post reply

Copy of data from one server to another server

  • December 6, 2007 at 1:54 am

    #184588

    Hi,

    I have been given a project plan to leave only 30 days of History on 3 tables (T1, T2, T3) on a server.

    But all the historical information should reside in another server only.

    I can't simply delete and leave only the last 30 days because the tables are large and the transaction will consume all resources.

    I need to plan to potentially truncate and then bring back the last 30 days.

    Any idea, on doing this without affecting the production.

    Thanks in advance.

  • December 6, 2007 at 2:19 am

    #757575

    You could partition the table.

    How frequently do you need to truncate?

    You could create a few tables, and create a view over them. Then just rotate the tables underneath.

    SQL Server 2005 allows you to use partitioned tables, where you could swap in and swap out tables. (see in Book Online "CREATE PARTITION SCHEME").

    This will make it even easier to insert data to the right table. Each partition has its own index structure, (even though they appear to be as a single index

    over the whole partitioned table). This makes it fast to replace parts, and even to update the individual parts.

    Swapping tables is much much cheaper than deleting rows. And you could move the swapped tables to the other server for archiving.

    But, much depends on how frequently you need to swap tables.

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • December 6, 2007 at 2:23 am

    #757577

    Thanks for your response.

    If the server is SQL 7, how can i do it.

  • December 6, 2007 at 2:47 am

    #757583

    sqldba (12/6/2007)

    Thanks for your response.

    If the server is SQL 7, how can i do it.

    Hmmm, SQL Server 7 🙁

    You could still break up the table into several tables, and then create a view over these with UNION ALL.

    On the individual tables set up check constraints on the keys/dates (this will be used by the optimizer)

    CREATE VIEW PartitionedTable

    AS

    SELECT col1, col2, col3 FROM PartitionedTable_part1

    UNION ALL

    SELECT col1, col2, col3 FROM PartitionedTable_part2

    UNION ALL

    SELECT col1, col2, col3 FROM PartitionedTable_part3

    UNION ALL

    SELECT col1, col2, col3 FROM PartitionedTable_part4

    When retrieving data SQL Server (even 7) is intelligent enough to use the check constraints and the indexes of the individual tables, so only the relevant

    underlying tables are used (this is good, because the individual index structures are small).

    Unfortunately SQL Server 7 does not allow you to update via this view, so you would need to implement inserts and updates yourself via a stored proc.

    Swapping in and out tables is basically renaming (sp_rename) underlying tables, pushing a new table back (rename), and then sp_refreshview.

    Do test, test, test these solutions.

    However, the above can get rather complex (too many tables, ...)

    Stepping back a bit: do you have calm periods when you could delete the data? How much time does it take to get rid of the old data? How frequently does this happen?

    You could try to delete in smaller chunks. You could, if allowed in a maintenance window, backup, swap to simple recovery, delete in smaller chunks (the log will not grow too much, as it will be reused), full backup, continue ...

    (this works of course only if you have the time for such a maintenance period)

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply