Post reply

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

  • July 21, 2020 at 5:07 pm

    #3771817

    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 ?

  • July 21, 2020 at 5:14 pm

    #3771819

    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?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • July 21, 2020 at 9:37 pm

    #3771889

    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.

     

  • July 21, 2020 at 11:08 pm

    #3771922

    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.

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

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

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