Luckily there was a shiney new (virtual) server waiting to be the backend for this customers myriad in-house applications.
The live production server was Standard edition SQL 2000, so I installed SQL Server 2008r2 on my laptop and SQL2012 on the new box – both ‘Standard editions’.
My plan …
– Tidy-up by removing dev and unused databases.
– Run SQL2008r2 upgrade-advisor against the SQL2k box & correct issues
– Backup all SQL2k user databases & drag to SQL2008 box
– Restore them (triggering an auto-upgrade)
– Change compatability levels from 80 to 100
– Run SQL2012 upgrade-advisor against SQL2008r2 box & correct issues
– Backup all SQL2008 user databases & drag to SQL2012 box
– Restore them (triggering an auto-upgrade)
– Change compatability level from 100 to 110
– DBCC CHECKDB WITH DATA_PURITY;
– EXEC sp_MSForEachDB ‘DBCC UPDATEUSAGE(?);’;
– Capture and apply SQL2k logins to SQL2012
– Rewrite 2x DTS packages as SSIS
– Apply SQL 2012 (SP2)
– Backup all SQL2012 databases
I was able to get one application (and Reports) re-pointed to the new backend immediately, however the others look ages.
Some of the ‘fun’ issues moving individual applications were … Some had links to a remote Oracle database necessitating Oracle 64bit client installation. Many had old reporting issues.
To keep the SQL2012 data up to date during the ‘fun’, I ran the SSIS wizard from SQL2012 and pulled data directly from SQL2k.
Database Consultant 