none
外部キーつきのテーブルを、事前に用意した同レイアウトの最新テーブルと置き換える方法について RRS feed

  • 質問

  • SQLServer 2005、2008を利用しています。

    SQLServerでDB構築しているアプリケーションで、夜間に外部から、翌日用の最新のマスタ情報を
    受信するのですが、できるだけサービス停止時間を短くした上で、最新の情報にマスタを切り替えたいと考えています。

    例えば、
    外部キーを持つ複数のテーブルが
    AAA.dbo.TableA
    AAA.dbo.TableB
    というように存在したとして、運用中に、背後で、外部から受信した情報で
    AAA.dbo.TableA_Work
    AAA.dbo.TableB_Work
    というテーブルを作成しておき、切替のタイミングで、リネームなどすることにより、
    TableA_Work、TableB?WorkをTableA、TableBに瞬時に切り替える、
    というようなことはできるでしょうか?

    TableA_Work、TableB_Workに作成される情報(外部からもらう情報)は、
    TableA、TableBの制約に則って作成されている、前提です。

    良い方法が思い浮かばず、上記ではリネームを想定した例を書いていますが、
    もちろん他の方法でも、短時間のマスタ切替について、何か実現方法があれば、ご教授いただきたいです・・・。
    2010年2月6日 0:50

回答

  • > というテーブルを作成しておき、切替のタイミングで、リネームなどすることにより、
    > TableA_Work、TableB?WorkをTableA、TableBに瞬時に切り替える、
    > というようなことはできるでしょうか?

    外部キーの削除、再作成が必要ならば、瞬時というわけにはいかないのでは?
    でも、リネームじゃない方法にしても外部キーの扱いに時間はかかりますよね

    > 良い方法が思い浮かばず、上記ではリネームを想定した例を書いていますが、
    > もちろん他の方法でも、短時間のマスタ切替について、何か実現方法があれば、ご教授いただきたいです・・・。

    外部キーはどうしても必要ですか?
    夜間にそっくりデータ入れ替えできるということは
    日中に更新をかけるようなテーブルではないですよね?(←AAA.dbo.TableA、AAA.dbo.TableB)
    外部キーの対象になっているテーブルに更新をかけるとき
    UPDATEクエリー実行の前に、プログラムで整合性チェックをかけていませんか?
    つまり、外部キーが役に立つケースは本当にあるのでしょうかという確認です。

    2010年2月8日 2:08
  • たいへん詳細は説明をありがとうございます
    状況は理解できたとおもいますが、なかなか複雑ですね。。

    > 1.については、ある程度のチェックは行っているものの、シビアなタイミングでのチェックはSQLServerに
    > 頼っているのが現状です・・・。

    少なくとも↑の条件があるなら外部制約キーは必要かもしれませんね
    (更新時、やたらとロックしないで済むという点で。)

    で、BULK INSERT では遅いと。。
    念のため確認ですが、外部キー以外のキーも削除してから BULK INSERT してますよね?
    そしてリネームする案を考えられたと…
    ちょっと
    変形して、VIEW を使う手はどうでしょう(実際にやったことはなくアイデアレベルですけれど)

    TableA は VIEW にしちゃって、VIEW が
    「select * from TableA」か
    「select * from TableA_Work」を切り替えるのみ

    いままで TABLE だったものが VIEW になってしまうところに抵抗感があるかもしれませんが。。
    それくらいしか思いつきませんでした。。。

    2010年2月8日 4:03
  • というテーブルを作成しておき、切替のタイミングで、リネームなどすることにより、
    TableA_Work、TableB?WorkをTableA、TableBに瞬時に切り替える、
    というようなことはできるでしょうか?
    目的にかなうかわかりませんが、リネームであればsp_renameを使えば可能です。

    (参考)
    sp_rename (Transact-SQL)
    http://msdn.microsoft.com/ja-jp/library/ms188351.aspx

    また、別のデータベースからテーブルをコピーするのであれば、smoのTransferオブジェクトが使用できます。
    マスターのみ別データベースであれば、データベースのデタッチ、アタッチで切り替えることもできると思います。

    #smoは内部でSSISを使用していますので、そちらの設定でも可能だと思います。


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://blogs.wankuma.com/trapemiya/
    2010年2月8日 6:12
  • >> そしてリネームする案を考えられたと…
    >
    >いえ・・・。
    >キーの削除等は行っていません。これまでは、
    >1.削除順のテーブル名のリストを作っておいて、対象テーブルからその順にデータを削除
    >2.BULK INSERT
    >と、いうシンプルな流れでした。

    すみません、キーという書き方が悪かったです。
    BULK INSERT の直前にインデックスを削除しているかどうかの確認でした。
    BULK INSERT に時間がかかるということなら、
    “インデックス削除→BULK INSERT→インデックス再作成”
    を試してみて、それでじゅうぶん早ければ現状システムの変更が少なくて済むかなと。。

    > もし、この方法を採用するとしたら、やはりALTER VIEWをかけて、
    > 都度、VIEWの定義を書き換えないといけないでしょうか・・・?

    とりあえず、VIEW は再作成を考えていました。
    ALTER VIEW でもとくに違いはないかもしれませんが。。どちらが良いか断言しづらいところです。。

    > それとも、例えば、どこかのテーブルに、今Workを見るべきか、本テーブルを見るべきか、
    > というような値を持っておいて、ひとつのVIEWでその値を見ながら、都度、適切なほうの
    > テーブルからデータを取得する、というようなことができるものでしょうか?

    できるかできないかでいうと、できるような気はしますが
    複雑にしてしまうと VIEW へ対する更新クエリーが効かなくなりそうに思います。
    そして“更新は直接テーブルへおこなう”ということにすると、日中の更新処理がややこしくなりそうな予感。。

    2010年2月8日 7:03

  • trapemiya様からも提案がありましたし、現行システムをなるべく変えず、
    データ更新時間短縮の方向で解決するのが安全な気はします。

    以上、補足でした。
    2010年2月8日 7:14
  • こんにちは、nagino です。

    パーティションテーブルによる実装がもっともシンプルに思えます。
    Enterprise Edition のみの対応ですが・・・。


    MCITP(Database Developer/Database Administrator)
    2010年2月8日 10:41

すべての返信

  • > というテーブルを作成しておき、切替のタイミングで、リネームなどすることにより、
    > TableA_Work、TableB?WorkをTableA、TableBに瞬時に切り替える、
    > というようなことはできるでしょうか?

    外部キーの削除、再作成が必要ならば、瞬時というわけにはいかないのでは?
    でも、リネームじゃない方法にしても外部キーの扱いに時間はかかりますよね

    > 良い方法が思い浮かばず、上記ではリネームを想定した例を書いていますが、
    > もちろん他の方法でも、短時間のマスタ切替について、何か実現方法があれば、ご教授いただきたいです・・・。

    外部キーはどうしても必要ですか?
    夜間にそっくりデータ入れ替えできるということは
    日中に更新をかけるようなテーブルではないですよね?(←AAA.dbo.TableA、AAA.dbo.TableB)
    外部キーの対象になっているテーブルに更新をかけるとき
    UPDATEクエリー実行の前に、プログラムで整合性チェックをかけていませんか?
    つまり、外部キーが役に立つケースは本当にあるのでしょうかという確認です。

    2010年2月8日 2:08
  • anningo 様、ご返信ありがとうございます。

    拙い説明で、お手数ををおかけして申し訳ありません。

    > 外部キーの削除、再作成が必要ならば、瞬時というわけにはいかないのでは?
    > でも、リネームじゃない方法にしても外部キーの扱いに時間はかかりますよね

    おっしゃるとおりだと思います・・・。
    現在は、1度本テーブルからデータを全て削除し、順にBULK INSERTする、という方法を
    取っており、データ登録中は、アプリケーションのサービスを停止しています。

    せめて、データを登録すること自体を、事前に行っておけば、切替の時間が
    短くなるのでは、と思い、上手くテーブルを複製しておいて切り替えるような方法が
    ないものかどうか、と思い、問合わせをさせていただきました・・・。
    “瞬時”などという紛らわしい表現を使って申し訳ありません。


    > 外部キーはどうしても必要ですか?
    > 夜間にそっくりデータ入れ替えできるということは
    > 日中に更新をかけるようなテーブルではないですよね?(←AAA.dbo.TableA、AAA.dbo.TableB)

    詳しい記載をしておらず、申し訳ありません。
    日中は日中で、データメンテナンス用の画面からの入力によって、親となるデータベースに発生した
    データのメンテナンス情報(差分)や、自分以外の子供となるデータベースで発生したメンテナンス情報を
    親経由で随時受け付けています。
    (自分自身にメンテナンスがあった場合も、親となるサーバへ情報を送っています)
    この差分情報は、親となるデータベースを持つサーバで、連番をつけて管理しています。

    それとは別に、親となる外部のデータベースのほうに大きな変更があった場合(主に夜間)に、
    今回質問にあげさせていただいたような、全件のファイルが送られてくる形です。
    (このとき、そのデータは、差分のどの連番のものまで反映されたものか、という情報を
    受け取り、それ以前の連番の差分情報は、反映不要、と見なす形です)

    > 外部キーの対象になっているテーブルに更新をかけるとき
    > UPDATEクエリー実行の前に、プログラムで整合性チェックをかけていませんか?
    > つまり、外部キーが役に立つケースは本当にあるのでしょうかという確認です。

    上記の通り、
    1.データメンテナンス用の画面、2.外部から届いた差分のメンテナンス情報、3.一括で届く情報、の
    3つの種類の入力による更新方法が存在しますが、

    1.については、ある程度のチェックは行っているものの、シビアなタイミングでのチェックはSQLServerに
    頼っているのが現状です・・・。

    2.については、画面での登録である程度チェックが行われている前提とし、プログラムによるチェックは
    行っていません。

    3.についても、前回記述しましたとおり、同制約がついたところから発信されたデータとして信頼し、
    事前のチェックは行わずに取り込んでいます

    子供となるデータベース発の変更が発生しなければ、削除してしまっても、と思うのですが、
    こちらからもメンテナンス用の画面からのデータ変更が発生するため、削除してしまうのは不安な状況です。

    以上で、ご確認いただいた内容に、回答できておりますでしょうか?
    不足や不備等ございましたら、お手数ですが、ご指摘をお願いいたします。

    2010年2月8日 3:04
  • たいへん詳細は説明をありがとうございます
    状況は理解できたとおもいますが、なかなか複雑ですね。。

    > 1.については、ある程度のチェックは行っているものの、シビアなタイミングでのチェックはSQLServerに
    > 頼っているのが現状です・・・。

    少なくとも↑の条件があるなら外部制約キーは必要かもしれませんね
    (更新時、やたらとロックしないで済むという点で。)

    で、BULK INSERT では遅いと。。
    念のため確認ですが、外部キー以外のキーも削除してから BULK INSERT してますよね?
    そしてリネームする案を考えられたと…
    ちょっと
    変形して、VIEW を使う手はどうでしょう(実際にやったことはなくアイデアレベルですけれど)

    TableA は VIEW にしちゃって、VIEW が
    「select * from TableA」か
    「select * from TableA_Work」を切り替えるのみ

    いままで TABLE だったものが VIEW になってしまうところに抵抗感があるかもしれませんが。。
    それくらいしか思いつきませんでした。。。

    2010年2月8日 4:03
  • というテーブルを作成しておき、切替のタイミングで、リネームなどすることにより、
    TableA_Work、TableB?WorkをTableA、TableBに瞬時に切り替える、
    というようなことはできるでしょうか?
    目的にかなうかわかりませんが、リネームであればsp_renameを使えば可能です。

    (参考)
    sp_rename (Transact-SQL)
    http://msdn.microsoft.com/ja-jp/library/ms188351.aspx

    また、別のデータベースからテーブルをコピーするのであれば、smoのTransferオブジェクトが使用できます。
    マスターのみ別データベースであれば、データベースのデタッチ、アタッチで切り替えることもできると思います。

    #smoは内部でSSISを使用していますので、そちらの設定でも可能だと思います。


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://blogs.wankuma.com/trapemiya/
    2010年2月8日 6:12
  • anningo様

    早速のご回答をいただき、ありがとうございます。

    > 念のため確認ですが、外部キー以外のキーも削除してから BULK INSERT してますよね?
    > そしてリネームする案を考えられたと…

    いえ・・・。
    キーの削除等は行っていません。これまでは、
    1.削除順のテーブル名のリストを作っておいて、対象テーブルからその順にデータを削除
    2.BULK INSERT
    と、いうシンプルな流れでした。

    BULK INSERT を行うときには、CHECK_CONSTRAINTS のステートメントはつけていませんので
    キー制約は、自動的に無視されて、データが登録されている、という形です。

    どちらかというと、これまで、BULK INSERT によって登録していたので、登録の部分では
    キー制約をあまり意識していなかったのが、別の方法を取ろうとした時に、テーブルが持つ
    外部キーをどうしたら良いのか、悩み始めた・・・という次第です。


    > ちょっと変形して、VIEW を使う手はどうでしょう(実際にやったことはなくアイデアレベルですけれど)

    なるほど・・・。
    アプリケーションからはVIEWを参照しておき、VIEWが、受信データを反映中は
    TableA_Workに退避したデータを参照、反映が終わったらTableAを参照する、
    とすれば、水面下で更新が行われる、という形になりそうですね!

    > TableA は VIEW にしちゃって、VIEW が
    > 「select * from TableA」か
    > 「select * from TableA_Work」を切り替えるのみ

    重ね重ねの質問で、申し訳ありませんが・・・。
    もし、この方法を採用するとしたら、やはりALTER VIEWをかけて、
    都度、VIEWの定義を書き換えないといけないでしょうか・・・?

    それとも、例えば、どこかのテーブルに、今Workを見るべきか、本テーブルを見るべきか、
    というような値を持っておいて、ひとつのVIEWでその値を見ながら、都度、適切なほうの
    テーブルからデータを取得する、というようなことができるものでしょうか?

    あまりVIEWの定義や、スクリプトに詳しくないもので、突拍子もない質問を
    していたら、申し訳ありません・・・。

    2010年2月8日 6:28
  • >> そしてリネームする案を考えられたと…
    >
    >いえ・・・。
    >キーの削除等は行っていません。これまでは、
    >1.削除順のテーブル名のリストを作っておいて、対象テーブルからその順にデータを削除
    >2.BULK INSERT
    >と、いうシンプルな流れでした。

    すみません、キーという書き方が悪かったです。
    BULK INSERT の直前にインデックスを削除しているかどうかの確認でした。
    BULK INSERT に時間がかかるということなら、
    “インデックス削除→BULK INSERT→インデックス再作成”
    を試してみて、それでじゅうぶん早ければ現状システムの変更が少なくて済むかなと。。

    > もし、この方法を採用するとしたら、やはりALTER VIEWをかけて、
    > 都度、VIEWの定義を書き換えないといけないでしょうか・・・?

    とりあえず、VIEW は再作成を考えていました。
    ALTER VIEW でもとくに違いはないかもしれませんが。。どちらが良いか断言しづらいところです。。

    > それとも、例えば、どこかのテーブルに、今Workを見るべきか、本テーブルを見るべきか、
    > というような値を持っておいて、ひとつのVIEWでその値を見ながら、都度、適切なほうの
    > テーブルからデータを取得する、というようなことができるものでしょうか?

    できるかできないかでいうと、できるような気はしますが
    複雑にしてしまうと VIEW へ対する更新クエリーが効かなくなりそうに思います。
    そして“更新は直接テーブルへおこなう”ということにすると、日中の更新処理がややこしくなりそうな予感。。

    2010年2月8日 7:03

  • trapemiya様からも提案がありましたし、現行システムをなるべく変えず、
    データ更新時間短縮の方向で解決するのが安全な気はします。

    以上、補足でした。
    2010年2月8日 7:14
  • anningo様、

    ご回答及び、補足をいただき、ありがとうございます。

    勘違いしてしまって申し訳ありません・・・。
    インデックスについても、削除、再作成といったことは行っていません。
    現在の構造でも、早くできる点を考えてみるのは、大切なことですね。

    VIEWを使った発想も、とても参考になりました。
    おっしゃるとおり、VIEWが拡張されてしまうと、更新は行えなくなってしまいますね。
    今回は、まず、現在の構造での改善から着手してみようと思いますが、
    こういった方法もあることを、しっかり抽斗にしまっておいて、今後、参考にさせていただきます。

    trapemiya様、

    アドバイスいただき、ありがとうございます。
    データベースのデタッチ、アタッチを使った方法、それから、
    この部分は詳しくないので、これから勉強ですが、SMOの、Transferオブジェクトを使った方法など、
    ご提案いただいた内容を検討しながら、解決方法を探したいと思います。


    拙い質問に、たくさんのアドバイスをいただき、ありがとうございました。
    2010年2月8日 10:35
  • こんにちは、nagino です。

    パーティションテーブルによる実装がもっともシンプルに思えます。
    Enterprise Edition のみの対応ですが・・・。


    MCITP(Database Developer/Database Administrator)
    2010年2月8日 10:41
  • nagino様

    パーティションテーブルのご案内、ありがとうございます。
    残念ながら、Enterprise Edition は、確保できない現状です・・・。

    ですが、とても勉強になります!
    教えていただいたURLで、じっくり勉強させていただきます。

    2010年2月8日 12:40