'DBLink'에 해당되는 글 3건

dblink

<DB Link순서> 

1. Oracle Client 설치 및 Oracle Net Service Name 등록 하기 

Oracle에 접속하기 위해서는 Oracle client를 설치하고 접속하고자 하는 Oracle DB에 대한 정보를 등록해줘야 한다. 이 과정에서 ODBC Data Source Name과 비슷한 역할을 하는Oracle Net Service Name을 등록하게 된다. 설치 및 ODBC Data Source Name등록에 대한 설명은 Oracle client 설치 가이드를 참고 하기 바란다. 

2. Linked server 등록하기 

Linked server name방식으로 작업을 수행하기 위해서는 Enterprise Manager에서 Linked server를 설정하거나 T-SQL에서 Sp_addLinkedServer, Sp_addLinkedSrvLogin, Sp_serverOption, Sp_dropServer 등을 이용해서 설정하면 된다. 각 SP에 대한 자세한 설명은 BOL(온라인설명서)를 참조하기 바라며, 아래는 Linked server를 생성하는 간단한 예이다. 

(1) Linked server 생성하기 

  sp_addlinkedserver [ @server = ] 'server' 

    [ , [ @srvproduct = ] 'product_name' ] 

    [ , [ @provider = ] 'provider_name' ] 

    [ , [ @datasrc = ] 'data_source' ] 

    [ , [ @location = ] 'location' ] 

    [ , [ @provstr = ] 'provider_string' ] 

    [ , [ @catalog = ] 'catalog' ] 

예) EXEC sp_addlinkedserver  'JYOra817',  'Oracle',  'MSDAORA',  'JY817' 

- JY817이라는 Oracle Net Service Name으로 등록된 Oracle데이터베이스를 JYOra817이라는 이름의 Linked server로 등록한다. 

(2) Login 생성하기 

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname' 

    [ , [ @useself = ] 'useself' ] 

    [ , [ @locallogin = ] 'locallogin' ] 

    [ , [ @rmtuser = ] 'rmtuser' ] 

    [ , [ @rmtpassword = ] 'rmtpassword' ] 

  예) EXEC sp_addlinkedsrvlogin 'JYOra817', 'FALSE', NULL, 'scott', 'tiger' 

- JYOra817이라는 Linked server에 scott/tiger라는 아이디와 패스워드를 이용해서 로그인하는 정보를 등록한다. 

3. Linked server를 이용한 QUERY수행하기 

(1) OPENQUERY() 사용하기 

앞서 생성한 Linked Sever를 이용해서 Query를 수행하고자 할 경우 OPENQUERY()라는 함수를 이용하는 방법이 있으며 구문은 아래와 같다. 

OPENQUERY ( linked_server , 'query' ) 

OPENQUERY를 사용하는 예이다. 

- select empno,ename,sal from OPENQUERY ( JYOra817 , 'select empno,ename,sal from scott.emp' ) 

위의 순서에 따라서 Oracle 클라이언트를 설치하고. Oracle로 접속해서 상대쪽 Oracle 정보를 Select하는것은 성공을 했습니다. 

Oracle 넷 매니저로 등록한 거라서 tnsnames.ora에도 연결할 서버 정보가 나와있습니다 

그런데, 이상하게 openquery로 질의를 날리면 계속 시간만 잡고 전혀 실행이 되질 않습니다. 

혹시나 권한이 없는가 해서 admin 계정 빌려서 접속했는데, 이번에는 mssql server 엔터프라이즈 관리자에서 연결된 서버 확인하자마자 위처럼 계속 시간만 잡아먹고 실행이 전혀 되지 않았습니다. DB가 아예 뻗어버리더군요;; 

방화벽 문젠가 싶어서 저희쪽과 상대방 서버쪽 방화벽을 다 확인해 보았는데도, Mssql-Oracle쪽은 나간 로그나 차단된 로그 자체가 존재하질 않습니다. Oracle로 상대박 Oracle 접속할 때는 허용되거나 차단된 정책이 다 나오는데.. 

Oracle 클라이언트 외에도 여러 버전이 설치가 되어있어서 그런건가요? 

Providename = OraOLEDB.Oracle 로 했는데, 제가 어떤 설정을 잘못한 것인지 알고 싶습니다. 

#T=dblink : mssql 2008 -> oracle 11g 테스트 완료

EXEC master.dbo.sp_addlinkedserver @server = N'test', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc="N'ORCL'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'test',@useself=N'False',@locallogin=NULL,@rmtuser=N'scott',@rmtpassword='1'

EXEC master.dbo.sp_serveroption @server=N'test', @optname=N'collation compatible', @optvalue=N'true'

GO

dblink2

SQL 네이티브 클라이언트 OLE DB 공급자 이용

SQL 네이티브 클라이언트 OLE DB 공급자

Microsoft SQL 네이티브 클라이언트 OLE DB 공급자는 Microsoft SQL Server 2005 데이터베이스에 OLE DB 인터페이스를 제공합니다. SQL Server 분산 쿼리는 SQL 네이티브 클라이언트 OLE DB 공급자를 사용하면 분산 쿼리가 SQL Server의 원격 인스턴스에서 데이터를 쿼리할 수 있습니다.

SQL Server 데이터베이스를 액세스하기 위해 연결된 서버를 만들려면 

sp_addlinkedserver를 실행하여 연결된 서버를 만들고 data_source로 SQL Server의 원격 인스턴스를 실행하는 서버의 네트워크 이름을 지정합니다. SQL Server 연결된 서버에서 공급자 SQLNCLI 지정은 선택 항목입니다. 

예를 들어 네트워크 이름이 NetSQLSrvr인 서버에서 실행되는 SQL Server 인스턴스에 대해 작동하는 LinkSQLSrvr라는 연결된 서버를 만들려면 다음 중 하나를 실행하십시오. 

sp_addlinkedserver N'LinkSQLSrvr', ' ', N'SQLNCLI', N'NetSQLSrvr'

sp_addlinkedserver N'LinkSQLSrvr', ' ', ' ', N'NetSQLSrvr'또는 다음 예에서와 같이 SQL Server 연결된 서버를 해당 네트워크 이름으로 정의할 수 있습니다.

sp_addlinkedserver N'NetSQLSrvr'

SQL Server 2000 인스턴스를 실행하는 서버에 대해 분산 쿼리를 실행할 때는 SQL Server 2000 서비스 팩 4(SP4)를 적용하는 것이 좋습니다. SP4를 적용하지 않은 경우 SP4에 포함된 Instcat.sql 스크립트를 실행하여 카탈로그 저장 프로시저를 업그레이드해야 합니다.

분산 쿼리가 SQL Server 버전 7.0 또는 그 이전 버전을 실행하는 서버에 대해 실행될 때는 분산 쿼리가 제대로 작동하도록 이전 버전의 카탈로그 저장 프로시저를 업그레이드해야 합니다. 예를 들어 서버가 SQL Server 7.0 인스턴스를 실행 중인 경우 SQL Server 2005의 인스턴스에서 실행 중인 서버의 분산 쿼리에서 해당 서버의 카탈로그 저장 프로시저를 참조하려면 먼저 카탈로그 저장 프로시저를 SQL Server 2005로 업그레이드해야 합니다. 

원격 SQL Server 테이블이 업데이트되면 로컬 서버나 클라이언트는 이 업데이트에 대해 실행한 트리거로부터 어떤 결과 집합이나 메시지도 받지 못하게 됩니다.

네 부분으로 된 이름을 사용할 때는 항상 스키마 이름을 지정하십시오. 분산 쿼리에 스키마 이름을 지정하지 않으면 OLE DB가 테이블을 찾을 수 없습니다. 로컬 테이블을 참조할 때 소유자 이름을 지정하지 않으면 SQL Server는 기본값을 사용합니다. 다음 SELECT 문은 연결된 서버 로그인이 연결된 서버의 AdventureWorks 데이터베이스에 있는 dbo 사용자에 매핑될 경우에도 7314 오류를 생성합니다.

sp_addlinkedserver @server = N'LinkServer',

    @srvproduct = N' ',

    @provider = N'SQLNCLI', 

    @datasrc = N'ServerNetName', 

    @catalog = N'AdventureWorks'

GO

SELECT *

FROM LinkServer.AdventureWorks.dbo.Vendor다음 예는 네트워크 이름이 othersite인 같은 컴퓨터를 액세스하는 연결된 서버와 원격 서버를 정의합니다. 이 연결된 서버 정의는 원격 서버의 네트워크 이름과 같은 이름을 사용합니다. 원격 서버 정의는 다른 이름을 사용합니다.

/* Create a linked server definition to othersite. */

EXEC sp_addlinkedserver 'othersite', N'SQL Server'

/* Create a remote server definition using a

   fictitious name. */

EXEC sp_addserver 'RPCothersite'

/* Set the fictitious name to the network name far away. */

EXEC sp_setnetname 'RPCothersite', 'othersite'이 이름들은 분산 쿼리나 원격 프로시저 호출에서 참조될 수 있습니다.

/* A distributed query referencing othersite. */

SELECT *

FROM othersite.Northwind.dbo.Employees

/* A remote procedure call to the same server. */

EXEC RPCothersite.master.dbo.sp_who

/* Distributed queries can be used to execute

   stored procedures on the other server. */

EXEC othersite.master.dbo.sp_who연결된 서버를 통해 실행된 저장 프로시저와 원격 서버를 통해 실행된 저장 프로시저의 로그인 매핑 메커니즘에는 몇 가지 차이점이 있습니다. 자세한 내용은 서버 연결을 참조하십시오.

 분산 쿼리의 트랜잭션 고려 사항 

Microsoft SQL 네이티브 클라이언트 OLE DB 공급자는 중첩된 트랜잭션을 지원하지 않습니다. 따라서 여러 Transact-SQL 문이 포함된 트랜잭션 내의 데이터 수정 작업에 대해, 그리고 분산 분할 뷰에 대한 데이터 수정 작업에 대해 XACT_ABORT를 ON으로 설정해야 합니다.

DBLINK

1. sa 계정으로 sql 스튜디오에 접속한다.

2. 원격 DB링크 연결

-> exec sp_addlinkedserver @server='생성할디비링크명', @srvproduct='', @provider='SQLOLEDB', @datasrc='접속할디비아이피'

3. 원격 DB에 로그인 허용

-> exec sp_addlinkedsrvlogin @rmtsrvname='DB 링크명', @useself='False', @locallogin=Null, @rmtuser='계정명', @rmtpassword='비밀번호';

4. 원격 DB의 프로시져 호출

-> exec 링크명.데이터베이스명.계정명.프로시저명;

5. 만약 RPC에 대해 구성되지 않았습니다 라는 오류가 뜬다면 아래 설정을 바꿔준다.

연결된서버로부터 로컬서버로의 RPC 호출을 활성화

EXEC sp_serveroption 'DB 링크명', 'rpc', TRUE;

6. 로컬서버로부터 연결된서버로의 RPC 호출을 활성화

EXEC sp_serveroption 'DB 링크명', 'rpc out', TRUE;