question

Rup avatar image
Rup asked

Migrating a large database from SQL Server 2005 to 2008

I have a 20TB SQL Server 2005 database that I would like to move to SQL Server 2008. The database has around 700 files.

Our production support team does not want a long downtime (1-2 days maximum) and they are also unable to allocate another 20TB storage for the new instance.

Under these circumstances, I am thinking of the detach / attach approach for migration. I will perform the following steps.

  1. An sp_detach_db on the old production database.

    use master
    go
    
    
    Alter Database ProdDB2005 Set SIGLE_USER;
    
    
    exec sp_detach_db @dbname='ProdDB2005', @skipchecks='true' 
    
    
    
  2. Create a new db on the SQL 2008 instance using the Create Database for Attach command.

    Create Database ProdDB2008
    On
    (FILENAME = 'x:\..\..\primary.mdf'),
    (FILENAME = 'x:\..\..\log.ldf'),
    (FILENAME = 'x:\..\..\secondary_001.ndf'),
    (FILENAME = 'x:\..\..\secondary_002.ndf'),
    ....
    ....
    (FILENAME = 'x:\..\..\secondary_700.ndf')
    For Attach;
    

I have tried this command on a 1GB development DB and it worked in about 1 minute on a development PC (3GB RAM, Dual Core CPU). Our production server has 32GB of RAM, 8 CPUs and the database files are mounted on SAN.

I would like to estimate how long the two above mentioned steps might take to run so an appropriate downtime can be planned. I would also like your expert suggestions on whether there are better ways to migrate this DB.

sql-server-2008sql-server-2005
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

I have no idea how long this update will to take to run. Sorry.

But, the approach you're taking is the best one. There are really only three ways of doing it, backup/restore, detach/attach, and some sort of object by object migration like through SSIS. Your approach seems to be the best under the circumstances. Make sure you change the compatibility level after you attach. The only thing I'd add, and it might be iffy for you considering the size, is to be sure you update the statistics.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered

The attach process is the fastest way as you already figured out and i think it wouldn't take much longer with your 20Tb database. Attach doesn't need to allocate new files as in an ordinary create database. Attach only stores basic information about your database and files master database.(à bit simplified) As Grant Said, make sure you alter compatibility mode and update stats. When you are done with the migration you should try database compression, you may Love it! I hyras you have à LOT of io operations, and you may reduce it with compression om the cost of CPU.

(i dislike autocomplete in iPhone)

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rup avatar image Rup commented ·
Thanks Grant and Håkan for your answers. In fact compression is the primary reason we are trying to move over to 2008. The database is partitioned by date and we can save a lot of space by compressing partitions older than one year. I think the backup compression featurev is also very helpful.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.