July 21, 2020 at 5:07 pm
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
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
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
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
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply