SQL Server’in önemli özelliklerinden bir olan Linked Server (bağlı sunucu), SQL Server’in OLE DB/ODBC veri kaynakları aracılığıyla farklı kaynaklar (Oracle, Access, Excel, Text .) üzerindeki verilere erişmemize, onlar üzerinde komutlar çalıştırmasına, dağıtık sorguları yönetme imkan tanır. Bir linked server tanımlamak için bir OLE DB provider ve bir OLE DB data source belirtilir. OLE DB provider, Microsoft Data Access Components (MDAC)’ın bir bileşeni olup veri kaynağıyla iletişimi sağlayan ve onu yönetme imkanı sunan kütüphanedir (DLL dosyası). Data Source ise bağlantı kurulacak veri tabanını temsil eder. Her veritabanı için farklı OLE DB provider kullanılır. Örneğin SQL Server tabanlı bir veritabanına erişmek için Microsoft OLE DB Provider for SQL Server OLE DB provider kullanılır. Aynı şekilde .mdb dosyaları için Microsoft OLE DB Provider for Jet, Oracle veritabanına bağlanmak için Microsoft OLE DB Provider for Oracle veri sağlayıcısı kullanılır.
MSDN’den alınmış aşağıdaki çizim bir linked server’in çalışma mantığını göstermektedir.
Linked Server Tanımlamak
SQL Server üzerinde bir linked server tanımlamak veya mevcut tanımlamaları silmek için sistem stored procedureleri veya Enterprise Manager / Management Studio aracı kullanılabilir. Bir linked server tanımlamak için sp_addlinkedserver yordamı, tanımlanmış sunucular hakkında bilgi almak için sp_linkedservers yordamı kullanılır.
sp_addlinkedserver [ @server = ] 'server' --Sunucu Adı
[ , [ @srvproduct = ] 'product_name' ] --Ürün Adı
[ , [ @provider = ] 'provider_name' ] --Sağlayıcı adı
[ , [ @datasrc = ] 'data_source' ] --Veri kaynağı
[ , [ @location = ] 'location' ] --Konum, lokasyon
[ , [ @provstr = ] 'provider_string' ] --Bağlantıyı sağlayacak nesne adı
[ , [ @catalog = ] 'catalog' ]--Katalog adı
sp_addlinkedserver yordamı bilgileri verilmiş dağıtık sunucuyu SQL Server içine iliştirir. @server parametresine sunucunun adı yazılır. SQL Server üzerinde instance kurulmuş ise bu parametreye “Server Adı\Instance Adı” olarak değer girilmelidir. @srvproduct parametresine OLE DB veri kaynağının adı yazılır. Eğer kaynak SQL sunucu ise “SQL Server” yazılır bu durumda provider_name, data_source, location, provider_string ve catalog alanlarının belirtilmesine gerek kalmaz. @provider parametresine veri kaynağıyla iletişim ve erişimi sağlayacak veri sağlayacısının adı girilir. Örneğin Analysis Services için MSOLAP, Oracle için MSDAORA, Excel (CSV) için Microsoft.Jet.OLEDB yazılır. Aşağıda farklı veri kaynakları için örnekler gösterilmiştir.
1-Microsoft OLE DB Provider for SQL Server Kullanma
--SQL Server tabanlı sunucu için linked server oluşturma
USE master
GO
EXEC sp_addlinkedserver
'W00',
N'SQL Server'
--Bir SQL Server instance için linked server oluşturma
EXEC sp_addlinkedserver @server='W00_Kafka', @srvproduct='',
@provider='SQLOLEDB', @datasrc='W00\Kafka'
W00 sunucu üzerinde bulunan DW isimli veritabanında dbo şemasının altındaki Urun tablosuna erişmek için aşağıdaki yazım biçimi kullanılır.
SELECT * FROM W00.DW.dbo.Urun
2 – Microsoft OLE DB Provider for Jet Kullanma
Bu veri sağlayıcısı MS Access, Excel ve SQL Server türündeki kaynaklarına erişmek için kullanılır.
EXEC sp_addlinkedserver @server = 'MuhasebeDB', @provider = 'Microsoft.Jet.OLEDB.4.0', @srvproduct = 'OLE DB Provider for Jet', @datasrc = 'C:\Muhasebe.mdb' EXEC sp_addlinkedserver N'ExcelLink', @srvproduct = N'', @provider = N'Microsoft.Jet.OLEDB.4.0', @datasrc = N'C:\Book1.xls', @provstr = N'Excel 8.0;' --Mapped Drive yerine bir UNC path verilebilir EXEC sp_addlinkedserver 'ExcelLink', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', '\\FileServer\DW\Book1.xls', NULL, 'Excel 8.0'; --EXCEL 2007 formatıyla bağlantı kurmak için ACE provider kullanılır EXEC sp_addlinkedserver @server = N'ExcelLink', @srvproduct=N'', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'C:\Book1.xlsx', @provstr=N'EXCEL 12.0' ;
Excel dosyasındaki Sheet isimli tabloya erişelim.
SELECT * FROM ExcelLink.Sheet1$;
3 – Microsoft OLE DB Provider for Oracle Kullanma
Aşağıdaki örnekte SAP sistemine ait Oracle veritabanına bağlantı kurulmuştur.
EXEC master.dbo.sp_addlinkedserver @server = N'SAPPro', @srvproduct=N'Oracle', @provider=N'MSDAORA', @datasrc=N'SAP', @provstr=N'MSDAORA'
Oracle üzerindeki SAPR3 şemasının altındaki MSEG tablosunda ilk 10 kaydı okuyalım.
SELECT TOP 10 * FROM SAPPro..SAPR3.MSEG
4 – Microsoft OLE DB Provider for ODBC Kullanma
Bu veri sağlayıcısı ODBC üzerinde tanımlı kaynaklara erişmek için kullanılır. Bu kaynaklara sadece Data Source parametresini belirterek erişebileceğimiz gibi Provider String parametresi belirterek te erişebiliriz. Sadece Data Source parametresi kullanılacağı zaman öncelikle ODBC Administrator aracı kullanılarak (c:\windows\system32\odbcad32.exe) sözkonusu veri kaynağının System DSN olarak tanımlanması gerekmektedir.
--data_source parametresi ile tanımlama
EXEC sp_addlinkedserver
@server = 'Sunucu1',
@srvproduct = '',
@provider = 'MSDASQL',
@datasrc = 'W00'
--provider_string parametresi ile tanımlama
EXEC sp_addlinkedserver
@server = 'Sunucu1',
@srvproduct = '',
@provider = 'MSDASQL',
@provstr = 'DRIVER={SQL Server};SERVER=W00;UID=sa;PWD=123;'
Text dosyasının veri kaynağı kullanılması
Düz metin dosyalarını veri kaynağı olarak kullanmak için Microsoft.Jet.OLEDB.4.0 veri sağlayıcısı kullanılır. Okunacak olan metin dosyasındaki veriler belli standartlarda kayıt edilmiş olmalıdır. Örneğin kayıtlar arasındaki bir liste ayırıcı olabileceği gibi kayıtlar uzunluklarına göre de ayırtedilebilir. Bir metin dosyasından okuma yapılacağı zaman Windows kayıt defteri (registry) referans alınır. Windows kayıt defterinde varsayılan metin sürücüsü ayarlarını geçersiz kılmak için Schema.ini dosyası oluşturulur. Schema.ini dosyasını kullanmak için aşağıdakileri maddelere dikkat etmek gerekir:
- Schema.ini dosyası metin dosyası ile aynı klasöre kayıt edilmeli.
- Schema.ini dosyasının birinci satırına bağlanılacak metin dosyasının adı ayraç içinde yazılır. (“[” ve “]”)
- Farklı metin dosyası biçimi belirtmek için ek bilgiler (kolon tipleri, genişlikleri .)eklenir.
Metin dosyasının formatını belirtmek için aşağıdaki önemli nitelikler kullanılır.
- Text (CSV) dosyasının kolon başlıkları içerip içermediği ColNameHeader özelliğine True/False değerleri atanarak belirtilir.
- Metin içinde hangi liste ayırıcının kullanıldığı Format özelliğiyle belirtilir. Aşağıda bazı örnek kullanımlar gösterilmiştir;
Sınırlayıcı olarak noktalı virgül karakteri (;) belirtme
Format=Delimited(;)
Sınırlayıcı olarak tab karakteri belirtme
Format=TabDelimited
Sabit genişlikli dosya belirtme
Format=FixedLength
Col1=MusteriId Text Width 10
Col2=AdiSoyadi Text Width 30
Col3=”Doğum Tarihi” DateTime Width 24 - Tarih türünden bir kolon varsa tarih formatı DateTimeFormat özelliğiyle belirtilir.
- Kolon isim ve tipleri Col(n)=
Aşağıdaki örnekte tab delimited türündeki bir dosyanın biçimi hakkında bilgi verilmiştir.
[Musteri.txt]
Format=TabDelimited
CharacterSet=ANSI
DateTimeFormat=dd-MM-yyyy
ColNameHeader = False
CurrencySymbol=TL
CurrencyPosFormat=0
CurrencyDigits=2
CurrencyThousandSymbol=,
CurrencyDecimalSymbol=.
NumberLeadingZeros=0
Col1=Tarih DateTime
Col2=AdiSoyadi Text
Col3=Aciklama Text
Col4=Tutar Currency
NOT : Schema.ini dosyasının tanımlanmadığı durumlarda makinedeki default ayarlar referans alınır. Bu ayarlar HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text alanında tanımlanmıştır.
Linked Server Sistem Yordamları
- sp_addlinkedserver : OLE DB veri kaynakları aracılığıyla SQL Server üzerinde bir linked server oluşturur. Tanımlı sunucuyu kaldırmak için sp_dropserver yordamı kullanılır.
- sp_addlinkedsrvlogin : Linked Server olarak tanımlanmış sunuculara güvenli erişimi sağlamak için kullanılır. Bir Linked Server ile iletişim kurmak için 3 temel güvenlik yöntemi tercih edilir. Uzaktaki sunucuya bağlanmak için ya yerel Windows kullanıcısı kullanılır ki bu durumda her iki sunucunun aynı domainde olması beklenir veya SQL Server kullanıcısı kullanılır ki bu durumda her iki sunucuda aynı SQL Server kullanıcı oluşturulmuş olmalıdır. Sonuncu yöntem ise iki sunucu arasında kullanıcı eşleştirilmesidir. Bu yöntem özellikle linked server’in domain dışında olduğu durumda tercih edilir. Yani birinci sunucudaki A kullanıcısı bağlı sunucudaki B kullanıcısıyla eşleştirilir. Böylece ilk makinedeki kullanıcı ikinci makinedeki kullanıcının yetkisini kullanarak kaynaklara erişmiş olur. sp_addlinkedsrvlogin yordamının parametreleri şu şekildedir;
--sp_addlinkedsrvlogin parametreleri: @rmtsrvname: Linked Server için verilen takma ad. @useself: 'TRUE' | 'FALSE' | NULL değerlerini alır. Uzaktaki sunucuya erişirken yereldeki kullanıcı hesabının kullanılıp kullanılmayacağını belirtir. @locallogin: Uzaktaki makineye bağlanılacak yerel kullanıcısı. @rmtuser: Uzak sunucudaki hesap. @rmtpassword: Uzak sunucudaki kullanıcının şifresi
Aşağıdaki örnekte W00 adlı sunucuya, Ahmet adlı domain hesabı uzaktaki makineye ait UzakKullanici adlı hesabı ve UzakKullaniciSifre şifresini kullanarak erişmektedir.
EXEC sp_addlinkedsrvlogin @rmtsrvname='W00', @useself='false', @locallogin='Domain\Ahmet', @rmtuser='UzakKullanici', @rmtpassword='UzakKullaniciSifre'
Bağlı sunucu için tanımlı güvenlik bilgisini kaldırmak için sp_droplinkedsrvlogin yordamı kullanılır.
- Katalog Yordamları : Uzaktaki yere alan nesneler hakkında bilgi almak için aşağıdaki katalog yordamları kullanılır.
- sp_catalogs: Uzak sunucudaki katalogları listeler. Bu liste SQL Server için veritabanı listesine denk gelir.
- sp_tables_ex: Bağlı sunucudaki tabloları listeler.
- sp_indexes: Uzak sunucudaki indeksleri listeler.
- sp_primarykeys: Tablo parametresini alıp tabloya ait primary key’leri (birincil anahtar kısıtlayıcısı) listeler.
- sp_foreignkeys:Tablo parametresini alıp tabloya ait foreign key’leri (yabancı anahtar kısıtlayıcısı) listeler.
- sp_columns_ex: Aldığı parametreye uygun olarak uzak sunucudaki kolonları listeler.
Dağıtık Sorguları Çalıştırmak
Dağıtık sorguları çalıştırmak yani uzak sunucu üzerinde sorgu çalıştırmak için aşağıdaki yöntemler kullanılır;
- Linked Serverdaki Tam İsim : SQL Server üzerinde tanımlı bağlı sunucuya ait tam niteliklik isim kullanılarak sorgu çalıştırılabilir. Tam nitelikli isim Linked Server Adi.Database Adı.Şema Adı.Nesne Adı olarak yazılır. Örneğin W00 isimli bağlı sunucu üzerindeki DW veritabanı altındaki Musteri tablosu şu şekilde sorgulanır.
SELECT * FROM W00.DW.dbo.Musteri --Veya sp_executesql yordami kullanılabilir EXEC W00.DW.dbo.sp_executesql N'SELECT TOP 10 * FROM tbMusteri'
NOT : Bağlı sunucu üzerinde sorgulama yapılırken uzak nesne için WITH(NOLOCK) hint kullanılamaz.
- OPENQUERY Fonksiyonu : OPENQUERY(‘Linked Server Adı’,’Sorgu’) şeklinde parametre alan bu fonksiyon bir sorguyu tanımlı linked server üzerinde çalıştırır. Önceki yönteme göre daha az kaynak harcar. Sorguyu doğrudan uzak sunucu üzerinde çalıştırır. Kayıt dizisi içerdiği için bir tabloymuş gibi SELECT edilir. Önceki örneği şu şekilde çalıştırabiliriz. SELECT * FROM OPENQUERY(‘W00′,’SELECT * FROM DW.dbo.Musteri’)
- OPENROWSET Fonksiyonu : Önceki yöntemlerden farklı olarak Ad-Hoc Queries (Doğaçlama Sorgular) için tercih edilen bu fonksiyon bağlanılacak sunucu ile tüm bağlantı bilgilerini parametre olarak alır. Yani sistem üzerinde kalıcı bir linked server tanımlamak yerine o anda bir bağlı sunucu tanımı yapmış oluyoruz diyebiliriz. Aşağıdaki örnekte MDB dosyasındaki Musteri tablosu sorgulanmıştır.
OPENROWSET ( { 'provider_name', { 'datasource';'user_id';'password' | 'provider_string' } , { [ catalog. ] [ schema. ] object | 'query' } | BULK 'data_file', { FORMATFILE ='format_file_path' [ En basit kullanım şekli şu şekildedir;<br /> <i>OPENROWSET('veri sağlayıcı adı','veri kaynağı',;'kullanıcı adı';'şifre',<br /> | 'veri sağlayıcı erişim cümlesi (veri kaynağı adı verilmediği durumlarda)',[katalog.][şema.]nesne,'sorgu')</i> "sorgu" bölümüne detaylı bir sorgu yazılabildiği gibi doğrudan bir Table veya View ismi de yazılabilir. [sql]SELECT MusteriId,AdiSoyadi FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\Musteri.mdb'; 'admin';'',Musteri); SELECT a.* FROM OPENROWSET('SQLOLEDB','W00';'sa';'sa', 'SELECT TOP 10 * FROM DW.dbo.tbAlisveris') AS a
Yeri gelmişken yazmakta fayda var. SQL Server 2005 – OPENROWSET fonksiyonunun en çok kullanıldığı alanlardan biri de BULK operasyonlarıdır. Ogrenci.txt dosyasının içeriğinin aşağıdaki gibi olduğunu düşünelim.
Ogrenci.txt
1,Ahmet Kaymaz,C#
2,Murat Şensoy,VB.NET
3,Leyla,SQL Server
SELECT * FROM OPENROWSET(BULK 'C:\OGRENCI.TXT',SINGLE_CLOB) AS T1
Bu sorgu dosyadaki verileri olduğu için tablo olarak döndürecektir.
Karakter olarak (SINGLE_CLOB) değil ikili veri olarak okumak için “SINGLE_CLOB” değeri parametre olarak girilir. Bu durumda içerik ASCII yerine bayte olarak dönmüş olur.
SELECT * FROM OPENROWSET(BULK 'C:\OGRENCI.TXT',SINGLE_BLOB) AS T1
Bu fonksiyonun farklı kullanım örneğini görmek için SQL’den Excel’e, Excel’den SQL’e Aktarım isimli makale incelenebilir.
Bu fonkisyonu kullanmaya çalışırken aşağıdaki hata mesajı alınabilir.
Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, see “Surface Area Configuration” in SQL Server Books Online.
Bu durumda aşağıdaki kodları kullanarak “doğaçlama dağıtık sorgu” seçeneğinin aktifleştirilmesi gerekmektedir.
sp_configure 'show advanced options',1 reconfigure with override go sp_configure 'Ad Hoc Distributed Queries',1 reconfigure with override
Bu fonksiyon da OPENROWSET gibi doğaçlama bağlantı bilgilerini kullanarak veri erişimi sağlar . OPENROWSET fonksiyonundan farklı olarak tam nitelikli yazım biçimini (catalog.schema.object notasyonu) destekler. Bu yönüyle Linked Server kullanım biçimine benzemektedir.
SELECT TOP 10 *
FROM OPENDATASOURCE('SQLOLEDB',
'Data Source=W00;User ID=sa;Password=sa'
).DW.dbo.tbMusteri
Kısacası OPENROWSET fonksiyonu doğrudan bir kayıt seti (table, view) döndürürken OPENDATASOURCE fonksiyonu nesneye erişmek için kullanılacak bağlantıyı döndürür.
NOT : Uzak sunucular üzerinde sorguları dağıtık transaction yönetiminde çalıştırmak için SET XACT_ABORT ON şeklinde XACT_ABORT özelliği aktif yapılır. Bu süreçte MSDTC (Microsoft Distributed Transaction Coordinator) sorumludur.
Şu ana kadar sistem yordamlarıyla bir bağlı sunucunun nasıl tanımlanacağını yazdık. Bu işlemleri Enterprise Manager veya Management Studio aracılığıyla daha görsel kolayca yapabiliriz. Enterprise Manager üzerinde bir linked server tanımlamak için “Security»Linked Server” menüsü kullanılır.
Açılan pencerede bağlı sunucunun adı ve bağlantı ile ilgili detaylar tanımlanır.
Management Studio içerisinde ise “Security » Server Objects” bölümü kullanılır.
Kaynak : msdn.microsoft.com