Linked Server 만들기

예시) sp_addlinkedserver(Transact-SQL)

[MySQL]

아래 쿼리를 실행하기 전에 MS-SQL서버가 설치된 곳에 "mysql-connector-odbc-5.1.8-winx64.msi" 이와 같은

mysql connector를 설치 하고 ODBC를 셋팅 합니다.

 

EXEC SP_ADDLINKEDSERVER

@SERVER='MAIL',                        --연결된 서버를 명시할 사용자가 정의한 이름

@SRVPRODUCT='MySQL',            --서버 제품명
@PROVIDER='MSDASQL',               --공급자(ODBC를 이용한 연결에 공통으로 사용하는 공급자)

@DATASRC='TestMySQL',                --ODBC설정시 정의한 이름
@CATALOG = 'TestMail'                            --DB명

GO

 

[ORACLE]

아래 쿼리를 실행하기 전에 MS-SQL서버가 설치된 곳에 "ODAC121012_x64.zip" 이와 같은

Oracle Data Access Control = Oracle Client를 설치 하고 tnsnames.ora파일을 셋팅 합니다.

 

----OLE DB를 이용한 연결
EXEC SP_ADDLINKEDSERVER 
       @SERVER='TAOLE',                            --연결된 서버를 명시할 사용자가 정의한 이름
       @SRVPRODUCT='ORACLE',                --서버 제품명
       @PROVIDER='OraOLEDB.Oracle',        --Oracle 홈페이지에 지원하는 OLEDB 공급자
       @DATASRC='TestDB'                            --tnsnames.ora에 명시된 서비스명

GO

 

----ODBC를 이용한 연결
EXEC SP_ADDLINKEDSERVER 
       @SERVER='TAODBC',                        --연결된 서버를 명시할 사용자가 정의한 이름
       @SRVPRODUCT='ORACLE',                --서버 제품명
       @PROVIDER='MSDASQL',                    --공급자(ODBC를 이용한 연결에 공통으로 사용하는 공급자)
       @DATASRC='TestOracle'                    --ODBC설정시 정의한 이름

GO

 

 

[MS-SQL]

EXEC SP_ADDLINKEDSERVER

@SERVER='WHLEE',                                        --연결된 서버를 명시할 사용자가 정의한 이름

@SRVPRODUCT='',                                           --서버 제품명
@PROVIDER='SQLNCLI11',                                --공급자

@DATASRC='172.0.0.1\WHLEE2012,1433',            --IP주소[\하나의상의서버가 있다면 인스턴스명],[포트]
@CATALOG = 'TestDB'                                        --DB명

GO

 

 

 

예시) sp_addlinkedsrvlogin(Transact-SQL)

EXEC SP_ADDLINKEDSRVLOGIN

@rmtsrvname='MAIL',                            --연결된 서버를 명시할 사용자가 정의한 이름

@useself='FALSE',                                --원격서버 ID/Password를 사용함(FALSE) , 로컬로그인계정 사용함(TRUE)

@locallogin'sa',                                    --로컬서버의 로그인 ID

@rmtuser='testid',                                --원격서버의 ID

@rmtpassword='testid'                            --원격서버의 Password

 

 

 

예시) sp_serveroption(Transact-SQL)
EXEC SP_SERVEROPTION

@server='MAIL',                                    --연결된 서버를 명시할 사용자가 정의한 이름

@optname='RPC',                                 --RPC 사용 옵션

@optvalue='TRUE'                            --RPC 사용 함

 

EXEC SP_SERVEROPTION

@server='MAIL',                                    --연결된 서버를 명시할 사용자가 정의한 이름

@optname='RPC OUT',                        --RPC 내보내기사용 옵션

@optvalue='TRUE'                            --RPC 내보내기사용 함

 

 

 

 

 

 

출처 : http://technet.microsoft.com/ko-kr/library/ms190479.aspx

 

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]
     [ , [ @provider= ] 'provider_name' ]
     [ , [ @datasrc= ] 'data_source' ]
     [ , [ @location= ] 'location' ]
     [ , [ @provstr= ] 'provider_string' ]
     [ , [ @catalog= ] 'catalog' ]

 

[ @server= ] 'server'

만들려고 하는 연결된 서버의 이름입니다. server는 sysname이며 기본값은 없습니다.

[ @srvproduct= ] 'product_name'

연결된 서버로 추가할 OLE DB 데이터 원본의 제품 이름입니다. product_name은 nvarchar(128)이며 기본값은 NULL입니다. SQL Server의 경우 provider_name, data_source, location, provider_string, catalog를 지정할 필요가 없습니다.

[ @provider= ] 'provider_name'

이 데이터 원본에 해당하는 OLE DB 공급자의 고유 PROGID(프로그래밍 ID)입니다. provider_name은 현재 컴퓨터에 설치된 지정된 OLE DB 공급자에 대해 고유해야 합니다. provider_name은 nvarchar(128)이며 기본값은 NULL입니다. 하지만 provider_name이 생략된 경우 SQLNCLI가 사용됩니다. (SQLNCLI 및 SQL Server를 사용하면 SQL Server Native Client OLE DB 공급자의 최신 버전으로 리디렉션됩니다. OLE DB 공급자는 레지스트리에서 지정된 PROGID를 사용하여 등록해야 합니다.

[ @datasrc= ] 'data_source'

OLE DB 공급자가 해석하는 데이터 원본의 이름입니다. data_source는 nvarchar(4000)입니다. data_source는 DBPROP_INIT_DATASOURCE 속성으로 전달되어 OLE DB 공급자를 초기화합니다.

[ @location= ] 'location'

OLE DB 공급자에 의해 해석된 데이터베이스의 위치입니다. location은 nvarchar(4000)이며 기본값은 NULL입니다. location은 DBPROP_INIT_LOCATION 속성으로 전달되어 OLE DB 공급자를 초기화합니다.

[ @provstr= ] 'provider_string'

고유한 데이터 원본을 나타내는 OLE DB 공급자의 연결 문자열입니다. provider_string은 nvarchar(4000)이며 기본값은 NULL입니다. provstr은 IDataInitialize로 전달되거나 DBPROP_INIT_PROVIDERSTRING 속성으로 설정되어 OLE DB 공급자를 초기화합니다.

SQL Server Native Client OLE DB 공급자에 대해 연결된 서버가 생성되면 SERVER=servername\instancename과 같이 SERVER 키워드를 사용하여 특정 SQL Server 인스턴스를 지정할 수 있습니다. servername은 SQL Server가 실행되고 있는 컴퓨터의 이름이고 instancename은 사용자가 연결될 특정 SQL Server 인스턴스의 이름입니다.

참고
미러된 데이터베이스에 액세스하려면 연결 문자열이 데이터베이스 이름을 포함해야 합니다. 이 이름은 데이터 액세스 공급자의 장애 조치(Failover) 시도를 지원하는 데 필요합니다. 데이터베이스를 @provstr 또는 @catalog 매개 변수에 지정할 수 있습니다. 필요에 따라 연결 문자열이 장애 조치(Failover) 파트너 이름을 제공할 수도 있습니다.

[ @catalog= ] 'catalog'

OLE DB 공급자에 연결할 때 사용되는 카탈로그입니다. catalog는 sysname이며 기본값은 NULL입니다. catalog는 DBPROP_INIT_CATALOG 속성으로 전달되어 OLE DB 공급자를 초기화합니다. SQL Server 인스턴스에 대해 연결된 서버를 정의한 경우 카탈로그는 연결된 서버가 매핑된 기본 데이터베이스를 참조합니다.

 

 

 

출처 : http://technet.microsoft.com/ko-kr/library/ms189811.aspx

 

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
     [ , [ @useself = ] 'TRUE' | 'FALSE' | NULL ]
     [ , [ @locallogin = ] 'locallogin' ]
     [ , [ @rmtuser = ] 'rmtuser' ]
     [ , [ @rmtpassword = ] 'rmtpassword' ]

 

[ @rmtsrvname = ] 'rmtsrvname'

로그인 매핑이 적용되는 연결된 서버의 이름입니다. rmtsrvname은 sysname이며 기본값은 없습니다.

[ @useself = ] 'TRUE' | 'FALSE' | 'NULL'

로컬 로그인을 가장하거나 로그인 및 암호를 명시적으로 제출하여 rmtsrvname에 연결할지를 결정합니다. 데이터 형식은 varchar(8)이며 기본값은 TRUE입니다.

값이 TRUE인 경우 로그인이 해당 자격 증명을 사용하여 rmtsrvname에 연결하고 rmtuser 및 rmtpassword 인수를 무시하도록 지정합니다. FALSE인 경우 지정된 locallogin에 대해 rmtuser 및 rmtpassword 인수를 사용하여 rmtsrvname에 연결하도록 지정합니다. rmtuser 및 rmtpassword도 NULL로 설정한 경우에는 연결된 서버에 연결하는 데 로그인이나 암호가 사용되지 않습니다.

[ @locallogin = ] 'locallogin'

로컬 서버의 로그인입니다. locallogin은 sysname이며 기본값은 NULL입니다. NULL은 rmtsrvname에 연결하는 모든 로컬 로그인에 해당 항목이 적용되도록 지정합니다. NULL이 아닌 경우 locallogin은 SQL Server 로그인 또는 Windows 로그인이 될 수 있습니다. Windows 로그인은 직접적인 방법으로든 또는 액세스 권한이 있는 Windows 그룹의 멤버 자격을 이용한 방법으로든 반드시 SQL Server에 대한 액세스 권한을 보유해야 합니다.

[ @rmtuser = ] 'rmtuser'

@useself가 FALSE인 경우 rmtsrvname에 연결하는 데 사용되는 원격 로그인입니다. 원격 서버가 Windows 인증을 사용하지 않는 SQL Server 인스턴스인 경우 rmtuser는 SQL Server 로그인입니다. rmtuser는 sysname이며 기본값은 NULL입니다.

[ @rmtpassword = ] 'rmtpassword'

rmtuser와 연결된 암호입니다. rmtpassword는 sysname이며 기본값은 NULL입니다.

 

 

 

출처 : http://technet.microsoft.com/ko-kr/library/ms178532.aspx

 

sp_serveroption [@server = ] 'server' 
      ,[@optname = ] 'option_name'    
      ,[@optvalue = ] 'option_value' ;

 

[ @server = ] 'server'

옵션을 설정할 서버의 이름입니다. server는 sysname이며 기본값은 없습니다.

[ @optname = ] 'option_name'

지정한 서버에 설정할 옵션입니다. option_name은 varchar(35)이며 기본값은 없습니다. option_name은 다음 값 중 하나일 수 있습니다.

값	설명
collation compatible	연결된 서버에 대한 분산 쿼리 실행에 영향을 줍니다. 이 옵션을 true로 설정하면 SQL Server에서는 연결된 서버의 모든 문자에 대한 문자 집합 및 데이터 정렬 시퀀스(또는 정렬 순서)가 로컬 서버와 호환된다고 가정합니다. 이렇게 함으로써 SQL Server에서 문자 열에 관한 비교를 공급자에 전달할 수 있습니다. 이 옵션을 설정하지 않으면 SQL Server에서 항상 문자 열에 관한 비교를 로컬로 평가합니다. 이 옵션은 연결된 서버에 해당되는 데이터 원본이 로컬 서버와 동일한 문자 집합 및 정렬 순서를 갖고 있는 것이 확실한 경우에만 설정해야 합니다.
collation name	use remote collation이 true이고 데이터 원본이 SQL Server가 아닌 경우에 원격 데이터 원본에서 사용하는 데이터 정렬의 이름을 지정합니다. 이름은 반드시 SQL Server에서 지원하는 데이터 정렬 중 하나여야 합니다. SQL Server가 아닌 OLE DB 데이터 원본에 액세스할 때 해당 데이터 정렬이 SQL Server 데이터 정렬 중 하나와 일치하면 이 옵션을 사용하십시오. 연결된 서버는 반드시 해당 서버의 모든 열에 대해 사용할 단일 데이터 정렬을 지원해야 합니다. 연결된 서버에서 단일 데이터 원본 내에 여러 데이터 정렬을 지원하거나 연결된 서버의 데이터 정렬이 SQL Server 데이터 정렬 중 하나와 일치하는지 확인할 수 없는 경우에는 이 옵션을 사용하지 않습니다.
connect timeout	연결된 서버에 연결하는 제한 시간 값(초)입니다. 0으로 설정한 경우 sp_configure 기본값을 사용합니다.
data access	분산 쿼리 액세스에 대해 연결된 서버를 사용하거나 사용하지 않습니다. sp_addlinkedserver를 통해 추가된 sys.server 항목에 대해서만 사용할 수 있습니다.
dist	배포자입니다.
지연된 스키마 유효성 검사	원격 테이블의 스키마 확인 여부를 결정합니다. true인 경우 쿼리 시작 부분에서 수행되는 원격 테이블에 대한 스키마 확인은 건너뜁니다.
pub	게시자입니다.
query timeout	연결된 서버에 대한 쿼리의 제한 시간 값입니다. 0으로 설정한 경우 sp_configure 기본값을 사용합니다.
rpc	지정된 서버로부터의 RPC가 가능하도록 합니다.
rpc out	지정된 서버로의 RPC가 가능하도록 합니다.
sub	구독자입니다.
시스템	정보를 제공하기 위해서만 확인됩니다. 지원되지 않습니다. 향후 호환성은 보장되지 않습니다.
원격 데이터 정렬 사용	원격 열의 데이터 정렬을 사용할지 로컬 서버의 데이터 정렬을 사용할지 결정합니다. true로 설정하면 SQL Server 데이터 원본에 원격 열의 데이터 정렬이 사용되고 SQL Server가 아닌 데이터 원본에 대해서는 collation name에 지정된 데이터 정렬이 사용됩니다. false로 설정된 경우 분산 쿼리가 항상 로컬 서버의 기본 데이터 정렬을 사용하며 collation name 및 원격 열의 데이터 정렬은 무시됩니다. 기본값은 false입니다. false 값은 SQL Server 7.0에서 사용하는 데이터 정렬 방식과 호환됩니다.
remote proc transaction promotion	이 옵션을 사용하여 MS DTC(Microsoft Distributed Transaction Coordinator) 트랜잭션을 통해 서버 간 프로시저 동작을 보호할 수 있습니다. 이 옵션이 TRUE(또는 ON)인 경우 원격 저장 프로시저를 호출하면 분산 트랜잭션이 시작되고 MS DTC를 사용해 이 트랜잭션을 참여시킵니다. 원격 저장 프로시저를 호출하는 SQL Server 인스턴스가 트랜잭션 주관자이며 트랜잭션의 완료를 제어합니다. 이후 연결에 대해 COMMIT TRANSACTION 또는 ROLLBACK TRANSACTION 문을 실행하면 제어 인스턴스는 MS DTC에서 관련 컴퓨터 간의 분산 트랜잭션 완료를 관리하도록 요청합니다. Transact-SQL 분산 트랜잭션이 시작되면 연결된 서버로 정의된 SQL Server의 다른 인스턴스에 대해 원격 저장 프로시저를 호출할 수 있습니다. 연결된 서버를 모두 Transact-SQL 분산 트랜잭션에 참여시키고 MS DTC는 각 연결된 서버에 대해 트랜잭션이 완료되도록 합니다. 이 옵션을 FALSE(또는 OFF)로 설정하면 연결된 서버에서 원격 프로시저를 호출하는 동안에는 로컬 트랜잭션이 분산 트랜잭션으로 승격되지 않습니다. 서버 간 프로시저 호출을 수행하기 전에 트랜잭션이 이미 분산 트랜잭션인 경우 이 옵션은 영향을 주지 않습니다. 연결된 서버에 대한 프로시저 호출은 동일 분산 트랜잭션에서 실행됩니다. 서버 간 프로시저 호출을 수행하기 전에 연결에 활성 상태인 트랜잭션이 없는 경우 이 옵션은 영향을 주지 않습니다. 그런 다음 활성 상태의 트랜잭션 없이 연결된 서버에 대해 프로시저가 실행됩니다. 이 옵션의 기본값은 TRUE(또는 ON)입니다.

[ @optvalue =] 'option_value'

option_name이 설정(TRUE 또는 on)되거나 해제(FALSE 또는 off)되어야 하는지 여부를 지정합니다. option_value는 varchar(10)이며 기본값은 없습니다.

option_value는 connect timeout 및 query timeout 옵션 값에 해당하는 음수가 아닌 정수가 될 수 있습니다. collation name 옵션의 경우 option_value는 데이터 정렬 이름 또는 NULL이 될 수 있습니다.