32bit to 64bit sql server 2008 database conversion
We are in the process of moving databases from older 32 bit hardware running sql 2005 to newer hardware with sql 2008 64 bit. My question is if the database is automatically converted to 64bit after it is reattached on the new server or if it is running in 32bit mode on a 64bit instance. Is there a way to tell?
Yes you can simply detach and attach your database when upgrading from 32-bit to 64-bit Windows.
I have done an x86->x64 conversion in the past and did not have any problems.
There is no problem because the storage layer is the same for both SQL x86 and x64.
The SQL Server on-disk storage format is the same in the 64-bit and 32-bit environments. Therefore, attach works across 32-bit and 64-bit environments. A database detached from a server instance running in one environment can be attached on a server instance that runs in another environment.
So to answer your question, if you:
- Created a database on an x64 MS SQL Server
- Created a database on an x86 MS SQL Server and moved it to the x64 MS SQL Server
Both Option #1 and #2 would result in the same end state. There is no such distinction between an x86 database and an x64 database.
From 2005 to 2008 there is a compatibility mode that is used. But for x86 to x64 there is no difference.
The database does not care, it is the server software that is running 32 or 64 bit. When you move a database from SQL2005 to SQL2008 it will have it compatibility mode set to SQL2005. To use SQL2008 features you will have to change it to SQL2008. If your database has some incompatibilities you can create another database that is SQL2008 and execute SQL2008 SQL against the SQL2005 database from the SQL2008 database.