SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

Archive for the ‘SQL Server’ Category

Denali で HADR 環境を構築

leave a comment

Denali では高可用性の構成として、

  • レプリケーション
    トランザクションログをディストリビュータに配信し、その内容をサブスクライバに適用することでデータの同期を行います。
    image
    # ディストリビュータはパブリッシャと兼用する場合が多いかもしれないですね。
  • ログ配布
    トランザクションログのバックアップを共有ディレクトリに取得し、配布先で取得されたログバックアップをリストアすることでデータの同期を行います。
    image
  • ミラーリング
    プリンシパル / ミラー間でミラーリング用のエンドポイント (接続点) を介してデータの同期を行います。
    ウイットネスサーバーを用意することで障害発生時にプリンシパルとミラーを切り替えることが可能です。
    image
  • クラスタリング
    共有ディスクにデータベースを配置し、SQL Server のサービスはどちらかのノードで実行する構成になります。
    データベースは共有ディスク一か所なのでデータの同期は行われません。
    image

 

のほかに、HADR (high-availability and disaster recovery) という構成が追加されています。
"HADR" Overview (SQL Server)
SQL Server Code-Named "Denali" CTP1 Release Notes の[3.7 High Availability and Disaster Recovery ("HADR")] にも情報が記載されています。

HADR は共有ディスクを使用しないで SQL Server の高可用性環境を作成できる構成になります。
image

特徴としてはプライマリとセカンダリのサーバーだけで同期をとることができ、サーバー間のデータ同期にはミラーリングと同様にエンドポイントが使用されている点になります。
パッと見はレプリケーションと変わらなそうですが、HADR はデータベース全体を同期の対象とすることが可能です。
# レプリケーションは同期をとるテーブルを選択する必要があったはずです。
レプリケーションは SQL Server Agent は使用してデータの同期が行われますが HADR では SQL Server Agent は不要です。
また、セカンダリデータベースは常に読み取りデータベースとして使用することができるように設定が可能です。

クライアントからの接続はミラーリングと同じような設定ができるようなのですがここはまだ検証ができていません。
"HADR" Overview (SQL Server) に[Client Connections] にこのあたりの記載がされています。
Connecting Clients to a Database Mirroring Session (SQL Server) へのリンクがあるので、ミラーリングと同じように Server / Failover_Partner を使用して自動的なフェールオーバーはできそうなのですが。

CTP1 では

This CTP supports only a single, asynchronous secondary replica.

となっているため、シングルのセカンダリ環境となります。
# GUI 上は複数サーバー設定できそうなのですが、具体的に何サーバー設定できるかが記載されているドキュメントが見つかっていないのですよね…。

HADR はクラスタリングの技術が使用されているため、WSFC が必須となります。
image
HADR で必要となる要件に関してはこちらの技術情報に記載がされています。
"HADR" Prerequisites and Restrictions
前提となるデータベースの設定条件に関してもこちらの技術情報に記載がされています。

ただし、SQL Server のインスタンスに関してはクラスタインスタンスとする必要はなくローカルインスタンス間でデータの同期を行うことが可能です。

この HADR の構築方法をまとめてみたいと思います。

今回の環境は前回の投稿で作成したクラスタ環境に、ローカルの既定のインスタンスを作成した環境で実施しています。
また、SQL Server のサービスアカウントはドメインユーザーを指定しています。

両サーバーでローカルアカウントを使用して、ミラーアカウントを作成、エンドポイントで証明書を使用する等を行えばドメインユーザーでなくてもデータ連携ができるかもしれませんが今回は簡単な構築の検証のためドメインユーザーを使用しています。
# WSFC なのでドメイン参加は必須ですのでドメインユーザーを使用するのはそれほど敷居が高くないと思います。

 

■HADR の有効化

 

最初の作業として HADR を使用するインスタンスで、HADR を有効化する必要があります。
HADR の有効化は [SQL Server Configuration Manager] から行います。

image

 

Configuration Manager で対象のインスタンスの SQL Server サービスのプロパティを開くと、[SQL HADR] というタブが表示されます。
このタブの [Enable SQL HADR service] を有効にすることで、対象のインスタンスで HADR を使用することが可能になります。
image
[Enable SQL HADR service] を両サーバーのインスタンスで有効にして、サービスを再起動します。
image

 

TCP/IP 接続の有効化

Denali CTP1 の初期状態では、[Shared Memory] のみが有効になった状態になっています。
image
HADR では TCP/IP でインスタンス間を接続する必要がありますので、TCP/IP を有効にしておきます。
# データの同期はエンドポイントを介して行われますが HADR 設定時に TCP/IP でインスタンスに接続ができる必要があります。
image
image
設定を有効にするため、SQL Server のサービスを再起動します。

 

■ファイアウォールの設定

 

HADR はインスタンス間の接続と、エンドポイントの接続ができる必要がありますので、必要となるポートを許可するファイアウォールのルールを設定します。

今回は既定のインスタンスを使用していますので、インスタンス間の接続は [TCP 1433] が必要になります。
エンドポイントでは既定ではミラーリングと同じ [TCP 5022] が使用されますので、この 2 種類のポートを許可するルールを作成します。

netsh advfirewall firewall add rule name="HADR" dir=in protocol=TCP localport=1433,5022 action=allow

 

■HADR の設定

HADR の設定は SQL Server Management Studio (SSMS) から実施します。
image

今回は、HADR_TEST というデータベースを使用て HADR を設定してみたいと思います。
# 復旧モデルは [完全] に設定しておく必要があります。
image

最初の作業としては [Availability Group]  を作成します。
HADR はこのグループに属しているデータベースの冗長化を行います。

  1. [Management] から、[Availability Groups] を右クリックして、[New Availability Group] をクリックします。
    image
  2. [Next] をクリックします。
    image
  3. Availability Group の名称を入力して、[Next] をクリックします。
    image
  4. HADR で冗長化を行うデータベースを選択する画面が表示されるのですが、データベースを作成した直後の状態だと以下のように対象のデータベースとして選択することができません。
    image
    [Show user databases not meeting requirements.] を有効にすると理由が表示されます。
    image
    HADR を構成するためには、バックアップを取得しておく必要があります。
    一度もバックアップを取得したことがないデータベースは HADR の対象とすることができません。

    バックアップを取得していると HADR の対象とすることができます。
    対象のデータベースを選択して、[Next] をクリックします。
    image

  5. HADR に含める SQL Server のインスタンスを設定します。
    デフォルトでは、HADR を設定しようとしたインスタンスが Primary に設定されています。
    image
    [Add] をクリックしてインスタンスの追加を行います。
    インスタンスの接続ダイアログが表示されますので HADR に含めるインスタンスに接続を行います。
    image
  6. インスタンスに接続をすると HADR の対象として追加がされます。
    image
    初期の状態では、[Read Mode in Secondary Role] は [Disallow Connections] となっています。
    今回は、Secondary は読み取り専用として使用したいため、[Allow All Connections] に設定を行います。
    image
    この設定ですが以下の技術情報の [Read-only Access Behavior] に記載があります。
    Read-Only Access to Secondary Availability Replicas
    READ_CONNECTIONS_ONLY と ALL_CONNECTIONS の違いはこれから勉強しないといけないなと思っています…。
  7. [Endpoints] に関しては自動で作成がされます。
    [Next] をクリックして次の画面に遷移します。
    image
  8. [Finish] をクリックして、Availability Group を作成します。
    [Script] をクリックすることで、Availability Group の設定を行うためのスクリプトを作成することも可能です。
    image
  9. [Start Data Synchronization] をクリックして Secondaly サーバーとデータの初期同期を行います。
    image
  10. データの初期同期ですが、バックアップ / リストアを使用して行われます。
    image
    バックアップ/リストアは共有ディレクトリ経由で行われますので、共有ディレクトリを作成する必要があります。
    今回は、[Share] という [Everyone:フルコントロール] の共有を作成しています。
    image
    注意点としては、Secondary のサーバーにデータベースが存在しているとバックアップ / リストアが実施できない点になります。
    Secondary 側にデータベースが存在していると以下のようなエラーになります。
    image
    正常に同期が完了すると以下の画面になります。
    image
  11. [Close] をクリックして完了します。
    image

以上で HADR の設定は完了です。
今回は SSMS で GUI を使って実行しましたが、クエリベースで実行する場合は以下の技術情報が参考になります。
Example: Setting Up an Availability Group Using Windows Authentication (Transact-SQL)

[Availability Groups] の下に設定が追加されていることが確認できます。
image
設定ついては [Object Explorer Details] からも確認をすることができます。
image
image

HADR の設定は特に難しい箇所はなく設定が出来ると思います。
現状は非同期の更新となっていますが、技術情報で以下のように記載されているように、かなり短い間隔で同期はされているように思えます。

Asynchronous-commit mode enables the primary replica to run with minimum transaction latency

 

細かな使い方に関しては別の投稿でまとめていきたいと思います。

Written by Masayuki.Ozawa

11月 13th, 2010 at 10:33 pm

Posted in SQL Server

Tagged with ,

Denali で 2 ノードクラスターを構築

leave a comment

Denali で 2 ノードクラスターを構築する手順をまとめてみたいと思います。

WSFC は構築をしてあり、MSDTC のリソースは作成済みです。
image

■1 ノード目のインストール

事前準備は終わっていますのでセットアップを起動してクラスターのインストールを行います。
Denali のクラスターのインストールは一台ずつ実施をします。
まずは最初のノードのインストールから。

  1. セットアップを起動すると .NET Framework 3.5 のインストールを確認されますので、[OK] をクリックしてインストールを行います。
    # .NET Framework 4.0 も合わせてインストールがされるようです。
    image
  2. [Installation] から [New SQL Server failover Cluster installation] をクリックします。
    image
  3. [OK] をクリックします。
    image
  4. [Next] をクリックします。
    CTP1 では、Enterprise Evaluation を使用してクラスターを構築します。
    image
  5. [I accept the license terms.] を有効にして、[Next] をクリックします。
    image
  6. [Install] をクリックします。
    image
  7. セットアップルールの検証が開始されます。問題が無ければ [Next] をクリックします。
    以下の画面では、[Microsoft Cluster Serivce (MSCS) Cluste rverification warnings] が発生しています。
    この警告ですが、WSFC のクラスタの検証レポートで警告が発生していたために発生しています。
    image
    image

    私がインストールする場合、インストール時にログオンしているドメインアカウントには最低限の権限 (Domain Users) しか与えていないので、コンピューターアカウントの作成に関してのチェックで警告となります。
    image
    インストール時にログオンしているユーザーにはコンピューターアカウント作成のための権限は委任していませんが、初期状態の 10 台までの作成で対応ができますので、警告は無視ししています。

    クラスターの検証レポートが以下の状態であれば、SQL Server のインストール検証では警告は発生しません。
    image

    また、SQL Server で使用するディスクに関してもインストーラーを実行するノードに移動をしておきます。
    image
    [使用可能記憶域] に属している状態ではインストールには使えませんので注意が必要です。
    image
    インストーラーを実行しているノードで使用可能な共有ディスクがない場合はエラーになります。
    image
    image

  8. インストールする機能を選択し、[Next] をクリックします。
    今回は、SQL Server のデータベースエンジン関連に必要な最低限の機能をインストールしています。
    image
  9. インスタンスの情報を入力して、[Next] をクリックします。
    今回は名前付きインスタンスを使用しています。
    image
  10. [Next] をクリックします。
    image
  11. SQL Server クラスターのグループ名を設定し、[Next] をクリックします。
    グループ名はコンボボックスになっているのでグループ名を変更することが可能です。
    image
    共有ディスクが [使用可能記憶域] に属している場合は、[Next] をクリックすることができませんので一度ほかのグループに移動をしておく必要があります。
    image
  12. チェックボックスで使用するディスクを選択し、[Next] をクリックします。
    image
  13. ネットワークを設定して [Next] をクリックします。
    今回は、DHCP のままでインストールをしてみたいと思います。
    image
  14. クラスターで使用するセキュリティ設定を選択して、[Next] をクリックします。
    今回は、[User service SIDs] を選択しています。
    image
  15. サービスアカウントと照合順序を設定して、[Next] をクリックします。
    image
    image
  16. セキュリティ設定、データディレクトリ、FILESTREAM の設定をして、[Next] をクリックします。
    image
    image
    image
  17. エラー報告をして [Next] をクリックします。
    image
  18. [Next] をクリックします。
    image
  19. [Install] をクリックしてインストールを開始します。
    image
    image
  20. [Close] をクリックしてインストールを完了します。
    image

