I worked with a client that migrated from SQL 2000 32 bit database straight up to SQL 2012 64 bit! Quite a challenge, and quite the journey over 12 years and 5 releases (2000,2005,2008,2008R2,2012) of SQL.
Some of the complicating factors include:
Backup and Restore
It is not possible to restore a SQL 2000 backup into SQL 2012. The solution is install an intermediate SQL instance (2005,2008,2008 R2), restore to that instance, then detach and attach into the SQL 2012 instance. For smaller databases, I was able connect to the SQL 2000 database from my SQL 2012 instance and use ‘Generate Scripts…’ in the context menu to script the objects and data.
DTS
SQL 2000 DTS packages no longer supported. Found the solution on MSDN from Jyoti Grove’s comment. This required locating and installing the SQLServer2005_BC_x64.msi file on my 2008 R2 install disk. I created a SSIS project in BIDS 2008, and migrated the dts packages individually into a SQL 2008 R2 SSIS project. Was able to migrate this project into SQL 2012 VS 2010 project. Some legacy tasks like ActiveX and older connection managers need to be rewritten, but it’s a great start.
Linked Server to Oracle
Installed the Oracle 11g 64 bit client, exported the connection info using the Oracle Net Manager (version 9) on the old server, imported using the same tool (version 11g) on the new server, set up an ODBC System DSN and I was able to talk with Oracle! They have an instant client available, but it wasn’t instant for me to set up. This blog post helped me quite a bit.
32 and 64 Bit driver side by side
Client was using Toad as an IDE for Oracle and SQL, but it’s built on Delphi, and only 32 bit. I downloaded the zip of the instant client 32 bit version, and during the install was able to point to the instant client zip file. Once installed, added the environment variable for oracle, and started toad and was able to find the Oracle server.
Still to do
Will need to rework parts of the SSIS packages, and recreate the SQL Agent jobs.
Client exports report results to Excel, may be some issues to work around there.
Results
Got this client migrated successfully. It was the same client that had the data warehouse with tables copied from Oracle, so I figured out how to script the indexes from Oracle into SQL Server. The database is fast and they’re happy.