August 8, 2008 at 3:55 pm
I'm trying to figure out why someone would use a linked server over just having another database on the same server. What might be the advantages to using a linked server? Are there disadvantages to using a linked server? If you have to separate your data into two different databases why not just have them on the same server instead of splitting them across two different servers?
Any thoughts?
August 8, 2008 at 4:01 pm
It kind of depends on the data. In many instances, the data represents 2 different logical sets of data; hence, you have 2 databases. The reason for using linked servers over putting both DBs on the same SQL instance would be to spread the workload out over 2 physical servers for scalability and performance. You can run into poor performance with linked servers depending on the queries/operations that reference the linked server since it has to potentially combine data from 2 seperate data sources in one query.
August 8, 2008 at 6:13 pm
So the values is to spread out the load across two server. The drawback would be the cost of joining data across servers would be slower, then joining across databases on same instance. Is that what your are saying?
What about security? Is is easier with linked servers or more complicated?
August 8, 2008 at 6:22 pm
Heather (8/8/2008)
So the values is to spread out the load across two server. The drawback would be the cost of joining data across servers would be slower, then joining across databases on same instance. Is that what your are saying?What about security? Is is easier with linked servers or more complicated?
Users are built on both instances, and mapped to each other. So - security can be no worse than in a single server environment, but you have to work twice as hard at it....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 9, 2008 at 5:40 am
Heather (8/8/2008)
I'm trying to figure out why someone would use a linked server over just having another database on the same server.
Here is a reason...
Picture this scenario, you work in an organization where there are different servers serving different departments then somebody says "hey dude... we need a reporting database able to show a complete picture of the organization"
So... what you do?
You build your Reporting server, define all those departmental -operational Servers as Linked Server and run a daily ETL process that moves summary data from the operational servers to the Reporting server... you massage the data you got from all of them and expose a "complete picture of the organization" to whoever wants -and has the credentials to see it.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 9, 2008 at 7:22 am
This makes sense for a reporting purposes, but why would someone do this for two operational databases?
August 11, 2008 at 9:47 am
Well, think about a federated server model where you've got identical copies, in terms of schema, of databases on multiple physical servers to spread the processing load out over the federated model. Each database physically is the same, but houses different sub-sets of data. Say you've loaded data into database A and database A appears to be overworked whereas database B is sitting idle. You could INSERT INTO dbB SELECT FROM dbA using a distributed transaction and move data over to database B where it can be processed quicker. I have a client that uses this model to spread workload out accross multiple servers and from time to time, they need to move data from one to another. I'm sure there are more examples of why someone may choose to do this. Just like any other choice, it may not be the best choice, but someone went down that path so that's how it is getting done.
August 12, 2008 at 6:27 am
If you have an environment with more than SQL Server in it, you may need to use linked servers to connect to Oracle, DB2, etc for data.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
August 12, 2008 at 8:04 am
My company had one app's SQL 2005 database on one server that needed to look up stuff in another app's SQL 2000 database on another server. We did the linked server thing and performance sucked, even after some optimization. We ended up duplicating the remote lookup tables into a local copy of the data that gets updated daily. This was a couple of years ago so maybe today would be different. Dunno.
Wayne
August 12, 2008 at 8:56 am
All good comments from this here is what I got so far. What other Advantages/Disadvantages might there be:
Advantages:
- A way to separate load
- A way to join two different databases into a single view
- A way to join across heterogenous data sources
Disadvantages
- More hardware
- More security work
- Possible performance issues
August 12, 2008 at 10:09 am
i think a good summary. for the second one i can add something. Imagine that you have 200 databases but they have different characteristics so you have to classify them. You create 10 servers. And in your company there are several statisticians who makes several queries during the day between the databases on different servers. So you can't copy all databases on their PC. You give some rights for them to query the servers. In this case you don't have the chance to criticize the advantages of linked server, you HAVE TO use it.
August 12, 2008 at 10:10 am
i think a good summary. for the second one i can add something. Imagine that you have 200 databases but they have different characteristics so you have to classify them. You create 10 servers. And in your company there are several statisticians who makes several queries during the day between the databases on different servers. So you can't copy all databases on their PC. You give some rights for them to query the servers. In this case you don't have the chance to criticize the advantages of linked server, you HAVE TO use it.
August 12, 2008 at 10:14 am
i think a good summary. for the second one i can add something. Imagine that you have 200 databases but they have different characteristics so you have to classify them. You create 10 servers. And in your company there are several statisticians who makes several queries during the day between the databases on different servers. So you can't copy all databases on their PC. You give some rights for them to query the servers. In this case you don't have the chance to criticize the advantages of linked server, you HAVE TO use it.
August 12, 2008 at 8:48 pm
Heather - even though you can reference a linked server from a local query and have it produce results that are logically the same as if the remote table was local, under the covers there is a BIG difference in how your results are calculated. Because some of your tables/views are on the remote side and some on the local you're dealing with 2 seperate DB engines, and don't have the ability to do nice fast hash/merge/sorts in memory to compare all of the sets. Some of the data is going to have to be dragged from the remote side across the network to your local DB engine - exactly how much depends on the columns involved in your SELECT, JOIN and WHERE clauses. For example (assuming a remote SQL Server), if all criteria and columns referenced on the remote side are non-character (eg int, decimal, etc.) the remote server can do a lot of the grunt work and only return matching rows for the local server to continue to process. However if your criteria references remote character data and your linked server is not collation compatible the remote side has to send over ALL columns and rows involved so that the local engine can resolve your criteria using local collation (as collation differences could potentially produce different results).
For this reason I often find it best (fastest) to use only stored procedures across a linked server connection, rather than referencing remote tables and views directly. Using INSERT... EXEC... or EXEC with output params on the remote end you cause the execution of the remote part to be entirely constrained to the remote server, and only the results come back across the wire to the local server for further processing.
Even on a fast local LAN performing a linked server query between two servers is an order of magnitude slower in most cases, and often hundreds of times slower than performing the same query with all objects on the one SQL instance (assuming the same schema and data in both examples).
So to your list:
- A way to separate load. Yes, kind of if the servers already exist and you have no other choice (even then you will cause load on both local and remote ends while executing the linked server query - and if you're locking anything on the local end it could be a long time if the remote side is slow). Definitely no if you're designing a DB system from scratch and are trying to spread processing load around. Look at replication, DB mirroring or log shipping instead.
- A way to join two different databases into a single view. Logically yes, but performance will generally suck. Consider replicating the remote data to the local server and referencing it instead.
- A way to join across heterogenous data sources. Can't argue with this one - sometimes a linked server is the only easy way to reference foreign data. In this case consider syncing changes into a local copy from the linked server and referencing the local copy in your queries.
Where linked server queries can shine is to sync remote data into a local source. If true replication, log shipping or DB mirroring isn't suitable for your scenario they are a good way to syncronise data. We use this methodology over slow WAN links and it works nicely, especially if you follow some simple guidelines: use only procs on the remote side, always pull from local rather than push to remote, be smart about your syncing criteria (eg use rowversion columns to detect changes), etc. Then you have a nice discrete syncing mechanism and all of your real queries reference the locally synced tables for maximum performance.
Regards,
Jacob
August 12, 2008 at 10:20 pm
Heather (8/9/2008)
This makes sense for a reporting purposes, but why would someone do this for two operational databases?
Physical security, performance enhanced by having two boxes instead of just 1, different hardware connections to the outside world, upgradability or not (I've run into customers that want to stay with SS2k instead of upgrading to 2k5) even though you can have more than 1 instance (why not a separate box, in that case?), possibility of hot spare, etc, etc.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply