SQL Server 2000 or SQL Server 2005 are legacy versions which are no more fully supported by Microsoft. Most of the organizations have already migrated to the latest versions of SQL Server long back, however there are few servers or applications which still use older versions of SQL Server like SQL Server 2000 SQL Server 2005 due to compatibility issues with latest versions of SQL Server versions. There may be lot of cost involved to upgrade legacy applications to work with latest versions of SQL Server, but it is high time to either move to another application or upgrade the application to work with latest SQL Server versions. There are two different methods available to migrate or upgrade an SQL Server instance to SQL Server 2008 R2, which are In-Place Upgrade and Side-by-Side upgrade. It is always better to prefer Side-by-Side upgrade which is more safe method and also we can move to new hardware instead of using the old hardware to run the new SQL Server instances. Below are the important steps to keep in mind while upgrading from SQL Server 2000 or SQL Server 2005 to SQL Server 2008 R2 using side-by-side method.
– As a first step it is important to understand which objects would be affected after upgrading to SQL Server 2008 R2. We can get this information by running the SQL Server 2008 R2 Upgrade Advisor against the SQL Server 2000 or SQL Server 2005 instance which will generate an report with list of any warnings or issues.
– As part of the up-gradation process, stop all write activity to the SQL Server 2000 or 2005 instance. This may involve disconnecting all users or forcing applications to read-only activity.
– Transfer data from the legacy instance to the SQL Server 2008 R2 instance. This can be done by backup/restore of databases from SQL Server 2000 or SQL Server 2005 to SQL Server 2008 R2. To reduce the amount of downtime, one can look into options like Log Shipping or Database Mirroring.
– Create supporting objects in system databases like SQL Server Agent jobs, security settings(Logins, Server role permissions), Instance level configuration settings, Database options and DTS packages (legacy mode) to the new SQL Server 2008 R2 instance.
– Create new maintenance plans to mimic the old maintenance plans on server.
– Run validation scripts to verify integrity of new data (rebuild indexes, checkdb ..etc).
– Test the new instance to verify that applications work without any problems.
Also there are some new features available in SQL Server 2008 R2 like database mirroring, Auditing, BI, etc, so check if any of the new features are worth to be implemented to High Availability, Security, Stability, etc.
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.