:::: MENU ::::

Posts Categorized / Linked Servers and remote data

  • Sep 26 / 2013
  • 0
dbDigger, Linked Servers and remote data

Concerns to keep in mind and analyze before going for linked servers

Linked Servers are used in SQL Server to get connect and retrieve the data from outside your local server instance. We may link to other SQL Server as well as non SQL Server DBMS. It is a mature and proven technology. Rather than discussing the configuration method i will discuss some concerns that should be analyzed and take care while implementing the Linked Server.

  • Joining the data between local and linked server databases will result in poor performance. And performace may go worst if link is on WAN.
  • Execution of procedure on remote server will leak memory on local server for each call from it. It is due to return status of procedure even if it is not used by the caller. This leak is very minimal about 40 Bytes per call but may accumulate over the time.
  • SQL variant data types also cause minor memory leaks for local server. Large character strings of this type may accumulate considerable memory leaks over the time.
  • Performance – Optimizing is always a challenge and distributed queries are even more difficult. An extra complication with linked servers is that to get statistics from the remote server, OLE DB runs DBCC SHOW_STATISTICS. But this command requires ‘sysadmin’ permission or membership in ‘db_owner’ or ‘db_ddladmin’, which is not expected from a regular user to have, no matter he accesses the linked server with his own credentials, or through a proxy account defined.
  • There are a number of SQL Server features that are not supported with linked servers
    • XML data type
    • Table-valued parameters
    • CLR User-defined data types
    • Cannot call table-valued functions with four-part notation
    • MAX parameters cannot be OUTPUT
    • BULK INSERT into linked server is not supported. More details here
  • Error messages with linked servers and distributed transactions are often very obscure. Often it includes an OLE DB error, which is difficult to understand. Also, the useful part of the error message is often only raised with level 10, so you cannot catch it. A prime example is when a remote call times out.
  • Distributed transactions – The MSDTC has following problems:
    • Cannot use SAVE TRANSACTION in a distributed transaction
    • Some errors when a distributed transaction fails are not catchable
    • Once you have rolled back locally, there is very little you can do, for instance you cannot call a stored procedure
  •  Security and permissions for linked servers will require extra effort and skill.
  • Sep 23 / 2013
  • 0
dbDigger, Linked Servers and remote data, System Administration, Windows Server

Copy paste not working during remote desktop sessions

I use Remote Desktop utility to get connect to the online servers if required. While using the remote desktop it enables us to share the clipboard among both the OS to copy the data. A couple of days back i noticed that i am no more able to copy data from or to my laptop from the server connected through remote desktop. It was problematic and first thing that came to mind was that i should verify the local resources settings in my remote desktop connection panel. It had following settings and nothing was wrong here

Data copy not working on remote desktop session

So now i had to google for any further clue and I found the reason in a minute by googling. I performed following steps to solve the issue.

  • Login to the remote server on Remote Desktop
  • Open task manager (by right clicking on taskbar)
  • Go to Processes tab
  • Search the process RDPClip.exe
  • End the process RDPClip.exe
  • Go to Applications tab in task manager
  • Click on New Task and start RDPClick.exe by entering in box

The issue is solved here. We may also implement the same  solution through following cmd commands for the same tasks
Taskkill.exe /im rdpclip.exe

  • Apr 25 / 2012
  • 0
dbDigger, Linked Servers and remote data, T-SQL Tips and Tricks

Accessing linked server data in SELECT or JOIN clause

Configured Linked servers allow to access heterogeneous and multiple source data. We may directly select, join the linked data with our local server data. Linked server may be configured for any OLEDB data source.
Once linked server is configured and working, here is a simple syntax to access its data in select or join  statement of T-SQL. For following linked server

Linked server name            DBASS2008
Database Name                    LinkedDB
Schema                                  dbo
Table                                      Linked table

To access the data in above provided linked server we would have to use fully qualified four part name pointing to linked server. It would be like [ServerName].[DBName].[SchemaName].[ObjectName]

select * from [DBASS2008].[LinkedDB].[dbo].[Linked table]

Same qualified syntax may be used while accessing data in join. If valid qualified syntax is not used then error message would be generated related to invalid object.

  • Sep 09 / 2008
  • 1
dbDigger, Linked Servers and remote data

ACCESSING REMOTE DATA SOURCE (Linked Servers and Ad Hoc Queries)

In some situations a SQL Server DBA is required to fetch data from another data source. Other data source may be another instance of SQL Server or any other RDBMS. Also it may be a file. SQL Server 2005 provides two major ways to access data from other data sources. These are

  • Linked Servers (may use distributed queries)
  • Ad hoc queries with openrowset

