Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

An Introduction To Linked Servers Expand / Collapse
Author
Message
Posted Friday, June 8, 2007 10:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 25, 2014 5:06 PM
Points: 8, Visits: 1,073

Yes - it can link to multiple servers.

Perhaps this snippet from Books On Line will help:

Relational Database Engine Architecture Overview

Database Engine Components

The relational database server of Microsoft® SQL Server™ 2000 has two main parts: the relational engine and the storage engine. One of the most important architectural changes made in SQL Server version 7.0 was to strictly separate the relational and storage engine components within the server and to have them use the OLE DB API to communicate with each other.

So, in a sense, you have been using a linked server already.
The local SQL server has the local relational engine “linked to” the local storage engine.

You can choose whether the linkage to the linked server is over ODBC or OLEDB.
It depends which driver you use/how you define the datasource.

For each linked server in my care I have a folder with setup scripts and basic test scripts.
Sample contents:

sp_linkedserver
go
sp_tables_ex 'servername'
go
sp_columns_ex 'servername'
go
select from a sample table using four part name

Remember that in query analyzer that the selected/highlighted area is executed (F5) not the whole script, so I can pick out the line(s) of interest or just let the whole thing rip.

Also, I set up a database with views so that an SQL query/script that will run in query analyzer on the target linked server host can run directly. Obviously, if the target linked server is not SQL server, then I have to be selective about what is included.

I like to have a table in each database just for test purposes. This is nothing fancy, couple of columns and three or four rows.

All in all, I can quickly verify if everything is working.
Alternatively, I have actual proven syntax in front of me if something is wrong and my in the middle of the night mental table name, database name and syntax index search isn't quite functioning properly.

On my PC I have a test database that is mirrored in MS Excel, MS Access, MS SQL server 2000, MS SQL Server 2005, DB2 9, MySQL, Oracle 10g, Sybase ASE 15 and Unisys ClearPath DMSII 51.1.

Both the SQL Server 2000 and SQL Server 2005 have these databases as linked servers.

The setup is scripted and so is the basic functionality testing.

Roy

Post #372433
Posted Wednesday, June 13, 2007 3:19 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 12:24 PM
Points: 184, Visits: 217

Good article!

Can anyone tell me if you can link from SQL Server 2000 to 2005 (not 64 bit) and run queries from SQL Server 2000 against the 2005 server?

Thanks 

Post #373689
Posted Wednesday, June 13, 2007 3:59 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 1, 2011 2:05 PM
Points: 32, Visits: 47

Good article, I learned something about OPENQUERY, which I had not used before.

A few things I have identified while doing linked server queries. 

  • Queries run over the local server linking to tables on the linked server take a performance hit timewise.  A query I have took 41 minutes to complete over the link, while when executed locally on the linked server took only 35 seconds.  It does depend on your tables and how much data is travelling across the link.
  • The OPENQUERY statement cannot include parameters or temp tables from the local server as part of the OPENQUERY statement.
  • You cannot specify an index or locking hint for a remote data source (i.e. "WITH(NOLOCK)" for example).
  • You must specify SET ANSI_NULLS ON  and SET ANSI_WARNINGS ON

Thanks for the article, some useful stuff.




Post #373695
Posted Wednesday, June 13, 2007 4:20 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
Just tried it.  Seems to work.  Use the cautions that the others have suggested here.  Pretty much if you can pull the data through ODBC it should work.

ATB

Charles Kincaid

Post #373704
Posted Wednesday, June 18, 2008 7:07 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 10:00 PM
Points: 19, Visits: 95
Hi, This is my first post to this Forum.... I am trying to get my head around linked server, stored procedures .... I'd like to see an example of a stored procedure that: 1) links to 'remote' SQL2005 server db 2) performs an update or insert on a linked table and a local table with rollback of the whole transaction if one of the updates / inserts fails (and if possible: give appropriate error message as to which of the updates / inserts in the transaction failed)..... Can someone please help out with this? thanks....
Post #519543
Posted Sunday, June 22, 2008 11:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 25, 2014 5:06 PM
Points: 8, Visits: 1,073
chinanihc says:
I am trying to get my head around linked server,

The short answer is get dotted.:)
This script fragment reviews how one can access a table with increasing dottiness.

Use Pubs
Select au_lname, au_fname
from Authors
-- Include owner:
Select au_lname, au_fname
from dbo.Authors
Use Northwind
-- Accessing table in a different database:
Select au_lname, au_fname
from Pubs.dbo.Authors

All selects return the same data.

Let us assume that the server that ran the above script has now been connected to another server as the linked server Lippman.

Select au_lname, au_fname
from Lippman.Pubs.dbo.Authors

So, all you need to do is to use four-part naming:
LinkedServer.Dbname.Owner.tablename
instead of:
tablename
in Select, Insert, Update and Delete statements.

Let me add that it may help both you and your users if a view is defined to simplify the above.
For example:

Create view Lippman_Pubs_dbo_Authors 
as
Select au_lname, au_fname
from Lippman.Pubs.dbo.Authors
go

Now the table on the linked server looks like a table on the local server.
This is how to access it:
Select au_lname, au_fname 
from Lippman_Pubs_dbo_Authors

Now, my mechanical replacement of dots by underscore leaves room for improvement/consolidation into your site's naming conventions.

I often create a separate database consisting of the views to the linked server. I'd be likely to call that database Lippman_Pubs and the view Authors.

Access is then
Select au_lname, au_fname 
from Lippman_Pubs.dbo.Authors

