[MSSQL] OPENROWSET, OPENDATASOURCE, OPENQUERY 와 Linked Server 성능, 장단점, 원격 접속 방법

OPENROWSET

---

OPENROWSET   

( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'   

   | 'provider_string' }   

   , {   [ catalog. ] [ schema. ] object   

       | 'query'   

     }   

   | BULK 'data_file' ,   

       { FORMATFILE = 'format_file_path' [ <bulk_options> ]  

       | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }  

} )   

  

<bulk_options> ::=  

   [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]   

   [ , DATASOURCE = 'data_source_name' ]

   [ , ERRORFILE = 'file_name' ]  

   [ , ERRORFILE_DATASOURCE = 'data_source_name' ]   

   [ , FIRSTROW = first_row ]   

   [ , LASTROW = last_row ]   

   [ , MAXERRORS = maximum_errors ]   

   [ , ROWS_PER_BATCH = rows_per_batch ]  

   [ , ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) [ UNIQUE ] ]

  

   -- bulk_options related to input file format

   [ , FORMAT = 'CSV' ]

   [ , FIELDQUOTE = 'quote_characters']

   [ , FORMATFILE = 'format_file_path' ]   

링크드 서버를 사용하지 않고, 커넥션 스트링을 사용해서 원격지에 연결하는 방식을 의미합니다.

SQLOLEDB 를 사용하여 원격 접속하는 방법입니다.

2번째 줄에서 IP, Port ; ID ; Password  순으로 입력하면 접근할 수 있습니다.

SELECT * FROM OPENROWSET('SQLOLEDB',

                         '192.168.56.101,2433';'sa';'P@ssword',

                         ' SELECT ''Openrowset '' + @@SERVERNAME');

OPENDATASOURCE

---

OPENDATASOURCE ( provider_name, init_string )  

위와 동일하게 링크드 서버를 사용하지 않고, 커넥션 스트링을 사용해서 원격지에 연결하는 방식을 의미합니다.

위의 OPENROWSET 과 다른 점은 쿼리가 ( ) 뒤에 작성되어야 합니다.

SELECT * FROM OPENDATASOURCE('SQLOLEDB',

                             'Data Source=192.168.56.101,2433; User ID=sa; Password=P@ssword')

                            .'SELECT ''OpenDataSource '' + @@SERVERNAME'

OPENQUERY

---

OPENQUERY ( linked_server ,'query' )  

링크드 서버를 사용하여 원격지에 접속하는 방식을 의미합니다.

따라서 로컬 서버에는 원격지에 대한 접속 정보가 등록되어 있어야 합니다.

링크드 서버를 추가하는 2가지 방식입니다.

2개를 생성하였을 때 링크드 서버의 명칭을 확인하시고 원하는 방향으로 생성해주시면 됩니다.

EXEC master.dbo.sp_addlinkedserver @server = N'192.168.56.101,2433',

                                   @srvproduct=N'SQL Server'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'192.168.56.101,2433',

                                     @useself=N'False',

                                     @locallogin=NULL,

                                     @rmtuser=N'sa',

                                     @rmtpassword='P@ssword'

--====================================================

EXEC master.dbo.sp_addlinkedserver 'LogShippingDB',

                                   '',

                                   'SQLOLEDB',

                                   '192.168.56.101,2433',

                                   '',

                                   ''

EXEC master.dbo.sp_addlinkedsrvlogin 'LogShippingDB',

                                     'False',

                                     NULL,

                                     'sa',

                                     'P@ssword'

이후 생성된 링크드 서버를 이용하여 원격지에 접속합니다.

SELECT * FROM OPENQUERY([192.168.56.101,2433], 'SELECT ''OPENQUERY 192.168.56.101,2433 '' + @@SERVERNAME')

SELECT * FROM OPENQUERY([LogShippingDB], 'SELECT ''OPENQUERY LogShippingDB '' + @@SERVERNAME')

Linked Server

---

이번에는 링크드 서버 자체를 쿼리에 작성하여 수행하는 방법입니다.

SELECT * FROM [192.168.56.101,2433].LogShippingDB.dbo.LogShippingTab

SELECT * FROM [LogShippingDB].LogShippingDB.dbo.LogShippingTab

추가적으로

링크드 서버 자체를 특정 쿼리에 붙이는 경우에는 다른 방법을 사용해야 합니다.

EXEC [LogShippingDB].master.sys.sp_executesql N'SELECT ''Linked Server '' + @@SERVERNAME'

만약 RPC에 대해 구성되지 않았습니다.  라는 오류가 나오면 해당 링크드 서버의 RPC 기능을 활성화 한 뒤에 수행하면 됩니다.

EXEC sp_serveroption 'LogShippingDB', 'RPC', TRUE

EXEC sp_serveroption 'LogShippingDB', 'RPC OUT', TRUE

각 기능의 장단점, 정리

---

Four Part 이름을 사용하는 Linked Server 자체를 수행하는 경우 로컬 서버의 리소스를 사용하게 됩니다.

(Four Part 란 [Server Name].[Database Name].[Schema Name].[Object Name] 을 사용하는 쿼리 )

즉, 원격 서버가 동일한 SQL 서버인 경우에는 아래와 같은 시스템 저장 프로시저 실행을 요청해서 반환 받은 정보를 바탕으로

로컬 서버가 최적화를 한 뒤에 원격 서버에서 쿼리를 실행하게 됩니다.

 - sp_tableinfo_rowset

 - sp_columns_rowset

 - sp_index_rowset 등

따라서 Four Part 의 경우는 SELECT 에 있어서 Pass-Through 쿼리에 비해 비효율 적일 수 있습니다.

OPENROWSET/OPENQUERY 의 경우 Pass-Through 방식으로 쿼리 자체를 원격 서버에 보내서 최적화 작업을 원격 서버가 수행하며, 그에 따른 결과만을 받게 됩니다. 따라서 해당 방식으로는 수행 속도가 크게 차이나지 않습니다.

차이점은 OPENQUERY 는 매개변수에 Linked Server 만 작성할 수 있으며, OPENROWSET 은 Linked Server 를 작성할 수 없습니다.

DML 의 경우에는 OLEDB Provide 의 제약으로 인해 구문을 전송하지 못하므로 Work Table 을 생성해야 하며 이런 경우에는 Four Part 를 사용한 Linked Server 를 사용하는 것이 좋습니다.