en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP

 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize

Recent Entries

Minimize
יונ14

Written by: ronen ariely
14/06/2011 17:13 RssIcon


linked server

התחברות למקור נתונים מרוחק באמצעות linked server

linked server הוא אלמנט בשרת המאפשר לנו לבצע התחברות למקור נתונים חיצוני או מקומי. התחברות זו מבוססת על שימוש בשרשרת התחברות ועבודה עם אחד ה providers להתחברות המוגדרים במערכת (ראה הערה בהמשך). התחברות זו מאפשרת לנו לגשת ישירות מתוכנת  ה SSMS למסדי נתונים שונים כגון MySQL או אורקל, לגשת לשרת SQL אחר, לגשת ל instance שונה בשרת שלנו, או בקיצור לגשת לכל קובץ/תוכנה הנתמכים בהתחברות  בעזרת provider מתאים (כן אפילו קבצי אקסס או אקסל או קבצי טקסט פשוטים)

הוספת linked server ניתן לבצע בצורה גרפית בתוכנת ה SSMS או בצורה המועדפת עלי בשאילתת SQL ועבודה עם הפרוצדורה השמורה sp_addlinkedserver.

אז מה קורה כאשר מוסיפים linked server?

הוספת linked server מוסיף/רושם את מקור הנתונים שלנו לרשימת ה- sysservers. השאילתה הבאה תציג לך את כל השרתים אליהם אתה יכול לגשת בשאילתות (כל השרתים הרשומים כרגע):

select * from sysservers

* כאשר אנחנו פותחים את תוכנת ה SSMS ומבצעים התחברות לשרת מקומי הרי שתוצאת שאילתה זו תציג לנו כברירת המחדל תמיד רשומה בודדת. זה המצב הרגיל בו אנו עובדים אחרי התקנת השרת. עם זה אנחנו יכולים להוסיף מספר רב ככל שרוצים של מקורות מידע שונים ולחבר אותם לשרת המקומי שלנו בעזרת linked server. לאחר שמוסיפים שרת (או כל מקור נתונים) ל sysservers אפשר לעשות בו שימוש רגיל לכל דבר. בצורה זו ניתן לבצע פעולות בין אלמנטים שונים הנמצאים בשרתים שונים כגון ביצוע JOIN של טבלה מאקסס עם טבלה משרת SQL.

הוספת linked server בצורה גרפית:

1.       נמצא את רשימת ה Linked Servers שלנו: Server Objects -> Linked Servers

Add New Linked Server

נלחץ על כפתור ימני של העכבר ונבחר ב “New Linked Server”

2.       טופס יצירת Linked Server יפתח לנו

Linked Server Settings

2.1.    נשלים הגדרות כלליות (פרטים בנספח למטה)

2.2.    נפתח את הטאב של Security ונוסיף הגדרות מתאימות (פרטים בנספח למטה)

Linked Server Security Settings

2.3.    נלחץ על OK ליצירת ה Linked Server החדש שלנו

 

הוספת linked server בעזרת שאילתה ושימוש ב sp_addlinkedserver

מייקרוסופט הכינו עבורנו פרוצדורה שמורה לא מתועדת בשם sp_addlinkedserver להוספת Link Server.

בקישור הבא ניתן לקרוא על הפרמטרים השונים בהם ניתן להשתמש:

http://msdn.microsoft.com/en-us/library/ms190479.aspx

במדריך זה מייד לאחר החלק התיאורטי נציג בצורת צעד אחרי צעד חלק מרכזי מהאפשרויות שיש לנו.

כיצד נריץ שאילתות על השרת שרשמנו במערכת?

על מנת להריץ שאילתות בשרת שהוגדר עם sp_addlinkedserver ניתן להשתמש ב OPENQUERY או ישירות בעזרת שם השרת המוגדר ב Linked Server שלנו. לשיטה זו נקרא four-part name מכיוון ששם האלמנט אליו נרצה לגשת יוצג בעזרת מסלול מלא הכולל 4 אלמנטים:

 "שם קישור" -> "שם מסד" -> "שם סכמה" -> "שם הטבלה או אלמנט אליו רוצים לגשת". לדוגמה:

 select * from MyLinkedServer.MyDataBase.dbo.MtTbl

הגיע הזמן למעט קוד...

לפני שנתחיל להגדיר דברים חדשים במערכת נבדוק את המצב הנוכחי שיש לנו ונלמד 2 שאילתות שילוו אותנו בכל התהליך לבדיקת השינויים שביצענו:

select * from sys.servers -- יחזיר לנו את רשימת השרתים המוגדרים כרגע

select * from sys.linked_logins -- יחזיר לנו את רשימת מיפויי ההתחברות לשרתים
GO

דרך נוספת לביצוע השאילתה הראשונה:

select * from sysservers

GO

כמו כן נבדוק מה שם השרת המקומי שלנו:

print @@servername

דוגמה 1: תזכורת לעבודה עם נתונים בשרת מרוחק בעזרת OPENROWSET

הערה: באחד הבלוגים הקודמים באתר רשמתי באופן מפורט כיצד עובדים בעזרת OPENROWSET לגישה לנתונים חיצוניים. מומלץ לעבור על נושא. במקטע זה במריך אני רק אציג בקצרה דוגמה בסיסית לעבודה בעזרת OPENROWSET

SELECT a.*

FROM OPENROWSET(

      'SQLOLEDB'

      ,xxx.xxx.xxx.xxx';'UserName';'Password'

      ,'select * from MyDataBase.dbo.MyTbl) AS a

GO

* במקום xxx.xxx.xxx.xxx יש לרשום את הכתובת לשרת המרוחק

עבודה עם OPENROWSET עדיין אינה גמישה כמו העבודה עם Linked Server כפי שנראה בהמשך. העבודה עם Linked Server למעשה מוגדרת ברמת השרת ומאפשר גישה למקור הנתונים המוגדר בו ללא צורך בהגדרה מקומית נוספת. מה גם שאופן הגישה לנתונים גמיש לחלוטין החל מקביעת שרשרת התחברות מלאה

דוגמה 2: הגדרת Linked Server לשרת המקומי:

נגדיר לינק סרבר לשרת המקומי שלנו:

--Add a Linked Server to local server
exec
sp_addlinkedserver

      @server='MyLinkedServer'

      , @srvproduct= ''

      , @provider='SQLNCLI'

      , @datasrc='MyServerName'

      , @location=''

הסבר פרמטרים:
- MyLinkedServer: שם כלשהו חופשי שאנחנו בוחרים עבור הקישור שיוצרים
- SQLNCLI: התחברות למקורות מידע נעשית בעזרת Provider-ים שונים המוגדרים לנו במערכת. הסבר נוסף בהמשך. בינתיים נסתפק בכך ש SQLNCLI הוא Provider המהיר והטוב ביותר אבל הוא מתאים רק להתחברות למסד נתונים מסוג SQL (השם המלא שלו הוא SQLCLIENT)
-
MyServerName: יש לרשום את השם של השרת הנוכחי שלנו כפי שמצאנו בהתחלה בעזרת @@servername. במיקום זה ניתן לקבוע את ה INSRANCE אליו רוצים לגשת Servername/Instance או פשוט לגשת לברירת המחדל בשרת.

עתה נוכל לגשת ולעבוד עם הנתונים בשיטת four-part name. נריץ למשל שאילתת בחירה פשוטה:

select * from MyLinkedServer.MyDataBase.dbo.MtTbl
go

או נוכל להציג את רשימת מסדי הנתונים שיש לנו בשרת אליו התחברנו:

sp_catalogs @server_name = 'MyLinkedServer'

select * from MyLinkedServer.master.dbo.sysdatabases

GO

חשוב! בדיקה זו תוכל לעזור לנו לוודא שאכן ההתחברות הצליחה כשניגש למשל לשרתים מרוחקים. יש לשים לב שיצירת Linked server תצליח גם אם שרשרת ההתחברות לשרת אינה נכונה. ההתחברות לשרת למעשה מבוצעת רק בזמן ההרצה של השאילתה שעושה שימוש בלינק שלנו ולא בזמן יצירת הקישור. לכן עדיף תמיד לבדוק מייד לאחר יצירת הקישור שאכן הכל עובד טוב.

חשוב! אין מגבלה למספר הקישורים שניתן לרשום לכל שרת. ניתן להגדיר מספר קישורים לאותו שרת, ובפרט לשרת המקומי שלנו כשאר בכל קישור נעזרים בפרמטרים שונים. למשל שרשרת התחברות שונה, משתמש שונה ועוד.

על מנת ליצור קישור נוסף כל מה שעלינו להקפיד לשנות את השם של הפרמטר @server. כמו שלא נוכל להדיר 2 טבלאות או כל 2 אלמנטים באותו שם הרי שגם לינקים צריכים להיות בשם שונה.

נבדוק את השינויים שביצענו בשרת:

select * from sys.servers

select * from sys.linked_logins

 

ניקוי השינויים שביצענו:

בדיוק כמו שהגדרנו לינק חש כך גם את המחיקה ניתן לבצע בעזרת ה SSMS בצורה גרפית או בעזרת שאילתה. ניסיון מחיקה של לינק בעזרת הממשק הגרפי יגרור הודעת אזהרה ששואלת אותנו האם למחוק את כל המיפויים של ההתחברות עבור שרת זה. דרך זו קלה ומאפשרת גם למחוק במצב בו מישהו כרגע מחובר לשרת (כמובן שזה יזרוק אותו מהעבודה ולכן יש לפעול בזהירות).

ננקה את השינויים שביצענו בשרת בעזרת שאילתה ושימוש בפרוצדורה sp_dropserver וכמובן נצפה במצב אחרי ביצוע השינויים.

sp_dropserver @server='MyLinkedServer'

select * from sys.servers

select * from sys.linked_logins 

 

דוגמה 3: הגדרת Linked Server לשרת מרוחק:

נגדיר לינק סרבר לשרת מרוחק תוך שימוש בכתובת ה IP שלו:

exec sp_addlinkedserver

      @server='RemoteLinkedServer'

      , @srvproduct= ''

      , @provider='SQLNCLI'

      , @datasrc='xxx.xxx.xxx.xxx'

      , @location=''

ניסיון לגשת לנתוני השרת המרוחק יקפיץ לנו הודעת שגיאה.

Msg 18452, Level 14, State 1, Line 1

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

הסיבה היא שגישה לשרת מרוחק אינה אפשרית תחת הרשאות של מערכת ההפעלה הנוכחית כמובן (הדברים יכולים מעט שונים כשעובדים תחת דומיין). אם נסתכל באובייקטים שיש לנו במערכת נגלה שנוצר לנו login-mapping בכל פעם שאנו מייצרים את ה Linked Server בצורה זו.

select * from sys.servers

select * from sys.linked_logins

GO

אבל למעשה מיפוי זה אינו עוזר לנו כאמור ולכן עלינו לייצר login-mapping חדש עם הגדרות שם משתמש וססמה בשרת המרוחק שלנו. יש להקפיד להכניס 1. שם הלינק שלנו שייצרנו קודם, 2. את שם המשתמש, 3. את הססמה של המשתמש בשרת המרוחק.

exec sp_addlinkedsrvlogin

      @rmtsrvname = 'RemoteLinkedServer'

      ,@useself = 'false',

      @rmtuser = 'UserName'

      , @rmtpassword = 'Password'

עתה נוכל להתחבר לשרת המרוחק ולעבוד בשיטת הארבע חלקים להגדרת השמות של האלמנטים אליהם ניגשים.

sp_catalogs @server_name = 'RemoteLinkedServer'

GO

 

select * from  RemoteLinkedServer.DataBaseName.dbo.MyTbl

GO

ניקוי השינויים שביצענו:

ננקה את השינויים שביצענו בשרת בצורה גרפית או בעזרת שאילתה.

sp_dropserver @server='RemoteLinkedServer'

select * from sys.servers

select * from sys.linked_logins

דוגמה 4: הגדרת Linked Server לשרת מרוחק – שימוש בשרשרת התחברות להעברת פרמטרים נוספים:

אופן העבודה דומה לדוגמה 3 פרט לכך שעתה נוסיף פרמטר של שרשרת התחברות ללינק שלנו

exec sp_addlinkedserver

      @server='RemoteLinkedServer'

      , @srvproduct= ''

      , @provider='SQLNCLI'

      , @datasrc='xxx.xxx.xxx.xxx'

      , @location=''
      , @provstr=Connect Timeout=30;Context Connection=True'

כל ההמשך זהה לדוגמה הקודמת.

 

מקורות מידע:

הקישור הבא יכול לעזור לגבי פרמטרים של הפרוצדורה:

http://msdn.microsoft.com/en-us/library/ms190479.aspx

דוגמאות קוד התחברות לסוגי מקורות מידע שונים בעזרת linkedserver:

http://www.eggheadcafe.com/sample-code/SQLServer/fed3adb0-4c15-4a1a-8acd-1b184aff558f/spaddlinkedserver-transactsql.aspx

http://msdn.microsoft.com/en-us/library/ms190479.aspx

הגדרות כלליות של Link Server והגדרות אבטחה בעבודה עם הממשק הגרפי של ה SSMS:

http://sqlserverplanet.com/dba/how-to-add-a-linked-server