Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Dec 5, 2003

Linked Severs on MS SQL Part 3 - Page 2

By Don Schlichting

Impersonate

By using the Local Login dialog boxes, we can map local SQL users to remote users. The Impersonate checkbox maps the local user, (sa in this example), to the remote login "sa". The local login must match exactly with the remote login for this to work.

Remote User

One to one user mappings can be done by selecting a Local Login, leaving Impersonate unchecked and specifying a Remote User and Password.

From Query Analyzer, the security syntax is:

EXEC sp_addlinkedsrvlogin 'TEST1', 'false', 'donLocal, 'donRemote', 'remotePassword'

Sp_DropLinkedSrvLogin

To remove a linked server login, use the sp_droplinkedsrvlogin function. This function has only two arguments, the name of the server, and the login to be removed. The syntax for removing the login "donLocal" from above is:

EXEC sp_droplinkedsrvlogin 'TEST1', 'donLocal'

When the linked server is first created, a default login mapping is created. To remove this mapping, run the same statement using NULL as the user name.

EXEC sp_droplinkedsrvlogin 'TEST1', NULL

PERFORMANCE

Distributed queries will be slower than single server selects. The fact that we are communicating over the wire to remote machines implies a performance hit. However, there are methods to minimize performance problems.

  • Use the OpenQuery function rather than a direct reference. (See part 2 for OpenQuery examples.)

  • Execute stored procedures on the remote if possible. To run a remote stored procedure, use the syntax:

    SELECT * FROM OpenQuery(TEST1, 'northwind.dbo.[Ten Most Expensive Products]')
  • Avoid data conversion operations if possible.

  • Set Collation to true when both servers have the same sort order and character set. This will prevent the remote server from sending the entire table over the wire to the local server when a WHERE clause is used. The collation is an option of the sp_ServerOption function. For our example, the syntax would be:

    EXEC sp_serveroption 'TEST1', 'collation compatible', 'true'

Conclusion

As with all things programmed, the necessary security will add additional complexity. However, SQL has given us some very usable tools to deal with it.

Hey, is your BOL a little sparse on SQL to Oracle linked servers. Join us next month for Linked Servers Part 4: Oracle.

» See All Articles by Columnist Don Schlichting



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date