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?
+2
A:
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.
Brian R. Bondy
2010-03-26 20:05:43
That is working fine, my question is if the database is still running in 32bit mode or if it is now in 64 bit mode? I am assuming it is 64 bit since the instance is a 64 bit instance.
Eric
2010-03-26 20:08:13
The storage layer is the same, its just the processes that are running as 64-bit now.
Brian R. Bondy
2010-03-26 20:09:07
@Eric: See my edit
Brian R. Bondy
2010-03-26 20:11:12
Maybe I am not expressing my question properly, is it possible that the database is still running in 32 bit mode on a 64bit instance or is it automatically converted to 64bit? I thought I had read someplace that the database would be larger on the 64bit instance
Eric
2010-03-26 20:11:14
@Eric: Your database should be the same whether it was created on x86 and upgraded to x64 or on x64. There is no such difference between an x86 database and an x64 database.
Brian R. Bondy
2010-03-26 20:12:35
Yes to option #2 it is a 32 database on a sql 2005 server that we are moving to a 64 bit sql 2008 instance. moving the database is not a problem I am just confused as to whether the database is converted from 32 bit to 64 bit after the move or if it is running in some type of 32 bit mode on a 64 bit instance?
Eric
2010-03-26 20:13:25
@Eric: As far as x86 and x64 you are assuming that there is a concept of an x86 database format and an x64 database format. There is not. They are the same file format. Also above Option #1 and Option #2 where not questions but it was an example to show that they are equivalent.
Brian R. Bondy
2010-03-26 20:18:10
will the size of the database get larger when moved from 2005 32 bit to 64 bit. It seems I read someplace that the database will get larger in size
Eric
2010-03-26 20:21:39
@Eric: No it will not be larger. Since the storage layer is the same, they will produce equivalent file formats (and therefore sizes) on disk.
Brian R. Bondy
2010-03-26 20:24:45
@Eric: the database format (file on disk) are **THE SAME** between 32- and 64-bit SQL Server. The only thing that changes is the SQL Server processes - they now run in 64-bit instead of 32-bit. There is **NO IMPACT** whatsoever on your files, or your disk. The database cannot be in **64-bit mode** - there is no such thing. The database is the database and remains the database. **NO CHANGES** whatsoever.
marc_s
2010-03-26 21:54:26
+1
A:
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.
Tom Groszko
2010-03-26 20:12:38