SQL Server 開発チーム ブログ

SQL Server 2008 CQI 書籍 (MS-Press) 出版

マイクロソフトの星川です。

SQL Server 2008リリース時に日本独自にパートナー様と共同実施したCQI (Center of Quality Innovation) の成果がホワイトペーパーだけでなくさらなる有益な情報を加えて書籍となりました。日経BPソフトプレス様より順次出版とのことですので、書店等でぜひご覧いただければと思います。技術者の方にはじっくり読める書籍もまだまだ重要と位置づけております。

CQI 徹底検証シリーズにデータウェアハウスシナリオのホワイトペーパーが追加されました

SQL Server 2008 データウェアハウスシナリオ Tips and Tricks Part 3

マイクロソフトの植田です。 

引き続き「大規模データウェアハウス」シナリオ検証で得られたTipsや注意点についてご紹介していきたいと思います。

今回は「緩やかに変化するディメンジョン」への対処方法について説明したいと思います。

簡単に「緩やかに変化するディメンジョン」とは何かについて触れたいと思います。DWHシナリオでは、ディメンジョンの多くが時間の経過に伴って変化します。多くの場合それらの変化はファクトデータの変化に比べて緩やかで、徐々に起きる変化を反映するディメンジョンのことを、「緩やかに変化するディメンジョン」と呼びます。この時、ディメンジョンテーブルに生じた変化にどのように対処するか、によっていくつかのパターンがあります。主なパターンは以下に分類されます。例えば以下のようなレコードがあり、場所の移転に伴って店舗名称を「代々木店」から「新宿店」に変更するケースについて各パターンを考えてみます。

店舗テーブル

1.      タイプ1

変更があったデータを、新しいデータで上書きする方法です。変更後のレコードは以下のようになります。

2.      タイプ2

変更前と変更後の両方のデータを保持し、レコードの有効/無効を判断する列を追加する方法です。レコードの有効フラグ列を追加するパターンと、レコードの開始/終了日時を追加するパターンがあります。以下の例では、開始/終了日時の列に加え、一意性を表す代替キー（サロゲートキー）の列が追加されています。

3.      タイプ3

変更のあったデータの履歴をレコード内に保持し、変更があった日付を追加する方法です。レコードのサイズにより、保持できるデータ履歴の数が制限されます。複数の列が更新されるシナリオではレコードサイズが大きくなります

4.      タイプ4

ディメンジョンテーブルは新しいデータで更新し、変更のあったレコードの履歴を別のテーブルで管理する方法です。このようにすると、保持できる履歴データの制限がなくなります。

店舗テーブル

店舗履歴テーブル

この他、全く対処しない場合（タイプ0）や、タイプ1, タイプ2, タイプ3を組み合わせたタイプ6（ハイブリッド）と呼ばれるものがあります。どのタイプを使用するべきか、についてはビジネス要件に依存します。そして、変化するディメンジョンへの対処方法によって、ディメンジョンテーブル、および、ファクトテーブルの設計に影響があります。例えばタイプ2を使用する場合はディメンジョンテーブルに、代替キーの列、および、開始/終了日時の列を追加する必要があります。加えて、ファクトテーブルのデータについても代替キーに変更する必要があります。上記の例では、ファクトデータの「店舗コード」を「店舗ID」で置き換える必要があります。

SQL ServerのIntegration Servicesを使用すると、上記のディメンジョンテーブルの変更をウィザードを利用して設定することができます。また、ファクトテーブルにおける代替キーとビジネスキーの変換も、「データフロータスク」において行うことが可能です。これらの設定方法に関する詳細は、公開予定のホワイトペーパーにて説明する予定です。今回、検証したシナリオでは変化するディメンジョンに対して、タイプ1とタイプ2を組み合わせたハイブリッド方式を採用しました。実装に関してはホワイトペーパーにて詳しく説明させて頂いているので、ここではどのようなビジネス要件が満たされるのか、簡単な例を見ていきたいと思います。

例えばファクトデータとして以下のようなレコードがあるとします。

売上テーブル

ディメンジョンを表すテーブルとして前述の店舗テーブルを使用する場合、テーブルスキーマは以下のようになります。

店舗テーブル

上記のファクトデータを店舗ごとに集計する場合、移転があった店舗をどのように集計するかが問題となります。分析観点により複数の集計方法がありますが（例えば、移転の前後で別々の店舗として売り上げを集計する、または、同一の店舗とみなして集計する）、最も多い要望は、「移転の前後で分けた場合と、同一店舗とみなした場合の両方の集計を行う」というものです。この要望は上記の店舗テーブルの「店舗名称（現）」、「店舗名称（履歴）」をディメンジョンとする事で実現できます。

集計例

どのような観点に基づいてファクトデータを分析するかによって他にも方法があると思いますが、ここでは一例を取り挙げさせていただきました。ディメンジョンの変更に対して、どの方法を用いるのが良いかはディメンジョンのデータ特性、分析観点によると思いますが（例えば、顧客テーブルにおける名前の変更は集計に影響を当たる可能性が小さいのでタイプ1で処理する等）、今回説明させていただいた内容が何かの参考になれば幸いです。

Windows Live Translator について

マイクロソフトの星川です。

インターネットの普及とともに情報は世界レベルで発信され、特にITにおける新しいテクノロジー、モデルなどの情報は英語で記述される場合が多いのが現状です。我々はできる限り日本語での情報をオリジナルからの作成を含め、有効な情報をセレクトして翻訳・レビュー等の後提供しているのですが、やはりそのプロセスの間には人が入っており、コンテンツの品質を保つためには時間とコストがかかります。

タイムリーな情報収集には原文をできる限り読むことをお勧めしますが、英語が得意でない方は、Windows Live Translator という翻訳ツールをご利用いただくとよいかもしれません。このツールには Side-by-Sideで2つの言語を同時に表示可能な機能があり原文を比較しながら読み進めることができます。ページはダイナミックに読み込まれ、少し反映するまでに時間を要しますが、随時翻訳されたテキストに変更されていきます。選択したセンテンスをハイライト表示もできます。個人的な感想ですが、英語から日本語の自動翻訳は言語的構造がその他の欧米諸国の言語と比べて複雑なためまだまだ発展途上といえるでしょう。特に文脈や前後関係が不明な場合などは不自然で理解困難な日本語を返してきたりします。このあたりはページ内のFAQ (一部引用) を一読することをお勧めします。

SQL Server 2008 CQI 徹底検証シリーズ　ホワイトペーパー追加のお知らせ

マイクロソフトの笹瀬です。

SQL Server 徹底検証シリーズ、ご活用されていますか？

弊社では、SQL Server の製品出荷前から、各機能の多岐にわたる検証作業をフィールドから集めたシナリオをベースに行っています。

この検証作業は、CQI（Center of Quality Innovation）というプロジェクトで、マイクロソフト調布技術センター (東京都調布市) にて、マイクロソフトの製品開発担当部門、SE 部門、コンサルティング部門、サポート部門の各担当者およびマイクロソフトのパートナー各社との共同プロジェクトとして実施されています。

その検証結果をホワイトペーパーにまとめ、「SQL Server 2008 徹底検証シリーズ」として公開させていただいております。

既にご好評をいただいている 「コンプライアンス実現のためのガイドライン」に加え、新たに以下の３種類のホワイトペーパーを追加いたしました。

l  データベース サーバー統合実践ガイド

l  ISMS/ISO27001 コンプライアンスのためのガイドライン

l  旧バージョンからの移行とアップグレード実践ガイド

「データベースサーバー統合実践ガイド」は、分散されたデータベースサーバー環境を SQL Server 2008 に統合するための実践ガイドです。データベース サーバー統合を三種類の手法で解説しています。関連する SQL Server の各機能、性能測定の結果を解説しながら各手法のメリット、デメリットも解説しています。本シナリオは、日本ヒューレット・パッカード株式会社(日本HP)殿と共同検証を行い、その結果をホワイトペーパーにまとめたものです。一方で、現在、日本HP殿で性能測定の検証結果を詳細にまとめたホワイトペーパーを作成中です。

「ISMS/ISO27001 コンプライアンスのためのガイドライン」は、SQL Server 2008 を利用したデータベース運用環境において、情報セキュリティのマネジメント サイクルを確立し、ISMS (ISO27001) コンプライアンスを実現するためのガイドです。ISMS 規格 (ISO/IEC 27001:2005) からデータベース運用に深く関わる 31 の管理策を選んで、管理策ごとに ISMS 運用の Plan (計画)、Do (実行)、Check (評価)、Act (改善) を実現するための手順を解説しています。なお、長年 ISMS 運用に取り組まれてきた「東芝セミコンダクター社」様における SQL Server 2008 のセキュリティ運用事例も掲載しています。

「旧バージョンからの移行とアップグレード実践ガイド」は、SQL Server 2000、SQL Server 2005 で構築されたシステムを SQL Server 2008 へ移行またはアップグレードするための実践ガイドです。データベースエンジン、Analysis Services, Integration Services、Reporting Services の旧バージョンからの移行手順、性能の比較などを解説しています。

本シナリオは、日本ユニシス株式会社殿と共同検証を行い、その結果をホワイトペーパーにまとめたものです。

いずれも約数か月の期間、社内の各部門とパートナー様でバーチャルチームを編成して、検証と議論を重ねた結果をホワイトペーパーにまとめました。

ぜひ、ご活用いただければと思います。

まだ、 「大規模データ ウェアハウス実践ガイド」の作業が若干残っていて、現在、最後のレビューを行っているところです。

近日公開できると思いますので、こちらもご期待ください。

笹瀬

暗号化されたデータベースとログ配布、ミラーリング、クラスタリング

マイクロソフトの平山です。

前回、前々回と続けてきた SQL Server 2008 からの新機能「透過的なデータ暗号化」を、他の機能の組み合わせて使うときの注意点を紹介するというテーマの最終回です。
今回は「ログ配布」、「データベースミラーリング」、「フェールオーバークラスタリング」を取り上げてみます。

それぞれ機能の基本的な動作を考えてみると、注意点や必要な事前準備を推測することができると思います。

まず「ログ配布」について考えてみます。
通常の場合は初期同期のための作業として、プライマリデータベースをセカンダリサーバーに復元します。
その際に、プライマリデータベースが暗号化されていると、セカンダリサーバーへの復元が失敗してしまいます。

次に「データベースミラーリング」の場合です。
ミラーリングの設定の事前準備として、プリンシパルサーバーのデータベースをミラーサーバーに復元しておく必要があります。
その際に、プリンシパルサーバーのデータベースが暗号化されていると(ログ配布の場合と同様に)、ミラーサーバへの復元時にエラーが発生してしまいます。

さて、この問題を回避するには、どうすればよいでしょうか?
その答えは前々回のエントリで紹介してあります。それぞれの場合の事前準備として、「ログ配布」の場合はセカンダリサーバーで、「データベースミラーリング」の場合はミラーサーバーで、サービスマスターキーの作成と証明書の復元を行っておく必要があります。
それ以外は、通常の設定方法と同じです。

最後に「フェールオーバークラスタリング」について紹介します。
フェールオーバークラスタリングが構成されている SQL Server インスタンスに暗号化されているデータベースが存在したとしても、特に事前準備はありません。
クラスタリング環境では、複数のノードのうちのいづれかひとつのノードで SQL Server インスタンスが稼働することができます。しかし、実体としてのデータベース (ユーザデータベースおよびシステムデータベース) は複数のノード分存在するのではなく、全ノードで共有しています。
そのため、サービスマスターキーや証明書も、どのノードで SQL Server インスタンスが起動しても同じものを使用していることになります。
ということで、事前に特別な考慮をすることなくクラスタ環境で暗号化されたデータベースを使用することができます。

SQL Server Team および個人の Blog List

マイクロソフトの星川です。

前回のブログで記述しましたが、以下社員でチーム or 個人単位で運営するSQL Serverに関するブログのリストです。ほとんどがUS本社開発チームからの情報発信ですが、それ以外の部署やUS以外のチームもあります。ブログという性質上今回発見できなかったり、今後増えたり、何らかの理由でしばらく更新が止まっているものもあると思いますのでご了承ください。URLのほかにタイトルと補足情報を追加させていただいております。個人のブログに関してはトピックがさまざまなため実際に見ないとわからないかも知れませんが、参考にしていただければと思います。ブログはそれぞれの人間のカラーが出ますので、本当に社内にも幅広くいろいろなチーム・人がいるなと実感してます。

暗号化されたデータベースとレプリケーション

マイクロソフトの平山です。
「透過的なデータ暗号化」を使用して暗号化されたデータベースの使い勝手を追求するシリーズの 2 回目の今回は、レプリケーションとの関連を紹介します。

レプリケーションでパブリッシュしようとするデータベースが暗号化されていても、サブスクライバへレプリケートを行うときに必要となる情報は、暗号化されていない状態で distribution データベースに格納されています。
つまり、レプリケート実行時にデータを復号化する必要がないため、サブスクライバ側でのサービスマスターキーの作成や証明書のリストアは必須ではありません。また、この動作はいずれレプリケーションのタイプにも当てはまります。

といった前提のもとに、暗号化されたデータベースをレプリケートする場合の注意点は次のようになります。

注意点 1:
パブリッシャデータベースが暗号化されていたとしても、サブスクライバデータベースが自動的に暗号化されることはありません。必要に応じて個別に暗号化を行ってください。
サブスクライバの暗号化の際には、必ずしもパブリッシャと同じ証明書を使用する必要はありません。

注意点 2:
パブリッシャデータベースが暗号化されていても、スナップショットレプリケーションや他のタイプのレプリケーションの初期化時に使用されるデータベースの初期スナップショットは暗号化されていません。つまり、ネイティブ形式のファイルとして出力されていても、バイナリエディタなどで展開されてしまうと、ファイルの内容をのぞき見られてしまう危険性があります。
そのため、スナップショットファイルを配置するフォルダの権限を適切に管理することで対処する必要があります。

注意点 3:
レプリケーションに関連するサイト間の通信を、より堅牢にするためにパブリッシャ、ディストリビュータ、サブスクライバ間の伝送経路の暗号化を検討してください。

SQL Server チームが運営する Blog お勧めリスト

マイクロソフトの星川です。

最近社内の world-wideのコミュニティにてSQL Server に関するブログ リストをまとめてくれた人がおり、マイクロソフト社員からSQL Server をメインで情報発信しているブログの数は個人を含めるとなんと60程ありました。後日、アクティブな状態を確認できたリストを公開したいと考えておりますが、今回はその中で個人的にお勧めのものをピックアップしたいと思います。

·         Microsoft SQL Server Development Customer Advisory Team (SQLCAT)

http://blogs.msdn.com/sqlcat/, http://sqlcat.com

技術系の中でDeepで常にお客様視点で活動を続ける SQLCATメンバーが運営しているブログとそのポータルです。メンバーはNASDAQに代表される大規模データベース構築を10年以上経験しているなど社内でも有名な技術者で構成されており、彼らのノウハウが集積されている Best Practice 関連の情報はお勧めです。いくつかの Best Practice Testingや最近ではTop10リストなど人気の高いコンテンツの日本語化と技術レビューに我々のチームが参加しております。日本にも頻繁に来てくれます。

·         Microsoft SQL Server News Blog - Data Platform Insider

http://blogs.technet.com/dataplatforminsider/ Microsoft's Data Platform official news

マーケティング チームが運営しているブログです。製品開発チームと常に密接に動いているチームです。技術系ではないですが、製品アップデート、イベントやプレスリリースなどに関連する情報が入手可能です。

·         Microsoft PSS SQL Support

http://blogs.msdn.com/psssql/ This is the official team Web Log for Microsoft PSS SQL Support. Posts are mostly provided by the PSS SQL Escalation Services team.

サポートチームによるブログで、主に Escalation Engineer という技術的にトップクラスのメンバーがポストしております。サポートポリシーの情報やトラブル シューティングなどの技術情報がアップされます。

その他USでの SQL Server のブログ リスト

http://technet.microsoft.com/en-us/sqlserver/bb671052.aspx

日本におけるSQL Server 最大コミュニティであるPASSJブログ

http://blogs.sqlpassj.org/

他製品も含む、マイクロソフト日本法人のブログ リスト

http://www.microsoft.com/japan/communities/blogs/default.mspx

SQL Server ベストプラクティス 日本語版　Top 10 リストが追加されました（sqlcat.com）

暗号化されたデータベースを別インスタンスに復元する方法

マイクロソフトの平山です。これから数回にわたり「透過的なデータ暗号化」を使用して暗号化されたデータベースの使い勝手について紹介していきたいと思います。 

SQL Server 2008 から導入された、「透過的なデータ暗号化」のメリットのひとつとして、データベースの物理ファイルやバックアップが流出したり盗難にあってもデータをのぞき見されることがない、ということが挙げられます。それは次のような理由によります。

• データベース全体が暗号化されるため、流出したファイルをバイナリエディタなどで展開しても、ファイルの中身を理解することができない
• データベースの復元(あるいはアタッチ)には証明書が必要なので、バックアップファイルのみが流出しても不正にリストアされてしまうことはない

SQL Server オンラインブックなどのドキュメントには、データベースを暗号化するための方法は親切に解説してありますが、暗号化したデータベースを復元する方法をわかりやすくまとめている箇所がありません。そこで今回のエントリでは、暗号化されたデータベースを別インスタンスに復元する方法を紹介したいと思います。

●まずはデータベースの暗号化までのおさらい

1. データベースとテーブルを作成します
create database TDE_DB
go
use TDE_DB
go
create table t1 (c1 nvarchar(500))
go
2. サービスマスターキーを作成します
use master
go
create master key encryption by password = 'secret!masterKey1'
go
3. 証明書を作成します
create certificate TDE_TEST with subject = 'Test Certificate'
go
3. データベース暗号化キーを作成します
use TDE_DB
go
create database encryption key with algorithm = AES_256
encryption by server certificate TDE_TEST
go
4. データベースを暗号化します
alter database TDE_DB set encryption on
go
 

●続いてデータベースと証明書のバックアップ

1. データベースのバックアップ
backup database TDE_DB to disk='C:\TDE_DB.bak'
go
2. 証明書のバックアップ
backup certificate TDE_TEST to file = 'C:\TDE_TEST.cer'
with private key (file = 'c:\TDE_TEST_KEY.pvk',encryption by password = 'secret!masterKey1')
go

●いよいよ別インスタンスへの復元です。データベースと証明書のバックアップファイルは復元先のマシンにコピーしておいてください。

1. 復元先のインスタンスでも独自のサービスマスターキーを作成しておく必要があります
create master key encryption by password = 'tdetest!tdetest1'
go
2. データベースの復元に先駆けて、証明書を復元します
create certificate TDE_TEST
from file = 'c:\TDE_TEST.cer'
with private key (file = 'c:\TDE_TEST_KEY.pvk', decryption by password ='secret!masterKey1')
go
3. これで別インスタンスへのデータベースの復元が可能になります
restore database TDE_DB from disk='C:\TDE_DB.bak'
go

ちなみに上記の手順ではなく、単にデータベースの復元のみを行うと次のようなエラーが発生します。

メッセージ 33111、レベル 16、状態 3、行 1
拇印 '0xC31E2828C900C230B2BC1E9BEBE5B66DCAB9EFXX' でサーバー 証明書 が見つかりません。

また、データベースの復元の際にとても重要な意味を持つ、サービスマスターキーのパスワード、証明書のバックアップの管理には細心の注意を払うようにしてください。

SQL Server 2008 データウェアハウスシナリオ　Tips and Tricks Part 2

I would like to introduce briefly how SSIS 2008 have achieved great performance on loading TB data into SQL Server database to Japanese users. Here is Japanese SQL Server development team’s blog…

マイクロソフトの植田です。 

前回のポストに引き続き「大規模データウェアハウス」シナリオ検証で得られたTipsや注意点についてご紹介していきたいと思います。

今回はSQL Server Integration Serviceを使用したデータロードを取り上げたいと思います。データロードを行う局面はいろいろあると思いますが、ここでは初期ロードのパフォーマンスについてお話したいと思います。

米国のSQL Server パフォーマンスチームが今年の初めに行った検証では、わずか30分の間に1.18TBものデータをロードしたという結果が公開されています。

http://blogs.msdn.com/sqlperf/archive/2008/02/27/etl-world-record.aspx　

主なHW構成は以下のようになっています。

ü  RDBMSサーバー（1台）

Ø  Dual-coreのCPUを32機搭載（64コア）、256GB RAM

Ø  8ノードのハードウェアNUMA構成（1ノードあたりCPU8コア、32GB RAM）

Ø  各ノードが4GbitのDual-portのHBA、Giga-NICを搭載

Ø  DB用ストレージには8スピンドルでRAID10を組んだLUNを17個使用

ü  SSISサーバー（4台）

Ø  Quad-coreのCPUを2機搭載（8コア）、4GB RAM

Ø  4Gbit Single-portのHBA×1、Giga-NIC×2

Ø  フラットファイル用のストレージとして1GbitのFibre Channelで接続されたSANを使用

1TB、30分を実現するポイントは以下と考えています。

1.      CPUコア数に応じて最適な同時実行多重度を決定し、CPUの利用効率をできるだけ高くする

2.      SSISパッケージを別筐体で実行し、CPU負荷を分散させる

3.      パーティション分割したテーブルを使用し、データロードをパーティション単位で行う

4.      SSISサーバーと各NUMAノードを専用のネットワークで結び（1つのSSISサーバーは2つのNUMAノードに接続）、ノードの8つのCPU(コア)の内、1つをネットワーク処理専用に割り当て、ネットワーク負荷を軽減

Ø  Soft-NUMAを構成し、1つのパーティションに対するデータロードが1つのCPU(コア)で行われるように設定する

ディスクの性能が低くない限り、一括データロードはCPUに負荷がかかる処理となります。一般的に、一つのデータロードプロセス（インスタンス）を一つのCPUで実行させるようにするのがベストプラクティスと言われています。上記の検証では56個のパーティションを用意し、56個のロードプロセスを同時に実行することにより最高のパフォーマンスを得ていました。64プロセスでない理由は、上記4に書かれているとおり、ノードあたり1つのCPU(コア)をネットワーク処理専用に割り当てているからです。また、SQL Server 以外にSSISパッケージを実行するDTExecのプロセスもCPUを消費します。従ってデータベースを保持するサーバーと、データロードを行うサーバーを分けた方がSQL Serverにより多くのCPUリソースを割り当てることができます。

データベースを保持するSQL Serverが動作するサーバーと、データロードを行うサーバーを別筐体に分けた場合、その間を結ぶネットワークがボトルネックになるケースが多く見られます。今回の検証では8つのネットワークインタフェースから効率的にデータが流れてくるように設計し、各ノードで1つのCPU（コア）がネットワーク処理（NICからの割り込み処理）を専門で行うように設定されています。この設定はWindows Server 2008、および、Interrupt-Affinity Policyツールを使用して行うことができます。Interrupt-Affinity Policyツールの詳細についてはWindows Hardware Developer CentralのWebサイトをご参照ください：Windows Hardware Developer Central。また、その他のCPU（コア）は同一ノード内のネットワーク処理専用CPU（コア）が受け付けたリクエストを処理するようにSoft-NUMAが設定されています。このように設定することにより、ネットワーク処理からローディング処理までを同一ノード内に局所化でき、オーバーヘッドが最小限に抑えられます。

Soft-NUMAの設定例

（CPUへのNUMAノードのマッピング）

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node0]

"CPUMaks"=hex:01

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node1]

"CPUMaks"=hex:02

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node2]

"CPUMaks"=hex:04

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node3]

"CPUMaks"=hex:08

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node4]

"CPUMaks"=hex:10

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node5]

"CPUMaks"=hex:20

                   ：

                   ：

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node62]

"CPUMaks"=hex:00,00,00,00,00,00,00,40

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node63]

"CPUMaks"=hex:00,00,00,00,00,00,00,80

（TCP/IPポートへのNUMAノードのマッピング）

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.STAB1300_04\MSSQLSERVER\SuperSocketNetLib\Tcp\IPAll]

"TcpPort"="2000[0x00000001],2001[0x00000002],2002[0x00000004],2003[0x00000008],2004[0x00000010],2005[0x00000020],

…

                   2061[0x2000000000000000],2062[0x4000000000000000],2063[0x8000000000000000]"

"TcpDynamicPorts"=""

"DisplayName"="Any IP Address“

Soft-NUMAの設定方法の詳細についてはオンラインブックをご参照ください。

ソフトNUMAを使用するようにSQL Serverを構成する方法

NUMAノードにTCP/IPポートをマッピングする方法

上記のように設定することで、SSISパッケージにおいて接続先のTCPポートを指定することにより、データロードプロセスを実行するCPUを選択することができます。

SSISデータロードパッケージ実行例

(IPアドレス10．0．0．1のポート2001に接続)

DTExec.exe /Conn

ConnectionString;"Data Source=10.0.0.1,2001; Initial Catalog=TPCHdestination; Provider=SQLNCLI10.1; Integrated Security=SSPI; PacketSize=32767;AutoTranslate=False;"

/F "C:\SSISpackages\DataLoadStream.dtsx"

以上が1TBのデータを約30分でロード完了するためのポイントになりますが、これらの設定は今回のデータロードに特化してオプティマイズされており、実際の環境に適応するのはそれほど容易ではありません。また、インデックスの作成など、実際の運用では不可欠と思われる項目もカバーされていません。私たちが日本で行ったDWHの検証では構成を単純にし、データベースサーバーには4Way（Quad-core=16コア）の汎用的なマシンを使用して、SSISとSQL Server DBエンジンを同居させる構成でデータロードパフォーマンスを測定しています。詳しい内容については今後公開されるホワイトペーパーの中で扱う予定ですが、簡単に結果だけお伝えすると、1.12TB（12億件）のデータをロードする処理を97分で完了することができました。1秒あたりのパフォーマンスに換算すると約201KB/sec、約207,000件/secとなります。また、3つのクラスタ化インデックスを作成してデータロードを行う場合は、146分で全ての処理を完了することができました。

以上ご参考にしていただければ幸いです。

次回は「緩やかに変化するディメンジョン」を取り上げたいと思います。

コミュニティにおけるマイクロソフト社員による発言やコメントは、マイクロソフトの正式な見解またはコメントではありません。

Hyper-V 環境における SQL Server 2008 のベストプラクティスとパフォーマンスの推奨事項

マイクロソフトの星川です。

仮想化技術は現在の ITトレンドの中で非常に注目度が高く、その適用範囲もデスクトップ、サーバー、ネットワーク、ストレージと非常に幅広いです。様々なシナリオのひとつであるサーバー統合を例にとっても、管理コストの削減だけでなく、電力および冷房にかかるコスト削減につながります。また、開発・テストのシナリオは我々のような開発チームに効率化をもたらしてくれます。特に、最近は弊社から Windows Server 2008 Hyper-V をリリースし、SQL Server とHyper-Vを組み合わせた場合、実際のパフォーマンスはどうですか、という質問を受けるようになってきました。この疑問に対応するお勧めの技術ドキュメントが先日 US CAT Best Practice Testing チームから公開されたので紹介させていただきます。我々のチームと日頃から関係の深い部署になります。Appendixを含まなければ約30ページのボリュームで、現在ドキュメントの日本語翻訳を関係部署と進めております。今回は、参考までにイントロ部分の日本語翻訳を記述させていただきました。

SQL Server 2008 データウェアハウスシナリオ　Tips and Tricks Part 1

ご無沙汰しております、マイクロソフトの植田です。 

最近まで、SQL Server 2008のシナリオ検証に長らく携わっておりましたがようやく落ち着きましたので、これから何回かにわたって検証の中で得られたTipsや注意点などについてお伝えできれば、と考えております。今回行われました検証の結果はすべて、以下のサイトで公開させていただく予定ですので、ここではドキュメントの中では詳しく紹介できていない注意点などについて綴っていきたいと思っています。

SQL Server 徹底検証シリーズ

私が主に参加していたのは「大規模データウェアハウス」シナリオですので、そのシナリオに関連したトピックをご紹介していく予定です。

今回は導入として、テストデータベースの構成などについてご紹介したいと思います。検証で使用したデータベースは以下のテーブルで構成されています。

※非クラスタ化インデックスは除外しています。サイズはデータ圧縮がかかっていない状態です。

大規模データウェアハウスを表す主な特徴としては以下になります。

ü  ファクトテーブルの行数は24億件、サイズは約2TB

ü  ファクトテーブルは月単位でパーティション分割されている

ü  ファクトテーブルは10年分のデータを保持し、1か月（1パーティション）あたりの行数は約2000万件

大規模なテーブルをパーティション分割する利点はいくつかありますが、特に以下の2点が挙げられます。

1.      データ管理における自由度が向上する

2.      メンテナンス作業の効率化

1については、例えば一つのパーティションに1つのファイルグループを対応させることにより、パーティション分割されたテーブルの物理的なデータ配置を制御できます。典型的な例として、日時データをキーとしてテーブルをパーティション分割し、時間が経過して古くなったデータはファイル単位で移動や圧縮（シュリンク）、または、削除(*)することによりディスクスペースを制御する、と言ったシナリオが挙げられます。今回の検証ではストレージシステムを2つ用意し、最近5年分のデータはメイン（高性能）のストレージに保持し、それよりも古くなったデータはアーカイブ用のストレージに移動させるシナリオをテストしました。ホワイトペーパーの中では、データベースをオンラインのまま、データファイルを移動させる方法について説明させていただく予定です。

(*)パーティションスイッチを持いてパーティションのデータだけを削除した後にファイルを削除する

2に関しては、データベースのバックアップ/リストアや、SQL Server 2008から導入されたデータ圧縮の処理をパーティション単位で行うことができる点が挙げられます。データベース全体ではなく、パーティション単位でバックアップ/リストアを行うので、当然処理時間は短くなります。また、リストアに関しては、リストア対象のパーティション以外はオンラインで行えますので、特定のパーティションのみの復旧作業であれば、データベース全体をオフラインにする必要はない、などのメリットがあります。

データ圧縮はディスクの使用量を削減する素晴らしい機能ですが、システムの利用のされ方によってはパフォーマンスに影響を与える可能性があります。具体的には更新処理が多くなる場合はオーバヘッドが発生することが予想できます。テーブルがパーティション分割されている場合、データ圧縮を使用することによりメリットが得られる可能性が大きくなります。例えば、テーブルのパーティションの中で、頻繁に更新される領域と、頻繁に参照されるがあまり更新されない領域がある場合、更新されるパーティションにはデータ圧縮を用いず、更新があまり行われないパーティションにはデータ圧縮を適応する、と言った運用方法が考えられます。今回の検証では、最新月のパーティション以外は更新されないため、最新月以外のデータが保持されているパーティションにはデータ圧縮を行うシナリオをテストしました。

以上大規模なテーブルをパーティション分割するメリットについて説明してきました。最後に、パーティション単位でのメンテナンスにおける注意点を１つ紹介して今回のポストを締めたいと思います。

SQL Server 2005 SP2 以降からSQL Server 2008にUpgradeし、1つのパーティションを一つのファイルグループに割り当て、ファイルグループ単位でバックアップを行う運用をしている際には注意が必要です。データベースのバックアップを取得する際、事前にデータベースの整合性をDBCC CHECKDBでチェックする運用が一般的ですが、SQL Server 2005のSP2以降から、DBCC CHECKFILEGROUPによって、複数のファイルグループにまたがってパーティション分割されたテーブルに対して、指定ファイルグループのみにおける整合性チェックが行えるようになりました。詳しくはSQL Server 2005 Books Onlineをご参照ください：DBCC CHECKFILEGROUP（Transact-SQL）。この機能強化により、パーティション単位でバックアップをとる場合、データベース全体の整合性をチェックするのではなく、該当のパーティションが使用するファイルグループのみDBCC CHECKFILEGROUPを行うことにより、整合性チェックの時間を短縮することが可能になりました。しかしSQL Server 2008ではDBCC CHECKFILEGROUPで整合性をチェックできるのは指定したファイルグループにすべてのページが収まっているテーブルのみになるため、複数のファイルグループにまたがってパーティション分割されているテーブルの整合性チェックについてはDBCC CHECKDBを使用する必要があります。

複数のファイルグループにまたがってパーティション分割されているテーブルが使用している一つのファイルグループに対してDBCC CHECKFILEGROUPを実行すると以下のようなメッセージが返されます。

オブジェクト"salesfact" (ID 2105058535)、インデックス"salesfact" (ID 0) の行セットID 72057594038779904 を処理できません。未検証のファイルグループ"FG200401" (ID 2) に属しています。

CHECKFILEGROUP により、データベース'mscqidwh' に0 個のアロケーションエラーと0 個の一貫性エラーが見つかりました。

この場合は上記のオブジェクト（テーブル）salesfactの整合性チェックはスキップされるため、指定したファイルグループに整合性エラーがあるか否かは判断できません。

この機能についてはSQL Server の未来のバージョンで改善される予定です。

次回はSQL Server Integration Servicesを使用したデータロードのトピックを取り上げたいと思っています。よろしくお願いします。

コミュニティにおけるマイクロソフト社員による発言やコメントは、マイクロソフトの正式な見解またはコメントではありません。

SQL Server 2008 RTM (開発完了)!