Choirul at the .Net Land

Migration and upgrading are often overlooked by many developers and DBA. They never take care about it until they face the real problem in upgrade and migration. I recalled this matter when I was doing interesting discussion with several senior engineer at Freeport Indonesia on last April 5th, 2007. It was Dondy who called me and asked for an appointment, if I have a time to do technical presentation about this topics for his colleagues. Dondy is working for Freeport now, as he need, as a Software Archictect. Blah :P.

Actually I was on vacation at that time, but I agreed to do that. I’m willing to share anything I know to anybody who keen to learn. As usual, I opened my library and did some reviews before I went to Freeport office. Upgrade and migration is not a daily job, so that’s why every DBA is definitely miss many things about it.

To summarrize the discussion with Mera and Emal who’re responsible for big database at Freeport, these are the things that we need to take attention on migration:

Understand your need. You should choose between upgrade and migration. Upgrade means that you just install SQL2005 on existing SQL2000 machine, close your eyes, and the installation wizard will do everything for you. Hopefully everything is OK, then you will get a new SQL 2005 machine with everything is upgraded.

Upgrade is not the option when you have alot of DTSs, replications, and Analysis Services (AS) packages in your server. DTS and AS do not work well with the upgrade proccess. If this is the case, choose migration instead. Install a new SQL 2005 instance, either on the same machine or on another machine.

Migrate the database engine and database file first. You have some options to do this. Attach/dettach, backup/restore, or using copy database wizard. As far as my experiences, copy database wizard doesn’t work well with big database file. It’s often got the timeout or object copy errors.

Migrate the AS database with AS Migration Wizard. Just type MigrationWizard.exe and follow the path. You need to supply the instance of AS server for both source (SQL 2000) and destination (SQL 2005). Please take note that some AS object such as virtual cube is not supported in SQL 2005. Read the detail in MSDN about that.

Migrate the DTS package to SSIS. Type DTSMigrationWizard.exe in command prompt, and follow the steps. Once you’re connected to SQL 2000 instance, you can choose which DTS package to be migrated. Some objects in old DTS task is not supported, but you still have a chance to make it works in SQL 2005. Make sure you download the SQL Server 2005 Feature Pack, and install SQL 2000 DTS Backward Compatibility before the migration process.

Follow the rule in replication. If you have several servers that involved in replication, then you have to follow this sequence on migration process: upgrade/migrate the distributor first, followed by the publisher, and then subscriber.

We also discussed about the performance impact about placing the database file on several filegroups. Because their database is currently reside on one big filegroup, they need to alter every table they want to move to another filegroup. It’s not and easy job, but it will paid off :). Performance will increase as a result of accessing several IOs in parallel. Also, they can do backup, restore, archiving in an easier manner instead of doing backup and restore on one big file everytime.

It was a nice discussion, and it reminded me on several fundamental aspects of SQL Server. Hope to see you again!