So, now this looks like a simple access to another database.
If you didn't include the servername as part of the database name (in my example: Pubs instead of Lippman_Pubs), maybe some people woulld never realise the data was on another server.
Post #521431
Posted Sunday, June 22, 2008 11:37 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:31 AM
Points: 4,358, Visits: 9,537
In the above, it is mentioned that you should create views to access your linked servers. That is good, but I would go one step further and define synonyms for the objects on the linked server you use regularly (SQL Server 2005 and greater). I would also create those synonyms in a different schema - just to separate things:

CREATE SCHEMA LinkedServer AUTHORIZATION dbo;
GO

CREATE SYNONYM LinkedServer.ObjectName FOR LinkedServer.Database.schema.object;
GO

Then, it can be used as:

SELECT * FROM LinkedServer.ObjectName;

One advantage to this approach is that you could easily modify the synonyms and redirect them to a different linked server. For example:

DROP SYNONYM LinkedServer.ObjectName;
CREATE SYNONYM LinkedServer.ObjectName FOR LinkedServer2.Database.schema.object;
GO

Now using 'SELECT * FROM LinkedServer.ObjectName' will select data from LinkedServer2 instead of LinkedServer.

Jeff


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #521437
Posted Sunday, June 22, 2008 11:51 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,294, Visits: 9,480
Jeffrey Williams (6/22/2008)
In the above, it is mentioned that you should create views to access your linked servers. That is good, but I would go one step further and define synonyms for the objects on the linked server you use regularly (SQL Server 2005 and greater).

Actually, I would call that "one step less far", given that Synonyms are less flexible than Views and not usable in all of the cases that Views are. I am curious why you would recommend Synonyms over Views, as I honestly cannot think of a single advantage that they have versus Views?


One advantage to this approach is that you could easily modify the synonyms and redirect them to a different linked server. For example:

DROP SYNONYM LinkedServer.ObjectName;
CREATE SYNONYM LinkedServer.ObjectName FOR LinkedServer2.Database.schema.object;
GO

This is an advantage, but not when compared to Views, because you can do the same thing with them.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #521440
Posted Sunday, June 22, 2008 12:07 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:31 AM
Points: 4,358, Visits: 9,537
I am not sure where synonyms cannot be used that views can. Any examples? I like the ability to abstract out access to linked servers and/or other databases using synonyms. This allows for easier movement when needed.

One system I work with can be built to access a database locally or through a linked server. For instances where this database is remote, we have to run a script (provided by the vendor, of course) that modifies all views and stored procedures by adding in the reference to the linked server.

Let's say we have three systems (I have more than this). The DEV system has all databases local, the QA system has this one database on a linked server and live also has this database on a linked server.

The code on DEV uses 'database.schema.object' to reference the tables. The code in QA uses 'qalinkedserver.database.schema.object' and the code in live uses 'livelinkedserver.database.schema.object'.

In this scenario - any changes to the code in DEV cannot be moved to QA or even on to LIVE as is. It must be modified in each environment for that environments specific linked server.

Using schemas and synonyms makes the above a lot easier to manage because all code stays the same, regardless of whether or not the system is using a linked server.

Now I know we can create the linked servers the same on each environment - but that also has it's own problems with additional management.

The only place I see that views would work better is when you need a query that accesses multiple objects. In that case, you are either going to create the view in the source database and use a synonym to reference the view - or you are going to create the view in the local database and reference the view. If you reference the view locally - you have the option of accessing the objects through the linked server directly, or through the synonyms that you have created.

So, yeah - I think it is one step further because it adds additional options.

Jeff


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #521441
Posted Sunday, June 22, 2008 12:43 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,294, Visits: 9,480
Jeffrey Williams (6/22/2008)
I am not sure where synonyms cannot be used that views can. Any examples?

Synonyms can not use Schema-binding nor be referenced by schema-bound objects. That means that if you decide to impose schema-binding on an application schema in the future, you will have to remove all of the synonyms. So that's one less option.


I like the ability to abstract out access to linked servers and/or other databases using synonyms. This allows for easier movement when needed.

Views do exactly the same thing, only with more options and more options for the future.


One system I work with can be built to access a database locally or through a linked server. For instances where this database is remote, we have to run a script (provided by the vendor, of course) that modifies all views and stored procedures by adding in the reference to the linked server.

Let's say we have three systems (I have more than this). The DEV system has all databases local, the QA system has this one database on a linked server and live also has this database on a linked server.

The code on DEV uses 'database.schema.object' to reference the tables. The code in QA uses 'qalinkedserver.database.schema.object' and the code in live uses 'livelinkedserver.database.schema.object'.

In this scenario - any changes to the code in DEV cannot be moved to QA or even on to LIVE as is. It must be modified in each environment for that environments specific linked server.

Using schemas and synonyms makes the above a lot easier to manage because all code stays the same, regardless of whether or not the system is using a linked server.

This is no different from using views in your schema instead of synonyms. You just change a view defintion instead of changing a synonym defintion. No difference.


So, yeah - I think it is one step further because it adds additional options.

??? Huh? You still haven't named a single thing that a Synonym referencing tables & views can do that a View cannot do! You could replace "synonym" with "view" in everything that you said and it would still be true. How is that more options?

As for views, A) They can be schema-bound and be referenced by schema-bound objects, and B) besides acting as an unfiltered alias, as Synonyms do, Views can also:
1) control the column list returned
2) filter the rowset returned
3) collapse relational references to other tables
4) control which columns are writeable
5) elaborate hiearchies
etc., etc...

And these options are available now and anytime in the future, so more flexible, more options and useable in more situations.

Now I will grant you, that for all non-Table-valued objects that Synonyms work on they are the aliasing tool of choice, but for Views & Tables, I still cannot see one reason to prefer Synonyms over Views.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #521446
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse