SQL Azure

SQL Azureの最大サイズについて過去の投稿で、２回ほど説明をしました。

しかし、それぞれで微妙に説明の仕方が違うことで、仕様理解をさまたげる結果となっていました。SQL Azureの最大サイズについて改めて整理することで、仕様について確認したいと思います。

SQL Azureデータベースの種類

SQL Azureのデータベースは、データベースを作成する際にエディションと最大サイズを指定します。

エディションには、WEBとBusinessの２種類あります。

最大サイズは、WEBエディションは「１GBまで」、「５GBまで」の2種類あり、Businessエディションには、「10GBまで」、「20GBまで」、「30GBまで」、「40GBまで」、「50GBまで」の5種類あります。

以上を図示すると次の表のようになります。

エディションと最大サイズの設定・変更方法

SQL Azureデータベースのエディションや最大サイズを指定（変更）するには、

  • データベースを作成するときのCREATE文で指定する
  • 作成後にALTER DATABASE文で変更する

の2種類の方法がありますが、どちらにしても明示的にSQLを発行する必要があり、自動的にエディションや最大サイズが変わることはありません。

最大サイズと課金

SQL Azureの課金は、実際のデータベース使用量に応じて課金されます。

例えば、最大サイズを50GBに設定していても、実際の使用量が15GBの場合は、10GB～20GBの使用量が課金されます。つまり、最大サイズは直接課金に関係が無く、実際の使用量に応じて課金されます。ただし、エディションは関係があります。たとえば、Business Editionを使用していて、使用量が0.5GBの場合は、Business Editionの0～10GBの使用量が課金されます(決して、Webエディションの0～1GBの課金にはなりません)。

最大サイズの目的は、ストッパーです。大量のデータが格納されてしまい、想像しなかったデータベース使用量となり課金額が増える事態を防ぐためのものです。基本的に設定した最大サイズを越えてデータを格納することができません。最大サイズを20GBにしておけば、20GBしか格納できないので課金の最大額を制限することができます。

最大サイズの仕様

最大サイズを指定しておくと、基本的に設定した最大サイズを越えないと説明しました。最大サイズを超えてしまうと、それ以上追加でデータを挿入したり更新ができなくなります。最大サイズを超えている時にデータの挿入、更新をしようとするとエラーがでます。以後、データの追加や削除をするには、データ容量を減らす必要があります。

最大サイズを指定していても、データの更新や追加の頻度が激しい場合は、設定した最大サイズを越えてデータを格納できてしまいます。データの使用量が設定した最大サイズを超えているかどうかはリアルタイムには確認されず、数分起きにデータ使用料が超えていないかの確認をし、一度超えていることを検知すると以後はエラーになります。

データをループで大量投入するサンプルデータを格納するストアドプロシージャーを同時に10本動かして実験をすると、最大サイズ1GBのWebエディションの設定でも5.5GBまでデータを格納できました。が、以後はエラーで格納できません。ですので、タイミングによっては、2GBなこともありました。

使用量が最大サイズを超えていることが確認されると、その段階で動いている更新クエリ(接続)は強制切断されます。再度、更新しようとすると40544エラーが発生します。（下参照）

Msg 40544, Level 20, State 5, Line 1
The database has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions. Code: 524289

強制切断の場合のエラー

Message 10054、Lebel 20、State 0、Row 0
サーバーから結果を受信しているときに、トランスポート レベルのエラーが発生しました。
(provider: TCP プロバイダ, error: 0- 既存の接続はリモート ホストに強制的に切断されました。)

課金の仕様

ここまで、SQL Azureデータベースのエディションと最大サイズの仕様について説明しました。最大サイズを指定していても、最大サイズを越えてデータを格納できる場合があるります。では、最大サイズを越えて、データを格納できてしまった場合、課金はどう対応されるのでしょうか。

Webエディションの1GB設定で、実際には5.5GBを使用している場合、課金はWebエディションの1GB？Businessエディションの10GB？のどちらでしょうか。

答えは、自分が設定したエディションと最大サイズに基づいて課金されます。
Webエディション最大サイズ1GBと設定していれば、課金もWebエディションの最大サイズ1GB用の課金しかされません。一時的に超えたとしても、超えた分で課金されることは無いようです。

SQL Azure Team Blog

How to Tell If You Are Out of Room – SQL Azure Team Blog – Site Home – MSDN Blogsを簡単に翻訳したエントリーです。

SQL Azureデータベースはサイズ上限があります。サイズ上限がある理由の一つは、予想を超えてデータ量が拡大し、驚くべき使用料を請求するような事態を望んでいないからです。50GBを上限に、いつでもデータベースの上限を増やしたり減らしたりすることができます。しかし、これは自動ではありません。このエントリーでは、SQL Azureデータベースのサイズをハンドリングし、拡大したり、現在の上限を取得したり、現在の容量を取得したり、サイズエラーを検知したりするTipsを紹介します。

話を始める前に一つ注意しないといけないのは、データベース上限を基に課金されるのでは無く、実際に使用しているデータ量が属する、データベースのエディションの範囲に応じて課金されます。Webエディションでは、0～1GBと1～5GBの範囲でそれぞれ課金されます。ビジネスエディションでは、0～10GB、10～20GB、20～30GB、30～40GB、40～50GBの範囲でそれぞれ課金されます。

 

現在の上限

 

次のクエリを使用することで、SQL Azureデータベースの現在の上限を取得することができます。

SELECT DATABASEPROPERTYEX ('AdventureWorksLTAZ2008R2' , 'MaxSizeInBytes' )

MaxSizuInBytesプロパティの結果は、バイト数です。1GBは、1,000,000,000バイトでは無く、1,073,741,824バイトです。

 

現在のサイズ

 

次のクエリを使用することで、データベースの現在のサイズを取得することができます。

SELECT SUM(reserved_page_count) * 8192
FROM    sys.dm_db_partition_stats

このクエリの結果も、バイト数で返します。上限のサイズを取得するクエリの結果とそのまま比較することができます。

ここで簡単なクイズ。

現在のサイズが上限サイズよりも大きくなるのはいつでしょうか？

答えは、あり得ないです。もし、上限を超えるデータを追加したり更新使用とすると、SQLがエラーを返します。

 

エラーを検知する

 

SQL Azureで４０５４４エラーを取得したら、エラーメッセージは「The database has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions.」となります。

SQL Managementでは次のように表示されます。

Msg 40544, Level 20, State 5, Line 1

The database has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions. Code: 524289

もしC#で、このエラーをハンドリングしたい場合は、次のようなコードになります。

try
{
    // ...
}
catch (SqlException sqlException)
{
    switch (sqlException.Number)
    {
        // The database has reached its size quota. Partition or delete data,
        // drop indexes, or consult the documentation for possible resolutions. 
        case 40544:
            break;
    }
}

 

自動的にデータベース上限を増やす

 

データベース量が増え、現在の上限を超えたとき、上限が増えたらすばらしいと思いませんか？SQL Azureは、上限ではなく使用量に応じて課金されます。例えば、0.8GBのデータ量を使用していたら、５GB上限の内、0～1GBの範囲の課金が行われます。データベースが1GBより大きくなった時、エラーは発生せず、１~5GBの範囲の課金が行われます。上限サイズの自動拡張を許可しても、課金は使用している量に応じて課金されます。もし、現在の範囲を超えても、エラー無く自動的に次の範囲の課金が行われます。

データ上限のの自動拡張は、今のところWEBエディションとビジネスエディソンの間は超えません。データベースの上限は、1、5、10、20、30、40、50GBがあります。Webエディションを使用していて、5GBに到達し、データベース上限を拡大させたいときは、ビジネスエディソンに変更する必要があります。こえは、データベースを自動拡張させていくにはエディションを変更するTransact-SQLを使用する必要があると言うことです。

 

エディションを変更するコード

 

SQL Serverエラーコード40544をキャッチしたら、エディションを変更したり、上限を自動的に増やすC#コードを書くつもりでした。Windows Azure webロールにコードを追加し、データベースサイズが自動的に増やせるようにします。

しかし、このコンセプトには問題があります。webサイトに1秒間に1000回の接続があり、ちょうどデータベースがいっぱいになり、3、4スレッドスレッドがWindows Azureサーバで40544エラーを受け取るかもしません。それらのスレッドは、データベースサイズを自動拡張させようとしますが、3回も4回も拡張が行われ必要なサイズよりもデータベースサイズが大きくなる可能性があります。解決策は、トランザクション管理をし、データベースの拡大は同時に1つしか実行できないようにすることです。しかし、SQL azure上のALTER DATABASE文は、トランザクション制御されません。

40544エラーをハンドリングし、自動的にデータベースサイズ、エディションを変更させsるソリューションの実現方法を考え続けます。これについて、何か良いアイディアをお持ちでしたら、コメントでアドバイスをください。

 

データベースサイズの変更

 

自動的にデータベースサイズを変更できない間、自分で上限を変更するTransact-SQLを流すことになります。エディションを変更する場合は、次のようなALTER DATABASE句を使用できます。

ALTER DATABASE AdventureWorksLTAZ2008R2 MODIFY (EDITION='BUSINESS', MAXSIZE=10GB)

データベースの上限はサイズを大きくしたり、減らしたりできます。もしサイズを減らそうとした時に、減らそうとしているサイズよりも現在のサイズのほうが大きい場合、次のようなエラーが出ます。

Msg 60003, Level 16, State 1, Line 1

Operation failed because the resulting cumulative database size would exceed your database sku limit