두 개의 MS SQL 서버가 존재한다고 가정을 하겠습니다. 그리고 서버의 이름은 SVR1과 SVR2라고 가정을 하겠습니다. 이런 상황에서 SVR1의 데이터베이스에 연결이 된 상태에서 SVR2 서버의 데이터베이스 내용을 참고해야 하는 경우가 발생 할 수 있습니다. 실제 업무에서 본다면 회계용 MS SQL 서버에 접속한 상태에서 특별한 목적으로 인사용 MS SQL 서버의 데이터베이스의 내용이 필요한 경우가 발생할 수 있습니다.
얼핏 생각하기에 두 서버가 MS SQL 서버가 설치가 되어 있고 네트워크로 연결이 되어 있으므로 그냥 쿼리문을 수행하면 될거라 생각하지만 그렇지 않습니다.
이 처럼 다른 MS SQL와 연결하여 작업을 해야하는 경우 사용되는 것이 연결된 서버(Linked Server)입니다. 연결된 서버를 이용하게 되면 다른 서버에 위치하고 있는 데이터베이스를 손쉽게 접근할 수 있으며, 심지어는 MS SQL 서버가 아니더라도 다른 종류의 데이터를 쉽게 접근 할 수 있습니다. 예를 들어 엑셀 파일을 데이터베이스 서버처럼 사용 할 수 도 있고 심지어 텍스트 파일도 데이터베이스 서버처럼 사용을 할 수 도 있습니다. 이에 대한 내용들을 살펴보도록 하겠습니다.
1. 연결된 서버 만들기
대부분의 작업들이 그러하듯이 연결된 서버를 만드는 것은 EM(Enterprise Manager)를 이용해서도 가능하고 QA(Query Analyzer)를 이용해서도 가능합니다. 이 두가지 방법은 순서대로 살펴보도록 하겠습니다.
그런데 이 강좌에 있어 전제 사항이 있습니다. 연결된 서버의 경우 두대의 MS SQL 서버를 연결해야 하는데 이 강좌를 작성하고 있는 지금 제게는 두대의 MS SQL 서버가 없습니다. 이런 이유도 강좌를 작성하는데는 두개의 MS SQL 서버 인스턴스를 사용하도록 하겠습니다. MS SQL 서버 2000은 하나의 서버에 여러개의 MS SQL 서버를 만들 수 있습니다. 이것이 인스턴스 입니다. 처음에 기본 인스턴스로 설치되면 이때 설치된 MS SQL 서버가 서버 이름이 되며 그 이후에 설치되는 MS SQL 서버는 "서버이름\인스턴스이름"을 이름으로 갖게 됩니다.
이 강좌에서는 다음의 인스턴스가 사용됩니다.
o FUTURE
o FUTURE\INST01
위 두개의 인스턴스가 개별적인 MS SQL 서버라고 생각하시고 강좌를 이해하시면 됩니다. 다음 [그림 1]은 두개의 인스턴스가 설치어 EM에 등록된 모습을 보여주고 있습니다.
[그림 1]
이제 하려고 하는 것은 FUTURE 서버에 접속을 해서 FUTURE\INST01 서버의 데이터베이스를 사용 할 수 있도록 FUTURE\INST01서버를 FUTURE 서버에 연결된 서버로 등록하는 것입니다. 만일 지금 상황에서 FUTURE 서버에 로그인 해서 다음과 같이 FUTURE\INST01 서버의 Pubs 데이터베이스에서 검색을 하려고 하면 어떻게 될까요?
|
|
SELECT * FROM [FUTURE\INST01].Pubs.dbo.Titles |
다음과 같은 에러가 발생하게 됩니다.
|
|
서버: 메시지 7202, 수준 11, 상태 2, 줄 1 |
위 에러메세지만 보아도 연결된 서버(Linked Server)를 만들어야 위 쿼리문이 제대로 수행되며 연결된 서버를 만드는 시스템 저장프로시져가 sp_addlinkedserver 임을 알 수 있습니다. 이 저장프로시져는 뒤에서 살펴보도록 하고 우선 EM을 통해 연결된 서버를 만들어 보도록 하겠습니다.
EM에서 연결된 서버를 등록하는 부분은 다음 [그림 2]와 같이 [보안] 부분의 [연결된 서버] 부분 입니다. 현재는 연결된 서버가 전혀 등록되어 있지 않음을 알 수 있습니다.
[그림 2]
[그림 2]의 "연결된 서버" 에서 마우스 오른쪽 버튼을 눌러 표시되는 단축 메뉴에서 "새 연결된 서버(S)"를 선택하면 다음 [그림 3]과 같이 "연결된 서버 속성" 대화 창이 표시됩니다.
[그림 3]
위 [그림 3]에서 [일반] 탭을 보면 입력할 사항들이 많습니다. 하지만 MS SQL 서버를 연결된 서버로 등록하는 경우는 "연결된 서버" 이름만 입력하고 "서버 유형"에서 "SQL Server" 만 선택하면 됩니다. 다머지 항목들은 MS SQL 서버가 아닌 다른 DBMS와 연결을 하는 경우에 사용됩니다. 이 부분은 나중에 사용을 해보도록 하겠습니다.
저희가 연결된 서버로 등록하려고 하는 것은 FUTURE\INST01 서버입니다. 이 서버를 등록하기 위해 다음 [그림 4] 처럼 입력과 선택을 하면 됩니다.
[그림 4]
이제 [보안] 탭과 [서버 옵션] 탭에 내용을 입력해야 합니다. 우선 [보안] 탭을 살펴 보도록 하겠습니다.
[그림 5]
[그림 5]에서 맨 위의 "로컬 서버 로그인과 원격 서버 로그인 매핑" 부분은 "로컬 로그인"에 지정된 계정으로 로컬 서버로 로그인 해서 연결된 서버에 접속을 하면 "원격 사용자"에 지정된 계정과 "원격 암호"로 지정된 암호를 이용해서 접속한 것으로 가장하라는 설정을 하는 것입니다. 예를 들어 FUTURE 서버에 jangrae 계정이 있고 FUTURE\INST01 서버에 james 라는 계정이 있는 상태에서 jangrae 계정으로 FUTURE 서버에 로그인 한 후 연결된 서버 FUTURE\INST01에 접속하면 james 계정으로 접속 한 것처럼 하고자 하는 경우 다음과 같이 설정하면 됩니다.
[그림 6]
위 [그림 6]에서 "가장" 부분은 원격 서버(연결된 서버 FUTURE\INST01)에 jangrae 라은 계정이 존재 할 경우 체크해 주면 됩니다. [그림 6]의 경우는 로컬 서버(FUTURE)와 원격 서버(FUTURE\INST01)의 계정이 다르기 때문에 계정과 암호를 지정해 준 것입니다.
※ 현재 FUTURE 서버에는 jangrae 계정이 등록되어 있고, FUTURE\INST01 서버에는 james 계정이 등록되어 있는 상태이므로 위 작업이 가능한 것입니다.
[그림 6]에서 지정하는 것은 양쪽 서버(로컬 서버와 원격 서버)의 계정을 1:1 로 지정해주고 있습니다. 하지만 이렇게 계정을 개별적으로 지정하지 않고 연결된 서버를 사용하기 위해서는 아래 부분 "위 목록에서 정의..." 부분을 사용하면 됩니다. 만일 로컬 서버(FUTURE)에 어떤 계정으로 연결되었든지간에 원격 서버(FUTURE\INST01)에 언결된 서버로 연결할 경우 james 계정으로 연결이 되도록 하기 위해서는 다음 [그림 7]과 같이 지정하면 됩니다.
[그림 7]
이렇게 되면 FUTURE 서버에 로그인 하더라도 FUTURE\INST01을 james 계정에 설정된 권한을 이용해 접근해 데이터를 사용 할 수 있습니다. 연결된 서버를 설정할 경우 적절한 권한을 갖고 접근 할 수 있도록 [그림 7]에서 설정을 해주어야 합니다. 만일 [그림 7]에서 FUTURE\INST01의 sa 계정과 암호를 지정하게 되면 FUTURE 서버에 로그인 해서 FUTURE\INST01을 다음대로 접근해 데이터베이스를 사용할 수 있게 됩니다. 이런 방법 보다는 적절한 권한을 갖는 계정을 FUTURE\INST01에 설정하고 그 계정을 이용해 접근이 되도록 지정해야 합니다. [그림 7]에서 지정된 james 계정의 경우 FUTURE\INST01 서버에서 Pubs 데이터베이스에 대한 접근 권한만이 설정되었기 때문에 Pubs 데이터베이스 이외의 데이터베이스에는 접근을 할 수 없게 됩니다.
나머지 설정 부분은 [도움말] 버튼을 눌러 도움말을 참조하여 그 역할을 필히 확인하시기 바랍니다. 아래 [그림 8]은 FUTURE\INST01 서버가 FUTURE 서버에 연결된 서버로 등록되어 테이블 목록이 표시되고 있는 내용입니다.
[그림 8]
위 [그림 8]에서 오른쪽에 표시된 테이블들은 FUTURE\INST01 서버의 Pubs 데이터베이스의 테이블 들입니다. 왜 Pubs 데이터베이스의 테이블이 표시되고 있을 까요?
그 이유는 연결된 서버 설정시 지정된 james 계정의 기본 데이터베이스가 Pubs 이기 때문입니다.
2. 연결된 서버의 사용
이제 FUTURE\INST01을 연결된 서버로 등록하였으므로 FUTURE 서버에 로그인 해서 다음과 같은 쿼리문을 수행하면 원했던 결과가 표시됩니다.
|
SELECT * FROM [FUTURE\INST01].Pubs.dbo.Titles |
연결된 서버의 특정 테이블을 참고하기 위해서는 위 쿼리문에서처럼 "서버이름.데이터베이스이름.소유자.테이블"과 같이 사용해야 합니다. [FUTURE\INST01] 처럼 []를 사용한 이유는 특수문자 \ 가 포함된 인스턴스를 사용하기 때문이며, 일반적인 경우는 []를 사용할 필요가 없습니다.
만일 다음과 같은 쿼리문을 수행하면 결과는 어떨까요?
|
SELECT * FROM [FUTURE\INST01].master.dbo.sysobjets |
다음과 같은 에러가 발생하게 됩니다.
|
서버: 메시지 7314, 수준 16, 상태 1, 줄 1 |
왜냐하면 FUTURE\INST01의 james 계정이 master 데이터베이스에 대한 권한이 없기 때문입니다. james 계정은 FUTURE\INST01의 Pubs 데이터베이스에 대한 권한만을 가지고 있습니다. 그렇다면 다음의 쿼리문은 어떻게 될까요?
|
SELECT * FROM [FUTURE\INST01].Northwind.dbo.Customers |
james 계정에 Northwind 데이터베이스에 대한 접근 권한이 없으므로 에러가 나야 하는게 정상적으로 수행됩니다. 그 이유는 FUTURE\INST01 서버의 Northwind 데이터베이스에 guest 계정이 있기 때문입니다. (이 부분에 대해 이해가 안되시는 문은 로그인계정 관련된 강좌를 참고하시기 바랍니다.)
이제 다음과 같은 쿼리문도 가능합니다.
|
USE Pubs SELECT S.stor_id, S.title_id, T.title |
FUTURE 서버의 Pubs의 Sales 테이블과 FUTURE\INST01 서버의 Pubs의 Titles 테이블을 조인해서 검색을 하고 있습니다. 물론 위 예제의 경우 큰 의미는 없습니다. 하지만 실제 업무에서 이러한 기능은 많은 효과를 가져오게 됩니다.
3. QA를 이용한 연결된 서버 만들기
QA를 이용해서 연결된 서버를 만드는 과정은 다음과 같습니다.
|
USE master EXEC sp_addlinkedserver EXEC sp_addlinkedsrvlogin 'FUTURE\INST01', 'false', NULL, 'james', 'password' |
위 쿼리문에서 sp_addlinkedsrvlogin 부분은 EM에서 [보안] 탭에서 지정하는 방법이 여러가지인것 처럼 이 부분도 여러가지 방법으로 사용이 됩니다. 만일 FUTURE 서버의 jangrae 계정과 FUTURE\INST01 서버의 james 계정을 매핑 시키는 경우는 다음과 같이 해야 합니다.
|
USE master EXEC sp_addlinkedserver EXEC sp_addlinkedsrvlogin 'FUTURE\INST01', 'false', 'jangrae', 'james', 'james' |
앞의 방법과 비교해서 sp_addlinkedsrvlogin 부분이 달라진 것을 볼 수 있습니다. sp_addlinkedsrvlogin 저장프로시져에 대한 자세한 내용은 온라인 설명서를 참고하시기 바랍니다.
4. 정리
이번 강좌에서는 연결된 서버를 만드는 과정을 살펴 보았습니다. 예전에 연결된 서버 만드는 방법을 물어오는 분들이 있었는데 이제야 강좌를 올리게 되었습니다. 너무 늦은 감이 있어 죄송합니다.
이제 연결된 서버를 만드는 방법을 배웠으니 이것을 토대로 해서 다양한 연결된 서버 이용방법을 살펴 보도록 하겠습니다. 예를 든다면 엑셀 파일의 워크시트의 내용을 QA에서 검색하고 입력하는 방법 또는 텍스트 파일의 내용을 QA에서 검색하고 입력하는 방법등입니다. 다음 강좌를 기대해 주시기 바랍니다.
[출처] 디비누리 SQLWorld