これで 1 ノード目のインストールは完了です。

 

■2 ノード目のインストール

 

続いて 2 ノード目のインストールを行います。

  1. 2 ノード目でセットアップを実行します。
  2. [OK] をクリックします。
    image
  3. [Installation] から、[Add node to a SQL Server failover cluster] をクリックします。
    image
  4. [OK] をクリックします。
    image
  5. [Next] をクリックします。
    image
  6. [I accept the license terms.] を有効にして、[Next] をクリックします。
    image
  7. [Install] をクリックします。
    image
  8. [Next] をクリックします。
    image
  9. ノードを追加するインスタンスを選択して、[Next] をクリックします。
    image
  10. [Next] をクリックします。
    image
  11. サービスアカウントのパスワードを入力して、[Next] をクリックします。
    image
  12. [Next] をクリックします。
    image
  13. [Next] をクリックします。
    image
  14. [Install] をクリックして、インストールを開始します。
    image
    image
  15. [Close] をクリックしてインストールを完了します。
    image

以上でクラスターのインストールは完了です。

image

クラスターインスタンスのインストールは今までのバージョンと変わらないですね。

Denali では高可用性構成として [HADR] という構成があります。

この構成に関しては次の投稿でまとめてみたいと思います。

Written by Masayuki.Ozawa

11月 13th, 2010 at 1:39 pm

Posted in SQL Server

Tagged with ,

SQL Live Monitor と PAL Tool を使用したモニタリングとレポーティング

leave a comment

今日は、CodePlex で提供されている [SQL Live Monitor] と [Performance Analysis of Logs (PAL) Tool] を使用した、SQL Server のモニタリングとレポーティングについてまとめてみたいと思います。

■SQL Live Monitor

SQL Live Monitor を使用すると、SQL Server の現状の稼働状況を確認することができます。
image
[SQL Server] の欄に情報を取得する SQL Server 名 (既定のインスタンスの場合はサーバー名、名前付きインスタンスの場合はサーバー名インスタンス名) を入力して、[Start] をクリックすることで情報が取得できます。

パフォーマンスチューニングをするにあたって、いくつかの項目は手動で情報を取得する必要がありますが現状の状態を把握するのにはとても便利なツールだと思います。
データキャッシュ / プロシージャキャッシュのメモリ使用状況は Page Life Expectancy といった定番の情報をリアルタイムに見やすく確認することが可能です。
Cache Hit Ratio 系は SQL Server のサービスが最後に起動してからの情報なのであまり参考にならない可能性がありますが…。
矢印でデータの流れがわかるようになっており、[>->->] であれば、左の項目から右の項目に、[<-<-<-] であれば右の項目から左の項目に対してデータが流れていることになります。

たとえば、[Checkpoints/Sec] ですが、チェックポイントが発生するとダーティーページがデータファイルに対してフラッシュされます。
そのため、[SQL Memory] から [Disk Storage] に対してデータが流れることになります。この矢印の方向を理解しておくと SQL Server のデータの流れが分かるかと思います。

 

情報はパフォーマンスモニタから取得されており、以下のような項目を取得し、画面に表示を行っています。

DiskIdleTime = LogicalDisk% Idle Time
AvgDiskReadSec = LogicalDiskAvg. Disk sec/Read
AvgDiskWriteSec = LogicalDiskAvg. Disk sec/Write
AvgDiskQueueLength = LogicalDiskAvg. Disk Queue Length
DiskReadBytesSec = LogicalDiskDisk Read Bytes/sec
DiskWriteBytesSec = LogicalDiskDisk Write Bytes/sec

TransactionsSec = DatabasesTransactions/sec
LogFlushes = DatabasesLog Flushes/sec
TempDBSize = DatabasesData File(s) Size (KB)tempdb
TempDBLogSize = DatabasesLog File(s) Size (KB)tempdb
TempLogSpaceUsed = DatabasesLog File(s) Used Size (KB)tempdb

CPUPercent = Processor% Processor Time

CompilesSec = SQL StatisticsSQL Compilations/sec
RecompilesSec = SQL StatisticsSQL Re-Compilations/sec
BatchesSec = SQL StatisticsBatch Requests/sec

TargetMemory = Memory ManagerTarget Server Memory (KB)
TotalMemory = Memory ManagerTotal Server Memory (KB)
ConnectionMemory = Memory ManagerConnection Memory (KB)
MemGrantsPending = Memory ManagerMemory Grants Pending

BufferCacheSize = Buffer ManagerDatabase pages
BufferCacheHitRatio = Buffer ManagerBuffer cache hit ratio
UserPageLookups = Buffer ManagerPage lookups/sec
PageLife = Buffer ManagerPage life expectancy
LazyWrites = Buffer ManagerLazy writes/sec
ReadAheads = Buffer ManagerReadahead pages/sec
Checkpoints = Buffer ManagerCheckpoint pages/sec
DiskReads = Buffer ManagerPage reads/sec
DiskWrites = Buffer ManagerPage writes/sec
StolePages = Buffer ManagerStolen pages

LocksPerSecond = LocksLock Requests/sec
LockWaits = LocksLock Waits/sec_Total
AvgLockWaitTime = LocksAverage Wait Time (ms)_Total

UserConnections = General StatisticsUser Connections
LoginsSec = General StatisticsLogins/sec
TempObjectCreate = General StatisticsTemp Tables Creation Rate
TempObjectDestroy = General StatisticsTemp Tables For Destruction
TempActiveTables = General StatisticsActive Temp Tables

ProcedureCacheSize = Plan CacheCache PagesSQL Plans
ProcedureCacheHitRatio = Plan CacheCache Hit RatioSQL Plans

MemPagesSec = MemoryPages/sec
FreePTEs = MemoryFree System Page Table Entries
PagedPool = MemoryPool Paged Bytes
NonPagedPool = MemoryPool Nonpaged Bytes
AvailableSystemRam = MemoryAvailable MBytes

ProcQueueLength = SystemProcessor Queue Length

WorkTables = Access MethodsWorktables Created/sec
WorkFiles = Access MethodsWorkfiles Created/sec
FullScans = Access MethodsFull Scans/sec

msdtc = Exec StatisticsDistributed QueryExecs in progress

手動でパフォーマンス監視をしたいときはこの項目をベースに必要となる項目を追加していくと楽かもしれないですね。

 

Live Monitor でリンクになっている箇所は情報をブレークダウンすることができます。

[User Connections]
image

[Total]
image

[Locks / sec]
image

[Avg Waits]
image

[Latch Stats]
image

[Current Memory]
image

[Plan Cache]
image

[Expensive Queries]
image

[Detailed TempDB Views]
image

 

SQL Live Monitor は現在の使用状況を確認するだけでなく、ログを取得する機能を持っています。
[Option] ボタンを押すことで、ログ取得の設定をすることが可能です。
image

デフォルトではログ取得は有効になっていないのですが、
[Log Data for offline analysis (CSV)]
[Log Blocked Process Details (CSV)]
[Log Data for PAL Analysis]
を有効にすることで、ログを取得することが可能になります。ログですが、SQL Live Monitor を実行したフォルダに取得されます。
# Blocked Process はうまく取得できなかったのですが…。

 

[PAL Analysis] は PAL Tool で解析をするための取得データになり、このデータはパフォーマンスモニタを使用して取得されています。
設定を有効にすると、[mscounters] というユーザー定義のデータコレクタ セットが作成され、このコレクタ セットで PAL Tool で解析するためのパフォーマンス情報が取得されます。
image

この PAL Tool 用のデータですが以下の情報が取得されています。

Network Interface(*)Bytes Total/sec
Network Interface(*)Current Bandwidth
Network Interface(*)Output Queue Length

SystemProcessor Queue Length

Processor(*)% Processor Time
Processor(*)% Privileged Time
Processor(_Total)% Processor Time
Processor(_Total)% Privileged Time
Processor(*)% Interrupt Time

PhysicalDisk(*)% Idle Time
PhysicalDisk(*)Avg. Disk sec/Read
PhysicalDisk(*)Avg. Disk sec/Write

LogicalDisk(*)% Idle Time
LogicalDisk(*)Avg. Disk sec/Read
LogicalDisk(*)Avg. Disk sec/Write
LogicalDisk(C:)Free Megabytes
LogicalDisk(*)Disk Transfers/sec

MemoryFree System Page Table Entries
MemoryPool Nonpaged Bytes
MemoryPool Paged Bytes
MemoryAvailable MBytes
MemoryPages/sec

SystemContext Switches/sec

Process(*)Private Bytes
Process(*)Handle Count
Process(*)Thread Count
Process(*)% Processor Time
Process(*)Virtual Bytes
Process(*)Working Set

MemorySystem Cache Resident Bytes
MemoryPages Input/sec

Paging File(*)% Usage
Paging File(*)% Usage Peak

Process(sqlservr)% Privileged Time
Process(sqlservr)% Processor Time
Process(*)IO Data Operations/sec
Process(*)IO Other Operations/sec

SQL StatisticsBatch Requests/sec

Access MethodsForwarded Records/sec
Access MethodsFreeSpace Scans/sec
Access MethodsFull Scans/sec
Access MethodsIndex Searches/sec
Access MethodsPage Splits/sec
Access MethodsScan Point Revalidations/sec
Access MethodsWorkfiles Created/sec
Access MethodsWorktables Created/sec

Buffer ManagerBuffer cache hit ratio
Buffer ManagerLazy writes/sec
Buffer ManagerCheckpoint pages/sec
Buffer ManagerFree pages
Buffer ManagerPage life expectancy
Buffer ManagerPage lookups/sec
Buffer ManagerPage reads/sec
Buffer ManagerPage writes/sec

General StatisticsLogins/sec
General StatisticsLogouts/sec
General StatisticsUser Connections

LatchesLatch Waits/sec
LatchesTotal Latch Wait Time (ms)

Memory ManagerMemory Grants Pending
Memory ManagerTarget Server Memory (KB)
Memory ManagerTarget Server Memory(KB)
Memory ManagerTotal Server Memory (KB)
Memory ManagerTotal Server Memory(
KB)

SQL StatisticsSQL Compilations/sec
SQL StatisticsSQL Re-Compilations/sec

Locks(_Total)Lock Requests/sec
Locks(_Total)Lock Waits/sec
Locks(_Total)Lock Wait Time (ms)
Locks(_Total)Lock Timeouts (timeout > 0)/sec
Locks(_Total)Number of Deadlocks/sec

このログを使用して PAL Tool でレポートを作成することが可能となります。

 

■Performance Analysis of Logs (PAL) Tool

 

PAL Tool はパフォーマンスモニタで取得した情報を解析しレポートを作成することができるツールになります。
# PowerShell 2.0 と .NET Framework 3.5 の Chart Control が必要になります。
Live Monitor では、PAL Tool の [Microsoft SQL Server 2005/2008] のテンプレートに合わせた情報を取得していますので、取得したデータをすぐに PAL Tool にかけてレポートを作成することが可能です。

こちらが PAL Tool を起動した画面になります。
image

レポートの作り方は簡単で、[Counter Log] から Live Monitor で取得したパフォーマンスモニタのログデータを選択し、
image

[Threshold File] から、[SQL Server 2005/2008] をテンプレートとして設定をし、
image

[Execute] から [Finish] をクリックすることでレポートを作成することが可能です。
image

そうすると裏で PowerShell が実行されレポートが作成されます。
image

PAL Tool の実行が完了するとこのようなレポートが生成されます。
# デフォルトだと PAL Tool を実行したユーザーの [ドキュメントPAL Reports] に出力されます。
image

CPU の使用率の警告やメモリの空きの警告など基本的な情報を出力してくれるほかに SQL Server 固有のレポートが生成されます。
たとえば、Page Life Expectancy は 300 以上を推移しているのが良好な状態というのが定番になっていますが、そのレポートに関しても作成がされています。
# 300 を超えていない場合は赤くなって警告のレポートが作成されます。
image

こちらは CPU の使用率のグラフになるのですが、80%～100% は注意が必要な範囲 (Critical) なので赤くなっているのが確認できます。
以下のグラフでは瞬間的に Clitical の領域に達していることが確認できます。

image

ただし、別のグラフ (Processor の Queue) では Warning / Critical には達していませんので瞬間的な CPU の負荷であって、CPU のスペックの限界までは達していない (処理能力には余裕がある) 状態であると考察することができます。
image

Live Monitor で取得したデータを基に SQL Server の状態と、サーバーの基本的な状態をレポートとして確認をできますので、複数のグラフを見比べサーバーの状態を確認することが可能となります。

パフォーマンス チューニングではベースラインとして現状を確認することが重要になりますので、これらのツールを組み合わせると一からベースライン情報の取得を考えなくてもよいので楽になるかと思います。

グラフをどう読み解いていくかが難しいところではあるのですが、最初の一歩としては Warning / Critical となっている箇所を注視して、他のグラフと重ね合わせていくとよいのかな～と。

Written by Masayuki.Ozawa

11月 12th, 2010 at 12:15 am

Posted in SQL Server

Microsoft SQL Server code-named 'Denali' CTP1 の提供開始

leave a comment

本日 [Microsoft SQL Server code-named ‘Denali’ CTP1] の提供が開始されました。

Code Name Denali ですが、次期 SQL Server の CTP 版になります。
# SQL Server 2011 になるのかなと。
image

SSMS が VIsutal Studio 2010 ベース (.NET Framework 4.0 もインストールされます) に変更されています。
image

SSMS が刷新され、レポートが Word 形式でもエクスポートできるようになりました。
image

PDF のレポートに関してもフォントが埋め込まれるようになったみたいですね。
image

Availability Groups という機能が増えているようなのですが、これは WSFC の必要があるようですね。

image

日本語の照合順序に関しては、[Japanese_XJIS_100] が最新のようですね。
image

 

まだ、ヘルプが見れないので新規にどのような機能が入ったのかは調べられていないのですがクラスタに関しては今週中に構築してみたいと思います。

Written by Masayuki.Ozawa

11月 9th, 2010 at 9:55 pm

Posted in SQL Server

Tagged with

Microsoft SQL Server code-named ‘Denali’ CTP1 の提供開始

leave a comment

本日 [Microsoft SQL Server code-named ‘Denali’ CTP1] の提供が開始されました。

Code Name Denali ですが、次期 SQL Server の CTP 版になります。
# SQL Server 2011 になるのかなと。
image

SSMS が VIsutal Studio 2010 ベース (.NET Framework 4.0 もインストールされます) に変更されています。
image

SSMS が刷新され、レポートが Word 形式でもエクスポートできるようになりました。
image

PDF のレポートに関してもフォントが埋め込まれるようになったみたいですね。
image

Availability Groups という機能が増えているようなのですが、これは WSFC の必要があるようですね。

image

日本語の照合順序に関しては、[Japanese_XJIS_100] が最新のようですね。
image

 

まだ、ヘルプが見れないので新規にどのような機能が入ったのかは調べられていないのですがクラスタに関しては今週中に構築してみたいと思います。

Written by Masayuki.Ozawa

11月 9th, 2010 at 9:55 pm

Posted in SQL Server

Tagged with

Resource データベースについて

leave a comment

先日、
@IT などを運営されている
アイティメディア株式会社様
の会議スペースをお借りして開催された、SQL Server の勉強会に参加をしてきました。

その勉強会の中で SQL Server の [Resource] データベースについて質問を受けたので、このデータベースについて少し書いてみたいと思います。

Resource データベースに関しては、Books Online で以下のように記載されています。

Resource データベースは、読み取り専用のデータベースで、SQL Server に含まれるすべてのシステム オブジェクトがこれに格納されます。
SQL Server システム オブジェクト (sys.objects など) は、物理的には Resource データベースに保存されていますが、論理的にはすべてのデータベースの sys スキーマ内にあるように扱われます。
Resource データベースには、ユーザーのデータやユーザーのメタデータは保持されません。

各データベースのシステムビューやシステムストアドプロシージャーなどで使用されている、[sys.xxxx] 関連の情報は [Resource] データベースの内容を透過的に参照している形になります。
# Resource データベースは SSMS (SQL Server Management Studio) には表示されません。
imageimage

普段何気なく使用している、[sys.master_files] のようなシステムビューですが、これは [ビュー] と名前がついている用に実体ではなく、実表を参照しているものになります。

ではこの実体はどこに格納されているかというと実体は [master] データベースに保存されています。
試しに [sys.master_files] のビュー定義を確認してみたいと思います。
確認するためには、[SELECT OBJECT_DEFINITION(OBJECT_ID(‘sys.master_files’))] というように OBJECT_DEFINITION を使います。

CREATE VIEW sys.master_files AS  
SELECT
    database_id  = f.dbid,
    file_id   = f.fileid,
    file_guid   = f.fileguid,
    type   = f.filetype,
    type_desc  = ft.name,
    data_space_id  = f.grpid,
    name    = f.lname,
    physical_name  = f.pname,
    state   =
        convert(tinyint,
            case f.filestate  — Map enum EMDFileState to AvailablityStates         
                when 0 then 0 when 10 then 0 — ONLINE       
                when 4 then 7 — DEFUNCT       
                when 5 then 3
                when 9 then 3 — RECOVERY_PENDING       
                when 7 then 1
                when 8 then 1
                when 11 then 1 — RESTORING       
                when 12 then 4 — SUSPECT       
            else 6 end), — OFFLINE   
    state_desc  = st.name,
    f.size,
    max_size    = f.maxsize,
    f.growth,
    is_media_read_only  = sysconv(bit, f.status & 8),  — FIL_READONLY_MEDIA
    is_read_only    = sysconv(bit, f.status & 16), — FIL_READONLY
    is_sparse    = sysconv(bit, f.status & 256), — FIL_SPARSE_FILE
    is_percent_growth  = sysconv(bit, f.status & 32), — FIL_PERCENT_GROWTH
    is_name_reserved  = sysconv(bit, case f.filestate when 3 then 1 else 0 end), — x_efs_DroppedReusePending
    create_lsn   = GetNumericLsn(f.createlsn),
    drop_lsn    = GetNumericLsn(f.droplsn),
    read_only_lsn   = GetNumericLsn(f.readonlylsn),
    read_write_lsn  = GetNumericLsn(f.readwritelsn),
    differential_base_lsn = GetNumericLsn(f.diffbaselsn),
    differential_base_guid = f.diffbaseguid,
    differential_base_time = nullif(f.diffbasetime, 0),
    redo_start_lsn   = GetNumericLsn(f.redostartlsn),
    redo_start_fork_guid = f.redostartforkguid,
    redo_target_lsn  = GetNumericLsn(f.redotargetlsn),
    redo_target_fork_guid = f.forkguid,
    backup_lsn   = GetNumericLsn(f.backuplsn)
FROM
    master.sys.sysbrickfiles f
    LEFT JOIN
        sys.syspalvalues st
        ON
        st.class = ‘DBFS’
        AND
        st.value = f.filestate
    LEFT JOIN
        sys.syspalvalues ft
        ON         ft.class = ‘DBFT’
        AND ft.value = f.filetype
WHERE
    f.dbid < 0x7fff — consistent with sys.databases
    AND
    f.pruid = 0
    AND
    f.filestate NOT IN (1, 2) — x_efs_Dummy, x_efs_Dropped
    AND
    has_access(‘MF’, 1) = 1 

ビューの元になるデータは [master.sys.sysbrickfiles] というテーブルになっています。
Resource データベースはこのようなシステムビューやシステムストアドプロシージャを格納し、master 上に格納されている情報を透過的に見せるために使用されています。
# 今回のベース表になっている、[master.sys.sysbrickfiles] は直接参照できないので。

この Resource データベースですが、以下の 2 つのファイルから構成されています。

  • mssqlsystemresource.mdf
  • mssqlsystemresource.ldf

通常、システムデータベースは
[C:Program FilesMicrosoft SQL ServerMSSQL10_50.<インスタンス ID>MSSQLDATA]
というディレクトリに格納されるのですが、リソースデータベースのファイルは、
[C:Program FilesMicrosoft SQL ServerMSSQL10_50.<インスタンス ID>MSSQLBinn]
に格納がされます。
インスタンスのプログラム扱いでファイルが格納されていますね。

この Resource データベースですが、バージョンがあり [SELECT SERVERPROPERTY(‘ResourceVersion’)] というクエリを実行することでバージョンを確認することができます。

このバージョンですが、SQL Server のバージョンと一致するようで、

  • SQL Server 2008 R2 RTM ： [10.50.1600]
  • SQL Server 2008 R2 CU4 ： [10.50.1746]

となります。

修正プログラムをインストールすると、Resource データベースのバージョンも更新されます。

Resouce データベースには実データではなく実データを見せるための定義が格納されているので、Books Online で

SQL Server では、Resource データベースをバックアップできません。
ファイル ベースまたはディスク ベースのバックアップは、mssqlsystemresource.mdf ファイルをデータベース ファイルではなくバイナリ (.EXE) ファイルのように実行できますが、SQL Server を使用してバックアップを復元することはできません。
mssqlsystemresource.mdf のバックアップ コピーの復元は手動でのみ実行できます。
また、現在の Resource データベースを古いバージョンや安全でない可能性のあるバージョンで上書きしないように注意する必要があります。

と書かれているのは、実データは含まれず定義のみが格納されており適切なバージョンの SQL Server をインストールすれば最適な状態になるからだと思います。

 

Resource データベースは SSMS に表示がされないので通常はどのような情報が格納されているのか見ることはできないのですが、実はデータベースのファイルをコピーしてアタッチすることで内容を確認することができます。
# mssqlsystemresource.mdf / ldf は読み取り専用で SQL Server 上で認識されているのでオンラインでもコピー可能です。

通常のアタッチでは、以下のようなエラーになります。
image
image

このエラーですが、[mssqlsystemresource] というデータベース名でアタッチをしようとしたために発生しています。
# Resource データベースですが内部的には、[mssqlsystemresource] で認識されているためだと思います。

[次の名前でアタッチ] のデータベースの名前を変更することで Resource データベースをアタッチすることが可能です。
今回は、Resource というデータベース名でアタッチしています。
image

アタッチをすることで Resource データベースの内容を直接確認することが可能になります。
image

通常、[sys.xxxxx] はシステム ビューやシステム ストアド プロシージャーとして認識されているのですが、アタッチした Resource データベースではユーザービュー / ユーザー ストアド プロシージャーとして認識がされます。
# システムビューやシステム ストアド プロシージャーとしても認識はされるのですが。

imageimage

システム ビュー / システム ストアド プロシージャーの状態では、スクリプト化ができないため [OBJECT_DEFINITION] を介して定義を確認していました。
image

ユーザーオブジェクトになっていればスクリプト化が可能なため、[sys.xxxx] といったものも簡単に内容を確認できます。
imageimage

 

Resource データベースは SQL Server 2005 になってから搭載されたものなのですが、通常使用している中ではあまり意識はしないので、どんなデータが入っているのかイメージがしにくいのですよね…。
# 私は、sys. 系の定義が入っているだけだと考えているのですが。

Written by Masayuki.Ozawa

11月 7th, 2010 at 10:54 pm

Posted in SQL Server

SQL Server 2008 SP1 CU8 スリップストリーム インストール時の注意点

leave a comment

SQL Server 2008 はスリップストリームインストールで Service Pack と Cumulative Update を同時にインストールすることが可能です。

ただし、SP1 最新のSQL Server 2008 SP1 CU8 をスリップストリームインストールしようとすると以下のエラーが発生します。
image

この情報については、以下の技術情報に記載されています。
SQL Server 2008 のインストールを更新またはスリップストリームする方法

この技術情報の中に、[必要な属性 ‘Path’ が見つかりません。] のエラーが表示された場合の対処方法が記載されています。
# このエラーの発生は、[累積的な更新プログラム パッケージ 8 以降] となっていますが、これは SP1 RU8 以降が対象となります。

回避方法としては以下の方法が記載されています。

方法 1
累積的な更新プログラム パッケージを抽出した場所

CU8<CPU>setupsqlsupport.msi

から、特定のアーキテクチャに対する SQL サポート .msi ファイルを手動でインストールします。
方法 2
ローカル フォルダーからセットアップを開始する前に、オプション 2 の手順 4. で示されているファイルに加えて、次の手順で示されているファイルをコピーする必要があります。ファイルをコピーするには、以下の手順を実行します。

  1. Microsoft.SQL.Chainer.Package.dll ファイルを、RTM フォルダーから、<メディア><アーキテクチャ フォルダー> フォルダーのローカル コピーにコピーします。
  2. Sqlsupport.msi ファイルをコピーします。次の最初の場所から、2 番目の場所のローカル コピーに、ファイルをコピーします。

    C:<kb_number_of_hotfix package><アーキテクチャ>setupSqlsupport.msi

    <メディア><アーキテクチャ フォルダー>setup

簡単に書いてしまうと CU8 のインストーラーに同梱されているサポートツールをインストールしてから、スリップストリームインストールを実行するということが書かれています。

サポートツールは [Setup] ディレクトリ内に各言語別に用意されているのですが、CU8 のインストーラーには 1041 (日本語) のセットアップツールが含まれていません…。
# 1031 は英語用のディレクトリになります。
image

英語用のディレクトリ内にある、[sqlsupport.msi] をインストールすれば、SP1 CU8 をスリップストリームインストールできるようになるのですが、セットアップが英語になってしまいます…。
image

英語のサポートツールをインストールしても、インストールされる SQL Server の LCID は [1041] で、[SqlCharSetName] は [cp932] となっているのですが。

 

日本語のセットアップツールを使ってスリップストリームインストールしたい場合は、現状は SP2 のサポートツールをインストールしてからスリップストリームインストールすることでエラーが発生しなくなります。

まずは、SP2 のインストールモジュールを展開します。
展開に使用するコマンドは以下のような形式になります。

c:tempSP2SQLServer2008SP2-KB2285068-x64-JPN.exe /extract:c:SP2

展開が終わったら、 [x64setup1041sqlsupport.msi] を実行して SP2 のサポートツールをインストールします。
image

 

サポートツールのインストールが終了したら通常のスリップストリームインストールの方法と同じ形式でコマンドを実行してインストールを行います。

setup.exe /PCUSource="C:SP1" /CUSource="C:SP1CU8"

SP2 のサポートツールであれば、SP1 CU8 をスリップストリームインストールすることが可能です。
image

SP2 は最近リリースされたものなので、この方法も最近になって使えるようになったものですが…。
SP2 がリリースされている状態で SP1 CU8 を新規にインストールする機会は少ないかもしれませんがメモとして。

2010/10/3 追記

CU には以前から英語のセットアップサポートツールしか含まれていないですね。
全言語用のサポートツール入れていたらかなりのサイズになってしまうので、現在のモジュール構成は納得。

Written by Masayuki.Ozawa

10月 3rd, 2010 at 8:13 pm

Posted in SQL Server

Tagged with

SQL Server 2008 R2 のデータベースエンジンをコマンドでインストール

leave a comment

ちょっと検証で、SQL Server 2008 R2 の環境を作ったり壊したりしていました。

GUI からセットアップするのもちょっと面倒なので、コマンドでインストールするためのコマンドラインを。

 

■SQL Server 2008 R2 をコマンドでインストール

SQL Server 2008 R2 をコマンドでインストールするためにはコマンドプロンプトから [Setup.exe] を実行します。
インストールのオプションに関しては、[Setup.exe /?] か Books Online で [コマンド プロンプトから SQL Server 2008 R2 をインストールする方法] を検索すると調べることが可能です。

コマンドプロンプトからのインストールはシステム データベースの再構築でも使うのですが、普段はそれほど実行する機会はないですよね…。

以下のコマンドで、SQL Server のデータベースエンジン部をインストールすることが可能です。
# WordPress だと改行が微妙なので、オプション単位で改行していますが一行で。

setup.exe 
/QS
/IACCEPTSQLSERVERLICENSETERMS
/INSTANCENAME=SQL2008R2
/ACTION=INSTALL
/FEATURES=SQL
/SQLSVCACCOUNT="SYSTEM"
/SQLSYSADMINACCOUNTS="Administrators"
/AGTSVCACCOUNT="SYSTEM"
/SQLCOLLATION="Japanese_XJIS_100_CI_AS"
/SQLUSERDBDIR="F:Data"
/SQLUSERDBLOGDIR="G:Log"
/SQLTEMPDBDIR="E:Data"
/SQLTEMPDBLOGDIR="E:Log"
/AGTSVCSTARTUPTYPE="Automatic"

 

SQL Server のインストールは構成ファイルでも実行することができるので、以下のような構成ファイルからも実行できます。

[SQLSERVER2008]
IACCEPTSQLSERVERLICENSETERMS=TRUE
INSTANCENAME=SQL2008R2
ACTION=INSTALL
FEATURES=SQL
SQLSVCACCOUNT="SYSTEM"
SQLSYSADMINACCOUNTS="Administrators"
AGTSVCACCOUNT="SYSTEM"
SQLCOLLATION="Japanese_XJIS_100_CI_AS"
SQLUSERDBDIR="F:Data"
SQLUSERDBLOGDIR="G:Log"
SQLTEMPDBDIR="E:Data"
SQLTEMPDBLOGDIR="E:Log"
AGTSVCSTARTUPTYPE="Automatic"

 

この構成ファイルを使うようにセットアップを実行すると、インストールを自動化することができます。
構成ファイルを使う場合、セットアップは以下の形式で実行します。

setup.exe /QS /ConfigurationFile="H:InstallConfigConfig.ini"

 

構成ファイルに関しては GUI のインストールでも使用できます。
セットアップの [詳細設定] で [構成ファイルに基づくインストール] をクリックして構成ファイルを選択することで使用可能です。
image

image

ただし、GUI から構成ファイルを使用したセットアップでは自動化されません。
構成ファイルに設定した内容がデフォルトで選択された状態になってインストールをすることができます。
image

検証で環境を作り直したりする場合は、コマンドで実行するのが楽でよいですね。

Written by Masayuki.Ozawa

10月 2nd, 2010 at 7:30 pm

Posted in SQL Server

Tagged with

SQL Azure のインデックス関連で使用できる T-SQL について

leave a comment

