SQL Server2008でCLR Enabled + ちょびっとJenkins利用
SQL Server2008のCLRを久しぶりに使う機会があったので、メモ。
- SQL ServerのCLRは、Oracleで言うところのJavaストアドのようなものです。
- DB内にアセンブリを登録し、それをSQLの中から呼び出します。
- 通常のSQLや付属の関数だけでは賄いきれない機能を、.NETのアセンブリを通して実行できます。
- 私の場合は、たとえば、コミットに失敗した際にDB内からのメール送信したり、複雑な文字列処理をするために使っています。
0. つくるもの
私の場合は、コーディング自体は、VisualStudio2010を使います。＃昔Javaをかじった関係で、書くときは通常C#を使うのですが、恥ずかしながらクラスとか設計とかオブジェクト指向とかデザインパターン云々…は全く分からず、staticばっかりとか、かなり難があるのですが…。コードはいずれということで、下記のようなものを登録します。
アセンブリ
- こちらはVisualStudio上でビルドします。(Exp. SampleCLR.dll)
- この中に、testFunction という関数を作ります。
- おなじく、testFunction という名前で、dboスキーマにユーザ定義関数を作ります。この時に、SampleCLRのtestFunctionとの関連付けを行います。
1. DBの設定変更
SQL Server2005では、Area Surface ManagerというGUIでの設定変更ツールがあったのですが、2008では無くなった模様。CLRは久しぶりだったので、その必要が出てくるまで気が付きませんでした。まず、DBのセキュリティ設定を調整し、CLR統合オプションを有効にしないといけません。
設定は、下記のようなSQLで変更をします。（ちなみに、DBはmasterでもmodelでもAdventureWorksでもどれでも大丈夫）
EXEC sp_CONFIGURE 'show advanced options' , '1';
GO
RECONFIGURE;
GO
EXEC sp_CONFIGURE 'clr enabled' , '1'
GO
RECONFIGURE;
GO
設定がうまくできているかどうかの確認は、sp_CONFIGURE 'clr
enabled' で問い合わせします。2. Assemblyを登録する
まず、Assemblyは、実稼働しているSQL Serverのローカルのフォルダか、SQL Serverのプロセスが参照できるフォルダに置かないとダメなようです。今回は、いったん、DeployしたいSQL Server上に、D:\tmp\SampleCLR.dll としてアップロードしておきます。対象は、”TestDB”というデータベースです。以下のような感じのSQLで登録を行います。
なお、VisualStudioで行う場合は、VisualStudioプロジェクト中に、作成されたアセンブリと同じフォルダに、Deploy専用のSQLが作成されます。
アセンブリ名 + sql というファイル名です。
ここを参照すると、どういうことを行っているかの流れは掴めると思います。
アセンブリの登録から関数の作成までは、上記のようなスクリプトによる処理が必要です。// TestDBを選択use TestDB go // 先に testFunction という名前の関数があったら削除します IF EXISTS ( SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.testFunction’) ) DROP FUNCTION dbo.testFunction go-- アセンブリがあったらいったん削除（もしくはALTERで、DROP FILE, ADD FILE）IF EXISTS ( SELECT 1 FROM sys.assemblies asms WHERE asms.name = N'SampleCLR' ) DROP ASSEMBLY [SampleCLR]-- アセンブリ自体を再登録CREATE ASSEMBLY SampleCLR FROM 'D:\tmp\SampleCLR.dll' GO-- アセンブリを使って再登録 -- 今回の関数は文字列を引数に取り、変換して返すものなので、こんな感じで定義。 -- ポイントは、AS EXTERNAL NAMEのところです。 CREATE FUNCTION testFunction (@arg NVARCHAR (100)) RETURNS NVARCHAR (100) AS EXTERNAL NAME [SampleCLR].[UserDefinedFunctions].[testFunction] GO
テストや開発環境には、VisualStudio （以下、VS) から行っても良いのですが、本番サーバだと、自分以外、自分の環境以外からもDeployできないと困ります。
そこで、Jenkinsで行う場合は、どうすればいいかな…と考えて、こんな感じにしてみました。（Microsoftの開発環境に関する知識は乏しいので、もっと良い方法があるかもしれませんが）
* * *
1. VSでコードを書き、ビルド。ローカルや開発環境で動作確認を行う。
2. 開発環境でのデプロイを行う際に生成されたSQLを元に、本番用に対する、Assenblyの登録と関数・ストアドの定義用のSQLファイルを作成する。
3. SVNなどのリポジトリにコミットする。
（VisualStudioプロジェクトファイル全部でもいいけれど、最低限、ビルドしたアセンブリとデプロイ用のSQLを登録）
4. 本番DBがあるサーバに対し、Jenkinsのマスタもしくはスレーブエージェントを利用できるようにしておく。
5. Jenkinsのジョブを作成し、リポジトリから、DLLと、アセンブリ＆CLR関数再定義用のSQLをチェックアウト。
6. DOSプロンプトから SQLスクリプトをコマンドライン実行する。(Jenkins上では、Windowsバッチコマンドとして作成）
なお、Jenkinsを使って、VSのライブラリが入ったビルドサーバで、ビルドさせることまで行ってもいいのですが、VSでDLLもコミットしてしまうなら、ビルドサーバは必要なし。ソースコードを修正すづだけで、誰でもDLLを作成できるようにするなら必要かなと思います。さて、この流れでやってみたところ、実際に登録は出来ました。
ただし、アセンブリをチェックアウトするのは、通常Jenkinsのジョブのワークスペース以下です。
また、Deploy用のSQLには、アセンブリが実際に置かれているパスを指定しないといけません。
Jenkinsのジョブのワークスペースは、環境変数からWindowsのコマンドに渡せますが、SQL ServerのTransact-SQLでは、通常、システムやユーザの環境変数にアクセスすることができません。
かわりに、DOSから sqlcmd を起動して、Deploy用のSQLを実行するように作っているので、sqlcmdの引数として変数を指定すれば、Transact-SQLに、Jenkinsの環境変数である、％WORKSPACE%を渡すことができます。
-v オプションで、変数を渡せるとのこと。
- 参考: sqlcmd でのスクリプト変数の使用
- http://msdn.microsoft.com/ja-jp/library/ms188714.aspx
結局、このようにしてみました。
(1) JenkinsのWindows バッチコマンドでの設定
- sqlcmd -v workspace="%WORKSPACE%" -U xxx -P xxxxxx -i DeployScript.sql
- -v で変数を指定、-i でDeploy用のSQL文を実行。
CREATE ASSEMBLY [SampleCLR]こうして、めでたく？ $(変数名) という記述の仕方で、Transact-SQLの中に値を渡せました。
AUTHORIZATION [dbo]
FROM '$(workspace)\bin\SampleCLR.dll'
WITH PERMISSION_SET = SAFE;
GO
わざわざここまでする必要も無いでしょうし、Microsoftのソリューションでもっと良いものがあるとは思いますが、ついついJenkins使って考えたくなってしまう日々です。
コメント
コメントを投稿