Transcript SQL Azure の管理とセキュリティ

【Azure for ITPRO シリーズ】
SQL Azure 管理とセキュリティ
2010年10月版 V1.1
マイクロソフト株式会社
エバンジェリスト
安納 順一
http://blogs.technet.com/junichia/
1
修正履歴
修正日時
修正内容
2010/10/13
2010年10月13日時点では Azure データセンター間の as copy of パラメタによるデータベース複製はサポートされていな
いことを追記
2
Agenda & Takeaway
1. SQL Azure オーバービュー
2. データベース管理とセキュリティ
– 管理ツール
– 認証とFirewall
– 移行/同期/バックアップ
3. SQL Server との違い
4. Business Intelligence
5. まとめ
3
まずは初物から
SQL AZURE オーバー ビュー
4
SQL Azure とは？
ホスティングサービスの先へ
予定あり
Public
Preview
未定
Reporting
Services
Business
Analytics
Data Sync
Service
Community
review
Management
Portal
SQL Data Platform as a Service
 パブリッククラウド上の RDB
 既存のスキルとツールを引き継いで利用可能
 既存データベースアプリケーションにクラウド接続を提供
SQL Azure の特徴
• インストール/パッチ適用の必要が無い
• サーバー本体のメンテナンスの必要が無い
• 高い可用性と耐障害性
• シンプルな管理
• ビジネスニーズに応じたスケールアップ/ダウン
• マルチテナント
• 従来の管理ツール、開発ツールを継続して利用可能
• T-SQL ベースの RDB モデル
6
オンプレミスとのシンメトリックな関係
SQL Azure へのアクセス方法は２つ (HTTP/S と TDS)
オンプレミスのアプリケーションからはシームレスにアクセスが可能
SOAP/REST
HTTP/S
ADO.NET/REST - EDM
HTTP/S
SQLCMD
管
理
Management
Studio
App Code
(ASP.NET)
PowerPivot
BI
Windows Azure
T-SQL (TDS)
SSAS
SSIS
LOB
T-SQL (TDS)
SQL Azure
SSRS
App
AppCode
Code
T-SQL (TDS)
ADO.NET/ODBC/PHP
※SQL Azureでサポートされるのは TDS（tabular data stream）7.3以降
※OLE DB経由の接続はサポートされない
SQL Server
ちなみに TDS 以外の接続は？
• SQL Azure OData Service をサポート予定（現在 Preview）
• REST API を使用した HTTPS アクセスが可能
SQL Azure DB
OData Service
TDS
認証/承認
REST API
https
Anonymous
or
AppFabric ACS
https://odata.sqlazurelabs.com/OData.sv
c/v0.1/<sql azure host>/<dbname>
OData Javascript Library
OData SDK for PHP
Restlet extension for OData (Java)
ruby_odata: OData client library for ruby
OData Client Library for Win Phone 7
OData client 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
Server
Azure プラットフォームサービス共通のID
（Live ID）を発行
SQL Azure Server と管理アカウントを作成
• サーバー名は自動生成
<servername>.database.windows.net
1つのアカウントで1つのサーバーのみ生成可能
Database
Database
Database
データベースの作成
• 1データベースあたり1GB - 50GB
9
（参考）アカウント作成直後
まだデータベース用ノード（サーバー）は割り当てられていない
10
（参考）サーバー作成と管理カウントの作成
ここで作成したアカウントが sa 権限を持つ
11
（参考）サーバー名と master DB の割り当て
サーバー名
初期は master データベース
のみが割り当てられる
12
50GB Database サポート
• 2タイプの SQL Azure Database：
– Web Edition
:最大サイズ = 1 GB or 5 GB
• ¥ 979.02 / 最大1GB のデータベース / 月
• ¥ 4,895.10 / 最大5GB までのデータベース / 月
– Business Edition
•
•
•
•
•
¥
¥
¥
¥
¥
：最大サイズ = 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 へのアクセスは TDS Gateway を介する
ユーザー固有のmaster データベースにより認証が行われDB一覧を取得
Master クラスター
TDS GATEWAY
ログインリクエスト
ユーザーのmasterを検索
master にアクセス
UserDBにアクセス
認
証
master node 1
master node 2
Partition
Manager
Partition
Manager
Data Node
Component
Data Node
Component
データクラスター
node:Machine14
node:Machine15
fabric
fabric
SQL SV
Mgmt.
Service
SQL SV
Mgmt.
Service
node:Machine150
fabric
SQL SV
Mgmt.
Service
master データベースの役割
•
•
•
•
SQL Azure Server は 複数の物理サーバーに分散したデータベースの集合体
master データベースによりユーザーのデータベースモデルが維持される
ログイン/ロール チェックは master データベースで行われる
ログイン管理/データベース管理は master に対して行う
ログイン/ロールチェック
ログイン管理
データベース管理
master
15
自動複製とフェールオーバー
• 異なるノードに 常に 2 つの複製を持つ
– プライマリレプリカ＊１
– セカンダリレプリカ＊２
トランザクションの
コミット時に複製
プライマリ
セカンダリ
コミット優先度１
複製
複製
プライマリの障害時には
コミット優先度の高い
ほうがプライマリになる
セカンダリ
コミット優先度2
更新処理はプライ
マリで行われる
TDS Gateway
DML Operations
(insert/update/delete)
（参考）
4時間以内に、障害が発生したプ
ライマリが復旧しない場合、廃棄
されて新たなセカンダリが生成さ
れる
ロードバランス
5分に1回のチェックサイクルで最適なノードを選定
プライマリ
Load Balancer
セカンダリ
セカンダリ
いろいろ小難しいこと言ってるけど、
ぜーんぶ 勝手にやってくれるんだって！
これならデータセンターよりいいかも…
とはいえ、クラウドだって管理は必要
SQL AZURE の管理とセキュリティ
19
SQL Azure 用管理ツール
SQL Azure Portal
•
•
•
•
ブラウザーからの操作
管理ユーザーのパスワード変更
データベースの作成と削除
Firewall の設定
SQL Server Management Studio
• おなじみの管理コンソール
• SQL Server 認証で SQL Azure に接続
• 基本的なDB/テーブル操作に加えて、DB移行にも有用
SQLCMD
• SQL Server 認証で SQL Azure に接続
Project Houston（CTP1）
•
•
•
•
ブラウザーからの操作
Silverlight ベースの管理コンソール
現時点では SSMS にはおよばない…
1280 * 1024 以上の解像度がお勧め
20
SQL Azure の管理機能 概要
Scenario/Tool
SQL Azure Portal
SSMS 2008 R2
Houston CTP1
データ ベース作成/削除
○
クエリーを使用
GUI
テーブル作成/変更/削除
×
クエリーを使用
GUI
データ層アプリの抽出/登録/配置
×
ウィザード
×
データの参照
×
クエリーを使用
GUI
データの操作
×
クエリーを使用
GUI
ログインの管理
管理者のﾊﾟｽﾜｰﾄﾞ変更
クエリーを使用
クエリーを使用
ユーザーの管理
×
クエリーを使用
クエリーを使用
ﾃﾞｰﾀﾍﾞｰｽｵﾌﾞｼﾞｪｸﾄのｽｸﾘﾌﾟﾄ生成
×
ウィザード
ロールの管理
×
クエリーを使用
クエリーを使用
ストアドプロシジャーの管理
×
クエリーを使用
GUI
ビューの管理
×
クエリーを使用
GUI
スキーマの管理
×
クエリーを使用
クエリーを使用
各種ステータス参照
×
クエリーを使用
○
Firewall の設定
○
×
×
21
SQL Azure Portal
管理者パスワード
リセット
サーバーの削除
データベースの
作成/削除
22
SQL Server Management Studio
• バージョンによってサポート範囲が異なる
• サーバーへの接続には SQL Server ログインを使用する
SQL Server 2008 Management Studio
• GUI による管理はサポートされていない
• データベースに接続するたびに「接続」ダイアログを使用する
※SQL Azure では USEコマンドがサポートされていない
• 全ての操作はクエリーで実行する
SQL Server 2008 R2 Management Studio
• GUIによる操作を「一部」サポート
• オブジェクトエクスプローラーを使
用可能
• データベース作成、ユーザー作成を
GUIから実施可能
• テーブル操作はクエリーを使用
• クエリーウィンドウはデータ
ベースごとに開く必要あり
※SQL Azure では USEコマン
ドがサポートされていない
23
SQL Azure Portal と Management Studio
AQL Azure ポータル（http://sql.azure.com/)
SQL Server 2008 R2
Management Studio
SQLCMD
•
ログインには <login>@<server名> を使用する
sqlcmd -U <ユーザーID@ホスト名> -P <パスワード> -S <サーバー名> -d <DB名> -N
（注）osql コマンドは未サポート
Codename “Project Houston”
http://sqlazurelabs.com/houston.aspx
• Silverlight版 SQL Azure 用管理ツール
–
–
–
–
–
テーブル デザイナー
クエリー デザイナー
ビュー デザイナー
ストアドプロシジャー デザイナー
各種ステータス表示
CTP1
Houston ー データベースの使用量
27
Houston ー テーブルのデザイン
28
Houston ー データの操作
行の追加/削除
データを直接編集
29
SQL Azure へのアクセス経路と認証
• SQL Azure の認証は「SQL Server 認証」のみ
• フロントのエンドポイントに実装された認証方式をユーザーに公開することも
可能
SSMS/SQLCMD/BCP
ASP.NET
認
証
OData
Service
REST
SQL Server
SQL Azure
独自
認証
Application
WIF
AppFabric
ACS
WIF
ASP.NET
AD FS 2.0
AD DS
30
Firewall の設定
• インターネット
• SQL Azure
←(1433/TCP)←
←(IP Range)←
SQL Azure ポータル
レンジを設定
xxx.xxx.xxx.xxx
クライアントのIP
アドレス
オンプレミス
インターネット
SQL Azure ← ANY
•
•
•
•
1433/TCP
サーバー単位に設定が必要
反映されるまでに5分程度を要する
現在は IPv6 未サポート
最大128ルール
sys.firewall_rules_table
※ OData Service を使用することで HTTPS でのアクセスが可能
OData Service + ACS による認証/承認の拡張
• OData と ACS の連携により、より強固な認証/承認が可能に
• オンプレミス AD DS を使用してシングルサインオン
SQL Azure DB
OData Service
AppFabric ACS
ﾌｪﾃﾞﾚｰｼｮﾝ信頼
TDS
認証/承認
ﾌｪﾃﾞﾚｰｼｮﾝ
REST API
https
SWTﾄ-ｸﾝ
⑤
信
頼
送
付
②SAMLﾄｰｸﾝ発行
①ﾄｰｸﾝ発行依頼
認証
AD FS
2.0
AD DS
Open Data Protocol (OData)
Data Clients
Excel
PowerPivot
.NET Client
Silverlight
Javascript
PHP
…
Open Data Protocol (AtomPub + EDM)
Data Sources
ADO.NET
SharePoint
Analysis &
Reporting
SQL
Server
SQL Azure
Win Azure
WCF Data & RIA
Services
33
移行/同期/バックアップ
移行
1. SQL Server Management Studio
– スクリプトウィザード
– データ層アプリケーション 抽出/登録/取込み
– データインポート/エクスポート ウィザード
移行
2. BCP.EXE バックアップ
同期
3. SQL Server Integration Service（SSIS） 移行
– 接続マネージャーに「ADO.NET」を使用
4. Sync Framework 同期
– SQL Azure Data Sync Tool for SQL Server
• Power Pack for SQL Azure November CTP（32-bit）
– SQL Azure Data Sync Service（ラボ）
5. SQL Azure Data Copy バックアップ
6. SQL Server Migration Wizard（SSMW） 移行
34
SQL Server → SQL Azure データ移行ツールの概要
not supported
by Microsoft
Scenario/Tool
簡便
SSMS
スクリプト
生成
ウィザード
●
高機能
スキーマの移行
√
データの移行
√
高速移行
SSMS
SSMS
SQL Azure
データ層アプ インポート/エク Migration
リケーション
スポート ウィ
Wizard
ザード
SSIS
BCP
●
●
●
●
●
●
●
●
√
√
√
√
√
√
√
√
√
√
√
35
SMSS ー スクリプト作成ウィザード
• SQL Server から SQL Azure へのデータベース移行
SQL Server Management Studio
┗ [データベース] ノード
┗[タスク]
┗[スクリプトの生成]
• SQL Azure へのネイティブな対応
• スクリプトによる細かな制御
 データ部は Insert 文に変換されるため高速移行には向かない
• 非互換によりスクリプトが生成されないこともある
 Unsupported Transact-SQL Statements (SQL Azure Database)
http://msdn.microsoft.com/en-us/library/ee336253.aspx
36
つづき ～ SQL Azure への接続設定
37
SSMS － データ層アプリケーション（DAC)の移行
• データベース オブジェクトおよびインスタンス オブジェクトを含む
エンティティ
• データ自体は含まれない
• テーブルやビューなどを個別に移行するのではなく、まとめて
パッケージ化（DACPACK）して移行することができる
• Visual Studio 2010 を使用して DAC を開発することも可能
DACPACKを抽出する場合
DACPACKを取り込む場合
SQL Server Management Studio
┗ [データベース] ノード
┗[タスク]
┗[データ層アプリケーションの抽出]
SQL Server Management Studio
┗[<サーバー>]
┗[データ層アプリケーションの配置]
データ
スキーマ
テーブル
ビュー
抽出
.DACPACK
配置
ログイン
ストアドプロシジャ
DAC
38
DAC と UCP（Utility Control Point）
UCP（ユーティリティコントロールポイント）と DAC により、アプリケー
ションが要求する OS バージョンや SQL Server バージョンにあったイン
スタンスに DAC を展開できる
DACアプ
リの抽出
管理者
SQL Server
Management Studio
DACアプリの状
態を集中監視
DAC
DAC
DACアプ
リの開発
ユーティリティ
コントロール ポイント
（UCP）
SQL Server インスタンス群
Visual Studio 2010
データベースエンジニア
39
SSMS ー インポート/エクスポート ウィザード
SQL Server Management Studio
┗ [データベース] ノード
┗[タスク]
┣ [データのインポート]
┗ [データのエクスポート]
データのインポート
データのエクスポート
変換元 source
SQL Server
SQL Azure
変換先 destination
SQL Azure
SQL Server
• SSIS と同様のウィザードを使用
• 移行単位を細かく制御可能
• データベース全体
• 選択したテーブル
• クエリに合致した行
• データベースは事前に作成しておく
• 自動的に互換性を補完しないので
必要に応じて手動での調整が必要
• クラスター化インデックス
の追記
• データ変換の修正
40
つづき ～ SQL Azure DB の指定方法
.NET Framework Data Provider for SqlServer 編
タイムアウトでエラー
となる場合にはここで
調整
41
SQL Azure Migration Wizard
http://sqlazuremw.codeplex.com/
• Scenario
– スキーマとデータの移行
• メリット
–
–
–
–
SQL Azure 互換
バルク移行に対応
はじめに clustered index 作成を行う
スクリプトを生成してくれる
• 留意点
– データがローカルに保存される
– NOT Supported by Microsoft
42
SQL Azure のバックアップ
障害シナリオ
ハードウェア
故障
データ紛失
X
ソリューション
提供
時期
備考
自動複製機能
・常に3つのレプリカが作成される
提供中
SQL Azureに組
み込み
BCP、SSIS 等
提供中
データベースクローン機能
・ローカル→ローカル
・ローカル→別サーバー
提供中
利用者自身が実行
予定
利用者自身が設定
・ローカル→別リージョン
スケジューリング可能なバックアップ機能
• 任意の時点のバックアップからリストア
• スケジュール設定
• バックアップされたデータは読取専用
• ローカル/リモート 対応
43
BCP.EXE
バルク API を使用したデータの高速移行
SQL Azure → ローカル
bcp KENALL.dbo.KEN out c:\data.txt -c
-S tcp:xxa8lqxxxx.database.windows.net
-U username@xxa8lqxxxx -P password
ローカル → SQL Azure
bcp KENALL.dbo.KEN in c:\data.txt -c
-S tcp:xxa8lqxxxx.database.windows.net
-U username@xxa8lqxxxx -P password
-T（統合認証）は未サポート
ｰS（サーバー名）は必須
※サーバー名には「tcp:」必須
44
SQL Azure データベース クローン
• 大規模 SQL Azure データベースの高速コピー
• 利用シナリオ
– データベースのバックアップまたは移行
• 同一サーバー内
• リージョン内のサーバー間
• リージョン間
– データベースごとのロールバック
CREATE DATABASE <dest_db> AS COPY OF <source_db>
<source_db> ：ソースデーターベース
<dest_db>
：複製先データベース
データベースクローン機能（Update4 で提供済)
CREATE DATABASE sv01.db1clone AS COPY OF sv01.db1
master
DB1
clone
DB1
sv01.database.windows.net
CREATE DATABASE sv02.db1clone AS COPY OF sv01.db1
master
DB1
clone
sv02.database.windows.net
NORTH US Region
master
データセンター間は
現時点で未サポート
DB1
clone
sv03.database.windows.net
SOUTH US Region
46
SQL Azure を中心としたデータ統合シナリオ
全てのプラットフォーム間でのデータ同期が可能に
オフラインキャッシュが
有効なアプリケーション
Sync
Framework
V2.1
Sync
Framework
V3(future)
C/Sアプリ
企業内
SQL Azure
Data Sync
Service
(preview)
スマート
フォン
Sync
Framework
V2.1
企業間
47
Microsoft Sync Framework
• プロバイダー間のデータプロビジョニングを実現
• Metadata Storage により差分同期を管理
• V2.1 では SQL Azure をプロバイダーとして登録可能
SQL Azure
オンプレミス
Sync Framework V2.1
Sync Orchestrator
SQL Server
Sync
Provider
SQL Azure
Provider
Metadata
Storage
Metadata
Storage
双方向の
差分同期
TDS
Sync Runtime
48
Sync Framework V2.1 アプリを利用した拠点間同期
• SQL Azureをハブとして拠点間のDBを同期
• 1433/TCP を 「内部 → インターネット」 で Open すれば通信可能
49
SQL Azure Data Sync Service（Project Huron）
• SQL Azure データベース間の同期
• ポータルサイトで設定が可能（完全なコードレス）
• スケジュールされた同期が可能
• Daily / Monthly / Hourly / Weekly
Public Preview
HUB
member
SQL Azure
Data Sync
Service
member
手近なSQL
Azure からオン
プレミスに同期
50
SQL SERVER との違い
51
SQL Server との管理方法とセキュリティの違い
違いのポイント
サーバーレベルセキュリティ
の設定場所
サーバーレベルのロールを管
理できるユーザー
SQL Server
SQL Azure
Management Studio の「セキュリ
Masterデータベース内の「users」
ティ」フォルダ
securityadmin
loginmanager
ログインユーザーを管理する
ためのコマンド
CREATE LOGIN
ALTER LOGIN
DROP LOGIN
CREATE LOGIN
ALTER LOGIN
DROP LOGIN
※パラメタに若干の違いあり
※Master DBで実行する必要あり
データベース作成権限を持つ
サーバーレベルのロール
dbcreator
dbmanager
SQL Azure ポータル
CREATE DATABASE
※パラメタに若干の違いあり
※Master DBで実行する必要あり
DROP DATABASE
※パラメタに若干の違いあり
※Master DBで実行する必要あり
データベースの作成
CREATE DATABASE
データベースの削除
DROP DATABASE
ログイン一覧の参照
sys.sql_logins ビュー
sys.sql_logins ビュー
※master DBで実行する必要あり
データベース一覧の参照
sys.databases ビュー
sys.databases ビュー
※master DBで実行する必要あり
52
loginmanager と dbmanager
loginmanager ロール
ログインアカウントを作成する権限を持つロール。SQL Serverで言うとこ
ろの securityadmin ロールに相当する。
ログインアカウントを管理するには、masterデータベースで実行する必要
がある。
dbmanager ロール
データベースを作成する権限を持つロール。SQL Server 2008/R2 の
dbcreator ロールに相当する。
データベースを管理するには masterデータベースで実行する必要がある。
SQL Azure Server プロビジョン時に作成した管理アカウントは両方の
ロールを持っている
SQL Azure では USE コマンドがサポートされていないため、master
データベース接続後に実行しなければならない
53
（参考）T-SQL 文法の違い
詳細は以下を参照してください
Transact-SQL Reference (SQL Azure Database)
http://msdn.microsoft.com/ja-jp/library/ee336281(en-us).aspx
Supported T-SQL Reference
http://msdn.microsoft.com/en-us/library/ee336267.aspx
Un-Supported T-SQL
http://msdn.microsoft.com/en-us/library/ee336253.aspx
（例）
SQL Azure
CREATE DATABASE database_name [(MAXSIZE = [1 | 10] GB )] [;]
SQL Server
CREATE DATABASE database_name
[ ON
[ PRIMARY ] [ <filespec> [ ,...n ]
[ , <filegroup> [ ,...n ] ]
[ LOG ON { <filespec> [ ,...n ] } ]
]
[ COLLATE collation_name ]
[ WITH <external_access_option> ]
]
[;]
54
（参考）サポートされているデータ型(2010/09時点）
詳細 http://msdn.microsoft.com/en-us/library/ee336233.aspx
カテゴリ
SQL Azureでサポートされるデータ型
真数（Exact numerics）
bigint, bit, decimal, int, money, numeric,
smallint, smallmoney, tinyint.
概数（Approximate numerics）
float, real.
日付と時刻
date, datetime2, datetime,
datetimeoffset, smalldatetime, time.
文字列
char, varchar, text.
Unicode文字列
nchar, nvarchar, ntext.
バイナリ文字列
binary, varbinary, image.
空間型
geography, geometry
その他のデータ型
cursor, sql_variant, table, timestamp,
uniqueidentifier, xml, hierarchyid
55
Data Access APIs
• Supported APIs
Supported
Unsupported
ADO.Net .Net 3.5 SP1 and 4.0
ODBC - SNAC 10
Entity Framework .Net 3.5 SP1 and 4
SQL Server 2008 Driver for PHP v1.1
OleDB
• 接続文字列の注意点
•
•
ADO.Net
• Encrypt=True
• ユーザーIDには @servername を付加する
ODBC
• Encrypt=yes
• Uid に @servername を付加する
• プロトコル
Supported
Unsupported
TCP/IP over port 1433
動的ポート/名前付きパイプ/共有メモリ
• 認証
Supported
Unsupported
SQL Server 認証
Windows 統合認証
BUSINESS INTELLIGENCE
57
Business Intelligence 機能
• オンプレミスからSQL Azure をデータソースとして利用
– PowerPivot for EXCEL
– SQL Server Reporting Service
– SQL Server Analysis Service
• クラウド上でSQL Azure をデータソースとして利用
– Using the Report Viewer Control with SQL Azure
• http://blogs.msdn.com/b/sqlazure/archive/2010/08/17/10
051010.aspx
– SQL Azure Reporting Service 予定あり
58
PowerPivot for Excel 2010
SQL Azure 上のデータ ベースを分析ソースとして利用可能
オンプレミス
ローカルPC
SQL Azure
SQL Server 2008 R2
など
CSV
CUBEの作成
PowerPivot
ピボットテーブル
EXCEL 2010
59
OData Service + PowerPivot for EXCEL
外向けに TDS（port 1433) が公開されていない環境では、OData
Service を使用した HTTPS 通信が可能
SQL Azure DB
OData Service
TDS
REST API
https
Atom フィード
としてデータを
取り込む
60
まとめ
61
まとめ
SQL Azure の登場により
• あきらめていたことが実現できます
• 従来の管理手法/考え方を継承できます
• 少しの違いだけ覚えてください
システム管理者はこらからも
頼られる存在であり続けます！
62
63