Migrating from SQL 2000 to SQL 2012

regsamp
regsamp used Ask the Experts™
on
Is it possible to take a Microsoft SQL 2000 database on one server and migrate it to a completely different server that is running Microsoft SQL 2012 and use this database? We use it for our Intranet.

Any assistance offered would be greatly appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
No, you can't do it directly.

http://blogs.technet.com/b/mdegre/archive/2012/06/15/migration-sql-server-2000-to-sql-server-2012.aspx

In short, you need to upgrade to SQL 2005 latest service pack first, then upgrade to 2012.

Author

Commented:
So the existing database that is currently SQL 2000 and is our Intranet needs to be upgraded to SQL 2005 and then migrated over. Correct?
Yes, that is correct.

Has the code in the intranet that makes SQL calls been upgraded to work with SQL 2005 and above?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
It has not. Does all that need to be updated as well?
I don't know as I don't know what the code is like that calls into your current database. I wouldn't do this on your live system. Build a virtual machine copy of the intranet and then try upgrading in a test environment.

This is no small job and needs to be handled correctly.

Author

Commented:
We have been trying to contact the Programmer who did the coding but nothing so far. Exactly. This is quite a big job that we want to make sure it does not have tons of issues.
Ok, well things like the database using deprecated datatypes:

http://dba.stackexchange.com/questions/15857/upgrade-process-from-sql-server-2000-to-sql-server-2012

SELECT  sys.objects.NAME AS [table_name] ,
        sys.columns.NAME AS [column_name] ,
        sys.types.NAME AS [data_type]
FROM    ( sys.columns
          INNER JOIN sys.objects ON sys.objects.object_id = sys.columns.object_id
        ) INNER JOIN sys.types ON sys.types.system_type_id = sys.columns.system_type_id
WHERE   sys.types.name IN ( 'text', 'ntext', 'image' )

Open in new window


More here on the migration path:
http://sqlmag.com/sql-server-2012/migrating-sql-server-2012

This link includes the supported edition and version upgrades here:
http://msdn.microsoft.com/en-us/library/ms143393.aspx

There may be SQL functions that have been used in the intranet code and/or stored procedures that are deprecated in the later version of SQL.

Author

Commented:
Thank you for those links and information. I will go through them.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial