Membuat Linked Server Di SQL Server Untuk Kebutuhan Openquery (MYSQL, SQLSERVER, ORACLE)

Latarbelakang

Bagaimana caranya kita dapat memperoleh data dari server lain dengan aplikasi yang sama (SQL Server) ataupun dengan Server yang menggunakan software database lain (Oracle, Mysql)?

Solusi

Linked Server adalah metode di mana SQL Server dapat berkomunikasi dengan database yang sesuai dengan ODBC, seperti contoh SQL Server lain atau database (Oracle, MySql) dengan query T-SQL langsung.

Ada beberapa pengaturan penting yang perlu diketahui saat menyiapkan Linked Server. Tip ini akan mencakup pembuatan Linked Server ke instance SQL Server lain menggunakan GUI SSMS bersama dengan opsi keamanan yang sesuai. Ini juga akan menunjukkan cara menggunakan Linked Server dalam T-SQL seperti OpenQuery. Ada banyak sekali pustaka topik lanjutan yang tersedia yang perlu dikuasai.

Membuat Linked Server SQL Server Management Studio

Buka SQL Server Management Studio, dan arahkan mouse ke Panel Object Explorer lalu perluas pada bagian folder Server Objects dan Klik Kanan mouse pada bagian Linked Server dan pilih “New Linked Server”.

Jika Linked Server Untuk SQL Server:

atau

Jangan Lupa Catalog isi dengan Database_Name jika tidak maka akan mengarah DB Master. Misalkan Catalog = DATABASE_NAME

Jika Linked Server Untuk MYSQL

Cara untuk membuat Linked Server untuk MySQL server sedikit berbeda dengan sesama SQL Server, Pada MySQL Server kita harus membuat Konfigurasi Connector ODBC terlebih dahulu di Control Panel\System and Security\Administrative Tools\ODBC Data Sources (64-bit).link seperti gambar di bawah ini,

Catatan: Harus sesuaikan apabila SQL Server Management Studio menggunakan yang 64Bit maka untuk MySQL Connector ODBC harus yang 64bit juga. Jika tidak maka pembuatan Linked Server akan gagal dan menampilkan pesan error “The OLE DB provider "MSDASQL" for linked server "Your Linked Server" reported an error”.

Setelah pembuatan Linked Server, kamu masuk ke dalam folder Server Objects > Linked Server > Provider. Klik kanan mouse pada MSDASQL dan pilih menu Properties dan centang beberapa opsi seperti berikut ini,

Yang dicentang adalah: Nested Queries, Level Zero Only, Allow inprocess, dan Support ‘Like’ Operator.

Jika Linked Server Untuk Oracle

Konfigurasi terlebih dahulu di provider link server dengan menjalankan script di bawah ini

exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'AllowInProcess', 1

Atau

Aktfikan Link Server Oracle di SQL Server

Selanjutnya buat new link server untuk menghubungan sql server ke oracle dengan data source nya adalah XE dengan remote login menggunakan nama user/schema pada oracle dan untuk nama data source data di cek dapat di cek di

C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\ tnsnames.ora

XE =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-TUVM0M0)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = XE)

)

Halaman Security Page

Setelah mengisi field pada halaman general untuk SQL Server, MySQL, Oracle selanjutnya kita akan mengisi field username dan password pada halaman Security. Pilih "Be made using this security context" untuk dapat memasukan username dan password yang digunakan pada Linked Server. Username default SQL Server (“sa”), Username default MySQL (“root”).

Halaman Server Options

Ada juga pengaturan pada halaman Server Options, tetapi untuk pertanyaan sederhana pengaturan ini dapat dibiarkan sebagai nilai default seperti yang ditunjukkan di bawah ini.

Pengaturan tidak perlu diubah dalam kebanyakan kasus penggunaan dan tabel ini akan menjelaskan apa yang dilakukan pengaturan.

Options	Deskripsi
Collation Compatible	Default False. Ini dapat di-set ke true jika server target menggunakan susunan yang sama persis seperti server utama. Perubahan dapat meningkatkan kinerja beberapa Query dengan memungkinkan perbandingan kolom jarak jauh pada kolom jenis teks.
Data Access	Default adalah True. Ketika false semua eksekusi remote query akan gagal. Pengaturan ini independen dari RPC Out. Dalam skenario di mana HANYA pemanggilan prosedur remote perlu dijalankan, pengaturan ini dapat diubah menjadi false sementara RPC Out tetap benar.
RPC / RPC Out	Ini adalah singkatan dari Remote Procedure Call dan memungkinkan Anda untuk menjalankan prosedur tersimpan pada Linked Server. RPC mengaktifkan call Remote Prosedur dari server yang ditentukan dan RPC OUT memungkinkan call remote Prosedur ke server yang ditentukan.
Use Remote Collation / Collation Name	Default adalah False. Ketika false, susunan server lokal akan digunakan pada semua remote query. Ketika True susunan remote server akan digunakan pada queries. Jika True dan remote server BUKAN SQL Server maka server lokal tidak dapat secara otomatis mencari nilai pemeriksaan yang tepat. Dalam hal ini gunakan pengaturan Nama Collation untuk mengetikkan collation remote server.
Connection Timeout	Default-nya adalah 0. Ketika tidak nol, ini adalah jumlah detik untuk menunggu sebelum waktu habis saat menghubungkan ke linked server. Ketika 0 maka nilai sebenarnya dibaca dari default server yang dapat ditemukan dengan menjalankan perintah ini. exec sp_configure 'remote login timeout (s)'
Query Timeout	Defaultnya adalah 0. Ketika bukan nol, ini adalah jumlah detik untuk menunggu sebelum menentukan waktu query yang dijalankan terhadap Linked Server. Ketika 0 maka nilai sebenarnya dibaca dari default server yang dapat ditemukan dengan menjalankan perintah ini. exec sp_configure 'remote query timeout (s)'
Distributor / Publisher / Subscriber	Pengaturan ini hanya digunakan ketika Linked Server sedang digunakan untuk mendukung replikasi.
Lazy Schema Validation	Default adalah False. Ketika false schema dari remote query diperiksa sebelum eksekusi query. Setiap perubahan pada remote schema dideteksi sebelumnya dan rencana query yang tepat dapat dibuat kembali untuk memperhitungkan perubahan tersebut. Ketika benar schema tidak divalidasi sampai saat pelaksanaan. Jika perubahan schema membuat rencana query tidak valid yang akan menyebabkan kesalahan runtime.
Enable Promotion of Distributed Transactions	Default adalah True. Ketika diaktifkan, remote query mana pun yang mengubah data akan meminta DTC (Distributed Transaction Coordinator). Proses ini melindungi permintaan tersebut dengan memungkinkan kedua server untuk memiliki transaksi bersama yang menyelesaikan 100% atau 0% - sama seperti transaksi reguler dalam permintaan lokal. Ketika dinonaktifkan, DTC tidak dipanggil dan permintaan remote yang gagal dapat gagal dan tidak dapat sepenuhnya dibatalkan.

Cara Menggunakan OpenQuery

Select Data Dengan OpenQuery

Untuk melakukan read data kamu dapat menggunakan T-SQL seperti di bawah ini,


select * from openquery (MYSQLAGUNGPANDUAN, 'select * from agungpanduan.profil'); -- for MYSQL
select * from openquery (ANOTHERSQLSERVER, 'select * from test.dbo.product'); --For SQL Server
SELECT * FROM OPENQUERY([ORACLEDB], 'select * from products'); -- for oracle

Insert Data Dengan OpenQuery

Untuk dapat melakukan insert data menggunakan T-SQL seperti dibawah ini,


INSERT INTO OPENQUERY (ANOTHERSQLSERVER, 'SELECT * FROM agungpanduan.profil')
VALUES  ('20005','Siti','Cianjur','1997-05-11'); -- for MYSQL

INSERT INTO OPENQUERY (ANOTHERSQLSERVER, 'SELECT name, price, qty FROM test.dbo.product')
VALUES  ('bb',2000,100); --For SQL Server

INSERT INTO OPENQUERY ([ORACLEDB], 'SELECT product_id, product_name, qty FROM products')
VALUES (4,'test',100); --For Oracle

Update Data Dengan OpenQuery

Untuk dapat melakukan Update data menggunakan T-SQL seperti dibawah ini,


-- for MYSQL
UPDATE OPENQUERY(MYSQLAGUNGPANDUAN, 
  'select nama, NIP FROM agungpanduan.profil WHERE NIP = 20003'
)
SET nama = 'Update Nama'
where NIP = 20003;

-- For Oracle
UPDATE OPENQUERY([ORACLEDB], 
  'SELECT product_id, product_name, qty FROM products where product_id=4'
)
SET Qty = 50
where product_id = 4;

-- For SQL Server
UPDATE OPENQUERY(ANOTHERSQLSERVER, 
  'select * from test.dbo.product WHERE id = 2'
)
SET Price = 3000
where id = 2;

Delete Data Dengan OpenQuery

Untuk dapat melakukan Delete Data menggunakan T-SQL seperti dibawah ini,


-- for MYSQL
DELETE FROM OPENQUERY(MYSQLAGUNGPANDUAN, 
  'select nama, NIP FROM agungpanduan.profil WHERE NIP = 20005'
)
where NIP = 20005;

-- For SQL Server
DELETE FROM OPENQUERY(ANOTHERSQLSERVER, 
  'select id from test.dbo.product WHERE id = 2'
)
where id = 2;

-- For Oracle
DELETE FROM OPENQUERY([ORACLEDB], 
 'SELECT product_id FROM products where product_id=2'
)
where product_id = 2;

Perhatikan data tabel sebelumnya, satu baris dihapus dan setelah dihapus apakah dengan NIP =’20005’ masih ada jika tidak ada maka penghapusan satu baris pada database Linked Server berhasil.

Execute Store Procedure Linked MYSQL Server

Tidak hanya eksekusi insert, delete, dan update kita pun dapat menjalankan Store Procedure di Linked Server namun RPC, dan RPC Out harus diaktikan terlebih dahulu menjadi TRUE.

Sekarang jalankan T-SQL dibawah ini,


EXEC('CALL agungpanduan.Jumlah()') AT MYSQLAGUNGPANDUAN;
EXEC('SHOW TABLES') AT MYSQLAGUNGPANDUAN; 
EXEC('SHOW SLAVE STATUS') AT MYSQLAGUNGPANDUAN; 
EXEC('SHOW DATABASES') AT MYSQLAGUNGPANDUAN; 
EXEC('SHOW CREATE TABLE mysql.user') AT MYSQLAGUNGPANDUAN;

Execute Store Procedure Linked SQL Server

Sama halnya seperti Linked Server untuk MYSQL, di Linked SQL Server pun RPC, dan RPC Out harus diaktikan terlebih dahulu.

Misalkan kita membuat Stored Procedure;


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE Jumlah

AS
BEGIN
	DECLARE 
    @Jumlah int;
	SET NOCOUNT ON;
	SET  @Jumlah = (SELECT count(*) from Product);

	print @Jumlah

	DECLARE @@KOLOM_JUMLAH TABLE (name varchar(10));

	INSERT INTO @@KOLOM_JUMLAH
	SELECT name from Product

	Select * from @@KOLOM_JUMLAH

END
GO

Lalu kita akan jalankan di Linked Server dengan T-SQL di bawah ini,


-- EXEC LINKED NAME SERVER. Database. Store Procedures
EXEC ANOTHERSQLSERVER.TEST.dbo.Jumlah
-- =================Atau ===============================================================
SELECT * FROM OPENQUERY(ANOTHERSQLSERVER,'TEST.dbo.Jumlah')
select * from openquery (ANOTHERSQLSERVER, 'select * from test.dbo.product');

Execute Store Procedure Linked Oracle

Sama halnya seperti Linked Server untuk MYSQL dan SQL Server, di Linked Oracle pun RPC, dan RPC Out harus diaktikan terlebih dahulu.

Misalkan kita membuat Procedure dulu di Oracle;

function myFunction(){
$(document).on("click", "tr", function () {
    var Name = $(this).closest("tr").find('td:eq(1)').text();
});


CREATE OR REPLACE PROCEDURE AGUNGKASEP.testpanggil
(productid IN products.product_id%type, Qty_InOut OUT products.qty%type)
IS 
tmp_qty number;  
BEGIN 
    select qty
    into tmp_qty
    from products
    where product_id = productid;
    if tmp_qty  <> 0 then
        Qty_InOut := tmp_qty*0.5;
    end if;
END testpanggil;
/