Post reply

Copy all tables from Server A to Server B using looping

  • June 29, 2018 at 3:41 am

    #370626

    What I want to do:

    • SELECT CONCAT(TABLE_SCHEMA,'.',TABLE_NAME) FROM INFORMATION_SCHEMA.Tables (optional where clause) (executed on source Server A)
    • Save results in some sort of collection
    • Create a template package:  SELECT * FROM <source table>, where <source table> comes from each row of the results
    • Balanced Data Distributor with appropriate number of threads for the server (say 4)
    • Output table based on an expression, based on the <source table>
    • So both the source table and target table are named identically, and the purpose is to copy all tables from Server A to Server B.
    • Each execution of the package is asyncronous.  What would be cool is if I could thread limit the executions, say 8 at a time.  But I think this would be hard.  So let's just launch them all at once.
    • The execution would be from deployed packages on the server

    Hope this is clear.  Is this possible?  One problem I see is the column metadata mapping.  Would BIML work for this? (not that I know BIML)

    Otherwise, any bright ideas?

  • June 29, 2018 at 4:06 am

    #1995762

    Although possible it is not what I would call dynamic in that everytime a new table is required or the table metadata changes you would need to regenerate the SSIS package.

    BIML can do it  and for a task that just does this it would not be too hard.

    • How many tables are we talking about, what is the volume in terms of rows for each table, which tables have LOB type data and how many columns are lob and respective volumes. Note that SSIS is particular bad with LOB data types.
    • How often would it need to run
    • what is the required/desired SLA for task to finish
    • Does it need to run the copy of all tables or particular tables only on demand
    • Will the destination tables exist at all times or will they need to be recreated (including/excluding indexes) each time the copy runs
    • How often is metadata changes expected and if source changes should it apply automatically to destination
    • Will destination have FK's defined (will affect recreation of tables if required
    • What are the server hardware specs - both source and destination, including SQL Server versions

Viewing 2 posts - 1 through 1 (of 1 total)

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