先日は統計情報関連で使用できる T-SQL について投稿したので、今回はインデックス関連について。

今回も以下の技術情報を元にしています。
Transact-SQL Reference (SQL Azure Database)

■システムストアドプロシージャ

sp_helpindex テーブルまたはビューのインデックスに関する情報をレポートします。

?

■システムビュー

sys.index_columns sys.indexes インデックスまたは順序付けられていないテーブル (ヒープ) の一部である列ごとに 1 つの行を含みます。
sys.indexes テーブル、ビュー、テーブル値関数など、テーブル オブジェクトのインデックスまたはヒープごとに 1 行のデータを格納します。

?

■T-SQL ステートメント

※ SQL Azure では、オンプレミスの SQL Server と構文 (使用できるオプション) が異なります。

ALTER INDEX インデックスの無効化、再構築、再構成によって、またはインデックスに関するオプションの設定によって、既存のテーブルやビュー インデックス (リレーショナルまたは XML) を変更します。
CREATE INDEX 指定のテーブルまたは指定のテーブルのビューに、リレーショナル インデックスを作成します。
CREATE SPATIAL INDEX 指定したテーブルと列に空間インデックスを作成します。
DROP INDEX DROP INDEX ステートメントを使用すると、現在の SQL Server データベースからインデックスを削除できます。

?

ざっと技術情報を見たところではこの辺がインデックス関連で使用できる T-SQL になるようですね。

[T-SQL ステートメント] に関しては、SQL Azure ではオンプレミスの SQL Server とは使えるオプションが異なっています。

いくつか見落としがあるかもしれませんが、インデックスに特化したものはそれほど数はないみたいですね。

Written by Masayuki.Ozawa

8月 23rd, 2010 at 12:50 pm

Posted in SQL Server

Spotlight on SQL Server で SQL Azure に接続

one comment

今日は外出時の移動の際に [Windows Azure実践クラウド・プログラミングfor C#/Visual Basic/PHP] を読んでいました。
書籍の中に、[アプリケーションの監視 / ロギング – 診断モニタ ?] という章があり、その中にパフォーマンスモニタについての
記載があります。
移動中にこの章を読んでいました。

最近、SQL Azure を少しずつ触っているのですが、SQL Azure のパフォーマンス情報はどのように取得すればよいのだろうと
ふと思いました。

そういえば Spotlight on SQL Server は SQL Azure にも対応していたな～と思いさっそく試してみました。

?

■Spotlight on SQL Server の入手

Spotlight on SQL Server の提供元である Quest 社のホームページから 30 日のトライアル版を入手することが可能です。
SpotlightR on SQL Server Enterprise

?

■Spotlight on SQL Server のインストール

Spotlight on SQL Server ですが、実行する端末または他の端末に情報収集用のデータベースを作成する必要があります。
# 昔のバージョンだと、空のデータベースがあれば良かったはずなのですが、現在の Spotlight on SQL Server は診断サーバー用の
機能をインストールして情報収集用のデータベースを作成する必要があるようです。

今回は Spotlight on SQL Server をインストールする端末として、

  • Windows 7 x86 Enterprise Edition
  • SQL Server 2008 R2 Developer Edition

がインストールされている環境を用意しました。

SQL Server 2008 R2 Developer Edition ではデフォルトで [TCP/IP] が無効になっているので、TCP/IP での接続を許可するよう
SQL Server 構成マネージャーで設定をしておきます。

image

それでは、Spotlight on SQL Server のインストールを開始します。

  1. Spotlight on SQL Server のインストーラーを実行します。
    image
  2. [Typical] を選択します。
    ?image
  3. [Install Spotlight Client] と [Install Diagnostic Server] を選択 (デフォルト) して、[Next] をクリックします。
    image
  4. [I accept the license agreement] を選択して、[Next] をクリックします。
    image
  5. [Install Diagnostic Server on this computer] を選択 (デフォルト) して、[Next] をクリックします。
    image
  6. 診断サーバーのサービスアカウントを指定し、[Next] をクリックします。
    今回は [Use the local System account] を選択して、ローカルシステムアカウントでサービスを起動します。
    image
  7. 管理者とユーザーアカウントについては、デフォルトの状態にして、[Next] をクリックします。
    デフォルトだと両設定にインストーラーの起動アカウントが設定されるようです。
    image
  8. [Create] をクリックします。
    image
  9. [Create] をクリックします。
    image
  10. [OK] をクリックします。
    image
  11. 今回はローカルインスタンス上に過去データ用のデータベースを作成していますので [Instance] に [localhost] を設定して、
    [Next] をクリックします。
    image
  12. [Next] をクリックします。
    image
  13. [Start] をクリックして、インストールを開始します。
    image
    image?
  14. [Finish] をクリックしてインストールを完了します。
    image

以上でインストールは完了です。

?

続いて本番、SQL Azure に接続をしたいと思います。

SQL Azure には、[SQLAzure] というデータベースを作成してあります。
image
Spotlight on SQL Server から接続ができるように SQL Azure のファイアウォールも設定済みです。
image

?

■Spotlight on SQL Server で SQL Azure に接続

それでは、Spotlight on SQL Server を使用して SQL Azure に接続をしてみたいと思います。

  1. Spotlight on SQL Server を起動します。
    image
  2. [SQL Azure] を選択して、右クリック → [Connection manager] をクリックします。
    image
  3. [Add new connection] をダブルクリックします。
    image
  4. 準備した SQL Azure への接続情報を入力して、[OK] をクリックします。
    # ユーザー名は [@サーバー名] で修飾せず、ユーザー名だけ入力すれば問題ありません。
    image
  5. コネクション設定が作成されますので作成された設定をダブルクリックします。
    image

以上で、SQL Azure への接続は終了です。
SQL Azure に接続をすると以下のような情報を取得することが可能です。
image

こちらが、SQL Server 2008 R2 の情報を取得した画面になります。
image

Spotlight on SQL Server で取得できる情報ですが、オンプレミスの SQL Server と比較するとかなり少ない情報になっているようですね。

どのような情報が実際に取得できるかは別の機会にまとめてみたいと思います。

Written by Masayuki.Ozawa

8月 22nd, 2010 at 10:22 pm

Posted in SQL Server

« Older Entries
Newer Entries »