Database Migration from SQL Server 2000 to 2008/2008 R2/2012

1.    Background:

The purpose of this article is to lay out the structure for database migration from SQL 2000/2005 to SQL 2008/R2/2012. This article describes migration process in a simpler way for database only which includes pre and post migration steps also.

2.    What is Upgrade?

Upgrade is an automated process in which the upgrade tool, called Setup, moves an old instance of SQL Server to a new instance while maintaining the data and metadata of the old instance. At the end of the upgrade, the old instance is no longer available and the new instance has the same name as the old instance.

3.    What is Migration?

Migration is a manual process in which the DBA installs a new instance of SQL Server and copies the metadata and data from an old instance of SQL Server to the new instance. Migration provides access to two instances of the system, letting you verify and compare the two systems. During migration, both the old and new systems remain online until migration to the new instance is complete. At the end of the migration, all applications are directed to access the new instance and the old instance is manually removed.

4.    High Level Architecture:

 

5.    Before Migration

5.1 What to Migrate

This step includes surveying environment to determine SQL Server components that will be migrating and then the best technical option(s) can be determined. Here we are planning to migrate SQL Server database.

  5.2 Upgrade prerequisites

1) Verify that SQL Server 2008/R2/2012 environment meets hardware and software requirements. 

2)  Ensure that SQL Server 2008/R2/2012 environment is capable of supporting future needs from a user and transaction perspective. (using SQL Server 2008 Upgrade Advisor to determine potential issues)

3)   Set up an environment to test the migration process as well as front-end applications and dependent (upstream and downstream) systems' functionality.

   5.3 SQL Server 2012 Upgrade Advisor

Perhaps the most important tool of the several tools typically used for upgrade planning is Upgrade Advisor. Upgrade Advisor smoothes the transition to SQL Server 2012 by predicting issues in your legacy instances of SQL Server 2000 and SQL Server 2005. It analyzes objects and code within legacy instances and produces reports detailing upgrade issues, if there are any, organized by SQL Server component. The resulting reports show detected issues and provide guidance about how to fix the issues or work around them. The reports are stored on disk, and you can review them by using Upgrade Advisor or export them to Microsoft Excel for further analysis.

In addition to analyzing data and database objects, Upgrade Advisor can analyze Transact-SQL scripts and SQL Server Profiler/SQL Trace traces. Upgrade Advisor examines SQL code for syntax that is no longer valid in SQL Server 2008/R2/2012. It generates a report listing the code in question, together with links to where you can find more information to help resolve the questionable code.

 

5.4 Requirements for running Upgrade Advisor 2012 are as follows:

1)    Windows Vista SP1, or SP2, Windows 7 and Windows Server 2008 R2.

2)  Windows Installer beginning with version 4.5. You can install Windows Installer from the Windows Installer Web site.

3) Microsoft .NET Framework 4. .NET Framework 4 is available on the SQL Server 2012 product media, and from the .NET Framework 4 download page.

4)  SQL Server 2000 Decision Support Objects (DSO) if analyzing SSAS (you can use SQL Server 2000 Setup to install DSO)

5)   SQL Server 2000 client components if analyzing DTS (you can use SQL Server 2000 Setup to install the SQL Server 2000 client components)

6)  Pentium III-compatible processor or a later version, with a processor speed of at least 500 MHz

7)  Available Disk Space should be 15MB.

Whether you choose an in-place upgrade or a side-by-side upgrade, run Upgrade Advisor on your legacy systems. You can run Upgrade Advisor from a local or remote server, and you can execute it from the Command Prompt window by using a configuration file name as an input parameter.

Note: You can run the SQL Server 2008/2012 Upgrade Advisor only against instances of SQL Server 2000 and SQL Server 2005. You cannot run it against instances of SQL Server 2008/2012 or on SQL Server 7.0.

Upgrade Advisor is a separate download. The most recent downloadable version is available as part of the below link:

http://www.microsoft.com/en-us/download/details.aspx?id=29065

5.  Key SQL Server 2012 migration considerations

Here are additional technical considerations that are keys to the migration:

1) Execute DBCC CHECKDB to validate that the previous environment (SQL Server 2000 or 2005) is free of corruption prior to the upgrade. 

2) Issue final backups and ensure a rollback plan is in place to continue business operations in case an unforeseen problem is encountered. 

3) Develop enterprise standards for SQL Server 2005 configurations, operations (security, maintenance, etc.) and development. 

4)   Archive unneeded data and perform data cleansing for the migrated data. 

5) Ensure all code is operating accurately prior to migration so that migration is the blame for broken code. 

6) Perform database maintenance such as UPDATE STATISTICS, DBCC CHECKDB and index rebuilds post upgrade. 

7) Validate that the SQL Server 2000/2005 configurations and database compatibility modes are accurate post upgrade.

6. What are the reasons for Migration to SQL Server 2008/R2/2012?

There are several reasons to migrate to SQL Server 2008/R2/2012 of which are involves server level improvements that are not performance related. SQL Server 2008/2012 offers certain reliability and availability improvements, such as the following:

1)     SQL Server 2008/2012's database mirroring is more robust and easier to configure and manage. 

2)    Policy-based management can be a tremendous feature for improving the consistency of SQL Server's configuration and ongoing management, especially with compliance or other restrictive requirements. 

3)     A new data auditing feature that provides more granular auditing of SQL Server activity. 

4)    Transparent Data Encryption (TDE) lets you turn on encryption without requiring massive application-level changes and can help meet specific security and compliance requirements. 

5)   The data collector helps make performance management and tuning easier and more centralized. 

6)   The Resource Governor gives administrators central control over CPU and memory allocation, which is useful for servers that host multiple databases and experience significant resource contention between those applications. 

7)    If you manage multiple servers, Server Group management enables you to run T-SQL queries against multiple servers simultaneously from a central management server. This makes server farm management easier and more consistent.

8)      To avail all the new features of the SQL Server 2008/2012.

7. Different ways of Database Migration (SQL Server 2000/2005 to SQL Server 2012)

 There are several ways to migrate the databases from SQL Server 2000/2005 to SQL Server 2008/2012:

        i. Copy database Wizard:  The Copy Database Wizard lets you move or copy databases and their objects easily from one server to another or one instance to another instance, with no server downtime. Using this wizard, you can do the following:

1)     Pick a source and destination server.

2)     Select databases to move or copy.

3)     Specify the file location for the databases.

4)     Create logins on the destination server.

5)     Copy additional supporting objects, jobs, user-defined stored procedures, and error messages.

6)     Schedule when to move or copy the databases.

In addition to copying databases, you can copy associated metadata, for example, logins and objects from the master database that are required by a copied database

       ii. Database Backup and Restore: You can take the backup the database from the previous version and restore it into the new version of SQL Server (SQL Server 2008).

            iii. Detaching and Attaching: To move a database using detach and attach, you should make the following steps:

1)    Detach the database.

2)    Move the database file(s) to the desired location on another server or disk.

3)    Attach the database specifying the new location of the moved file(s):  After detaching, the database will be removed from SQL Server but will be intact within the data and transaction log files that compose the database. You can use these data and transaction log files to attach the database to any instance of SQL Server, including the server from which the database was detached. After attaching, the database will be available in exactly the same state it was in when it was detached.

6. Post Migration testing:

After migration, we need to compare the previous version’s database to migrate database.

Followings are the things that we need to check:

      1)    Check the total no. of tables, stored procedures, views and their names in the migrated database. It should be same as previous versioned database.

       2)     Check the row counts of the tables