To easily see which SQL Process relates to which Instance of SQL Server – In Windows Task Manager / Processes
– right-click on one of the ‘sqlservr.exe’ names
– and select ‘Open file location’.
By examining the path to the file that opens I was able to distinguish the particular instance that this executable related to.
Looking over a new SQL Server I noticed a failing job. It was the ‘Check DB’ part of a maintenance-plan and contained this error message …
Msg 2508, Level 16, State 3, Line 1
The In-row data RSVD page count for object “SomeTable”, index ID 0, partition ID 104586854531027, alloc unit ID 104586854531027 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.
CHECKDB found 0 allocation errors and 1 consistency errors in table ‘SomeTable’ (object ID 1595868725).
It transpired that this box was once upgraded from SQL-2000 and some steps had been missed.
It was trivial to fix (and not a sign of corruption) …
DBCC CHECKDB WITH DATA_PURITY; GO EXEC sp_MSForEachDB 'DBCC UPDATEUSAGE(?);';
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 