Linked Server from Test to Production

Question

Linked Server from Test to Production

PJ_SQL

SSCertifiable

Points: 6703
More actions
March 15, 2016 at 10:58 am

#325716

I am just reading data from one dB and inserting into another database.
Is it ok to create linked server on test environment to point to production and insert into table into test?
Would it hurt performance in prod?
Or is it wise to have both databases in Test environment and read and insert from same environment?

Viewing 8 posts - 1 through 7 (of 7 total)

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

JustMarie SSCertifiable Points: 7771 More actions · Answer 1

Why not use SSIS to stage the data from the source server to the destination server? Create a staging database (I prefer those but you can use schema in the same one if you like), data flow the data into the staging table, update destination.

ScottPletcher SSC Guru Points: 101101 More actions · Answer 2

You can reduce the effect on production by using "WITH (NOLOCK)" on the reads, unless you need an absolutely accurate copy of the data in test. If so, you'd be better off restoring a backup of the prod db to test anyway.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

PJ_SQL SSCertifiable Points: 6703 More actions · Answer 3

PJ_SQL

SSCertifiable

Points: 6703

March 15, 2016 at 2:08 pm

#1865447

Thank you

Eric M Russell SSC Guru Points: 125561 More actions · Answer 4

To copy rows from source table(s) to target tables(s) with minimal performance impact on both the source database (less blocking) and target database (less transaction logging), I would reccomend using BCP.EXE or SSMS Export Data Wizzard to bulk copy data from source database to a file on a drive other than the one used by your .mdf or .ldf files, and then BCP or Import file into table on target database. This is similar to the staging technique, except you are staging to file system rather than staging to intermediate tables.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

JustMarie (3/15/2016)
Why not use SSIS to stage the data from the source server to the destination server? Create a staging database (I prefer those but you can use schema in the same one if you like), data flow the data into the staging table, update destination.

Same question in reverse... why not just use a single INSERT/SELECT across a simple linked server?

Amplification... SSIS expands the surface area quite a bit and requires a whole 'nuther plane of logins for users. For this problem, you'd end up with a connection not unlike what a linked server would be, anyway.

--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)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 6

Eric M Russell (3/15/2016)
To copy rows from source table(s) to target tables(s) with minimal performance impact on both the source database (less blocking) and target database (less transaction logging), I would reccomend using BCP.EXE or SSMS Export Data Wizzard to bulk copy data from source database to a file on a drive other than the one used by your .mdf or .ldf files, and then BCP or Import file into table on target database. This is similar to the staging technique, except you are staging to file system rather than staging to intermediate tables.

You're a kindred spirit on this subject. IIRC, even SQL Server uses it for replication.

--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)

Eric M Russell SSC Guru Points: 125561 More actions · Answer 7

If you do create a linked server connection specifically for this purpose, then remember to drop it afterward or restrict it to only your login. Otherwise it could be abused by another user.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho