Is linking servers a good idea?

Question

Is linking servers a good idea?

schep021

Hall of Fame

Points: 3538
More actions
December 13, 2007 at 10:53 am

#119292

We currently have two database servers for one of our applications. One is used for staging/development, and the other is the production server. I am writing scripts to automate the restore process and make it as painless as possible. I would be nice if I could query the production server from the staging server so that I know when and where the latest backup from the production database are (among other things). I have found a work around -- but, I am now looking into linking the servers.
Does anyone have any comments/concerns? Are there security risks that I should consider before doing this? Any info would be appreciated.
Thank you in advance!

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

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

JDixon-385826 SSCrazy Points: 2723 More actions · Answer 1

While linking servers are not ideal and most likely not the most efficient way of gathering data. If you use a login with minimal security rights to create the connection there is less of a concern. You could also create the link when you need it and drop it when you are done. using sp_addlinkedserver and sp_dropserver

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

Hi

One doubt - restoring from staging to production will restore the restore staging data to production. Is that ok ?

"Keep Trying"

Karthik-214069 SSChasing Mays Points: 631 More actions · Answer 3

One more thing with linked servers is, you can issue queries WITH (NOLOCK)....

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 4

I would not link the servers. Often people with rights to query staging do not have rights to production (nor should they). It's a potential security hole.

Instead I'd give rights to the backup drive on production and query the files directly. Alternatively, set up a job to copy the backup file from production to staging on a regular basis so you can easily check it. This gives you a second copy if something happens to production, you can pull it to tape from staging and lessen the load on production.

Be sure that you do not let everyone have rights to the backup file. This should be for (few) administrators only.