【Azure for ITPRO シリーズ】 SQL Azure 管理とセキュリティ

【Azure for ITPRO シリーズ】SQL Azure 管理とセキュリティ 2010年10月版 V1.1 マイクロソフト株式会社エバンジェリスト安納順一 http://blogs.technet.com/junichia/

修正履歴

Agenda & Takeaway • SQL Azure オーバービュー • データベース管理とセキュリティ • 管理ツール • 認証とFirewall • 移行/同期/バックアップ • SQL Server との違い • Business Intelligence • まとめ

まずは初物から SQL Azure オーバービュー

SQL Azure とは？ ホスティングサービスの先へ • パブリッククラウド上の RDB • 既存のスキルとツールを引き継いで利用可能 • 既存データベースアプリケーションにクラウド接続を提供 Public Preview Community review 予定あり未定 Reporting Services Business Analytics Data SyncService Management Portal Database SQL Data Platform as a Service

SQL Azure の特徴 • インストール/パッチ適用の必要が無い • サーバー本体のメンテナンスの必要が無い • 高い可用性と耐障害性 • シンプルな管理 • ビジネスニーズに応じたスケールアップ/ダウン • マルチテナント • 従来の管理ツール、開発ツールを継続して利用可能 • T-SQL ベースの RDB モデル

オンプレミスとのシンメトリックな関係 SQL Azure へのアクセス方法は２つ (HTTP/S と TDS) オンプレミスのアプリケーションからはシームレスにアクセスが可能 ADO.NET/REST - EDM HTTP/S SOAP/REST HTTP/S 管理 SQLCMD Management Studio App Code (ASP.NET) PowerPivot BI SSRS T-SQL (TDS) Windows Azure T-SQL (TDS) SSAS T-SQL (TDS) SSIS T-SQL (TDS) T-SQL (TDS) LOB App Code App Code T-SQL (TDS) T-SQL (TDS) SQL Azure SQL Server ADO.NET/ODBC/PHP ※SQL Azureでサポートされるのは TDS（tabular data stream）7.3以降 ※OLE DB経由の接続はサポートされない

ちなみに TDS 以外の接続は？ • SQL Azure OData Service をサポート予定（現在 Preview） • REST API を使用した HTTPS アクセスが可能 SQL AzureDB OData Service TDS https://odata.sqlazurelabs.com/OData.svc/v0.1/<sql azure host>/<dbname> 認証/承認 RESTAPI https Anonymous or AppFabric ACS ODataJavascript Library OData SDK for PHP Restlet extension for OData (Java) ruby_odata: OData client library for ruby OData Client Library for Win Phone 7 ODataclient for Objective-C .NET F 3.5 SP1 Data Services update for .NET F 3.5 SP1 Microsoft .NET F 4 Silverlight 4

SQL Azure サービスのプロビジョニング Account Azure プラットフォームサービス共通のID（LiveID）を発行 • SQL AzureServer と管理アカウントを作成 • サーバー名は自動生成<servername>.database.windows.net Server 1つのアカウントで1つのサーバーのみ生成可能 Database Database Database • データベースの作成 • 1データベースあたり1GB - 50GB

（参考）アカウント作成直後 まだデータベース用ノード（サーバー）は割り当てられていない

（参考）サーバー作成と管理カウントの作成 ここで作成したアカウントが sa 権限を持つ

（参考）サーバー名と masterDB の割り当て サーバー名初期は master データベースのみが割り当てられる

50GB Database サポート • 2タイプの SQL Azure Database： • Web Edition :最大サイズ = 1 GB or 5 GB • ¥ 979.02 / 最大1GB のデータベース / 月 • ¥ 4,895.10 / 最大5GB までのデータベース / 月 • BusinessEdition ：最大サイズ =10 | 20 | 30 | 40 | 50 • ¥ 9,799.02 / 最大10GB までのデータベース / 月 • ¥ 19,598.04 / 最大20GB までのデータベース / 月 • ¥ 29,397.06 / 最大30GB までのデータベース / 月 • ¥ 39,196.08 / 最大40GB までのデータベース / 月 • ¥ 48,995.10 / 最大50GB までのデータベース / 月 Web の場合 Business の場合

SQL Azure の構造とスケーラビリティ ユーザーDB へのアクセスは TDSGateway を介するユーザー固有のmaster データベースにより認証が行われDB一覧を取得 node:Machine14 node:Machine15 node:Machine150 fabric fabric fabric Mgmt. Service Mgmt. Service Mgmt. Service SQL SV SQL SV SQL SV Master クラスター TDSGATEWAY ログインリクエストユーザーのmasterを検索認証 master にアクセス UserDBにアクセス master node 1 master node 2 Partition Manager Partition Manager データクラスター Data Node Component Data Node Component

master データベースの役割 • SQL AzureServer は複数の物理サーバーに分散したデータベースの集合体 • master データベースによりユーザーのデータベースモデルが維持される • ログイン/ロールチェックは master データベースで行われる • ログイン管理/データベース管理は master に対して行うログイン/ロールチェックログイン管理データベース管理 master

自動複製とフェールオーバー • 異なるノードに常に 2つの複製を持つ • プライマリレプリカ＊１ • セカンダリレプリカ＊２トランザクションのコミット時に複製コミット優先度１セカンダリ複製プライマリの障害時にはコミット優先度の高いほうがプライマリになる複製プライマリコミット優先度2 セカンダリ更新処理はプライマリで行われる TDSGateway （参考） 4時間以内に、障害が発生したプライマリが復旧しない場合、廃棄されて新たなセカンダリが生成される DMLOperations (insert/update/delete)

ロードバランス 5分に1回のチェックサイクルで最適なノードを選定セカンダリセカンダリプライマリ Load Balancer ノードのチェック（障害・リソース）メトリックスを収集（負荷等）適用 5分に1回分析＆ノードの決定

いろいろ小難しいこと言ってるけど、 ぜーんぶ勝手にやってくれるんだって！これならデータセンターよりいいかも…

とはいえ、クラウドだって管理は必要 SQL Azure の管理とセキュリティ

SQL Azure 用管理ツール • SQL AzurePortal • ブラウザーからの操作 • 管理ユーザーのパスワード変更 • データベースの作成と削除 • Firewall の設定 • SQL Server Management Studio • おなじみの管理コンソール • SQL Server 認証で SQL Azure に接続 • 基本的なDB/テーブル操作に加えて、DB移行にも有用 • SQLCMD • SQL Server 認証で SQL Azure に接続 • Project Houston（CTP1） • ブラウザーからの操作 • Silverlight ベースの管理コンソール • 現時点では SSMS にはおよばない… • 1280 * 1024 以上の解像度がお勧め

SQL Azure の管理機能概要

SQL AzurePortal 管理者パスワードリセットサーバーの削除データベースの作成/削除

SQL Server Management Studio • バージョンによってサポート範囲が異なる • サーバーへの接続には SQL Server ログインを使用する SQL Server 2008Management Studio • GUI による管理はサポートされていない • データベースに接続するたびに「接続」ダイアログを使用する※SQL Azure では USEコマンドがサポートされていない • 全ての操作はクエリーで実行する SQL Server 2008R2Management Studio • GUIによる操作を「一部」サポート • オブジェクトエクスプローラーを使用可能 • データベース作成、ユーザー作成をGUIから実施可能 • テーブル操作はクエリーを使用 • クエリーウィンドウはデータベースごとに開く必要あり※SQL Azure では USEコマンドがサポートされていない

SQL AzurePortal と Management Studio AQL Azure ポータル（http://sql.azure.com/) SQL Server 2008 R2 Management Studio

SQLCMD sqlcmd -U <ユーザーID@ホスト名> -P <パスワード> -S <サーバー名> -d <DB名> -N • ログインには <login>@<server名>を使用する（注）osql コマンドは未サポート

CTP1 Codename “Project Houston” http://sqlazurelabs.com/houston.aspx • Silverlight版 SQL Azure 用管理ツール • テーブルデザイナー • クエリーデザイナー • ビューデザイナー • ストアドプロシジャーデザイナー • 各種ステータス表示

Houstonーデータベースの使用量

Houston ーテーブルのデザイン

Houstonーデータの操作 行の追加/削除データを直接編集

SQL Azure へのアクセス経路と認証 • SQL Azure の認証は「SQL Server 認証」のみ • フロントのエンドポイントに実装された認証方式をユーザーに公開することも可能 SQL Server 認証 SSMS/SQLCMD/BCP ASP.NET 独自認証 Application OData Service REST SQL Azure WIF AppFabric ACS ASP.NET WIF AD FS 2.0 AD DS

Firewall の設定 • インターネット←(1433/TCP)←オンプレミス • SQL Azure←(IP Range)←インターネット SQLAzure ← ANY1433/TCP SQL Azure ポータルレンジを設定 • サーバー単位に設定が必要 • 反映されるまでに5分程度を要する • 現在は IPv6 未サポート • 最大128ルールクライアントのIPアドレス xxx.xxx.xxx.xxx sys.firewall_rules_table ※OData Service を使用することで HTTPS でのアクセスが可能

OData Service+ ACS による認証/承認の拡張 • OData と ACS の連携により、より強固な認証/承認が可能に • オンプレミス AD DS を使用してシングルサインオン SQL AzureDB OData Service AppFabric ACS TDS ﾌｪﾃﾞﾚｰｼｮﾝ信頼認証/承認ﾌｪﾃﾞﾚｰｼｮﾝ信頼 RESTAPI ⑤SWTﾄ-ｸﾝ送付 https ③SAMLﾄｰｸﾝ送付 ④SWTﾄｰｸﾝ発行 ②SAMLﾄｰｸﾝ発行 ①ﾄｰｸﾝ発行依頼 AD DS AD FS 2.0 認証

Open Data Protocol (OData) Data Clients ExcelPowerPivot .NET Client Silverlight Javascript PHP … Open Data Protocol (AtomPub + EDM) Data Sources Analysis & Reporting ADO.NET SharePoint SQL Server WCF Data & RIA Services SQL Azure Win Azure

移行/同期/バックアップ 移行 • SQL Server Management Studio • スクリプトウィザード • データ層アプリケーション抽出/登録/取込み • データインポート/エクスポートウィザード • BCP.EXE • SQL ServerIntegration Service（SSIS） • 接続マネージャーに「ADO.NET」を使用 • Sync Framework • SQL Azure Data Sync Tool for SQL Server • PowerPackfor SQL Azure November CTP（32-bit） • SQL Azure Data SyncService（ラボ） • SQL AzureData Copy • SQL Server Migration Wizard（SSMW）バックアップ移行移行同期同期バックアップ移行

SQL Server → SQL Azure データ移行ツールの概要 not supported by Microsoft

SMSSースクリプト作成ウィザード • SQL Server から SQL Azure へのデータベース移行 • SQL ServerManagement Studio • 　　┗ [データベース] ノード • 　　┗[タスク] • 　　　　┗[スクリプトの生成] • SQL Azure へのネイティブな対応 • スクリプトによる細かな制御 • データ部は Insert 文に変換されるため高速移行には向かない • 非互換によりスクリプトが生成されないこともある • Unsupported Transact-SQL Statements (SQL Azure Database)http://msdn.microsoft.com/en-us/library/ee336253.aspx

つづき～ SQL Azure への接続設定

SSMS － データ層アプリケーション（DAC)の移行 • データベースオブジェクトおよびインスタンスオブジェクトを含むエンティティ • データ自体は含まれない • テーブルやビューなどを個別に移行するのではなく、まとめてパッケージ化（DACPACK）して移行することができる • Visual Studio 2010 を使用して DACを開発することも可能 DACPACKを抽出する場合 DACPACKを取り込む場合 • SQL ServerManagement Studio • 　　┗ [データベース] ノード • 　　　┗[タスク] • 　　　　┗[データ層アプリケーションの抽出] SQL ServerManagement Studio ┗[<サーバー>] 　　　┗[データ層アプリケーションの配置] データスキーマ .DACPACK 配置テーブル抽出ビューログインストアドプロシジャ DAC

DAC と UCP（Utility Control Point） UCP（ユーティリティコントロールポイント）と DAC により、アプリケーションが要求する OS バージョンや SQLServer バージョンにあったインスタンスに DAC を展開できる DACアプリの抽出 SQL Server Management Studio DACアプリの状態を集中監視管理者 DAC DAC ユーティリティコントロールポイント（UCP） DACアプリの開発 SQL Server インスタンス群 Visual Studio2010 データベースエンジニア

SSMS ーインポート/エクスポートウィザード SQL ServerManagement Studio ┗ [データベース] ノード ┗[タスク] 　　┣ [データのインポート] 　　┗ [データのエクスポート] • SSIS と同様のウィザードを使用 • 移行単位を細かく制御可能 • データベース全体 • 選択したテーブル • クエリに合致した行 • データベースは事前に作成しておく • 自動的に互換性を補完しないので必要に応じて手動での調整が必要 • クラスター化インデックスの追記 • データ変換の修正

つづき～ SQL AzureDB の指定方法 .NET Framework Data Provider for SqlServer編タイムアウトでエラーとなる場合にはここで調整

SQL Azure Migration Wizard http://sqlazuremw.codeplex.com/ • Scenario • スキーマとデータの移行 • メリット • SQL Azure 互換 • バルク移行に対応 • はじめに clustered index 作成を行う • スクリプトを生成してくれる • 留意点 • データがローカルに保存される • NOT Supported by Microsoft

SQL Azure のバックアップ • 障害シナリオ • ソリューション • 提供 • 時期 • 備考 • ハードウェア • 故障 • 自動複製機能 • ・常に3つのレプリカが作成される • 提供中 • SQL Azureに組み込み X • データ紛失 • BCP、SSIS 等 • 提供中 • データベースクローン機能 • ・ローカル→ローカル • ・ローカル→別サーバー • ・ローカル→別リージョン • 提供中 • 利用者自身が実行 • スケジューリング可能なバックアップ機能 • 任意の時点のバックアップからリストア • スケジュール設定 • バックアップされたデータは読取専用 • ローカル/リモート対応 • 予定 • 利用者自身が設定

BCP.EXE バルク API を使用したデータの高速移行 SQL Azure → ローカル bcpKENALL.dbo.KENout c:\data.txt -c -S tcp:xxa8lqxxxx.database.windows.net -U username@xxa8lqxxxx -P password ローカル → SQL Azure bcpKENALL.dbo.KENin c:\data.txt-c -S tcp:xxa8lqxxxx.database.windows.net -U username@xxa8lqxxxx -P password -T（統合認証）は未サポートｰS（サーバー名）は必須 ※サーバー名には「tcp:」必須

SQL Azure データベースクローン • 大規模 SQL Azure データベースの高速コピー • 利用シナリオ • データベースのバックアップまたは移行 • 同一サーバー内 • リージョン内のサーバー間 • リージョン間 • データベースごとのロールバック CREATE DATABASE <dest_db> AS COPY OF <source_db> <source_db> ：ソースデーターベース <dest_db> ：複製先データベース

データベースクローン機能（Update4 で提供済) CREATE DATABASE sv01.db1clone AS COPYOF sv01.db1 NORTHUS Region master DB1clone DB1 sv01.database.windows.net CREATE DATABASE sv02.db1clone AS COPYOF sv01.db1 master DB1clone sv02.database.windows.net データセンター間は現時点で未サポート SOUTH US Region DB1clone master sv03.database.windows.net

SQL Azure を中心としたデータ統合シナリオ 全てのプラットフォーム間でのデータ同期が可能にオフラインキャッシュが有効なアプリケーション Sync FrameworkV2.1 Sync Framework V3(future) Sync C/Sアプリ Sync SQL Azure Data Sync Service (preview) 企業内スマートフォン Sync Sync Sync Sync Sync Sync FrameworkV2.1 企業間

Microsoft Sync Framework • プロバイダー間のデータプロビジョニングを実現 • Metadata Storage により差分同期を管理 • V2.1 では SQL Azure をプロバイダーとして登録可能 SQL Azure オンプレミス Sync FrameworkV2.1 Sync Orchestrator 双方向の差分同期 SQL Azure Provider SQL Server Sync Provider TDS Metadata Storage Metadata Storage Sync Runtime

Sync Framework V2.1 アプリを利用した拠点間同期 • SQL Azureをハブとして拠点間のDBを同期 • 1433/TCP を「内部 → インターネット」で Open すれば通信可能

SQL AzureData SyncService（Project Huron） Public Preview • SQL Azure データベース間の同期 • ポータルサイトで設定が可能（完全なコードレス） • スケジュールされた同期が可能 • Daily/Monthly/Hourly/Weekly HUB member member SQL Azure Data SyncService 手近なSQL Azure からオンプレミスに同期

【Azure for ITPRO シリーズ】 SQL Azure 管理とセキュリティ