April 28, 2008 at 4:31 pm
I have a server I will be upgrading from sql 2000 runnning on win 2003 32 bit to sql 2005 64 bit running on win 2003 64 bit. My question is about the db's. They reside on a SAN. I know you can detach and attach, or restore from a sql backup to convert. The ? is, can I detach a 32 bit sql mdf,ndf,ldf files and attach them to a 64 bit version? ANyone tried this before?
April 29, 2008 at 5:17 am
AFAIK there is no set format for a 32 bit and 64 bit database files. 32 bit to 64 bit, is like giving more power to SQL Server to use 64 bit processing.
You could detach database from 32 bit server attach it to 64 bit server. The only problem you would have is when setting the compatibility level of the database. Now that your moving from SQL Server 2000 - SQL Server 2005, during attaching the SQL Server 2005 instance will convert the database to SQL Server 2005 readable (you can't get back to 2000) with compatibility level 80. If you change this manually, you may see issues.
Here is what I would do ideally
1) Run upgrade advisor against 2000 database
2) Fix the issues raised by the advisor
3) Move a copy of the .mdf and .ldf files from 2000 instance
4) Test the application
Hope this helps.
Viking
April 30, 2008 at 6:48 am
I have done this. The mdf,ldf files are the same format between 32-bit and 4-bit. Just make sure your apps can handle 64-bit especially if SQL Server accessing any API's (DLL) that are 32-bit for the application.
I was able to stop the services for SQL Server 2000, copy the database files over to the SQL Server 2005 folders (actually I was moving them to a new server). Then attach the database files.
After attaching the database files it where the work really begins. You have to becareful of the indexes; some are disabled going from 2000 to 2005 regardless of 32-bit/64-bit. Also, updating statistics and update usage (DBCC) is a must along with check db. Then you will need to remap logins to userids (this can be scripted) if you look around for them. The make sure any ODBC DSNs and API's are in place as well.
Enjoy!
April 30, 2008 at 7:40 am
We have moved a number of databases from 32 bit SQL Server 2000 to 64 bit SQL Server 2005 running on a cluster. We have used both the detach/attach method and have also restored from backups. Both methods have worked fine for us.
Mike
“I know that you believe you understand what you think I said, but I'm not sure you realize that what you heard is not what I meant.”...Robert McCloskey
___________________________________________________________________
April 30, 2008 at 8:08 am
thanks. One ? since you have been down that road. I was going to use SSIS to export logins. I have a mix of sql and nt/sql, did you do that before or after you attached?
April 30, 2008 at 8:14 am
We ran scripts after (although I am not sure that matters). Then we ran an SQL script to resync the users.
Mike
“I know that you believe you understand what you think I said, but I'm not sure you realize that what you heard is not what I meant.”...Robert McCloskey
___________________________________________________________________
April 30, 2008 at 6:04 pm
Driver may be a problem. Install the latest patch for Oracle There a bug with path that have"(x86)" in them.
DTS Package will require 32 drivers. Link server will require 64 drivers.
DTS package that have sql agent jobs, depends how you move them, may get new guid numbers.
You may loss all your versioning.
The DTSBackup 2000 tool is great for this. It help move dts packages and create new cmd commands for you cmd task.
Might want to install the new DTS desinger for SQL 2005 it can be a pain to open them packages once moved.
Run the DTSRUn/? at the command line to make sure dts was installed correct.
Also turn on DTS logging for each pacakge.
Test all your update statements. there are a couple of bugs with update statements.
Be ready to rule back just incase.
May 1, 2008 at 5:51 pm
One thing to note: If you have a 32 bit app that you want to run on the server's OS, and if it uses ODBC, you will want to dig out the 32 bit ODBC Manager from the system directory. The ODBC Manager on the menu is 64 bit, and any DSNs that you create with it won't be usable by a 32 bit app.
Upgrades from 2k 32 to 2k5 64 have gone well when I have done them.
I'm curious if anyone has done one that had CLR functions, and if there were issues with them.
July 18, 2008 at 7:31 am
Has anybody had to go the other way?
I have some software that has a new version out which will not run in 64bit environments. The old version ran fine there and so I have a 64bit OS and 64bit SQL2005 database. My user built a new server with 32bit OS and 32bit SQL2005 to get the software installed. Now we need to move the data from the old server to new.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply