December 21, 2010 at 6:33 am
I am in the process of consolidating several SQL Servers (some SQL server 2000 32 bit and some SQL Server 2005 32bit) onto a Server running Windows 2008R2 and SQL Server 2005 Standard Edition 64bit.
Here are the scripts that I run to do the migration from SQL Server 2000 to SQL Server 2005:
USE MASTER
ALTER DATABASE MYDB SET PAGE_VERIFY CHECKSUM
--Run DBCC UpdateUsage
Use MYDB
DBCC UPDATEUSAGE (0)
--Update all statistics
Use MYDB
EXEC sp_updatestats
--Rebuild all indexes (writes the checksum on all index pages)
USE MYDB
EXEC sp_msForEachTable @COMMAND1= 'DBCC DBREINDEX ( "?")'
--Run a DBCC CheckDB with the Data_Purity option
USE MYDB
DBCC CHECKDB with DATA_PURITY
--Set the db into compatibility mode 90
USE MASTER
Exec sp_dbcmptlevel @dbname = 'MYDB', @new_cmptlevel = 90 ;
My question is ... is there anything else that needs to be done to make the 32bit Databases compatible on a 64bit Instance?? I have been doing some reading and researching and have found people having incompatiblity problems - especially with extended stored procedures after moving the databases to 64bit.. but I have not been able to find any solutions. Can anyone help guide me in the right direction?
December 21, 2010 at 7:28 am
Make sure you don't have linked server (or direct access) to excel files on the 32 bit. They won't work on the 64 bit server. There is apparently a workaround but I was never able to put it in prod (and ran out of time to implement it).
December 21, 2010 at 7:40 am
Databases are not 32 bit or 64 bit. There is no difference in the file structure or backup structure between the two. You can move databases from 32 bit to 64 bit and back with no concern.
The steps you have listed are for SQL 2000->SQL 2005 upgrade (or 2000 - 2008 or 2005 - 2008) and have nothing to do with the processor architecture.
You may have problems if you have custom extended stored procedures, but that's not due to the database structure, that's because extended procs are basically applications running in the SQL process space.
Why are you upgrading to SQL 2005? Why not SQL 2008 or 2008 R2?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2010 at 7:48 am
we are not going to 2008 because we have too many legacy apps that are not certified for 2008
December 22, 2010 at 9:04 am
stop using old commands like dbreindex and use new ones like alter index. if you rebuild all your indexes it should also rebuild all your statistics as well
December 22, 2010 at 11:49 am
alen teplitsky (12/22/2010)
if you rebuild all your indexes it should also rebuild all your statistics as well
Rebuilding indexes only updates the stats that are associated with indexes. It won't update column statistics (ones created by auto_create_stats or CREATE STATISTICS)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply