August 12, 2008 at 10:48 pm
...also, for example, you may be running Active Directory (AD) on a separate server, and if you have a need to import records from AD (like records of staff members) you could achieve this easily by setting up AD as a linked server.
Similarly, you may have separate servers for SQL 2000 databases and SQL 2005 databases, and need to pull data from one server to the other
my 2c 🙂
August 13, 2008 at 10:46 am
As others have pointed out, reporting is a big reason. Where I'm currently at (I'm a contractor) they have several cases where one SQL Server instance handles transactions and the linked SQL Server instance does the reporting. They also have replication setup so that updates occur on the reporting server very quickly. Each is on a node in sperate clusters so that should one instance go down the entire system won't be affected.
August 19, 2008 at 4:32 am
I'm told one other disadvantage (that I didn't see mentioned above) is that you cannot define RI constraints across linked servers. (I'd love to hear that is a misconception.)
August 19, 2008 at 10:01 pm
True enough... but if you have enough to physically seperate the data, you won't usually have any RI requirements between the two servers...
--Jeff Moden
August 20, 2008 at 8:00 am
another issue is naming conventions. if code gets developed in test that uses a four part name, that code will not work when moved to production unless the link exists there with the same name. so, choose your names wisely.
our experience has been that linked servers have more disadvantages than not. the biggest disadvantage is when developers construct code that relies on linked servers. their code may (or may not) have adequate performance, and there is an expectation that querying data remotely should be as fast as querying locally. should performance drop they expect our team to wave a magic wand and fix things quickly, without fully understanding what they have built.
----------------------
https://thomaslarock.com
July 16, 2009 at 6:26 am
Another reason is some companies are just pains. We have a particular program that uses a SQL server backend the company promotes this as a feature to allow you to access the data both through the program and backend for reporting. However they do not allow you to create another database on the server so you run into trouble as soon as you want to query the data in the database agaisnt anything not in it. the solution although annoying is using a second sql server and linking to this one.
There is no real reason not to allow a second database on the server they just use that as the standard line that the extra database could affect theirs which is silly really but a lot of companies say it.
July 16, 2009 at 7:03 am
peterthorpe81 (7/16/2009)
...so you run into trouble as soon as you want to query the data in the database agaisnt anything not in it....
Excellent point. The fact that so many "reporting" packages cannot accomodate external data sources makes me suspect they have never tried using their stuff in the "real world"!
July 17, 2009 at 11:38 am
The problem these companies are trying to avoid is having to support their own product in an environment they can't control. It can be difficult, and they can easily be blamed for things that just aren't their fault. It's a liability issue. Of course, if they were to TEST their products more thoroughly, then they'd have to learn how to provide more specific configuration advice to their customers, and probably not have such problems because then they'd have learned how to deal with it.
Steve
(aka smunson)
:-):-):-)
Jim Russell (7/16/2009)
peterthorpe81 (7/16/2009)
...so you run into trouble as soon as you want to query the data in the database agaisnt anything not in it....Excellent point. The fact that so many "reporting" packages cannot accomodate external data sources makes me suspect they have never tried using their stuff in the "real world"!
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
July 21, 2009 at 10:33 am
We use linked servers to compare data from backups on occasion. I'm in the unfortunate position of not being allowed to lock things down as tight as I'd like. As a result we sometimes have data disappear or get updated in ways that break our software. The nice thing about having a development server set up as linked is that I can run a restore onto the linked server from right before the problem happened then do an easy "insert into () select" or an update from the linked server.
Yeah, I could do a restore to another database on the same instance but that can cause local fragmentation on the drives or any other variety of performance issues. This method has worked for us for quite a while now. Worked well enough that I don't freak out when some developer asks "How hard would it be to fix all the records related to client 'x' on table 'y'?"
Also of note is that there's no reason I would need to leave the links in place other than for convenience since they are rarely used. But that convenience is awfully...convenient.
March 1, 2011 at 6:05 am
Hi I have situation, where in:
1. MS Dynamics App is running on SQL Server
2. Oracle Apps is running on Oracle,
Oracle Apps is the main application and MS Dynamics is the feeder. The financial details are to be taken to Oracle. Is it a better idea to use Linked Server concept here. Or should we go for other integration mechanism using any tools including SSIS/DTS or others. There is no need for a real time Data integration. It can be periodic.
Please let me know if any one has used any solution like these before.
March 1, 2011 at 6:13 am
If it can be asynchronous I'd go with a batch-style process (SSIS or some other tool) to avoid poking holes in database security.
That's just my take on it.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply