Best Practices: Upgrading Cluster SQL Server from 2000 to 2005 version - are you worried?
I know it sounds easy to upgrade a SQL Server Cluster environment from 2000 to 2005 version, as easy it mentioned within the Books Online for SQL Server 2005. When I have performed for the first time, my first thought was (do we need that or take out the reasons). But one thing is sure that SQL Server 2005 offers a number of enhancements you want to take advantage of, so if and when you've got to do it, you can proceed without a lot of downtime!
Do not forget to evaluate the costs involved in this upgrade, wherever possible perform the upgrade on a new server and do not corrupt the existing servers. So in terms of the expensive solution will be: creating a whole new cluster, which means new servers and perhaps a new storage area network (SAN). This will ensure no disturbance to the current undisturbed platform and retaining the existing items such as network switches. This doesn't sound fruitful to the finance team within the organisation, but it has major advantages in reducing downtime to the application. By default the new server will have much resources such as Memory, disks and will generally performs much better than old, with disk capacity and speed increasing at an ever-growing rate. But, before taking the live server out make sure to perform complete end-to-end testing on the application.
So to take this in practicality, once you have the hardware in place, you can create your new virtual SQL Server on this setup, copy your production databases over, and then put the new system through its paces, leaving plenty of time to shake out the bugs before cutover day. Just be sure to script out the logins from your existing server, this is where most of the DBAs missout for the logins. If you have any high availability options set such as log shipping, unless your databases are quite small and you have a period of time in which no users are connected. You can log-ship right up to just before cutover. Then, take the users out, cut and ship the final log, then point the app at the new instance.
Going from SQL Server 2000 to 2005 do have added advantages such as Database Mirroring which is far superior than Log shipping in previous version, and if you use DNS aliases, you probably won't even need to point the apps to the new instance. Just update the DNS alias instead. This approach has the advantage that if you get part way through the migration and have to revert back to the original, at least you have the original. This may sound long-time process and will be very easy if you can practise before the live implementation date.
Ok, how about upgrading existing hardware which is a less expensive route, but it requires more advance planning. BY default a cluster can support more than one SQL Server instance, but each instance must have its own disk resources. So when you're carving up your SAN, set one LUN aside for a future upgrade. To perform the upgrade, install SQL Server binaries on this disk resource. You can exercise the system and, when you're ready, shut down the current SQL Server, move the disk resources from the old SQL Server group, update the dependencies, and bring the new SQL Server instance online. Attach the databases from the old instance, and you're up and running. Just forgot to mention in any case during any upgrading process requires full backups for both system and user databases, logins and any other application connectivity components such as third party drivers or other data sources within ETL processes.
The second one do sound less-expensive approach-and it carries with it some risk. For instance if something goes bad, you can't detach the databases from the new instance and put them back. You're reduced to restoring from backups-and that can mean some serious downtime. This involves if you have enough space on the current SAN hardware to accomodate 2 copies of current databases (system & user). Detaching & attaching or Backup & restoring the databases may be easy once you have practiced, but there is an additional overhead to test the failover of current & new cluster setup. There comes resolution & bug fixes that involves resources from systems & humans.
Practice Makes Process Perfect, do not leave any process even documenting the whole procedure & steps. The documentation will always help you to go through the process if it is required in the future.
