この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
いわさです。
Amazon RDS for SQL Serverを使った複数のシステムで、システム間のデータ連携を行うシーンがありました。
その際に、あるデータベースサーバーから別のデータベースサーバーへアクセス出来るのか、気になりました。
Microsoft SQL Serverでは分散クエリやリンクサーバーを使うことで外部データベースサーバーのデータを扱うことができます。
本日は、Amazon RDS for SQL Serverでもそれらの機能が使えるのかを確認してみました。
確認環境
パブリックサブネットにRDS for SQL Serverを2つ作成します。
2つのRDS間のTCP接続は許可しています。
今回はパブリックアクセスを有効化しており、ローカル環境から接続して適当なデータベースとテーブル、レコードを作成しておきました。
便宜上、RDS1とRDS2と呼びますね。
RDS1にはfuga
データベースを作成します。
RDS 1
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
データベースを作成します。
作成するレコードはちょっと変えています。
RDS 2
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のドキュメントのとおり機能を確認することができましたね。
ちなみに、アドホック分散クエリだと、リンクサーバーを作成しなくても良いですが、頻繁にアクセスするのであればリンクサーバーの利用が推奨されています。
リンクサーバー利用時の注意事項も含め、以下を参考にしてください。