Linked Server vs Replication - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Linked Server vs Replication

Author

Topic

JohnBGood
Starting Member

48 Posts

Posted - 2011-04-11 : 15:33:58

We are inheriting a reporting infrastructure that relies heavily on Linked Servers. This was done as a security precaution - to keep report developers from hitting production servers directly.

I've asked why they did not use SQL replication. The answer was that SQL replication is against internal policy - we are not sure why.

Are there compelling reasons to go with SQL replication? The linked server infrastructure here seems to be working fine (although it seems to me much less secure than replication). Any insight would be much appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-11 : 15:35:20

Linked servers is a terrible strategy for a reporting environment. Performance is a huge problem.

Yes there are compelling reasons to go with transactional or snapshot replication for reporting, so you first need to figure out why that internal policy is in place.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

JohnBGood
Starting Member

48 Posts

Posted - 2011-04-11 : 15:45:24

Thanks!! I did a simple performance test - ran a "Select * from ViewName": first against the linked database, then hitting the Prod database directly. The query time was about the same. Can you elaborate just a bit on performance issues? are there other compelling reasons besides performance to use replication over linked db's.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-11 : 16:01:58

Linked servers do not offload the work to another server like replication can, so I see no point in using linked servers at all. Linked servers don't help with security either, so I'm not sure why it was even implemented this way.

As far as elaborating on the performance issues, well just do a search on linked servers and performance problems and you'll see lots of evidence. We get lots of questions here, so even just a SQLTeam search should show a bunch.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

JohnBGood
Starting Member

48 Posts

Posted - 2011-04-11 : 16:18:34

Makes perfect sense!! Thanks!!!

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-11 : 18:17:07

have you thought of using web service as source for your report. no replication no linked server involved. another option maybe?

If you don't have the passion to help people, you have no passion

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-11 : 18:20:54

Well web services should be done on a reporting instance that gets replicated to. The point of replicating to another environment for reports is to offload the work to another server. Using web services doesn't really alter the answer as far as I know.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-04-15 : 08:42:50

For Linked Server:

You do not need hardware
Restricted Object Access
There is no Extra maintenance Cost
There is no Extra DBA task like Log truncation and backup
Performance decrease due to Network Bottleneck if Linked Server exist on another Server

For Replication Issue

Suitable for Standby database,Reporting and DRP
Restricted objects access
Maintenance Cost
Hardware Cost
Performance decrease in both Mode as Transactional or Merger Replication

Regards,
Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources