Best way to replicate 1 table from Production DB to Dev Db

  • I'm sure there are a dozen different ways that this can be accomplished and many things that will make some better that others.

    I have a VM win2012R2 SQL2016dev 'dev' and a box win2016 sql2016ent 'prod'

    Developers cannot have any access to prod, and have full access on dev.

    I would like to provide them with the same data that is written to the LOG table in one DB on prod.

    I have thought of a few options;

    SSIS - mainly the ability to move data between 2 different systems, but I don't think that this is a good idea.

    trigger to Linked server - not even sure if this is possible.

    replication - have not worked with this yet.

    What suggestions do you have ?

  • As it's a log table, would I be right in thinking that the only items to be replicated would be new INSERTs? Or are there UPDATEs and DELETEs to contend with, too?

    What is the primary key of the source table? A simple ascending IDENTITY column with only INSERTs to worry about would make this an easy problem to solve.

    How much latency would you like? Is a daily update sufficient, or would you like it to be more regular?

    What is it about SSIS that makes you think it would be a bad idea?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See for details of how to post T-SQL code-related questions.

  • yes inserts only, and yes simple ascending IDENTITY.

    As immediately as possible.  Primarily used during an update so that they can see what is failing.

    Not sure that SSIS can do continuous in this way like replication.  This is (in my mind anyway) an absurd log file that loads about 1 million records every 2 hours.  OH, that reminds me there is something else that I would have to account for.  There is a job that runs every 2 hours, it grabs the last 10k records, truncates and loads those records back with fresh new IDs.


  • drbob wrote:

    There is a job that runs every 2 hours, it grabs the last 10k records, truncates and loads those records back with fresh new IDs.

    To what end?  What is the purpose of doing this?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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