In following elaboration of using these two ways I will discuss SQL Server as remote data source.

Linked servers provide SQL Server with access to remote data sources that may be SQL Server also or any other relational DBMS or text file. Remote data sources are connected through OLE DB provider. OLE DB selection determines that what kind of distributed query operations may be implemented.

Create Linked Server
To create the linked server, use the system stored procedure sp_addlinkedserver. To create a linked server for a SQL Server instance named test on host DBA, we will have following format

EXEC sp_addlinkedserver @server= 'dbatest',  
 @srvproduct= 'SQL Server'  

Or if it is default instance then you may just enter host name instead of hostinstance format.

Change Any property of a linked server
After a linked server has been created then you may change its several properties like collation compatibility, collation name, connection timeout, data access and query time out etc. For example to change query time out property to 60 seconds for the created linked server, I will use following system stored procedure

EXEC sp_serveroption  
 @server = 'dbatest' ,  
 @optname = 'query timeout',  
 @optvalue = 60  

View meta data of a linked server
Now we have a linked server with query time out property changed to 60 seconds. In order to view the meta data of this linked servers use following script.

SELECT [name], data_source,query_timeout, modify_date  
 FROM sys.servers  
 WHERE is_linked = 1  

Configure Log In of a Linked Server
Now we have a linked server attached. But to access the data on it we have to map proper logins as required. In following script I have mapped the user dba (my local user) to user sa of linked server (remote data source). If I set the values of parameter @locallogin = null then all my local users will be mapped against the user sa of remote data source. If yoy set the parameter @useself = true then windows authentication will be used for connection to linked server. Also we have to provide password for remote user that is sa in this case.

EXEC sp_addlinkedsrvlogin  
 @rmtsrvname = 'dbatest',  
 @useself = false ,  
 @locallogin = 'dba', -- if null Applies to all local logins  
 @rmtuser = 'sa',  
 @rmtpassword = 'test'  

View Login Information Of Linked Server
To view the login mapping for remote server use the following script.

SELECT s.name LinkedServerName,  
 ll.remote_name, p.name LocalLoginName  
 FROM sys.linked_logins ll  
 INNER JOIN sys.servers s ON  
 s.server_id = ll.server_id  
 LEFT OUTER JOIN sys.server_principals p ON  
 p.principal_id = ll.local_principal_id  
 WHERE s.is_linked = 1  

Executing the Queries
Now linked server is configured and logins are also mapped properly. It is now time to execute remote queries as it is actual purpose of all these configurations. Distributed queries reference one or more linked servers. And perform read or update operations against remote tables, views, or stored procedures. The types of query operations that are supported against linked servers depend on the level of support for transactions present in the OLE DB providers used. The basic syntax for referencing a linked server is using a four-part name. To fetch data from pubs.authors of dbatest server

SELECT * FROM [dbatest].pubs.dbo.authors  

To execute a system-stored procedure on the linked server

EXEC [dbatest].master.dbo.sp_monitor  

Tired of using four parts name?
If it is not convenient to use four parts lengthy name then you may create a synonym for whole name. To create a synonym mySyn for whole four parts name

CREATE SYNONYM testSynonym  
 FOR [dbatest].pubs.dbo.authors  

It is relevant to mention here that SQL Server provides another way to execute distributed queries other than using the four parts naming method. OPENQUERY is a function that issues a pass-through query against an existing linked server and is referenced in the FROM clause of a query just like a table. The syntax is as follows:

OPENQUERY ( linked_server ,'query' )  

Wipe Out
Now it is time to see that how to drop the created objects like linked servers, login mappings and synonyms.

--To drop the mapped login that is in our case dba  
 sp_droplinkedsrvlogin 'dbatest' , 'dba'  
 --To drop the linked server dbatest  
 sp_dropserver 'dbatest'  
 --To directly drop linked server along with all mapped logins  
 sp_dropserver 'dbatest', 'droplogins'  
 --To drop the synonym mySyn for linked server four parts name  
 DROP SYNONYM testSynonym  

In some cases it is not required to retain the connection from linked server for a long time. So to execute such Ad-hoc queries OPENROWSET command is used. It is referenced in the FROM clause and acts like a table in a SELECT statement. OPENROWSET creates an ad hoc connection to the data and does not use an existing linked server connection to query the remote data source.
This property to use OPENROWSET to query a remote data source is off by default in SQL Server 2005. You may turn it on through surface area configuration.

To perform same select operation from pubs.authors database on dbstest, we have following format of OPENROWSET

 FROM OPENROWSET('SQLNCLI','dbatest';'sa';'test',  
 'SELECT * from pubs..authors')  
Consult us to explore the Databases. Contact us