2011-04-13

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 オプションで、変数を渡せるとのこと。

結局、このようにしてみました。
(1) JenkinsのWindows バッチコマンドでの設定
  • sqlcmd -v workspace="%WORKSPACE%" -U xxx -P xxxxxx -i DeployScript.sql
  • -v で変数を指定、-i でDeploy用のSQL文を実行。
(2) Deploy用のSQL文での指定
CREATE ASSEMBLY [SampleCLR]
AUTHORIZATION [dbo]
FROM '$(workspace)\bin\SampleCLR.dll'
WITH PERMISSION_SET = SAFE;
GO
こうして、めでたく? $(変数名) という記述の仕方で、Transact-SQLの中に値を渡せました。

わざわざここまでする必要も無いでしょうし、Microsoftのソリューションでもっと良いものがあるとは思いますが、ついついJenkins使って考えたくなってしまう日々です。

2011-04-11

E-mail Ext Plugin

Jenkinsでとても便利に使っているプラグインの1つが、E-mail Ext Pluginです。

個人的にはビルドの結果はメールで通知するのではなく、画面そのものを見たり、RSSやTwitterでキャッチするほうが好きなのですが、内部では共通で使えるメッセージの基盤というと、やっぱりメールです。

そうはいうものの、Jenkinsの導入当初、サーバがちょっと特殊な環境にあったため、ジョブの結果のメールを送信するのがとても面倒な状況に陥りました。

当時(Hudson)のメールはUTF-8が基本になっていました。わたしの使う環境では、途中の中継サーバがISO-2022-JPしか受け付けてくれず、メールが弾かれてしまったのです。

中継サーバの設定は返ることは出来なかったので、なんとかメールのエンコーディングを切り替えられないかMLに相談したところ、さぼてんさんがJenkins本体に対して改修を対応して下さいました…(_ _)

しばらくそれで幸せになったのですが、メッセージをもうちょっと拡張したいなということで、E-mail Ext Pluginを導入。

ただし、これはJenkins本体とは完全には連携していないので、別途改修が必要とのことでした。

HTMLメールでの通知なら、私の環境でもメールが弾かれることはないのですが、Jenkinsの出したメールをどこかのサーバが受け取って、aliasで処理して何か次の処理につなげる…ということを考えると、text/plainのJIS、Content-Transfer-Encoding 7bitじゃないとやっぱりやりにくい…。(スタッフもあんまりHTMLメールは好きじゃないということもあるのですが…)

こちらもMLに相談したところ、曽我部さんが対応してくださいました(_ _)

* * *

さて、実際使っているJenkinsのバージョンアップを待って、遅ればせながら、やっと曽我部さんの描いてくださった指示に従って、メールの設定を反映させることができました。

私はCentOSを使っていますので、こんな感じで設定しています。

  • Content-Typeに関しては、Jenkins本体のメール設定の部分で指定したものを引き継いでくれます。
  • Content-Transfer-Encoding については、Jenkinsの起動時のプロパティに設定となります。
  • CentOSのRPMでインストールした場合は、 /etc/sysconfig/jenkins に設定を加えます。(下記の通りにJENKINS_ARGSを指定)
    • JENKINS_ARGS=" -Dhudson.plugins.emailext.ExtendedEmailPublisher.Content-Transfer-Encoding=7bit"
    • 起動スクリプトの /etc/rc.d/init.d/jenkins で、この設定を拾ってくれます。

実は、HudsonがJenkinsになったので、このオプションはどう指定するのかな…と思ったのですが、JavaのソースもまだパッケージはHudsonになっているようで、パラメータの指定も Dhudson.plugins.emailext … のままのようでした。

いつか切り替わるのかな?

ともかく、いつもながら凄いなあ…と思いつつ、使うだけのユーザになってしまっていて、申し訳ない限りです。

2011-04-07

JenkinsのWebinarのメモ (2011/02/02)

ずいぶん書くのが遅くなってしまいましたが、川口さんご本人による、JenkinsのWebinarに参加しましたので、その時のメモです。

Webinarという企画自体、参加は初めて。『どういうスタイルなんだろう?』と思いつつ、ヘッドセット用意していなかったのですが、オンラインで受講できるそうなので、さっそく申し込みを行い、2月に入りました…。

週末からこどもがインフルエンザに罹ってしまい、出社はNGでもWebinar受講はできるだろうと思っておりました。

ところが、当日の朝になって、看病をしていた自分にもなんとなく悪寒が…。急いで病院に行くとドンピシャリ。(あの検査方法、なんとかなりませんかね…。涙目。)

以下、布団に入りながらのメモになります…。

 

20110202

 

なお、資料ではHudsonでしたが、Jenkinsに置き換えを行っています。

* * *

Jenkinsのバックアップについて

JENKINS_HOME が大事だけれど、特にどこをバックアップすべきか?

  • 川口さんがSunに在籍していた時は、JENKINS_HOMEが300数十GBにもなっていた。
  • 大事なのはConfig。buildの記録は、無くなってもいいだろう。
  • LinuxならLVMで、容量を拡張して行けばいい。
  • 高価なストレージがあるのなら、ハードウェア的にスナップショットを取るといったバックアップでも良いだろう。
  • Nectar という、 Jenkinsのコマーシャルディストリビューションを利用する方法もある。
    こちらはバックアップ機能も充実。

バックアップだけではだめ。リストアのテストしないとダメ!

ディスク(ストレージ)について

自分の知らない人が、同じJenkinsを使っている場合、ディスクのやりくりは困るね。ディスクについてのTipsを。

  • 後でディスクを足せる、というように作るのがポイント。
  • 高速なSCSIディスクみたいなのは要らない。安くて容量でっかいほうが良い
  • 追加したらスムーズに使えるようにする。
  • Windows: "Spanned Volume"
  • Linuxは一番面倒。LVMで設定しとかないといけない。

Jenkinsを動かす上では、Solarisカーネルは優れていると思っているよ。(by 川口さん)

インストーラーについて

  • Windowsのネイティブインストーラは鋭意作成中。(このBlog書いた時点でリリースされてます!)
  • JavaのWebアプリというと、サーバ管理者は嫌がるけれど、「RPMとかでパッケージ化されてるよ」、というとインストールに対する敷居が低くなるらしい。(これもTips?)

マスタ / スレーブについて

  • SlaveのリモートFSルートを、メモリ上に設定できないか考えて、やってみたらしい。
    でも、やっぱりディスクのほうが良いとのこと。
  • 公開鍵認証のやりかたを知らないシステム管理者が多かった…。

 

分散処理について

  • サーバやビルドマシンは今や家畜...(^^;
  • 可愛がっておかしくなったら懇切丁寧に面倒を見る、というのでは、時間が足りない。
  • 使えるものは使おう!

 

継続利用/普及のコツ

  • Jenkinsが動いているURLを、できるだけ覚えやすくする!
  • 開発者が見に来ないと意味が無い。
  • 後から変わるかもしれないものはURLに入れない(ポートとか)

* * *

Webinarのメモはここまでです。あと残り5分くらい、というところで、体力と気力が尽きてしまいました…orz

Webinarを通して質問したり、ということはできませんでしたが、日本語でお話しして下さったので、とても助かりました。

また、勉強会の中継とは違い、プレゼン資料の画面が固定・安定している分、理解に負担がかからずに助かりました。(スライドは英語表記だった気がしますが)

さらに、この日、画面のデモにはHudsonではなくJenkinsの文字が。

「ああ、本当にJenkinsになるんだ~!」と実感した瞬間でした。

川口さん、いまごろですが、どうもありがとうございました!

2011-04-06

机の上に。

どんなに心配しても、自分が涙を流しても、痛みと辛さを代わってあげることはできない。

『私たちは大丈夫だからね。それより、子どもたちのことも、自分の仕事もあるでしょ。頑張りなさい』と、逆に励ましの言葉をもらってしまった…。

実際、家庭を持ち、自分の子どもを守らないといけないわたしができることは、限られてしまう…。

やっぱり、自分の生活を、仕事を、続けていくしかないのかな。

いつでも支えてあげられるように、受け入れてあげられるように。

いろいろあったけど、出社して、仕事を再開。

my_desk

こういう経験があって、時間は今まで以上に大事になった。

気持ちを忘れないように、自分の机の前に、座右の銘としてカードを立てておいてます。