Amazon RDS for SQL Serverから別のRDS for SQL Serverに対してクエリを実行してみた

いわさです。

Amazon RDS for SQL Serverを使った複数のシステムで、システム間のデータ連携を行うシーンがありました。
その際に、あるデータベースサーバーから別のデータベースサーバーへアクセス出来るのか、気になりました。

Microsoft SQL Serverでは分散クエリやリンクサーバーを使うことで外部データベースサーバーのデータを扱うことができます。
本日は、Amazon RDS for SQL Serverでもそれらの機能が使えるのかを確認してみました。

確認環境

パブリックサブネットにRDS for SQL Serverを２つ作成します。
２つのRDS間のTCP接続は許可しています。

今回はパブリックアクセスを有効化しており、ローカル環境から接続して適当なデータベースとテーブル、レコードを作成しておきました。

便宜上、RDS1とRDS2と呼びますね。
RDS1にはfugaデータベースを作成します。

1> create database fuga
2> go
1> use fuga;
2> go
Changed database context to 'fuga'.
1> create table members(id int identity(1,1) primary key, name nvarchar(32), birthday datetime);
2> go
1> insert into members (name) values ('a1');
2> insert into members (name) values ('a2');
3> insert into members (name) values ('a3');
4> go

(1 rows affected)

(1 rows affected)

(1 rows affected)
1> select * from members;
2> go
id          name                             birthday               
----------- -------------------------------- -----------------------
          1 a1                                                  NULL
          2 a2                                                  NULL
          3 a3                                                  NULL

(3 rows affected)

RDS2にはhogeデータベースを作成します。
作成するレコードはちょっと変えています。

1> create database hoge;
2> go
1> use hoge;
2> go
Changed database context to 'hoge'.
1> create table members(id int identity(1,1) primary key, name nvarchar(32), birthday datetime);
2> go
1> insert into members (name) values ('b1');
2> insert into members (name) values ('b2');
3> insert into members (name) values ('b3');
4> go

(1 rows affected)

(1 rows affected)

(1 rows affected)
1> select * from members;
2> go
id          name                             birthday               
----------- -------------------------------- -----------------------
          1 b1                                                  NULL
          2 b2                                                  NULL
          3 b3                                                  NULL

(3 rows affected)

リンクサーバー

まずは、リンクサーバーを試してみます。
リンクサーバーを使うことでSQL Server同士あるいは別の外部DBなどへコマンドを実行したり、クエリしたり出来るようになります。

SSMSかTransact-SQLで設定が可能なのですが、Amazon RDSの場合だと権限の問題で、SSMS上からの有効化が使えません。
しかし、Transact-SQLのsp_addlinkedserverとsp_addlinkedsrvloginを使うことで設定が可能です。

RDS1側でリンクサーバーの設定をします。

ここでは、リンクサーバー名をLinkedRDSとしています。
便宜上、RDS1への接続情報はユーザー名iwasa、パスワードをpasswordとしています。

1>  EXEC master.dbo.sp_addlinkedserver @server = N'LinkedRDS', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'hoge-mssql-2.cpnu9ipu74g4.ap-northeast-1.rds.amazonaws.com';
2> go
1>  EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LinkedRDS',@useself=N'False',@locallogin=NULL,@rmtuser=N'iwasa',@rmtpassword='password';
2> go

設定したら以下でリンクサーバーの確認をします。

1> SELECT name FROM LinkedRDS.master.sys.databases;
2> go
name                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------
master                                                                                                                          
tempdb                                                                                                                          
model                                                                                                                           
msdb                                                                                                                            
rdsadmin                                                                                                                        
hoge                                                                                                                            

(6 rows affected)

RDS2のデータベースが見えていますね。

RDS1からRDS2のデータをクエリしてみます。

1> select * from fuga.dbo.members;
2> go
id          name                             birthday               
----------- -------------------------------- -----------------------
          1 a1                                                  NULL
          2 a2                                                  NULL
          3 a3                                                  NULL

(3 rows affected)
1> select * from LinkedRDS.hoge.dbo.members;
2> go
id          name                             birthday               
----------- -------------------------------- -----------------------
          1 b1                                                  NULL
          2 b2                                                  NULL
          3 b3                                                  NULL

(3 rows affected)

取得できました！
ちょっとリモートアクセスしてるようなレイテンシーは感じました。
このあたりは利用にあたってしっかり評価したほうが良さそうです。

次に結合してみます。

1> select t1.id, t1.name, t2.name from fuga.dbo.members t1 inner join LinkedRDS.hoge.dbo.members t2 on t1.id = t2.id;
2> go
id          name                             name                            
----------- -------------------------------- --------------------------------
          1 a1                               b1                              
          2 a2                               b2                              
          3 a3                               b3                              

(3 rows affected)

おお、これもいけますね。
データの作成はどうか。

1> insert into LinkedRDS.hoge.dbo.members (name) values ('b4');
2> go

(1 rows affected)
1> select * from LinkedRDS.hoge.dbo.members;
2> go
id          name                             birthday               
----------- -------------------------------- -----------------------
          1 b1                                                  NULL
          2 b2                                                  NULL
          3 b3                                                  NULL
          4 b4                                                  NULL

(4 rows affected)

こちらも問題ないですね。とても良いです。

アドホック分散クエリ

リンクサーバー以外に、アドホック分散クエリというのもあります。
こちらはリンクサーバーの設定は不要で、クエリの際にリモートサーバーに関する設定を指定します。

ただし、SQL Serverとして、既定はオフになっています。RDSでもオフです。
有効化しないとエラーメッセージが表示されます。

1> select * from OPENROWSET('SQLNCLI', 'hoge-mssql-2.cpnu9ipu74g4.ap-northeast-1.rds.amazonaws.com';'iwasa';'password', 'select * from hoge.dbo.members');
2> go
Msg 15281, Level 16, State 1, Server EC2AMAZ-NRIPPA5, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

そこで、ドキュメントにしたがってT-SQLで有効化をしようとするのですが、RDSの場合はエラーになります。

1> EXEC master.dbo.sp_configure 'Ad Hoc Distributed Queries', 1;
2> go
Msg 15247, Level 16, State 1, Server EC2AMAZ-ECALCAG, Procedure master.dbo.sp_configure, Line 105
User does not have permission to perform this action.
1>

RDSでの対処としては、パラメータグループから有効化を行う必要があります。

1> select * from OPENROWSET('SQLNCLI', 'hoge-mssql-2.cpnu9ipu74g4.ap-northeast-1.rds.amazonaws.com';'iwasa';'password', 'select * from hoge.dbo.members');
2> go
id          name                             birthday               
----------- -------------------------------- -----------------------
          1 b1                                                  NULL
          2 b2                                                  NULL
          3 b3                                                  NULL
          4 b4                                                  NULL

(4 rows affected)

有効化後、無事にリンクサーバー設定なしでリモートサーバーにクエリを発行することができました。

さいごに

本日はRDS for SQL Serverを使って、リンクサーバーとアドホック分散クエリを使うことで、複数データベース間のクエリ実行が出来ることを確認しました。
少しAWSならではの癖はありますが、概ねSQL Serverのドキュメントのとおり機能を確認することができましたね。

ちなみに、アドホック分散クエリだと、リンクサーバーを作成しなくても良いですが、頻繁にアクセスするのであればリンクサーバーの利用が推奨されています。
リンクサーバー利用時の注意事項も含め、以下を参考にしてください。

• リモート サーバーのクエリ (データベース エンジン) - SQL Server | Microsoft Docs
• リンク サーバー間でビューをクエリする場合のパフォーマンスの問題 - SQL Server | Microsoft Docs