Comments
Description
Transcript
SQL Server 2014 自習書シリーズ No.5
SQL Server 2014 自習書シリーズ No.5 Microsoft Azure SQL Database 入門 Published: 2010 年 1 月 12 日 SQL Server 2014 更新版: 2015 年 1 月 30 日 有限会社エスキューエル・クオリティ SQL Server 2014 自習書 No.5 Azure SQL Database 入門 この文章に含まれる情報は、公表の日付の時点での Microsoft Corporation の考え方を表しています。市場の変化に応える必要 があるため、Microsoft は記載されている内容を約束しているわけではありません。この文書の内容は印刷後も正しいとは保障で きません。この文章は情報の提供のみを目的としています。 Microsoft、SQL Server、Visual Studio、Windows、Windows XP、Windows Server、Windows Vista は Microsoft Corporation の米国およびその他の国における登録商標です。 その他、記載されている会社名および製品名は、各社の商標または登録商標です。 この文章内での引用(図版やロゴ、文章など)は、日本マイクロソフト株式会社からの許諾を受けています。 © Copyright 2015 Microsoft Corporation. All rights reserved. 2 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 目次 STEP 1. Azure SQL Database の概要 ................................................................................. 5 1.1 Microsoft Azure SQL Database(旧名 SQL Azure)とは................................................ 6 1.2 Azure SQL Database を操作するためのツール ............................................................13 1.3 Microsoft Azure の 1 ヶ月間の無料評価版 ..................................................................16 STEP 2. Azure SQL Database の基本操作 ...........................................................................18 2.1 Azure SQL Database 上に SQL サーバー/データベースの作成 ......................................19 2.2 Azure SQL Database ファイアウォールの設定 ............................................................33 2.3 SSDT(SQL Server Data Tools)の最新版のインストール ..............................................35 2.4 SSDT ツールからの接続 ..........................................................................................40 2.5 SQL Server 2014 Management Studio からの接続......................................................46 2.6 任意のデータベースへの接続 .....................................................................................49 2.7 テーブルの作成 ......................................................................................................50 2.8 データの追加(INSERT) .........................................................................................54 2.9 日付時刻データの確認(UTC) ..................................................................................58 2.10 データの更新と削除(UPDATE/DELETE) ..............................................................59 2.11 ビューの作成(CREATE VIEW) ............................................................................60 2.12 ストアド プロシージャの作成(CREATE PROCEDURE) .............................................61 2.13 インデックスの作成 .............................................................................................62 2.14 V12 からサポートされた SELECT INTO、SQL CLR ...................................................65 2.15 Azure SQL Database でサポートされるその他のオブジェクト .....................................70 2.16 Azure SQL Database でのロックの動作(読み取り一貫性) ........................................79 2.17 データ圧縮を利用する ..........................................................................................82 2.18 インメモリの列ストア インデックスを利用する .........................................................87 2.19 データ パーティションを利用する ...........................................................................90 2.20 行レベル セキュリティ(プレビュー版)を利用する ....................................................93 2.21 動的データ マスク(プレビュー版)を利用する .........................................................97 STEP 3. Azure SQL Database の その他の操作 ................................................................. 100 3.1 Azure SQL Database での特定時刻への復元 ............................................................. 101 3.2 Azure SQL Database での監査(Audit).................................................................. 103 3.3 Azure SQL Database の DTU(性能指標)、エディション変更 ...................................... 122 3.4 Azure SQL Database での性能監視方法 ................................................................... 129 3.5 アラート ルールの設定(しきい値を超えた場合にメール送信) ...................................... 137 3.6 DMV(動的管理ビュー)を利用した性能監視 .............................................................. 140 3.7 sqlcmd ユーティリティからの接続/操作 .................................................................. 147 3.8 SQL Server との主な相違点 ................................................................................... 149 STEP 4. 既存の SQL Server 環境の Azure SQL Database への移行 ...................................... 153 4.1 Azure SQL Database への移行方法の概要 ................................................................ 154 4.2 移行元となるデータベース(NorthwindJ)の作成 ....................................................... 156 3 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 4.3 SSIS ウィザードでのテーブル データの移行 .............................................................. 160 4.4 bcp コマンドでのテーブル データの移行 .................................................................. 168 4.5 DAC Framework(DAC Fx)によるデータベースの移行 ............................................... 172 4.6 スクリプト生成ウィザードによるデータベースの移行 ................................................... 200 4.7 Azure SQL Database の SQL サーバー間でのデータベースの移行 ................................. 214 4.8 SSDT の便利な機能(スキーマ比較、リファクタリング) ............................................. 219 STEP 5. Elastic Scale による シャーディング .................................................................... 223 5.1 Elastic Scale(プレビュー版)の概要 ....................................................................... 224 5.2 Elastic Scale Client Library の利用 ......................................................................... 226 4 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 STEP 1. Azure SQL Database の概要 この STEP では、Azure SQL Database の概要について説明します。 この STEP では、次のことを学習します。 Azure SQL Database の概要 Azure SQL Database を操作するためのツール Microsoft Azure の 1 ヶ月間の無料評価版 5 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 1.1 Microsoft Azure SQL Database(旧名 SQL Azure)とは 「Microsoft Azure SQL Database」(以降、Azure SQL Database と記述)は、マイクロソ フトのクラウド プラットフォームである「Microsoft Azure」のサービスの 1つとして提供され ている、クラウド ベースのリレーショナル データベース(RDB)です。クラウドの一般用語とし ては、「DBaaS」(Database as a Service)または「PaaS」(Platform as a Service)に分類 されるものです。 SQL Server のクラウド版(SQL Server とほぼ完全互換) Azure SQL Database は、SQL Server の持っているデータベース エンジン機能を、クラウド 上で利用できるようにしたサービス=「SQL Server のクラウド版」です。したがって、Azure SQL Database は、SQL Server を利用するのと同じように利用することができます。例えば、次の画 面は、SQL Server の開発者向けツールである SSDT(SQL Server Data Tools)を利用して、デ ータベース内に「テーブル」を作成しているときの様子です。 Azure SQL Database のサーバーに接続 テーブルの作成 をグラフィカルな操 作で行っている様子 Azure SQL Database 上に作成した データベースを グラフィカルに操作 SQL Server と同様 ビューやストアド プ ロシージャ、関数な ども作成できる 自動的に作成された スクリプトが表示される このように Azure SQL Database は、SQL Server を利用するのと同じように、各種のオブジェ クトをグラフィカルに作成/操作していくことができます。 また、Azure SQL Database は、次のように Transact-SQL(T-SQL)ステートメントを利用し ても、各種のオブジェクトを作成/操作していくことができます。 6 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 CREATE TABLE ステートメントで テーブルの作成 INSERT ステートメ ントでデータの追加 UPDATE ステートメ ントでデータの更新 CREATE VIEW ステートメントで ビューの作成 SELECT ステートメン トでデータの参照 Azure SQL Database 上のデータベース内に作成できるオブジェクト Azure SQL Database 上に作成したデータベース内には、次のオブジェクトを作成することがで きます(いずれも、SQL Server 上で作成するのと同じように作成していくことができます)。 テーブル/データ(データ圧縮も可能) ビュー/ストアド プロシージャ/ユーザー定義関数/シノニム/制約/トリガー インデックスの作成/インデックスの再構築・再構成(オンライン処理も可能) データベース ユーザー/オブジェクト権限/データベース ロール sp_executesql/CTE(共通テーブル式)/MERGE ステートメント サブクエリ(副問い合わせ)/ローカル一時テーブル/テーブル変数/カーソル トランザクション/例外処理(TRY..CATCH)/RAISERROR によるエラーの発生 Spatial データ型(経度、緯度などの地図データの格納) 執筆時点での最新版である Version 12 からは、データ パーティション/SQL CLR(CLR 統合)/インメモリ列ストア インデックス/SELECT INTO にも対応。行レベルでのセ キュリティ機能も提供 具体的な操作方法や SQL Server との細かい相違点などについては、この自習書内でステップ バ イ ステップ形式で、簡単な例を使って説明していくので、ぜひ試しながら読み進めてください。 7 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 Note: Azure SQL Database は日々進化している(進化のスピードが非常に速い) Azure SQL Database は、最初のバージョンが提供されたのは、約 5 年前の 2010 年 1 月で、当時は「SQL Azure」と呼ばれていました。最初のバージョンでは、非常に大きな制約が数多くあり、例えば、データベース サイ ズが最大 10GB までしか対応していない、データベースの照合順序を変更できない、性能監視用のビューがサポートさ れていないなど、SQL Server と比べると、できないことが本当にたくさんありました。 しかし、2010 年 4 月の Azure SQL Database のバージョン アップでは MARS(Multiple Active Result Set)機能 がサポートされたり、2010 年 6 月のバージョン アップでは Spatial データ型(geometry、geography)と HierarchyID データ型のサポート、2011 年 12 月のバージョン アップではデータベースの照合順序を変更できるよ うになったりするなど、非常に速いスピードで進化をしています(わずか最初の 2 年の間に、大幅なバージョン アッ プが 6 回も行われました)。 その後の 3 年間でも何度もバージョンアップが行われて、データ圧縮機能が利用できるようになったり、オンラインで のインデックスの再構築が利用できるようになったり、SQL Server 2012 から提供された組み込み関数のサポート、 性能を強化した Premium エディションの提供、可用性の向上(99.99%の SLA)、監査(Audit)機能の提供、指定 した時刻へのデータベース復元のサポート、災害対策向けのジオ レプリケーション、読み取り可能なセカンダリのサポ ートなど、日々進化しています。 執筆時点(2015 年 1 月)での最新版である Version 12(12 という数字は、SQL Server 2014 の内部バージョン に合わせたもの)では、データベース サイズは最大 500GB、Premium エディションのさらなる性能向上、並列ク エリやインメモリの列ストア インデックス、データ パーティション、SQL CLR のサポート、2GB を超えるトラン ザクションや SELECT INTO のサポート、性能監視用のビューやコマンドも多数サポートされるようになって、SQL Server との違いを探すのが難しいぐらいに進化を遂げています。また、SQL Server にはない機能(将来の SQL Server で搭載される予定の機能)である「行レベルでのセキュリティ」が Azure SQL Database でイチ早く実装さ れるなど(執筆時点ではプレビュー版の提供) 、Azure SQL Database のほうが進んでいる部分もあります。 また、Elastic Scale(プレビュー版)を利用すれば、データベースのシャーディング(複数のデータベースを 1つの データベースのように見せかけること)ができるようになるので、500GB を超えたデータも扱えるようになります (2つに分割すれば 1TB 分、3つに分割すれば 1.5TB 分のデータ量を格納できるようになります)。 Azure SQL Database を利用する利点 Azure SQL Database を利用する主な利点は、次のとおりです。 マイクロソフトによって自動で運用管理されている = 利用者はアプリケーション開発に集中できる 99.99% の SLA ジオ レプリケーションによる災害対策が可能(セカンダリは読み取りも可能) SQL Server とほぼ完全互換の Transact-SQL(T-SQL) SQL Server からの移行が容易(データベースを丸ごと移行可能) 利用した分だけの課金(時間単位の課金で、初期費用は不要。契約解除手数料もなし) 一番安い Basic エディションなら、1 ヶ月利用しても 500~600 円、1 日だけの利用な ら 16.2~19.4 円 アクセス数の多い日/多い時間帯などに合わせて、エディションを変更可能 キャンペーンなどでアクセス数が急激に増える日や、ピークタイムなどに合わせて、性能 の良いエディションへ変更したり、複数のデータベースを作成して、負荷分散を図ってい くことが可能 8 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 Azure SQL Database は、マイクロソフトによって運用管理されているので、バックアップや災害 対策などで、高度な専門知識が必要ありません。利用者は、アプリケーション開発に集中すること ができます。 Azure SQL Database でのバックアップは、自動的に取得されていて、戻したい時刻にいつでも戻 すことができます(Basic エディションなら 7 日以内、Standard エディションなら 14 日以内、 Premium エディションなら 35 日以内の任意の日時/時刻にデータベースを復元することができ ます)。次の画面は、データベースを復元しているときの様子です(簡単に復元できます) 。 [復元]を クリックする 現在の時刻が 表示される 復元可能な時刻が 表示される どの日時/時刻に復元 するのかを指定する また、災害対策のためのジオ レプリケーション(データセンターをまたがったデータベースの複 製)も、ポータル サイトの GUI 操作だけで(数クリックするだけで)、簡単に設定できます。 セカンダリを作成する データセンターの選択 Premium エディション ならセカンダリでの 読み取りも可能 セカンダリとして設定したい データセンターを選択 9 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 ジオ レプリケーションの設定が完了すると、次のようにグラフィカルに監視することができます。 プライマリの データセンターの場所 セカンダリとして選択した データセンターの場所 サーバーの 状態が表示される Azure SQL Database は、SLA も 99.99% なので、データベースの運用管理をまかせることが でき、ユーザーは、アプリケーション開発に集中できるようになります。 Azure SQL Database では、オンプレミス(ローカル環境)の SQL Server のデータベースを、 Azure SQL Database 上に移行することも簡単に行うことができます。移行のためのウィザード が提供されていて、次のように簡単に移行することができます(詳しくは Step 4 で説明します)。 オンプレミスの SQL Server 上の データベースを右クリック Azure SQL Database へ 移行するためのウィザードの起動 ウィザードで 簡単に移行ができる 10 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 Azure SQL Database の料金 Azure SQL Database の料金は、次の URL から確認することができます。 SQL Database 料金 http://azure.microsoft.com/ja-jp/pricing/details/sql-database/ このページでは、次のように料金の詳細が記載されています。 データセンターの場所 によって料金が変わるので データセンターを選択する Basic エディションの料金 東日本の場合は 1ヶ月利用しても 612円 Standard エディション の料金 11 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 このページをまとめると、次のようになります。 エディション Basic Standard Premium DTU 性能指標 DB サイズ 復元 ポイント 5 2GB 7日 料金 東日本の場合 備考 ¥0.78/時間 (~ ¥612/月) S0 10 ¥2.33/時間 (~ ¥1,734/月) S1 20 S2 50 S3 100 ¥11.64/時間 (~ ¥8,670/月) P1 100 ¥72.05/時間 (~ ¥53,550/月) P2 200 P3 800 250GB 500GB 14日 35日 ¥4.66/時間 (~ ¥3,468/月) ¥11.65/時間 (~ ¥8,670/月) ¥144.08/時間 (~ ¥107,202/月) ¥576.30/時間 (~ ¥428,808/月) Premium エディションでは、 インメモリの列ストア インデック スや、並列クエリ、高速ストレージ を利用可能。読み取り可能セカンダ リも作成可能 性能や、作成可能なデータベース サイズ、復元ポイント(どの時点まで復元できるのか) 、利用で きる機能などによって、料金が分かれていて、一番安い Basic エディションであれば、1 ヶ月利 用たとしても約 612 円、1 日だけの利用なら 19.7 円で済みます(データセンターに東日本を選 択した場合)。Basic エディションは価格が安い分だけ、性能や機能で大きな制限を受けますが、こ の自習書の内容を試す分には、何も問題なく行うことができます。 Standard エディションの S0 レベルの場合は、1 ヶ月利用しても約 1,734 円、1 日だけの利用 なら 55.9 円で済みます。また、課金は ”時間単位” で行われるので、S0 レベルを 1 時間だけ利 用するのであれば、2.33 円で済みます(表の中の /時間 は、1 時間あたりの料金という意味にな ります) 。 初期費用はかからず、契約解除手数料もかからないので、利用した時間の分だけ課金されるのが Azure SQL Database になります。 Azure を管理するためのポータル サイトを利用すれば、現在の課金状況も、次のように確認する ことができます(さらに細かい課金状況を Excel ファイルとしてダウンロードすることも可能)。 Azure SQL Database では、アクセス数の多い日や、多い時間帯などに合わせて、エディションを 変更することもできるので、キャンペーンなどでアクセス数が急激に増える日や、ピークタイムな どに合わせて、性能の良いエディションへ変更したり、複数のデータベースを作成して、負荷分散 を図っていったりすることもできます。 12 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 1.2 Azure SQL Database を操作するためのツール Azure SQL Database は、次のツールを利用して操作することができます。 Azure ポータル SSDT(SQL Server Data Tools) Management Studio のオブジェクト エクスプローラー(SQL Server 2008 R2 以降) Management Studio のクエリ エディター(SQL Server 2008 以降) sqlcmd ユーティリティ(SQL Server 2008 以降) bcp コマンド、Integration Services(SQL Server 2008 以降) Azure ポータルは、Microsoft Azure を管理するためのポータル サイトで、次のように Web ブ ラウザーから利用することができます(画面は、新しい Azure ポータルのプレビュー版) 。 データベース の利用状況 監査ログを グラフィカルに表示 13 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 Transact-SQL での操作なら SQL Server 2008 以降 Azure SQL Database は、Transact-SQL(T-SQL)ステートメントを利用して、各種のオブジ ェクトを操作することができるので、SSDT(SQL Server Data Tools)や Management Studio のクエリ エディター、sqlcmd ユーティリティなどの Transact-SQL の実行環境があれば、ほぼ すべての操作を行うことができます。例えば、次の画面は、SQL Server 2008 の Management Studio を利用して、Azure SQL Database 上のデータベースを操作しているときの様子です。 SQL Server 2008 の Management Studio でも クエリ エディターを利用して Azure SQL Database の操作が可能 テーブルの作成 INSERT UPDATE ビューの作成 SQL Server 2008 の Management Studio の場合は オブジェクト エクスプローラー での操作には非対応 SELECT Azure SQL Database のサーバーに接続 このように、古いバージョンの SQL Server でも、Transact-SQL ステートメントを実行できるツ ールがあれば、Azure SQL Database を操作することができます。 GUI で最新版の Azure SQL Database を操作するには最新版の SSDT Transact-SQL ステートメントではなく、グラフィカルな操作で Azure SQL Database を利用し たい場合には、最新版の SSDT(SQL Server Data Tools)や、SQL Server 2014 の CU5(累 積的な更新プログラム パッケージ 5)以降を適用した Management Studio を利用するのがお 勧めになります。 最新版の SSDT や SQL Server 2014 の CU5 以降であれば、Azure SQL Database の最新バ ージョンである V12(Version 12)に対応したデータベース操作や、移行などを行うことができ ます。 例えば、次の画面は、最新の SSDT ツールを利用して、オンプレミス環境(ローカル)の SQL Server 上のデータベースと、Azure SQL Database 上のデータベースのスキーマ比較を行ってい るときの様子です。 14 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 SSDT でスキーマ比較 をしているときの様子 異なる部分は 赤や黄色、斜線 で表示される また、次の画面は、SQL Server 2014 の Management Studio(CU5)を利用して、オンプレミ ス環境(ローカル)の SQL Server 上のデータベースを、Azure SQL Database 上のデータベー スとして移行(完全コピーの作成)を行っているときの様子です。 このように、より便利に Azure SQL Database を操作するには、最新版のツールを利用するのが お勧めになります(具体的なインストール方法や操作方法については、Step 2 以降で詳しく説明 します) 。 15 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 1.3 Microsoft Azure の 1 ヶ月間の無料評価版 Azure SQL Database を含めた Microsoft Azure のサービスは、1 ヶ月間無料で利用できる評価 版が提供されています。 1 か月間の無料評価版 http://azure.microsoft.com/ja-jp/pricing/free-trial/ MSDN サブスクリプションの特典など MSDN サブスクリプションのメンバーや MPN(Microsoft Partner Network)のメンバーの場合 は、メンバー向けの特別プログラムなどが用意されています。例えば、MSDN サブスクリプション の場合には、次の URL で特典の詳細を確認することができます。 サブスクリプションの特典の比較 http://www.visualstudio.com/products/msdn-subscriptions-vs 16 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 MSDN Platforms なら $100 分(約1万円分) 、Visual Studio Professional with MSDN なら $50 分(約 5,000 円分) 、Visual Studio Ultimate with MSDN なら $150 分(約 15,000 円分) を利用できるなどの特典があります。 筆者は、Visual Studio Ultimate with MSDN の $150 分の特典を利用して、この自習書を執筆し ましたが、1 ヶ月に 15,500 円分を利用することができ、(性能検証を除いた)自習書の操作手順 の画面ショットを取得する作業では、次のように 597 円分の利用料金で済みました(まだ、15,000 円近く利用できる金額が残りました) 。 597円分を 利用 Note: 以前のバージョンの Web/Business エディションは中止予定 以前の Azure SQL Database では、Web エディションと Business エディションがあり、データベースの容量に応 じた課金方式をとっていました。執筆時点では、引き続きこの 2 つのエディションを利用することもできますが、どち らも 2015 年 9 月には中止される予定になっています。 17 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 STEP 2. Azure SQL Database の基本操作 この STEP では、Azure SQL Database 上にデータベースを作成して、各種の データベース オブジェクトの作成/利用など、Azure SQL Database の基本的 な操作方法について説明します。 この STEP では、次のことを学習します。 SQL サーバーの作成/管理者アカウントの作成 データベースの作成、照合順序の設定 Azure SQL Database ファイアウォールの設定 SSDT(SQL Server Data Tools)からの接続 SQL Server Management Studio からの接続 テーブルの作成とデータの追加、更新、削除 ビュー/ストアドプロシージャ/インデックスの作成 その他のデータベース オブジェクトの作成 Azure SQL Database でのロックの動作 データ圧縮の利用 インメモリの列ストア インデックスの利用 データ パーティションの利用 行レベル セキュリティの利用 動的データ マスクの利用 18 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 2.1 Azure SQL Database 上に SQL サーバー/データベースの作成 Azure SQL Database では、データベースをホストするための「SQL サーバー」を作成して、そ の中にデータベースを作成していく形になります。 Microsoft Azure の データ センターの場所 SQL サーバー データベース テーブル データ データベース テーブル SQL サーバー ≒ SQL Server のような関係になり、SQL サーバーを作成するときには、どのデ ータセンターへ作成するのかなどを設定します。 Let's Try それでは、実際に SQL サーバーやデータベースを作成してみましょう。Microsoft Azure のポ ータル サイト(以降、Azure ポータルと記述)を利用すれば、SQL サーバーの作成は、データベ ースの作成時に一緒に行うことができるので、ここでは、これを試してみましょう。 1. まずは、Azure ポータル(2015 年 1 月時点ではプレビュー版)へアクセスするために、IE (Internet Explorer)で、次のように URL を入力します。 https://portal.azure.com/ 1 2 Azure ポータルでは、Microsoft アカウントのサインインが要求されるので、Microsoft ア 19 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 カウントのメール アドレスを入力して、 [続行]ボタンをクリックします。 続いて、次のように[サインイン]ページが表示されるので、Microsoft アカウントのパスワ ードを入力して、 [サインイン]ボタンをクリックします。 1 2 2. サインインが完了すると、次のように Azure ポータルの[ホーム]ページが表示されます。 20 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 Note: Web ブラウザーは IE 10 以上、Chrome 最新版などを利用 Azure ポータル(プレビュー版)は、次の Web ブラウザーでサポートされています。 ・Internet Explorer 10 以上 ・Safari 7 以上 ・Chrome 最新版 ・Firefox 最新版 SQL サーバーとデータベースの作成 1. 次に、Azure ポータルで、次のように画面左下の[新規]ボタンをクリックして、[SQL Database]をクリックします。 2 1 2. これにより、次のように新しくデータベースを作成するための[SQL Database]ブレードが 表示されるので、 [名前]に「AzureDB」など任意のデータベース名を入力します。 21 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 データベースを作成するための [SQL Database]ブレードが表示される 1 新しく作成する データベースの名前 を入力する SQL サーバーの作成 3. [SQL Database]ブレードでは、データベース名を入力した後に、次のように[サーバー] をクリックします。 [サーバー]ブレード が表示される 2 1 これにより、 [サーバー]ブレードが表示されるので、 [新しいサーバーの作成]をクリックし 22 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 て、新しくサーバー(Azure SQL Database の SQL サーバー)を作成します。 4. 次のように、 [新しいサーバー]ブレードが表示されたら、 [サーバー名]に任意のサーバー名 を入力します(画面は matusrv1 と入力) 。 [新しいサーバー] ブレードが表示される 新しく作成する サーバーの名前 を入力する 1 2 3 4 5 Note: 新しいポータルではサーバー名を設定できるようになった 以前の Azure ポータル(manage.windowsazure.com)では、サーバー名を入力することはできず、自動的に サーバー名が決定して(j14m8lnddk のように英数字が割り振られて)いましたが、新しい Azure ポータルで は、任意のサーバー名を入力できるようになりました。 [サーバー管理者ログイン]では、サーバーにログインするためのユーザー名を任意に設定し て(画面は matumoto と入力)、 [パスワード]にユーザーへ設定したい任意のパスワード、 [パスワードの確認]に再度同じパスワードを入力します。ここで設定したユーザー名とパス ワードは、サーバーに接続する際に必要になるので、覚えておいてください。 [場所]では、任意のサーバーの配置場所(データセンターの場所)を設定しますが、既定で は「米国中北部」 (North Central US)が設定されます。 [V12 サーバーの作成(最新の更新)]では、 「はい」を選択することで、最新の Version 12 に対応したサーバーを作成できるようになります(執筆時点では、V12 は、ヨーロッパや北 米では正式版、東日本などではプレビュー版という形になっていますが、2015 年 4 月までに はすべての地域で正式版になる予定です) 。 23 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 サーバーを配置するデータセンターの場所は、次のように「東日本」や「西日本」、 「東アジア」 などを選択することもできますが、データセンターの場所によって、Azure SQL Database の 料金が変わってくるので、各料金プランを確認するようにしておいてください。 データセンターの場所 を変更することも可能 1 例えば、 「東日本」を選択した場合は、Basic エディションは、1 ヶ月あたり約 612 円である ところを、 「米国中北部」の場合は約 509 円になる、などの違いがあります。料金の詳細につ いては、次のページが参考になります。 SQL Database 料金 http://azure.microsoft.com/ja-jp/pricing/details/sql-database/ [リージョン]を変更すると 場所に応じた料金を確認できる 1 24 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 [新しいサーバー]ブレードでは、すべての設定が完了したら、 [OK]ボタンをクリックしま す。 5. サーバーを設定した後は、次のように[ソースの選択]をクリックして、 [ソースの選択]ブレ ードを表示します。 [ソースの選択] ブレードが表示される 2 1 ここでは、 [空のデータベース]を選択することで、新しく空のデータベースを作成することが できます。 価格レベル(エディション)の選択 6. 次に、[価格レベル]をクリックして、どのエディションを利用するのかを選択します。 [価格レベルの変更] ブレードが表示される 2 1 3 [価格レベルの変更]ブレードが表示されたら、[B](Basic)をクリックして、[選択]ボタ ンをクリックすることで、一番安い価格の Basic エディションのデータベースを作成するこ 25 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 とができます(サーバーのデータセンターの場所で、既定の米国中北部を選択している場合は、 1 ヶ月あたり約 509 円、1 日あたり約 16.4 円、1 時間あたり 0.69 円になります)。 [価格レベル]は、既定では、 「S0」 (Standard エディションの S0 レベル)が選択されてい るので、この場合は、月あたり約 1,530 円、1 日だと約 50 円になります。 データベースを作成したことで、ひと月分の利用料金がかかるわけではなく、作成してから利 用した時間分(1時間以内の利用の場合は、1時間に切り上げ)の利用料金がかかる形になり ます(時間単位の課金です) 。例えば、 「7 時間 40 分」利用して、Basic エディションで米国 中北部にサーバーを作成している場合には、0.69 円*8 時間 で 5.52 円 の利用料金になり ます(データベースを削除するまでが課金対象になります)。 選択したエディションは、データベースの作成後に変更することもできるので、Basic エディ ションで作成しても、後から Standard エディションの S0 や、Premium エディションの P1 などへ変更することができます(変更方法については後述します) 。 なお、 [価格レベルの変更]ブレードでは、次のように[すべて表示]をクリックすると、すべ ての価格レベルを確認/選択できるようになります。 1 S0 は 1ヶ月利用すると 1,529.93円の料金 になることが分かる 26 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 データベースの照合順序の変更(Japanese_CI_AS へ変更) 7. 次に、[オプションの構成 照合順序]をクリックして、[オプションの構成]ブレードを表示 し、データベースの照合順序を変更します。 [オプションの構成] ブレードが表示される 2 照合順序を Japanese_CI_AS に変更する 1 3 [照合順序]に「Japanese_CI_AS」(日本語版の SQL Server での既定の照合順序)と入 力して、 [OK]ボタンをクリックします。既定では、 「SQL_Latin1_General_CP1_CI_AS」 という照合順序に設定され、この場合は char/varchar データ型(n 付きの nchar/ nvarchar ではない文字列データ型)に設定した列に対して、日本語の文字を追加することが できなくなるので注意してください(詳しくは後述します)。 リソース グループの作成 8. [SQL Database]ブレードへ戻ったら、次は[リソース グループ]をクリックして、 [リソ ース グループ]ブレードを表示します。 27 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 2 3 1 4 ここでは、 [新しいリソース グループの作成]をクリックして、新しくリソース グループを作 成します。[リソース グループの作成]ブレードが表示されたら、[名前]に任意のリソース グループ名(画面は ResourceGroup1)を入力して、 [OK]ボタンをクリックします。 このようなリソース グループの作成は、必須の作業ではないのですが、後述の[監査]機能を 利用する場合に、リソース グループを作成しておいたほうが管理がしやすくなるので、ここ では新しく作成しておきます。 9. 以上で、すべての設定が完了で、最後に[作成]ボタンをクリックします。 データベースの名前 作成するサーバーの 名前 空のデータベース となっていることを確認 価格の設定 (Basic が一番安い) 照合順序の変更 作成するリソース グループ の名前 1 28 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 これで、データベースやサーバー、リソース グループの作成が行われます。データベースの作 成状況は、次のように[通知]ブレードで確認することができます。 1 データベースの作成中 はこのように表示される 作成されたデータベースの確認 10. データベースの作成が完了すると、次のように該当データベース(AzureDB)の構成ブレー ドが表示されます。 [データベースの構成] ブレードが表示される もし、このブレードが自動的に表示されない場合は、次のように[参照]をクリックして、 [SQL データベース]をクリックします。 29 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 1 2 これで、次のように[SQL データベース]ブレードが表示されて、作成したデータベースが一 覧されるので、該当データベース(AzureDB)をクリックすれば、同じブレードを表示でき るようになります。 1 [データベースの構成] ブレードが表示される 11. [データベースの構成]ブレードでは、次のように[プロパティ]をクリックすると、 [プロパ ティ]ブレードが表示されて、データベースの設定情報(照合順序やサーバー名、管理者ログ イン名など)を確認することができます。 30 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 サーバー名 1 照合順序 サーバー名の 完全修飾名 管理者ログイン名 12. [データベースの構成]ブレードでは、次のように下にスクロールして、 [価格層]をクリック すると、 [価格レベルの変更]ブレードが表示されて、データベースのエディションを変更する こともできます。 [価格レベルの変更] ブレードが表示される 1 下にスクロール する 2 31 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 このように作成されたデータベースは、課金対象になるので、この自習書の手順が終わった後 には、データベースを削除するようにしておいてください。データベースの削除は、次のよう に[削除]ボタンをクリックして行うことができます。 1 2 32 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 2.2 Azure SQL Database ファイアウォールの設定 Azure SQL Database は、既定では、ファイアウォールによって、すべての IP アドレスに対し て、アクセスを拒否するように設定されています(IP アドレス レベルでのアクセス制限が施され ています)。したがって、データベースを作成した後は、ファイアウォールを設定して、利用してい る IP アドレスに対して、アクセス許可を設定しておく必要があります。 1. ファイアウォールを設定するには、次のように[データベースの構成]ブレードでサーバー名 をクリックして(画面は matusrv1 をクリック)、 [サーバーの構成]ブレードを表示します。 [サーバーの構成]ブレード が表示される 1 2. [サーバーの構成]ブレードでは、次のように[設定]をクリックして、 [ファイアウォール] をクリックします。 [設定]ブレード が表示される 2 1 33 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 3. 次のように、 [ファイアウォール設定]ブレードが表示されたら、 [ルール名]に任意のルール 名(rule1 など)を入力して、 [開始 IP]と[終了 IP]に、アクセスを許可したい IP アド レスを入力して、 [保存]ボタンをクリックします。 [ファイアウォール設定] ブレードが表示される 2 Microsoft Azure 内の 別のサービスからアクセス させるかどうかの設定 1 アクセスを許可したい IP アドレスを入力 ここで設定した IP アドレスを利用しているマシンだけが、Azure SQL Database の SQL サ ーバーに接続できるようになります(Azure SQL Database では、IP アドレス レベルでの アクセス制限が施されています)。 また、 [Azure サービスへのアクセスを許可する]を[ON]に設定した場合は、Microsoft Azure 上の別サービス(Microsoft Azure 上に配置した Web アプリケーションなど)から Azure SQL Database へアクセスできるようになります。 34 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 2.3 SSDT(SQL Server Data Tools)の最新版のインストール SSDT(SQL Server Data Tools)の最新版を利用すると、Azure SQL Database の最新版であ る V12(Version 12)でも、グラフィカルにオブジェクトを操作していけるようになるので、こ こでは SSDT の最新版をインストールする方法を説明します。SSDT の最新版は、次の URL (SSDT チームの Blog)から取得することができます。 SQL Server Data Tools Team Blog http://blogs.msdn.com/b/ssdt/ V12 に対応した SSDT の最新版 執筆時点では英語版のみ V12 には未対応の SSDT 2014年 11月 Update 版 ここでは、次の 2 種類の SSDT が提供されています。 V12 に対応した最新版の SSDT 2014 年 11 月に提供された SSDT の Update 版(November Update) このうち、執筆時点(2015 年 1 月)では、V12 に対応した最新版の SSDT が英語版しか提供さ れておらず、英語版の Visual Studio 2013 環境にしかインストールすることができないので(後 日、日本語版も提供される予定です) 、2014 年 11 月に提供された SSDT(November Update) を利用する方法を説明します(これは日本語版の Visual Studio 2013 にインストールすることが できます) 。 SSDT をインストールするには、Visual Studio 2013 が必要になるのですが、Visual Studio 2013 をインストールしていない環境の場合でも、後述の「SSDT-BI」ツールを先にインストールしてお けば、SSDT をインストールすることができます。 Note: Visual Studio 2012/2013 に付属の SSDT はバージョンが古い SSDT は、Visual Studio 2012/2013 の Professional エディション以上であれば、次のようにインストール時の [インストールするオプション機能]ページからインストールすることができますが、この SSDT は SQL Server 2012 対応のもので、古いバージョンであることに注意する必要があります(SQL Server 2014 や最新版の Azure SQL Database へ対応したものは、上記の URL からインストールする必要があります)。 35 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 SSDT と SSDT-BI SSDT には、BI プロジェクトを作成するための「SSDT-BI」 (SQL Server Data Tools - Business Intelligence) ツー ル も あり、 これ は SQL Server 2008 R2 以前の バー ジョン で は BIDS (Business Intelligence Development Studio)と呼ばれていたツールに相当するものです。 Reporting Services や Analysis Services、Integration Services など、BI 関連のプロジェクト を作成するためのツールになります。 Visual Studio 2013 をインストール済みの環境がある場合には、この SSDT-BI をインストール する必要はありませんが、Visual Studio 2013 をインストールしていない環境の場合には、先に SSDT-BI をインストールしておくようにします。 SSDT-BI の最新版である「SSDT-BI for Visual Studio 2013」は、次の URL からダウンロー ドして、インストールすることができます。 SSDT-BI for Visual Studio 2013 のダウンロード URL http://www.microsoft.com/ja-JP/download/details.aspx?id=42313 36 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 SSDT-BI for Visual Studio 2013 のインストール要件 SSDT-BI for Visual Studio 2013 は、次の OS にインストールすることができます。 Windows 7/8/8.1 Windows Server 2008 R2/2012/2012 R2 サ ー バ ー OS の 場 合 は 、 Windows Server 2008 に は イ ン ス ト ー ル す る こ と が で き ず 、 Windows Server 2008 R2 以降が必要になります。 また、SSDT-BI for Visual Studio 2013 のインストールに必要になるソフトウェアは、次のとお りです。 .NET Framework 4.5.1 .NET Framework 4.5.1 は、Windows 8.1 と Windows Server 2012 R2 の場合は、OS のインストール時に自動的にインストールされているので、別途インストールしておく必要はあり ません。Windows 7/8 や Windows Server 2008 R2/2012 を利用する場合には、次の URL から .NET Framework 4.5.1 をダウンロードして、インストールしておく必要がありま す。 .NET Framework 4.5.1 のダウンロード URL http://support.microsoft.com/kb/2858728/ja .NET Framework 4.5.1 のダウンロード 37 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 SSDT-BI for Visual Studio 2013 のインストール SSDT-BI for Visual Studio 2013 のインストールは、次のように行うことができます(この作業 は、Visual Studio 2013 をインストール済みの環境がある場合は不要です) 。 38 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 SSDT のインストール(2014 年 11 月の November Update の場合) SSDT-BI をインストールした後は、SSDT のインストールを行います。2014 年 11 月版の SSDT は、次の URL からダウンロードして、インストールすることができます。 http://msdn.microsoft.com/en-us/dn864412 Japanese を選択 以上で、SSDT のインストールが完了です。 39 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 2.4 SSDT ツールからの接続 次に、SSDT(SQL Server Data Tools)を利用して、Azure SQL Database を操作してみまし ょう。この Step で説明する手順は、SQL Server の Management Studio を利用しても全く 同じように操作できるので、SSDT 環境がない場合には、Management Studio を利用して試して みてください。 1. まずは、 [スタート]画面の[Microsoft SQL Server 2014]グループから[SQL Server Data Tools for Visual Studio 2013]をクリックして、SSDT を起動します(Visual Studio 2013 をインストールしている環境の場合は、Visual Studio 2013 をクリックしても SSDT を起動することができます) 。 1 2. SSDT は、Visual Studio 2013 に統合された機能なので、Visual Studio 2013 が起動しま す。Visual Studio 2013 が起動したら、次のように[表示]メニューから[SQL Server オ ブジェクト エクスプローラー]をクリックします。 1 40 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 3. SQL Server オブジェクト エクスプローラーが表示されたら、 [SQL Server]を右クリック して、[SQL Server の追加]をクリックします。 1 SQL Server オブジェクト エクスプローラー 4. [サーバーへの接続]ダイアログが表示されたら、[サーバー名]に「サーバー名.database .windows.net」形式(完全修飾名)で、Azure SQL Database のサーバー名を入力します (サーバー名は、データベースを作成するときに、作成したサーバーの名前です) 。 3 1 2 4 5 [認証]では、 「SQL Server 認証」を選択して、 [ログイン]に Azure SQL Database の管 理者アカウントの名前(画面は matumoto)、 [パスワード]に管理者アカウントに設定した パスワードを入力して、[オプション]ボタンをクリックします。 [接続プロパティ]タブが表示されたら、 「暗号化接続」をチェックすることで、SSL(Secure Sockets Layer)を利用したネットワーク データの暗号化機能を有効化することができます。 暗号化接続は必須ではありませんが、有効化しない場合には、Azure SQL Database への接 続(ネットワーク上を流れるパケット)が暗号化されなくなってしまうので、アカウントのパ スワードや、やりとりするデータを暗号化するためにも、暗号化接続を有効化しておくことを お勧めします。 なお、Azure SQL Database のサーバー名(完全修飾名)や、管理者アカウントの名前は、次 41 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 のように Azure ポータルの[データベースの構成]ブレードで、 [プロパティ]をクリックす ることで、確認することができます。 [データベースの構成] ブレード 1 2 3 5. サーバーへの接続が完了すると、次のように SQL Server オブジェクト エクスプローラー には、サーバー名(画面は matusrv1.database.windows.net)が表示されて、作成した AzureDB データベースと、システム データベースの master を参照することができます。 Azure SQL Database の サーバー名とバージョン番号 が表示される 作成した AzureDB と master データベース を参照できる 表示されるバージョン番号「12.~」は、執筆時点(2015 年 1 月)での最新のバージョン番 号で、今後、Azure SQL Database がバージョン アップされた場合には、番号が変更される ことになります。この「12」という数字は、SQL Server 2014 の内部バージョン番号と同じ 42 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 で、Azure SQL Database のデータベース エンジンが SQL Server 2014 相当であること を示しています。 Note: Azure SQL Database ファイアウォールでブロックされた場合のエラー Azure SQL Database ファイアウォールで IP アドレスを許可していない場合には、次のようにエラーが表示さ れます。 ブロックされた IP アド レスが表示される エラー メッセージには、ブロックされた(アクセス拒否された)IP アドレスが表示されているので、この IP アドレスを、ファイアウォールで追加しておくようにしてください。 6. 次に、SQL Server オブジェクト エクスプローラーで、[システム データベース]を展開し て、master データベースを右クリックし、[新しいクエリ]をクリックします。 3 「master」へ 接続している 2 クエリ エディター が起動する 1 master データベースを 右クリックして [新しいクエリ]をクリック これにより、クエリ エディターが起動して、master データベースに接続していることを確 認できます。 7. 続いて、クエリ エディターで次のように入力して、Azure SQL Database の SQL サーバー 43 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 のバージョン番号を確認します。 SELECT @@VERSION Azure SQL Database の バージョン番号が表示される ことを確認 結果に表示される「SQL Azure」という名前は、Azure SQL Database の旧名称です。この ように、バージョン番号は「@@VERSION」を利用しても確認することができます。 Note: CREATE DATABASE ステートメントでデータベースの作成 Azure SQL Database のデータベースは、CREATE DATABASE ステートメントを利用して、作成することも できます。これは次のように記述します。 CREATE DATABASE AzureDB2 COLLATE Japanese_CI_AS ( EDITION = 'BASIC' ) COLLATE で照合順序を指定して、EDITION で BASIC と指定することで Basic エディションのデータベー スを作成することができます。EDITION で STANDARD と指定した場合は、Standard エディションの S0 レベルのデータベースを作成することができます。 データベースに設定された照合順序は、databases カタログ ビューを利用して、次のように結果を右側へスク ロールすることでも確認できます。 44 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 Note: データベースの削除(DROP DATABASE) データベースの削除を行いたい場合には、通常の SQL Server と同様、DROP DATABASE ステートメントで 行うこともできます。 DROP DATABASE AzureDB2 なお、前述したように、データベースの削除は、次のように Azure ポータルの[データベースの構成]ブレード で、[削除]ボタンをクリックすることでも行うことができます。 1 2 45 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 2.5 SQL Server 2014 Management Studio からの接続 Azure SQL Database は、SQL Server の Management Studio を利用しても、SSDT と同 じように操作することができます。 それでは、これも試してみましょう。ここでは、SQL Server 2014 を例こ説明しますが、SQL Server 2008 R2 以降の Management Studio であれば、同じように操作することができます。 1. まずは、Management Studio を起動するために、[スタート]画面で[Microsoft SQL Server 2014]グループの[SQL Server 2014 Management Studio]をクリックしま す。 1 2. [サーバーへの接続]ダイアログが表示されたら、 [サーバー名]に Azure SQL Database の サーバー名(~.database.windows.net)、 [認証]で「SQL Server 認証」を選択し、 [ロ グイン]に管理者アカウントの名前、 [パスワード]に管理者アカウントのパスワードを入力し て、 [オプション]ボタンをクリックします。 1 2 46 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 次のように、 [接続プロパティ]タブが表示されたら、 [暗号化接続]をチェックして、 [接続] ボタンをクリックします。 1 2 3 3. 接続が完了すると、次のようにオブジェクト エクスプローラーが表示されて、Azure SQL Database を操作できるようになります。 Azure SQL Database のサーバー名と バージョン番号が表示される。 Version 12 は 13.0 と誤表示される 作成した AzureDB データベースを 参照できる なお、オブジェクト エクスプローラーに表示されるバージョン番号「13.~」は、誤表示にな っていて、SQL Server 2014 の CU6(執筆時点では未提供)以降を適用することで、正しい バージョン番号が表示されるようになります。なお、英語版の場合は、CU5 であれば、正し く 12 と表示されます。 4. Management Studio で、クエリ(Transact-SQL ステートメント)を実行するには、次のよ うに該当データベースを右クリックして、 [新しいクエリ]をクリックします。 47 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 2 クエリ エディター が起動する 1 該当データベースを 右クリックして [新しいクエリ]をクリック Note: SQL Server 2008 の場合のエラー SQL Server 2008 の Management Studio を利用している場合には、オブジェクト エクスプローラーが Azure SQL Database には対応していないので、次のようにエラーが表示されてしまいます。 なお、SQL Server 2008 でも、クエリ エディターや、後述の sqlcmd ユーティリティを利用すれば、Azure SQL Database に接続することができるので、それらを利用して Azure SQL Database の操作を行うことがで きます。クエリ エディターは、Management Studio のツールバーの[新しいクエリ]または[データベース エンジン クエリ]ボタンをクリックすることで、起動することができます。 48 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 2.6 任意のデータベースへの接続 ここからの手順は、SSDT(SQL Server Data Tools)ツールを利用して説明します。Management Studio を利用しても、全く同じように操作できるので、SSDT 環境がない場合には、Management Studio を利用して試してみてください。 作成したデータベースへの接続 まずは、SSDT を利用して、作成した AzureDB データベースに接続してみましょう。 1. 最初に、次のように USE ステートメントを利用してみます。 USE AzureDB 結果はエラーになり、接続することができません。Azure SQL Database では、USE ステー トメントがサポートされていないので、このようにエラーとなってしまいます。 2. Azure SQL Database で任意のデータベースに接続したい場合には、次のように SQL Server オブジェクト エクスプローラーで、接続したいデータベース(ここでは AzureDB) を右クリックして、[新しいクエリ]ボタンをクリックします。 3 「AzureDB」に接続 していることを確認 できる 2 クエリ エディター が起動する 1 AzureDB データベースを 右クリックして [新しいクエリ]をクリック これにより、新しいクエリ エディターが表示されて、AzureDB データベースに接続できた ことを確認することができます。 49 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 2.7 テーブルの作成 次に、データベース内にテーブルを作成してみましょう。テーブルの作成は、SSDT(SQL Server Data Tools)を利用すれば、グラフィカルに作成することができます。 1. SSDT でテーブルを作成するには、次のように[テーブル]フォルダーを右クリックして、 [新 しいテーブルの追加]をクリックします。 2 テーブル デザイナー が起動する 1 [テーブル]フォルダーを 右クリックして [新しいテーブルの追加] をクリック これにより、 [テーブル デザイナー]が表示されて、グラフィカルにテーブルを作成していく ことができます。 2. テーブル デザイナーでは、次のようにテーブルを定義します。 列名 データ型 NULL を許容 a int 許可しない b int 許可する c varchar(200) 許可する d datetime 許可する その他 IDENTITY の指定を True へ変更 主キーを True へ設定(既定値) 1 2 3 GUI で操作すると 自動的にスクリプト (T-SQL)が生成される 50 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 「a」 、「b」 、「c」、 「d」の 4 つの列を作成して、 「a」列は右下の[プロパティ]ウィンドウで [IDENTITY の指定]を「True」に変更して、1 から始まる連番(1、2、3、…)が格納さ れるようにします。 テーブル名の指定とテーブルの作成 3. テ ー ブ ル デ ザ イ ナ ー で グ ラ フ ィ カ ル に 操 作 し た も の は 、 次 の よ う に 自 動 的 に T-SQL (CREATE TABLE ステートメント)が生成されています。 1 GUI で操作すると 自動的にスクリプト (T-SQL)が生成される テーブル名を変更するには、CREATE TABLE の隣に表示されるテーブル名(既定値は [Table])を変更します。ここでは、画面のように [t1] に変更して、 「t1」という名前のテー ブルを作成するようにします。 4. テーブル名を変更した後は、テーブルを作成するために、テーブル デザイナー上部の[更新] ボタンをクリックします。 1 2 [データベース更新のプレビュー]ダイアログが表示されたら、 [データベースの更新]をクリ 51 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 ックします。 5. これにより、次のように[データツール操作]ウィンドウが表示されて、テーブルの作成(内 部的には CREATE TABLE ステートメントの実行)が行われます。 1 [データツール操作] ウィンドウが表示される 2 [更新が正常に完了しました] と表示されれば、 テーブルの作成が完了 [更新が正常に完了しました]と表示されれば、テーブルの作成が完了です。 6. テーブルの作成が完了した後は、次のように、SQL Server オブジェクト エクスプローラー で、作成したテーブルが表示されるようになります(表示されない場合は、 [テーブル]フォル ダーを右クリックして、 [最新の情報に更新]をクリックしてみてください) 。 1 52 作成されたテーブル が表示される SQL Server 2014 自習書 No.5 Azure SQL Database 入門 Note: CREATE TABLE ステートメントでテーブルを作成 テーブルは、通常の SQL Server と同様、CREATE TABLE ステートメントを利用して作成することもできます。 CREATE TABLE t1_2 ( a int IDENTITY(1, 1) PRIMARY KEY ,b int ,c varchar(200) ,d datetime ) 2 1 クエリ エディターで CREATE TABLE ステートメント を記述して、実行する Note: sp_help でテーブル構造の確認 作成したテーブルの構造は、通常の SQL Server と同様、次のように sp_help システム ストアド プロシージャを 利用して確認することもできます。 sp_help 't1' 53 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 2.8 データの追加(INSERT) 次に、作成した t1 テーブルに対して、データを追加してみましょう。データの追加についても、 通常の SQL Server と同様、INSERT ステートメントを利用して行うことができます。 1. まずは、SSDT(SQL Server Data Tools)で、クエリ エディターを開くために、SQL Server オブジェクト エクスプローラーで「AzureDB」データベースを右クリックして、[新しいク エリ]をクリックします。 3 「AzureDB」に接続 していることを確認 できる 2 クエリ エディター が起動する 1 2. AzureDB データベースを 右クリックして [新しいクエリ]をクリック クエリ エディターが表示されたら、次のように INSERT ステートメントを記述します。 INSERT INTO t1 VALUES(111, 'AAA', '2015/01/07') 2 1 3 クエリ エディターで INSERT ステートメントを記述 「1件処理されました」 と表示されれば成功 ステートメントを記述したら、クエリ エディターのツールバーの[実行]ボタンをクリックし て、ステートメントを実行します。実行後、 [メッセージ]タブに「1 件処理されました」と表 示されれば、データの追加が成功です。 54 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 Note:V12 以降はクラスター化インデックスが必須ではない Azure SQL Database の Version 11 までは、テーブルにデータを追加するためには、クラスター化インデックスを 作成していることが必須条件だったのですが、Version 12 からは必須ではなくなりました。 Version 11 以前を利用している場合には、次のようにクラスター化インデックスを作成していないテーブルの場合に は、データの INSERT 時にエラーが発生していました。 これに対して、Version 12 からは、クラスター化インデックスが必須ではなくなったので、次のようにクラスター化 インデックスを作成していなくても、データの INSERT をすることができます。 なお、SSDT の最新版を利用していない場合には、テーブル デザイナーからテーブルを作成する際に、クラスター化 インデックスがない場合には(例えば、PRIMARY KEY を外しているなどをしている場合には)、次のようにエラーが 発生してしまいます。 これを回避するには、(執筆時点では日本語版はないのですが)Version 12 に対応した最新版の SSDT を利用する か、クエリ エディターで CREATE TABLE ステートメントを利用して、テーブルを作成するようにします。 55 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 日本語文字の追加 データベースの照合順序を「Japanese_CI_AS」など、日本語に対応したものへ設定している場 合には、日本語の文字だったとしても、何の問題なくデータを追加することができます。 それでは、これも試してみましょう。 1. 次のように入力して、 「t1」テーブルの「c」列(varchar(200)で定義した列)に日本語デー タを追加してみます。 INSERT INTO t1 VALUES(222, 'テスト1', '2015/01/07') INSERT INTO t1 VALUES(333, '漢字テスト', '2015/01/07') SELECT * FROM t1 日本語の文字も正しく 格納できている 日本語の文字でも、問題なく格納できていることを確認できると思います。 Note: 照合順序を指定せずに作成したデータベースの場合の注意点 照合順序を指定せずに、Azure SQL Database の SQL サーバー上にデータベースを作成した場合には、照合順 序が「SQL_Latin1_General_CP1_CI_AS」に設定されています。これは、次のような状況です。 照合順序を指定しないで データベースを作成した場合は 照合順序が SQL_Latin_~ になる このように、 「SQL_Latin1_General_CP1_CI_AS」照合順序が設定されたデータベースでは、char や varchar のデータ型に設定した列に、日本語(ダブル バイト)文字を追加することができません。これは、次 のように確認することができます。 56 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 varchar の場合 varchar 列には 日本語文字を 正しく格納できない この状況を回避するには(日本語データを正しく格納したい場合には)、テーブルの作成時に COLLATE 句を利 用して「列の照合順序」を Japanese_CI_AS など、日本語対応のものへ設定するか、nchar や nvarchar などの Unicode 対応のデータ型を利用するようにします。さらに、日本語のデータを扱う際には、N プレフィ ックスを付ける必要もあります。もし、N プレフィックスを付けなかった場合には、データベースの既定のコー ド ページで処理されてしまうので、この場合も正しくデータを格納することができません。 したがって、 「SQL_Latin1_General_CP1_CI_AS」照合順序が設定されたデータベース環境では、日本語デ ータを正しく格納するには、次のように操作するようにします(nchar/nvarchar データ型を利用して、N プレ フィックスを付けるようにします)。 nvarchar を利用 N プレフィックス を付ける 日本語文字を 正しく格納できる なお、照合順序やデータ型については、SQL Server 2012 の自習書シリーズの「Transact-SQL 入門」編でも 詳しく説明しているので、こちらもぜひご覧いただければと思います。 57 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 2.9 日付時刻データの確認(UTC) Azure SQL Database では、日付時刻データは UTC(協定世界時:Coordinated Universal Time) として扱われます。これも試してみましょう。 1. 次のように GETDATE 関数などを利用して、 「t1」テーブルの「d」列に、現在の日付時刻を 格納してみます。 INSERT INTO t1 VALUES(444, 'BBB', GETDATE()) INSERT INTO t1 VALUES(555, 'BBB', SYSDATETIME()) INSERT INTO t1 VALUES(666, 'BBB', SYSUTCDATETIME()) SYSUTCDATETIME で取得 した UTC(協定世界時)と 同じ時刻になっている ローカルの時刻を取得できる GETDATE と SYSDATETIME 関数の結果が、UTC(協定世界 時)を取得できる SYSUTCDATETIME 関数の結果と同じになっていることを確認すること ができます。このように、Azure SQL Database では、ローカル(Azure SQL Database の SQL サーバー上)の時刻が UTC として扱われています。 日本標準時(UTC +9)の取得 1. 日本標準時は、UTC に 9 時間を加算したものなので、次のように DATEADD 関数を利用し て取得することができます。 INSERT INTO t1 VALUES(777, 'BBB', DATEADD(hour, 9, SYSUTCDATETIME()) ) UTC(協定世界時) に+9 時間した時刻 58 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 2.10 データの更新と削除(UPDATE/DELETE) 次に UPDATE/DELETE ステートメントを利用して、データの更新と削除を行ってみましょう。 これも、通常の SQL Server と同様の操作で行うことができます。 1. まずは、UPDATE ステートメントを利用して、 「t1」テーブルの「a」列が「4」のデータに対 して、「b」列の値を「999」へ更新してみます。 UPDATE t1 SET b = 999 WHERE a = 4 2. 次に、DELETE ステートメントを利用して、「a」列が「4」のデータを削除してみます。 DELETE FROM t1 WHERE a = 4 このように、データの更新と削除についても、通常の SQL Server と同様の操作で行うこと ができます。 59 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 2.11 ビューの作成(CREATE VIEW) ビューについても、通常の SQL Server と同様、CREATE VIEW ステートメントを利用して作成 することができます。これも試してみましょう。 1. 次のように CREATE VIEW ステートメントを記述して、 「t1」テーブルの「a」列が「3」以 下のデータを取得するビューを作成してみます。 CREATE VIEW view1 AS SELECT * FROM t1 WHERE a <= 3 2. 次に、作成したビューを参照してみましょう。 SELECT * FROM view1 このように、通常の SQL Server と同様の操作で、ビューを作成/参照することができます。 60 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 2.12 ストアド プロシージャの作成(CREATE PROCEDURE) ストアド プロシージャについても、通常の SQL Server と同様、CREATE PROCEDURE ステー トメントを利用して作成することができます。これも試してみましょう。 1. 次のように CREATE PROCEDURE ステートメントを記述して、指定した値(@maxVal) の分だけループ処理をして、 「t1」テーブルにデータを追加するストアド プロシージャを作成 してみます(入力が面倒な場合は、サンプル スクリプト内に完成版があります) 。 CREATE PROC proc1 @maxVal int AS DECLARE @i int = 1 WHILE @i <= @maxVal BEGIN INSERT INTO t1 VALUES(@i, 'CCC', GETDATE()) SET @i += 1 END 2. 作成後、@maxVal に「10000」を指定して、1 万件のデータを追加してみましょう。 EXEC proc1 @maxVal = 10000 Basic エディションの場合は、1 万件のデータを追加するのに 1 分数十秒程度の時間がかか ります(上位のエディションを利用している場合には、より速く実行することができます) 。 このように、ストアド プロシージャについても、通常の SQL Server と同様の操作で利用す ることができます。SQL Server 2012 の自習書シリーズの「開発者のための Transact-SQL 応用」編では、ストアド プロシージャについて詳しく説明しているので、こちらもぜひご覧い ただければと思います。 61 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 2.13 インデックスの作成 インデックスについても、通常の SQL Server と同様、CREATE INDEX ステートメントを利用 して作成することができます。これも試してみましょう。 1. まずは、クエリ エディターのツールバーの[実際の実行プランを含める]をクリックして、ク エリの実行時に実行プランが表示されるようにします。 1 2. 続いて、次のように、 「t1」テーブルの「b」列が「9999」のデータを検索するクエリを実行 して、実行プランを確認します。 SELECT * FROM t1 WHERE b = 9999 1 ↓ Clustered Index Scan(クラスター化 インデックスの全スキャン)になってい ることを確認 結果は、Clustered Index Scan(クラスター化インデックスの全スキャン)が表示されて、 全件検索(全データのフル スキャン)が行われていることを確認できます。 3. 次に、CREATE INDEX ステートメントを利用して、 「b」列に「idx_b」という名前のインデ ックスを作成してみます。 CREATE NONCLUSTERED INDEX idx_b ON t1(b) 62 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 4. 作成後、前の手順と同じクエリを実行して、実行プランを確認します。 SELECT * FROM t1 WHERE b = 9999 作成したインデックス (idx_b)の Index Seek になっていることを確認 今度は、作成したインデックス(idx_b)の Index Seek(ピンポイント検索)になっている ことを確認することができます。 インデックスの再構築(REBUILD) インデックスの再構築についても、通常の SQL Server と同様、ALTER INDEX ステートメント を利用して行うことができます。これも試してみましょう。 1. 次のように ALTER INDEX ステートメントを記述して、「idx_b」インデックスの再構築を 実行してみます。 ALTER INDEX idx_b ON t1 REBUILD 以上のように、Azure SQL Database でも、通常の SQL Server と同様の操作で、インデッ クスの作成や実行プランの確認、インデックスの再構築などを行うことができます。 63 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 そのほかのインデックス操作 Azure SQL Database の最初の頃のバージョンでは、FILLFACTOR や、オンラインでのインデッ クス再構築、インデックスの再構成(REORGANIZE)、dm_db_index_physical_stats 動的管 理 関数 に よ る断 片 化 状況 の チェ ッ ク など が サ ポー ト され て い なか っ た ので す が、 最 新 版 の Version 12 ではこれらの操作も問題なく利用することができます。 Version 11 では、2GB を超えるインデックスの再構築がサポートされていませんでしたが、 Version 12 からは 2GB を超えたインデックスでも再構築や再構成がサポートされるようにな りました。 なお、インデックスや実行プラン、断片化、FILLFACTOR を利用したインデックスの再構築などに ついては、SQL Server 2012 の自習書シリーズの「インデックスの基礎とメンテナンス」編で詳 しく説明しているので、こちらもぜひご覧いただければと思います。 64 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 2.14 V12 からサポートされた SELECT INTO、SQL CLR Azure SQL Database は、最新版の V12(Version 12)からは、SELECT INTO や SQL CLR (CLR 統合)がサポートされるようになりました。 SELECT INTO SELECT INTO は、SQL Server と同様、次のように実行することができます(t1 テーブルをも とに、t1_copy テーブルを作成します) 。 SELECT * INTO t1_copy FROM t1 t1 テーブル と同じ構成 データも コピーされている このように、Version 12 からは SELECT INTO が利用できるようになったので、大変便利です。 SQL CLR(CLR 統合) Azure SQL Database の Version 12 からは、権限セットが「SAFE」(安全)に設定されている SQL CLR オブジェクトがサポートされるようになりました。例えば、次のように作成した SQL CLR ユーザー定義関数があるとします。 65 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 SQL CLR オブジェクトを 作成するための データベース プロジェクト Visual Basic の StrConv 関数で 半角変換する SQL CLR 関数 [権限のレベル]を 「SAFE」に設定 この SQL CLR ユーザー定義関数を Azure SQL Database 上に配置すると、次のように実行する ことができます。 Azure SQL Database で SQLCLR を実行できる 半角カタカナ に変換される SQL CLR オブジェクトを Azure SQL Database 上に配置する方法には、次の 3つ方法がありま す。 1. V12 対応の最新版の SSDT を利用する(現在は、英語版の Visual Studio 2013 にのみイ ンストール可能) 2. Visual Studio 2013 上で、「スクリプト生成」を行う 3. いったん SQL Server 上に配置して、配置したものをスクリプト生成する 1つ目の最新版の SSDT を利用する方法は、執筆時点では、英語版の Visual Studio 2013 が必 要になるのですが(後日、日本語版に対応した SSDT の最新版が登場する予定です)、次のように 66 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 [Project Settings ]( プロ ジェ クト の設 定 ) ペ ージ で、 ター ゲッ ト プ ラッ トフ ォー ムに 「Microsoft Azure SQL Database V12 (Preview)」を選択します。 V12 を 選択できる これで、[BUILD](ビルド)メニューから[Publish](公開)をクリックすれば、Azure SQL Database 上に SQL CLR オブジェクトを配置することができます。 2つ目の方法は、日本語版の Visual Studio 2013 で、データベース プロジェクトを作成し、 [ビ ルド]メニューの[公開]をクリックしたときに、 [スクリプトの生成]ボタンをクリックします。 このボタンをクリックすると、次のように CREATE ASSEMBLY と CREATE FUNCTION ステ ートメントを生成してくれるので、この 2つをコピーします。 この2つ をコピー 67 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 コピーしたスクリプトを、次のように Azure SQL Database 上で実行すれば、SQL CLR オブジ ェクトを配置することができます。 クエリ エディターに 貼り付けて実行 これで、SQL CLR オブジェクトを利用できるようになります。 Azure SQL Database で SQLCLR を実行できる 半角カタカナ に変換される 3つ目の SQL CLR オブジェクトの配置方法は、いったん SQL Server 上に配置した SQL CLR オブジェクトをもとにスクリプト(CREATE ASSEMBLY と CREATE FUNCTION)を生成する というものです。これは、Management Studio を利用して、次のように行えます。 CREATE ASSEMBLY ステートメントが生成される これをスクリプト化すれば CREATE FUNCTION ステートメントを生成できる 68 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 データベースの[プログラミング]の[アセンブリ]フォルダーを展開して、SQL Server 上に登 録した SQL CLR オブジェクトを右クリックして、[アセンブリのスクリプト化]をクリックすれ ば、CREATE ASSEMBLY ステートメントを生成できます。また、 [関数]フォルダーや[ストア ド プロシージャ]フォルダー内の該当オブジェクトを右クリックして、 [~のスクリプト化]をク リックすれば、CREATE FUNCTION や CREATE PROCEDURE ステートメントを生成するこ とができます。 このように、Version 12 からは SQL CLR オブジェクトが利用できるようになったので、大変便 利です。 Note: ロケール ID に注意 本文中に SQL CLR で利用した StrConv 関数は、次のように第 3 引数にロケール ID(1041)を指定していました。 ロケールID に 1041 (日本)を指定 この SQL CLR 関数は、弊社のお客様で実際に利用しているものなのですが、オンプレミスの SQL Server 環境(日 本語環境)であれば、第 3 引数(ロケール ID)を省略していても何の問題もありません。しかし、Azure SQL Database は、英語環境になるので、ロケール ID を省略していると、次のように実行エラーとなってしまいます。 ロケールID を 省略している場合 実行エラー となってしまう したがって、ロケール ID を引数として渡す必要があるメソッドを利用して、SQL CLR オブジェクトを作成している 場合には、ロケール ID を明示的に指定(日本なら 1041)するようにしてみてください。 69 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 2.15 Azure SQL Database でサポートされるその他のオブジェクト ここまでの手順では、以下のデータベース オブジェクトについて、通常の SQL Server と同様の 操作で、Azure SQL Database 上にも作成/利用できることを確認しました。 テーブル/データ 制約 ビュー ストアド プロシージャ インデックスの作成/インデックス再構築/断片化の調査 V12 からは、SELECT INTO/SQL CLR オブジェクト(SAFE のみ) これらの他にも、通常の SQL Server と同様の操作で多くのデータベース オブジェクトを作成/ 利用することができます。その主なものは、次のとおりです(Azure SQL Database は、SQL Server とほぼ完全互換なので、利用できないオブジェクトのほうが少ないぐらいになります)。 SQL Server でサポートされている組み込み関数 ユーザー定義関数 シノニム トリガー sp_executesql サブクエリ(副問い合わせ) ローカル一時テーブル テーブル変数 CTE(共通テーブル式) MERGE ステートメント トランザクション 例外処理(TRY ~CATCH) RAISERROR によるエラーの発生 カーソル データベース ユーザーとオブジェクト権限 データベース ロール これらについては、サンプル スクリプト内のファイルに同じように試せるスクリプトを用意して いるので、こちらもぜひ試しながら読み進めてください。 70 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 SQL Server でサポートされている組み込み関数 Azure SQL Database の最新版である Version 12 の「12」は、SQL Server 2014 のビルド番 号である「12」と同じバージョンであることを意味しているので、SQL Server 2014 のデータベ ース エンジンと互換性があります(SQL Server 2014 で利用できる機能のほとんどを、Azure SQL Database でも利用することができます) 。 した がっ て 、SQL Server 2014 で サポ ート され てい る組 み込 み関 数 ( 月 末を 取得 できる EOMONTH や、文字列から日付を生成できる DATEFROMPARTS、書式を変更できる FORMAT、 条件分岐ができる IIF、CHOOSE など)も問題なく利用することができます。これらも、次のよ うに試すことができます。 -- 月末を取得 SELECT EOMONTH(d), d, * FROM t1 yyyy/MM/dd yyyy/MM また、V12 からは、集計関数での OVER 句もサポートされるようになったので、次のように前年 同月を取得したり、累積金額を取得したりすることも簡単に行えるようになりました。 71 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 V12 からは OVER 句がサポート されるようになった 受注と受注明細テーブルは Step4の移行手順を利用して NorthwindJ データベースを Azure SQL Database 上に 移行することで利用できます 前年同月(2005年 1月) の受注金額 OVER 句 2005年 1月と 2月 を足した金額 2005年 1月と 2月、 3月 を足した金額 2005年 1月~12月 までを足した金額 2006年 1月と 2月 を足した金額 ユーザー定義関数(CREATE FUNCTION) ユーザー定義関数は、通常の SQL Server での操作と同様、CREATE FUNCTION ステートメン トを利用して作成することができます。 72 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 -- ユーザー定義関数の作成例 CREATE FUNCTION dbo.func1(@p1 int) RETURNS int BEGIN RETURN @p1 * 100 END シノニム(CREATE SYNONYM) シノニムは、通常の SQL Server での操作と同様、CREATE SYNONYM ステートメントを利用 して作成することができます。 -- シノニムの作成例 CREATE SYNONYM synonymTest1 FOR t1 トリガー(CREATE TRIGGER) トリガーは、通常の SQL Server での操作と同様、CREATE TRIGGER ステートメントを利用し て作成することができます。 -- トリガーの作成例 CREATE TRIGGER triTest1 ON t1 FOR UPDATE AS SELECT * FROM deleted SELECT * FROM inserted deleted テーブル 更新前の値 222 inserted テーブル 更新後の値 999 73 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 sp_executesql sp_executesql システム ストアド プロシージャについても、通常の SQL Server での操作と 同様に利用することができます。 -- sp_executesql の利用例 EXEC sp_executesql N'SELECT * FROM t1 WHERE a <= @p1' ,N'@p1 int' ,@p1 = 4 サブクエリ(副問い合わせ) サブクエリについても、通常の SQL Server での操作と同様に利用することができます。 -- サブクエリの例(a 列の大きい順に 11件目から 20件目までを取得) SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY a DESC) AS rownum, * FROM t1 ) AS temp WHERE temp.rownum BETWEEN 11 AND 20 ページング(OFFSET .. FETCH) Azure SQL Database では、SQL Server 2012 以降でサポートされているページング機能 (OFFSET .. FETCH)を利用することもできます。これを利用すれば、n 件目から m 件目を取得 74 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 するといったことも、次のように簡単に記述することができます。 -- OFFSET .. FETCH で n件目から m件取得 SELECT * FROM t1 ORDER BY a DESC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY 10件飛ばして、11件目から 10件分取得 = 11件目から 20件目までを取得 (前述のサブ クエリの結果と同じものを取得) ローカル一時テーブル ローカル一時テーブルについても、通常の SQL Server での操作と同様に利用することができま す。 -- a 列の大きい順に並べ替えた結果を一時テーブルに格納 -- SELECT INTO は V12 から利用可能 SELECT ROW_NUMBER() OVER (ORDER BY a DESC) AS rownum, * INTO #t1 FROM t1 -- 11件目から 20件目までを取得(前述のサブクエリの結果と同じものを取得) SELECT * FROM #t1 WHERE rownum BETWEEN 11 AND 20 -- 一時テーブルの削除 DROP TABLE #t1 なお、SELECT INTO がサポートされるようになったのは、V12 からなので、V11 を利用して いる場合は、CREATE TABLE ステートメントを利用して、一時テーブルを作成する必要がありま す。 一時テーブルの照合順序は、SQL Server 2012 以降でサポートされている CDB(Contained Database:包含データベース)の動作と同様、 (tempdb データベースの照合順序に関係なく)デ ータベースに設定した照合順序が継承されるようになっています。 75 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 テーブル変数 テーブル変数についても、通常の SQL Server での操作と同様に利用することができます。 -- テーブル変数の作成 DECLARE @t table ( rownum int ,a int ,b int ,c varchar(200) ,d datetime ) -- a 列の大きい順に並べ替えた結果を格納 INSERT INTO @t SELECT ROW_NUMBER() OVER (ORDER BY a DESC) AS rownum ,* FROM t1 -- 11件目から 20件目までを取得(前述のサブクエリの結果と同じものを取得) SELECT * FROM @t WHERE rownum BETWEEN 11 AND 20 CTE(共通テーブル式) CTE(共通テーブル式:Common Table Expression)についても、通常の SQL Server での操 作と同様に利用することができます。 -- CTE の例(前述のサブクエリの結果と同じものを取得) WITH cteTest1 AS ( SELECT ROW_NUMBER() OVER (ORDER BY a DESC) AS rownum ,* FROM t1 ) SELECT * FROM cteTest1 WHERE rownum BETWEEN 11 AND 20 MERGE ステートメント MERGE ステートメントについても、通常の SQL Server での操作と同様に利用することができ ます。 -- MERGE ステートメントの例 MERGE INTO t1 USING t2 ON t1.a = t2.a WHEN MATCHED THEN UPDATE SET t1.b = t2.b WHEN NOT MATCHED THEN INSERT VALUES ( t2.b, 'MMM', GETDATE() ); 76 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 トランザクション トランザクションについても、通常の SQL Server での操作と同様に利用することができます。 -- トランザクションの例 CREATE TABLE tranTest ( a int PRIMARY KEY, b int) -- コミットの場合 BEGIN TRAN INSERT INTO tranTest VALUES ( 1, 777 ) COMMIT TRAN -- ロールバックの場合 BEGIN TRAN INSERT INTO tranTest VALUES ( 2, 777 ) ROLLBACK TRAN SELECT * FROM tranTest 例外処理(TRY .. CATCH) 例外処理(TRY .. CATCH)についても、通常の SQL Server での操作と同様に利用することがで きます(THROW を利用した再スローにも対応しています) 。 -- 例外処理(TRY .. CATCH)の利用例 BEGIN TRY BEGIN TRAN INSERT INTO tranTest VALUES ( 2, 999 ) INSERT INTO tranTest VALUES ( 1, 999 ) COMMIT TRAN END TRY BEGIN CATCH ROLLBACK TRAN SELECT ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY() END CATCH 77 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 RAISERROR RAISERROR についても、通常の SQL Server での操作と同様に利用することができます。 -- RAISERROR によるエラーの発生例 RAISERROR ('エラーテスト!', 16, 1) カーソル カーソルについても、通常の SQL Server での操作と同様に利用することができます。 -- カーソルの例 DECLARE @a int, @c varchar(200) DECLARE cur CURSOR FOR SELECT TOP 10 a, c FROM t1 OPEN cur FETCH NEXT FROM cur INTO @a, @c WHILE (@@FETCH_STATUS <> -1) BEGIN PRINT CONVERT(varchar, @a) + ', ' + @c FETCH NEXT FROM cur INTO @a, @c END CLOSE cur DEALLOCATE cur 以上のように、Azure SQL Database では、ほとんどの Transact-SQL ステートメントを、SQL Server と変わりなく利用することができます(ほぼ完全互換なので、クラウドでもオンプレミス でもシームレスに利用することができます) 。 78 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 2.16 Azure SQL Database でのロックの動作(読み取り一貫性) Azure SQL Database で は 、「 READ_COMMITED_SNAPSHOT 」 お よ び 「 SNAPSHOT ISOLATION」 (スナップショット分離レベル)が有効化されています(SQL Server の場合は、既 定では有効化されていません)。これは、次のように databases システム ビューを参照すること で確認できます。 Read Committed Snapshot が有効に設定 Snapshot Isolation (スナップショット分離レベル) が有効に設定されている 「READ_COMMITED_SNAPSHOT」は、SELECT ステートメントを発行した時点でのデータが 読 み 取 れ る こ と を 保 証 す る 機 能 ( Oracle デ ー タ ベ ー ス で の デ フ ォ ル ト の 動 作 と 同 じ )、 「SNAPSHOT ISOLATION」(スナップショット分離レベル)は、トランザクションを開始した 時点でのデータが読み取れることを保証する機能です。 READ_COMMITED_SNAPSHOT での動作 READ_COMMITED_SNAPSHOT が有効な場合は、次の図のように、排他ロックのかかっている更 新中のデータ(未コミットのまだ確定していないデータ)は参照させないようにし、更新前のデー タ(その時点での正しいデータ)を参照させることで一貫性を保ちます。 トランザクション トランザクション Y X 【 a=2 を更新 】 UPDATE t1 SET b= 777 WHERE a=2 : : 処理中 : : t1 排他ロック 1 更新中のデータ。 未コミット(まだ確定 していない)データ テーブル tempdb データベース a b 1 888 更新前データ 2 777 2 3 333 5 555 : : 999 排他ロック中 でもロック待ち は発生しない 【 a=2 を検索 】 2 SELECT * FROM t1 WHERE a=2 SELECT 発行時点 での正しいデータ COMMIT or ROLLBACK ROLLBACK (取り消し) の 場合は、777 から 999 に戻る なお、更新前のデータは、内部的には tempdb データベース内へ格納されています。このように、 79 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 Azure SQL Database では、排他ロックにブロックされないデータの読み取りが可能になってい ます。 この状況は、次のように確認することができます。 排他ロックにブロックされずに読み取りが可能。 更新前のデータ「999」を取得 1 つ目の接続(左側)で「a=2」のデータを排他ロックして、そのときに 2つ目の接続(右側)か ら排他ロックのかかっているデータを参照します。ロック待ちは発生せずに(排他ロックにはブロ ックされずに) 、 「999」というデータを参照できていることを確認できます。この「999」は、更 新前のデータ(現時点での正しいデータ)になります。 スナップショット分離レベル(SNAPSHOT ISOLATION LEVEL) Azure SQL Database では、スナップショット分離レベルも有効化されています。これは、トラン ザクションを開始した時点でのデータが読み取れることを保証する機能です。 READ_COMMITTED_SNAPSHOT との違いは、次の図のとおりです(④の動作が違いです) 。 READ_COMMITTED_SNAPSHOT トランザクション X BEGIN TRANSACTION BEGIN TRANSACTION UPDATE t1 SET b= 777 WHERE a=2 1 更新後データ (まだ未確定) スナップショット分離レベル トランザクション Y BEGIN TRANSACTION 2 SELECT * FROM t1 WHERE a=2 更新後データ (まだ未確定) 更新前データ 2 SELECT * FROM t1 WHERE a=2 a b a b a b a b a b a b 2 777 2 999 2 999 2 777 2 999 2 999 更新前のデータが返る tempdb : 処理中 : COMMIT TRANSACTION 3 更新後データ(確定) a b 2 777 更新前のデータが返る tempdb : 処理中 : 3 BEGIN TRANSACTION UPDATE t1 SET b= 777 WHERE a=2 1 更新前データ トランザクション Y トランザクション X 4 COMMIT TRANSACTION 更新後データ(確定) SELECT * FROM t1 WHERE a=2 a b 2 777 4 SELECT * FROM t1 WHERE a=2 a b a b 2 777 2 999 更新後のデータ (確定した値)が返る SELECT ステートメント発行時点での 正しいデータ (確定済みのデータ) を返す 更新前のデータが返る トランザクション開始 (BEGIN TRANSACTION) 時点での 正しいデータ (確定済みのデータ) を返す 80 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 READ_COMMITTED_SNAPSHOT がステートメント レベル(文レベル)での読み取り一貫性であ ったのに対して、スナップショット分離レベルは、トランザクション レベルでの読み取り一貫性を 提供します。 READ_COMMITTED_SNAPSHOT は、SELECT ステートメントを発行した時点での確定(コミッ ト)されたデータを読み取り、スナップショット分離レベルは、トランザクション開始時点での確 定済みデータを読み取ります。トランザクションの途中でコミットされて確定したとしても、トラ ンザクションの開始時点で確定されていないデータは更新前の値を返し、開始時点での正しいデー タを一貫して返します。したがって、スナップショット分離レベルは、会計処理のように、トラン ザクションが長くなり、かつその間にほかのトランザクションからの影響を受けたくないような場 合に役立つ機能です。 この状況は、次のように確認することができます。 トランザクションの開始 1つ目の接続を コミット後 確定済みデータ トランザクションを開始 した時点のデータ 2つ目の接続(右側)では、トランザクション中は、一貫して「999」という値が返り、トランザ クションを開始した時点での正しいデータを取得できていることを確認できます。 このように、スナップショット分離レベルでは、トランザクション レベルでの読み取り一貫性を実 現することができます。 81 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 2.17 データ圧縮を利用する Azure SQL Database は、SQL Server でお馴染みの「データ圧縮」機能も利用することができま す。データ圧縮は、テーブル内のデータそのものを圧縮できる機能で、データを圧縮することによ って、ディスクへの書き込み/読み取り量(I/O 数)を減らすことができるので、性能向上を実現 できる機能です。ただし、圧縮を行う分、CPU パワーを余分に消費することになるので、それとの トレードオフになります。 データ圧縮は、SQL Server と同様、次のように設定することができます。 ALTER INDEX インデックス名 ON テーブル名 REBUILD [ PARTITION = パーティション番号 ] WITH ( DATA_COMPRESSION = [ ROW | PAGE | NONE] ) ALTER INDEX ステートメントでインデックスを再構築(REBUILD)するときに、WITH 句で DATA_COMPRESSION を指定することで、データ圧縮を行うことができます。データ圧縮には、 「行圧縮」(Row Compression)と「ページ圧縮」(Page Compression)の2種類があり、 DATA_COMPRESSION で ROW を指定した場合が行圧縮、PAGE を指定した場合がページ圧 縮、NONE を指定した場合が非圧縮(圧縮なし)になります。 行圧縮の内部動作 行圧縮では、次のように、固定長データ型の利用していない領域を削る(可変長データ型のように 扱う)ことで、データを圧縮しています。 行圧縮の例 int データ型 char(10) データ型 行圧縮で圧縮可能な領域 行圧縮は、固定長データ型を 可変長のように扱うことで圧縮可能! col1 99 88 88 77 77 77 99 なら 1バイトしか 使用しないので、 3バイト分圧縮可能 8888 なら 2バイトし か使用しないので、 2バイト分圧縮可能 int 型の場合は、使用していない領域を圧縮可能 col2 A A A B B B B C C C C 末尾の余分な空白 を圧縮可能 C C C C char 型の場合は、末尾の余分な空白を圧縮可能 int データ型は、4 バイトの固定長データ型ですが、「99」のように 0~255 の間の整数を格納す る場合には、1 バイトの使用領域で済むので、残りの 3 バイトを圧縮することができます。また、 「8888」のように -32,768~32,767 の間の整数である場合は、2 バイトの使用領域で済むので、 残りの 2 バイトを圧縮することができます。 char/nchar などの固定長文字列のデータ型の場合は、末尾に余分な空白がある場合に、そこを 圧縮することができます。 82 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 ページ圧縮の内部動作 ページ圧縮は、行圧縮よりもさらに圧縮ができる(サイズを小さくすることができる)機能です。 ページ圧縮では、行圧縮を行った後に、さらにページ単位での圧縮を行うことで、サイズを小さく することができます。具体的には、次のようにページ内での重複部分を圧縮(プレフィックスを圧 縮)しています。 ページ圧縮の例 ページ圧縮は、ページ内の プレフィックスの重複部分を圧縮! ページ圧縮では、Prefiix(接頭辞) が同じものは圧縮可能 ただし、ページ圧縮では、より高度な圧縮をかけるので、CPU のオーバーヘッドが行圧縮よりも上 がってしまうというデメリットがあります。 データ圧縮の試し方 データ圧縮は、次のように試すことができます。 -- テーブルの作成 -- PRIMARY KEY 兼クラスター化インデックス「pk_dtest1」を作成 CREATE TABLE dtest1 ( col1 int ,col2 int ,CONSTRAINT pk_dtest1 PRIMARY KEY(col1) ) -- 行圧縮を設定する ALTER INDEX pk_dtest1 ON dtest1 REBUILD WITH ( DATA_COMPRESSION = ROW ) このように、ALTER INDEX ステートメントを利用して、インデックスを再構築(REBUILD)す 83 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 るときに、DATA_COMPRESSION を指定することで、データ圧縮を行うことができま(ROW を 指定すれば行圧縮になります) 。 圧縮したものを、元に戻したい場合は、次のように NONE を指定するようにします。 ALTER INDEX pk_dtest1 ON dtest1 REBUILD WITH ( DATA_COMPRESSION = NONE ) 弊社事例 ~4 億件のデータ~ 次のグラフは、筆者のお客様のデータベース(4 億件のデータ)を、Azure SQL Database 上に配 置して、データ圧縮(行圧縮、ページ圧縮)を行った場合の結果です。 20.1% 圧縮 45.4% 圧縮 通常テーブル(圧縮なし)が 8.2GB であるところを、行圧縮では 6.5GB(約 20.1%) 、ページ圧 縮では 4.46GB(約 45.4%、半分近いサイズ)に圧縮することができています。このお客様のデ ータは、普段から tinyint や snallint などの小さいデータ型を活用されているので、行圧縮ではあ まり圧縮ができていませんが、int や bigint などの大きいデータ型を利用している環境であれば、 行圧縮でも 30%ぐらいの圧縮をすることができます。 また、このときのクエリの実行時間は、次のようになりました(S3 プレビュー版で計測) 。 CPU パワーを 余分に利用する分 だけ遅い 15%余分に CPUパワーを利用 42%余分に CPUパワーを利用 2.4倍 性能UP 28% 遅い 1.9倍 性能UP * 実行時間は、通常(圧縮なし)を 100 とした場合の相対値で表示。Standard エディションの S3(プレビュー版)で計測 84 54.2% 遅い SQL Server 2014 自習書 No.5 Azure SQL Database 入門 クエリ1 では、行圧縮で 2.4 倍の性能向上、ページ圧縮では 1.9 倍の性能向上を実現することが できたものの、クエリ2 では、行圧縮で 28%の性能ダウン、ページ圧縮では 54.2%の性能ダウ ンとなりました。データ圧縮では、速くなるクエリがある反面、CPU 利用のオーバーヘッドによっ て、遅くなるクエリも出てくるので、実際のクエリで検証を行うことが非常に重要になります。ま た、より利用頻度の高いクエリ(重要なクエリ)のスピードに注目するようにして、データ圧縮を 利用すべきかどうかを検討してみてください(使いどころをうまく選択すれば、データ圧縮は非常 に大きな効果を得ることができるので、ぜひ活用してみてください) 。 なお、データ圧縮については、SQL Server 2014 の自習書シリーズの「SQL Server 2005 ユー ザーのための SQL Server 2014」編でも詳しく説明しているので、こちらもぜひご覧いただけれ ばと思います。 Premium エディションへの変更や、列ストア インデックスを利用した場合 クエリの性能を向上させたい場合には、上位のエディションを利用するという方法もあります。前 述の事例グラフは、Standard エディションの S3(プレビュー版)で、データ圧縮を利用して性 能を向上させる例でしたが、これを Premium エディションに変更したり、Premium エディシ ョンでサポートされる「インメモリの列ストア インデックス」を利用したりすることで、次のよう に性能を向上させることができます。 Standard エディ ションの S3 Premium エディションの P1 に変更で 2.9倍 性能UP P2 に変更で 5.7倍 性能UP P3 に変更で 23.5倍 性能UP P3 で列ストア イ ンデックス利用で 184.5倍 性能UP * 実行時間は、S3 での通常(圧縮なし) を 100 とした場合の相対値で表示 Premium エディションの P1 に変更することで 2.9 倍、P2 で 5.7 倍、P3 なら 23.5 倍もの 性能向上を実現することができます。Premium エディションでは、高速ストレージの利用や、並 列クエリがサポートされていることによって、Standard エディションよりも良い性能でクエリを 実行することができます。また、列ストア インデックスに関しては、この後詳しく説明しますが、 これを利用すると、集計関連のクエリで驚異的な性能向上を実現することができます(グラフのよ うにクエリ1 では 184.5 倍もの性能向上)。 データ圧縮では性能が向上しなかった、前述の「クエリ2」でも、Premium エディションや列ス トア インデックスを利用すれば、次のように性能向上を実現することができます。 85 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 Standard エディ ションの S3 Premium エディションの P1 では変わらず P2 に変更で 1.8倍 性能UP P3 に変更で 6.7倍 性能UP P3 で列ストア イ ンデックス利用で 172.6倍 性能UP * 実行時間は、S3 での通常(圧縮なし) を 100 とした場合の相対値で表示 Premium エディションの P1 への変更では、性能向上はありませんでしたが、P2 に変更するこ とで 1.8 倍、P3 なら 6.7 倍もの性能向上を確認することができました。また、インメモリの列 ストア インデックスを利用すれば、172.6 倍もの性能向上を実現できることも確認できました。 インメモリの列ストア インデックスによる性能効果 列ストア インデックスは、Premium エディションでのみ利用できる機能なので、P3 だけでな く、P1 や P2 でも利用することができます。P1 や P2 で利用したときの結果は、次のとおりで す(クエリ1 とクエリ2 は、前述のクエリと同じものです) 。 P1 で 22.3倍 性能UP P1 で 22.8倍 性能UP P2 で 16.0倍 性能UP P2 で 23.9倍 性能UP P3 で 22.7倍 性能UP * 実行時間は、P1 での通常を 100 とした場合の相対値で表示 P3 で 25.6倍 性能UP * 実行時間は、P1 での通常を 100 とした場合の相対値で表示 このように、列ストア インデックスを利用すれば、20 倍以上の性能向上を簡単に実現することが できます。列ストア インデックスの作成方法や、どういった場面で利用するのかについては、次の 項で説明します。 86 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 2.18 インメモリの列ストア インデックスを利用する Azure SQL Database は、V12(Version 12)からは、インメモリの列ストア インデックスを利 用できるようになりました(Premium エディションでのみ利用することができます) 。 列ストア インデックスは、SQL Server では、SQL Server 2012 のときに、読み取り専用の列 ストア インデックス(非クラスター化列ストア インデックスと呼ばれます)、SQL Server 2014 からは更新可能な列ストア インデックス(クラスター化列ストア インデックスと呼ばれます)が 提供されました。Azure SQL Database では、Premium エディションを利用していれば、どちら の列ストア インデックスも利用することができます。 列ストア インデックスは、いわゆる「インメモリのカラム指向データベース」の SQL Server に おける実装であり、内部的には列(カラム)単位でインデックスを格納して、それらを高度に圧縮 することで、性能を大幅に向上させることができる機能です(インメモリのカラムベース エンジン が利用されています)。特に大きな効果を発揮するのが、大量のデータに対する集計処理の場合で す。夜間バッチ処理時(夜間バッチでの日次集計や月次集計処理など)や、DWH(データ ウェア ハウス) 、BI システム環境での集計処理で、大きな性能向上を実現できる機能です。 列ストア インデックスの作成方法 更新可能な列ストア インデックス(クラスター化列ストア インデックス:通称 CCSI)を作成し たい場合には、次のように記述します。 CREATE CLUSTERED COLUMNSTORE INDEX インデックス名 ON テーブル名 CREEATE INDEX ステートメントに COLUMNSTORE キーワードを追加するだけで、列ストア インデックスを作成することができます。更新 可能な列ストア インデックスの場合は、 CLUSTERED キーワードを付けて、ON 句にテーブル名を指定するだけで作成することができま す。 読み取り専用の列ストア インデックス(非クラスター化列ストア インデックス)作成したい場合 には、次のように記述します。 CREATE NONCLUSTERED COLUMNSTORE INDEX インデックス名 ON テーブル名 (列名1, 列名2, 列名3, …) 違いは、NONCLUSTERED キーワードを付けて、テーブル名の隣に、インデックスに含めたい列 をカンマ区切りで指定している点です。 列ストア インデックスの試し方 列ストア インデックスは、次のように試すことができます。 87 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 -- テーブルの作成 CREATE TABLE ctest1 ( col1 int ,col2 int ) -- 更新可能な列ストア インデックス(クラスター化列ストア インデックス)の作成 CREATE CLUSTERED COLUMNSTORE INDEX idx_ctest1 ON ctest1 このように、CREATE INDEX ステートメントで COLUMNSTORE キーワードを追加すること で、列ストア インデックスを作成することができます。 列ストア インデックスを作成している場合には、実行プランが次のように変わります。 Columnstore インデックス スキャン と表示される なお、Standard エディションを利用している場合は、列ストア インデックスを作成したり、利用 したりしようとするときに、次のように「サポートされていません」という主旨のエラー メッセー ジが返ります。 Standard エディションの場合は、 列ストア インデックスを利用でき ないのでエラーになる 弊社事例 ~4 億件のデータ~ 次のグラフは、前述のデータ圧縮のときと同様、筆者のお客様のデータベース(4 億件のデータ) を、Azure SQL Database 上に配置して、クラスター化列ストア インデックス(CCSI)を作成し 88 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 た場合の結果です。 86.4% 圧縮 わずか 1/7 以下に! 通常テーブル(圧縮なし)が 8.2GB、行圧縮では 6.5GB、ページ圧縮では 4.5GB であるところ を、クラスター化列ストア インデックスでは、わずか 1.1GB(1/7 以下のサイズ)にまで圧縮す ることができています。 また、このときのクエリの実行時間は、次のとおりです(前出のグラフを再掲)。 P1 で 22.3倍 性能UP P1 で 22.8倍 性能UP P2 で 16.0倍 性能UP P2 で 23.9倍 性能UP P3 で 22.7倍 性能UP * 実行時間は、P1 での通常を 100 とした場合の相対値で表示 P3 で 25.6倍 性能UP * 実行時間は、P1 での通常を 100 とした場合の相対値で表示 クエリ1 でも、クエリ2 でも、20 倍以上もの性能向上を確認することができました。このよう に、列ストア インデックスは、大きな性能向上を期待できる機能なので、ぜひ試してみてください。 なお、列ストア インデックスについては、SQL Server 2014 の自習書シリーズの「SQL Server 2014 の新機能の概要」編でも詳しく説明しているので、こちらもぜひご覧いただければと思いま す。 89 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 2.19 データ パーティションを利用する Azure SQL Database は、V12(Version 12)からは、データ パーティションも利用できるよう になりました。 データ パーティションは、SQL Server と同様、次のように作成することができます。 -- パーティション関数の作成 CREATE PARTITION FUNCTION pFunc1 (datetime) AS RANGE RIGHT FOR VALUES ('2013/01/01', '2014/01/01', '2015/01/01') -- パーティション構成の作成 CREATE PARTITION SCHEME pScheme1 AS PARTITION pFunc1 ALL TO ([PRIMARY]) -- パーティション テーブルの作成 CREATE TABLE ptest1 ( colA int IDENTITY(1,1) NOT NULL ,colB datetime ) ON pScheme1(colB) CREATE PARTITION FUNCTION でパーティション関数、CREATE PARTITION SCHEME で パーティション構成を作成して、CREATE TABLE でのテーブル作成時に、ON 句でパーティショ ン構成(上の例では pScheme1)を指定すれば、パーティション テーブルを作成できます。 作成したパーティション関数(pFunc1)は、次のように 4 つのパーティションに分割できるよう になっています(2012 年~2015 年までの 4 年分のパーティション) 。 RANGE RIGHT ('2013/01/01', '2014/01/01', '2015/01/01') 2013/1/1 x < 2013/1/1 パーティション 2012年 1 2014/1/1 2013/1/1 <= x < 2014/1/1 パーティション 2013年 2 2015/1/1 2014/1/1 <= x < 2015/1/1 パーティション 3 2014年 2015/1/1 < x パーティション 4 2015年 このパーティション テーブルに対して、次のようにデータを INSERT してみます。 -- データを 6件 INSERT INSERT INTO ptest1 VALUES ('2012/12/31') ,('2013/01/01') ,('2013/12/31') ,('2014/01/01') ,('2014/12/31') ,('2015/01/01') データを INSERT した後、どのパーティションに格納されたかどうかを確認するために、次のよ 90 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 うに $PARTITION を利用してみます。 -- どのパーティションに格納されたか確認 SELECT *, $PARTITION.pFunc1(colB) As [パーティション番号] FROM ptest1 2012年は パーティション1 2013年は パーティション2 2014年は パーティション3 2015年は パーティション4 2012 年はパーティション1、2013 年はパーティション2 という形で、年ごとに格納されるパー ティションが分かれていることを確認できます。 データ パーティションでは、SQL Server と同様、パーティション インデックスを作成すること もできます。これは次のように行えます。 -- パーティション インデックスの作成 CREATE CLUSTERED INDEX index_colB ON ptest1 (colB) ON pScheme1 (colB) パーティション インデックス を利用した効率の良い検索 になっていることを確認できる パーティション単位でのインデックスの再構築、圧縮設定 データ パーティションを構成すれば、パーティション単位でインデックスの再構築や再構成を行 91 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 ったり、データ圧縮の設定を行ったりすることもできます。これは、次のように実行できます。 -- パーティション2 を再構築 ALTER INDEX index_colB ON ptest1 REBUILD PARTITION = 2 -- パーティション3 を再構成 ALTER INDEX index_colB ON ptest1 REORGANIZE PARTITION = 3 -- パーティション1 を行圧縮 ALTER TABLE ptest1 REBUILD PARTITION = 1 WITH ( DATA_COMPRESSION = ROW ) このように、Azure SQL Database では、Version 12 からデータ パーティション機能が利用で きるようになりました。 なお、データ パーティションについては、SQL Server 2012 の自習書シリーズの「データ パー ティション入門」編でも詳しく説明しているので、こちらもぜひご覧いただければと思います。 92 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 2.20 行レベル セキュリティ(プレビュー版)を利用する Azure SQL Database は、V12(Version 12)では、行レベル セキュリティ(プレビュー版)機 能を利用することができます。これは、SQL Server 2014 にはまだ実装されていない機能で、将 来のバージョンの SQL Server で搭載される予定のものです。執筆時点ではプレビュー版になり ますが、SQL Server よりも早く、Azure SQL Database 上に実装された形です。 行レベル セキュリティを利用すれば、次のようにユーザーごとに、見せたい行を制限できるように なります。 行レベル セキュリティ t1 User1 User2 は User2 のデータ のみを参照できる テーブル colUserName colA colB User1 1 AAA User1 2 BBB User2 3 CCC User2 4 DDD User3 5 EEE User1 6 FFF : : : User1 は User1 のデータ のみを参照できる User2 Manager Manager は 全データを参照できる この図のような行レベル セキュリティを実装するには、次のように操作します。 -- データベース ユーザーの作成 CREATE USER Manager WITH PASSWORD = 'P@ssword' CREATE USER User1 WITH PASSWORD = 'P@ssword' CREATE USER User2 WITH PASSWORD = 'P@ssword' -- テーブルの作成。colUserName 列にアクセス可能なユーザーの名前を格納するようにする CREATE TABLE rsTest1 ( colUserName sysname ,colA int ,colB varchar(200) ) -- データを 4件追加 INSERT rsTest1 VALUES ('User1', 1, ,('User1', 2, ,('User2', 3, ,('User2', 4, 'AAA') 'BBB') 'CCC') 'DDD') -- データの確認 SELECT * FROM rsTest1 -- データベース ユーザーに、テーブルに対する SELECT 権限を付与 GRANT SELECT ON rsTest1 TO Manager 93 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 GRANT SELECT ON rsTest1 TO User1 GRANT SELECT ON rsTest1 TO User2 go -- ユーザー定義関数の作成 -- ユーザーはユーザーのデータ、Manager は全データを参照できるように制御 CREATE FUNCTION rsFunc1(@UserName AS sysname) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS result WHERE USER_NAME() = @UserName OR USER_NAME() = 'Manager' go -- フィルターの作成 -- 上で作成したユーザー定義関数に、該当テーブルの colUserName 列を与える CREATE SECURITY POLICY rsFilter1 ADD FILTER PREDICATE dbo.rsFunc1(colUserName) ON dbo.rsTest1 WITH (STATE = ON) go このように、USER_NAME() 関数を利用したユーザー定義関数を作成して(上の例では rsFunc1 と い う 名 前 で 作 成 )、 CREATE SECURITY POLICY ス テ ー ト メ ン ト で ADD FILTER PREDICATE を指定してフィルターを作成します。該当テーブル(rsTest1)に対して、rsFunc1 関数でフィルターをかけ、関数へ与える引数には colUserName 列を与えるという構成です。 まずは、何も権限が与えられていないユーザーでテーブルを SELECT してみます。 -- 何も権限が与えられていないユーザー SELECT * FROM rsTest1 結果には、何も返ってきません。このように、テーブルに対してフィルターを設定していると、ユ ーザー定義関数で設定したユーザー以外は、テーブル データを参照できない形になります。 次に、Manager ユーザーでアクセスをしてみます。Azure SQL Database では、SQL Server と 同様、次のように EXECUTE AS USER ステートメントを利用して、指定したユーザーをシミュ レートすることができます。また、REVERT ステートメントを実行することでシミュレートを終 94 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 了することができます。 -- Manager をシミュレート EXECUTE AS USER = 'Manager' SELECT * FROM rsTest1 REVERT Manager ユーザーは、すべてのデータを参照することができます。 次に、User1 ユーザーでアクセスをしてみます。 -- User1 をシミュレート EXECUTE AS USER = 'User1' SELECT * FROM rsTest1 REVERT User1 ユーザーは、colUserName 列が User1 のデータしか参照できないことを確認できます。 次に、User2 ユーザーでアクセスをしてみます。 -- User2 をシミュレート EXECUTE AS USER = 'User2' SELECT * FROM rsTest1 REVERT 95 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 User2 ユーザーは、colUserName 列が User2 のデータしか参照できないことを確認できます。 このように、行レベル セキュリティを利用すれば、ユーザーごとに、見せたい行を制限できるよう になります。 なお、設定した行レベル セキュリティを無効化にするには、次のようにフィルターを無効化します (STATE を OFF に設定します) 。 -- 行レベル セキュリティを無効化する(フィルターを無効化) ALTER SECURITY POLICY rsFilter1 WITH (STATE = OFF) その他、行レベル セキュリティに関する最新情報は、次のページが参考になると思います。 Row-Level Security Preview https://msdn.microsoft.com/library/7221fa4e-ca4a-4d5c-9f93-1b8a4af7b9e8.aspx 96 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 2.21 動的データ マスク(プレビュー版)を利用する Azure SQL Database は、V12(Version 12)では、動的データ マスク(Dynamic Data Masking) 機能を利用することができます(執筆時点ではプレビュー版) 。これも、行レベル セキュリティ機 能と同様、SQL Server 2014 にはまだ実装されていない機能です(将来のバージョンの SQL Server で搭載される予定)。 動的データ マスクを利用すれば、次のようにデータをマスクすることができるようになり、セキュ リティ強化を実現できるようになります(メールアドレスやクレジット カード番号などの顧客情 報をマスクしておけば、情報流出を抑えることができるようになります)。 動的データ マスクの利用例 特権ユーザーは 元データを参照できる 特権ユーザー 権限のない ユーザー 数値を乱数 に置換 文字列を先頭 2文字 残して、残りをマスク メールアドレス をマスク 日付データを 現在時刻に置換 権限のないユーザーは マスクされたデータ しか参照できない 動的データ マスクは、次のように[データベースの構成]ブレードで、 「動的データ マスク」をク リックすることで設定することができます。 2 3 1 [動的データ マスク]ブレードでは、 [動的データ マスク]で「有効」を選択、 [特権のあるログ 97 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 イン]で任意の特権ユーザー(画面は matumoto)を入力します(ここで入力したユーザーは、 マスクに関係なく元のデータを参照できるユーザーになります。複数のユーザーがいる場合には、; セミコロンで区切って指定することができます) 。 次に、[マスクの追加]をクリックして、どのようなマスクにするのかを設定していきます。 5 1 4 2 3 [マスクの種類]で「テーブルと列」を選択することで、テーブルの列に対して、マスクを設定で きるようになります。 [テーブル]には、マスクを設定したいテーブル名(画面は t1) 、 [列]には、 マスクを設定したい列の名前( (画面は c)を入力して、 [マスク方法の選択]でどのようにマスク を設定するのかを選択します。画面では、 「ユーザー設定の文字列」を選択して、 [公開されたプレ フィックス]で「2」と入力していますが、これによって、次のように「t1」テーブルの「c」列の データを、先頭(プレフィックス)の 2 文字だけ公開(伏せずに)して、残りをマスクする(既定 では X でパディングする)ように設定できます。 マスクされたデータ 先頭2文字は表示されて 残りが X でマスクされる 元のデータ t1 テーブル の「c」列 そのほかに設定できるマスク ルールには、次のものがあります。 98 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 日付データを 現在時刻に置換 クレジット カード番号 をマスク メールアドレスをマスク 数値を乱数に置換 マスクのルールを設定した後、 [保存]ボタンをクリックすれば、マスクの設定が完了です。 設定後は、 [特権のあるログイン]で設定したユーザーからは、元のデータ(マスクする前のデータ) を参照することができ、その他のユーザーからは、マスクされたデータしか参照できないようにな ります。ただし、ユーザーがログインするときには、後述の「監査」機能と同様、Azure SQL Database の SQL サーバーに接続するときの名前に、 「.secure」を付けたものを指定する必要があります(サ ーバー名.database.windows.net を サーバー名.database.secure.windows.net に変更す る必要があります)。 .secure を付けて 接続する必要がある その他、動的データ マスクに関する最新情報は、次のページが参考になると思います。 Get started with SQL Database Dynamic Data Masking http://azure.microsoft.com/en-us/documentation/articles/sql-database-dynamic-datamasking-get-started/ 99 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 STEP 3. Azure SQL Database の その他の操作 この STEP では、Azure SQL Database での特定時刻への復元(Point in time Restore)や、監査(Audit) 、DTU(性能指標) 、エディションの変更方法、性能 監視方法、SQL Server との主な相違点などを説明します。 この STEP では、次のことを学習します。 Azure SQL Database での特定時刻への復元(Point in time Restore) Azure SQL Database での監査(Audit) Azure SQL Database の DTU(性能指標)、エディションの変更方法 アラート ルールの設定(しきい値を超えた場合にメール送信) DMV(動的管理ビュー)による性能監視 sqlcmd ユーティリティからの接続 SQL Server との主な相違点 100 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 3.1 Azure SQL Database での特定時刻への復元 Azure SQL Database では、マイクロソフトによって、自動的にバックアップが行われていて、こ のバックアップは、指定した任意の時刻に復元(リストア)することができます。この機能は、 「Point in Time Restore」と呼ばれています。復元ポイント(何日前のデータにまで復元できるか)は、 エディションによって異なり、次のように設定されています。 エディション 復元ポイント Basic 7日以内の任意のポイント Standard 14日以内の任意のポイント Premium 35日以内の任意のポイント Basic エディションは 7 日、Standard エディションは 14 日、Premium エディション 35 日以 内の任意の日時/時刻にリストアすることができます。 Let's Try それでは、これを試してみましょう。バックアップは自動で取得されているので、復元を行うには、 復元したい時刻を指定するだけで完了です。 1. まずは、Azure ポータルで、該当データベースの[データベースの構成]ブレードを開きます。 2. [データベースの構成]ブレードでは、次のように[復元]ボタンをクリックします。 [復元]ブレード が表示される 1 3 復元データは 新しいデータベース として作成される 現在の時刻が UTC で表示される 復元可能な時刻が UTC で表示される 2 復元ポイントを 指定する 4 これにより、 [復元]ブレードが表示されて、復元ポイント(どの日時/時刻までデータを復元 101 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 したいのか)を指定できるようになります。 [日付]をカレンダーから選択して、 [時間]と[分] を UTC(協定世界時)で指定すれば、その時刻の状態へ復元することができます(日本の時 刻は、UTC に +9 時間したものになります)。 復元データは、現在のデータベースとは別に、新しいデータベースとして作成する必要がある ので、 [データベース名]には新しく作成するデータベースの名前を入力します。既定では、こ の名前には[AzureDB_2015-01-28T05-45Z]のように、 [データベース名_復元ポイント で指定した日付 T 時刻 Z]になっています。 名前や復元ポイントを設定後、 [作成]ボタンをクリックすると、データベースの作成が始まり ます。 3. データベースの作成状況は、画面左側の[通知]ブレードで確認することができます。 1 データベースの 作成中 4. データベースの復元が完了すると、SSDT や Management Studio からアクセスできるよう になります。 復元ポイント時点での データを参照できる 復元データをもとに 作成されたデータベース これで、指定した時点でのデータを参照することができるので、このデータをもとに、現在の データを修正していくことができるようになります。 なお、復元によって、元々のデータベースと同じエディションのデータベースが作成されるこ とになるので(課金対象になるので) 、データの参照/修正作業などが完了した後は、復元した データベースを削除しておくようにしてください。 102 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 3.2 Azure SQL Database での監査(Audit) Azure SQL Database では、監査(Audit)機能が実装されているので、ログインの成功や失敗、 特定のテーブル(顧客情報を格納したテーブルなど)に対するアクセスをログに記録することも簡 単に行うことができます。 Let's Try それでは、これを試してみましょう。Azure SQL Database の監査機能では、アクセス ログの保 存先に、Azure ストレージを利用するので、まずは、ストレージ アカウントを作成しておく必要 があります。 1. ストレージ アカウントを作成するには、次のように[新規]をクリックして、 [すべて]をク リックします。 2 1 1. [Marketplace]ブレードが表示されたら、 [Storage, cache + backup]をクリックして、 [Storage]をクリックします。 2 1 103 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 2. [Storage]ブレードが表示されたら、[作成]ボタンをクリックします。 1 3. [ストレージ アカウント]ブレードが表示されたら、 [ストレージ]に任意のストレージ アカ ウント名を入力(画面は matustorage)、 [価格レベル]でストレージ アカウントの価格を 設定します。 1 3 2 4 画面では、[価格レベル]に一番安い「L ローカル冗長」を選択していますが、L の場合は、 データセンターの場所が West US、1 ヶ月に約 100GB を利用した場合でも約 244.8 円に なるので、1 日であれば 100GB でも 7.9 円です(10GB なら 0.79 円という非常に安価な 設定になっているので、監査を 1 日だけ試すのであれば、ほぼお金がかかりません。試し終わ 104 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 った後は、ストレージ アカウントを削除しておいてください) 。 価格レベルを選択した後は、 [選択]ボタンをクリックします。 なお、 [すべて表示]をクリックした場合は、次のようにすべての価格レベルを参照することが できます。 1 4. 次に[リソース グループ]で、データベースを作成するときに、作成したリソース グループ である「ResourceGroup1」を選択します。 1 2 105 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 5. 次に、 [場所] (データセンターの場所)がリソース グループを作成した場所に自動変換されて いることを確認します(画面は North Central US:米国中北部) 。 1 2 設定を確認したら、 [作成]ボタンをクリックします。これでストレージ アカウントの作成が 開始されます。 6. ストレージ アカウントの作成中は、 [通知]ブレードには、次のように表示されます。 1 ストレージの 作成中 7. ストレージ アカウントの作成が完了すると、次のように[ストレージ アカウントの構成]ブ レードが表示されます(表示されない場合は、 [参照]をクリックして、 [Storage]をクリッ クすれば、ストレージ アカウントの一覧を表示できるので、そこから該当ストレージ アカウ 106 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 ントを選択するようにします) 。 監査の設定 ストレージ アカウントの作成が完了したら、 「監査」を設定します。監査は、SQL サーバー全体に 設定することもできますが、ここでは、特定のデータベース(前の Step で作成した AzureDB デ ータベース)に対してのみ監査を設定してみます。 1. AzureDB データベースに対して、監査を設定するには、次のように[データベースの構成] ブレードを開いて、データベースとストレージ アカウントが同じリソース グループ内に存在 していることを確認します。 データベースとストレージ が同じリソース グループ内 にあることを確認 2. 次に、[データベースの構成]ブレードを下にスクロールして、 [監査]をクリックします。 107 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 [監査]ブレード が表示される 1 3. [監査]ブレードでは、 [接続文字列]をクリックします。 1 ここでは、監査を有効にするには、これらの接続文字列を利用する必要があるという主旨の内 容になっています(詳しくは後述しますが、SQL サーバーに接続するときの名前に、 「.secure」 を付ける必要がある形になります) 。 4. 接続文字列の確認が終わったら、次のように[ストレージ アカウント]で先ほど作成したスト レージ アカウント(画面は matustorage)を選択します。 108 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 1 2 3 [監査オプション]では、 [すべて]をチェックして、 [OK]ボタンをクリックします。これで 監査の設定が有効になり、[すべて]を選択したことで、「データ アクセス」や「データの変 更」、 「スキーマの変更」などすべてを監査(ログ記録)できるようになります。 5. 監査の設定が有効になると、[通知]ブレードで次のように表示されます。 監査の作成が完了した 場合のメッセージ 1 6. 設定した直後は、次のように[監査]には「過去 24 時間に監査イベントはありません」と表 示されます。 109 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 1 以上で監査の設定が完了です。 SQL サーバー名を変更して接続(~.database.secure.windows.net) 次に、SQL サーバーへ接続して(わざとログイン失敗などをして) 、監査ログに操作を記録してみ ます。 1. まずは、SSDT の SQL Server オブジェクト エクスプローラーで、 [SQL Server]を右クリ ックして、 [SQL Server の追加]をクリックします。 1 2 [サーバーへの接続]ダイアログでは、[サーバー名]に、「matusrv1.database.secure .windows.net」のように、 「.secure」を追加したものを指定するようにします。 2. 次に、[パスワード]をわざと間違えて、ログインの失敗をしておきます。 110 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 わざとパスワードを間違えて、 ログイン失敗する 1 2 3. 次に、正しいパスワードを入力して、SQL サーバーへ接続し、 「AzureDB」データベースを右 クリックして、 [新しいクエリ]をクリックします。 2 SELECT ステートメント を記述して実行 1 任意の SQL ステートメントを実行します。 4. ステートメントの実行が完了したら、 [データベースの構成]ブレードに戻ります。 1 今度は、監査に円グラフが表示されているので、これをクリックします(もし円グラフが表示 111 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 されない場合は、SSDT で SQL サーバーに接続するときに「.secure」を追加しているかど うかを再確認してみてください。また、ストレージ アカウントとデータベースが同じリソー ス グループ内にあるかどうかも確認してみてください) 。 5. [監査]をクリックすると、次のように円グラフの詳細を確認することができます。 1 ログイン失敗などに、回数が表示されていることを確認できると思います。 6. [監査]ブレードでは、次のように[構成]をクリックすると、監査の設定を変更することが できます。 [すべて]を監査するように設定していたものを、データの変更など、特定のイベン トのみを監査するように変更することができます。 1 監査の設定 を変更できる 112 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 .secure を必須にする場合(セキュリティが有効なアクセスを必須) 1. [監査]ブレードでは、次のように[セキュリティが有効なアクセス]で[必須]を選択する ことができます。 1 3 2 このように設定した場合は、Azure SQL Database にアクセスする際に、必ず「.secure」を 付けなくてはいけなくなる、という設定にできます。既定値は[省略可能]で、この場合は、 「.secure」を付けなくても接続することができ、その場合は監査ログには残らない、という 動作になります。 2. [セキュリティが有効なアクセス]を[必須]に設定した場合は、次のように「.secure」を 付けない場合は、接続エラーになります。 .secure なし で接続している場合 113 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 Excel 2013 のテンプレート ファイルを開く ~Power Query~ 監査ログは、Excel 2013 を利用して、次のようにグラフィカルに参照することもできます。 これを利用する手順は、次のとおりです。 1. まずは、 [監査]ブレードで、次のように[Excel で開く]をクリックします。 1 2 これにより、 「01-Azure SQL DB Audit Logs Report Template.xlsx」という名前の Excel 2013 形式のテンプレート ファイルのダウンロードが行えます( [保存]ボタンをクリックし ます)。 2. このテンプレートは、Power Query for Excel のクエリ機能を利用しているので、次の URL から Power Query for Excel をダウンロードして、インストールしておく必要があります。 http://www.microsoft.com/ja-jp/download/details.aspx?id=39379 114 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 1 32bit の場合は こちら 2 x64 の場合は こちら 3 3. このテンプレートは、Excel 2013 の Pwer View 機能も利用しているので、Excel 2013 を 起動した後、次のように[オプション]設定で、Power View 機能を有効化しておくようにし ます。 115 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 1 5 2 4 3 4. Power Query for Excel のインストールと Power View の有効化が完了したら、ダウンロー ドした「01-Azure SQL DB Audit Logs Report Template.xlsx」ファイルを Excel 2013 で開きます。 1 [保護ビュー]バーが表示されたら[編集を有効にする]ボタンをクリックします。 116 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 5. 次に、 [POWER QUERY]タブを開いて、 [ブッククエリ]の[ペインを表示する]をクリッ クします。 1 2 3 右側に[ブック クエリ]ペインが表示されたら、 [AuditLogs]という名前のクエリを右クリ ックして、 [編集]をクリックします。 6. これにより、 [クエリ エディター]ウィンドウが表示されるので、 [ホーム]タブで[詳細エデ ィター]をクリックします。 1 2 3 117 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 [詳細エディター]ダイアログが表示されたら、 「yourstorage」と記述してある部分を、監 査を設定したときに選択したストレージ アカウントに変更(筆者の場合は matustorage に 変更)して、[完了]ボタンをクリックします。 7. クエリ エディターに戻ったら、[資格情報の編集]ボタンをクリックします。 1 2 3 これにより、ストレージへのアクセス ダイアログが表示されるので、 [アカウント キー]にス トレージ アカウントにアクセスするためのアカウント キーを入力して、[保存]ボタンをク リックします。 アカウント キーは、次のように[ストレージの構成]ブレードで、 [キー]をクリックするこ とで確認することができます(プライマリ アクセス キーまたはセカンダリ アクセス キーを コピーしたものを上のダイアログで貼り付けます) 。 2 1 118 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 8. 正しいアクセス キーを入力していると、次のように監査ログのデータが読み込まれます。 2 1 ログが 読み込まれる 次に、[閉じて読み込む]ボタンをクリックして、クエリ エディターを終了します。 9. Excel 2013 に戻ると、 [ブック クエリ]の「AuditLogs」クエリが実行されて、ログの読み 込みが行われます(読み込みが完了するまで待ちます) 。 10. 読み込みが完了した後は、次のようにテンプレート ファイルの内のグラフが連動して、グラ フィカルに監査状況を確認できるようになります。 119 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 11. 次のように[Event Type Distribution]タブを開くと、Power View で作られたレポート になっていて、インタラクティブにグラフをクリックしたり、フィルターを設定したりできる ようになっています。 1 なお、Power View は Silverlight 機能を利用しているので、インストールを行っていない場 合は、Silverlight のインストールが促されます 120 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 Azure SDK 2.5 で監査テーブルの参照 Azure SDK 2.5 をインストールしている環境では、Visual Studio のサーバー エクスプローラ ーを利用して、監査によって内部利用されているテーブル(SQLDBAuditLogs2014~ という名 前)の中身を、次のように直接参照することもできます。 監査ログの中身 を直接参照できる Azure SDK 2.5 をインストー ルしていると、サーバー エク スプローラーで、ストレージ アカウントの中身を参照できる Azure SDK 2.5 は、次の URL からダウンロードすることができます。 Announcing Azure SDK 2.5 for .NET and Visual Studio 2015 Preview http://azure.microsoft.com/blog/2014/11/12/announcing-azure-sdk-2-5-for-net-andvisual-studio-2015-preview/ 1 121 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 3.3 Azure SQL Database の DTU(性能指標)、エディション変更 Azure SQL Database では、DTU(Database Throughput Unit:データベース スループット ユ ニット)という単位で、パフォーマンス レベルを定義しています。 エディション パフォーマンス レベル Basic Standard Premium DTU DBサイズ 復元ポイント 5 2GB 7日 料金(東日本の場合) ¥0.78/時間 (~ ¥612/月) S0 10 S1 20 S2 50 S3 (プレビュー) 100 ¥11.64/時間 (~ ¥8,670/月) P1 100 ¥72.05/時間 (~ ¥53,550/月) P2 200 P3 800 ¥2.33/時間 (~ ¥1,734/月) ¥4.66/時間 (~ ¥3,468/月) 250GB 500GB 14日 35日 ¥11.65/時間 (~ ¥8,670/月) ¥144.08/時間 (~ ¥107,202/月) ¥576.30/時間 (~ ¥428,808/月) DTU は、データベース エンジンのパフォーマンスを表す指標で、CPU やメモリ、書き込み速度、 読み取り速度の測定値を組み合わせて(マイクロソフトによって)算出されたものになっています。 単純な DTU 比較で考えると、P1 は「100」、P3 は「800」なので、P3 は P1 よりも 8 倍速く 処理ができる(もちろん処理内容によって変化) 、と捕らえることができるようになっています。 DTU は、マイクロソフトのベンチマーク ツールを利用して、基本的な OLTP ワークロードを実 行・算出した結果になっていて、詳しくは、オンライン ブックの次のページに記載されています。 Azure SQL データベースのサービス階層とパフォーマンス レベル https://msdn.microsoft.com/library/azure/dn741336.aspx このページでは、次のように DTU についてもう少し詳しく記載されています。 122 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 この表では、最大ワーカースレッド数や、最大セッション数、利用したベンチマーク ツールで、1 秒あたりにどれぐらいのトランザクションを実行できたのかなどが記載されています。実際にどれ ぐらいの差が出るのかについては、後述します。 Note: ベンチマークの詳細 DTU で利用したベンチマークの詳細について興味がある方は、次の URL がお勧めです。 Azure SQL データベースのベンチマークの概要 https://msdn.microsoft.com/ja-jp/library/azure/dn741327.aspx Chnnnel 9: Azure SQL Database for Business-Critical Cloud Applications: (04) Azure SQL Database Benchmark http://channel9.msdn.com/series/Azure-SQL-Database-for-Business-Critical-Cloud-Applications/04 123 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 実際のクエリの実行速度例 Azure SQL Database の性能を、筆者がテストした結果になりますが、次のような実行性能を確認 にすることができました。 実際のクエリの実行時間例 クエリ B: CPU 高負荷処理(シングル) クエリ C: 更新系の処理 クエリ A: GROUP BY 集計演算 S0 は大きな 制限を受ける Basic は非常に大きな 制限を受ける S0 は大きな 制限を受ける データ量が 1GB を 超えていたので B は対象外 P3 は非常に速い Premium エディションの 並列クエリの恩恵 並列クエリにはならない CPU 負荷の処理なので P1~P3 では差は出ず Premium エディションの ストレージ性能の恩恵 * 実行時間は、S0 または Basic の値を 100 とした場合の相対値で表示しています。 クエリ A、B、C の 3 種類を試しましたが、Premium エディション(P1、P2、P3)は、さす がに価格相応の非常に良い性能が出ることを確認できました。これに比べて、S3(執筆時点では、 プレビュー版)は、CPU に関しては P1 に近い性能を得られますが、書き込み性能に関しては Premium に大きく劣るということを確認できました。Premium エディションは、高速ストレー ジを利用しているので、書き込みおよび読み取り(ディスク I/O)が伴う処理にも強いことが分か りました。 一方で、Basic や S0 は、非常に大きな性能制限を受けていることが分かりました。特に CPU に 関する制限が大きく、価格が安い分だけ、大きな制限を受けています。 冒頭の DTU 値と、DTU の差(S0 に比べて何倍か)、クエリ A の実行時間および実行時間の差を まとめると、次の表のようになります。 エディション パフォーマンス レベル DTU DTU の差 クエリA 実行時間 実行時間 の差 5 ー ー ー ¥0.78/時間 (~ ¥612/月) S0 10 1 100 1 ¥2.33/時間 (~ ¥1,734/月) S1 20 2倍 60.6 1.7倍 ¥4.66/時間 (~ ¥3,468/月) S2 50 5倍 32.3 3.1倍 ¥11.65/時間 (~ ¥8,670/月) S3 (プレビュー) 100 10倍 16.7 6.0倍 ¥11.64/時間 (~ ¥8,670/月) P1 100 10倍 11.6 8.7倍 ¥72.05/時間 (~ ¥53,550/月) P2 200 20倍 6.3 16.0倍 ¥144.08/時間 (~ ¥107,202/月) P3 800 80倍 1.5 64.7倍 ¥576.30/時間 (~ ¥428,808/月) Basic Standard Premium 料金(東日本の場合) S0 と P3 は、DTU 的には 80 倍の差ですが、クエリ A では 64.7 倍の差を確認することがで き、その他もだいたい DTU 通りの性能差を確認することができました。S3 と P1 は、DTU 的 には同じ値ですが、クエリ C のように更新系の処理の場合には、Premium エディションの高速 124 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 ストレージの恩恵によって、大きな差が出ることが分かりました。 4 億件の SELECT INTO の性能 次のグラフは、筆者のお客様のデータベース(4 億件のデータ、8.2GB 分)を利用して、SELECT INTO ステートメントでデータのコピーを作成したときの実行時間を比較したものです。 2.5倍 性能UP 4.9倍 性能UP 11.5倍 性能UP * 実行時間は、S3 での実行時間を 100 とした場合の相対値で表示 P1 は、S3 よりも 2.5 倍も速く処理することができ、P2 なら 4.9 倍、P3 なら 11.5 倍ものス ピードで処理できることを確認できました。SELECT INTO では、最初のフェーズでコピー元のデ ータを読み取って、読み取った後は、ひたすら書き込んでいく、という動作になるので、Premium エディションの高速ストレージの恩恵をより受けることができます。 また、P2 や P3 では、次のように並列 INSERT 処理も行われるので、より高速に処理ができる ことを確認できました。 P2、P3 では、 並列 INSERT 処理が 行われる 4 億件に対するクエリ性能 次のグラフは、上記と同様、4 億件のデータに対して、集計処理を行うクエリを実行したときの実 行時間を比較したものです。 125 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 2倍 性能UP 8.1倍 性能UP 185倍 性能UP * 実行時間は、P1 での実行時間を 100 とした場合の相対値で表示 このクエリは、CPU 負荷が中心で、実行時間のうちのほとんどが CPU 時間になるのですが、P1 と比べると、P2 は 2 倍、P3 は 8.1 倍で処理できることを確認できました。P2 と P3 では、 並列クエリ(複数の CPU コアを利用したパラレル処理)が効果的に働くことで、このような性能 差になりました。 P1、P2、P3 は、DTU の 100、200(2 倍)、800(8 倍)という数値の通り、このクエリのよ うに CPU 負荷が中心であれば、並列クエリの恩恵によって、2 倍、8 倍で処理ができることが分 かりました。 また、上のグラフでは、P3 でインメモリの列ストア インデックスを作成したときの結果も掲載し ていますが、これを利用すれば 185 倍もの性能向上を実現できています。前の Step で説明した ように、列ストア インデックスは、Premium エディションでのみ利用できる機能で、大量データ に対する集計処理が得意なカラム指向のインデックスです。 以上のように、DTU は、あくまでも性能のおおまかな指標になるものなので、速くしたい処理が CPU 負荷中心なのか、ストレージ処理中心なのか、メモリを多く必要としている処理なのか、など によって、パフォーマンス レベルを選択する形になります。 また、列ストア インデックスを利用するには、Premium エディションが必要であることや、 Standard エディションと Premium エディションでは、性能面だけでなく、復元ポイントが異 なったり(Standard は 14 日、Premium は 35 日) 、ジオ レプリケーション(データセンター をまたがったデータベースの複製)でのアクティブ セカンダリ(読み取り可能なセカンダリ)が作 成できるかどうかなどが変わったりするので、そういった部分も加味して、エディションおよびパ フォーマンス レベルを選択していく形になります。 エディション、パフォーマンス レベルの変更 Azure SQL Database では、パフォーマンス レベルを柔軟に変更することができます(24 時間の 間に最大 4 回まで変更することができます)。 126 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 パフォーマンス レベルの変更は、次のように[データベースの構成]ブレードで、 [価格層]をク リックすることで行えます。 [データベースの構成] ブレード [価格レベルの変更]ブレード が表示される 2 1 変更したい パフォーマンス レベルを選択 3 [価格レベルの変更]ブレードが表示されたら、変更したいパフォーマンス レベルを選択して、 [選択]ボタンをクリックします。 変更にかかる時間は、データベースのサイズによっても異なり、サイズが小さければ数分~数十分 で完了しますが、データベース サイズが大きい場合には数時間かかる場合もあります。変更中は、 次のように[通知]ブレードにメッセージが表示されます。 パフォーマンス レベル の変更中のメッセージ 変更中は 「更新中」と表示される 1 変更中は、 [データベースの構成]ブレードの[価格層]パネルが「更新中...」と表示されますが、 更新が完了すると、次のように新しいパフォーマンス レベルが表示されます。 127 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 変更が完了すると 新しいパフォーマンス レベルが表示される パフォーマンス レベルの変更中は、変更が終わるまで、完全にデータベースが利用できなくなるわ けではなく、変更中でも、ほとんどの時間を継続利用することができます(変更中でも、変更前の パフォーマンス レベルの性能で、通常と同じように操作することができます。課金についても、変 更が完了するまでは、元のパフォーマンス レベルでの課金になります) 。変更の最終フェーズでは、 パフォーマンス レベルの切り替えが行われるのですが、そのタイミングのときのみデータベース へのアクセスができなくなります(接続が切れる形になります) 。 なお、データベースのパフォーマンス レベルは、 [SQL データベース]ブレードでまとめて確認す ることもできます。 データベースの 価格レベルを まとめて確認できる 3 2 1 パフォーマンス レベルの変更や、価格の考え方などに関しては、「SQL Database 料金」ページ (以下の URL)の「FAQ」セクションも参照しておくことをお勧めします。 http://azure.microsoft.com/ja-jp/pricing/details/sql-database/ 使用時間が 1時間に満たない場合 の利用料金について パフォーマンス レベルを 変更するときの実行時間 について 128 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 3.4 Azure SQL Database での性能監視方法 Azure SQL Database では、 「dm_db_resource_stats」という動的管理ビューが用意されてい て、CPU 利用率やディスク I/O、ログ書き込み率、メモリ利用率を確認することができます。 15 秒ごとに監視データ(CPU 利用率やディスク I/O など)が取得されていて、 (現在は)直近1 時間分のデータを参照することができます。これを利用すれば、現在のパフォーマンス レベルで CPU が足りているのか、メモリが十分に足りているのかなどを判断することができるので、大変便 利です。 この「dm_db_resource_stats」ビューの結果は、Azure ポータルで、グラフィカルに確認する こともできます。これを行う手順は、次のとおりです。 1. まずは、 [データベースの構成]ブレードで、[リソース使用率]をクリックします。 [メトリック]ブレード が表示される 1 DTU パーセンテージ が表示される データベース構成 ブレードよりも 1分遅れて表示される DTU パーセンテージ が表示される これにより、 [メトリック]ブレードが表示されて、詳細を確認できるようになります。グラフ 129 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 には、既定で[DTU PERCENTAGE]が表示されますが、これは、 「CPU 利用率、ディスク I/O、ログ書き込み率」のうち、現時点で、最も値が大きかったものが表示されます(詳しく は後述します) 。なお、執筆時点では、 [メトリック]ブレードに表示されるグラフは、 [リソー ス使用率]パネルのグラフよりも 1 分遅れた値になっています。 2. グラフでは、次のようにマウス オーバーすると、その時点での値を確認することもできます。 マウス オーバーすると その時点での値を確認できる グラフのカスタマイズ(CPU 利用率やディスク I/O などを追加) グラフは、CPU 利用率やディスク I/O、ログ書き込み率の値を表示するようにカスタマイズする こともできます。 1. グラフをカスタマイズするには、次のようにグラフを右クリックして、 [グラフの編集]をクリ ックします。 1 グラフを右クリックして、 [グラフの編集]をクリック 2. これにより、 [グラフの編集]ブレードが表示されるので、次のように[時間の範囲]で「過去 1時間」 、[グラフの種類]で「折れ線」を選択します。 130 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 [グラフの編集]ブレード が表示される 1 2 3 4 次に、[CPU percentage]と「Disk I/O percentage」、 「Log I/O percentage」をチェ ックして、 [保存]ボタンをクリックします。 これで、グラフに CPU 利用率やディスク I/O、ログ書き込み率が追加されるようになります。 CPU percentage(CPU利用率) Disk I/O percentage(ディスク I/O) Log I/O percentage(ログ書き込み率) を確認できるようになる DTU PERCENTAGE には、 、CPU percentage(CPU 利用率)と Disk I/O percentage (ディスク I/O) 、Log I/O percentage(ログ書き込み率)のうち、最も値が大きかったも のが表示される形になっています。 131 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 DTU の考え方/ボトルネックの確認方法 前述したように、Azure SQL Database では、DTU という単位で、パフォーマンス レベルが決 定しています。 エディション パフォーマンス レベル DTU 5 1 S0 10 2倍 ¥2.33/時間 (~ ¥1,734/月) S1 20 4倍 ¥4.66/時間 (~ ¥3,468/月) S2 50 10倍 ¥11.65/時間 (~ ¥8,670/月) S3 (プレビュー) 100 20倍 ¥11.64/時間 (~ ¥8,670/月) P1 100 20倍 ¥72.05/時間 (~ ¥53,550/月) P2 200 40倍 ¥144.08/時間 (~ ¥107,202/月) P3 800 160倍 ¥576.30/時間 (~ ¥428,808/月) Basic Standard Premium DTU の差 料金(東日本の場合) ¥0.78/時間 (~ ¥612/月) Basic や S0 は、価格が安い分、大きな制限を受けるエディションになっているので、簡単に CPU 利用率が 100% を使い切っている状態になったりします。 例えば、次のようにクエリを実行すると、CPU 負荷を高くすることができます(Basic エディシ ョンを利用している場合は、実行に 6 分ぐらいかかります) 。 DECLARE @i int = 1 WHILE @i <= 1000 BEGIN DECLARE @kekka float SELECT @kekka = RAND(a) FROM t1 ORDER BY NEWID() SET @i += 1 END このときの状況を dm_db_resource_stats 動的管理ビューで確認すると、次のようになります。 CPU 利用率が 100% ディスク I/O と Log Write は 0 この結果は、Basic エディションの場合のもので、avg_cpu_percent(15 秒間の平均 CPU 利 用率)が 100% を使い切っている状態であることを確認できます。 このように、100%近辺を推移している(リソースを使い切っている)ということは、エディショ 132 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 ン/パフォーマンス レベルを上位のものに変更すれば、性能を上げられる可能性がある、というこ とを意味しています。 なお、動的管理ビューの結果は、次のようにグラフで確認することもできます。 CPU 利用率が 100% CPU 利用率の 最大が 100% 同じものを グラフとして表示 ディスク I/O や Log Write は低い値 対応 対応 dm_db_resource_stats 動的管理ビューの結果 対応 CPU percentage の最大が 100%なので、DTU PERCENTAGE も 100%と表示される形にな っています。DTU PERCENTAGE は、CPU、ディスク I/O、Log Write(Log I/O)のうち、いず れかの最大値を表示しているだけになります(3つのうち、どれかが高い値を示している、という ことを知るための情報になっています)。 同じクエリを Standard エディションの S1 で実行した場合は、次のようになります。 CPU 利用率が 100% 実行時間は、1 分半ぐらいになり、Basic の場合の約 6 分と比べると、非常に速くなっています。 しかし、CPU 利用率が 100%近辺を推移している(リソースを使い切っている)ということは、 133 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 上位のパフォーマンス レベルに変更すれば、さらに性能を上げられる可能性がある、ということを 意味しています。実際に、Premium エディションの P1 で同じクエリを実行した場合には、わ ずか 18 秒程度で処理が完了します。 このように、Azure SQL Database の性能においては、dm_db_resource_stats 動的管理ビュ ーの 4 つの指標(CPU 利用率、ディスク I/O、Log Write、メモリ利用率)に注目して、パフォー マンス レベルを選択していくことができるようになっています。 書き込み中心処理の場合 ~Log Write:ログへの書き込み~ INSERT や UPDATE、SELECT .. INTO、インデックスの再構築など、書き込みが中心となる処 理の場合は、トランザクション ログへの書き込みが行われるので、次のように、avg_log_write _percent(ログ書き込み率)が 100%近辺を推移している(リソースを使い切っている)状態に なります。 Log Write が 100% このように、ログ書き込み率が高い値を示して、性能が出ていない場合には、Premium エディシ ョンへ変更することで、高速ストレージの恩恵を受けることができます。あるいは、この状況のと きに CPU 利用率が高くないという条件が付きますが、データ圧縮(行圧縮やページ圧縮)や列ス トア インデックスを利用して、書き込み量を減らす、という対処方法もあります。 メモリが足りていない場合の処理 ~ディスク I/O~ データがメモリに載りきらず、メモリが足りていない状態の場合には、次のように、avg_disk_io _percent(ディスク I/O)が高い値を推移している状態になります。 Disk IO が高い値 134 メモリが足りなくても 100% にはならない ことがある SQL Server 2014 自習書 No.5 Azure SQL Database 入門 この値は、データ ファイルとの I/O を表していて、メモリが足りていない場合には、データ フ ァイルからの読み取り(I/O)が発生することになります。したがって、このようにディスク I/O が高い値を推移している場合は、メモリが足りていない可能性が高くなります。この場合は、上位 のエディションに変更できれば、利用可能なメモリ量が増えるので、メモリ不足が解消できる可能 性があります。あるいは、この状況のときに CPU 利用率が高くないという条件が付きますが、デ ータ圧縮(行圧縮やページ圧縮)や列ストア インデックスを利用して、データの読み取り量を減ら す、という対処方法もあります。 また、このような場合は、インデックス チューニングを行って、適切なインデックスを作成したり、 データ パーティション(パーティション インデックス)を構成して、特定のパーティション デー タのみを処理するように変更することで、性能を向上させていくこともできます。 SET STATISTICS IO ON でメモリが足りていないかを確実にチェック メモリが足りていないかどうかを確実にチェックしたい場合には、SQL Server でお馴染みの SET STATISTICS IO ON コマンドを実行して、Read-Ahead Reads(先行読み取り数)や Physical Reads(物理読み取り数)を確認するのがお勧めです。 SET STATISTICS IO ON メモリが足りなくて データ ファイル(ストレージ) から読み取った場合に この値がカウントされる Read-Ahead Reads や Physical Reads は、初回の実行時は、仕方のないことですが(メモリ 内のキャッシュに配置) 、同じクエリの 2 回目以降の実行時にも、これらの値が表示されるという ことは、メモリが足りていない状態(メモリ内にないのでストレージからデータを取得しなければ いけなかった)と判断をすることができます。 なお、メモリが足りていなかったとしても(Read-Ahead Reads や Physical Reads が発生し たとしても)、Premium エディションであれば、高速ストレージが搭載されているので、数 GB レベルの読み取り量であれば、数秒で読み取ることが可能です。 並列クエリ ~P2、P3~ V12 の Premium エディションでは、並列クエリ(CPU の複数コアをパラレル利用して、クエ リ処理を高速化できる機能)がサポートされているので、CPU 負荷の高いクエリの場合には、P2 や P3 へ変更することで、性能が向上する可能性があります(執筆時点では、P1 での並列クエリ の実行は確認することができませんでしたが、今後のバージョン アップでは、P1 でも並列クエリ が利用できるようになるかもしれません) 。 135 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 並列クエリになっているかどうかは、次のように実行プランを見ることで確認することができます (Parallelism アイコンがある場合が、並列クエリです)。 P2、P3 の実行プラン(並列クエリ) 並列クエリ(Parallelism) として処理されている Standard エディションの S1 や S2 など、その他のパフォーマンス レベルでの実行プラン 並列クエリ にならない 以上のように、Azure SQL Database では、dm_db_resource_stats 動的管理ビューまたは Azure ポータルの[リソースの利用率]グラフを利用して、CPU 利用率やディスク I/O、ログ書 き込み率、メモリ利用率などを確認して、パフォーマンス レベルを選定していくことができます。 アクセスの多い昼間には、上位のパフォーマンス レベルを利用して、アクセス数の少なくなる深夜 には下位のパフォーマンス レベルに下げる、といった利用方法もとることができます。 136 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 3.5 アラート ルールの設定(しきい値を超えた場合にメール送信) DTU に関しては、アラート ルールを設定して、しきい値を超えた場合にメールを送信する機能が あります。ここでは、CPU 利用率が 80% を超えた場合に、メールを送信するように設定してみ ましょう。 1. アラート ルールを設定するには、次のように[データベースの構成]ブレードで[アラート ルール]をクリックします。 2 1 [アラート ルール]ブレードが表示されたら、 [アラートの追加]をクリックします。 2. 次のように[アラート ルールの追加]ブレードが表示されたら、 [名前]に任意のルール名(画 面は CPU80)を入力します。 1 3 4 2 5 6 137 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 [メトリック]では、どの DTU 項目に対してルールを設定するのかを選択しますが、CPU 利 用率に関するルールを設定するので、「CPU percentage」を選択します。 [条件]では「より大きい」、 [しきい値]で「80」と入力することで、CPU 利用率が 80%よ り大きいというルールを設定できます。 [期間]では「直近 5 分」を選択することで、 「直近の 5 分間の間に、平均 CPU 利用率が 80%より大きかった場合」というルールにできます。 [電子メール サービスと共同管理者]をチェックすることで、ルール(しきい値)を超えた場 合に、メールを送信できるようになります。 設定後、 [OK]ボタンをクリックすれば、アラート ルールの作成が完了です。 3. 作成されたアラート ルールは、次のように[アラート ルール]ブレードに一覧されます。 作成された アラート ルール アラート ルールは、リソース使用率の[メトリック]ブレードでも確認することができます。 [メトリック] ブレード 1 作成された アラート ルール 138 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 4. ルール(しきい値)を超えた場合には、アイコンが次のように表示されます。 ルール違反(しきい値) を超えた場合のアイコン 5. また、ルールを超えた場合は、次のようにメールが届きます。 このように、アラート ルールを設定すると、メールで負荷状況を確認することができるので 大変便利です。 139 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 3.6 DMV(動的管理ビュー)を利用した性能監視 Azure SQL Database では、SQL Server での性能監視でお馴染みの「DMV」(動的管理ビュー: Dynamic Management View)も、主要なものがサポートされています。DMV は、最近のバージ ョンアップで、随時強化されているところになっていて、クエリ チューニングでよく利用する 「dm_exec_query_stats」 (クエリ統計)や、インデックスの使用状況をチェックすることがで きる「dm_db_index_usage_stats」 、Missing インデックス(不足しているインデックス)を チェックすることができる「dm_db_missing_index_~」などを利用できます。これらは、次の ように利用できます。 CPU 利用の多いクエリの参照 CPU 利用の多いクエリは、dm_exec_query_stats ビューを利用して確認することができます。 -- CPU 利用の多いクエリ TOP 10 SELECT TOP 10 total_worker_time / execution_count / 1000.0 AS [平均 CPU 時間(ミリ秒)] , total_elapsed_time / execution_count / 1000.0 AS [平均実行時間(ミリ秒)] , SUBSTRING(text, (statement_start_offset / 2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(text) ELSE statement_end_offset END - statement_start_offset) / 2) + 1) AS sql FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle) ORDER BY 1 DESC インデックス サイズの確認 インデックスのサイズは、dm_db_partition_stats ビューを利用して確認することができます。 140 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 SELECT OBJECT_NAME(ps.object_id) AS objName ,ps.index_id, MAX(i.name) AS idxName ,SUM(ps.used_page_count) * 8 / 1024.0 AS sizeMB ,SUM(ps.row_count) AS rowCnt FROM sys.dm_db_partition_stats ps LEFT OUTER JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id WHERE OBJECTPROPERTYEX(ps.object_id, 'IsUserTable') = 1 GROUP BY ps.object_id, ps.index_id インデックスの断片化状況 インデックスの断片化状況は、dm_db_index_physical_stats を利用して確認することができ ます。 -- t1 テーブルの断片化状況を確認 SELECT OBJECT_NAME(object_id), index_id, avg_fragmentation_in_percent ,index_level, page_count, record_count FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('t1'), NULL, NULL, 'DETAILED') ORDER BY index_id, index_level DESC 141 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 未使用インデックス 一度も利用されたことがないインデックスは、dm_db_index_usage_stats を利用して確認す ることができます。 SELECT o.name AS table_name, i.name AS index_name, * FROM sys.objects o INNER JOIN sys.indexes i ON o.object_id = i.object_id WHERE o.type = 'U' AND i.index_id NOT IN ( SELECT s.index_id FROM sys.dm_db_index_usage_stats s WHERE s.object_id = i.object_id AND i.index_id = s.index_id AND database_id = DB_ID() ) Missing インデックス(不足しているインデックス) Missing インデックス(不足しているインデックス)は、dm_db_missing_index_~ を利用し て確認することができます。 SELECT group_handle, i.*, gs.* FROM sys.dm_db_missing_index_group_stats gs INNER JOIN sys.dm_db_missing_index_groups g ON gs.group_handle = g.index_group_handle INNER JOIN sys.dm_db_missing_index_details i ON i.index_handle = g.index_handle 142 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 Missing インデックスは、SQL Server と同様、実行プランを利用しても、次のように確認するこ とができます。 不足しているインデックス がある場合は、実行プラン に提示される プロシージャ キャッシュの参照 プロシージャ キャッシュは、dm_exec_cached_plans を利用して確認することができます。 SELECT query_plan, size_in_bytes / 8192 AS [使用ページ数], * FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_query_plan (plan_handle) Management Studio を利用している 場合は、query_plan のリンクを クリックすると、グラフィカルに実行 プランを確認可能 143 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 ロック/ブロッキングに関する情報の参照 ロックに関する情報は、dm_tran_locks を利用して確認することができます。 SELECT * FROM sys.dm_tran_locks ロック待ち を確認できる ロックに関しては、sp_lock はサポートされていませんが、sp_who2 がサポートされているの で、次のようにブロッキング情報(どのプロセスによってブロックされているのか)を確認するこ ともできます。 ブロッキング情報(どの SPID に よってブロックされているのか) を確認できる なお、現在の ”待機” の情報(ロック待ちだけでなく、さまざまな内部的な待機に関する情報)を 確認することができる dm_os_waiting_tasks に関しては、執筆時点では、Standard エディシ ョンの S2 レベル以上であれば、利用できることを確認できました。 SELECT * FROM sys.dm_os_waiting_tasks また、”待機” に関する統計情報(Wait Stats)についても、S2 レベル以上であれば、次のように 利用できることを確認できました。 Wait Stats(待機の統計情報)の参照 -- Wait Stats(待機に関する情報の取得) SELECT * FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC 144 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 その他、S2 レベル以上であれば、次のようにパフォーマンス カウンターの情報(dm_os_ performance_counters)や、メモリ使用状況の情報(dm_os_buffer_descriptors)につい ても、利用できることを確認できました。 パフォーマンス カウンター情報の参照 -- パフォーマンス カウンター情報の取得 SELECT * FROM sys.dm_os_performance_counters メモリ使用量の内訳(オブジェクトごとのメモリ使用量) -- オブジェクトごとのメモリ使用量の内訳 SELECT DB_NAME (b.database_id), OBJECT_NAME(p.object_id), p.index_id ,COUNT(*) As 使用ページ数 FROM sys.allocation_units a INNER JOIN sys.partitions p ON a.container_id = p.hobt_id INNER JOIN sys.dm_os_buffer_descriptors b ON a.allocation_unit_id = b.allocation_unit_id WHERE b.database_id = DB_ID() GROUP BY b.database_id, p.object_id, p.index_id ORDER BY 使用ページ数 DESC 145 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 このように、Azure SQL Database では、データベースを監視するための主要な DMV(動的管理 ビュー)がサポートされているので、SQL Server を監視するのと同じように監視することができ ます(クエリ チューニングやインデックス チューニング、トラブル シューティングを行っていく ことができます) 。 なお、DMV やインデックス チューニング、ロック(ブロッキング)などについては、SQL Server 2012 の自習書シリーズの「監視ツールの基本操作」や、「インデックスの基礎とメンテナンス」、 「ロックと読み取り一貫性」編で詳しく説明しているので、こちらもぜひご覧いただければと思い ます。 146 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 3.7 sqlcmd ユーティリティからの接続/操作 Azure SQL Database は、通常の SQL Server と同様、sqlcmd ユーティリティから接続するこ ともできます。これも試してみましょう。 1. まずは、 [スタート]ボタンを右クリックして、コマンド プロンプトを起動します。 1 2. 次のようにコマンドを記述して「AzureDB」データベースに接続します。 sqlcmd /S "Azure SQL Database サーバー名" /U "管理者アカウント名" /P パスワード /d 接続したいデータベースの名前 /N /S オプションで Azure SQL Database の SQL サーバーの名前、/U と /P オプションで 管理者アカウントの名前とパスワード、/d オプションでデータベースの名前を指定します。 /N オプションは必須ではありませんが、暗号化接続を行うためのオプションになっています。 なお、古いバージョンの SQL Server(2008 や 2008 R2)を利用している場合は、/U オプ ションで指定する管理者アカウント名を「アカウント名@サーバー名」(matumoto@matu srv1 のように matusrv1.database.windows.net の先頭部分付けて指定する必要があり ます。また、/N オプションは SQL Server 2008 R2 から利用できるようになったものなの で、SQL Server 2008 の sqlcmd ユーティリティを利用している場合は、暗号化接続を行う ことができません。 3. 続いて、次のように CREATE TABLE ステートメントを記述して「t2」テーブルを作成して 147 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 みます(sqlcmd ユーティリティでは、go を記述することで Transact-SQL ステートメント を実行することができます) 。 CREATE TABLE test2 ( a int PRIMARY KEY, b int) go INSERT INTO test2 VALUES(1, 111) INSERT INTO test2 VALUES(2, 222) go SELECT * FROM test2 go このように、Azure SQL Database でも、通常の SQL Server と同様の操作で、sqlcmd ユ ーティリティから接続することができます。 148 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 3.8 SQL Server との主な相違点 Azure SQL Database は、Version 12 からは、SQL Server とほぼ完全な互換性があるので、 違いを探すことのほうが難しいぐらいですが、主な相違点には、次のようなものがあります。 SQL Server 認証のみがサポートされる TCP 1433 ポートでのアクセスのみがサポートされる Azure SQL Database ファイアウォールによる IP アドレス レベルのアクセス制限を 設定できる データベースの照合順序の既定値が「SQL_Latin_General ~」に設定される。日本語 を格納するには、SQL Server の既定値である「Japanese_CI_AS」などを利用する USE ステートメントによるデータベース移動がサポートされていない 日付時刻が UTC(協定世界時)で格納される。日本時間(UTC +9 時間)へ変更するに は、DATEADD(hour, 9, SYSUTCDATETIME()) と記述する READ_COMMITED_SNAPSHOT とスナップショット分離レベルが有効化されている バックアップが自動で取得されているので、BACKUP ステートメントはサポートされて いない フルテキスト検索や、SQL Server Agent サービス、分散クエリ、分散トランザクショ ン、グローバル一時テーブルなどがサポートされていない その他の SQL Server との違いや、Azure SQL Database での Transact-SQL の互換性につい ては、オンライン ブックの以下の場所に詳しく記載されています。 Azure SQL データベース Transact-SQL リファレンス https://msdn.microsoft.com/ja-jp/library/ee336281.aspx 149 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 また、Version 12 に関する最新情報は、次の URL に詳しく記載されています。 What's new in the Latest SQL Database Update V12 (preview) http://azure.microsoft.com/ja-jp/documentation/articles/sql-database-preview-whats-new/ ガイドラインと制限事項 また、オンライン ブックの以下のトピックも目を通しておくことをお勧めします(執筆時点では、 V12 には対応していないのですが、多くの部分が V12 にも当てはまります)。アプリケーション を開発する上でのガイドラインもあるので、一読しておくことをお勧めします。 Azure SQL データベースの一般的なガイドラインと制限事項 https://msdn.microsoft.com/ja-jp/library/ee336245.aspx 150 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 Azure SQL データベースのセキュリティのガイドラインと制限事項 https://msdn.microsoft.com/ja-jp/library/ff394108.aspx Azure SQL データベースのリソース制限(V12 からは多くの制限が緩和されています) https://msdn.microsoft.com/library/azure/dn338081.aspx Azure SQL Database の調整 https://msdn.microsoft.com/ja-jp/library/azure/dn338079.aspx 151 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 今後の情報源 今後も Azure SQL Database は、随時バージョン アップされて、機能強化/サポートされるもの が増えていく予定がありますので、そういったアップデート情報/最新情報については、以下の Microsoft Azure チームのブログが役立つと思います。 Microsoft Azure チームのブログ http://azure.microsoft.com/blog/ 152 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 STEP 4. 既存の SQL Server 環境の Azure SQL Database への移行 この STEP で は、 既存 の SQL Server 環 境の デー タ ベー スを Azure SQL Database 上へ移行する方法を説明します。 この STEP では、次のことを学習します。 Azure SQL Database への移行方法の概要 SSIS ウィザードでのテーブル データの移行 bcp コマンドでのテーブル データの移行 DAC Framework(DAC Fx)を利用したデータベースの移行 スクリプト生成ウィザードによるデータベースの移行 SQL サーバー間のデータベース移行(Azure データセンター間でのコピー) SSDT の便利な機能(スキーマ比較、リファクタリング) 153 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 4.1 Azure SQL Database への移行方法の概要 既存の SQL Server 環境(オンプレミス)のデータベースを、Azure SQL Database 上へ、デー タを含めて丸ごと移行するには、次の 5 つの方法があります。 1. Integration Services(SSIS ウィザードまたは SSIS パッケージ)を利用する 2. bcp コマンドを利用する 3. DAC Framework(DAC Fx)の .bacpac を利用する 4. Management Studio の「スクリプト生成ウィザード」を利用する(データを移行するための INSERT ステートメントも生成可能) 5. CodePlex サイトで提供されている「SQLAzureMW」 (SQL Database Migration Wizard) ツールを利用する(データの移行には内部的に bcp コマンドが利用される。マイクロソフト のサポート対象外のツールとなる) これらの方法のうち、もっとも簡単、かつパフォーマンス良く移行することができるのが 3つ目の DAC Framework(DAC Fx)の .bacpac を利用する方法です。この .bacpac は、SQL Server 2014 の CU5 以上、または DAC Fx の最新版をインストールことで利用することができます(詳 しい利用方法は後述します) 。 データベース丸ごとではなく、特定のテーブルのデータのみを移行したい場合に便利なのが(筆者 もよく利用するのが)、Integration Services の SSIS ウィザードです(SQL Server 2008 以 降の SSIS ウィザードが Azure SQL Database に対応しています)。ただし、データ サイズが数 GB レベルになる場合には、SSIS ウィザードだと bcp コマンドや .bacpac に比べて転送速度が 遅いので、お勧めではありません。SSIS ウィザードは、数万件程度のデータの場合には、お勧め の移行方法になります。 また、さらに少ないデータ量(数千件レベル)の場合には、Management Studio の「スクリプト 生成ウィザード」もお勧めです。これは、すべてのオブジェクト(テーブル作成の CREATE TABLE だけでなく、ビュー作成の CREATE VIEW、権限設定の GRANT、データを追加するための INSERT ステートメントなど)をスクリプト化することができる機能です。スクリプト生成ウィザ ードは、SQL Server 2008 R2 以降の Management Studio が Azure SQL Database に対応し ています。 なお、上記の 3 つの方法(DAC Framework、SSIS ウィザード、スクリプト生成ウィザード)で は暗号化接続を利用することができますが、bcp コマンドと SQLAzureMW では、暗号化接続を 利用することができないので、お勧めではありません。 これらの移行方法をまとめると、次の表のようになります。 154 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 移行方法 転送速度 Integration Services (SSIS ウィザード) 遅い bcp コマンド DAC Fx (.bacpac) スクリプト生成 ウィザード SQLAzureMW v5.15 (CodePlex) 暗号化接続 速い 速い ○ × データベース ユー ザーとオブジェクト 権限の転送 備考 × SSIS ウィザードは、既定では、 PRIMARY KEY がとれてしまう。 特定のテーブル データのみを移行す る場合に便利 × SSIS 同様、特定のテーブル データ のみを移行する場合に便利。 テーブル作成の CREATE TABLE は 別途スクリプト生成する必要あり ○ ○ ただし、パスワード の再設定が必要 ○ 非常に遅い 速い ○ ただし、包含データ ベース ユーザーに は非対応 × × データベースを丸ごと移行するには 一番お勧めの方法。 SQL Server 2014 CU5 以降または 最新の DAC Fx をインストールする ことで V12 に対応。 内部的には bcp が利用されている。 テーブルだけでなく、制約やイン デックス、ビュー、ストアド プロ シージャ、トリガー、ユーザー、オ ブジェクト権限なども移行可能 Management Studio の機能。 SQL Server 2008 R2 以降で利用可 能。データを追加する INSERT ス テートメントをスクリプト化できる データ量が数千件程度の場合にお勧 め クラスター化インデックスがない テーブルに、自動的にクラスター化 インデックスを作成してしまう。 V12 ではクラスター化インデックス は不要であるにも関わらず、自動的 に付与してしまう。 内部的には bcp が利用されている。 サポート対象外となる データベース ユーザーやオブジェクト権限の転送、備考に記載した細かい内容については、この後 の Step で詳しく説明していきます。 155 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 4.2 移行元となるデータベース(NorthwindJ)の作成 まずは、Azure SQL Database 上へのデータベース移行を試すために、SQL Server 上に移行元と なるデータベースを作成します。ここでは、サンプル スクリプト内の「NorthwindJ.sql」ファイ ルを実行して、データベース(NorthwindJ)を作成します。 Let's Try それでは、データベースを作成してみましょう(SQL Server 2014 を例に説明します) 。 1. まずは、Management Studio を起動するために、[スタート]メニューの[すべてのプログ ラム]から[Microsoft SQL Server 2014]の[SQL Server 2014 Management Studio] をクリックします。 1 2. 起動後、 [サーバーへの接続]ダイアログが表示されたら、 [サーバー名]に SQL Server の名 前を入力して、 [接続]ボタンをクリックします。 1 SQL Server の名前を入力 2 156 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 3. 接続完了後、次のようにツールバーの[ファイルを開く]ボタンをクリックします。 1 2 サンプル スクリプトを 解凍した場所を展開して NorthwindJ.sql を選択 3 これにより、 [ファイルを開く]ダイアログが表示されるので、サンプル スクリプトを解凍し たフォルダーを展開して、 「NorthwindJ.sql」ファイルを選択し、 [開く]ボタンをクリック します。 4. 次のようにデータベースを作成するためのスクリプトが表示されるので、ツールバーの[実行] ボタンをクリックして、スクリプトを実行します。 2 1 5. データベースを作成 するためのスクリプト が表示される 数秒後に実行が完了して、次のように画面下に「クエリが正常に実行されました」と表示され ることを確認します。 157 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 1 「クエリが正常に実行されました」 と表示されることを確認 以上でデータベースの作成が完了です。 NorthwindJ データベースの構成 NorthwindJ デ ー タ ベ ー ス は 、 Microsoft Access 2003 に 付 属 の サ ン プ ル デ ー タ ベ ー ス 「Northwind」を SQL Server 上へアップサイズし、この自習書の手順を試すために、一部のデ ータを加工したものです。具体的なスキーマ構成は次のとおりです。 158 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 このデータベースは、商品の販売管理を題材として、 「商品」や「商品区分」 、 「受注」、 「受注明細」 テーブルなどが格納されています。また、このデータベース内には、次のように sqllogin1 と sqllogin2 という名前のデータベース ユーザー(およびログイン アカウント)を作成して、オブ ジェクト権限を設定しています。 sqllogin1 と sqllogin2 ログイン アカウントの作成 sqllogin1 と sqllogin2 データベース ユーザーの作成 sqllogin1 に対して、 商品テーブルの SELECT 権限を REVOKE n_view1 に対する SELECT 権限を GRANT n_proc1 に対する EXEC 権限を GRANT sqllogin2 を db_datareader ロールへ追加 sqllogin1 は、商品テーブルの SELECT はできず、n_view1 ビューの SELECT はできるよう にオブジェクト権限を設定していますが、これは次のように EXECUTE AS USER を利用して、 確認することができます。 EXECUTE AS USER = 'sqllogin1' -- sqllogin1 として以下のステートメントを実行 SELECT * FROM 商品 SELECT * FROM n_view1 REVERT -- sqllogin1 のシミュレートを終了 sqllogin1 として 以下のステートメントを実行する sqllogin1 には 商品テーブルを SELECT する権限 が付与されていないのでエラーになる 159 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 4.3 SSIS ウィザードでのテーブル データの移行 Azure SQL Database では、通常の SQL Server と同様、Integration Services(SSIS)の SQL Server インポートおよびエクスポート ウィザード(以降、SSIS ウィザードと記述)を利用して データのインポート/エクスポートを行うことができます。 SSIS ウィザードは、データベース丸ごとではなく、特定のテーブルのデータのみを移行したい場 合に便利で、筆者もよく利用しています。ただし、データ サイズが数 GB レベルになる場合は、 SSIS ウィザードは、bcp コマンドや DAC Fx(.bacpac)に比べて転送速度が遅くなるので、お 勧めではありません。SSIS ウィザードは、数万件程度のデータを移行する場合にお勧めの方法に なります。 Let's Try それでは、これを試してみましょう。ここでは、SSIS ウィザードを利用して、NorthwindJ デー タベース内の「商品」テーブルを、Azure SQL Database 上に移行してみましょう。 1. SSIS ウィザードを利用するには、次のように Management Studio で「NorthwindJ」デ ータベースを右クリックして、 [タスク]メニューの[データのエクスポート]をクリックしま す(これは、SQL Server 2008 以降の Management Studio であれば、同じように操作す ることができます)。 1 2 これにより、[SQL Server インポートおよびエクスポート ウィザード]が起動するので、 [次へ]ボタンをクリックします。 2. 次の[データ ソースの選択]ページでは、次のように[データ ソース]で「SQL Server Native 160 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 Client 11.0」を選択します。 1 2 3 4 5 [サーバー名]に移行元となる SQL Server の名前を入力(画面は MATUMO) 、[認証]で [Windows 認証を使用する]を選択、 [データベース]で「NorthwindJ」を選択して、 [次 へ]ボタンをクリックします。 3. 次の[変換先の選択]ページでは、次のように[変換先]で「.Net Framework Data Provider for SqlServer」を選択します。 1 2 暗号化接続(SSL による ネットワーク暗号化)を 行う場合に True へ設定 6 5 3 4 7 161 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 [Encrypt]で「True」を選択、 [Data Source]に Azure SQL Database の SQL サーバ ー名(画面は matusrv1.database.~)、[Initial Catalog]に移行先となるデータベース の名前(画面は AzureDB) 、 [User ID]に管理者アカウントの名前(画面は matumoto) 、 [Password]に管理者アカウントのパスワードを入力して、[次へ]ボタンをクリックしま す。 [Encrypt]の「True」は必須ではありませんが、これを指定することで、暗号化接続を 利用することができます。 なお、SQL Server 2008 や 2008 R2 などの古いバージョンの SSIS ウィザードを利用して いる場合は、管理者アカウントの名前を「アカウント名@サーバー名」(matumoto@matu srv1 のように matusrv1.database.windows.net の先頭部分付けて指定する必要があり ます。 Note: 「SQL Server Native Client 11.0」は使用することも可能(暗号化接続なし) SQL Server 2014 の SSIS ウィザードを利用している場合は、[変換先]に、次のように「SQL Server Native Client 11.0」を選択しても、データの移行を行うことができます。 ただし、この接続方法では、暗号化接続を利用することができないので、本文中の手順のように、「.Net Framework Data Provider for SqlServer」を利用することをお勧めします。 4. 次の[テーブルのコピーまたはクエリの指定]ページでは、 [1 つ以上のテーブルまたはビュー からデータをコピーする]を選択して、[次へ]ボタンをクリックします。 162 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 1 2 5. 次の[コピー元のテーブルおよびビューを選択]ページでは、 [変換元]で「[dbo].[商品]」を チェックして、 「商品」テーブルをコピーするようにします。 1 2 ここでは、 [マッピングの編集]ボタンをクリックすると、次のように[列マッピング]ダイア ログが表示されて、移行先で作成されるテーブルの列名などを確認/変更することができます。 163 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 1 PRIMARY KEY は 作成してくれない 作成されるテーブルは、既定では PRIMARY KEY が付与されていませんが、今回は何も変 更せずに、 [コピー元のテーブルおよびビューを選択]ページへ戻って[次へ]ボタンをクリッ クし、次のページへ進みます。 Note: PRIMARY KEY を付与する場合 [列マッピング]ダイアログでは、次のように PRIMARY KEY を付与したテーブルに変更することも可能で す。 PRIMARY KEY を記述 1 構成が変わった ので警告が表示される 2 なお、既に、移行先にテーブルが作成済みである場合は、そのテーブルを選択して、データの移行のみを行うこ ともできます。 6. 次の[パッケージの保存および実行]ページでは、 [すぐに実行する]を選択して、 [次へ]ボ タンをクリックします。 164 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 1 2 7. 次の[ウィザードの完了]ページでは、 [完了]ボタンをクリックすると、データの転送(エク スポート)が開始されます。 1 8. データの転送中は、次のように進行状況が表示されます。 165 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 Note: Version 11 の場合のエラー Azure SQL Database の Version 11 を利用している場合には、データの転送中に、次のようにエラーが発生し ます。 Version 11 では、データを追加するために、クラスター化インデックスが必須になるので、本文中の手順のよう に PRIMARY KEY を作成していない場合は、このようにデータの転送エラーになります。これを回避するに は、前述の Note「PRIMARY KEY を付与する場合」のように、テーブルの作成時に PRIMARY KEY を付与す るようにします。 166 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 次のように、[状態]がすべて「成功」と表示されれば、データの転送が完了です。 1 2 [閉じる]ボタンをクリックしてウィザードを終了します。 9. Azure SQL Database の SQL サーバーに接続して、転送されたテーブルを参照すると、次 のようにデータが正しく移行されていることを確認できます。 このように、Azure SQL Database でも、通常の SQL Server と同様の操作で、Integration Services を利用したデータ転送を行うことができます。なお、Integration Services につい ては 、SQL Server 2012 の 自習 書 シリ ー ズの 「 Integration Services 入 門」 お よび 「Integration Services 応用」編で詳しく説明しているので、こちらもぜひご覧いただけれ ばと思います。 167 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 4.4 bcp コマンドでのテーブル データの移行 Azure SQL Database では、通常の SQL Server と同様、bcp コマンドを利用して、データの転 送(エクスポートやインポート)を行うこともできます。 Let's Try それでは、これも試してみましょう。 1. まずは、 [スタート]ボタンを右クリックして、コマンド プロンプトを起動します。 1 2. コマンド プロンプトが起動したら、次のように bcp コマンドを記述して、移行元となる SQL Server 上の NorthwindJ データベース内の「商品」テーブルをエクスポート(出力) します。 bcp "NorthwindJ.dbo.商品" out "C:\temp\商品.bcp" /S "SQLServerの名前" /T /n /S オプションで移行元となる SQL Server の名前を指定(画面は MATUMO) 、/T オプシ ョンを利用することで Windows 認証で接続、/n オプションを利用することで、ネイティ 168 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 ブ フォーマットのファイルとして、テーブル データを出力することができます(後述のテキ スト ファイルよりも高速にデータを処理できるようになります)。 bcp でデータのインポート 次に、エクスポートしたファイル(C:\temp\商品.bcp)を Azure SQL Database 上の「商品」 テーブルにインポートしてみましょう。 1. まずは、Azure SQL Database 上の「AzureDB」データベースへ接続して、次のように TRUNCATE TABLE ステートメントを実行し、 「商品」テーブルのデータをすべて削除してお きます。 TRUNCATE TABLE 商品 2. 次に、コマンド プロンプトを起動して、次のように bcp をコマンドを記述します。 bcp "AzureDB.dbo.商品" in "C:\temp\商品.bcp" /S "サーバー名.database.windows .net" /U "管理者アカウント名" /P パスワード /n bcp コマンドで out を指定していた部分を in に変更して、/S オプションで移行先となる Azure SQL Database の SQL サーバーの名前を指定(画面は matusrv1.database.win dows.net)、/U オプションで管理者アカウントの名前、/P オプションで管理者アカウント 169 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 のパスワードを指定します。 なお、SQL Server 2008 や 2008 R2 などの古いバージョンの bcp コマンドを利用してい る場合は、管理者アカウント名を「アカウント名@サーバー名」 (matumoto@matusrv1 形 式)で指定する必要があります。 3. bcp コマンドの実行後、Azure SQL Database の SQL サーバーに接続して、転送されたテ ーブルを参照すると、次のようにデータが正しく移行されていることを確認できます。 このように bcp コマンドを利用すれば、データの転送(エクスポートとインポート)を簡単 に行うことができます。ただし、bcp コマンドは、暗号化接続には対応していないので、暗号 化接続を行いたい場合には、SSIS ウィザードや、後述の DAC Framework(.bacpac) 、スク リプト生成ウィザードなどを利用するようにします。 Note: 大量データの場合 ~/F、/L オプション~ bcp コマンドでは、既定ではデータのインポートは、1つのトランザクションとして扱われるので、インポート 中にエラー(接続エラーなど)が発生した場合には、インポート処理全体が取り消されます(例えば、100 万件 のインポートで、99 万件までインポートした時点でエラーが発生すれば、0 件もインポートされていない状態に 戻ってしまいます) 。 このような状態を回避するには、次のように /F と /L オプションを利用して、行数を指定してインポートを行 うという方法があります。 bcp "AzureDB.dbo.商品" in "C:\temp\商品.bcp" /S "~" /U "~" /P ~ /n /F 1 /L 100000 bcp "AzureDB.dbo.商品" in "C:\temp\商品.bcp" /S "~" /U "~" /P ~ /n /F 100001 /L 200000 このように実行すれば、最初の実行で 1 行目から 10 万行目まで、2 回目の実行で 10 万1行目から 20 万行目 までを実行する、といった使い方ができるので、もしも、インポート エラーが発生した場合にも、失敗したとこ ろを再実行するだけで良いようになります。 170 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 Note: bcp でテキスト ファイルに出力 ~/c オプション~ bcp コマンドでは、/n の代わりに /c オプションを付けることで、タブ区切りのテキスト ファイルとしてデー タをエクスポートすることもできます。これは、次のように記述します。 bcp "NorthwindJ.dbo.商品" out "C:\temp\商品.txt" /S "SQLServer の名前" /T /c 実行後、Windows エクスプローラーで C:\temp フォルダーを開いて、作成されたファイルを開くと、データ がテキスト ファイルとしてエクスポートされていることを確認することができます。 タブ区切りの ファイル /S オプションで指定する SQL Server の名前を、Azure SQL Database の SQL サーバー名に変更して、/T オプションを /U と /P オプションに変更して管理者アカウントのユーザー名とパスワードに変更すれば、 Azure SQL Database 上のデータをテキスト ファイルとして出力する目的として利用することもできます。 ただし、暗号化接続には対応していないので、暗号化接続を利用する場合には、前述の SSIS ウィザードを利用 することをお勧めします(SSIS ウィザードは、テキスト ファイルにエクスポートすることも簡単に行うことが できます)。 171 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 4.5 DAC Framework(DAC Fx)によるデータベースの移行 既存の SQL Server 環境のデータベースを、Azure SQL Database 上へ、丸ごと移行する方法と して、一番お勧めなのが DAC(Data-tier Application:データ層アプリケーション)Framework (以降、DAC Fx と記述)の .bacpac を利用する方法です。この .bacpac は、SQL Server 2014 の CU5 以上、または DAC Fx の最新版をインストールことで利用することができます。 DAC Fx の最初のバージョンは、SQL Server 2008 R2 から提供されて、このときは .dacpac と いうファイル形式(先頭が b ではなく d)で、スキーマのみ(テーブル構造やビュー、ストアド プロシージャなどのオブジェクト定義のみ)をエクスポートおよびインポートできるものでした (データの移行を行うことはできませんでした) 。 これに対して、SQL Server 2012 以降では、DAC Fx のバージョンが 2 へ上がって(通称 DAC Fx v2 となって) 、データを移行することもできるようになりました。これは .bacpac というフ ァイル形式になります。 執筆時点(2015 年 1 月)での最新版の DAC Fx(Azure SQL Database の V12 に対応した DAC Fx)は、次の URL(SQL Server Data Tools チームの Blog)からダウンロードするか、SQL Server 2014 の CU5 以降を適用することで利用することができます。 http://blogs.msdn.com/b/ssdt/archive/2014/12/18/sql-server-database-toolingpreview-release-for-the-latest-azure-sql-database-update-v12-preview.aspx 1 172 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 Let's Try それでは、これを試してみましょう。 1. DAC Fx によるデータベースの移行を行うには、次のように Management Studio のオブジ ェクト エクスプローラーで、移行元の SQL Server に接続して、移行元となるデータベース (画面は NorthwindJ)を右クリックし、 [タスク]メニューの[データベースを Windows Azure SQL データベースに配置]をクリックします。 1 2. ウィザードが開始されたら、 [次へ]ボタンをクリックして、次のページへ進みます。 1 173 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 3. 次の[配置の設定]ページでは、「接続」ボタンをクリックします。 1 2 3 [サーバーへの接続]ダイアログが表示されたら、 [サーバー名]に移行先となる Azure SQL Database の SQL サーバー名、 [認証]で「SQL Server 認証」を選択、 [ログイン]と[パ スワード]にに管理者アカウントの名前とパスワードを入力して、 [オプション]ボタンをクリ ックします。 4. [接続プロパティ]タブでは、 [暗号化接続]をチェックすることで、暗号化接続を有効化する ことができます。 1 2 3 設定後、 [接続]ボタンをクリックします。 174 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 5. [配置の設定]ページに戻ったら、[新しいデータベース名]に移行先となるデータベース名 (既定は移行元と同じ名前。画面は NorthwindJ)を任意で設定します。 1 2 内部的には .bacpac ファイル が作成される(後述) 3 [Windows Azure SQL データベースの設定]では、データベースのエディション(Basic、 Standard、Premium)などを設定しますが、このウィザードによって作成されたデータベー スは課金対象になるので、テスト中は Basic を選択することをお勧めします(もちろん、上位 のエディションを選択することで、データ移行をすばやく行えるようになりますが、この自習 書の手順を試すには、一番安い Basic エディションで大丈夫です) 。 なお、SQL Server 2014 の CU5 を適用していない場合は、エディションの選択で「Web」 と「Business」しか選択することができません。この2つは古い料金体系(容量課金)のと きのもので、どちらも 2015 年 9 月に中止される予定のエディションになっています。現時 点では、Web や Business エディションを利用することも可能なので、CU5 を適用していな い環境の場合は、とりあえず一番安い Web エディションの 1GB を選択しておき、データベ ースの移行が完了した後に、エディションを Basic や Standard に変更する、ということも できます。 設定後は、 [次へ]ボタンをクリックして、次のページへ進みます。 6. 次の[概要]ページでは、 [完了]ボタンをクリックすることで、データベースの移行が開始さ れます。 175 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 1 7. データベースの移行中は、次のようにページが表示されます。 1 データベース内の各オブジェクト(テーブルやビュー、ストアド プロシージャ、トリガー)ご とに結果が表示され、すべての[結果]が「成功」と表示されれば、移行が完了です。最後に [閉じる]ボタンをクリックして、ウィザードを終了します。 Basic エディションの場合は、実行(移行)に数分ぐらいかかりますが、上位のエディション に変更することで、実行時間を短縮することができます。 176 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 Note: 古い DAC Fx でクラスター化インデックスがないテーブルがある場合 SQL Server 2014 の CU5 以上を適用していない Management Studio の場合は、クラスター化インデックス がないテーブルがある場合は、次のようにエラーが発生して、次へ進むことができません。 都道府県テーブルに クラスター化インデックスがない のでエラーになっている Azure SQL Database は、Version 11 のときは、クラスター化インデックスが必須だったので、DAC Fx もク ラスター化インデックスが存在するかどうかをチェックしていて、存在しない場合は、このようにエラーを返す ように作られていました。今回の NrothwindJ データベース内の「都道府県」テーブルにはクラスター化インデ ックスを作成していないので、古い DAC Fx を利用している場合には、このようなエラーを確認できます。 Azure SQL Database は、最新の Version 12 では、クラスター化インデックスが必須ではなくなったので、最 新の DAC Fx(CU5 以降)であれば、クラスター化インデックスがなかったとしてもエラーにはなりません。 Note: フルテキスト インデックスを作成している場合 Azure SQL Database ではフルテキスト インデックスがサポートされていないので、SQL Server 側でフルテ キスト インデックスを作成している場合は、次のようにエラーが発生します。 フルテキスト インデックスは 未サポートなのでエラーとなる このエラーを回避するには、SQL Server 側でフルテキスト インデックスを削除しておくようにします。SQL Server のサンプル データベースである「AdventureWorks」を Azure SQL Database 上に移行して、テス トを行いたい場合には、事前にフルテキスト インデックスを削除しておくことで移行できるようになります。 177 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 8. ウィザードが完了したら、Azure SQL Database の SQL サーバーに接続して、移行された テーブルを参照すると、次のようにデータが正しく移行されていることを確認できます。 データも移行でき ていることを確認 テーブルが作成 されていること を確認 ログイン アカウントのパスワードの再設定 DAC Fx では、Azure SQL Database 上にログイン アカウントが既に存在している場合は、その ログイン アカウントを利用し、存在していない場合は、新しく(データベース ユーザーに対応し た)ログイン アカウントを自動作成します。 ログイン アカウント が自動作成されている しかし、作成されたログイン アカウントのパスワードは、移行元で設定されたものとは異なるもの に設定されてしまっています(.bacpac は、内部的にはスクリプト生成を行っているのですが、パ スワードに関しては、セキュリティ強化の目的で、ランダムな英数字が再設定されるようになって います) 。 したがって、データベースの移行後は、ログイン アカウントのパスワードを再設定しておくように 178 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 します。これを行うには、Azure SQL Database 側で、master データベースに接続して、次の ように ALTER LOGIN ステートメントを実行します。 -- Azure SQL Database 側のログイン アカウントのパスワードの再設定 ALTER LOGIN sqllogin1 WITH PASSWORD = 'P@ssword' ALTER LOGIN sqllogin2 WITH PASSWORD = 'P@ssword' 1 master データベースを 右クリックして [新しいクエリ]をクリック sqllogin1 でログインして動作の確認 次に、sqllogin1 ログイン アカウントで Azure SQL Database にログインして、オブジェクト 権限の設定が正しく動作するか(権限の移行が成功しているか)どうかを確認してみましょう。 1. まずは、次のようにクエリ エディター上で、任意の場所を右クリックして、 [接続]メニュー から[接続の変更]をクリックします。 1 2. [サーバーへの接続]ダイアログが表示されたら、 [サーバー名]に Azure SQL Database の SQL サーバーの名前、 [ログイン]に「sqllogin1」、 [パスワード]に「P@ssword」と入力 (前の手順で再設定したパスワードを入力)して、 [オプション]ボタンをクリックします。 179 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 2 4 3 5 6 [接続プロパティ]では、 [データベースへの接続]で「NorthwindJ」データベースを選択し て、NorthwindJ データベースに接続します。 3. 接続後、クエリ エディターで次のように入力して「商品」テーブルを参照してみます。 SELECT * FROM 商品 結果はエラーとなり、商品テーブルのデータを参照することはできません(sqllogin1 は、商 品テーブルに対する SELECT 権限が付与されていないので、参照することができません) 。 4. 次に、n_view1 ビューを参照してみます。 SELECT * FROM n_view1 180 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 このビューに対しては SELECT 権限が付与されているので、データを参照することができて います。 以上のように、DAC Fx を利用すれば、既存の SQL Server 環境のデータベースを簡単に Azure SQL Database 上に移行することができます。ログイン アカウントのパスワードの再設定のみが 必要になりますが、テーブルやデータだけでなく、制約やビュー、ストアド プロシージャ、トリガ ー、データベース ユーザー、オブジェクト権限、インデックスなどもそのまま移行することができ ます。 Contained Database(包含データベース)を利用している場合 前の手順では、ログイン アカウントに紐付いたデータベース ユーザーを利用している場合を想定 していましたが、Azure SQL Database は、Contained Database(包含データベース)にも対 応しています。Contained Database は、SQL Server 2012 から提供された機能で、ログイン ア カウントとはマッピングする必要がない(紐付ける必要がない)、データベース内にのみユーザー (包含ユーザー)を作成することができる機能です。SQL Server 2012/2014 を利用している場 合は、次のように設定することで、Contained Database 機能を利用できるようになります。 Contained Database 機能の有効化 データベースの作成 データベースを Contained Database に設定 Contained Database 機能を有効化すると、次のように CREATE USER ステートメントで WITH PASSWORD を付けることで、 「データベース内にのみ存在するユーザー」=「包含データ ベース ユーザー」を作成することができます。 データベース内にのみ存在する 包含データベース ユーザーを作成 [プロパティ]では パスワードを持つ SQL ユーザー と表示される 181 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 これらの Contained Database に関しても、サンプル スクリプトを解凍したフォルダーを展開し て、「NorthwindJ_CDB.sql」ファイルを選択し、[開く]ボタンをクリックすれば、試すことが できるので、ぜひ試してみてください(NorthwindJ_CDB という名前のデータベースが作成され て、その中に sqluser1 および sqluser2 という包含データベース ユーザーが作成されます)。 Contained Database に設定したデータベースも、DAC Fx(.bacpac)を利用して、同じように Azure SQL Database 上にデータベースを移行することができます。 ただし、移行後は、包含データベース ユーザーのパスワードがリセットされてしまうので(新しい パスワードが再設定されてしまうので)、次のように ALTER USER ステートメントを実行して、 パスワードを再設定するようにします。 ALTER USER sqluser1 WITH PASSWORD = 'P@ssword' ALTER USER sqluser2 WITH PASSWORD = 'P@ssword' 1 2 包含データベースに接続して ALTER USER ステートメントを実行 して、包含データベース ユーザーの パスワードを再設定する このように DAC Fx(.bacpac)は、Contained Database(包含データベース)にも対応してい ます(後述のスクリプト生成ウィザードは、Contained Database には対応していません)。 Note: SQL Server 2012/2014 側で包含データベース ユーザーに変更する方法 SQL Server 2012/2014 では、sp_migrate_user_to_contained システム ストアド プロシージャを利用するこ とで、ログイン アカウントとマッピングされたデータベース ユーザーを、Contained User(包含データベース ユ ーザー)に変更することもできます。 EXEC sp_migrate_user_to_contained N'DBユーザー名', N'keep_name', N'disable_login' これを利用すれば、既存のデータベース ユーザーを簡単に包含データベース ユーザーに変更できるので便利です。 データベース ユーザーの数が多い場合には、オンライン ブックの以下のセクションで、すべてのデータベース ユーザ ーをまとめて変更するためのスクリプトも提供されているので、こちらも参考になると思います。 Contained Database(包含データベース) https://msdn.microsoft.com/ja-jp/library/ff929071.aspx 182 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 DECLARE @username sysname ; DECLARE user_cursor CURSOR FOR SELECT dp.name FROM sys.database_principals AS dp JOIN sys.server_principals AS sp ON dp.sid = sp.sid WHERE dp.authentication_type = 1 AND sp.is_disabled = 0; OPEN user_cursor FETCH NEXT FROM user_cursor INTO @username WHILE @@FETCH_STATUS = 0 BEGIN EXECUTE sp_migrate_user_to_contained @username = @username, @rename = N'keep_name', @disablelogin = N'disable_login'; FETCH NEXT FROM user_cursor INTO @username END CLOSE user_cursor ; DEALLOCATE user_cursor ; すべてのデータベース ユーザーを まとめて包含データベース ユーザーへ 変更してしまうスクリプト (オンライン ブックより引用) DAC Fx でエクスポートを利用する場合(.bacpac ファイルの作成) 前の手順では、DAC Fx を利用して、直接データベースを移行する手順を説明しましたが、この方 法では、内部的には次のように .bacpac というファイルが作成されています。 移行元のデータベースをエクスポート(.bacpac ファイルの作成) 移行先に .bacpac ファイルをインポートしてデータベースを作成 この .bacpac ファイルの作成(エクスポート)やインポートは、個別に実行することもできるの で、これも試してみましょう。 1. .bacpac ファイルの作成(エクスポート)を行うには、次のように移行元の SQL Server に 接続して、該当データベース(NorthwindJ)を右クリックし、 [タスク]メニューの[データ 層アプリケーションのエクスポート]をクリックします。 1 データ層アプリケーションは、DAC(Data-tier Application)の日本語訳です。エクスポート 183 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 メニューのほかに、 [データ層アプリケーションの抽出]や、 [データ層アプリケーションの配 置]、 [データ層アプリケーションのアップグレード]など、似たようなメニューがありますが、 これらは、DAC Fx の V1(最初のバージョン)相当の .dacpac(スキーマのみ)を作成(抽 出)したり、配置(スキーマを配置)したりするものになっていて、.bacpac(データを含ん だもの)とは異なるメニューになるので注意してください。 2. ウィザードが開始されたら、 [次へ]ボタンをクリックして、次のページへ進みます。 1 3. 次の[エクスポート設定]ページでは、 [ローカル ディスクに保存]を選択して、任意の場所 に .bacpac ファイルへのパスを記述します(画面は、C:\temp\NorthwindJ.bacpac)。 1 2 184 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 Note: .bacpac を Azure ストレージ(BLOB ストレージ)上に保存する場合 [エクスポートの設定]ページでは、次のように Azure ストレージ上を指定して、.bacpac ファイルを保存す ることもできます。 1 2 3 4 ストレージ アカウント のコンテナーを指定 5 4. 次の[概要]ページでは、設定内容を確認して[完了]ボタンをクリックします。 185 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 1 これでエクスポートが実行されます。 5. エクスポートが完了すると、次のように、すべての[結果]が「成功」と表示されます。 1 これで、.bacpac ファイルの作成が完了です。 186 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 Note: .bacpac ファイルの中身 .bacpac ファイルは、内部的には zip 形式で圧縮されたファイルになるので、次のように拡張子を .zip に変 更すれば。中身を確認することもできます。 ファイルの拡張子を .zip に変更すると 中身を確認できる データベース内の テーブルごとにフォルダー が作成されている bcp で出力された ファイルを確認できる。 これはテーブル データを bcp コマン ドでネイティブ フォーマットで出力 したものになっている .bacpac では、内部的には、bcp コマンドを利用してデータがエクスポートされています(ネイティブ フォー マットが利用されています)。 スキーマ(オブジェクトの定義)は、次のように XML 形式のファイルとしてエクスポートされています。 model.xml ファイル にスキーマ(オブジェクト定義) が格納されている 「n_proc1」 ストアド プロシージャ の定義 187 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 Management Studio がない場合 ~SqlPackage コマンド~ ここまでの手順は、Management Studio を利用して DAC Fx(.bacpac)を利用する手順を説明 しましたが、Management Studio がない環境の場合でも、DAC Fx をインストールしておけば、 SqlPackage コマンドを利用して、.bacpac ファイルを作成することができます。 DAC Fx の執筆時点での最新版(SQL Database V12 に対応したもの)は、SQL Server Data Tools チームの以下の Blog からダウンロード/インストールすることができます。 http://blogs.msdn.com/b/ssdt/archive/2014/12/18/sql-server-database-toolingpreview-release-for-the-latest-azure-sql-database-update-v12-preview.aspx 1 DAC Fx をインストールすると、次のフォルダーに SqlPackage コマンドがインストールされて います(32 ビット版をインストールしている場合は、Program Files に (x86) を付けたパスに なります) 。 C:\Program Files\Microsoft SQL Server\120\DAC\bin 188 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 SqlPackage コマンドは、コマンド ライン ツールなので、コマンド プロンプトを起動して、次 のように記述することで、.bacpac ファイルをエクスポートすることができます(32 ビット版を インストールしている場合は cd で移動するパスを変更してください) 。 cd C:\Program Files\Microsoft SQL Server\120\DAC\bin sqlpackage.exe /Action:Export /ssn:"移行元のSQL Serverの名前" /sdn:"データベース名" /su:"ログインアカウント名" /sp:"パスワード" /tf:".bacpacファイルへのパス" /Action オプションで Export を指定することで、.bacpac ファイルのエクスポートを行うこと ができ、/ssn オプションで移行元となる SQL Server の名前を指定(画面は MATUMO) 、/sdn オプションで移行元となるデータベースの名前(画面は NorthwindJ) 、/su と /sp オプション で SQL Server に接続するためのログイン アカウントとパスワード、/tf オプションで .bacpac ファイルへのパスを記述します。 エクスポートした .bacpac ファイルを、Azure SQL Database 上にインポートするには、次のよ うに SqlPackage コマンドを実行します(以下のように作成したデータベースは、Standard エ ディションの S0 として作成されるので、インポートの完了後は、必要に応じてエディションを変 更したり、テストが終わったらデータベースの削除をし忘れないように注意してください) 。 sqlpackage.exe /Action:Import /tsn:"SQLサーバー名.database.windows.net" /tdn:"移行先となるデータベース名" /tec:"True" /tu:"管理者アカウント名" /tp:"管理者アカウントのパスワード" /sf: ".bacpacファイルへのパス" インポートするときは、/Action オプションで Import と指定し、/tsn オプションで移行先と なる Azure SQL Database の SQL サーバーの名前を指定、/tdn オプションで移行先となるデ ータベースの名前(新しくデータベースが作成されます) 、/tu と /tp オプションで Azure SQL Database の管理者アカウントの名前とパスワード、/sf オプションで .bacpac ファイルへのパ 189 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 スを記述します。/tec:"True" は、暗号化接続を行うための指定です。 インポートが完了した後は、ログイン アカウントのパスワードを再設定すれば、データベースの移 行が完了です。 Management Studio で .bacpac ファイルのインポート Management Studio を利用して、Azure SQL Database 上に .bacpac ファイルをインポート する場合は、次のように操作します。 1. まずは、移行先となる Azure SQL Database の SQL サーバーに接続して、 [データベース] フォルダーを右クリックして、 [データ層アプリケーションのインポート]をクリックします。 1 2. ウィザードが開始されたら、 [次へ]ボタンをクリックして、次のページへ進みます。 1 190 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 3. 次の[インポートの設定]ページでは、 [ローカル ディスクからインポート]を選択して、イ ンポートしたい .bacpac ファイルを選択します。 1 2 4. 次の[データベースの設定]ページでは、 [新しいデータベース名]で移行先となるデータベー ス名(既定は移行元と同じ名前)を任意で設定します。 1 2 3 [Windows Azure SQL データベースの設定]では、データベースのエディション(Basic、 191 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 Standard、Premium)などを設定しますが、このウィザードによって作成されたデータベー スは課金対象になるので、テスト中は一番安い Basic エディションを選択することをお勧め します。 5. 次の[概要]ページでは、 [完了]ボタンをクリックすることで、インポートを開始できます。 1 6. インポート中は、次のページが表示されます。 7. 次のように、すべての[結果]が「成功」と表示されれば、インポートが完了です。 192 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 1 インポートが完了した後は、ログイン アカウントのパスワードを再設定すれば、データベー スの移行が完了です。 このように、.bacpac ファイルを利用すれば、エクスポートとインポートを利用して、既存の SQL Server 環境のデータベースを、Azure SQL Database 上へ、データを含めて丸ごと簡 単に移行することができます。 Note: Azure ポータルで Azure ストレージ上の .bacpac をインポート .bacpac ファイルを Azure ストレージ上にエクスポートしている場合は、Azure ポータルを利用して、次のよ うに[サーバーの構成]ブレードからインポートすることもできます。 1 [サーバーの構成] ブレード [データベースのインポート]をクリックすると、次のように[データベースのインポート]ブレードが表示さ れるので、[ストレージ]で .bacpac ファイルが格納されているストレージ アカウントのコンテナーを指定し ます。 193 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 ストレージ アカウントの選択 コンテナーの選択 コンテナー内 .bacpac ファイル を選択する 後は、 [価格レベル]と[データベース名]を指定して、管理者アカウントの名前とパスワードを入力すれば、イ ンポートが開始されます。 価格レベル の選択 データベース名 を入力 管理者アカウント の名前とパスワード を入力する インポート中は、次のように[インポート/エクスポート履歴]をクリックすると、進行状況を確認できるように なっています。 194 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 データベース サイズが大きい場合の考慮事項 .bacpac では、データベース サイズが大きい場合には、次のように行うのがお勧めです。 .bacpac ファイルをエクスポート(ローカル ドライブに保存) エクスポートした .bacpac ファイルを Azure ストレージ上にコピー(AzCopy ツー ルなどを利用) Azure ポータルを利用して、.bacpac ファイルをインポートする お勧めする一番の理由は、Azure ポータルを利用することで、インポートの進捗状況を確認できる 点です(前ページの Note に記載)。進捗状況が確認できれば、あとどれぐらいで完了するのかを 推測することができるので、移行計画を立てやすくなります。実際の移行にあたっては、事前に、 実際のデータベース サイズよりも、小さいサイズのものを作成して(データを一部削除するなどし て) 、移行(インポート)にどれぐらいの時間がかかるのかを計測しておくことをお勧めします。 参考までに、弊社のお客様データ(実際のデータを 1 億件スケールに変更したもの)を移行したと きの結果が次のとおりです。 移行元の データベース データベース サイズは 2GB テーブル サイズ が 2GB データ件数が 1億件 データベースのサイズは 2GB で、この中に、テーブル(テーブル名を伏せるために t1 に変更) のサイズ 2GB、データ件数 1 億件のものが入っています。 このデータベースに対して、Management Studio(CU5)から .bacpac ファイルのエクスポー トを実行して、そのときにかかったおおよその時間は 26 分でした(利用したハードウェアは、Core i7 2600K 3.4GHz、32GB メモリ、SSD Crucial MX550 1TB) 。 .bacpac ファイルのサイズは、240MB になりました(8 分の 1 ぐらいにまで圧縮されていまし 195 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 た) 。 .bacpac ファイルの サイズは 240MB この 240MB の .bacpac ファイルを、AzCopy ツールを利用して、Azure ストレージ上にコピ ーしたときのスピードは、1 分 16 秒でした。 1分 16秒で コピーが完了 *利用したネットワーク環境 NTTフレッツ光(マンションタイプ) Upload 約 30Mbps(約 3.75MB/sec) AzCopy ツールは、ローカルのファイルを Azure ストレージ上にコピーできるツールですが、監 査の最後の項で説明した「Azure SDK 2.5」をインストールしていれば、利用することができま す。AzCopy は、次のように利用できます。 cd C:\Program Files (x86)\Microsoft SDKs\Azure\AzCopy azcopy /Source:コピーしたい.bacpacファイルを格納しているローカルフォルダーへのパス /Dest:https://ストレージアカウント名.blob.core.windows.net/コンテナ名 /Pattern:".bacpacファイルの名前" /DestKey:ストレージアカウントのアクセスキー /Source オプションでコピーしたい .bacpac ファイルを格納しているローカルフォルダーへの パス、/Pattern オプションで .bacpac ファイルの名前を指定して、/Dest オプションにストレ ージ アカウントのコンテナーへの URL、/DestKey オプションにストレージ アカウントへのア クセス キーを入力すれば、ファイルをコピーすることができます。コンテナーの URL やアクセス キーは、次のように Azure ポータルで確認することができます。 コンテナーの URL アクセスキー 196 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 このように、Azure ストレージ上にコピーした .bacpac ファイルは、Azure ポータルを利用し て、次のようにインポートを行うことができます。 インポート中は、次のように進行状況を確認することができます。 インポートの 進行状況を確認できる インポートの開始時刻 と終了時刻を確認できる インポートが完了すると Completed と表示される 画面は、P2 でインポートを行った場合ですが、37 分 19 秒で完了することを確認できました。 197 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 なお、同じ .bacpac ファイルを、P3 でインポートを行った場合には、14 分 46 秒で完了するこ とも確認できました。インポートの注意点としては、インポート中も、データベースの課金が発生 するという点になります(インポートの最初のフェーズでデータベースが作成されるので、そこか ら課金対象になります)。もちろん、上位のエディション/パフォーマンス レベルを選択すること で、インポート時間を大きく短縮することができるので、それとのトレードオフになります。 インポート後、インデックスの再構築をするのがお勧め インポートが完了した後は、インデックスの再構築をしておくことがお勧めになります。元々の環 境では、1 億件 2GB のテーブル サイズでしたが、インポートによって、次のように 3.3GB に 大きくなりました。 このようにサイズが大きいままだと、クエリ性能に影響が出るので(読み取り I/O 数が増えること になるので) 、インデックスの再構築を行って、サイズを元の大きさに戻しておくことがお勧めにな ります。インデックスの再構築は、次のように ALTER INDEX ステートメントで行えます。 ALTER INDEX インデックス名 ON テーブル名 REBUILD 198 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 インデックスの再構築後は、次のようにサイズが 2GB(移行元と同じサイズ)になることを確認 できました。 このように、.bacpac ファイルをインポートした後は、インデックスを再構築しておくことをお勧 めします。 199 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 4.6 スクリプト生成ウィザードによるデータベースの移行 次に、Management Studio の「スクリプト生成ウィザード」を利用したデータベースの移行方法 を説明します。この方法は、データ量が少ない場合(数千件レベルの場合)にお勧めです。スクリ プト生成ウィザードを利用すると、すべてのオブジェクト(テーブル作成の CREATE TABLE だ けでなく、ビュー作成の CREATE VIEW、権限設定の GRANT、データを追加するための INSERT ステートメントなど)をスクリプト化することができます。スクリプト生成ウィザード は、SQL Server 2008 R2 以降の Management Studio であれば、Azure SQL Database に対 応したスクリプトを生成することができます。 Let's Try それでは、スクリプト生成ウィザードを利用して、データベースを移行してみましょう。 1. スクリプト生成ウィザードは、次のように Management Studio のオブジェクト エクスプロ ーラーで、移行元の SQL Server に接続して、移行元となるデータベース(NorthwindJ) を右クリックし、 [タスク]メニューの「スクリプトの生成」をクリックすることで起動するこ とができます。 1 2. ウィザードが開始されたら、 [次へ]ボタンをクリックして、次のページへ進みます。 200 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 1 3. 次の[オブジェクトの選択]ページでは、 「データベース全体とすべてのデータベース オブジ ェクトのスクリプトを作成」を選択して、 [次へ]ボタンをクリックします。 1 1 4. 次の[スクリプト作成オプションの設定]ページでは、 「詳細設定」ボタンをクリックします。 201 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 1 5. これにより、次のように[スクリプト作成の詳細オプション]ダイアログが表示されます。 3 2 1 4 このダイアログでは、 [データベース エンジンの種類に対応したスクリプト]で「Azure SQL 202 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 Database」を選択することで、Azure SQL Database 上で実行可能なスクリプトを生成でき るようになります。また、 [スクリプトを作成するデータの種類]では「スキーマとデータ」を 選択することで、データを追加するための INSERT ステートメントを生成できるようになり ます。 その他、 [スクリプトの照合順序]で「True」、 [オブジェクト レベル権限のスクリプトを作成] で「True」、 [トリガーのスクリプトを作成]で「True」を選択しておくようにします。 [スク リプトの照合順序]を「True」へ変更することで照合順序(COLLATE 句)のスクリプト化、 [オブジェクト レベル権限のスクリプトを作成]を「True」へ変更することでオブジェクト 権限(GRANT/REVOKE/DENY)とデータベース ユーザー(CREATE USER)のスクリ プト化、 [トリガーのスクリプトを作成]を「True」へ変更することでトリガーをスクリプト 化(CREATE TRIGGER)することができるようになります。 設定後、 [OK]ボタンをクリックします。 Note: SQL Server 2008 はスクリプト生成ウィザードに未対応 SQL Server 2008 を利用している場合は、スクリプト生成ウィザードが Azure SQL Database に対応していな いので、SQL Server 2008 以前のバージョンを利用している場合には、CodePlex サイトで提供されている 「SQL Database Migration Wizard」(http://sqlazuremw.codeplex.com/)ツールなどを利用すること で、スクリプト生成を行うことができます。 6. [スクリプト作成オプションの設定]ページへ戻ったら、「新しいクエリ ウィンドウに保存」 を選択して、[次へ]ボタンをクリックします。 1 2 これで生成されたスクリプトを新しいクエリ エディター上へ表示することができます。 203 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 7. 次の[概要]ページでは、設定内容を確認して[次へ]ボタンをクリックします。 1 これでスクリプトの生成が実行されます。 8. スクリプトの生成中は、次のページが表示されます。 次のように、すべての[結果]が「成功」と表示されれば、スクリプト生成が成功です。 204 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 1 データベース内の各オブジェクト(テーブルやビュー、ストアド プロシージャ、トリガー)ご とに結果が表示され、すべての[結果]が「成功」と表示されれば、スクリプト生成が成功で す。最後に[完了]ボタンをクリックして、ウィザードを終了します。 9. 終了後、クエリ エディター上にスクリプトが生成されていることを確認できます。 ユーザーの作成 ロールへの追加 テーブルの作成 各種データベース オブジェク トを作成するためのスクリプ トが生成されている このようにスクリプト生成ウィザードを利用すれば、テーブルを作成するための CREATE 205 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 TABLE ステートメントだけでなく、INSERT ステートメントやビュー(CREATE VIEW) 、 ストアド プロシージャ(CREATE PROCEDER) 、トリガー(CREATE TRIGGER) 、データ ベース ユーザー(CREATE USER)などもスクリプト化してくれるので大変便利です。 また、次のようにオブジェクト権限(GRANT/REVOKE/DENY)の設定もスクリプト生成 してくれています。 ビューの作成 オブジェクト権限の設定も スクリプト化されている INSERT ステートメントも スクリプト化されている 生成されたスクリプトは、このまま残しておいてください(クエリ エディター ウィンドウを 閉じないでください) 。 Note: Contained Database(包含データベース)には未対応 スクリプト生成ウィザードは、Contained Database(包含データベース)には対応していません。Contained Database を設定して、包含データベース ユーザーを作成している場合は、次のようにスクリプトの生成のフェ ーズでエラーとなってしまいます。 Contained Database を利用している場合は、DAC Fx(.bacpac)で移行することをお勧めします。 206 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 Azure SQL Database 側でログイン アカウントの作成 次に、移行先となる Azure SQL Database の SQL サーバー側にログイン アカウントを作成し ます。スクリプト生成ウィザードでは、ログイン アカウントの生成は行われないので、手動で移行 先に作成しておく必要があります。 1. ログイン アカウントを作成するには、Management Studio(または SSDT)を新しく起動し て、Azure SQL Database の SQL サーバーに接続します。 1 2 2. 次に、オブジェクト エクスプローラーで master データベースを右クリックして、 [新しい クエリ]をクリックし、master データベースに接続します(クエリ エディターを表示しま す) 。 3 1 3. クエリ エディター が起動する master データベースを 右クリックして 「新しいクエリ」をクリック 2 「master」へ 接続している クエリ エディターが表示されたら、次のように CREATE LOGIN ステートメントを実行し て、ログイン アカウントを作成します。 CREATE LOGIN sqllogin1 WITH PASSWORD = 'P@ssword' CREATE LOGIN sqllogin2 WITH PASSWORD = 'P@ssword' 207 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 2 CREATE LOGIN の実行 1 「master」デー タベースに接続 Azure SQL Database に接続してスクリプトの実行 1. 次に、スクリプト生成ウィザードによって生成されたスクリプトを Azure SQL Database に 対して実行しますが、スクリプトが記述されたクエリ エディターへ戻って、次のように[クエ リ]メニューの[接続]から「接続の変更」をクリックします(これで接続先を変更すること ができます)。 1 2. [データベース エンジンへの接続]ダイアログが表示されたら、 [サーバー名]に移行先とな る Azure SQL Database の SQL サーバー名、 [認証]で「SQL Server 認証」を選択、 [ロ グイン]と[パスワード]にに管理者アカウントの名前とパスワードを入力して、[オプショ ン]ボタンをクリックします。 208 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 3 1 4 2 5 6 [接続オプション]タブでは、 [データベースへの接続]に、移行先となるデータベース名を指 定します(画面は AzureDB を利用) 。 3. 接続後、次のようにツールバーの「!実行」ボタンをクリックして、スクリプトをすべて実行 します。 2 1 「AzureDB」 データベースに接続 実行後、次のように[クエリは正常に実行されました]と表示されれば、データベースの移行 が完了です(なお、データベースのエディションが Basic の場合には、このスクリプトの実行 に 10 分ぐらいの時間がかかるので、少し待っていてください。上位エディションを利用して いる場合には、実行時間を短縮することができます)。 209 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 1 「クエリが正常に実行されました」 と表示されれば、移行が完了 Note: Version 11 ではスクリプトの実行時にクラスター化インデックスがないとエラーになる Azure SQL Database の Version 11 のときは、クラスター化インデックスがないテーブルがあった場合に、ス クリプト生成したものを実行しようすると、次のようにエラーになりました。 V11 では、クラスター化イン デックスがないと INSERT が できないので、エラーとなって しまう NorthwindJ データベースでは、 「都道府県」テーブルにクラスター化インデックスを作成していないのですが、 このテーブルに対するデータの INSERT ステートメントで、エラーになっています。 スクリプト生成ウィザードは、あくまでも既存のオブジェクトをもとにスクリプトを生成しているだけなので、 既存のテーブルにクラスター化インデックスが作成されていない場合は、Azure SQL Database 上でもそのよう に作成しようとします(全く同じようにオブジェクトを作成しようとします)。最新版の Version 12 では、クラ スター化インデックスがなくてもエラーにはならないのですが、Version 11 を利用している場合は、クラスター 化インデックスを別途作成しなければなりません。 210 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 なお、前述の Note で紹介した CodePlex サイトで提供されている SQL Database Migration Wizard (SQLAzureMW)ツールを利用した場合は、クラスター化インデックスが作成されていないテーブルに対して は、自動的にクラスター化インデックスを付与するスクリプトを生成する機能が提供されています。しかし、 V12 対応の執筆時点での最新版である「SQLAzureMW v5.15」でも、この動作は変わっておらず、意図的にク ラスター化インデックスを外しているテーブルがあったとすると、それに対してもクラスター化インデックスを 付与してしまう、という動作になってしまいるので、利用には注意してください。 ■ クラスター化インデックスが作成されていないテーブルをリストアップする もし、SQL Server 上で、クラスター化インデックスが作成されていないテーブルをリストアップしたい場合に は、次のようにクエリを実行することで確認することができます。 USE NorthwindJ SELECT DISTINCT OBJECT_NAME(object_id) FROM sys.indexes WHERE index_id = 0 AND OBJECTPROPERTY(object_id, 'IsUserTable') = 1 クラスター化インデックス が作成されていないテーブ ルをリストアップ indexes システム ビューは、データベース内のすべてのインデックス情報を参照できるビューです。WHERE 句の「index_id = 0」によってヒープ(クラスター化インデックスが作成されていないテーブルのデータ格納状 態)を取得することができます(クラスター化インデックスが作成されている場合は、index_id = 1 と設定され ます) 。 object_id には、テーブルのオブジェクト ID が格納されているので、OBJECT_NAME 関数でテーブル名を取 得、OBJECTPROPERTY 関数で IsUserTable(ユーザー テーブル)かどうかをチェックしています(1 なら ユーザー テーブル、0 ならシステム テーブル) 。 移行されたデータの確認 スクリプトの実行が完了した後は、データベースが正しく移行されているかどうかを確認しておき ましょう。 1. まずは、次のように記述して、 「商品」テーブルのデータが移行できていることを確認してみま す。 SELECT * FROM 商品 211 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 データも移行でき ていることを確認 テーブルが作成 されていること を確認 2. 次に、オブジェクト エクスプローラーを展開して、トリガーやビュー、ストアド プロシージ ャなどが移行できていることも確認します。 トリガー ビュー ストアド プロシージャ * 日本語版の SQL Server 2014 CU5 では、オブジェクト エクスプローラー が V12 に完全に対応していないので、列の一覧や、インデックスを GUI から 確認することができませんが、CU6 以降で対応予定です。 3. 次に、データベース ユーザーへ設定したオブジェクト権限が移行できていることを確認する ために、次のように database_permissions ビューを参照します。 SELECT OBJECT_NAME(major_id) AS オブジェクト名 212 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 ,USER_NAME(grantee_principal_id) AS ユーザー名 ,permission_name, state_desc FROM sys.database_permissions WHERE USER_NAME(grantee_principal_id) = 'sqllogin1' sqllogin1 に対して、 n_proc1 に対する EXEC 権限が GRANT n_view1 に対する SELECT 権限が GRANT されていることを確認できる sqllogin1 データベース ユーザーに対して設定された権限(n_view1 に対する SELECT 権限と n_proc1 に対する EXEC 権限)も正しく移行できていることを確認できます。 このように、スクリプト生成ウィザードを利用すれば、既存の SQL Server 環境のデータベ ースを簡単に Azure SQL Database 上に移行することができます。テーブルやデータだけで なく、制約やビュー、ストアド プロシージャ、トリガー、データベース ユーザー、オブジェ クト権限、インデックスなどもそのまま移行することができます。 スクリプト生成ウィザードは、データ量が少ない場合(数千件レベルの場合)にお勧めの移行 方法です。データ量が多い場合(データベース サイズが数 GB レベルになる場合)には DAC Fx(.bacpac) 、データ量がそれほど多くない場合(数万件レベルの場合)で、かつ特定のテ ーブル データのみを移行したい場合には、SSIS ウィザードがお勧めの移行方法になります。 213 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 4.7 Azure SQL Database の SQL サーバー間でのデータベースの移行 Azure SQL Database の SQL サーバー上に作成したデータベースは、別のサーバーにも簡単に 移行することができます。これには、次の 4 つの方法があります。 データベースのコピー(CREATE DATABASE .. COPY OF) DAC Fx(.bacpac)を利用する Integration Services や bcp コマンドによるデータの複製を利用する スクリプト生成ウィザードを利用する 2~4 つ目は、SQL Server からのデータベースの移行手順で説明したものと同じ方法なので、ここ では、データベースのコピーを利用した SQL サーバー間でのデータベースの移行方法を説明しま す。 データベースのコピー(CREATE DATABASE .. COPY OF) Azure SQL Database 上のデータベースは、次のように CREATE DATABASE ステートメント で COPY OF を付けることで、簡単にコピーすることができます。 CREATE DATABASE コピー先のデータベース名 AS COPY OF コピー元のデータベース名 1 2 DB コピーで 全く同じように 複製された DB 214 DB コピーの 実行 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 CREATE DATABASE に続けて、コピー先となるデータベース名(画面は AzureDB_Copy)を 指定して、AS COPY OF でコピー元のデータベース名(画面は AzureDB)を指定するだけで、 データベースの内容を丸ごと複製することができます。 このコピーでは、エディションも同じものがコピーされるので、コピー元が Basic なら Basic、 Standard の S0 なら S0 が作成されます。コピーしたデータベースは、課金対象になるので、テ ストで行っている場合は、確認が終わったら削除しておくようにしてください。 SQL サーバー間でのデータベースのコピー AS COPY OF では、次のように Azure SQL Database の SQL サーバーの名前を指定して、コ ピーを行うことができます。 CREATE DATABASE コピー先のデータベース名 AS COPY OF SQLサーバーの名前.コピー元のデータベース名 AS COPY OF で SQL サーバーの名前(画面は matusrv1)を記述することで、別の SQL サー バー上にデータベースのコピーを作成することができます。 なお、最新版の Version 12 では、SQL サーバーが配置されているデータセンターの場所に関係 なく、データベースのコピーを行うことができますが、Version 11 を利用している場合には、同 じデータセンター内に配置された SQL サーバー間でのみデータベースのコピーを行うことがで きます(Version 11 では、データセンターが異なるとコピーができませんでした) 。 215 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 コピーした後のログイン アカウントとデータベース ユーザーの再マッピング DAC Fx(.bacpac)では、移行先にログイン アカウントがなければ、自動的に作成してくれる、 という動作でしたが(パスワードの再設定は必要)、データベースのコピーの場合は、ログイン ア カウントの作成は行ってくれません。したがって、コピー元でログイン アカウントに紐付いた、デ ータベース ユーザーを作成している場合には、次のように CREATE LOGIN ステートメントを利 用して、コピー先にもログイン アカウントを作成しておく必要があります。 -- コピー先で、master に接続してログイン アカウントを作成する CREATE LOGIN sqllogin1 WITH PASSWORD = 'P@ssword' CREATE LOGIN sqllogin2 WITH PASSWORD = 'P@ssword' ログイン アカウントを作成した後は、このログイン アカウントに紐付いている、データベース ユ ーザーを再マッピング(紐付けし直し)する必要があります。これを行うには、次のように ALTER USER ステートメントを利用します。 -- コピー先のデータベースに接続してデータベース ユーザーとの再マッピングを行う ALTER USER sqllogin1 WITH LOGIN = sqllogin1 ALTER USER sqllogin2 WITH LOGIN = sqllogin2 このように、コピー先でログイン アカウントの作成とデータベース ユーザーの再マッピングを行 えば、データベース ユーザーに設定していたオブジェクト権限を、コピー元で設定していたのと同 じように利用することができます。 なお、Contained Database(包含データベース)を利用していて、包含データベース ユーザーを 利用している場合には、上記の操作は不要で、データベースのコピーを行っただけで、包含データ ベース ユーザーが利用できるようになります。 Azure ポータルを利用したデータベースのコピー Azure ポータルを利用しても、データベースのコピーを行うことができます。これを行うには、次 のように[データベースの構成]ブレードで[コピー]をクリックします(このコピーは、内部的 には、CREATE DATABASE .. AS COPY OF が利用されています) 。 1 216 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 [コピー]ブレードが表示されたら、 [データベース名]にコピー先となるデータベース名を入力し て、 [ターゲット サーバー]でコピー先となる SQL サーバーを選択して、 [選択]ボタンをクリッ クします。 1 2 4 3 最後に、 [作成]ボタンをクリックすれば、コピーが始まって、コピーの進行状況は、次のように[通 知]ブレードで確認することができます。 1 217 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 Azure ポータルで DAC Fx(.bacpac)を利用 Azure ポータルでは、DACFx(.bacpac)を利用したエクスポートとインポートができる機能も用 意されています。これは、次のように[データベースの構成]ブレードで、 [エクスポート]をクリ ックすることで行えます(エクスポートを行って、.bacpac ファイルを作成する場合) 。 1 2 5 3 4 エクスポート先には、Microsoft Azure のストレージ アカウント(BLOB ストレージ)を利用す るので、事前にストレージ アカウントを作成しておくようにします。 ストレージ アカウントにエクスポートした .bacpac ファイルは、次のように[SQL サーバーの 構成]ブレードで、 [データベースのインポート]をクリックすることでインポートすることができ ます。 1 218 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 4.8 SSDT の便利な機能(スキーマ比較、リファクタリング) SSDT では、データベースのスキーマ比較を行ったり、リファクタリング(オブジェクト名の変更 など)などを行ったりすることもできます。ローカルの SQL Server と Azure SQL Database の データベースで、スキーマ比較をして、定義が異なるオブジェクトがある場合には同じスキーマに する(更新する) 、といったことも行えます。 SSDT でのスキーマ比較 SSDT には、Visual Studio 2008 以前のバージョンで提供されていたデータベース プロジェクト 機能と同様、異なるサーバー間(開発機と本番機など)でのスキーマ比較(テーブルの列名やデー タ型、ストアド プロシージャのソースコードなどの比較)を行うことができます。 これを行うには、SQL Server オブジェクト エクスプローラーで、次のように対象データベースを 右クリックして、 [スキーマ比較]をクリックします。 これによって、次のように[スキーマ比較]エディターが表示されるので、 [ターゲットの選択]で 比較対象となるサーバー/データベースを選択すれば、スキーマ比較を行うことができます。 ターゲットの選択後、次のように[比較]ボタンをクリックすると、スキーマ比較を行うことがで きます。 219 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 スキーマ比較 をした結果 異なる部分は 赤や黄色、斜線 で表示される スキーマ比較を行うと、異なる部分があった場合には、赤や黄色、斜線で表示してくれます(従来 の Visual Studio 2008 でのスキーマ比較よりも見やすくなりました)。また、SSDT のスキーマ 比較機能では、従来からの機能と同様、異なる部分をターゲット サーバーへ反映させることも簡単 に行うことができます([更新]ボタンをクリックするだけで反映可能です) 。したがって、開発機 で修正したスキーマ変更を、本番環境へ反映させる、といったことも簡単に行うことができます。 SSDT のリファクタリング機能 ~名前やスキーマ変更が可能に~ SSDT では、リファクタリング機能が強化されて、オブジェクト名やスキーマの変更にも対応でき るようになりました。これを利用するには、次のようにデータベースを右クリックして、 [新しいプ ロジェクト]をクリックし、新しいデータベース プロジェクトを作成します。 SQL Server オブジェク ト エクスプローラーで Azure SQL Database に接続 [データベースのインポート]ダイアログでは、任意のプロジェクト名を設定して、 [開始]ボタン 220 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 をクリックします。 Azure SQL Database 内の オブジェクトをもとにデータベース プロジェクトを作成可能 Azure SQL Database 内の オブジェクトをインポートして スクリプトを生成可能 リファクターを行うには、次のようにビューやストアド プロシージャなどのスクリプトを表示し て、名前を変更したいオブジェクトを右クリックして、[リファクター]をクリックします。 1 名前を変更したいオブ ジェクトを右クリック 2 3 新しい名前 を入力 4 221 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 [リファクター]メニューの[名前の変更]をクリックした場合は、新しい名前(変更したい名前) を入力して[OK]ボタンをクリックすると、次のように[変更のプレビュー]ダイアログが表示さ れます。 名前の変更によって影響のある オブジェクトが一覧される このダイアログには、名前を変更したことによって影響のあるオブジェクトが一覧されて、 [適用] ボタンをクリックすれば、これらのオブジェクトを一括変更することができます。 リファクター機能で名前変更 商品から Products へ もちろんテーブル自身も 自動変更される 同じ名前のテーブルを利用している 他のストアド プロシージャも自動変更 このように、SSDT では、リファクタリング機能が強化されているので大変便利です(従来の Visual Studio 2008 のリファクタリング機能では、「ワイルドカードの展開」と「完全修飾名」の みの提供でした) 。 なお、データベース プロジェクトでは、.dacpac ファイル(データは含まれていない、スキーマ 定義のみ)を作成することができます。 222 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 STEP 5. Elastic Scale による シャーディング この STEP では、「Azure SQL Database Elastic Scale」を利用して、シャー ディング(データベース分割によるスケールアウト)を実現する方法を説明しま す。Azure SQL Database の Standard または Premium エディションでは、 500GB というデータベースの容量制限があるところを、Elastic Scale を利用す れば、複数のデータベースを 1つのデータベースのように見せかけることができ るようになるので、500GB を超えたデータを扱えるようになります(2つに分 割すれば 1TB 分、3つなら 1.5TB 分のデータ量を格納できるようになります) 。 この STEP では、次のことを学習します。 Elastic Scale の概要 Elastic Scale Client ライブラリの利用方法 223 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 5.1 Elastic Scale(プレビュー版)の概要 Azure SQL Database Elastic Scale(以降、Elastic Scale と記述)は、データベースのシャ ーディング(Sharding)を実現することができる機能として、現在プレビュー版が提供されていま す。 データベースのシャーディングは、次の図のように、複数のデータベースを 1 つのデータベースの ように見せかけることができる機能です(データベースを論理的に分割できる機能です) 。シャーデ ィングのシャード(Shard)は、 「破片」や「かけら」という意味で、分割した複数のデータベース がシャード(かけら)で、それらが集まって 1つのデータベースとして振る舞えるというものです。 Elastic Scale のシャーディングのイメージ データベース #1 (1, 100) クライアント アプリケーション 104 は データベース#2 に格納される データの追加 INSERT INTO t1 VALUES(104, .., ..) colA colB colC 1 2 3 : : : : : : : : : : : : 値によって データベースを分割 colA 列のデータが 1~100 の範囲の ものを格納するシャード データベース #2 (101, 200) データの全取得 SELECT * FROM t1 colA colB colC 101 102 103 : : : : : : : : : : : : 全データが対象なら、 すべてのデータベースから データを取得 colA 列のデータが 101~200 の範囲の ものを格納するシャード データベース #3 (201, 300) クライアントからは どのデータベースを利用している かは意識する必要がない = 1つのデータベースに見える colA colB colC 201 202 203 : : : : : : : : : : : : colA 列のデータが 201~300 の範囲の ものを格納するシャード この図は、値の範囲(Range)によって、シャードを作 成している例ですが、Elastic Scale では特定の値に よってシャーディングを作成することもできます。 データベースが分割されていても、クライアント アプリケーションからは、どのデータベースを利 用しているかは意識する必要がありません。 Elastic Scale は、データベース分割によるスケールアウトだけでなく、次のようにアクセス数の多 いシャードを性能の良い上位エディションに変更する(スケールアップする) 、といった柔軟な構成 をとることもできます。 Elastic Scale は柔軟な構成が可能 Premium P3 101~200 は アクセス数が多いので 高性能の上位エディ ションに変更する Standard S0 (1, 100) (101, 200) スケール アップ colA colB colC 201 202 203 : : : : : : : : : : : : : : : : : : : : : : : : : : : : colA colB colC 101 102 103 : : : : : : : : : : : : : : colA colB colC : : : 1 2 3 : : : : : : : : : : : : : : : : : : : : : : : : : : : 201~300 を 201~250 と 251~300 に 分割する(Split) (201, 300) Split の逆の Merge も可能) Split(分割) (201, 250) (251, 300) colA colB colC colA colB colC 201 202 203 : : : : : : : : : : 251 252 253 : : : : : : : : : : : : : : スケールアウト 224 Merge (統合) SQL Server 2014 自習書 No.5 Azure SQL Database 入門 また、シャードは、分割して複数のシャードにすることもでき(=Split と呼びます)、逆に複数の シャードを1つのまとめることもできます(=Merge と呼びます) 。 Elastic Scale の第一歩 Elastic Scale を始めるには、まず、次のページを参照するのがお勧めになります。 Azure SQL Database Elastic Scale のトピック http://azure.microsoft.com/ja-jp/documentation/articles/sql-database-elastic-scaledocumentation-map/ ここでは、各種の情報に関するリンクがまとまっています。 次に、サンプル アプリケーションを実行してみることをお勧めします。サンプル アプリケーショ ンは、ダウンロードした後に、Azure SQL Database への接続文字列(SQL サーバー名や管理者 アカウントの名前とパスワード)などを変更するだけで試すことができるので、一度試してみるこ とをお勧めします。試し方については、日本語の翻訳されたページ(以下)もあります。 Azure SQL Database Elastic Scale の概要 (プレビュー) http://azure.microsoft.com/ja-jp/documentation/articles/sql-database-elastic-scale-getstarted/ 225 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 5.2 Elastic Scale Client Library の利用 ここでは、NuGet で提供されている「Elastic Scale Client Library」を利用して、どのように Azure SQL Database のシャーディング(データベース分割)を実装するのかを説明します。この ライブラリを利用すれば、シャーディングに対して、次のようにデータを INSERT することがで きます(C# の場合の例)。 string cnstr = "Server=SQLサーバー名.database.windows.net;" + "Database=データベース名;" + "User ID=管理者アカウント名;Password=パスワード;Encrypt=true;"; ShardMapManager smm = ShardMapManagerFactory.GetSqlShardMapManager( cnstr, ShardMapManagerLoadPolicy.Lazy); RangeShardMap<long> sm = smm.GetRangeShardMap<long>(mapName); int data_colA = 133; string data_colB = "AAAA"; // colA 列に格納するデータ // colB 列に格納するデータ // SqlConnection を OpenConnectionForKey で作成 string cred = "User ID=管理者アカウント名;Password=パスワード;Encrypt=true;"; using (SqlConnection conn = sm.OpenConnectionForKey( data_colA, cred, ConnectionOptions.Validate)) { // SqlCommand で INSERT を実行 SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = @"INSERT INTO t1 VALUES(@a, @b)"; SqlParameter p1 = cmd.Parameters.Add("@a", SqlDbType.Int); SqlParameter p2 = cmd.Parameters.Add("@b", SqlDbType.VarChar, 100); p1.Value = data_colA; p2.Value = data_colB; cmd.ExecuteNonQuery(); } ADO.NET でのプログラミングと同じように、SqlConnection と SqlCommand オブジェクト を利用して、データを INSERT することができます。通常との違いは、SqlConnection オブジ ェクトを作成するときに、RangeShardMap オブジェクトの OpenConnectionForKey メソ ッドを利用しているところです。これは、次の図のような構成になります。 データの INSERT 時 int data_colA = 133; Key 値(133)を与えるこ とで、どのシャードに接続 すべきかを認識する string cred = "~"; using (SqlConnection conn = sm.OpenConnectionForKey( data_colA, cred, ~)) INSERT INTO t1 VALUES(data_colA, .., ..) シャード1 (1, 100) t1 テーブル colA 1 2 : シャード2 (101, 200) は シャード2 に 格納される colA 列のデータが 1~100 の範囲の ものを格納するシャード t1 テーブル colA data_colA(133) colB : : : 101 102 colB : : 133 : : : 226 colA 列のデータが 101~200 の範囲の ものを格納するシャード SQL Server 2014 自習書 No.5 Azure SQL Database 入門 この例では、OpenConnectionForKey メソッドの第1引数に data_colA(colA 列に格納する データ 133 を保持している変数)を与えていますが、この値(Key 値)によって、どのシャード に格納すべきかを認識できるようになっている、というライブラリになっています。 SqlCommand オブジェクトの利用方法は、通常の ADO.NET と同じです。このように、Elastic Scale Client Library を利用すれば、アプリケーションからは、どのデータベース(シャード) を利用しているかは意識する必要がなくなります。 Let's Try それでは、Elastic Scale Client Library を試してみましょう。ここでは、次のような C# の Windows フォーム アプリケーションを作成する場合を例に説明します。 シャーディング の構成 データの追加 データの参照 Button1 を ク リ ッ ク す る こ と で シ ャ ー デ ィ ン グ を 構 成 し て 、 Button2 で デ ー タ の 追 加 (INSERT) 、Button3 でデータの参照(SELECT)を行うようにします。 シャーディングは、次の図のように構成します。 構成 estest_1 (1, 100) t1 テーブル colA estest シャードの 管理情報を格納 1 2 : colB : : : colA 列のデータが 1~100 の範囲の ものを格納するシャード estest_2 (101, 200) t1 テーブル colA 101 102 : colB : : : colA 列のデータが 101~200 の範囲の ものを格納するシャード データベースを 3つ作成して、シャードの管理情報を格納するための「estest」 、1~100 のデー タを格納するためのシャード「estest_1」、101~200 のデータを格納するためのシャード 「estest_2」を作成します。テーブル名は「t1」として、 「colA」と「colB」の 2 つの列で構成 するようにします。 1. まずは、Azure SQL Database の SQL サーバーに接続して、データベースを 3つ作成しま す(master データベースに接続して、CREATE DATABASE ステートメントを実行します)。 227 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 CREATE DATABASE estest COLLATE Japanese_CI_AS ( EDITION = 'BASIC' ) CREATE DATABASE estest_1 COLLATE Japanese_CI_AS ( EDITION = 'BASIC' ) CREATE DATABASE estest_2 COLLATE Japanese_CI_AS ( EDITION = 'BASIC' ) このように作成したデータベースは、課金対象になるので、テストが完了したら、削除し忘れ ないように注意してください。 2. 次に、2つのシャード(estest_1 と estest_2 データベース)の中に、まったく同じ構成の 「t1」テーブルを次のように作成します。 CREATE TABLE t1 ( colA int NOT NULL PRIMARY KEY, colB varchar(100) NULL ) シャード1 (estest_1) に t1 テーブル を作成 シャード2 (estest_2) にも全く同じ構成 の t1 テーブル を作成 テーブル名は「t1」として、 「colA」と「colB」の 2 つの列で構成するようにします。colA は int、colB は varchar(100) データ型で定義して、colA に格納される値に応じて、シャ ードが分かれるという形にしていきます。 228 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 シャーディングの構成 ~Button1~ 次に、Button1 をクリックしたときに、シャーディングを構成するように Elastic Scale Client Library を利用して、コードを記述します。シャーディングの構成では、シャードをいくつ作成す るのか、各シャードが保持する値の範囲などを設定します。これには、ShardMapManager や RangeShardMap、RangeMapping、Shard オブジェクトなどを利用しますが、おおまかな関 係は、次のとおりです。 Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement 名前空間 ShardMapManagerFactory クラス ShardMapManager シャードマップの管理 ShardMapManager オブジェクト CreateSqlShardMapManage メソッド CreateRangeShardMap メソッド GetSqlShardMapManage メソッド シャードを値の範囲 で行う場合に利用 RangeShardMap オブジェクト CreateRangeMapping メソッド シャードの範囲 を構成 CreateShard メソッド シャードを構成する データベースを設定 Shard オブジェクト RangeMapping オブジェクト CreateSqlShardMapManage メソッドで、シャーディングを管理するための ShardMap Manager オブジェクトを作成して、CreateRangeShardMap メソッドで RangeShardMap オブジェクトを作成します。これは値の範囲(1~100 や 101~200 のような範囲)でシャード マップを作成するためのオブジェクトで、特定の値(1 や 2、3 などの値)に応じてシャードを分 割することもでき、この場合は ListShardMap オブジェクトを利用します。 RangeShardMap オブジェクトでは、CreateRangeMapping メソッドで RangeMapping オブジェクトを作成して、値の範囲を定義し、CreateShard メソッドで Shard オブジェクト(シ ャード)を作成して、どの範囲のどのデータベースを割り当てるのかに利用します。 それでは、具体的なコードを記述してみましょう。 1. まずは、Visual Studio 2013 を起動して、新しいプロジェクトを作成します。 3 2 1 229 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 [テンプレート]で[Visual C#]の[Windows フォーム アプリケーション]を選択して、 新しいプロジェクトを作成します。 2. 次に、Windows フォーム上に[Button]を 3 つ配置します。 3. 次に、NuGet から Elastic Scale Client Library を取得するために、 [ツール]メニューの [Nuget パッケージ マネージャー]の[ソリューションの NuGet パッケージの管理]をク リックします。 1 2 2 1 3 230 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 [NuGet パッケージの管理]ダイアログでは、 [オンライン]を選択して、 [検索]ボックス に「Elastic Scale」と入力して、Enter キーを押下します。これにより、検索結果に「Azure SQL Database Elastic Scale Client Library」が表示されるので、 [インストール]ボタン をクリックして、インストールします。 4. インストールが完了したら、Button1 をダブルクリックして、Click イベント ハンドラーを 作成し、ここにコードを記述していきます。まずは、ShardMapManager オブジェクトなど を操作するために、次のように using を利用して Elastic Scale Client ライブラリの名前 空間を宣言します。 using Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement; Elastic Scale Client ライブラリを利用するために 名前空間を宣言する Button1 の Click イベント ハンドラー 5. 次に、Azure SQL Database 上のデータベース「estest」に接続するための接続文字列を private 変数として定義します。また、これから作成するシャードマップの名前も「maptest1」 という値で変数として定義しておきます。 private string + + private string cnstr = "Server=SQLサーバー名.database.windows.net;" "Database=estest;" "User ID=管理者アカウント名;Password=パスワード;Encrypt=true;"; mapName = "maptest1"; Azure SQL Database 上 のデータベースに接続す るための接続文字列 シャードマップの 名前を「maptest1」とする 接続文字列の記述方法は、通常の ADO.NET と同様です。 6. 次に、Button1 の Click イベント ハンドラーの中に、ShardMapManager オブジェクト を作成するためのコードを、次のように記述します。 231 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 // 既存の ShardMapManager の存在チェック。TryGetSqlShardMapManager ShardMapManager smm; bool shardMapManagerExists = ShardMapManagerFactory.TryGetSqlShardMapManager( cnstr, ShardMapManagerLoadPolicy.Lazy, out smm); if (shardMapManagerExists) { // ShardMapManager が既に存在している場合 Console.WriteLine("Shard Map Manager already exists"); } else { // ShardMapManager の作成。CreateSqlShardMapManager ShardMapManagerFactory.CreateSqlShardMapManager(cnstr); Console.WriteLine("Created SqlShardMapManager"); smm = ShardMapManagerFactory.GetSqlShardMapManager( cnstr, ShardMapManagerLoadPolicy.Lazy); } ShardMapManagerFactory ク ラ ス の TryGetSqlShardMapManager メ ソ ッ ド で 、 cnstr(接続文字列)を与えて、データベース内に ShardMapManager が存在しているか どうかをチェックして、存在していない場合は、 CreateSqlShardMapManage メソッド で、ShardMapManager を作成するようにしています。作成した ShardMapManager は、 GetSqlShardMapManage メソッドを利用して、smm というオブジェクト変数に格納し ています。 7. 次に、値の範囲でシャードマップを作成するために、続けて、次のようにコードを記述します。 // RangeShardMap(範囲タイプのシャードマップ) RangeShardMap<long> sm = null; if (!smm.TryGetRangeShardMap(mapName, out sm)) { // シャードマップの作成。CreateRangeShardMap sm = smm.CreateRangeShardMap<long>(mapName); } // シャードの作成。CreateShard。SQL サーバー名とデータベース名を指定する string srvName = "SQLサーバー名.database.windows.net"; Shard shard1 = null, shard2 = null; if (!sm.TryGetShard(new ShardLocation(srvName, "estest_1"), out shard1)) { shard1 = sm.CreateShard(new ShardLocation(srvName, "estest_1")); } if (!sm.TryGetShard(new ShardLocation(srvName, "estest_2"), out shard2)) { shard2 = sm.CreateShard(new ShardLocation(srvName, "estest_2")); 232 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 } // 範囲マッピングの作成。CreateRangeMapping。範囲の設定(1~100 と 101~200) RangeMapping<long> rmap = null; // shard1 は 1~100 の範囲 if (!sm.TryGetMappingForKey(1, out rmap)) { sm.CreateRangeMapping(new RangeMappingCreationInfo<long> (new Range<long>(1, 100), shard1, MappingStatus.Online)); } // shard2 は 101~200 の範囲 if (!sm.TryGetMappingForKey(101, out rmap)) { sm.CreateRangeMapping(new RangeMappingCreationInfo<long> (new Range<long>(101, 200), shard2, MappingStatus.Online)); } ShardMapManager の存在チェック RangeShardMap (シャードマップ) の作成 シャードの作成 DB との紐付け ShardMapManager の作成 Button1 の Click イベント ハンドラー 内にコードを記述 範囲マッピングの作成 1~100 の範囲を作成 範囲マッピングの作成 101~200 の範囲を作成 CreateRangeShardMap メソッドで RangeShardMap オブジェクトを作成します。これ は値の範囲(1~100 や 101~200 のような範囲)でシャード マップを作成するためのオブ ジェクトで、特定の値(1 や 2、3 などの値)に応じてシャードを分割することもでき、この 場合は ListShardMap オブジェクトを利用します。 RangeShardMap オ ブ ジ ェ ク ト で は 、 CreateRangeMapping メ ソ ッ ド で RangeMapping オブジェクトを作成して、値の範囲を定義し、CreateShard メソッドで Shard オブジェクト(シャード)を作成して、どの範囲のどのデータベースを割り当てるの かに利用します。このコードでは、shard1 に estest_1 データベースを割り当てて、1~ 100 の範囲マッピング、shard2 に estest_2 データベースを割り当てて、101~200 の 範囲マッピングを設定しています。 233 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 8. コードの記述が完了したら、ツールバーの[開始]ボタンをクリックしてデバッグを開始し、 [Button1]をクリックします。 1 2 Button1 をクリックしてから 1分ぐらい経過すると、実行が完了するので、完了後、 [閉じる] ボタンをクリックして、Windows フォームを終了し、デバッグ実行を終了します。 9. 実行が完了した後に、Azure SQL Database 上のデータベース(estest や estest_1 など) を参照すると、次のように ShardMapManager が自動作成されていることを確認できます。 テーブルが自動で 作成されている 各シャードの 範囲の MinValue(最小値) と MaxValue(最大値) データの INSERT ~Button2~ 次に、Button2 がクリックされたときに、t1 テーブルにデータを追加(INSERT)するようにコ ードを記述していきましょう。 1. データの追加には、ADO.NET の SqlConnection と SqlCommand オブジェクトを利用 するので、まずは、次のように System.Data.SqlClient 名前空間を宣言しておきます。 using System.Data.SqlClient; 234 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 2. 次に、Windows フォーム上の Button2 をダブルクリックして、Click イベント ハンドラ ーを作成し、次のようにコードを記述します。 ShardMapManager smm = ShardMapManagerFactory.GetSqlShardMapManager( cnstr, ShardMapManagerLoadPolicy.Lazy); RangeShardMap<long> sm = smm.GetRangeShardMap<long>(mapName); // 追加するデータ int data_colA = 133; string data_colB = "AAAA"; // SqlConnection を OpenConnectionForKey で作成 string cred = "User ID=管理者アカウント名;Password=パスワー ド;Encrypt=true;"; using (SqlConnection conn = sm.OpenConnectionForKey( data_colA, cred, ConnectionOptions.Validate)) { // SqlCommand で INSERT を実行 SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = @"INSERT INTO t1 VALUES(@a, @b)"; SqlParameter p1 = cmd.Parameters.Add("@a", SqlDbType.Int); SqlParameter p2 = cmd.Parameters.Add("@b", SqlDbType.VarChar, 100); p1.Value = data_colA; p2.Value = data_colB; cmd.ExecuteNonQuery(); } Button2 の Click イベント ハンドラー 内にコードを記述 ShardMapManager の取得 追加するデータ RangeShardMap の取得 接続のためのユーザー情報 (Azure SQL Database の 管理者アカウント) OpenConnectionForKey で SqlConnection を作成 通常の ADO.NET と同様 で SqlCommand で INSERT ステートメントを実行 デ ー タ の 追 加 は 、 ADO.NET で の プ ロ グ ラ ミ ン グ と 同 じ よ う に 、 SqlConnection と SqlCommand オブジェクトを利用します。SqlConnection オブジェクトを作成するとき に、RangeShardMap(シャードマップ)の OpenConnectionForKey メソッドを利用し て、colA に格納するデータ(data_colA=133)を与えるようにします。これは、次の図の ような構成になります(前出の図の再掲) 。 235 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 データの INSERT 時 int data_colA = 133; Key 値(133)を与えるこ とで、どのシャードに接続 すべきかを認識する string cred = "~"; using (SqlConnection conn = sm.OpenConnectionForKey( data_colA, cred, ~)) INSERT INTO t1 VALUES(data_colA, .., ..) シャード1 (1, 100) t1 テーブル colA 1 2 : colB : : : シャード2 (101, 200) colA 列のデータが 1~100 の範囲の ものを格納するシャード t1 テーブル colA data_colA(133) は シャード2 に 格納される 101 102 colB : : 133 : : : colA 列のデータが 101~200 の範囲の ものを格納するシャード OpenConnectionForKey メソッドで data_colA(133)を与えることで、この値をどの シャードに格納すべきかを、ライブラリが認識できるようになっています (ShardMapManager がシャードを管理しています) 。 SqlCommand オブジェクトの利用方法は、通常の ADO.NET と同じです。 3. コードの記述が完了したら、ツールバーの[開始]ボタンをクリックしてデバッグを開始し、 [Button2]をクリックします。 1 2 実行が完了したら、 [閉じる]ボタンをクリックして、Windows フォームを終了し、デバッグ 実行を終了します。 4. 次 に、 追 加 され た デ ータ を 確認 す る ため に 、 Azure SQL Database 上 のデ ー タベ ース (estest_1 と estest_2)に接続して、 「t1」テーブルを参照します。 SELECT * FROM t1 シャード1 (estest_1 データベース)に はデータが格納されていない 236 シャード2 (estest_2 データベース)に データが格納されている SQL Server 2014 自習書 No.5 Azure SQL Database 入門 シャード2(estest_2 データベース)の t1 テーブルにだけデータ(133)が追加されてい ることを確認できます。 5. 次に、コード内の data_colA 変数に与える値を 133 から 77 に変更して、デバッグを開 始し、[Button2]をクリックします。 int data_colA = 77; 2 3 1 6. 実行後、追加されたデータを確認するために、「t1」テーブルを参照します。 SELECT * FROM t1 シャード1 (estest_1 データベース)に 77 が格納されている 今度は、シャード 1(estest_1 データベース)の t1 テーブルにだけデータ(77)が追加さ れていることを確認できます。 このように、Elastic Scale Client ライブラリを利用すれば、データベースの分割(複数のデ ータベースを1つのデータベースのように見せかけること)ができるようになります。 データの SELECT ~Button3~ 次に、「t1」テーブルのデータを参照(SELECT)するコードを記述してみましょう。 1. データの参照では、複数のシャードからデータを取得するために、MultiShardConnection、 MultiShardCommand、MultiShardDataReader というオブジェクトを利用しますが、基 本的な利用方法は、ADO.NET の SqlConnection、SqlCommand、SqlDataReader と同じで 237 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 す。まずは、MultiShard~ を利用するために、次のように名前空間を宣言します。 using Microsoft.Azure.SqlDatabase.ElasticScale.Query; 2. 次に、Windows フォーム上の Button3 をダブルクリックして、Click イベント ハンドラ ーを作成し、次のようにコードを記述します。 // ShardMapManager の取得 ShardMapManager smm = ShardMapManagerFactory.GetSqlShardMapManager( cnstr, ShardMapManagerLoadPolicy.Lazy); // RangeShardMap の取得 RangeShardMap<long> sm = smm.GetRangeShardMap<long>(mapName); // MultiShardConnection を利用。GetShards でシャードの一覧を取得 string cred = "User ID=管理者アカウント名;Password=パスワー ド;Encrypt=true;"; using (MultiShardConnection conn = new MultiShardConnection( sm.GetShards(), cred)) { // MultiShardCommand で SELECT を実行 using (MultiShardCommand cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT * FROM t1"; cmd.CommandType = CommandType.Text; cmd.ExecutionOptions = MultiShardExecutionOptions.IncludeShardNameColumn; cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults; // MultiShardDataReader で結果を Read する using (MultiShardDataReader sdr = cmd.ExecuteReader()) { while (sdr.Read()) { var data_colA = sdr.GetFieldValue<int>(0); var data_colB = sdr.GetString(1); Console.WriteLine(data_colA); Console.WriteLine(data_colB); } } } } MultiShardConnection では、GetShards メソッドを利用することで、シャードの一覧を 取得することができます。MultiShardCommand で実行したい SELECT ステートメント を定義して、MultiShardDataReader で実行結果を Read するという形になります。 3. コードの記述が完了したら、ツールバーの[開始]ボタンをクリックしてデバッグを開始し、 [Button3]をクリックします。 238 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 1 2 Button13 の Click イベント ハンドラー 内にコードを記述 実行が完了すると、次のように[出力]ウィンドウに t1 テーブルのデータが表示されている こを確認できます。 t1 テーブルの データを取得できている ことを確認 データベースの削除(Elastic Scale の削除) 確認が完了したら、最後にデータベースを削除しておきます(データベースが残っていると課金対 象になるので、削除しておくようにしてください) 。データベースを削除すれば、Elastic Scale の 設定(ShardManager の情報など)も完全に削除されることになります。 1. データベースの削除は、Azure SQL Database の master データベースに接続して、DROP 239 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 DATABASE ステートメントを実行します。 DROP DATABASE estest DROP DATABASE estest_1 DROP DATABASE estest_2 master データベースに接続 して DROP DATABASE を 実行しておく 以上のように、Elastic Scale Client ライブラリを利用すれば、複数のデータベースを1つの データベースのように見せかけることができるようになります。データベースのシャーディン グを実現できます。 Elastic Scale では、Split(シャードの分割)や Merge(2 つのシャードの統合)などもある ので、冒頭のドキュメント マップを参考に、ぜひ試してみてください。 おわりに 最後までこの自習書を試された皆さま、いかがでしたでしょうか? Azure SQL Database は、ク ラウド上に配置されているデータベースですが、通常の SQL Server と同じように、容易に操作で きることを体感していただけたのではないでしょうか。特に Version 12 からは、完全互換と言っ ても良いくらい SQL Server と同じように操作できるようになっています(Azure SQL Database は非常に早いスピードで進化を遂げています)。 また、Elastic Scale は、現在プレビュー版ですが、データベースを(論理的に)分割して、スケー ルアウトを実現することができるので、今後の正式リリースが本当に楽しみなテクノロジーです。 Azure SQL Database には、ジオ レプリケーション(災害対策に、データセンターをまたがった レプリケーション)や、アクティブ セカンダリ(読み取り可能なセカンダリ)機能などもあるので、 ぜひ試してみてください。 240 SQL Server 2014 自習書 No.5 Azure SQL Database 入門 執筆者プロフィール 有限会社エスキューエル・クオリティ(http://www.sqlquality.com/) SQLQuality(エスキューエル・クオリティ)は、日本で唯一の SQL Server 専門の独立系コンサルティング 会社です。過去のバージョンから最新バージョンまでの SQL Server を知りつくし、多数の実績と豊富な経 験を持つ、OS や .NET にも詳しい SQL Server の専門家(キャリア 17年以上)がすべての案件に対応し ます。人気メニューの「パフォーマンス チューニング サービス」は、100%の成果を上げ、過去すべてのお 客様環境で驚異的な性能向上を実現。チューニング スキルは世界トップレベルを自負、検索エンジンでは(英 語情報を含めて)ヒットしないノウハウを多数保持。ここ数年は BI/DWH システム構築支援のご依頼が多 く、支援だけでなく実際の構築も行う。 主なコンサルティング実績/構築 大手製造業の「CAD 端末の利用状況の見える化」システム構築 Oracle や CSV(Notes)、TSV ファイル、Excel からデータを抽出し、SQL Server 2012 上に DWH を構築 見える化レポートには Reporting Services を利用 大手映像制作会社の BI システム構築(会計/業務システムにおける予実管理/原価管理など) 従来 Excel で管理していたシートを Reporting Services のレポートへ完全移行。 Oracle や勘定奉行からデータを抽出して、SQL Server 上に DWH を構築 大手流通系の DWH/BI システム構築支援(POS データ/在庫データ分析/ABC 分析/ポイントカード分析) 大手アミューズメント企業の BI システム構築支援(人事システムにおける人材パフォーマンス管理) Reporting Services による勤怠状況の見える化レポートの作成、PostgreSQL/人事システムからのデータ抽出 外資系医療メーカーの BI システム構築支援(Analysis Services と Excel による販売分析システム) OLAP キューブによる売上および顧客データの多次元分析/自由分析(ユーザーによる自由操作が可能) 大手流通系の DWH システムのパフォーマンス チューニング データ量 100 億件の DWH、総ステップ数2万越えのストアド プロシージャのパフォーマンス チューニング ミッション クリティカルな金融システムでのトラブル シューティング/定期メンテナンス支援 SQL Server の下位バージョンからの移行/アップグレード支援(32 ビットから x64 への対応も含む) 複数台の SQL Server の Hyper-V 仮想環境への移行支援(サーバー統合支援) ハードウェア リプレース時のハードウェア選定(最適なサーバー、ストレージの選定)、高可用性環境の構築 2 時間かかっていた日中バッチ実行時間を、わずか 5 分へ短縮(95.8% の性能向上) Java 環境(Tomcat、Seasar2、S2Dao)の SQL Server パフォーマンス チューニング etc コンサルティング時の作業例(パフォーマンス チューニングの場合) アプリケーション コード(VB、C#、Java、ASP、VBScript、VBA)の解析/改修支援 ストアド プロシージャ/ユーザー定義関数/トリガー(Transact-SQL)の解析/改修支援 インデックス チューニング/SQL チューニング/ロック処理の見直し 現状のハードウェアで将来のアクセス増にどこまで耐えられるかを測定する高負荷テストの実施 IIS ログの解析/アプリケーション ログ(log4net/log4j)の解析 ボトルネック ハードウェアの発見/ボトルネック SQL の発見/ボトルネック アプリケーションの発見 SQL Server の構成オプション/データベース設定の分析/使用状況(CPU, メモリ, ディスク, Wait)解析 定期メンテナンス支援(インデックスの再構築/断片化解消のタイミングや断片化の事前防止策など)etc 松本美穂(まつもと・みほ) 有限会社エスキューエル・クオリティ 代表取締役 Microsoft MVP for SQL Server(2004 年 4 月~) 経産省認定データベース スペシャリスト/MCDBA/MCSD for .NET/MCITP Database Administrator SQL Server の日本における最初のバージョンである「SQL Server 4.21a」から SQL Server に携わり、現在、SQL Server を中心とするコンサルティングを行っている。得意分野はパフォーマンス チューニングと Reporting Services。著書の 『SQL Server 2000 でいってみよう』と『ASP.NET でいってみよう』(いずれも翔泳社刊)は、トップ セラー(前者は 28,500 部、後者は 16,500 部発行)。近刊に『SQL Server 2012 の教科書』(ソシム刊)がある。 松本崇博(まつもと・たかひろ) 有限会社エスキューエル・クオリティ 取締役 Microsoft MVP for SQL Server(2004 年 4 月~) 経産省認定データベース スペシャリスト/MCDBA/MCSD for .NET/MCITP Database Administrator SQL Server の BI システムとパフォーマンス チューニングを得意とするコンサルタント。アプリケーション開発(ASP/ ASP.NET、C#、VB 6.0、Java、Access VBA など)やシステム管理者(IT Pro)経験もあり、SQL Server だけでなく、 アプリケーションや OS、Web サーバーを絡めた、総合的なコンサルティングが行えるのが強み。Analysis Services と Excel による BI システムも得意とする。マイクロソフト認定トレーナー時代の 1998 年度には、Microsoft CPLS トレーナ ー アワード(Trainer of the Year)を受賞。 241