SQL Server のプロジェクト指向オフライン データベース開発を採用してみた話 実践編

2016年7月30日 第16回中国地方DB勉強会 のセッション資料

Transcript

1. SQL Server の プロジェクト指向 オフラインデータベース開発 を採用してみた話 第16回中国地方DB勉強会 きよくら ならみ 2016年7月30日
2. 自己紹介 ハンドル：きよくら ならみ @kiyokura 最近よく触ってるもの TypeScript / knockout.js Award Visual Studio and Development Technologies
3. コミュニティ Okayama IT Engineers Community 岡山を拠点に活動する IT技術者のコミュニティ 不定期で勉強会等のイベントを 単独・合同で開催 http://oitec.net/
4. 免責事項 本資料並びにセッションでの発言は私個人の調 査や情報集および考えに基づいて構成したもの です したがって所属企業やマイクロソフト等、私個 人以外の如何なるものの意見を代表するもので はありません 本セッションでの内容に起因して損害が生じた 場合においても、発表者はその責任を負うこと ができません
5. アジェンダ はじめに 困っていませんか？ オフライン型データベース開発 SQL Server Data Toolsとは やってみよう さらなる課題 5
6. はじめに 6
7. このセッションでの用語の定義 データベース開発（DB開発） 「テーブルとかビューとかストアドとかデータベース 上のオブジェクトを設計・開発する」こと 「データベース開発」というとなんだかRDBMSそのものを開発することに思え ますが、ほかに適切な用語がなかったのとMicrosoftの関連資料でこう訳してた のでそのまま使います 7
8. このセッションの目的 ✓ 「オフライン型DB開発」という 考え方について知っていただく ✓ SQL Server Data Toolsの機能を 知っていただく ✓ 実際にどのようにプロジェクトに 適用するかの例を見ていただく 8
9. このセッションのゴール ✓ オフライン型DB開発という考え 方があるんだ！ ✓ SQL Server Data Tools便利そう！ 使ってみたい！ ✓ SQL Server結構いいかも？ （おまけ） 9
10. 普段の開発どうですか？ 10
11. データべース開発で… 問題なく回っていますか？ ✓ ソースのバージョン管理 ✓ チーム開発 ✓ 複数のDB環境の管理 ✓ 単体テスト 11 ※データベース上のオブジェクト＝ テーブル/ビュー/ストアド/ユーザー定義関数/ユーザー定義型等々の諸々
12. まれによく見る()光景その１ バージョン管理？ バックアップがあるさ！ A：「なんかデグってるんだけど？」 B：「ごめん、バックアップから戻すわ」 C：「俺の作業が巻戻るんでやめてもらえます？」 A：「ぬっころす（#^ω^）ﾋﾟｷﾋﾟｷ」 12
13. まれによく見る()光景その2 共有サーバで同時に開発 A：「なんか急に動かなくなったんですけど！？」 B：「××ビューの定義変えちゃった！てへぺろ(・ω<」 A：「ぬっころす（#^ω^）ﾋﾟｷﾋﾟｷ」 13
14. まれによく見る()光景その3 『サーバA』が最新の“正”の環境 A：「サーバAとサーバB、スキーマ定義違うよ？」 B：「あ、先週直接直したからサーバBが最新やで」 A：「ぬっころす（#^ω^）ﾋﾟｷﾋﾟｷ」 14
15. まれによく見る()光景その4 単体テスト？書いてないけど 「おまえそれ@t_wada の前でも 同じこと言えんの？」 15 _,,;' '" '' ゛''" ゛' ';;,, （rヽ,;''"""''゛゛゛'';, ﾉｒ） ,;'゛ i ＿ 、＿ iヽ゛';, ,;'" ''| ヽ・〉 〈・ノ |ﾞ゛ `';, ,;'' "| ▼ |ﾞ゛ `';, ,;'' ヽ＿人＿ / ,;'_ ／ｼ、 ヽ⌒⌒ / ﾘ ＼ | "ｒ,, ｀"'''ﾞ´ ,,ﾐ゛ | | ﾘ、 ,ﾘ | | i ゛ｒ、ﾉ,,ｒ" i _| | ｀ー――----┴ ⌒´ ） （ヽ ＿＿＿＿＿＿ ,, ＿´） （_⌒ ＿＿＿＿＿＿ ,, ィ 丁 | | |
16. まれによく見る()光景 問題になることが多い？ ✓ ソースのバージョン管理 ✓ バックアップだけでだと困ることがある ✓ チーム開発 ✓ 共有サーバで開発作業のバッティング ✓ 複数のDB環境の管理 ✓ どの環境の定義が正しいの？ ✓ 単体テスト ✓ DBの単体テストをどうやっていくか 16
17. アプリケーションのフレームワークで解決？ 解決できるなら無問題！ 特にWebアプリケーションフレームワークでは ORMと組み合わせて大部分を解決できるものも しかし銀の弾丸ではない 特定のアプリに依存しないDBオブジェクト ほぼRDBMSのみで動いてる基幹システムとか 生存期間：DB＞アプリケーション 17
18. 本日お話しできたらいいなと思っている内容 これらの問題を対象とする手法とツール ✓ プロジェクト指向オフライン データベース開発 ✓ SQL Server Data Tools (SSDT) 18
19. Q：俺、SQL Server使ってないんだけど ✓ 手法や考え方を参考にしてもらえるところもあるか も？ ✓ もっといい方法をご存知の場合はぜひ教えていただ けると 余談： あと、SQL Serverも悪くないですよ、無料版のExpress というのもありますし、来年にはLinux版も出る予定で すよ 19
20. プロジェクト指向 オフライン データベース開発 20
21. プロジェクト指向オフライン データベース開発とは プロジェクト指向オフライン データベース開発 マイクロソフトが提案している手法 一般的な用語ではないかも？ 「接続指向のデータベース開発」と対比 比較的よくつかわれている開発手法を「接続型」と分類 その対比として「オフライン型」と呼んでいる（っぽい） マイクロソフトによる説明 Presentation on Visual Studio Database Projects Integration with Visual Studio Team Foundation Server http://blogs.msdn.com/b/ssdt/archive/2014/08/04/presentation-on- visual-studio-database-projects-integration-with-visual-studio- team-foundation-server.aspx 21 ※以下、上記のエントリと資料から一部抜粋
22. 接続型開発（パターン１） 22 開発端末 SSMS 実行 backup ※SSMD＝SQL Server Management Studio .bak Fileサーバ SQL DBサーバ データベースに接続し SSMSやコマンドラインのツールなどを用いて 都度サーバ上のオブジェクトを操作する開発
23. 接続型開発（パターン１） 23 開発端末 SSMS 実行 backup ※SSMD＝SQL Server Management Studio .bak Fileサーバ SQL DBサーバ ✓ 手軽 × 間違った時のリカバリがバックアップからのリストアのみ × バックアップがとられているタイミングにしか戻れない × 他人の開発・修正の影響をダイレクトに受ける × 他サーバへのデプロイの管理が困難 × 何がデプロイされてて何がデプロイされてない？
24. 接続型開発（パターン2） 24 開発端末 SSMS 実行 write Fileサーバ または ソース管理 SQL DBサーバ 差分のSQL文を事前に作成・管理 差分のSQL文を発行してサーバのオブジェクトを操作する開発 SQL スクリプト （ALTER） read
25. 接続型開発（パターン2） 25 開発端末 SSMS 実行 write Fileサーバ または ソース管理 SQL DBサーバ ✓ バックアップと併用することで任意のロールバックが可能 △ バックアップを戻した後、戻りたいところまで順番にSQLを実行する必要あり ✓ 変更の理由や追跡が可能 × ある特定の時点の”正しい姿”がはっきりと解らない × 容易なロールバックやデプロイすべき構成の特定が困難（履歴の積重ね） × 他人の開発・修正の影響をダイレクトに受ける × 他サーバへのデプロイの管理が困難 SQL スクリプト （ALTER） read
26. オフライン型開発 26 開発端末 VS 発行 commit ソース管理 SQL DBサーバ SQL文を編集して開発 ローカルのDBに発行してテスト テストが完了したらソース管理にコミット（CREATE文） ソース管理から他人の開発を取得 テストが完了したら共用のサーバに発行 SQL スクリプト (CREATE) sync SQL Deploy TEST
27. オフライン型開発 27 開発端末 VS 発行 commit ソース管理 SQL DBサーバ スクリプトの断片ではなくプロジェクトという単位で管理する DBのスキーマのように論理構造を意識してローカルでも管理 オブジェクトのスクリプトは差分ではなく定義そのものを管理 例えばALTER文ではなくCREATE文を管理 ローカルで完結した環境でテストを実行 プリリクエスト等も活用 SQL スクリプト (CREATE) sync SQL Deploy TEST
28. オフライン型開発 28 開発端末 VS 発行 commit ソース管理 SQL DBサーバ ✓ 特定のコミット時点のスキーマを容易に再現可能 ✓ 変更の理由や追跡が可能 ✓ 共有サーバへ危険な変更を事前に検出 ✓ プルリクエスト等の活用で他人の変更も容易にチェック ✓ デプロイ単位の管理 SQL スクリプト (CREATE) sync SQL Deploy TEST
29. SQL Server Data Tools 29
30. SQL Server Data Tools Visual Studio用のアドオン 無償で利用可能 VS2010以降で利用可能 VS2012までのものはメンテ終了、VS2013/VS2015を推奨 オフライン型DB開発を行うための機能 多岐にわたる機能を内蔵 が、別にオフライン型開発をしなくても便利に使えます 対象DBはSQL Server Azure SQL Databaseでも利用可能 30
31. SSDTの導入方法 推奨：Webサイトから直接ダウンロード Download Latest SQL Server Data Tool  https://msdn.microsoft.com/ja-jp/mt186501  言語とVSのバージョンを適切に選択してDL Visual Studioの「拡張機能と更新プログラム」からでも 入るのだが… 日本語版のVSの場合はいらないことがあったりして最近微妙 31
32. 代表的な機能 データベースプロジェクト SQL Server オブジェクトエクスプローラー テーブルデザイナー インテリセンス デバッグ実行 単体テスト スキーマ比較 データ比較 デプロイ 32
33. 関連する機能 SQL Server Express LocalDB（以降LocalDB） 開発者向けのSQL Server Express の実行モード サービスではなく必要時にプロセスとして起動 ホストプロセスの子プロセスとして実行される アプリケーション側はDBのファイル（mdf）を管理するだけでだ いたい大丈夫 33
34. データベースプロジェクト SSDTをインストール後に利用できるプロジェクト形式 DBの各種オブジェクトをSQLスクリプトの形で管理  VSのプロジェクト形式なのでそのままソース管理機能が利用可能 以降に紹介する機能を利用できる 34
35. SQL Server オブジェクトエクスプローラー SQL ServerおよびDBプロ ジェクトの操作と管理に特 化 SQL Serverに接続して操作 を行う機能 サーバーエクスプローラと同様の操作 SSDT独自の操作もある  スキーマ比較/データ比較等 LocalDBやAzure SQL Databaseにも接続 可能 DBプロジェクトの論理 ビューとして DBプロジェクト内のスクリプトファイ ルを DBオブジェクトとして論理的に展開・ 管理 35
36. テーブルデザイナー GUIでテーブルの定義を作成・変更可能 36
37. インテリセンス インテリジェントなコード補完 SSMS（SQL Server Management Studio）より賢い 37
38. デバッグ実行 ステップ実行などのデバッグが可能 SQL Server LocalDBにデプロイ→デバッグも簡単に可能 ファイアウォールや各種設定や特別な権限など不要なため、 SSMSでネットワーク上のサーバで行うより楽 38 デバッグ実行先DBは、ローカル or 自分専用のインスタンスにすることをお勧め （F5押したらデプロイしちゃうのでうっかりすると…）
39. 単体テスト 単体テスト関連の機能を内包 単体テストプロジェクト作成 単体テストクラスのスキャフォールド 単体テストデザイナ 39
40. スキーマ比較 二つのスキーマを比較し更新を行う データベース ←→ データベース DBプロジェクト ←→ データベース オブジェクトのデプロイや、 DBプロジェクトへの取り込みにも利用可能 40
41. 参考：スキーマ比較やインポート機能でオブジェクトを取り込むと… フォルダ構造をきれいに階層化してくれる 普通に追加した時もやっといてよ、という気がしないでもないけど、プロジェク ト→データベースの一方通行（真にオフライン型開発）をするときは自分好みの 階層構造にしたいかもしれないのでそれはそれでショウガナイとも思う 41 スキーマ比較 or インポートした時 直接作成した時
42. データ比較 DB間のテーブル同士のデータ差分を検出・更 新 42
43. その他 複数のデータベースにまたがる開発も可能 複数のDBプロジェクトをソリューションに追加 DBプロジェクト参照機能で、そこそこいい感じに 別DBへの参照をプレースホルダで記述できる デプロイ時に解決 43
44. やってみよう デモを通して機能をざっくりと紹介します 44
45. デモシナリオ 前提条件 • 共有のSQL Serverをメンバ全員で直接触って開発 • バージョン管理されてない • SQL Server 2016（2005以降で利用可能） • Visual Studio 2015 （2010以降で可能、最新を推奨） • 2016年7月現在、VS2013以降用のみ最新がリリースされている シナリオ 1. とりあえずデータベースプロジェクトの導入＆ バージョン管理 2. 単体テストをやってみる 3. そしてオフライン開発へ…… 45
46. とりあえずデータベースプロジェクトの導入＆ バージョン管理 このステップの目的 1. まずはソースコードのバージョン管理を行う 2. （そのため＆今後の基盤とするために）データベース 開発プロジェクトを導入する このステップでやること 1. データベース開発プロジェクトを新規作成 2. SQL Server上の既存スキーマをインポート 3. ソースコードのバージョン管理 追加ステップ 1. サーバ上での変更を取り込む 46
47. 単体テストの導入 このステップの目的 1. サーバベースの開発で単体テストのみ採用する このステップでやること • テスト実行のデータベースの作成 • テストプロジェクトと単体テストの作成 47
48. 重要なポイント ここまでは『勝手にやれる』 • チームや上司の同意がなくてもやれる • 既存のプロセスと喧嘩せずに導入できる 48
49. そしてオフライン型開発へ このステップの目的 1. オフライン型開発に切り替える このステップでやること 1. ローカルで開発 2. デバッグ実行＆単体テスト実行 3. デプロイ 49
50. それでもやっぱり 銀の弾丸ではない 50
51. 実際採用してみてどうだったか すごく便利で捗った ✓ ソース管理が楽 ✓ プルリクエスト駆動 ✓ ローカルでの手軽なデバッグ ✓ データベース間の同期や比較 開発用/テスト用/デモ用 ✓ テスト駆動”気味”のストアド開発 呼び出し側のアプリと非同期で開発が可能 51
52. 万事解決…というわけではない SQL Server LocalDBの制約 SQLで単体テストを書く辛み 微妙な挙動をすることがある 52
53. SQL Server LocalDBの制約 インスタンスレベルの照合順序が変更できない システムデータベースに依存した処理でエラー に 対策 システムデータベースに依存しないよう頑張る SQL Server Expressを利用する 53
54. SQLで単体テストを書く辛み SSDTの機能の「テスト条件」の限界 テスト条件が使い勝手がいいかという問題 テスト条件の自作もできるが…… T-SQLでのAssertが可能。だが… RAISERROR構文を利用することで可能 IFで判定してRAISERROR…… 正直可読性もメンテナンス性も良いとは言いがたい T-SQL自体の限界(?) そもそもSQLのストアドの利用目的や考え方の問題 テストしやすいようにストアドを書くことに限界がある 対策 ある程度の割り切りが必要（と思う） 54
55. 微妙な挙動 スキーマ比較で更新に成功したふりして失敗する 結果表示は「成功」となってるのに更新できてないこ とがある 更新先に変更がかかったりロックがかかっている場合など？ 更新に失敗するのは仕方がないが、失敗したらな失敗したように表示してほしい … 55
56. まずはハイブリッド型から始めてもいいかも 接続型で開発した上で、 DBプロジェクトの機能を使ってソース管理 これができるだけでも随分有効 ここから始めて徐々にオフライン開発にシフトするの もアリ 56
57. まとめ 57
58. プロジェクト指向のオフラインDB開発 DB開発における処問題を解決する形 SQL Server はSSDTで対応 58
59. SQL Server Data Tools Visual Studio 用の無償アドオン プロジェクト指向オフラインDB開発を実現す るための各種機能 強力な機能があるので使えるところだけでも使 うと幸せになれるかも？ 使えるところから使っていこう 59
60. 60 ご清聴ありがとうございました