SE の雑記

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

第 1 回 Get The Fact セミナーの振り返り その 9

leave a comment

今回は [デタッチ / アタッチを使用したデータベースの移動] について振り返っていきたいと思います。

SQL Server にはデータベースを デタッチ (サーバーからデータベースのエントリを外す) / アタッチ (サーバーにデータベースのエントリを追加) することで、データベースを移動 (別サーバー / 同一サーバーの別ディレクトリ) することができます。

セミナーの中では、上位バージョンへのアタッチ / アタッチ後の統計情報についてのお話がありましたので、今回はその点をまとめてみたいと思います。

 

■上位バージョンへのアタッチ

SQL Server ではデータベースをデタッチ / アタッチすることでデータベースを移動することができます。

アタッチはバージョンが上位のデータベースに対しても実行することが可能です。
SQL Server 2000 では [pubs] というデータベースがありました。
このデータベースをデタッチして SQL Server 2008 R2 にアタッチしてみたいと思います。

まずは、SQL Server 2000 で [pubs] データベースを [デタッチ] します。
image
image

デタッチをすることで、SQL Server からデータベースのエントリが外れます。
image

この状態になると SQL Server のサービスを起動していた状態でデータベースのファイル (mdf / ldf) をコピーすることが可能となります。
image

ファイルをコピーして SQL Server 2008 R2 に [アタッチ] をします。
image
image

このような操作をすることでデータベースを移動することができます。

アタッチした直後はデータベースの [互換性レベル] はアタッチ元のバージョンの状態となっています。
image

今回は SQL Server 2000 のデータベースをアタッチしていますので、互換性レベルも [SQL Server 2000] となっています。
互換性レベルが低い状態では、SQL Server のすべての機能が使用できないのでアタッチで移動した場合は互換性レベルを変更することも検討したほうが良いかと思います。
互換性レベルを低くした場合、以前のバージョンの構文解析が使える可能性がありますが、なくなった機能が保障されるわけではないですので。

■アタッチ後の統計情報の更新状況について

データベースのアタッチ後の、データベースの断片化や統計情報ですがアタッチ後も以前の状態を引き継いでいる (アタッチ前のバージョンの状態) という話がセミナー内でありました。

この動作を実際に確認してみたいとおもいます。

今回は [pubs] データベースの [UPKCL_auidind] のインデックスの統計情報について確認してみたいと思います。
統計情報の確認ですが、以下のクエリを使用しました。

DBCC SHOW_STATISTICS(‘dbo.authors’, ‘UPKCL_auidind’) WITH  STAT_HEADER

SQL Server 2000 で実行した結果が以下になります。
image

それでは、SQL Server 2008 R2 でも確認をしてみたいと思います。
image

[Updated] を比較すると、同一の日付になっていることが確認できます。

互換性レベルを変更して、統計情報の更新状況を確認してみます。

互換性レベルを [SQL Server 2000] → [SQL Server 2008] に変更して、
image
統計情報を確認してみます。
image

互換性レベルを変更しても、統計情報は変わっていないですね。

異なるバージョンにアタッチをした場合、統計情報は以前の情報がそのまま引き継がれます。
そのため、手動で統計情報を更新することで最新のバージョンの統計情報に明示的に更新することがセミナーで話にありました。

[sp_updatestats] を実行することで全オブジェクトを対象として統計情報を更新することができます。

sp_updatestats を実行した後は、統計情報が更新されていることが確認できます。
image

実行前後を比較すると統計情報の内容が変わっていることが確認できます。
インデックスの断片化の状態も変わらないのでデータベースの断片化解消と統計情報の更新はアタッチ後に実施したほうが良さそうですね。
# インデックスの再構築をした場合は統計情報も更新されたはずですが。

 

■アタッチ後のデータベースの互換性について

データベースは下位互換があるため、新しいバージョンの SQL Server では下位バージョンの SQL Server のデータベースをアタッチすることが可能です。

セミナーを受けていて、一度上位のバージョンでアタッチしたデータベースを下位のバージョンで再度アタッチすることができるかという疑問がわきましたのでこの点について少し確認をしてみたいと思います。

まずは SQL Server 2000 でデタッチしたデータベースを SQL Server 2008 R2 でアタッチします。
image

その後、何も操作をしないで SQL Server 2000 にデータベースをアタッチしてみます。
そうすると以下のエラーが発生します。
image

一度、上位のバージョンでアタッチしたデータベースは下位のバージョンではアタッチできないようです。

SQL Server 2000 だとメッセージが分かりにくいので、SQL Server 2005 でアタッチをしてみたいと思います。
image
こちらのエラーメッセージだとわかり易いですね。
SQL Server のデータベースはダウングレード パスがサポートされていないと表示されています。

SQL Server のデータベースですが内部でデータベースのバージョンを保持しています。
# ここでいうバージョンは互換性レベルとは異なります。
データベースのバージョンですが、[DBCC DBINFO] を使用することで確認ができます。

DBCC DBINFO を実行するためにはトレースフラグ [3604] を有効にする必要があります。
以下のクエリを実行すると DBCC DBINFO による情報の取得ができます。

DBCC TRACEON(3604)
DBCC DBINFO
DBCC TRACEOFF(3604)

SQL Server 2000 で実行した結果が以下になります。

DBINFO STRUCTURE:
—————–

DBINFO @0x05D2F170
——————
dbi_dbid = 1              dbi_status = 24           dbi_nextid = 497436846
dbi_dbname = master       dbi_maxDbTimestamp = 600  dbi_version = 539
dbi_createVersion = 539   dbi_ESVersion = 0        
dbi_nextseqnum = 1900-01-01 00:00:00.000           
dbi_crdate = 1900-01-01 00:00:00.000                dbi_filegeneration = 0

dbi_checkptLSN
————–
m_fSeqNo = 162            m_blockOffset = 319       m_slotId = 1

dbi_dbbackupLSN
—————
m_fSeqNo = 0              m_blockOffset = 0         m_slotId = 0

dbi_differentialBaseLSN
———————–
m_fSeqNo = 0              m_blockOffset = 0         m_slotId = 0

dbi_createIndexLSN
——————
m_fSeqNo = 0              m_blockOffset = 0         m_slotId = 0

dbi_oldestBackupXactLSN
———————–
m_fSeqNo = 0              m_blockOffset = 0         m_slotId = 0

dbi_versionChangeLSN
——————–
m_fSeqNo = 0              m_blockOffset = 0         m_slotId = 0
dbi_familyGUID = 00000000-0000-0000-0000-000000000000

dbi_recoveryForkNameStack
————————-

entry 0
——-
m_fSeqNo = 0              m_blockOffset = 0         m_slotId = 0
m_guid = eafadb72-3092-4cb4-9719-4232baca799e      

entry 1
——-
m_fSeqNo = 0              m_blockOffset = 0         m_slotId = 0
m_guid = 00000000-0000-0000-0000-000000000000      
dbi_differentialBaseGuid = 00000000-0000-0000-0000-000000000000
dbi_collation = 53264     dbi_category = 0         
dbi_maxLogSpaceUsed = 5416960                  

SQL Server 2000 では、バージョンは [539] となります。

SQL Server 2008 R2 で実行した結果が以下になります。

DBINFO STRUCTURE:

DBINFO @0x000000000E83DBE0

dbi_dbid = 1                         dbi_status = 65544                   dbi_nextid = 1739153241
dbi_dbname = master                  dbi_maxDbTimestamp = 4000            dbi_version = 661
dbi_createVersion = 661              dbi_ESVersion = 0                   
dbi_nextseqnum = 1900-01-01 00:00:00.000                                  dbi_crdate = 1900-01-01 00:00:00.000
dbi_filegeneration = 0              
dbi_checkptLSN

m_fSeqNo = 217                       m_blockOffset = 496                  m_slotId = 1
dbi_RebuildLogs = 0                  dbi_dbccFlags = 0                   
dbi_dbccLastKnownGood = 1900-01-01 00:00:00.000                          
dbi_dbbackupLSN

m_fSeqNo = 0                         m_blockOffset = 0                    m_slotId = 0

dbi_oldestBackupXactLSN

m_fSeqNo = 0                         m_blockOffset = 0                    m_slotId = 0
dbi_LastLogBackupTime = 1900-01-01 00:00:00.000                          
dbi_differentialBaseLSN

m_fSeqNo = 204                       m_blockOffset = 328                  m_slotId = 150

dbi_createIndexLSN

m_fSeqNo = 0                         m_blockOffset = 0                    m_slotId = 0

dbi_versionChangeLSN

m_fSeqNo = 141                       m_blockOffset = 232                  m_slotId = 1

dbi_MinSkipLSN

m_fSeqNo = 0                         m_blockOffset = 0                    m_slotId = 0
dbi_familyGUID = 433c0a8b-4d49-473a-89a4-37275c72ca4e                    
dbi_recoveryForkNameStack

entry 0

m_fSeqNo = 0                         m_blockOffset = 0                    m_slotId = 0
m_guid = 89b51b99-fcc8-4a24-ac6b-d1bda81b4427                            

entry 1

m_fSeqNo = 0                         m_blockOffset = 0                    m_slotId = 0
m_guid = 00000000-0000-0000-0000-000000000000                            
dbi_differentialBaseGuid = 662f7cdb-cbd9-4c5d-9168-311c7ed84211           dbi_firstSysIndexes = 0001:00000010
dbi_collation = 315464               dbi_category = 0                     dbi_maxLogSpaceUsed = 5275648
dbi_localState = 0                   dbi_roleSequence = 0                
dbi_failoverLsn

m_fSeqNo = 0                         m_blockOffset = 0                    m_slotId = 0

dbi_dbmRedoLsn

m_fSeqNo = 0                         m_blockOffset = 0                    m_slotId = 0

dbi_dbmOldestXactLsn

m_fSeqNo = 0                         m_blockOffset = 0                    m_slotId = 0
dbi_dbMirrorId = 00000000-0000-0000-0000-000000000000                    
dbi_pageUndoLsn

m_fSeqNo = 0                         m_blockOffset = 0                    m_slotId = 0
dbi_disabledSequence = 0            
dbi_dvSplitPoint

m_fSeqNo = 0                         m_blockOffset = 0                    m_slotId = 0
dbi_CloneCpuCount = 0                dbi_CloneMemorySize = 0             
dbi_updSysCatalog = 1900-01-01 00:00:00.000                              
dbi_LogBackupChainOrigin

m_fSeqNo = 0                         m_blockOffset = 0                    m_slotId = 0
dbi_dbccLastKnownGood = 1900-01-01 00:00:00.000                           dbi_modDate = 1900-01-01 00:00:00.000
dbi_verPriv = 171050706              dbi_svcBrokerGUID = 00000000-0000-0000-0000-000000000000
dbi_svcBrokerOptions = 0             dbi_dbmLogZeroOutstanding = 0        dbi_dbmLastGoodRoleSequence = 0
dbi_dbmRedoQueue = 0                 dbi_rmidRegistryValueDeleted = 1     dbi_dbmConnectionTimeout = 0
dbi_fragmentId = 0                   dbi_AuIdNext = 1099511627812         dbi_CurrentGeneration = 0

dbi_EncryptionHistory

Scan 0

m_fSeqNo = 0                         m_blockOffset = 0                    m_slotId = 0
EncryptionScanInfo:ScanId = 0       

Scan 1

m_fSeqNo = 0                         m_blockOffset = 0                    m_slotId = 0
EncryptionScanInfo:ScanId = 0       

Scan 2

m_fSeqNo = 0                         m_blockOffset = 0                    m_slotId = 0
EncryptionScanInfo:ScanId = 0      

SQL Server 2008 R2 では [661] となります。

アタッチを GUI で実行した場合にはわからないのですが、クエリでアタッチをすると以下のメッセージが表示されていることが確認できます。

USE [master]
GO
CREATE DATABASE [pubs] ON
( FILENAME = N’F:SQL2008R2pubs.mdf’ ),
( FILENAME = N’G:SQL2008R2pubs_log.ldf’ )
FOR ATTACH
GO

データベース ‘pubs’ をバージョン 539 から現在のバージョン 661 に変換しています。
データベース ‘pubs’ で、バージョン 539 からバージョン 551 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 551 からバージョン 552 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 552 からバージョン 611 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 611 からバージョン 621 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 621 からバージョン 622 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 622 からバージョン 625 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 625 からバージョン 626 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 626 からバージョン 627 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 627 からバージョン 628 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 628 からバージョン 629 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 629 からバージョン 630 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 630 からバージョン 631 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 631 からバージョン 632 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 632 からバージョン 633 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 633 からバージョン 634 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 634 からバージョン 635 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 635 からバージョン 636 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 636 からバージョン 637 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 637 からバージョン 638 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 638 からバージョン 639 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 639 からバージョン 640 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 640 からバージョン 641 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 641 からバージョン 642 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 642 からバージョン 643 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 643 からバージョン 644 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 644 からバージョン 645 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 645 からバージョン 646 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 646 からバージョン 647 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 647 からバージョン 648 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 648 からバージョン 649 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 649 からバージョン 650 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 650 からバージョン 651 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 651 からバージョン 652 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 652 からバージョン 653 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 653 からバージョン 654 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 654 からバージョン 655 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 655 からバージョン 660 へのアップグレード手順が実行されています。
データベース ‘pubs’ で、バージョン 660 からバージョン 661 へのアップグレード手順が実行されています。

アタッチしたタイミングでデータベースのアップグレードが実行されます。
そのため、特に操作をしなくてもデータベースをアタッチしただけで内部のバージョン情報が変わり、下位の SQL Server ではアタッチできなくなります。

軽く調べたところ、SQL Server のバージョン別のデータベースの内部バージョン情報は以下のようになるようです。

SQL Server 2000 539
SQL Server 2005 611
SQL Server 2008 655
SQL Server 2008 R2 661
SQL Server Denali CTP1 684

デタッチ / アタッチによるデータベースの移動は内部バージョンと統計情報の更新に気を付ける必要がありそうですね。

振り返りの最後にパフォーマンスデータコレクションについてのお話がありました。

今回の振り返りの最後としてパフォーマンスデータコレクションについてまとめていきたいと思います。

Written by masayuki.ozawa

1月 1st, 2011 at 10:23 pm

Posted in SQL Server,セミナー

Tagged with ,

«
»

Leave a Reply

CAPTCHA

*